Results 1 to 4 of 4
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formatting - Blank When Zero (VB)

    I have just discovered how to effect the "blank when zero" clause of COBOL in VBA (without writing an emulator)
    I display a version number from a LONG, to be 3 digits:<pre> MsgBox Format(lngVersion, "000")</pre>

    I want three digits displayed for the version number, provided it is a valid number, value greater than zero.
    If the value is zero, we have not yet made a version, so I should show spaces. This seems to do the trick:<pre>MsgBox "*" & Format(0, "$#,##0;; ") & "*"</pre>


    In WordXP's Help files under "Different Formats for Different Numeric Values (Format Function)"
    If you include semicolons with nothing between them, the missing section is printed using the format of the positive value.
    For example, the following format displays positive and negative values using the format in the first section and displays "Zero" if the value is zero.

    "$#,##0;;Zero"



    "COBOL Rules OK" (Daniel D. McCracken 1963).

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting - Blank When Zero (VB)

    I'll think a bit more. Thanks, Hans for the feedback.
    For my immediate purposes, your solutions work fine - each one delivers spaces.
    I went a bit further (beyond the original specifications, as usual), and wondered what was being returned, using LEN. Remember to discount "2" from each length to account for the asterisks.

    <pre>Sub test()

    MsgBox Len("*" & Format(0, "$#,##0;; ") & "*") ' my original
    MsgBox Len("*" & Format(0, "$#,##0;; ") & "*") ' Hans "no need slashes"

    MsgBox Len("*" & Format(0, "$#,##0;; ") & "*") ' Hans 1
    MsgBox Len("*" & Format(0, "$#,##0;; ") & "*") ' Hans 2
    MsgBox Len("*" & Format(0, "$#,##0;;""""") & "*") ' Hans & the trifecta

    End Sub
    </pre>


    I'm especially fond of the double double double quotes. I'm thinking of working it up to a thesis.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formatting - Blank When Zero (VB)

    Spaces are treated as spaces in a format string, there is no need to prefix them with . You can also use
    <pre>MsgBox "*" & Format(0, "$#,##0;; ") & "*"
    </pre>

    or
    <pre>MsgBox "*" & Format(0, "$#,##0;; ") & "*"
    </pre>

    Or you could use an empty string within the format string (requiring double double double quotes <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)
    <pre>MsgBox "*" & Format(0, "$#,##0;;""""") & "*"
    </pre>

    You can hide negative numbers by applying the same technique to the middle section, etc.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formatting - Blank When Zero (VB)

    Chris,

    I corrected my previous reply by using <!t>[pre]<!/t> and <!t>[/pre]<!/t> instead of <!t>
    Code:
    <!/t> and <!t>
    <!/t>, so that you can actually see the different number of spaces in the first two examples I gave.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •