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:
KeyAsciiisn’t used at a later point in the procedure.
ByVal, 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
I think things become clearer if we re-write the code so that it explicitly uses that
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:
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
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:
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.