VBA scientific format of number...

[RobbieC]

Hi there, I have a number: -300.565181720644

I need to output from my VBA code as 'scientific' format (##0.0E+0)

I've found that if I format a cell to ##0.0E+0, the vba outputs to the sheet: -300.6E+0 which is correct

but if I try and format the number within VBA (and not save to cell in sheet) using:

Format(-300.565181720644, "Scientific")

I get just -300

or if I use

Format(-300.565181720644, "##0.0E+0")

I get -300.6

How can I get this to output from VBA to get the result of -300.6E+0 ?

If you can point me in the right direction, I'd be very grateful

Thanks

Rob

 

[steve the fish]

You could use a custom format such as:

With Range("A1")
    .Value = -300.565181720644
    .NumberFormat = "##0.0E+0"
End With

 

[RobbieC]

Thanks for that Steve. This solution does work, but I'm looking to render the value as 'TEXT' so I need the VBA to output as the finished value ie. -300.6E+0

No matter what I do, it always outputs as the full value ie. -300.565181720644 and it's only the cell formatting that puts it into the correct format.

I need the scientific value outputted as text directly from VBA...

Is this possible?...

 

[steve the fish]

That isnt a true scientific number so do you want the true one or your custom one?

 

[RobbieC]

For my purposes, I'm not really interested in the number as it's not going to be used for any further calculations.

If I can convert the characters in the cell (-300.6E+0) to 'text' so that it doesn't convert back to the full number, it'll solve the problem

I'll have a play to see if I can use your cell formatting idea to output my final value.

Thanks Steve

 

[steve the fish]

Excel is going to keep trying to convert that text to a number if it can when you input it. You can either format the input cell to text before you enter the value or maybe use this:

Range("A1").Value = "'" & Format(-300.565181720644, "scientific")

 

[RobbieC]

OOoooh, Steve, you are a legend... I can pull that out as text now!!!!

Out of interest, what does the apostrophe do in the cell?

 

[steve the fish]

Just tells excel you want to use text. If you input '1 into a cell that number will be text not a number.

 

[RobbieC]

Brilliant. Thanks for your help Steve. That's a useful thing to know!