vba 带有变量的excel vba调用子程序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6252287/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
excel vba call subroutine with variables
提问by user366121
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?
我不知道为什么我会收到这条消息。有谁知道我错过了什么？
回答by Simon Cowen
You would call the sub as
你会称子为
EnterCellValueMonthNumber "N23:Q23", 1
No brackets. Or
没有括号。或者
Call EnterCellValueMonthNumber("N23:Q23", 1)
Brackets, and Callbefore it.
括号，Call在它之前。
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 需要一个 Range 对象作为第一个参数，而您提供的是一个字符串；您应该将 sub 的签名更改为：
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
另外，我不确定您要使用此代码实现什么，因为它只会将范围的左上角单元格设置为 1。会像
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.
我也会非常谨慎地使用Range("...")而不指定您所指的工作表。这将作用于活动表中的任何一个，因此可能会导致意外问题，几乎总是首选SheetX.Range("..."). 同样，使用.Select，它是不必要的，并且只会在将来给您带来问题。
回答by Hari Seldon
You actually have 2 issues.
你实际上有2个问题。
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 cellsof type range... set it as type stringusing this instead:
但是，如果您运行它，那仍然无效，因为您已经设置cells了类型range...string使用以下方式将其设置为类型：
Sub EnterCellValueMonthNumber(cells As String, number As Integer)
Range(cells).Select
ActiveCell.FormulaR1C1 = number
End Sub
回答by Ramakrishnan N
Try the following code:
试试下面的代码：
Sub EnterCellValueMonthNumber(ByVal cells As String, number As Integer)
Range(cells).Select
ActiveCell.FormulaR1C1 = number
End Sub
