excel vba调用具有变量的子程序 [英] excel vba call subroutine with variables

问题描述

我定义了以下子程序：

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)

我收到以下错误消息：

Compile error Expected: =

我不知道为什么我得到这个信息。有没有人知道我失踪了？

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

推荐答案

你可以把这个sub称为

You would call the sub as

EnterCellValueMonthNumber "N23:Q23", 1

没有括号或者

Call EnterCellValueMonthNumber("N23:Q23", 1)

支架和之前调用

此外，您的Sub期待作为第一个参数的Range对象，并提供一个字符串;您应该将子签名更改为：

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)

此外，我不确定您正在尝试使用此代码实现什么，因为它只会设置左上角单元格范围为1.会像

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

更合适？

我也非常警惕使用 Range（...）而不指定您指的是哪张表。这将作用于活动工作表中的任何一个，从而导致意外的问题，几乎总是喜欢 SheetX.Range（...）。类似地，使用。选择，这是没有必要的，只能在将来导致您的问题。

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.

这篇关于excel vba调用具有变量的子程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！