PDA

View Full Version : Finding Spaces Equivalent to a Constant String



Cyberdude
02-24-2006, 11:38 AM
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?

mdmackillop
02-24-2006, 11:52 AM
Has anyone come to grips with this problem and solved it?

Yes and No:banghead:

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

Aaron Blood
02-24-2006, 12:39 PM
Usually when I need to do things like that I just create a userform and dump the output to a multi-column listbox.

XLGibbs
02-24-2006, 02:49 PM
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...

Bob Phillips
02-24-2006, 05:05 PM
For example


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

Cyberdude
02-25-2006, 03:47 PM
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.

TonyJollans
02-26-2006, 06:43 AM
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 (http://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 (http://www.thecodenet.com)

Bob Phillips
02-26-2006, 07:45 AM
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


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

TonyJollans
02-26-2006, 08:11 AM
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.