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