Using "Special Characters" in VBA

[rcarmichael]

Good afternoon,
I have some VBA triggered by a button in a workbook that will automatically hide/show a series of columns (A:CC) if the corresponding cell in ROW(8) contains "NZ".

Private Sub NZToggle_Click()

Dim SrchRng As Range, cel As Range

If NZToggle.Caption = "Hide NZ" Then
        'Hide "New Zealand"
     
Set SrchRng = Range("A8:CC8")

For Each cel In SrchRng
    If InStr(1, cel.Value, "NZ") > 0 Then
        cel.EntireColumn.Hidden = True
    End If
Next cel

  NZToggle.Caption = "Show NZ"
Else
        'Show "New Zealand"
     
Set SrchRng = Range("A8:CC8")

For Each cel In SrchRng
    If InStr(1, cel.Value, "NZ") > 0 Then
        cel.EntireColumn.Hidden = False
    End If
Next cel

  NZToggle.Caption = "Hide NZ"
End If
End Sub

This code works well, but instead of searching for the text "NZ", I want to search for the special character/s "".
I have tried replacing "NZ" with "" by pasting in the special character/s but it results in "????".
I then tried replacing "NZ" with UNICHAR(127475) which results in a "Compile error: Sub or Function not defined" on the Unichar step.
I then tried replacing "NZ" with Chrw(127475) which results in a "Run-time error '5': Invalid procedure call or argument" on the If InStr(1, cel.Value, ChrW(127475)) > 0 Then step.

Any idea what I'm doing wrong, or if what I'm trying to do is even possible?
Sincere regards,
Ryan

 

[Alex Blakenburg]

Someone else may be able to give you a better answer but either of these worked for me:
(use your own cell reference)

Dim unicodeValue As Variant
unicodeValue = Evaluate("=Unichar(127475)")
Debug.Print InStr(1, ActiveCell, unicodeValue)

Using the function from here:

Unicode Escape Sequences Across Various Languages and Platforms (including Supplementary Characters)

Unicode escape sequences across various languages and platforms (including Supplementary Characters), with working examples

sqlquantumleap.com

Section heading Excel / VBA

Debug.Print InStr(1, ActiveCell,  UnicodeFromInt(127475))

The function to insert being:

'https://sqlquantumleap.com/2019/06/26/unicode-escape-sequences-across-various-languages-and-platforms-including-supplementary-characters/
Function UnicodeFromInt(val As Long)
    If val < 0 Or val > 1114111 Then
        UnicodeFromInt = "ERROR: value must be between 0 and 1114111!!"
        GoTo GetOut
    End If
 
    If val >= 55296 And val <= 57343 Then
        UnicodeFromInt = "ERROR: surrogate code points are not displayable!!"
        GoTo GetOut
    End If
 
 
    If val < 65536 Then
        UnicodeFromInt = ChrW(val)
    Else
        UnicodeFromInt = ChrW(55232 + Int(val / 1024)) & ChrW(56320 + Int(val Mod 1024))
    End If
 
GetOut:
End Function

 

[rcarmichael]

Someone else may be able to give you a better answer but either of these worked for me:

Thank you for your reply @Alex Blakenburg.
So close - Any columns with "" hide, but any with "" do not... any idea why?
Thanks,
Ryan

 

[rcarmichael]

Thank you for your reply @Alex Blakenburg.
So close - Any columns with "" hide, but any with "" do not... any idea why?
Thanks,
Ryan

Ignore that - forgot to expand my search range!
Works perfectly, thanks again @Alex Blakenburg !

 

[Alex Blakenburg]

Phew. Thanks for letting me know. Glad its working for you.