[11011001]

So, I format a cell in Excel as something like "0.000E+00"

the original value is like 3423.44

Now, I want 3.4234E+03 to be what I get from this cell when I copy it to a string, but I get freaking 3423.44!!!

Stupid piece of #($ excel... err....

Any suggestions?

I really don't feel like programming fortran style E formats... I already did that, and well the code is giving some messed up results, therefore i thought it easier to try excels built in stuff.

[Marshall]

Originally posted by 11011001:
Now, I want 3.4234E+03 to be what I get from this cell when I copy it to a string, but I get freaking 3423.44!!!

How are you going about copying it to a string? I assume you're doing this from the VBA macro programming environment.

If you're using someRange.Value (where someRange is the cell you're accessing) try using someRange.Text instead. Beyond that, some sample code would be helpful.

[11011001]

Oi, I did not know you get .text from a cell...

anyways, the really basic code would be

Option Explicit

Sub Main()
Dim sigFigs As Integer
Dim a As Integer
Dim formatString As String
Dim formattedString

sigFigs = 4
formatString = "0."

For a = 1 To sigFigs
formatString = formatString & "0"
Next a

formatString = formatString & "E+00"

ThisWorkbook.Worksheets(1).Cells(1, 1) = 23214.324234
ThisWorkbook.Worksheets(1).Cells(1, 1).NumberFormat = formatString

formattedString = ThisWorkbook.Worksheets(1).Cells(1, 1).Text

Debug.Print formattedString
End Sub

And you can see I used .Text!!! that worked!!! Wooohooo!!!

To make this sub routine even faster (it does this thousands of times), is there a way to format a double to an E formatted string like I am doing... to convert a doubel like say 234234.534534 to string like 2.3434E+05 without writing it to a cell first? (I am working with some input files for a fotran program, which is why I need these funny E-formats, like E10.4 or whatever)

[Marshall]

Originally posted by 11011001:
To make this sub routine even faster (it does this thousands of times), is there a way to format a double to an E formatted string like I am doing... to convert a doubel like say 234234.534534 to string like 2.3434E+05 without writing it to a cell first?

Yes, certainly. Try this:

Code:
Sub Main()

    Dim sigFigs As Long
    Dim formatString As String
    Dim formattedString As String

    sigFigs = 4
    formatString = "0." & String$(sigFigs, "0") & "E+00"

    formattedString = Format$(23214.324234, formatString)

    Debug.Print formattedString

End Sub

Notes:

sigFigs is declared as a Long because Long is VBA's 32-bit data type. The machine is going to use 32 bits regardless of whether you define it as a Long or an Integer, so you might as well not limit yourself to the Integer's smaller size.

The String$() function creates a string of the given length and fills it with the given character, so you don't need a loop for that.

Format$() takes a number and a format string and gives you the result.

You don't have to put the $ at the end of the String$() and Format$() function names, but if you leave it off, VBA will return a Variant instead of a String, which is less efficient. Normally this doesn't matter, but if you're going through a loop many times, every little bit helps.

[11011001]

Way! Cool!!! Thanks...

this thing is going to scream now... before it would take about 5 minutes to get through about 70 sets of data per workbook.... this is good, very good. Oro!