excel vba call subroutine with variables

Question

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 1

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.