Two short questions

[WLMPilot]

1) How is a variable DIM if it might hold decimal places?

2) Ref Msgbox, how do I show multiple lines within Msgbox? I have two
part-time jobs and I want to display the estimated pay for each for current
payperiod and then a total. I can get it on one line, but I would like to
show each job on separate line and lined up so I can display total amount too.

EXAMPLE: EMS Job1: $100.00
EMS Job2: $235.00
TOTAL: $335.00

Thanks,
Les

 

[Ron Rosenfeld]

1) How is a variable DIM if it might hold decimal places?

DIM dNum as Double

2) Ref Msgbox, how do I show multiple lines within Msgbox?

Separate the lines with vbLf

I have two
part-time jobs and I want to display the estimated pay for each for current
payperiod and then a total. I can get it on one line, but I would like to
show each job on separate line and lined up so I can display total amount too.

EXAMPLE: EMS Job1: $100.00
EMS Job2: $235.00
TOTAL: $335.00

For example:

================================
Option Explicit
Sub foo()
Const dVar1 As Double = 100
Const dVar2 As Double = 235
Const sFmt As String = "$#,##0.00"

MsgBox ("EMS Job 1: " & Format(dVar1, sFmt) & _
vbLf & "EMS Job 2: " & Format(dVar1, sFmt) & _
vbLf & "TOTAL: " & Format(dVar1 + dVar2, sFmt))

End Sub
====================================

--ron

 

[FSt1]

hi
1. as a singie or double. single should do (4 bytes)
2. msgbox("this is the first line." & vbnewline & "this is the second."
for more just keep adding more vbnewline

regards
FSt1

 

[James]

1) You need to declare the variable as a single or double precision, e.g.

Dim X as Single
Dim Y as Double

You can then use the Format function for when you wish to present the data.

2) You can use VbCrLf. For example

MsgBox X & vbCrLf & Y

This will place a carriage return line feed character after X and print Y
onto the next line.

Hope this helps...

Cheers

James

 

[WLMPilot]

Thanks. Worked great. However I have two more questions in order to tweak
the display.

1) the format I used ("$##0.00) causes the numbers to not line up. If
there is no money made on a job, then I get $0.00, when I need $ 0.00. How
can I get the spaces in there? I tried "$000.00" also.

2). I want to underline between 2nd line and Total. How do I get the
underline in there?

Thanks again!!

Les

 

[JLGWhiz]

Hi Les, For the alignment, check out the Accounting format. For the
underline, you will need to use the Borders property to set the bottom border
of the cell above the total or the top border of the cell with the total to a
double line style.

 

[WLMPilot]

Not sure what you mean about checking Accounting Format. Is there a
different format to use to get it to line properly?

As for the Border Property, can that be used in a Msgbox?

Thanks,
Les

 

[JLGWhiz]

In Excel, the Accounting number format places the currency symbol in the same
place relative to the decimal point, or comma if Euro style, and always
displays two places to the right of the decimal point. Your numbers are
always aligned by cents, thousands, etc. You can do almost the same thing
with a custom format, but the currency sign varies based on the number of
digits in the figure listed.

You can check it out by using Excel, select a range then from the menu
Format>Number>Accounting.

 

[JLGWhiz]

For the Border, no, it cannot be used in a message box. It applies to a
worksheet cell or group of cells. See Borders Property in VBA Help file.

 

[Rick Rothstein \(MVP - VB\)]

You will have trouble getting things to align the way you want in a MsgBox
as the font used by the system is not a fixed-width font; hence, spaces have
a different width than non-spaces do. Another possibility that you can
consider is using a UserForm and constructing a fake message box in which
the text is displayed using a fixed-width font (Courier New for this
example). Here is a fully hard-coded sample for you to how to implement the
concept (you can use it as a guide in making a more flexible one). In the
VBA editor, insert a Module into your project add put this code in its code
window...

'****** Start Module Code ******
Public dVar1 As Double
Public dVar2 As Double
'****** End User Form Code ******

Next, insert a UserForm into your project and put a Label control and a
CommandButton on it. Then copy/paste this code into its code window...

'****** Start User Form Code ******
Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim sFmt As String
Dim fmtDVar As String
Dim fmtDVar1 As String
Dim fmtDVar2 As String
Dim MaxLength As Long
sFmt = "#,##0.00"
fmtDVar1 = Format$(dVar1, sFmt)
fmtDVar2 = Format$(dVar2, sFmt)
fmtDVar = Format$(dVar1 + dVar2, sFmt)
sFmt = String(Len(fmtDVar), "@")
With CommandButton1
.Caption = "OK"
.Move 59.2, 99, 51, 24
End With
With Me
.Caption = "Totals"
.Height = 152.25
.Width = 176.25
End With
With Label1
.Move 9.75, 11.25, 150, 75.75
.Font.Name = "Courier New"
.Font.Bold = True
.Font.Size = 10
.Caption = "EMS Job 1: $" & Format(fmtDVar1, sFmt) & vbLf & _
"EMS Job 2: $" & Format(fmtDVar2, sFmt) & vbLf & _
" " & String(Len(fmtDVar) + 1, "=") & vbLf & _
"TOTAL: $" & Format(fmtDVar, sFmt)
End With
End Sub
'****** End User Form Code ******

Finally, double-click the worksheet name for the worksheet you want to get
your values from (assumed to be A1 and B1 for this example) in the Project
Explorer window (Ctrl+R if it is not showing) and copy/paste this code into
the code window that appeared when you double clicked it...

'****** Start Worksheet Code ******
Sub ShowPrices()
dVar1 = CDbl(ActiveSheet.Range("A1").Value)
dVar2 = CDbl(ActiveSheet.Range("B1").Value)
UserForm1.Show
End Sub
'****** End Worksheet Code ******

Now, go to this worksheet and type a value into both A1 and B1. Then, press
Alt+F8, select the ShowPrices macro from the list and Run it. Try other
values in A1 and B1 and run the macro to see how the display adapts to your
entered numbers. This macro can eventually be assigned to a some action
initiated from the worksheet (a button, a right-click event on the
worksheet, or some other method).

Rick

 

[Ron Rosenfeld]

Thanks. Worked great. However I have two more questions in order to tweak
the display.

1) the format I used ("$##0.00) causes the numbers to not line up. If
there is no money made on a job, then I get $0.00, when I need $ 0.00. How
can I get the spaces in there? I tried "$000.00" also.

Well, you really can't do it properly because you are dealing with a
proportionally spaced font, and <space> is not as wide as a digit. Space's and
commas are about 1/2 the width of a digit, so you could right-justify the
MsgBox and then pad with twice the number of spaces as there are characters in
the numeric string. This gets you close but not exact. Maybe close enough?

2). I want to underline between 2nd line and Total. How do I get the
underline in there?

Again, you can't underline the font in a message box. About the best you can
do is draw a line of the proper length. That will give you an "extra" line,
though, rather than an underlined line.

Something like below will do what I've described:

=============================================
Option Explicit
Sub foo()
Const dVar1 As Double = 930
Const dVar2 As Double = 2
Const sFmt As String = "#,##0.00"
Dim res As String

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

dL = Application.WorksheetFunction.Max( _
Len(Format(dVar1, sFmt)), _
Len(Format(dVar2, sFmt)), _
Len(Format(dVar1 + dVar2, sFmt))) + 3


With Application.WorksheetFunction
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))))
End With

res = MsgBox("EMS Job 1:" & d1pad & Format(dVar1, sFmt) & _
vbLf & "EMS Job 2:" & d2pad & Format(dVar2, sFmt) & _
vbLf & Application.WorksheetFunction.Rept("_", Len(d3pad)) & _
vbLf & "TOTAL:" & d3pad & Format(dVar1 + dVar2, sFmt), vbMsgBoxRight)


End Sub
================================================
--ron

 

[Rick Rothstein \(MVP - VB\)]

Thanks. Worked great. However I have two more questions in order to

Well, you really can't do it properly because you are dealing with a
proportionally spaced font, and <space> is not as wide as a digit.
Space's and
commas are about 1/2 the width of a digit, so you could right-justify the
MsgBox and then pad with twice the number of spaces as there are
characters in
the numeric string. This gets you close but not exact. Maybe close
enough?

Again, you can't underline the font in a message box. About the best you
can
do is draw a line of the proper length. That will give you an "extra"
line,
though, rather than an underlined line.

Something like below will do what I've described:

=============================================
Option Explicit
Sub foo()
Const dVar1 As Double = 930
Const dVar2 As Double = 2
Const sFmt As String = "#,##0.00"
Dim res As String

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

dL = Application.WorksheetFunction.Max( _
Len(Format(dVar1, sFmt)), _
Len(Format(dVar2, sFmt)), _
Len(Format(dVar1 + dVar2, sFmt))) + 3


With Application.WorksheetFunction
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))))
End With

res = MsgBox("EMS Job 1:" & d1pad & Format(dVar1, sFmt) & _
vbLf & "EMS Job 2:" & d2pad & Format(dVar2, sFmt) & _
vbLf & Application.WorksheetFunction.Rept("_", Len(d3pad)) & _
vbLf & "TOTAL:" & d3pad & Format(dVar1 + dVar2, sFmt), vbMsgBoxRight)


End Sub
================================================

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

Rick

 

[Ron Rosenfeld]

Well, you really can't do it properly because you are dealing with a
proportionally spaced font, and <space> is not as wide as a digit. Space's and
commas are about 1/2 the width of a digit, so you could right-justify the
MsgBox and then pad with twice the number of spaces as there are characters in
the numeric string. This gets you close but not exact. Maybe close enough?



Again, you can't underline the font in a message box. About the best you can
do is draw a line of the proper length. That will give you an "extra" line,
though, rather than an underlined line.

Something like below will do what I've described:

This response gives a better and more consistent alignment than did my previous
attempt. I have a better way of computing the "padding".

(There is still the problem with underline, though):

=============================================
Option Explicit
Const sFmt As String = "#,##0.00"
Sub foo()
Const dVar1 As Double = 9930
Const dVar2 As Double = 0

Dim res As String

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

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 & Application.WorksheetFunction.Rept("_", 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 = " $" & Application.WorksheetFunction.Rept(" ", PadLength)
End Function
====================================


--ron

 

[Ron Rosenfeld]

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>.
--ron

 

[Rick Rothstein \(MVP - VB\)]

If you make dVar1 = 1234, the alignment of the text and $ sign doesn't

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

 

[Ron Rosenfeld]

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