Format(1000, "##0.000E+00") not working to format as engineering type

[gijimbo]

When I use the custom number format below, I get 100.000E+01.
Every help file and website I can find says the result should be 1.000E+03 which is what I want.

Debug.Print Format(1000,"##0.000E+00")  ' Yields 100.000E+01
Debug.Print Format(1000,"000.000E+00")  ' Yields 100.000E+01

Shouldn't these 2 lines yield different results? This is killing me.

 

[Joe4]

This works for me:

Debug.Print Format(1000,"0.000E+00")

 

[gijimbo]

Thanks for the quick response!
Unfortunately that won't always generate results in engineering format, just scientific format.
For example the number 20,000 should look like 20.000E+03.
Format(20000,"0.000E+00") will yield 2.000E+04.

 

[Joe4]

Unfortunately that won't always generate results in engineering format, just scientific format.
For example the number 20,000 should look like 20.000E+03.
Format(20000,"0.000E+00") will yield 2.000E+04.

Sorry, I was not familiar with the Engineering format.

I came across this article. Perhaps it will help:
https://excelribbon.tips.net/T012874_Engineering_Calculations.html

 

[gijimbo]

Yeah, that's the crazy thing to me. I use the ##0.0E+00 format all the time in Excel and it works fine. For whatever reason, when I use the Format() function in VBA it's not working the same. Even the Microsoft site shows that it should work... that's what's really frustrating me.
https://docs.microsoft.com/en-us/of...format-function-visual-basic-for-applications

 

[Joe4]

Excel functions and VBA functions are not the same, and do not always work the same.

Microsoft site shows that it should work... that's what's really frustrating me.
https://docs.microsoft.com/en-us/off...r-applications

I was looking through that article, and I could find that. I even did a search on "engineer" and it found no matches.
Perhaps I just missed it. Where does it show that?

 

[gijimbo]

Part way down where they show examples there are two listed that work with the '#' they way they say they should.

' User-defined formats.
MyStr = Format(5459.4, "##,##0.00")    ' Returns "5,459.40".
MyStr = Format(334.9, "###0.00")    ' Returns "334.90".

When I run the following, my results are the same as theirs.

Debug.Print Format(5459.4, "##,##0.00")
Debug.Print Format(334.9, "###0.00")

Then further down, under "User-defined numeric formats" they give the following explanation of the # symbol:
Digit placeholder. Display a digit or nothing. If the expression has a digit in the position where the # appears in the format string, display it; otherwise, display nothing in that position. This symbol works like the 0 digit placeholder, except that leading and trailing zeros aren't displayed if the number has the same or fewer digits than there are # characters on either side of the decimal separator in the format expression.

So it seems like once you introduce the E+00, it forgets how the # is supposed to work and it treats it like a '0'.

 

[Joe4]

So it seems like once you introduce the E+00, it forgets how the # is supposed to work and it treats it like a '0'.

If there is no digit to fill it because it is less than the value, it will pad fill it with a zero.

I believe that the issue you are having is there does not appear to be a way to tell the formatting to work only in multiples of E+03.
If you numbers were all of the same order of magnitude, I think you could come up with a single format to work. But because they are not, and you want them all in multiples of E+03, I don't think there is a simple way to do it.
You will probably need to create a User Defined Function in VBA to do it.

 

[gijimbo]

Well shoot, I thought there must have just been something missing in how I was trying to format it.
It still seems really crazy to me that it works differently in VBA than it does in a worksheet. The function works the same as it does in WS formating in almost every way I try except when I add the E+00. I'm guessing it was an oversight when they wrote the function.

Thanks again for trying to help.

For the moment I'm going to just roll with good-ol' scientific notation and add code to convert the numbers I was trying to compare with (that are acquired from a LAN or COM port) to scientific notation as well. (They're being sent in eng format)

 

[Joe4]

Yeah, there are many Excel functions that don't appear in VBA, and then some that are similar that go by different names (i.e. the "Text" Excel function is similar to the "Format" VBA function).
And many of the Date functions are different (TODAY() vs. DATE()), etc.