Consulting

Results 1 to 9 of 9

Thread: Finding Spaces Equivalent to a Constant String

  1. #1

    Finding Spaces Equivalent to a Constant String

    I have several lines containing, say, three concatenated strings. Each of the concatenated strings will have varying lengths like:

    Cyberdude......Millie Hill......CA
    MyMothaw.......Houston[col.TX
    Son................San Jose......CA

    I would like to Msgbox a list of these strings so that their first characters are aligned like in the example above. Since the characters are variable length, it?s an exercise in futility to try to insert blanks to make them align. I?ve tried using the SPACE(nnn) function by making the argument a litle more complicated:

    SPACE(12 - 1.8 * LEN(?Cyberdude?))

    This assumes that I want the first column to be nominally 12 ?characters? wide, so I try to get an equivalent width of the ?space? character that must be appended to each constant in order to fill out 12 characters. In the example above I have determined by experimenting that I need 1.8 equivalent spaces to match the average width of each Cyberdude character. This technique has some merit with a constant like ?Cyberdude?, but when I get a constant with a lot of narrow characters like ?Millie Hill?, then the equivalent space must be much larger. A constant like ?MyMothaw? has more wide characters, so the equivalent space must be smaller.

    I need a function similar to LEN that can provide me with an equivalent SPACE width for the specified constant. It would be nice to have the specification in the form of a multiplier like the 1.8 that I used above, but maybe it has to be specifed in pixels or some such thing.

    Keep in mind that I want to use this in a Msgbox output where I have no control over the font used for the constants.

    Has anyone come to grips with this problem and solved it?

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Quote Originally Posted by Cyberdude
    Has anyone come to grips with this problem and solved it?
    Yes and No

    You could try putting vbTab as a spacer. Not foolproof as it depends on text length, but maybe if you added some calculated spaces + vbtab you could get things to work.
    Regards
    Malcolm
    Last edited by mdmackillop; 02-24-2006 at 12:08 PM. Reason: Sample added.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3
    VBAX Contributor Aaron Blood's Avatar
    Joined
    Sep 2004
    Location
    Palm Beach, Florida, USA
    Posts
    130
    Location
    Usually when I need to do things like that I just create a userform and dump the output to a multi-column listbox.

  4. #4
    VBAX Master XLGibbs's Avatar
    Joined
    Jan 2006
    Location
    state of confusion, but vacation in denial
    Posts
    1,315
    Location
    Quote Originally Posted by Aaron Blood
    Usually when I need to do things like that I just create a userform and dump the output to a multi-column listbox.
    I agree, if you need to customize a message box result it is easier to just design a form that has the same functionality as the msbox, that way you have a little more control over the font and spacing and such..particularly in a list/column case like this...
    If you have posted the same question at multiple forums, please read this IMPORTANT INFO.

    Please use the thread tools to mark your thread Solved


    Please review the Knowledge Base
    for samples and solutions , or to submit your own!




  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    For example

    [vba]
    Dim ary
    ary = [{"Yellow","1st Colour";"Red","2nd Colour";"Green","3rd Colour"}]
    MsgBox ary(1, 1) & vbTab & ary(1, 2) & vbNewLine & _
    ary(2, 1) & vbTab & ary(2, 2) & vbNewLine & _
    ary(3, 1) & vbTab & ary(3, 2) & vbNewLine
    [/vba]

  6. #6
    Very good and appropriate replies, guys. Sorry it took me so long to acknowledge, but I've had some problems getting through.
    I've been reluctant to use a userform because of some bad experiences I had with them about four years ago. That was then (and a much less powerful PC) and now maybe I'm ready to try them out again. Back then I found that userforms tended to use a LOT of resources ... more than they were worth to me, so I've avoided them ever since. And now it's the chore of going through the learning curve again, but it looks like the time is right.
    Thanks again for your suggestions.

    P.S. What happened to the "Solved" facility?? It's gone.

  7. #7
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    Quote Originally Posted by xld
    For example


    VBA:
    Dim ary
    ary = [{"Yellow","1st Colour";"Red","2nd Colour";"Green","3rd Colour"}]
    MsgBox ary(1, 1) & vbTab & ary(1, 2) & vbNewLine & _
    ary(2, 1) & vbTab & ary(2, 2) & vbNewLine & _
    ary(3, 1) & vbTab & ary(3, 2) & vbNewLine
    VBA tags courtesy of www.thecodenet.com
    The problem comes, and I think this was the point of the question, when some strings are long enough to go past a tab position ...

    VBA:
    Dim ary
    ary = [{"Tangerine","1st Colour";"Red","2nd Colour";"Green","3rd Colour"}]
    MsgBox ary(1, 1) & vbTab & ary(1, 2) & vbNewLine & _
    ary(2, 1) & vbTab & ary(2, 2) & vbNewLine & _
    ary(3, 1) & vbTab & ary(3, 2) & vbNewLine
    VBA tags courtesy of www.thecodenet.com
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    Quote Originally Posted by TonyJollans
    The problem comes, and I think this was the point of the question, when some strings are long enough to go past a tab position ...


    Dim ary
    ary = [{"Tangerine","1st Colour";"Red","2nd Colour";"Green","3rd Colour"}]
    MsgBox ary(1, 1) & vbTab & ary(1, 2) & vbNewLine & _
    ary(2, 1) & vbTab & ary(2, 2) & vbNewLine & _
    ary(3, 1) & vbTab & ary(3, 2) & vbNewLine
    That's why you test things, and if you get a problem, you change it

    [vba]
    Dim ary
    ary = [{"Tangerine","1st Colour";"Red","2nd Colour";"Green","3rd Colour"}]
    MsgBox ary(1, 1) & vbTab & ary(1, 2) & vbNewLine & _
    ary(2, 1) & vbTab & vbTab & ary(2, 2) & vbNewLine & _
    ary(3, 1) & vbTab & vbTab & ary(3, 2) & vbNewLine
    [/vba]

  9. #9
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    But that's not a generic solution - and can only be tested with known combinations of strings.

    Message boxes do not really have the capacity for this kind of thing built in - and a Userform, as has already been suggested, is the real answer.
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

Posting Permissions

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