excel - Special character check mark - Stack Overflow

I have found a macro that replaces letters with a certain letter and numbers with another letter in a specified range.

Public Const Letters                As String = "abcdefghijklmnopqrstuvwxyz"
Public Const Numbers                As String = "0123456789"
Public Const FoundLetter            As String = "L"
Public Const FoundNumber            As String = "N"

It works perfectly, but I want to use a check mark (ChrW(&H2713)) instead of a letter. So what should I write instead of: As String = "L"?

I have found a macro that replaces letters with a certain letter and numbers with another letter in a specified range.

Public Const Letters                As String = "abcdefghijklmnopqrstuvwxyz"
Public Const Numbers                As String = "0123456789"
Public Const FoundLetter            As String = "L"
Public Const FoundNumber            As String = "N"

It works perfectly, but I want to use a check mark (ChrW(&H2713)) instead of a letter. So what should I write instead of: As String = "L"?

• excel
• vba

Share Improve this question Follow asked Feb 3 at 7:19 jkpjkp 344 bronze badges 1

• 2 Use the unicode character? copy/paste this -- ✓ – braX Commented Feb 3 at 7:19

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 3

You can create a function and use it like a constant.

Function CheckMark() As String
  CheckMark = ChrW(&H2713)
End Function

Sub Test()
  Range("A1") = CheckMark
End Sub

You can use the Unicode check mark character (✓) directly in your VBA code using ChrW(&H2713). However, since Public Const does not support function calls like ChrW(), you need to define it as a constant string using the actual character.Replace "L" with "✓".

• For letters

  Public Const FoundLetter As String = "✓"
  

• For numbers

  Public Const FoundNumber As String = "✓"
  

Whenever a letter or a number is found, it will be replaced with a check mark.

Using a Sub to assign a value to a public variable.

Public FoundLetter As String

Sub Init_Var()
    FoundLetter = ChrW(&H2713)
End Sub

Sub ReplaceNumber()
    Call Init_Var
    ' Replace digit 9 with check mark
    Sheet1.UsedRange.Replace what:="9", Replacement:=FoundLetter, lookat:=xlPart
End Sub