Working with Strings || Excel VBA Master Class || 3.4d

Note: This webpage is designed to accompany the Youtube video posted above. The video offers detailed explanation on all topics; while this webpage will serve as a repository for the code presented in the video. Source code will be presented in the order of the sections within the video.

String Characters

Sub StringExamples()
Debug.Print “Welcome. This is the VBA Master Class.”
' Concatenate
Debug.Print “Welcome. “ &  “This is the VBA Master Class.”
' Add Double Quote with a Character Code
Debug.Print “Welcome. This is the “ & Chr(34) & “VBA Master Class” & Chr(34) & “.”
' Add Double Quotes Straightaway
Debug.Print “Welcome. This is the “”VBA Master Class””.”
' Add Carriage Return and Line Feed
Debug.Print "Welcome." & vbCrLf & "This is the VBA Master Class."
MsgBox  "Welcome." & vbNewLine & "This is the VBA Master Class."
End Sub

String Types

Sub StringDataTypes()
' Fixed Length
Dim strGreeting as String * 20
strGreeting = “Welcome”
Debug.Print “””” & strGreeting & “”””
End Sub



Sub StringDataTypes()
    Dim strGreeting As String
    strGreeting = "Welcome"
    Debug.Print """" & strGreeting & """"
End Sub

Empty String

Sub EmptyString()
Dim employeeFirstName as String
Debug.Print Len(employeeFirstName)
End Sub

Sub CheckContactDetails()
    Dim emailId As String
    emailId = ThisWorkbook.Sheets("Form").Range("B4").Value
    If emailId = "" Then

    ' If Len(emailId) = 0 Then
    ' If emailId = vbNullString Then
        MsgBox "Please populate an Email Address."
        Exit Sub
    End If


End Sub

Concatenation

Sub Concatenation()
Debug.Print “Welcome. ” & “ This is the VBA Master Class.”
End Sub

Sub StringExamples()
Debug.Print “Welcome. ” + “ This is the VBA Master Class.”
End Sub

Sub StringExamples()
Debug.Print “Welcome. ” + “ This is the VBA Master Class No.” + Cstr(3.4)
End Sub

Manipulate Strings

Option Explicit

Const EmployeeID As String = "Emp-00131-TAB"

Sub ReadEmployeeId()
    
    Dim firstHyphen As Long
    firstHyphen = InStr(EmployeeID, "-")
    Dim startPos As Long
    startPos = firstHyphen + 1
    Dim secondHyphen As Long
    secondHyphen = InStrRev(EmployeeID, "-")
    Dim strLength As Long
    strLength = secondHyphen - startPos
    Dim employeeNumber As String
    employeeNumber = Mid(EmployeeID, startPos, strLength)
    Debug.Print employeeNumber

End Sub

Const EmployeeID As String = "Emp-31-TAB"

Sub ReadEmployeeId()
    
    Dim employeeNumber As String
    Dim employeeSplit() As String
    employeeSplit = Split(EmployeeID, "-")
    employeeNumber = employeeSplit(1)
    Dim lenExtractString As Long, lenDesiredString As Long, lenToPad As Long
    Dim paddedZeros As String
    lenExtractString = Len(employeeNumber)
    lenDesiredString = 5
    lenToPad = lenDesiredString - lenExtractString
    paddedZeros = String(lenToPad, "0")
    employeeNumber = paddedZeros + employeeNumber
    Debug.Print employeeNumber

End Sub

Const EmployeeID As String = "     00131"

Sub ReadEmployeeId()
    
    Dim employeeNumber As String
    employeeNumber = LTrim(EmployeeID)
    Debug.Print """" + employeeNumber + """"

End Sub

Const EmployeeID As String = "     00131  "

Sub ReadEmployeeId()
    
    Dim employeeNumber As String
    employeeNumber = Trim(EmployeeID)
    Debug.Print """" + employeeNumber + """"

End Sub


Const EmployeeID As String = "0 0 1 3 1"

Sub ReadEmployeeId()
    
    Dim employeeNumber As String
    employeeNumber = Replace(EmployeeID, " ", "")
    Debug.Print """" + employeeNumber + """"

End Sub


Const EmployeeName = "john doe"

Sub ReadEmployeeName()

    Debug.Print StrConv(EmployeeName, vbProperCase)

End Sub

String Comparison

Sub StringComparison()
    
    If "John" = "john" Then
        Debug.Print "Both names are same."
        Else
        Debug.Print "Both names are different."
    End If

End Sub

Sub StringComparison()
    Debug.Print StrComp("John", "john", vbTextCompare)
End Sub

Sub StringComparison()
    Debug.Print ("John" Like "J*")
    Debug.Print ("[email protected]" Like "*@*.com")
End Sub

Const folderName As String = "C:\Youtube\Current\VBA_Masterclass\Demo\"
Const fileName As String = "TestFile.doc"
Sub StringComparison()
    If fileName Like "*.xl*" Then
        Workbooks.Open (folderName + fileName)
    End If
End Sub


Const Grade As String = "G"
Sub StringComparison()

    If Grade Like "[ABCD]" Then
        Debug.Print "Passed"
        Else
        Debug.Print "Failed"
    End If
End Sub

Const Grade As String = "A"
Sub StringComparison()
' Continuous Range
    If Grade Like "[A-D]" Then
        Debug.Print "Passed"
        Else
        Debug.Print "Failed"
    End If
End Sub

Formatting Strings

Const StartDate As Date = #1/31/2025#
Sub UsingFormat()

Debug.Print Format(StartDate, "dd-MMM-YY")
End Sub


Sub UsingFormat()
Debug.Print Format(45.5, "Currency")
Debug.Print Format(45.5, "$#.#0")
End Sub

Related Posts