I just figured out that setting optional parameters requires "Call" infront of the method.
Public Sub Test()
Call abc("aaa")
Call abc("aaa", 2)
abc("aaa") ' is fine
abc("aaa", 2) ' is a syntax error
End Sub
Function abc(a As String, Optional iCol As Long = 3)
MsgBox (iCol)
End Function
Can you add a "why does this make sense?" to my new information?
Greetings, Peter
Edit: PS the function abc for no other use than to simplify the question.
Call is an optional keyword, but the one caveat is that if you use it you must include the parentheses around the arguments, but if you omit it you must not include the parentheses.
Quote from MSDN:
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.
Link: https://msdn.microsoft.com/en-us/library/office/gg251710.aspx
This means that the following syntaxes are allowed:
Call abc("aaa")
Call abc("aaa", 2)
abc "aaa", 2
abc("aaa") ' <- Parantheses here do not create an argument list
abc(((("aaa")))) ' <- Parantheses here do not create an argument list
The following syntaxes are not allowed:
Call abc "aaa", 2
abc("aaa", 2) ' <- Parantheses here create an argument list
This doesn't take effect when using a function to get a return value, for example if you were to do the following you need parentheses:
Function abc(a As String, Optional iCol As Long = 3)
abc = iCol
End Function
'## IMMEDIATE WINDOW ##
?abc("aaa", 2) 'this works
?abc "aaa, 2 'this will not work
?Call abc "aaa", 2 'this will not work
?Call abc("aaa", 2) 'this will not work
If you are using Call on a Function then consider changing it to a Sub instead, functions are meant to return a value as in the cases above.
Something like this would work:
Option Explicit
Public Sub Test()
Dim strText As String
strText = "E"
Call Abc(strText, 3)
Call Abc(strText, 2)
Abc (strText)
' Abc (strtext,5)
Abc strText, 2
Abc strText
End Sub
Public Sub Abc(strText As String, Optional iCol As Long = 5)
Debug.Print iCol
End Sub
Absolutely no idea why the commented code does not work...
Call is an optional keyword, as already described in detailed in the answer above.
for your second option
abc("aaa", 2) ' is a syntax error
Just use:
abc "aaa", 2
Note: there is little use to have a Function if you don't return anything, you could have a regular Sub instead.
to have a Function that return a String for instance (just something made-up quick):
Function abc(a As String, Optional iCol As Long = 3) As String
abc = a & CStr(iCol)
End Function
and then call it:
Public Sub Test()
d = abc("aaa", 2)
MsgBox d
End Sub
