Site icon bVisual

Using CODE() and UNICODE() functions in Visio

David Parker

Microsoft Visio does have CHAR() and UNICHAR() ShapeSheet functions, just like Excel, but it does not have the inverse CODE() and UNICODE() functions, unlike Excel. However, there is a way to create a formula in the Visio ShapeSheet to provide these functions.

The following worksheet demonstrates how the CHAR(), CODE(), UNICHAR(), and UNICODE() functions work in Excel.

ColumnFormula
Indexn
Char=IFERROR( CHAR(INT([@Index])),””)
Code=IFERROR( CODE([@Char]),””)
Unichar=UNICHAR(INT([@Index]))
Unicode=UNICODE([@Unichar])
1 / 6

Notice that there are no values for either CHAR() or CODE() above 255 because they use a single bit value. However, UNICHAR() and UNICODE() use two-bit values so can work for over 65,000 values.

A similar table in Visio, constructed using a List shape, requires some formulas in the User-Defined Cells section of the ShapeSheet for the formulas in the Shape Data section to use.

The User-defined Cells formulas are:

RowFormula
User.Chars=CHAR(INT(32))&CHAR(INT(33))& … CHAR(INT(255))
User.Code=FIND(ARG(“c”),User.Chars)
User.StartAt31
User.Unichars=UNICHAR(INT(32))&UNICHAR(INT(33))& … UNICHAR(INT(402))
User.Unicode=FIND(ARG(“c”),User.Unichars)

The Shape Data section formulas are:

RowValue Formula
Prop.Index=GUARD(User.StartAt+User.ListPosition)
Prop.Char=GUARD(CHAR(Prop.Index))
Prop.Code=GUARD(EVALCELL(User.Code,”c”,Prop.Char)+User.StartAt)
Prop.Unichar=GUARD(UNICHAR(Prop.Index))
Prop.Unicode=GUARD(EVALCELL(User.Unicode,”c”,Prop.Unichar)+User.StartAt)
2 / 6

Now, I can use these User.Code and User.Unicode formulas in any scenario where I need to get a character from a numeric value.

The formulas for User.Chars and User.Unichars are very long, so I wrote a couple of VBA sub-routines to generate them, and ran them to print into the Immediate window.

Fortunately, the Design / Editing / Edit Formula dialog box will accept the multi-line text, and then just concatenate them into a single log string on close. I found it was necessary to explicitly convert the numbers using Int() function, otherwise the built-in Unichar() function returned just #Value.

Option Explicit

Public Sub PrintChars()
Dim i As Long
Dim s As String
Dim x As Long
Dim y As Long
    y = 52

    For x = 32 To 255 Step (y + 1)
        s = ""
        For i = x To (x + y)
            If i = 32 Then
                s = "=Char(Int(" & i & "))"
            Else
                s = s & "&Char(Int(" & i & "))"
            End If
        Next i
        Debug.Print s
    Next x
End Sub

Public Sub PrintUnichars()
Dim i As Long
Dim s As String
Dim x As Long
Dim y As Long
    y = 52
    For x = 32 To 370 Step (y + 1)
        For i = x To (x + y)
            s = ""
            If i = 32 Then
                s = "=Unichar(Int(" & i & "))"
            Else
                s = s & "&Unichar(Int(" & i & "))"
            End If
        Next i
        Debug.Print s
    Next x
End Sub

Note that I did not bother to loop through all of the 64k values for Unichars!

The Visio list shape in the Office Web Apps viewer ( or in a new tab ):

The Excel worksheet in the Office Web Apps viewer:

Further reading : The Absolute Minimum Every Software Developer Absolutely, Positively Must Know About Unicode and Character Sets (No Excuses!)

Exit mobile version