Excel Format
Excel Format
(OP)
How do I get this to work?
ActiveCell.Value = "NPTS = " & format(tpts, i4)
I want the I4 to be like a fortran I4 format, i.e. right justified
ActiveCell.Value = "NPTS = " & format(tpts, i4)
I want the I4 to be like a fortran I4 format, i.e. right justified





RE: Excel Format
ActiveCell.Value = "NPTS = " & Format(tpts, "0")
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Excel Format
I4 is how you write out an integer right justified in a field 4 spaces wide.
If tpts = 89 then examples for I4 and I7 are
CODE
89 (I4)
89 (I7)
CODE
...= 5
...=167
RE: Excel Format
where iformat is a user-defined function as follows:
CODE
' Output iformat is a string representing integer, left-padded with spaces to specified length
' Input - desiredspaces - how many spaces total we want the output number (including minus sign)
' Input - inputnumber floating point/double number - will be converted to integer
' program generates an error if desiredspaces is not large enough
Dim length As Integer ' tells how many characters will be needed to display the input number
Const notEnoughRoom = 999 ' the error number if desiredspaces is not enough
Dim extraSpacesNeeded As Integer ' how many extra spaces do we need
Dim extraspaces As String ' holds the extra spaced needed for left-padding to desired length
Dim iCounter As Integer ' loop counter
If inputnumber = 0 Then
length = 1
ElseIf inputnumber > 0 Then
length = Fix(Application.Log10(inputnumber)) + 1
Else
length = Fix(Application.Log10(Abs(inputnumber))) + 2
' added 1 for the minus sign
End If
If length > desiredspaces Then Error (notEnoughRoom)
extraSpacesNeeded = desiredspaces - length
extraspaces = ""
For iCounter = 1 To extraSpacesNeeded
extraspaces = extraspaces & " "
Next iCounter
iformat = extraspaces & Format(inputnumber, "0")
End Function
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Excel Format
RE: Excel Format
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Excel Format
This format code just doesn't work for VBA Format conversions. You could write code like:
CODE
ActiveCell.Value = tpts
Cheers,
Joerd
Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: Excel Format
Roger
RE: Excel Format
As you say, the vba format command is totally confused by the "?":
? format(3,"0") gives 3
but..
format(3,"?") gives ?
(weird).
Unlike format, the excel text command recognizes ?. So we could eliminate one line by using:
Activecell.value = Application.text(npts, """NPTS ""????")
But I don't understand exactly what all those double quotes are doing. Can you explain?
A minor point – that format string doesn't work right for negative numbers. Should be easy enough to fix with the right combination of format strings separated by semicolons, but I couldn't get it to work. I'm guessing a variable like "npts" is always positive anyway.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Excel Format
The format string should have quotes around it, or VBA gets confused. So that's where the outermost quotes are for. But a single quote inside the string would terminate is (VBA would think it is the end of the string) so the ugly, but historical, solution is to type it twice. The actual format is "NPTS "????
I have to admit that I recorded the macro while I was formatting the cell, so the macro recorder took care of it.
I suppose the correct full format string would be "NPTS "????;"NPTS "-???? which becomes with the extra quotes: """NPTS ""????;""NPTS ""-????" but I didn't test so there could be a typo. If you want you can add another section (after another semicolon) if you want something for zero values, too.
Cheers,
Joerd
Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: Excel Format
I'll just comment one method out so I can keep both methods. Someday when I'm smarter I might even figure out how they really work. Of course if I ever do get smarter I won't need to work anyway.