vba VBA多参数函数调用语法错误

VBA multi-parameter function call Syntax Error

提问by Zac B

I'm trying to call a function in Excel VBA (2007), and am recieving a syntax error on the call. I have an array of data structures called ImportSets, which contains worksheets and strings, and am trying to pass members of items in that array to a function, called Import.

我正在尝试在 Excel VBA (2007) 中调用函数，并且在调用时收到语法错误。我有一个名为 ImportSets 的数据结构数组，其中包含工作表和字符串，我正在尝试将该数组中项目的成员传递给名为 Import 的函数。

The calling code looks like this:

调用代码如下所示：

For n = 1 To 7  
    Debug.Print ("Destsheet: " & ImportSets(n).DestSheet.name)  
    Debug.Print ("Sourcesheet: " & ImportSets(n).SourceSheet.name)  
    Debug.Print ("Sourcecolumn: " & ImportSets(n).SourceColumn)  
    Import(CostAnalysisWorksheet.Sheets("Reimbursements"), ImportSets(n).DestSheet, ImportSets(n).SourceSheet, ImportSets(n).SourceColumn)  
Next n  

All of the Debug.Print statements return meaningful and correct strings, and check for the existence of "Reimbursements" returns true. The method call is on one line. Here is the ImportSet object code:

所有 Debug.Print 语句都返回有意义且正确的字符串，检查“Reimbursements”是否存在返回 true。方法调用在一行上。这是 ImportSet 对象代码：

Public Type ImportSet
    DestSheet As Worksheet
    SourceSheet As Worksheet
    SourceColumn As String
    ...other code...
End Type

The function body looks like this:

函数体如下所示：

Function Import(ByRef ReimbursementSheet As Worksheet, ByRef DestSheet As Worksheet, ByRef ImportSheet As Worksheet, ByRef ImportSheetPriceColumn As String) As String  
    ....code here .....  
End Function

I am getting a red-highlighted syntax error on the function call (in the first snippet). I am probably missing something stupid. What is it?

我在函数调用（在第一个代码段中）收到一个红色突出显示的语法错误。我可能错过了一些愚蠢的东西。它是什么？

回答by nemmy

I haven't used VBA in Excel 2007 but older versions only allow you to put brackets around function call parameters if you're assigning the return value to a variable. Try this:

我没有在 Excel 2007 中使用过 VBA，但是如果您将返回值分配给变量，旧版本只允许您在函数调用参数周围放置括号。尝试这个：

Import CostAnalysisWorksheet.Sheets("Reimbursements"), ImportSets(n).DestSheet, ImportSets(n).SourceSheet, ImportSets(n).SourceColumn

回答by osknows

The important point is how you want the function to return the value and whether your passing variables ByVal or ByRef. ByRef allows the function to change the variable ByVal means the function cannot change the variable.

重要的一点是您希望函数如何返回值以及您传递的变量是 ByVal 还是 ByRef。ByRef 允许函数改变变量 ByVal 意味着函数不能改变变量。

These 2 examples essentially do the same thing but note the subtlety in manipulating a variable ByRef and returning a variable from a function.

这两个示例基本上做同样的事情，但要注意操作变量 ByRef 和从函数返回变量的微妙之处。

Sub test()
Dim lngX As Long, lngY As Long, Product As Long

   lngY = 10
   lngX = 5
   Product = multiply(lngX, lngY)
   MsgBox (Product)
End Sub

Function multiply(ByVal lngX As Long, ByVal lngY As Long) As Long
   multiply = lngY * lngX
End Function

or alternatively pass the variables ByRef and manipulate with the function

或者传递变量 ByRef 并使用函数进行操作

Sub test()
Dim lngX As Long, lngY As Long, Product As Long

   lngY = 10
   lngX = 5
   Product = 0
   multiply lngX, lngY, Product
   MsgBox (Product)

End Sub

Function multiply(ByVal lngX As Long, ByVal lngY As Long, ByRef Product As Long)
  Product = lngY * lngX
End Function

This example is quite trivial but often an object, array etc may need passed to a function for processing ByRef rather than to provide an answer ByVal

这个例子很简单，但通常一个对象、数组等可能需要传递给一个函数来处理 ByRef 而不是提供一个答案 ByVal

回答by Mathieu Guindon

This Q&A is being used as a duplicate target, but none of the answers are telling the whole story.

此问答被用作重复目标，但没有一个答案能说明全部情况。

First, this behavior has nothing to do with the version of Excel or whatever the host application is: it's just standard VBA syntax, and the rules have been been the same for well over 20 years now - JavaScriptand Perlhave their respective kinks as well, as does every single programming language ever made.

首先，这种行为与 Excel 的版本或宿主应用程序的任何内容无关：它只是标准的 VBA 语法，并且规则已经相同了 20 多年——JavaScript和Perl也有各自的问题，就像曾经做过的每一种编程语言一样。

When the parentheses are delimiting an argument list, the VBE puts the opening parenthesis immediately after the invoked function:

当括号分隔参数列表时，VBE 会在调用的函数之后立即放置左括号：

foo = MsgBox("test")
           ^^^

When the parentheses are interpreted as part of the first argument(i.e. a parenthesized expression), the VBE puts a space between the invoked procedure and its argument list:

当括号被解释为第一个参数的一部分（即带括号的表达式）时，VBE 在调用的过程和它的参数列表之间放置一个空格：

MsgBox ("test")
     ^^^

This code wouldn't compile:

这段代码不会编译：

MsgBox ("test", vbInformation)
     ^^^

Because the entire parenthesized expression is the first argument, and there's no way ("test", vbInformation)can be evaluated as a value - it's a syntax error, like in the OP.

因为整个带括号的表达式是第一个参数，并且("test", vbInformation)无法将其评估为值 - 这是一个语法错误，就像在 OP 中一样。

If the expression canbe evaluated as a value, this value is then passed by value(ByVal) regardless of the invoked procedure's signaturespecifying that parameter as ByRef- see 5.3.1.11 Procedure Invocation Argument Processing, runtime semantics:

如果表达式可以作为一个值计算，那么这个值将通过值( ByVal)传递，而不管被调用过程的签名如何指定该参数ByRef- 参见5.3.1.11 过程调用参数处理，运行时语义：

• If the parameter has no argument mapped to it, the parameter is ByVal, or the parameter is ByRef and the mapped argument's expression is classified as a value, function, property or unbound member, a local variable is defined with procedure extent within the procedure being invoked with the same name value and declared type as the parameter [...]

• 如果参数没有映射到它的参数，参数是 ByVal，或者参数是 ByRef 并且映射参数的表达式被归类为值、函数、属性或未绑定成员，则在过程中定义一个局部变量，过程范围是使用与参数相同的名称值和声明类型调用 [...]

The solution is, as others concluded, to drop the parentheses when making a procedure call:

正如其他人总结的那样，解决方案是在进行过程调用时去掉括号：

MsgBox "test", vbInformation

...or to consistentlyuse the obsolete explicit call syntax:

...或始终使用过时的显式调用语法：

Call MsgBox("test", vbInformation)

Parentheses are only needed when making a function call(i.e. when capturing the return value into a local variable):

只有在进行函数调用时才需要括号（即，将返回值捕获到局部变量中时）：

Dim result As vbMsgBoxResult
result = MsgBox("test", vbInformation Or vbOkCancel)