Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom String Format
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 -- Neal Z |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom String Format
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 wrote: 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 -- Neal Z -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom String Format
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. -- Neal Z "Dave Peterson" wrote: 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 wrote: 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 -- Neal Z -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with converting CUSTOM format/TEXT format to DATE format | Excel Worksheet Functions | |||
Format Cell as custom type but data doesn't display like I custom. | Excel Discussion (Misc queries) | |||
Excel 2003. Custom format gets replaced by Special format. | New Users to Excel | |||
Converting a time format to a String format in Excel | Excel Discussion (Misc queries) | |||
how do I add phone number format as a permanent custom format? | Excel Discussion (Misc queries) |