Search code examples
excelvbaunicodeemojiunicode-string

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

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.

Solution

  • 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/