×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel Format

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

RE: Excel Format

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.

RE: Excel Format

(OP)
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

CODE

123456789
  89       (I4)
     89    (I7)
What I need is the = sign in space 20 and tpts to look like this:

CODE

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

RE: Excel Format

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

CODE

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.

RE: Excel Format

(OP)
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.

RE: Excel Format

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.

RE: Excel Format

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:

CODE

ActiveCell.NumberFormat = """NPTS   ""????"
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

(OP)
TYVM

Roger

RE: Excel Format

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.

RE: Excel Format

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: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.

RE: Excel Format

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources