Advanced

Unicode Private Use Areas in VBA

Looking for a safe temporary character or text delimiter that is guaranteed to not exist in your data set? Look no further than the Unicode Private Use Areas.

Mike Wolfe

Sep 13, 2022 • 4 min read

The Unicode character set has three ranges of character codes that are set aside for private use.

These so-called "Private Use Area" characters can be used:

As a temporary placeholder character during text processing
As a delimiter in a text-based file export
Any time you want a valid character that you can rely on not being present in the source text

What is Meant by "Private Use"?

According to the Unicode definition:

Private-use characters are assigned Unicode code points whose interpretation is not specified by this standard and whose use may be determined by private agreement among cooperating users. These characters are designated for private use and do not have defined, interpretable semantics except by private agreement.

...

No charts are provided for private-use characters, as any such characters are, by their very nature, defined only outside the context of this standard

PUA Ranges

The Private Use characters occupy three ranges of Unicode characters, provided here in hexadecimal format:

0xE000 .. 0xF8FF: 6,400 code points
0xF0000 .. 0xFFFFD: 65,534 code points
0x100000 .. 0x10FFFD: 65,534 code points

For more information about hexadecimal numbers in VBA–especially those in the range check out my article, Understanding Signed and Unsigned Numbers in VBA.

Windows PUA Characters

Out of curiosity, I wanted to see which PUA characters are defined in Windows and what they look like in the standard message box font.

Unfortunately, the VBA MsgBox function does not display Unicode characters. We can get around that shortcoming using the Windows API function MessageBoxW. I created a drop-in replacement for the VBA MsgBox in my article, Unicode-Friendly MsgBox.

Armed with the Unicode-safe MsgBox, I wrote a quick procedure to loop through all 6,400 characters of the first PUA range.

Sample Code

Public Const MinPuaCode As Integer = &HE000
Public Const MaxPuaCode As Integer = &HF8FF

Sub TestPuaCodes()
    Dim Msg As String
    
    Dim Batch As Byte
    For Batch = 0 To 7
        'Get max and min code values for the batch
        Dim MinCode As Integer, MaxCode As Integer
        MinCode = CInt(MinPuaCode + Batch * 900)
        MaxCode = CInt(MinPuaCode + ((Batch + 1) * 900) - 1)
        If MaxCode > MaxPuaCode Then MaxCode = MaxPuaCode
        
        'Add the header line to the message
        Msg = "From " & Hex(MinCode) & _
              " to " & Hex(MaxCode) & _
              vbNewLine & vbNewLine
              
        'Append a 30 x 30 array of PUA Unicode characters to the message
        Dim Row As Byte
        For Row = 0 To 29
            Dim Col As Byte
            For Col = 0 To 29
                Dim PuaCode As Long
                PuaCode = MinPuaCode + Col + (30 * Row) + (900 * Batch)
                If PuaCode < MaxPuaCode Then Msg = Msg & ChrW(PuaCode)
            Next Col
            Msg = Msg & vbNewLine
        Next Row
        
        'Show the message using our Unicode-safe MsgBox:
        '   https://nolongerset.com/unicode-friendly-msgbox/
        MsgBox Msg, , "PUA Codes: Batch " & Batch + 1
    Next Batch
    
End Sub

Sample Output

Running the code on my Windows 10 computer yielded the eight screenshots below. Some of the glyphs (especially in Batch 1) look like they could be potentially useful. However, as stated above, there is no guarantee that every font or system will implement semantically-similar glyphs in these Private Use Areas.

Proceed with caution if you use any of the glyphs below.