Format/field width specifier for UserForm TextBox

Special K

Board Regular
Joined
Jun 20, 2011
Messages
83
Office Version
  1. 2010
Platform
  1. Windows
I have a TextBox on a UserForm that displays the results of calculations done inside the UserForm's code. I build up a string using the concatenate operator along with spaces (" ") and the values. When all calculations are finished, I print a report similar to the following:

Code:
input     output 1    output 2    output 3
-------------------------------------------
input 1   value 1-1   value 1-2   value 1-3
input 2   value 2-1   value 2-2   value 2-3
...
input N   value N-1   value N-2   value N-3

The problem is that the length of input 1, input 2, input N strings can be variable, but I still want all columns to line up in the TextBox. The way to solve this using C/C++ would be to use a field width specifier such as the following:

Code:
printf("%-20s%-10s%-10s\n", value1[1], value1[2], value1[3]);

Where an appropriate field width is chosen based on the expected size of the strings.

Does something similar exist in Excel VBA? I suppose I could manually go through each string and pad the end with as many spaces as it would take to give the desired length, but that seems kind of tedious.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Strings can be padded automatically. Make sure to use a mono spaced font such as Courier New.

Code:
Private Sub CommandButton2_Click()
Dim v$(0 To 3), h$(0 To 3), w, i%, n%
w = Array(8, 15, 20, 30)                ' field widths
h(0) = "Input": h(1) = "Output1": h(2) = "Output2": h(3) = "Output3"
v(0) = "s0": v(1) = "string1": v(2) = "string2string2": v(3) = "string3string3string3"
n = 0
For i = LBound(w) To UBound(w)
    n = n + w(i)
    h(i) = Pad(h(i), w(i))
    v(i) = Pad(v(i), w(i))
Next
Me.TextBox15 = h(0) & h(1) & h(2) & h(3) & vbLf & WorksheetFunction.Rept("-", n) & _
vbLf & v(0) & v(1) & v(2) & v(3)
End Sub[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]Public Function Pad$(ByVal os$, ByVal rl%)
Pad = Format(os, String(rl, "@"))
End Function
[/FONT][/COLOR]
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top