VBA Chr function

Chr is a Text function in Excel that we may use in the VBA.

However, like many other Excel functions, you do not need to use it as WorksheetFunction object.

Syntax:

An example of using Chr function

We will pass a few ASCII values to Chr() function to get the associated character.

The results are displayed in the message box:

Sub chr_ex()

MsgBox "Result of ASCII 65 = " & Chr(65)

MsgBox "Result of ASCII 90 = " & Chr(90)

MsgBox "Result of ASCII 48 = " & Chr(48)

MsgBox "Result of ASCII 97 = " & Chr(97)

End Sub

Results:

Using 1 to 127 ASCII values and writing results in an Excel sheet by Chr

In this example,

See the code and output:

Sub chr_ex()

Dim i As Integer

For i = 1 To 127

    Range("A" & i) = i

    Range("B" & i) = Chr(i)

Next i

End Sub

Output sample:

New line example by Chr(10)

This example shows the usage of Chr(10) which represents the “Line feed (LF)” character.

We used a for loop that will execute ten times.

In each iteration, we will use Chr(10) to add a new line.

VBA Code:

Sub chr_ex()

Dim i As Integer
Dim Str As String

'Using new line character by Chr

For i = 1 To 10

    Str = Str & i & Chr(10)

Next i

MsgBox Str

End Sub

Output:

Adding double quotes in a string example

Strings in VBA are enclosed in double quotes.

The following example concatenates a word in double quotes and displays it in the message box:

Sub chr_ex()

Dim Str As String
Str = "This is a" & Chr(34) & "VBA" & Chr(34) & " Tutorial"

MsgBox Str

End Sub

Output: