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?

Sacksen

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.

Washboard

Although I defined another subroutine: Sub EnterCellValue(cells As range) range(cells).Select ActiveCell.FormulaR1C1 = "M" End Sub and called it like this: EnterCellValue ("N23:Q23") and had no problems. This is irritating. – Sacksen

Answer 2

You actually have 2 issues.

First the actual answer to your question. You need to say:

Call EnterCellValueMonthNumber("N23:Q23",1)

But that still wont work if you run it, since you have set cells of type range... set it as type string using this instead:

Sub EnterCellValueMonthNumber(cells As String, number As Integer)
    Range(cells).Select
    ActiveCell.FormulaR1C1 = number
End Sub

Infecund

Answer 3

Try the following code:

Sub EnterCellValueMonthNumber(ByVal cells As String, number As Integer)

    Range(cells).Select

    ActiveCell.FormulaR1C1 = number

End Sub

Camembert