如何让 VBA 子例程调用将数组传递给子例程中的另一个函数的函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13868045/
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
How to get VBA Subroutine to call a function that passes an array to another function within the subroutine
提问by user1902208
I am trying to get my VBA subroutine to run a function that creates an array and then passes that array to another function which further processes those array items. I have made an example that is extremely simple and functions exactly like my actual code. it is as follows:
我试图让我的 VBA 子例程运行一个创建数组的函数，然后将该数组传递给另一个进一步处理这些数组项的函数。我做了一个非常简单的例子，它的功能与我的实际代码完全一样。如下：
Sub SubRoutine()
ProcessArray CreateArray
End Sub
Function ProcessArray(Arr() As Variant) As Variant
End Function
Function CreateArray() As Variant
Dim Array1(1 To 4) As Variant
CreateArray = Array1
End Function
It's just the two functions and the subroutine which calls those two functions. The compiler refuses to compile my code and explains to me that:
它只是两个函数和调用这两个函数的子程序。编译器拒绝编译我的代码并向我解释：
Compile error:
编译错误：
Type mismatch: array or user-defined type expected
类型不匹配：需要数组或用户定义的类型
To which I can only say that everything is the same data type and the argument passed is indeed an array. Just in case you were wondering, yes I have tried with an array that has had data allocated to it.
我只能说一切都是相同的数据类型，传递的参数确实是一个数组。以防万一你想知道，是的，我试过一个已经分配了数据的数组。
I know it's something subtle I am missing in VBA syntax but I have not seen an example like this. Any insight would be greatly appreciated.
我知道这是我在 VBA 语法中遗漏的一些微妙的东西，但我还没有看到这样的例子。任何见解将不胜感激。
回答by Daniel
Change this line:Function ProcessArray(Arr() As Variant) As Variant
to this:Function ProcessArray(Arr As Variant) As Variant
将此行更改Function ProcessArray(Arr() As Variant) As Variant
为：Function ProcessArray(Arr As Variant) As Variant
This way your function will now accept a Variant that contains an array, instead of looking for an array of Variants. As you said, a subtle but significant difference.
这样，您的函数现在将接受包含数组的 Variant，而不是查找 Variant 数组。正如你所说，一个微妙但显着的差异。
回答by Mr_Moneybags
I think what still wasn't answered is why
1. MySub(MyArg)
works just fine, but
2. MyOtherSub(MyArg1, MyArg2)
does not
我认为仍然没有回答的是为什么
1.MySub(MyArg)
工作得很好，但
2.MyOtherSub(MyArg1, MyArg2)
没有
This blog entry explains it well.
Essentially, you can pass an argument which would normally be byref as byval:
Call MySub(Arg1, (Arg2)) 'Arg1 is passed byref, Arg2 is passed byval
本质上，您可以将通常是 byref 的参数作为 byval 传递：
Call MySub(Arg1, (Arg2)) 'Arg1 is passed byref, Arg2 is passed byval
Code #1. works because VBA thinks you are passing one argument byVal so it doesnt count as parentheses being used in a Subcall without the Callkeyword. As mentioned in other answers, no parentheses are allowed in a Subcall without the Callkeyword.
代码#1。之所以有效，是因为 VBA 认为您正在传递一个参数 byVal，因此它不算作在Sub没有Call关键字的调用中使用的括号。如其他答案中所述，在Sub没有Call关键字的调用中不允许使用括号。
The Callkeyword requires Params in parentheses. So with the Callkeyword Call MySub(MyArg)would actually pass MyArgbyRef because the parentheses are used as the enclosure for arguments.
该Call关键字要求PARAMS在括号中。所以使用Call关键字Call MySub(MyArg)实际上会传递MyArgbyRef 因为括号用作参数的外壳。
Code #2 does not work because there is no valid way to justify the parentheses in the syntax (it is only allowed with the Callkeyword.
代码 #2 不起作用，因为没有有效的方法来证明语法中的括号（它只允许与Call关键字一起使用。
