excel vba call subroutine with variables

excel vba subroutine
user366121 picture user366121 · Jun 6, 2011 · Viewed 83.3k times · Source

I defined the following subroutine:

Sub EnterCellValueMonthNumber(cells As range, number As Integer)

range(cells).Select
ActiveCell.FormulaR1C1 = number

End Sub

When I call the subroutine like this:

EnterCellValueMonthNumber ("N23:Q23",1)

I get the following error message:

Compile error Expected: =

I have no idea why I get this message. Does anyone know what I am missing?

Answer

Simon Cowen picture Simon Cowen · Jun 6, 2011

You would call the sub as

EnterCellValueMonthNumber "N23:Q23", 1

No brackets. Or

Call EnterCellValueMonthNumber("N23:Q23", 1)

Brackets, and Call before it.

Also, your Sub is expecting a Range object as the first argument and you're supplying a string; you should change the signature of the sub to:

Sub EnterCellValueMonthNumber(cells As String, number As Integer)

Also, I'm uncertain as to what you are trying to achieve with this code as it will only set the top-left cell of the range to 1. Would something like

Range(cells).Value = number
' Or, if you're going to be passing in something more complex later...
Range(cells).FormulaR1C1 = number

be more appropriate?

I'd also be very wary of using Range("...") without specifying which sheet you are referring to. This will act on whichever is the active sheet and can thus cause unexpected problems, almost always prefer SheetX.Range("..."). Similarly for using .Select, it's unnecessary, and can only cause problems for you in the future.

Related questions

Excel VBA calling sub from another sub with multiple inputs, outputs of different sizes

I would like to call a sub from another sub inside in the same module. The first sub would be my main code and there I would call the second subroutine. Second subroutine receives multiple inputs as integer, double, double …

Read More
Redim Preserve gives 'subscript out of range'

I want to Redim Preserve an array I keep getting the error 'subscript out of range'. I am aware of the fact that only the size of the last dimension can be changed. That is exactly what I am doing. …

Read More
Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More