Prev: Form question
Next: Calling VB pgm from DOS, thence exiting to DOS after a CHDIR
From: Rick Rothstein on 13 Apr 2010 10:37
I wouldn't use concatenations of formatted text to do this; rather, I would
use the Mid statement (not Mid function) and "stuff" your data strings into
an output string and then assign that output string to your TextBox. I'm
assuming you know the overall width of the output string (you used 25
characters in your sample data) and the positions of the first and second
columns within that output string (taking into account the leading space,
you started the first column at character position 2 and the second column
at character position 11). These assumed values are set in the first string
Const statements and are declared along with the rest of your variables.
Note the declaration of the OutString variable... it is declared as a fixed
length String of as many characters as the overall length of the output
string is to be (we could also have made it a dynamic string and fixed its
length by assign the appropriate number of spaces using the Space function
instead). After you assign your values to the DataItem array elements, you
then use the Mid statement to stuff them into their positions within the
output string. Here is the code structure to do that...

'*************** START OF CODE STRUCTURE ***************
' Constant and Variable Declarations
Const ColumnOneStart As Long = 2
Const ColumnTwoStart As Long = 11
Const OverallFieldLength As Long = 25
Dim Data3 As String
Dim OutString As String * OverallFieldLength
Dim DataItem(1 To 3) As String

' Assign DataItem values
DataItem(1) = "AAAAA"
DataItem(2) = "BBBBBBB"
DataItem(3) = 123.45

' Stuff DataItem values into output String
Mid(OutString, ColumnOneStart) = DataItem(1)
Mid(OutString, ColumnTwoStart) = DataItem(2)
Data3 = Format$(DataItem(3), "0.00")
Mid(OutString, OverallFieldLength + 1 - Len(Data3)) = Data3

Text1.Text = OutString
'*************** END OF CODE STRUCTURE ***************

Note: This "string stuffing" method executes much faster than alternative of
concatenating string values together and is a better method to use,
especially within a long For..Next or Do..Loop looping situation.

--
Rick (MVP - Excel)



"Jack B. Pollack" <N(a)NE.nothing> wrote in message
news:#qzhZ$o2KHA.5880(a)TK2MSFTNGP02.phx.gbl...
> I am trying to format text in a textbox so it is in 3 columns (I'm using a
> fixed width font so it will align correctly).
> The 3rd column is a number. I would like it to be decimal point aligned
> (with possibly 1 leading zero - not 2 or 3 leading zeros).
>
> So I would like the output to be:
> AAAAA BBBBBBB 123.45
> AAA BBBBB 0.11
> AAAAA BBBBBBB 3.20
>
> The code below aligns correctly but gives me 2 extra leading zeros. I
> basically want this output but have the 2 leading digits replaced with a
> space if no digit is present.
>
> Text1.SelText = " " & Format$((DataItem(1), "!@@@@@") & " " &
> Format$((DataItem(2), "!@@@@@@@") & " " & Format$(DataItem(3), "000.00")
> & Chr$(13) & Chr$(10)
>
>
>
> I also tried Format$(DataItem(3), "##0.00")
>
From: GS on 13 Apr 2010 12:36
Awesome! I was wrestling with some way establish predetermined column
widths. Nice idea, - thank you!

Another aspect that occured to me after I posted was how to handle if
the 3rd element's decimal places varied or it was whole numbers. I
realized then that Format() would be the only way to go and so caused
me to rethink my solution. Your solution handles it all very nicely!

Regards,
Garry
--

Rick Rothstein submitted this idea :
> I wouldn't use concatenations of formatted text to do this; rather, I would
> use the Mid statement (not Mid function) and "stuff" your data strings into
> an output string and then assign that output string to your TextBox. I'm
> assuming you know the overall width of the output string (you used 25
> characters in your sample data) and the positions of the first and second
> columns within that output string (taking into account the leading space, you
> started the first column at character position 2 and the second column at
> character position 11). These assumed values are set in the first string
> Const statements and are declared along with the rest of your variables. Note
> the declaration of the OutString variable... it is declared as a fixed length
> String of as many characters as the overall length of the output string is to
> be (we could also have made it a dynamic string and fixed its length by
> assign the appropriate number of spaces using the Space function instead).
> After you assign your values to the DataItem array elements, you then use the
> Mid statement to stuff them into their positions within the output string.
> Here is the code structure to do that...
>
> '*************** START OF CODE STRUCTURE ***************
> ' Constant and Variable Declarations
> Const ColumnOneStart As Long = 2
> Const ColumnTwoStart As Long = 11
> Const OverallFieldLength As Long = 25
> Dim Data3 As String
> Dim OutString As String * OverallFieldLength
> Dim DataItem(1 To 3) As String
>
> ' Assign DataItem values
> DataItem(1) = "AAAAA"
> DataItem(2) = "BBBBBBB"
> DataItem(3) = 123.45
>
> ' Stuff DataItem values into output String
> Mid(OutString, ColumnOneStart) = DataItem(1)
> Mid(OutString, ColumnTwoStart) = DataItem(2)
> Data3 = Format$(DataItem(3), "0.00")
> Mid(OutString, OverallFieldLength + 1 - Len(Data3)) = Data3
>
> Text1.Text = OutString
> '*************** END OF CODE STRUCTURE ***************
>
> Note: This "string stuffing" method executes much faster than alternative of
> concatenating string values together and is a better method to use,
> especially within a long For..Next or Do..Loop looping situation.
>
> --
> Rick (MVP - Excel)
>
>
>
> "Jack B. Pollack" <N(a)NE.nothing> wrote in message
> news:#qzhZ$o2KHA.5880(a)TK2MSFTNGP02.phx.gbl...
>> I am trying to format text in a textbox so it is in 3 columns (I'm using a
>> fixed width font so it will align correctly).
>> The 3rd column is a number. I would like it to be decimal point aligned
>> (with possibly 1 leading zero - not 2 or 3 leading zeros).
>>
>> So I would like the output to be:
>> AAAAA BBBBBBB 123.45
>> AAA BBBBB 0.11
>> AAAAA BBBBBBB 3.20
>>
>> The code below aligns correctly but gives me 2 extra leading zeros. I
>> basically want this output but have the 2 leading digits replaced with a
>> space if no digit is present.
>>
>> Text1.SelText = " " & Format$((DataItem(1), "!@@@@@") & " " &
>> Format$((DataItem(2), "!@@@@@@@") & " " & Format$(DataItem(3), "000.00") &
>> Chr$(13) & Chr$(10)
>>
>>
>>
>> I also tried Format$(DataItem(3), "##0.00")
>>


From: Mike Williams on 13 Apr 2010 16:30
"Jack B. Pollack" <N(a)NE.nothing> wrote in message
news:%23qzhZ$o2KHA.5880(a)TK2MSFTNGP02.phx.gbl...

> I am trying to format text in a textbox so it is in 3 columns
> (I'm using a fixed width font so it will align correctly).

I know you've already got it all covered now, but I just thought I would
throw in my own two pence worth which you might like to also consider. One
way to avoid using fixed width fonts (which often do not look very nice) is
to use one of the few standard fonts in which the space character is exactly
half the width of each of the 0-9 digits (such as Times New Roman or
Palatino Linotype), which will enable you to pad the numbers with two spaces
for each "missing" leftmost digit. Another way, which will allow you to use
any font you want, is to use a RichTextBox instead of a normal TextBox and
to set up your tabs exactly as you want them, using left align tabs for some
items and right align tabs for others (such as your numbers). Here is how to
do it (into a form containing a RichTextBox and a Command Button):

Mike

Option Explicit
Private Declare Function SendMessage Lib "user32" _
Alias "SendMessageA" (ByVal hwnd As Long, _
ByVal uMgs As Long, ByVal wParam As Long, _
lParam As Any) As Long
Private Const MAX_TAB_STOPS As Long = 32
Private Type PARAFORMAT2
cbsize As Long
dwMask As Long
wNumbering As Integer
wReserved As Integer
dxStartIndent As Long
dxRightIndent As Long
dxOffset As Long
wAlignment As Integer
cTabCount As Integer
rgxTabs(0 To MAX_TAB_STOPS - 1) As Long
dySpaceBefore As Long
dySpaceAfter As Long
dyLineSpacing As Long
sStyle As Integer
bLineSpacingRule As Byte
bOutlineLevel As Byte
wShadingWeight As Integer
wShadingStyle As Integer
wNumberingStart As Integer
wNumberingStyle As Integer
wNumberingTab As Integer
wBorderSpace As Integer
wBorderWidth As Integer
wBorders As Integer
End Type
Private Const WM_USER As Long = &H400
Private Const EM_SETTYPOGRAPHYOPTIONS As Long = (WM_USER + 202)
Private Const EM_GETPARAFORMAT As Long = (WM_USER + 61)
Private Const EM_SETPARAFORMAT As Long = (WM_USER + 71)
Private Const TO_ADVANCEDTYPOGRAPHY As Long = &H1
Private Const PFM_TABSTOPS As Long = &H10
Private Const LEFT_TAB As Long = &H0
Private Const CENTRE_TAB As Long = &H1000000
Private Const RIGHT_TAB As Long = &H2000000
Private Const DECIMAL_TAB As Long = &H3000000

Private Sub Command1_Click()
Dim PF2 As PARAFORMAT2, retVal As Long
With RichTextBox1
.Text = "The following are the default tabs:" _
& vbCrLf & "AAA" & vbTab & "BBBB" & vbTab _
& "1.23" & vbCrLf & "AAAAA" & vbTab & "BB" _
& vbTab & "345.67" & vbCrLf & "AA" & vbTab _
& "BBBBB" & vbTab & "67.89" & vbCrLf
End With
retVal = SendMessage(RichTextBox1.hwnd, _
EM_SETTYPOGRAPHYOPTIONS, _
TO_ADVANCEDTYPOGRAPHY, ByVal TO_ADVANCEDTYPOGRAPHY)
If retVal <> 0 Then
RichTextBox1.SelStart = Len(RichTextBox1.Text)
PF2.cbsize = Len(PF2)
SendMessage RichTextBox1.hwnd, _
EM_GETPARAFORMAT, 0&, PF2
PF2.cTabCount = 2
PF2.rgxTabs(0) = 800 Or LEFT_TAB ' twips
PF2.rgxTabs(1) = 2100 Or RIGHT_TAB ' twips
PF2.dwMask = PFM_TABSTOPS
SendMessage RichTextBox1.hwnd, _
EM_SETPARAFORMAT, 0&, PF2
End If
With RichTextBox1
.SelText = "The following are the tabs we just created:" _
& vbCrLf & "AAA" & vbTab & "BBBB" & vbTab _
& "1.23" & vbCrLf & "AAAAA" & vbTab & "BB" _
& vbTab & "345.67" & vbCrLf & "AA" & vbTab _
& "BBBBB" & vbTab & "67.89" & vbCrLf
End With
End Sub



First  |  Prev  | 
Pages: 1 2
Prev: Form question
Next: Calling VB pgm from DOS, thence exiting to DOS after a CHDIR