VBA Function to Remove Non-Alphanumeric Characters

Occasionally, you'll run into data that has been entered with inadvertent carriage returns or other non-alphanumeric characters. Sometimes, these invisible characters can cause mysterious errors in your code. The solution is to use a function to remove any superfluous characters from the data.

The following function removes everything except for the characters indicated between the brackets. You can tweak the accepted character set as needed. The function works by looping through each character in the string and testing each character to see if it matches one of the characters listed between the square brackets.

Function cleanText(strText As String) As String

    Dim valid as String
    Dim test As String
    valid = ""
    test = ""

    For i = 1 To Len(strText)
        test = Mid(strText, i, 1)
        'Only allow characters and spaces, not carriage returns or any other code
        If test Like "[A-Z,a-z,0-9, ,.,/,~,@,#,$,%,^,&,*,(,),_,-,+,= ]" Then
            valid = valid & test
        End If
    Next i
 
    'Return the valid characters
    cleanText = valid

End Function

The cleanText function uses the Visual Basic (VBA) "Like" operator. The Like operator is similar to using Regular Expressions and also has some similarities to the SQL Like operator. You can read more about the VBA Like operator on this page.

VBA Regular Expression Phone Number Validator

This phone number validator validates numbers with or without dashes and with or without dots between the segments.


Public Function validatePhoneNo(phoneno As String) As Boolean

    If phoneno <> "" Then
        Dim re As RegExp
        Dim matches As MatchCollection
        Set re = New RegExp
        re.IgnoreCase = True
        re.Global = True
        re.Pattern = "^\(?\d{3}-?\.?\)?\s?\d{3}-?\.?\d{4}$"
        Set matches = re.Execute(phoneno)
        If matches.Count <> 1 Then
            validatePhoneNo = False
            Exit Function
        End If
    Else
        validatePhoneNo = False
        Exit Function
    End If
 
    validatePhoneNo = True
 
End Function

Use VBA to Automatically Set Dates in Form Fields

In this case, I have a report that requires a Begin Date and an End Date.

To make things easier for the user, I pre-fill the date fields with the date range most commonly used in the report. I use the Form_Load() event to automatically set the dates in the form fields.

Private Sub Form_Load()
    Me.txtBegDt.Value = "1/1/" & Year(Date)
    Me.txtEndDt.Value = Date
End Sub


The first line sets the Begin Date (txtBegDt) field to January 1 of the current year. The second line sets the End Date (txtEndDt) to the current date. Easy as that!

Shortcut to Open the VBA Editor

Using keyboard shortcuts saves a lot of time for things you do frequently. I use this shortcut every time I need to open the VBA editor in an Office Program:

Alt + F11

That's it. Practice using this shortcut a few times until it becomes second nature.