(Word2003) FORMAT number with square brackets

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

(Word2003) FORMAT number with square brackets

Post by ChrisGreaves »

(Edited to include MY debug window output (at end))
I have a reason for printing/typing a numeric value enclosed in square brackets, thus from the value 1 I should receive the four-character string

Code: Select all

[01]
I tried Format(1, "[00]") but this returned the string "1".

Code: Select all

Sub test()
    Debug.Print Format(1, "00") ' basic two-digit format
    Debug.Print Format(1, "-00") ' basic two-digit format
    Debug.Print Format(1, "[00") ' appears to return a null/blank string
    Debug.Print Format(1, "[00]") ' does NOT format as I want (enclosed in square brackets)
    Debug.Print Format(1, "(00)") ' shows I can use PARENTHESES
    Debug.Print Format(1, "{00}") ' shows I can use BRACES
    Debug.Print "[" & Format(1, "00") & "]" ' clumsy but effective
End Sub
Of all these essays, only the clumsy-looking last delivers what I want. I can live with that.
But I am puzzled because I searched the web to see what meaning the (2003) VBA function FORMAT attaches to the square brackets and could not find an answer.
https://docs.microsoft.com/en-us/office ... plications
https://www.techonthenet.com/excel/form ... string.php
https://www.excelfunctions.net/vba-format-function.html
http://exceldevelopmentplatform.blogspo ... ck-to.html
https://www.mrexcel.com/board/threads/a ... er.114052/
And yes, in desperation I turned to Excel/VBA, even though I am using Word/VBA.

In all my Formatting (starting a long, long time ago with FORTRAN II) I can't recall ever using square brackets as reserved symbols within a format statment.
Within regular Expressions, yes, and in proprietary languages, yes; but never with formatting.

So I am curious - what is it about SQUARE brackets that causes Word2003/VBA to hiccough?

(signed) Just Curious" of Bonavista

Code: Select all

01
-01

1
(01)
{01}
[01]
An expensive day out: Wallet and Grimace

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: (Word2003) FORMAT number with square brackets

Post by HansV »

This will work:

Code: Select all

    Debug.Print Format(1, "\[00\]")
I don't know the answer to your question.
Best wishes,
Hans

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: (Word2003) FORMAT number with square brackets

Post by ChrisGreaves »

HansV wrote:
24 Nov 2020, 19:13
This will work:
Thanks Hans, and by extension, so does this, pretty well:

Code: Select all

Sub test2()
    Debug.Print Format(1, "\[00\]")
End Sub
Sub test3()
    Dim lng As Long
    lng = 91
    Dim strFormat As String
    strFormat = "\" & Chr(lng) & "000\" & Chr(lng)
    Debug.Print Format(lng, strFormat)
    For lng = 32 To 255
        strFormat = "\" & Chr(lng) & "000\" & Chr(lng)
        Debug.Print Format(lng, strFormat)
    Next lng
End Sub
I don't know the answer to your question.
And thanks too for this. I spend too much time wondering what ***I've*** done wrong.
It sounds to me as if the formatting strings were rolled out before they were completed!
deadlines, Deadlines, DEADlines.
Cheers
Chris
An expensive day out: Wallet and Grimace

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: (Word2003) FORMAT number with square brackets

Post by Doc.AElstein »

Could what Hans posted suggest that some “wild card wiring” has inadvertently got cross wired into VBA function FORMAT and is “chucking the spanner in the works”?
I think in some syntax related to wild things, you have to use \ if you want to use a character that has a special meaning in wildcard searches. Square brackets are one of those special meanings characters.
So in wild thing searches you would routinely do \[ or \] if you actually wanted to refer to [ or ]

Alan

https://eileenslounge.com/viewtopic.php ... 44#p175744
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: (Word2003) FORMAT number with square brackets

Post by ChrisGreaves »

Doc.AElstein wrote:
25 Nov 2020, 07:10
Could what Hans posted suggest that some “wild card wiring” has inadvertently got cross wired into VBA function FORMAT and is “chucking the spanner in the works”?
Hi Alan.
Yes, this is what I was hinting at when I wrote "as if the formatting strings were rolled out before they were completed!".
The slash In this case is an example of an "escape sequence".
Cheers
Chris
An expensive day out: Wallet and Grimace