On Sun, 27 Jan 2008 22:59:23 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

If you make dVar1 = 1234, the alignment of the text and $ sign doesn't
match
any more. Because of the thousands separator perhaps?

I noticed that, too. But I think my latest version takes care of that,
for the
most part.

I have read (but not confirmed) that for many proportional spaced fonts,
the
digit width is generally fixed, and about twice that of a <space, <comma
or
<dot.

Yes, your revision appears to work fine. I was aware that the digits were
very nearly the identical width in proportional fonts, but I don't recall
ever seeing that they were twice the width of the space, comma or dot;
however, with your code working so well, it would appear that is the case.
(I'll definitely keep it in mind for use back in the compiled VB
newsgroups.<g) By the way, for the underline character, if you divide DL by
1.5 instead of by 2 in your WorksheetFunction.Rept function call, it fills
out the area nicely. Perhaps the ratio of an underline compared to a space
is very nearly fixed?

With regard to your WorksheetFunction.Rept function calls, once for the
underline character and once for the space character, VB has two built-in
function that you can use instead. This...

Application.WorksheetFunction.Rept("_", Len(d3pad))

can be replaced by this...

String(dL / 2, "_")

although this is the 2 that I proposed replacing with 1.5 above. Also, you
can completely remove this framework...

With Application.WorksheetFunction
...
End With

if you replace these...

d1pad = " $" & .Rept(" ", 2 * (dL - Len(Format(dVar1, sFmt))))
d2pad = " $" & .Rept(" ", 2 * (dL - Len(Format(dVar2, sFmt))))
d3pad = " $" & .Rept(" ", 2 * (dL - Len(Format(dVar1 + dVar2,
sFmt))))

with these...

d1pad = " $" & Space(2 * (dL - Len(Format(dVar1, sFmt))))
d2pad = " $" & Space(2 * (dL - Len(Format(dVar2, sFmt))))
d3pad = " $" & Space(2 * (dL - Len(Format(dVar1 + dVar2, sFmt))))

using VB's Space function instead of the worksheet's REPT function.

Rick


On my subsequent iteration, the With Application.worksheetfunction. had been
removed.

Thanks for the pointer on the Space and String functions. Here's the revised
version.

========================
Option Explicit
Private Const sFmt As String = "#,##0.00"
Sub foo()
Const dVar1 As Double = 1
Const dVar2 As Double = 9999

Dim res

Dim d1pad As String
Dim d2pad As String
Dim d3pad As String
Dim dL As Long 'used to compute padding

dL = Len(Format(dVar1 + dVar2, sFmt)) * 2 + 2

d1pad = Pad(dVar1, dL)
d2pad = Pad(dVar2, dL)
d3pad = Pad(dVar1 + dVar2, dL)

res = MsgBox("EMS Job 1:" & d1pad & Format(dVar1, sFmt) & _
vbLf & "EMS Job 2:" & d2pad & Format(dVar2, sFmt) & _
vbLf & String(dL / 2, "_") & vbLf & _
"TOTAL:" & d3pad & Format(dVar1 + dVar2, sFmt), vbMsgBoxRight)

End Sub

Private Function Pad(dVar As Double, dL As Long) As String
Dim PadLength As Long
Dim lDigitCount As Long
Dim lCommaCount As Long
Const lDotCount As Long = 1

lDigitCount = Len(Format(dVar, "0.00")) - 1
lCommaCount = Len(Format(dVar, sFmt)) - _
Len(Replace(Format(dVar, sFmt), ",", ""))

PadLength = dL - lDigitCount * 2 - lCommaCount - lDotCount
Pad = " $" & Space(PadLength)
End Function
========================================
--ron