When you write a macro in VBA, very often you will need to handle a string. Indeed, you will certainly need to retrieve several characters of the string or do a contain or format a part of your string…

Format in a string

By separating a string with the acronyms “&“, you can insert variables in your string. You can also format these variables using the format functions. In the example below, we used FormatNumber and FormatPercent.

Sub UseFormatFunction()
'Format inside a string

    Dim S As String
    S = "The PnL is equal to" & FormatNumber(1098) & "and it represents " & _
   FormatPercent(0.14) & "of the portfolio."
    MsgBox S

End Sub

Contains in VBA

How can you check that your variable or sentence contain a define word? To do it, you can use the Like operator.  Asterisks and Like operators can also be used to do a “begin with” or “end with”. You can also test the not contain, using the operator Not.

Sub ContainsInString()

    TestString = "I am a Potato"
    'Contains the word Potato
    If TestString Like "*Potato*" Then
        MsgBox "Potato is contains in the variable"
    End If
    'Not contains the word Tiger
    If Not TestString Like "*Tiger*" Then
        MsgBox "Tiger is not contains in the variable"
    End If
    'Not contains the value on A1
    If Not TestString Like "*" & Range("A1").Value2 & "*" Then
        MsgBox "The value on A1 is not in the variable."
    End If

End Sub

Format part of the string

How can you format a particular word in the string?

To do it, you need to find the position of your word in the character string. Start by questioning whether the word exists in the string using the Like operator, or by doing a contain. Once the word has been found, you need to find out where it starts using the InStr function in VBA. This function returns a number specifying the position of the first occurrence of one string within another. Once the position of the first letter of your word has been found, knowing the length of the word you are looking for, you can format it, using the Characters method.

Sub FormatPartOfString()

    'Format a part of a string
    If Sheets("Menu").Cells(1, 1).Value2 Like "*name*" Then
        BeginWord = InStr(1, Cells(1, 1).Value2, "name")
        'Put "name" in bold in the string
        Cells(1, 1).Characters(BeginWord, 4).Font.Bold = True
        'Color "name" in red in the string
        Cells(1, 1).Characters(BeginWord, 4).Font.Color = RGB(192, 0, 0)
    End If
End Sub

Write arrow in a string

For an exhaustive list of possible values, you can consult the ASCII character code table. 

To obtain an ascending or descending arrow, you can put the code &H2198 or &H2197 in the ChrW function.

Sub WriteArrowInString()

    'Using the UTF16 code
    Range("A1").Value2 = "The volatility increases. " & ChrW(&H2197)
    Range("A2").Value2 = "The price decreases. " & ChrW(&H2198)

End Sub

Spaces in a string

If you want to set a specific number of spaces in your character strings between two words, insert the Space function indicating the number of spaces between two strings.

Sub SpaceFct()
'10 Spaces

     Dim StrChain
    'Insert 10 spaces between two strings
    StrChain = "Happy" & Space(10) & "Smile" & _
                        Space(10) & "!"
    MsgBox StrChain

End Sub
Spaces in Msgbox

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s