Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two short questions
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two short questions
On Sun, 27 Jan 2008 10:43:01 -0800, WLMPilot
wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two short questions
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 "WLMPilot" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two short questions
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two short questions
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 "Ron Rosenfeld" wrote: On Sun, 27 Jan 2008 10:43:01 -0800, WLMPilot wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two short questions
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" wrote: 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 "Ron Rosenfeld" wrote: On Sun, 27 Jan 2008 10:43:01 -0800, WLMPilot wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two short questions
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" wrote: 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" wrote: 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 "Ron Rosenfeld" wrote: On Sun, 27 Jan 2008 10:43:01 -0800, WLMPilot wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two short questions
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 FormatNumberAccounting. "WLMPilot" wrote: 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" wrote: 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" wrote: 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 "Ron Rosenfeld" wrote: On Sun, 27 Jan 2008 10:43:01 -0800, WLMPilot wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two short questions
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. "WLMPilot" wrote: 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" wrote: 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" wrote: 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 "Ron Rosenfeld" wrote: On Sun, 27 Jan 2008 10:43:01 -0800, WLMPilot wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two short questions
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 "WLMPilot" wrote in message ... 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 "Ron Rosenfeld" wrote: On Sun, 27 Jan 2008 10:43:01 -0800, WLMPilot wrote: 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two short questions
On Sun, 27 Jan 2008 11:32:01 -0800, WLMPilot
wrote: 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two short questions
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 ================================================ If you make dVar1 = 1234, the alignment of the text and $ sign doesn't match any more. Because of the thousands separator perhaps? Rick |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two short questions
On Sun, 27 Jan 2008 21:23:21 -0500, Ron Rosenfeld
wrote: On Sun, 27 Jan 2008 11:32:01 -0800, WLMPilot wrote: 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: 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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two short questions
On Sun, 27 Jan 2008 21:36:40 -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. --ron |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two short questions
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 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two short questions
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Answers to questions posing more questions in a workbook | Excel Worksheet Functions | |||
SHORT KEY | Excel Discussion (Misc queries) | |||
View Questions and Answer to questions I created | Excel Discussion (Misc queries) | |||
Three short questions regarding importing of data. See code | Excel Programming | |||
4 short answer questions | Excel Programming |