Search code examples
vbasyntaxsyntax-errornamed-parameterscolon-equals

Meaning of `:=` Syntax in VBA methods

When writing the following VBA, what is the root cause of the error "Expected =" given that we are using the Format:=2.

Workbook.Open (filename, Format:=2)

I understand that this format works when setting the variable as in the following code, but why does it work here and not in the above format?

Set wrkb = Workbook.Open (filename, Format:=2)

Also what is this operator called, := and how is it used?

Solution

  • It's not an operator, it's a named argument.

    You can chose the order of the arguments/parameters by directly specifying what they are.

    The concept of named arguments also exists in multiple modern languages, such as c# (its optional, just like in VBA) and swift (by default it's required, but you can disable it).

    Named arguments also allow you to omit arguments that are optional altogether, but pass an argument that is further back in the list of arguments. A good way to try named arguments out is the messagebox, since it has many optional arguments with default values.

    Example: MsgBox only specifying title:

    MsgBox Title:="wew lad"

    Or, in the more modern way of writing vb(a) code:

    Call MsgBox(Title:="wew lad")

    The MsgBox is a good example, since you can call it normally, and then specify a parameter further back directly (works with other methods too):

     Call MsgBox("Yes!", Title:="wew lad")

    Once there are named parameters, you can't add ordered parameters after:

    'Call MsgBox (Prompt:="Yes!", vbOkCancel  Title:="wew lad")
'this doesnt work!

    Now, why does this raise an error:

    MsgBox ("Hello", Title:="test")

    This is some of the weirder parts of vba. Calling functions with return values but ignoring the value while using parentheses is a bit broken.

    You can circumvent this by adding a Call in front of it (vba then knows it can ignore the result).

    This would look like this:

    Call MsgBox ("Hello", Title:="test")

    Honestly, I don't know why this is, but I've had it cause really weird bugs. When you use the parentheses syntax, I can really recommend using the Call keyword before the method call.

    As Macro Man mentioned, you can also omit the parentheses, instead of using Call:

    MsgBox "Hello", Title:="test"

    And it will work, too. This was the original vba coding style and isn't really used anymore.