Refer to worksheet that uses icon/emoji as part of name

Question

I'm using Excel for Office 365 MSO 64-bit.

I want to select worksheets based on the worksheet's name.

For example, I have two lines of VBA code that activate a workbook and then select a sheet in the workbook by the sheet's name.

Windows("myworkbook").Activate
Sheets("mysheet").Select

However, I have sheets with names that contain icons or emojis. For example: "🚑 Patient".

If I try to paste the icon/emoji into VBA code like this: Sheets("🚑 Patient").Select, the icon does not show up in the VBA editor. I get Sheets("????? Patient").select.

I tried to use ChrW() to encode the ambulance character (see here: https://www.compart.com/en/unicode/U+1F691)

When I run this macro, I get

invalid procedure call or argument

Sub SelectWeirdSheet()
Windows("MYWorkbook.xlsx").Activate
x = ChrW(128657) ' get invalid procedure call or argument here 
Sheets(x & " Patient").Activate
End Sub

I also tried code for ambulance ChrW(&H1F691), but I get the same error. My suspicion is that I am using the wrong argument for ChrW().
The docs say my argument for ChrW() is out of range. That helps explain the error, but I'm still missing a work-around.

Question: Is there a way to use VBA to select worksheets that have an icon/emoji as part of their name?

I know you can also refer to worksheets by index number like this Sheets(3).Select.
However, there will be instances where I don't know the index of the sheet ahead of time, but I will know the name of the sheet, so it is preferable to call the worksheets by name.

Answer 1

In order to properly address the emoji, it should be split into two separate unicode characters. In this case, it would be x = ChrW(&HD83D) & ChrW(&HDE91)

Those two unicode characters make up the ambulance emoji.

So, this Macro now works.

Sub SelectWeirdSheet()
    Windows("MYWorkbook.xlsx").Activate
    x = ChrW(&HD83D) & ChrW(&HDE91)
    Sheets(x & " Patient").Activate
End Sub

Found the solution on reddit of all places https://www.reddit.com/r/excel/comments/6pq1r1/vba_how_can_i_write_emojis_using_chrw/

Answer 2

In addition to the self-answered response, when working in a single workbook, the coder can assign a CodeName to the sheet in the VBA IDE, and then use that CodeName directly. This is really only valid if the Sheet is not re-created (i.e. is a permanent sheet in the book) at any stage, because a new/copied sheet will be automatically given a new CodeName by Excel.

For example, if given the CodeName shtPatient (see picture bellow), the code could be:

Sub SelectWeirdSheet()
    ' Windows("MYWorkbook.xlsx").Activate '<-- this approach has limitations
    shtPatient.Activate ' See my comment below about the limitation - this will not work as expected in this example.
End Sub

Note: https://stackoverflow.com/a/10718179/9101981 explains why not to use Activate, but I have left the code as-is for the purposes of this answer. Also look at Using Worksheet CodeName and Avoiding .Select & .Activate. Another limitation noted is that the CodeName is only valid for the workbook that the code is in - so may not be applicable in this case.

I have highlighted the CodeName parts of the IDE in the image below, see how "Test Patient" is not called "Sheet7", but instead has a meaningful name that I gave it in the properties window below.