如何在Excel VBA（2016）中将范围obj变量传递给子项 [英] How do I pass a range obj variable to a sub in Excel VBA (2016)

问题描述

给定以下代码：
我似乎没有成功地将范围对象变量从一个子功能传递给另一个子功能。我花了一整天的时间研究，并在我吞下自豪之前进行了实验，并来到这里。

请阅读以下意见，并回覆您对于最后两行不会表现的原因。

  Public Sub doSomethingToRows（ROI As Range）
 *'做某些与提供的范围内的单元格值* 
 
 End Sub 
'
公共子testDoAltRows（）
 
 Dim RegionOfInterest As Range'这是一个对象吗？ 
 
'*以下产出：类不支持自动化（错误430）* 
'*设置RegionOfInterest =新工作表'这只是给出错误* 
 
设置RegionOfInterest = Worksheets（Sheet1）。Range（A1）
 RegionOfInterest.Value = 1234.56'*可以更新单元格A1 * 
 
设置RegionOfInterest = Worksheets（Sheet1 ）.Range（B5：D15）
 RegionOfInterest.Columns（2）.Value =~~~~~~'* okay * 
 
'doSomethingToRows（RegionOfInterest）'为什么我得到OBJECT IS REQUIRED错误吗？ 
 doSomethingToRows（Worksheet（Sheet1）。Range（B5：C15））'但是这样执行好了
 End Sub 
  

解决方案

从 msdn Call关键字语句的文档，

备注

调用过程时，不需要使用Call关键字。
但是，如果使用Call关键字来调用需要
参数的过程，则必须将参数列表括在括号中。 如果您省略
的呼叫关键字，您还必须省略
argumentlist 附近的括号。如果您使用调用语法来调用任何内在函数或
用户定义的函数，函数的返回值将被丢弃。

将整个数组传递给一个过程，使用数组名称后跟
空括号。

从实际的角度来看，即使Subs可以调用或没有Call关键字，选择一种方式并坚持使用它作为编码风格的一部分是有道理的。我同意共产国际 - 根据对现代VBA代码的观察，我认为使用Call关键字应被视为不赞成。相反，在参数列表周围调用Subs没有括号。

现在，重要问题的答案是：

以下列子程序为例：

  Public Sub ShowSum（arg1 As Long，arg2 As Long）
 MsgBox arg1 + arg2 
 End Sub 
  pre> 
 
 
我们已经确定，如果不使用 Call 关键字，则必须像这样调用Subs： p> 
 
 
 ShowSum 45,37 
 
 
 
如果它像codeShowSum（45， 37）？那么你甚至不能编译，因为VBA马上抱怨Expected =。这是因为VBA解析器看到括号，并决定这必须是 函数 调用，因此期望您处理返回值使用=赋值语句。
 
 
 
只有一个参数的Sub怎么办？例如：
  Public Sub ShowNum（arg1 As Long）
 MsgBox arg1 
 End Sub 
  
调用此子的正确方法是 ShowNum 45 。但是，如果您将这个输入到VBA IDE中，该怎么办？ ShowNum（45）？一旦你将光标移开线，你就会注意到VBA在子名和开始的括号之间增加了一个空格，给出了关于代码行实际被解释的关键线索：
  ShowNum（45）
  
 
 
&P VBA没有处理这些括号，就像它们包围参数列表一样，而是将它们视为分组括号。最重要的是，这并不重要，但是对于具有默认成员的对象来说，这是对的。
 
 
 
要查看原因导致的问题，请尝试运行以下：
  Dim v As Variant 
设置v =范围（A1）
设置v = （范围（A1））'< ---类型不匹配
  
请注意，在标记行上出现类型不匹配。现在将这两个语句添加到监视窗口，并查看类型列：
  + ------- ------ + ----- + -------------- + 
 |表达式|值|类型| 
 + ------------- + ----- + -------------- + 
 |范围（A1 ）| |对象/范围| 
 |（Range（A1））| |株/串| 
 + ------------- + ----- + -------------- + 
  
当您使用分组括号围绕对象时，会对其默认属性进行评估 - 在Range对象的情况下，它是Value属性。所以这真的只是一个巧合，VBA允许你摆脱把括号放在参数列表 - 真的，VBA只是将它解释为分组括号，并相应地计算值。您可以通过在Sub上使用多个参数来尝试相同的东西，在VBA中无效的子参数用参数列表括号括起来。
 
 
 
 @PaulG 
 
 
 
尝试这样：
  Public Sub Main（）
调试.Print TypeName（Range（A1））
 Debug.Print TypeName（（Range（A1）））
 End Sub 
  
 
Given the following code:
I can not seem to successfully pass a Range Object Variable from one sub-function to another. I spent an entire day researching, and experimenting before I swallowed pride and came here. 

Please read the comments below, and reply with any ideas you have regarding why the LAST two lines will not behave. 
Public Sub doSomethingToRows(ROI As Range)
*'do Something with the cell values within the supplied range*

End Sub
'
Public Sub testDoAltRows()

    Dim RegionOfInterest As Range       'is this an object or not?

    '*The following yields: Class doesn't support Automation (Error 430)*
    '*Set RegionOfInterest = New Worksheet 'this just gives an error*

    Set RegionOfInterest = Worksheets("Sheet1").Range("A1")
    RegionOfInterest.Value = 1234.56        '*okay, updates cell A1*

    Set RegionOfInterest = Worksheets("Sheet1").Range("B5:D15")
    RegionOfInterest.Columns(2).Value = "~~~~~~"    '*okay*

    'doSomethingToRows (RegionOfInterest)   'why do I get "OBJECT IS REQUIRED" error?
    doSomethingToRows (Worksheets("Sheet1").Range("B5:C15")) 'but this executes okay
End Sub

解决方案
From the msdn documentation of the Call keyword statement,

  
Remarks 
  
  
You are not required to use the Call keyword when calling a procedure.
  However, if you use the Call keyword to call a procedure that requires
  arguments, argumentlist must be enclosed in parentheses. If you omit
  the Call keyword, you also must omit the parentheses around
  argumentlist. If you use either Call syntax to call any intrinsic or
  user-defined function, the function's return value is discarded.
  
  
To pass a whole array to a procedure, use the array name followed by
  empty parentheses.
From a practical standpoint, even though Subs can be called with or without the "Call" keyword, it makes sense to pick one way and stick with it as part of your coding style. I agree with Comintern - it is my opinion, based on observation of modern VBA code, that using the "Call" keyword should be considered deprecated. Instead, invoke Subs without parenthesis around the argument list.

And now the answer to the important question: 

Why does your code throw an error?

Take for example the following Subroutine:
Public Sub ShowSum(arg1 As Long, arg2 As Long)
    MsgBox arg1 + arg2
End Sub
We have established that, if not using the Call keyword, Subs must be invoked like so:

ShowSum 45, 37

What happens if it were instead called like ShowSum(45, 37)? Well, you wouldn't even be able to compile as VBA immediately complains "Expected =". This is because the VBA parser sees the parenthesis and decides that this must be a Function call, and it therefore expects you to be handling the return value with an "=" assignment statement.

What about a Sub with only one argument? For example:
Public Sub ShowNum(arg1 As Long)
    MsgBox arg1
End Sub
The correct way to call this Sub is ShowNum 45. But what if you typed this into the VBA IDE: ShowNum(45)? As soon as you move the cursor off of the line, you'll notice that VBA adds a space between the Sub name and the opening parenthesis, giving you a crucial clue as to how the line of code is actually being interpreted:
ShowNum (45)
VBA is not treating those parenthesis as if they surrounded the argument list - it is instead treating them as grouping parenthesis. MOST of the time, this wouldn't matter, but it does in the case of Objects which have a default member.

To see the problem this causes, try running the following:
Dim v As Variant
Set v = Range("A1")
Set v = (Range("A1"))  '<--- type mismatch here
Notice that you get a "Type Mismatch" on the marked line. Now add those two statements to the watch window and look at the "Type" column:
+-------------+-----+--------------+
| Expression  |Value|     Type     |
+-------------+-----+--------------+
|Range("A1")  |     |Object/Range  |
|(Range("A1"))|     |Variant/String|
+-------------+-----+--------------+
When you surround an Object with grouping parenthesis, its default property is evaluated - in the case of the Range object, it is the Value property.

So it's really just a coincidence that VBA allowed you to get away with "putting parenthesis around the argumentlist" - really, VBA just interprets this as grouping parenthesis and evaluates the value accordingly. You can see by trying the same thing on a Sub with multiple parameters that it is invalid in VBA to invoke a Sub with parenthesis around the argument list.

@PaulG

Try this:
Public Sub Main()
    Debug.Print TypeName(Range("A1"))
    Debug.Print TypeName((Range("A1")))
End Sub


                        
这篇关于如何在Excel VBA（2016）中将范围obj变量传递给子项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！