Determining an ANSI Value in a Macro

@Mike J
Mea culpa. Your code is correct; mine is wrong.
Here is your code formatted for looks:

Function EXCEL_UNICODE(txt As String) As Long
    Dim highSurrogate As Long, lowSurrogate As Long
    If Len(txt) = 0 Then Exit Function
    'Get the first 16-bit block
    highSurrogate = AscW(Mid(txt, 1, 1))
    If highSurrogate < 0 Then highSurrogate = highSurrogate + 65536
    'Check if it's the start of an emoji/surrogate pair (&HD800 to &HDBFF)
    If highSurrogate >= 55296 And highSurrogate <= 56319 _
        And Len(txt) > 1 Then lowSurrogate = AscW(Mid(txt, 2, 1))
    If lowSurrogate < 0 Then
        lowSurrogate = lowSurrogate + 65536
        'Recombine the pair into the true 32-bit Unicode Code Point
        EXCEL_UNICODE = (highSurrogate - 55296) * 1024 _
            + (lowSurrogate - 56320) + 65536
    Else
        EXCEL_UNICODE = highSurrogate
    End If
End Function

Function EXCEL_UNICHAR(code As Long) As String
    Dim highSurrogate As Long, lowSurrogate As Long
    'If it's a true 32-bit character (like an emoji)
    If code > 65535 Then
        code = code - 65536
        highSurrogate = (code \ 1024) + 55296
        lowSurrogate = (code Mod 1024) + 56320
        'Re-encode back into VBA's expected signed format
        If highSurrogate > 32767 Then highSurrogate = highSurrogate - 65536
        If lowSurrogate > 32767 Then lowSurrogate = lowSurrogate - 65536
        EXCEL_UNICHAR = ChrW(highSurrogate) & ChrW(lowSurrogate)
    Else
        'Standard or 16-bit character
        If code > 32767 Then
            EXCEL_UNICHAR = ChrW(code - 65536)
        Else
            EXCEL_UNICHAR = ChrW(code)
        End If
    End If
End Function

@J.Woolley

Thanks, I have just tried Unicode() and Unichar() in Excel 2021 and, as you rightly say, they perform correctly.
But =AscW(text) was exactly what AI came up with at its first attempt and it didn't work. I got -10179 as a result.
Its second attempt simply subtracted 65536 if the number was negative and that didn't work either. I got 55357 as result.
Its final version, the one in my comment, produced the correct result of 128221, which is an emoji of a pad and pen.

Perhaps more interesting is that =AscW(Text) doesn't work in Excel 2021 VBA either, so presumably one would have to rely on
Application.WorksheetFunction.UNICODE() or something like AI produced if you want to incorporate it inside a macro for extended unicode characters.

@Mike J
I guess you're stuck with Excel 2010 so you don't have Excel 2013 functions UNICODE and UNICHAR, which correspond to VBA AscW and ChrW. Maybe you should consider upgrading.
Your chat with AI must have been confusing. Here's some code to try instead:

Function MyUNICODE(text As String) As Long
    MyUNICODE = AscW(text)
End Function

Function MyUNICHAR(val As Long) As String
    MyUNICHAR = ChrW(val)
End Function

I recently had a chat with AI trying to find the code of a particular character I downloaded as part of a data block.
It gave me the code and char versions each time.

the first versions used AscW() and returned a 5-digit negative code, but it would not reverse correctly

The second version also used Ascw(), but added 65536 if it was negative and returned a 5 digit positive code, but that still did not reverse correctly

The final version correctly returned a 6 digit positive code, and reversed correctly.

The codes, which work in Excel 2010, are below:


Function EXCEL_UNICODE(txt As String) As Long
Dim highSurrogate As Long, lowSurrogate As Long

If Len(txt) = 0 Then Exit Function

' Get the first 16-bit block
highSurrogate = AscW(Mid(txt, 1, 1))
If highSurrogate < 0 Then highSurrogate = highSurrogate + 65536

' Check if it's the start of an emoji / surrogate pair (&HD800 to &HDBFF)
If highSurrogate >= 55296 And highSurrogate <= 56319 And Len(txt) > 1 Then
lowSurrogate = AscW(Mid(txt, 2, 1))
If lowSurrogate < 0 Then lowSurrogate = lowSurrogate + 65536

' Recombine the pair into the true 32-bit Unicode Code Point
EXCEL_UNICODE = (highSurrogate - 55296) * 1024 + (lowSurrogate - 56320) + 65536
Else
EXCEL_UNICODE = highSurrogate
End If
End Function


Function EXCEL_UNICHAR(code As Long) As String
Dim highSurrogate As Long, lowSurrogate As Long

' If it's a true 32-bit character (like an emoji)
If code > 65535 Then
code = code - 65536
highSurrogate = (code \ 1024) + 55296
lowSurrogate = (code Mod 1024) + 56320

' Re-encode back into VBA's expected signed format
If highSurrogate > 32767 Then highSurrogate = highSurrogate - 65536
If lowSurrogate > 32767 Then lowSurrogate = lowSurrogate - 65536

EXCEL_UNICHAR = ChrW(highSurrogate) & ChrW(lowSurrogate)
Else
' Standard or 16-bit character
If code > 32767 Then
EXCEL_UNICHAR = ChrW(code - 65536)
Else
EXCEL_UNICHAR = ChrW(code)
End If
End If
End Function

I haven't done extensive testing on this code, but it still seems to work with simple ascii characters

ASCII character codes range from 0 to 127 (7-bits).
ANSI character codes range from 0 to 255 (8-bits).
If you might have UNICODE characters beyond ANSI like ✓ (check) or Δ (delta), use AscW(...) instead of Asc(...). Both return the same value for ASCII or ANSI characters, but Asc(...) returns 63 (?) for UNICODE characters beyond ANSI.