Simulating VB Controls in VBA Code

aikimarkGet vaccinated; Social distance; Wear a mask
CERTIFIED EXPERT
Published:

Introduction

While answering a recent question in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code on my first attempt.  This is a quick tip about simulating VB classic controls in the VBA code environment.

First Attempt Does Not Work

To answer the (textbox keypress event) question, I needed to access the text and selstart properties of a textbox control.  I added a couple of variables in the General Declarations section of a module for the properties.  I set the variables in the Immediate window and got the following code to return the correct answer.
Option Explicit

Public SelStart As Long
Public Text18 As String


Private Sub Text18_KeyPress(KeyAscii As Integer)
    Dim strParsed() As String
    Dim strPostKeyValue As String
    If SelStart = 0 Then
        strPostKeyValue = Chr(KeyAscii) & Text18
    Else
        strPostKeyValue = Left$(Text18, SelStart) & Chr(KeyAscii) & Mid$(Text18, SelStart + 1)
    End If
    strParsed = Split(strPostKeyValue, "x", , vbTextCompare)
    If (UBound(strParsed) >= 0) And (UBound(strParsed) < 2) Then
        If IsNumeric(strParsed(LBound(strParsed))) And (IsNumeric(strParsed(UBound(strParsed))) Or Len(strParsed(UBound(strParsed))) = 0) Then
        Else
            KeyAscii = 0
        End If
    Else
        If IsNumeric(Chr(KeyAscii)) Then
        Else
            KeyAscii = 0
        End If
    End If

End Sub

Open in new window

Since the default property of a textbox is its Text property, I thought this would be simple enough to replace the variable references with Text18 qualified references.  Unfortunately, I missed a couple and could not use the compiler to double-check my syntax.

Note: The feedback I received was "It doesn't work."  Unfortunately, the questioner didn't have an Option Explicit statement or my mistakes would have been caught immediately.

Simulating the Textbox Properties

The solution to this code-only simulation is to create a structure for the textbox properties.  This is done with a Type‚ĶEnd Type statement as shown below.

Option Explicit

Type TextBoxProperties_struc
    Text As String
    SelStart As Long
    SelLength As Long
End Type

Public Text18 As TextBoxProperties_struc


Public Sub Text18_KeyPress(KeyAscii As Integer)
    Dim strParsed() As String
    Dim strPostKeyValue As String
    If Text18.SelStart = 0 Then
        strPostKeyValue = Chr(KeyAscii) & Text18.Text
    Else
        strPostKeyValue = Left$(Text18.Text, Text18.SelStart) & Chr(KeyAscii) & Mid$(Text18.Text, Text18.SelStart + 1)
    End If
    strParsed = Split(strPostKeyValue, "x", , vbTextCompare)
    If (UBound(strParsed) >= 0) And (UBound(strParsed) < 2) Then
        If IsNumeric(strParsed(LBound(strParsed))) And (IsNumeric(strParsed(UBound(strParsed))) Or Len(strParsed(UBound(strParsed))) = 0) Then
        Else
            KeyAscii = 0
        End If
    Else
        If IsNumeric(Chr(KeyAscii)) Then
        Else
            KeyAscii = 0
        End If
    End If

End Sub

Open in new window


Now, the compiler catches all the syntax errors.  It does prevent me from using the default property, but that is a small price to pay for the convenience of answering such VB classic form/control related questions in the VBA environment.

For more complicated control properties, you might need to create a class or create a user form with the particular type of control you need.  
Note: User form controls aren't the same as VB classic controls.  Sometimes they have different properties and methods.
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
If you liked this article and want to see more from this author,  please click here.

If you found this article helpful, please click the Yes button near the:

      Was this article helpful?

label that is just below and to the right of this text.   Thanks!
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
2
4,046 Views
aikimarkGet vaccinated; Social distance; Wear a mask
CERTIFIED EXPERT

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.

Get access with a 7-day free trial.
You Belong in the World's Smartest IT Community