Excel - VBA format function codes

I thought I understood the VBA Format function codes, but apparantly
not.

I have several columns of numbers that I want to write to a file so
that they line up. Some of them are integers, some have a few decimal
places.



For the integers, I first tried "####0":

?format(123,"####0") & "|"
123|

I forgot that "#" does not insert spaces.

Next I tried "@@@@0":

?format(123,"@@@@0") & "|"
1230|

Now I get the spaces, but why am I getting the extra zero?

Next I tried "@@@@@":

?format(123,"@@@@@") & "|"
123|

This works, but it seems wrong. It works with zero, too:

?format(0,"@@@@@") & "|"
0|

This seems inconsistent with "#":

?format(0,"#####") & "|"
|




Now for the non-integers. Based on the above, I tried:

?format(4.56,"@@@.@@") & "|"
4..56|

What the heck? Why the extra "."?

Next I tried:

?format(4.56,"@@@.00") & "|"
4.5.006|

How do I format a number so I get 2 decimal places and 6 spaces
overall?








Is there any place where all of these codes are *clearly* explained?

@ is a text place holder, for numbers use "0" so maybe "00000" instead of


See http://www.ozgrid.com/Excel/CustomFormats.htm


--
Dave Mills
There are 10 types of people, those that understand binary and those that do not.

I thought I read somewhere that "@" was also a number placeholder
similar to "0", but with spaces for leading 0's. Apparently, this is
not so, because I cannot get it to work right.


The "0" code includes leading zeroes, which makes it more difficult to
read. I want "12" to format as "sss12", not "00012". ("s" = space.)

All I want is a format expression that will round to a specific number
of decimal places and then pad on the left with spaces to a constant
width.


I am trying to format a column of numbers, rounded to different
numbers of decimal places, to a constant width, say 6.

This table shows what I would like to achieve. ("s" indicates a
space.) The "0" code achieves everything I want except that it
includes leading zeroes.

Decimal  Desired           Expr1
Value  Places   Output   Expr1  Output
123     0     sss123  000000  000123
9     0     sssss9  000000  000009
.5     2     sss.50  000.00  000123
1.5     2     ss1.50  000.00  000123
12.34     2     s12.34  000.00  000123
56.789     2     s56.79  000.00  000123
4.6666     3     s4.667  00.000  000123

Is there a code that will do exactly what the "0" code does but uses
spaces for the leading zeroes?

The best I have been able to come up with is to use Format twice: once
to get the rounding and a second time to get the width.

fmtcode = "@@@@@@@"
For i = 1 To N
L1 = L1 & Format(Format(V1(i), "0"), fmtcode)
L2 = L2 & Format(Format(V2(i), "0.00"), fmtcode)
L3 = L3 & Format(Format(V3(i), "0.000"), fmtcode)
Next i

There has to be a simpler way.

Try http://blog.mclaughlinsoftware.com/2009/03/04/lpad-in-excel/



--
Dave Mills
There are 10 types of people, those that understand binary and those that do not.

I think that is for worksheet functions. As far as I know, there is no
REPT function in VBA.

Bonsour=AE Prof Wonmug  avec ferveur  ;o))) vous nous disiez :=20


not so far ...=20
;o)))
Syntaxe
String(number, character)
Dim MyString
MyString =3D String(5, "*")    '  "*****".
MyString =3D String(5, 42)    '  "*****".
MyString =3D String(10, "ABC")    '  "AAAAAAAAAA".

for integer 6 spaces overall:
Right(String(6,32)& myIntValue,6) & "|"

overall ?
Right(String(6,32)& Format(mydecValue,"0.00"),6) & "|"

note : for integer value and decimal value decimal separator will not be =
correctly lined up ???

finally  you also must use fixed font (courier, terminal, system)