I was posting on one of the online forums a while back and helped a user understand some code which he had found on the web and, in particular, how it restricted what could be typed into a textbox on a userform. The code looked like this:
Private Sub TextBox1_KeyPress( _ ByVal KeyAscii As MSForms.ReturnInteger) Select Case KeyAscii Case vbKey0 To vbKey9, vbKeyBack 'OK Case Else KeyAscii = 0 Beep End Select End Sub
The question revolved around the highlighted line of code. The user thought that, in theory, that line of code should do nothing because:
KeyAscii
isn’t used at a later point in the procedure.KeyAscii
is passedByVal
, so the change won’t propogate back to the caller of the event handler (incorrect).
However, he knew from testing that, if he removed that line of code, the restricted entry was no longer enforced. The code clearly works – but how?
The most important point to this answer is that MSForms.ReturnInteger
is not a literal: it looks like an Integer
because it has Integer
in the name – but it isn’t one. If you look in the object browser you’ll see that it is a class and it has a default Value
property.
I think things become clearer if we re-write the code so that it explicitly uses that Value
property:
Private Sub TextBox1_KeyPress( _ ByVal KeyAscii As MSForms.ReturnInteger) Select Case KeyAscii.Value Case vbKey0 To vbKey9, vbKeyBack 'OK Case Else KeyAscii.Value = 0 Beep End Select End Sub
This code is exactly the same but now KeyAscii
looks more like an object (an instance of a class). The question now becomes: what happens when an object is passed ByVal
? Let’s do a test with our own custom class:
Class1
Option Explicit Private mlngMyValue As Long Public Property Let MyValue(value As Long) mlngMyValue = value End Property Public Property Get MyValue() As Long MyValue = mlngMyValue End Property
Module1
Option Explicit Sub test() Dim c1 As Class1 Set c1 = New Class1 c1.MyValue = 1 Debug.Print c1.MyValue 'gives 1 TryToChangeProperty c1 Debug.Print c1.MyValue 'gives 2 End Sub Sub TryToChangeProperty(ByVal c As Class1) c.MyValue = 2 End Sub
That’s right. As Chip explains on his site, when an object is passed ByVal
, we can’t change the reference to the object itself upstream – but we can change its properties. If it were passed ByRef
then we’d be able to change the object reference itself:
Module2
Option Explicit Sub test2() Dim c1 As Class1 Set c1 = New Class1 c1.MyValue = 1 Debug.Print c1.MyValue 'gives 1 TryToChangeValue c1 Debug.Print c1.MyValue 'gives 2 TryToChangeObjectByVal c1 Debug.Print c1.MyValue 'gives 2 TryToChangeObjectByRef c1 Debug.Print c1.MyValue 'gives 0 End Sub Sub TryToChangeValue(ByVal c As Class1) c.MyValue = 2 End Sub Sub TryToChangeObjectByVal(ByVal c As Class1) Dim c2 As Class1 Set c2 = New Class1 Set c = c2 End Sub Sub TryToChangeObjectByRef(ByRef c As Class1) Dim c2 As Class1 Set c2 = New Class1 Set c = c2 End Sub
So now we have the answer: KeyAscii = 0
works because KeyAscii
is an object and this sets the KeyAscii.Value
property to 0
. Although the KeyAscii
object is passed ByVal
, changes we make to its properties will have an impact on any code further up the call stack which references the same object.