excel vba调用带有变量的子例程
收藏

我定义了以下子例程:

Sub EnterCellValueMonthNumber(cells As range, number As Integer)

range(cells).Select
ActiveCell.FormulaR1C1 = number

End Sub

当我这样调用子例程时:

EnterCellValueMonthNumber ("N23:Q23",1)

我收到以下错误消息:

Compile error Expected: =

我不知道为什么收到此消息。有人知道我在想什么吗?

最佳答案

您可以将sub称为

EnterCellValueMonthNumber "N23:Q23", 1

没有括号。要么

Call EnterCellValueMonthNumber("N23:Q23", 1)

Brackets, and Call before it.

同样,您的Sub期望将Range对象作为第一个参数,并且您要提供一个字符串;您应该将子签名更改为:

Sub EnterCellValueMonthNumber(cells As String, number As Integer)

另外,我不确定您要使用此代码实现什么,因为它只会将范围的左上角单元格设置为1。

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

更合适?

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.

    公众号
    关注公众号订阅更多技术干货!