Custom String Format

[Neal Zimm]

Hi All,
After reading the help on Format, I don't know
what I missed on trying to format a code with
a bit of custom text. Clearly I would like to
avoid the last brute force method. There were no
examples I saw that fit in the programming
community for Excel.

Where did I go wrong?
Thanks,
Neal


Sub Format_Apply_Fmt_To_Var()
Dim gsWbkFmtAy(1 To 2) As String, Cd As String, Desc As String
gsWbkFmtAy(1) = "Rte Wbk"
gsWbkFmtAy(2) = "Other"

Cd = "1"
Desc = Format(Cd, gsWbkFmtAy(Val(Cd)))
Debug.Print Desc & " 1 s/b: Rte Wbk"
' result is: Rte 1bk

Cd = "2"
Desc = Format(Cd, gsWbkFmtAy(Val(Cd)))
Debug.Print Desc & " 2 s/b: Other"
' result is: Oth0er

Cd = "a"
Desc = Format(Cd, gsWbkFmtAy(1))
Debug.Print Desc & " a s/b: Rte Wbk"
' result is: a

Cd = "b"
Desc = Format(Cd, gsWbkFmtAy(2))
Debug.Print Desc & " b s/b: Other"
' result is: b

'exasperation
Dim Ix As Integer
Desc = ""
Cd = "1"
For Ix = 1 To UBound(gsWbkFmtAy)
If Ix = Val(Cd) Then Desc = gsWbkFmtAy(Ix): Exit For
Next Ix
Debug.Print Desc & " brute force for 1"
' as expected, Rte Wbk
Exit Sub

 

[Dave Peterson]

VBA's Format() is a lot like =text() in excel.

There are characters that mean special things inside that format string:

mmm (month abreviation)
ww (week number)
h (hour)
m (minute)
and a bunch more.

You can include an "escape" character in the string to see that character:

gsWbkFmtAy(1) = "Rte \Wbk"

So the W following the \ character is seen as a W--not as a place holder for a
week number.

You could also enclose strings in double quotes:
gsWbkFmtAy(1) = "Rte ""W""bk"
or
gsWbkFmtAy(1) = "Rte ""Wbk"""
or
gsWbkFmtAy(1) = """Rte Wbk"""

(Remember to double up your quotation marks inside a string.)

 

[Neal Zimm]

Thanks Dave,
Format help was the first I read and I saw the chars you mentioned, I
never connected the dots with my error despite seeing " Rte 1bk" the 1
should have been a big hint.

thanks again.