Excel Format

[rnordquest]

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

 

[electricpete]

I can't recall what I4 means. Does this do what you want?

ActiveCell.Value = "NPTS = " & Format(tpts, "0")

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[rnordquest]

Nope, it doesn't work.

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

123456789
  89       (I4)
     89    (I7)

What I need is the = sign in space 20 and tpts to look like this:

...= 89
...=  5
...=167

 

[electricpete]

OK, how about ActiveCell.Value = "NPTS = " & iformat(tpts, 4)
where iformat is a user-defined function as follows:

Function iformat(inputnumber As Double, desiredspaces As Integer) As String
' 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.

 

[rnordquest]

Holy crap batman. Do you mean I just thought of a basic simple format that Bill Gates didn't? Wow, all of that just to put a value in a certain spot. Thanks.

 

[electricpete]

There may be a way to do it with excel's built-in formats. It was easier for me to write that code than try to figure out those formats.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[joerd]

You can get the same effect by formatting the cell as "NPTS "????
This format code just doesn't work for VBA Format conversions. You could write code like:

ActiveCell.NumberFormat = """NPTS   ""????"
ActiveCell.Value = tpts

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[rnordquest]

TYVM

Roger

 

[electricpete]

I figured there would be a way.

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.

 

[joerd]

Smart thinking to use Application.Text, I always use Format in VBA.
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 for tips on how to make the best use of Eng-Tips.

 

[rnordquest]

You are correct; the number of points is always positive. One line or two won't have much effect on processing time for the few thousand lines I have to process but less lines is usually more better in my book.

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.