vbaunicodeemoji

How to identify emoji in string in VBA

I'm writing code in Excel that parses directions given to drivers in a fleet to eliminate someone doing it manually. They use the emoji 🛑 to indicate a stop sign, and currently someone goes through each list of directions, selects the character, and turns it red.

I'd like to do that automatically. But I'm not seeing the relationship between how VBA treats a regular string and how to manipulate that to Unicode.

I tried returning the AscW value of a cell with just the emoji in it, and got -10179. So I tried

I want something like:

    If Target.Characters.Count > 0 Then
        For x = 1 To Target.Characters.Count
            If Target.Characters(x, 1) = ChrW(d83dded1) Then
                Target.Characters(x, 1).Font.Color = RGB(255, 0, 0)
            End If
        Next x
    End If

The info for the emoji I found here. iemoji.com

I've tried a few permutations on this, but can't get it to work.

I tried returning the AscW value of a cell with just the emoji in it, and got -10179. So I tried If AscW(Target.Characters(x, 1)) = -10179 Then But got "Object doesn't support this property or method".

I tried

Solution

  • This worked for me:

    Dim pos As Long
    
pos = InStr([A1].Value, ChrW(-10179) & ChrW(-8495))
If pos > 0 Then [A1].Characters(pos, 2).Font.Color = vbRed