Excel / VBA Chr function

What is 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.

The Chr returns the string that contains the character associated with the specified character code (ASCII code).

Syntax:

Chr(charcode)

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, we will use a For loop and run the loop from 0 to 127 numbers.

In each iteration, the number is passed to Chr function and it returns the associated ASCII value for that.

The number is written in an Excel sheet’s cell by using a Range function.

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.

So, if you want to include double quotes within a string then you may use Chr(34) character (one of the ways).

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: