One Kinter-Garden Question

[prabhafriend]

Whenever I see the syntax of a function it is called with the brackets everywhere, But why it don’t allow us to use the brackets when don’t use the “call” keyword or an “=”. I just want to know what it right syntax to call function?

[MaximS]

Some of the function doesn't need bracets if they does not require any parameters to work ie. function Date.

Functions can be called in various ways:
- 'Call Function(param1, param2, ....)'
- or used as variable ie. 'If Function(param1, ....) = True Then'

[Paul_Hossler]

Dispite what is says in Help



Call Optional; keyword. If specified, you must enclose argumentlist in parentheses.


and



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.


What I came up with for the different combanations :



Sub Test()

Params0
Call Params0

Params1 (100)
Params1 150
Call Params1(200)

'Params2(2, 3) Does not work
Call Params2(2, 3)
Params2 x:=4, y:=5
Params2 6, 7

End Sub

Sub Params0()
MsgBox "Zero"
End Sub
Sub Params1(x As Long)
MsgBox "One -- " & x
End Sub
Sub Params2(x As Long, y As Long)
MsgBox "Two -- " & (x * y)
End Sub


Just one of those Excel Mysteries

Paul

[Bob Phillips]

Be very very careful.

If you use



myMacro (some_parameter)


some-parameter is evaluated before being passed to the macro, whereas in



myMacro some_parameter


it is not, so they are behaving differently. I have yet to see a case that warrants the former approach.

[Paul_Hossler]

I believe that the ( ) are for variable type coersion, in that they override (user beware !!!!) the variable type.

At Note #1 below, the Variant/Date gets coersed into a String, but at Note #2, the Variant/Date remains a Date type


Option Explicit
Sub XLD_1()

Dim L As Long
Dim S As String
Dim V As Variant

L = 1234
S = "ABCD"
V = #1/2/2010#
'these are OK
Call LowerLevelStr(S)
Call LowerLevelLong(L)
Call LowerLevelVar(V)

'these also
LowerLevelStr (S)
LowerLevelLong (L)
LowerLevelVar (V)

'these get Compile time ByRef Argument Type mis-match error, as expected
'Call LowerLevelStr(L)
'Call LowerLevelLong(S)

'the ( ) are Type Coersion operators

'The Long can be coersed into a string,
LowerLevelStr (L)

'and the Date coersed into a string, Note #1
' Vartype returned = 8
LowerLevelStr (V)

'the same variable passed to a sub expecting a Variant does not do the type coersion
' Vartype returned = 7, Note #2
LowerLevelVar (V)


'!!!!but the String can not be coersed into a Long
' and fails at run time but is 'acceptable' at Compile
LowerLevelLong (S)
End Sub


'vbLong 3 Long integer
'vbDate 7 Date value
'vbString 8 String

Sub LowerLevelStr(X As String)
MsgBox X & " -- Vartype " & VarType(X)
End Sub

Sub LowerLevelLong(X As Long)
MsgBox X & " -- Vartype " & VarType(X)
End Sub

Sub LowerLevelVar(X As Variant)
MsgBox X & " -- Vartype " & VarType(X)
End Sub


I have used ( ) occasionally (and as you said, VERY CAREFULLY) to type cast a variable into another type

Paul

[Bob Phillips]

I am not referring to casting variables, but erroneous (seeming) results.

Anything in parentheses is treated as an expressions to be evaluated (even if it is not really an expression, such as a variable name). The problem comes with arguments that are passed ByRef. A brackets-encased argument will pass the memory address of the temporary memory location used to evaluate that expression and that is what the subroutine will use to write back to. This means that the original variable which was supposed to be
updated by the subroutine will not be (no error will be generated, but your results will be incorrect).

[Paul_Hossler]

Ah -- got it. Don't understand (now) why anyone would need / want / use it, but I see what you mean


Sub WithByRef(ByRef X As Long)
X = 2 * X
End Sub

Sub drv()
Dim n As Long

n = 100

MsgBox n
WithByRef n
MsgBox n

'Ah ha -- now I understand
MsgBox n
WithByRef (n)
MsgBox n

MsgBox n
Call WithByRef(n)
MsgBox n
End Sub


Paul

[Bob Phillips]

Exactly Paul, as I said earlier ... I have yet to see a case that warrants the former approach.