Скачать или смотреть How to Format Scientific Numbers in a Non-Standard Way Using VBA

Learn how to output numbers in a specific non-standard scientific notation using VBA in Excel. This article provides a clear solution to match legacy formats.
---
This video is based on the question https://stackoverflow.com/q/68640718/ asked by the user 'SAL' ( https://stackoverflow.com/u/3099/ ) and on the answer https://stackoverflow.com/a/68640917/ provided by the user 'pgSystemTester' ( https://stackoverflow.com/u/11732320/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: VBA "Scientific" non-standard number format

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Formatting Scientific Numbers with VBA in Excel

In the world of data analysis and number representation, scientific notation is frequently used to denote large or small numbers efficiently. However, there are instances when specific formatting is required to align with legacy systems or custom standards. In this guide, we tackle a unique problem: how to format a number in a non-standard scientific format using VBA (Visual Basic for Applications) in Excel.

The Problem

You might find yourself in a situation where you have to output numbers from Excel into a text format that adheres to non-standard scientific notation. For example, if you want to format the number 6,000,000 to display as 0.600E+ 07 instead of the standard 6.000E+ 06. This formatting is essential for matching the output from older programming languages, such as Fortran.

Standard VBA Formatting

Using the standard VBA format function, one might attempt to achieve the formatting like this:

[[See Video to Reveal this Text or Code Snippet]]

When using the above code for our example number, it produces:

[[See Video to Reveal this Text or Code Snippet]]

Unfortunately, this is not the desired result.

The Solution

To achieve the required non-standard scientific notation, we can manipulate the format. Below, we present a straightforward solution that combines the standard formatting with string manipulation to create the output we need.

Step-by-Step Breakdown

Declare Your Variables: Start by declaring the necessary variables for your number and the formatted string.

[[See Video to Reveal this Text or Code Snippet]]

Format the Number: Instead of using the standard formatting directly, we can concatenate a "0" with the formatted number. This approach will manipulate the exponent correctly so that it appears in the desired format.

[[See Video to Reveal this Text or Code Snippet]]

Print the Result: To see your result, use the Debug.Print function.

[[See Video to Reveal this Text or Code Snippet]]

Complete VBA Code

Here is the complete code you can use in Excel VBA:

[[See Video to Reveal this Text or Code Snippet]]

Output

When you run this code, the output will be:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

By manipulating the standard formatting function in VBA, we can meet specific requirements for number representation in non-standard scientific notation. This approach allows you to interface with legacy systems or custom prescriptions effectively. Now you can confidently output numbers in the desired format from Excel to text files while keeping legacy compatibility in mind.

If you encounter similar formatting problems, consider using string concatenation and VBA’s formatting capabilities to achieve your goals.