2

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

I want to write a VBA macro that selects different worksheets in a workbook based on the worksheet's name.

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

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

However, I have to work with some sheets that contain icons or emojis in them. For example, there is a worksheet that has this name: " Patient".

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

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

When I run this macro below), I get an invalid procedure call or argument as noted below.

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... also tried ChrW(&H1F691), but I get the same error. My suspicion is that I am using the wrong argument for ChrW(), but I'm lost.

edit: So, the docs say that 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 refer 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 for me to call the worksheets by name.

Thank you.

Cindy Meister
  • 24,375
  • 21
  • 34
  • 43
pdanese
  • 1,967
  • 4
  • 14
  • 20

2 Answers2

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/

AJD
  • 2,362
  • 2
  • 11
  • 22
pdanese
  • 1,967
  • 4
  • 14
  • 20
1

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.

enter image description here

AJD
  • 2,362
  • 2
  • 11
  • 22