EXCEL VBA: Scientific Notation in a Userform Textbox

I am developing a userform in EXCEL using VBA. One of the default values for a user input in the form is 1E-12. I need to display this in the userform. However, when I initialize the textbox in the form, EXCEL immediately changes the representation of the value to 0.000000000001 (Both in the editor and in the userform when I check it). Computationally, of course, this is not an issue, however, it is a little tough to read for the user (and takes up to much space in the form to display the whole number). Is there a way to force EXCEL to show this value in scientific notation in the userform textbox?

Thanks!

Dan

Solution

You can use the VBA.Strings.Format$ function to control the string representation of numbers.

Specifying a "Scientific" format string will sort-of work, but if you need to control the level of precision, you'll need a more elaborate format string. You can make a FormatScientific function to abstract away that complexity:

Public Function FormatScientific(ByVal value as Double, Optional ByVal precision As Long = 6) As String
    If precision < 1 Then precision = 1
    Dim formatString As String
    formatString = "0." & String(precision - 1, "0") & "#e-#"
    FormatScientific = Format$(value, formatString)
End Function

Here I made the precision optional parameter be 6 if unspecified; adjust as you see fit.

Now you can invoke FormatScientific(0.0000123456789) and get 1.234568e-5 with a consistent number of significant digits, regardless of what you give it, whereas a "Scientific" format string only gives you 1.23E-05, which loses quite a lot of the significant information.

(adapted from parts of this code)