VBA: Understanding ByVal In An ActiveX Textbox KeyPress Event Handler

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:

  1. KeyAscii isn’t used at a later point in the procedure.
  2. KeyAscii is passed 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 Value property.

ReturnInteger

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.

Advertisements

About Colin Legg

RAD Developer Microsoft MVP - Excel 2009 - 2014
This entry was posted in Microsoft Excel and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s