Predict The Output

[Colin Legg]

I kinda cheated because I've seen the thread that prompted Aaron to start his one, so I had a wee heads-up on it ....

 

[Greg Truby]

OK, so anybody got a link to someplace explains why the parenthesis syntax does not pass by reference? Totally caught me off guard.

 

[ZVI]

I could try to explain, or should wait for one more dozen of mistaken?

 

[Norie]

I actually didn't think the code would even compile.

But what do I know.

 

[RoryA]

There's an MSDN blog here. Usually gets people when they are passing an object.

 

[ZVI]

So I'm not the first who has canceled an intrigue, but Rory and MS have made it. Here is my explanation:

<font face=Courier New>
' Could be declared as Sub Y(ByRef X) as well.
Sub Y(X)
  X = 5
End Sub

Sub X()
  
  Dim A As Long, B As Long
  A = 1
  B = 2
      
  ' Reference of A variable is passed, A is changeable by Sub Y()
  Y A
   
  ' Reference of VBA internal run-time variable referenced to result of A+B is passed
  ' Neither A nor B is changeable by Sub Y()
  Y (A + B)
  
  ' Reference of VBA internal run-time variable referenced to result of A+B is passed.
  ' Variable A is not passed therefore it is not changeable by Sub Y()
  Y (A + 7)
  
  ' The same as above with replacing of 7 by 0 (zero)
  ' Reference of VBA internal run-time variable equal to result of A+0 is passed.
  ' Variable A is not changeable by Sub Y because expression into () brackets
  ' means auto creation in memory of VBA temporal internal run-time variable with
  ' reference to value of evaluated expression includes into () brackets.
  ' No special VBA-parsing of expression into the brackets is provided
  ' irrespective of it includes one or more variables or constants.
  Y (A)
   
  ' But Call syntax means passing expression in brackets as reference.
  ' Reference of A variable is passed, A is changeable by Sub Y
  Call Y(A)
  
  ' Reference of VBA internal run-time variable equal to result of A+B is passed.
  ' Neither A nor B is changeable by Sub Y as it not passed
  Call Y(A + B)
  
End Sub
</FONT>

 

[ZVI]

There is another and easier one, but who knows?

<font face=Courier New>
' It could be easy done by [A3]=Day(Date)
' But could you fix .Evaluate("=DAY(" & x & ")") ?
' BTW, I know how it could be fixed :)
Sub WhatIsWrong()
  Dim x
  x = Date
  [A1] = x
  
  With Application
    
    ' Correct
    [A2] = .Evaluate("=DAY(A1)")
    
    ' Wrong
    [A3] = .Evaluate("=DAY(" & [A1] & ")")
    [A4] = .Evaluate("=DAY(" & x & ")")
    Debug.Print .Evaluate("=DAY(" & x & ")")
    
    ' How to fix it?
    [A4] = .Evaluate("=DAY(" & ??? & ")")
    
  End With
  
End Sub
</FONT>

Regards,
Vladimir

 

[Patience]

I am beginning to think maybe me getting one out of three was more luck than judgement...

 

[hatman]

Hmm, I see I wasn't the only one who got the value of "i" wrong. To be honest, I have gotten in the habit of using the Call statement in these cases (mostly). The very nature of the Call statement syntax forces the use of parentheses, resulting in the expected output. I'm not sure WHY I started using Call several years ago... I wonder now if I didn't get annoyed with the "unpredictable" results outlined by Oorang's code.

 

[Norie]

hatman

I don't think using Call is a bad idea.

In fact it might be a good idea.

Obviously you can run other subs just simply using there name, dependent on how their located and named/declared.

But people reading the code might be thinking - is it a variable, is it a sub, is it a function, is it an aardvark, can it fly.