2

I need to enter a series of equations into a VBA function. The equations contain coefficients in scientific notation and I need to keep this notation to allow easy checking of the VBA script.

However, as soon as I enter a scientific number in the VBA editor and hit Return, the number is automatically converted to a floating number. Although accuracy is not effected, readability suffers tremendously and makes checking very hard.

How can I suppress auto formatting in the VBA 7.0 editor?

For example, if I enter the following line in the VBA editor:

e = 3.472e-4 * d + 2.156e-7

and hit Return I end up seeing the following line

e = 0.0003472 * d + 0.0000002156

I need to somehow stop this auto-formatting nightmare.

svenr
  • 37
  • 7
  • It does, but it also worsens readability. – svenr Sep 02 '15 at 01:47
  • 1
    try adding a 0 at the end: `e = 3.472E-40 * d + 2.156E-70` – paul bica Sep 02 '15 at 02:45
  • @PortlandRunner: The Help for "Format Function": **(E- E+ e- e+)**: _Scientific format. If the format expression contains at least one digit placeholder (0 or #) to the right of E-, E+, e-, or e+, the number is displayed in scientific format and E or e is inserted between the number and its exponent. The number of digit placeholders to the right determines the number of digits in the exponent. Use E- or e- to place a minus sign next to negative exponents. Use E+ or e+ to place a minus sign next to negative exponents and a plus sign next to positive exponents_ (but I need confirmation) – paul bica Sep 02 '15 at 02:56
  • @portland - I know, I also tried a couple of calculations and it's not the same result (increases the exponent). I also tried with # and that doesn't help either – paul bica Sep 02 '15 at 03:14

2 Answers2

1

You could make a 1-character wrapper for val like:

Function v(s As String) As Double
    v = Val(s)
End Function

and then use expressions like:

e = v("3.472e-4") * d + v("2.156e-7")

that might minimize the impact on readability

alternatively -- why not just accept what the editor does but have comments that explain the background formula?

Another approach -- use another editor like Textpad for which VBA syntax highlighting is available (in Textpad's case you need to download a VBA syntax definition file from their website to enable highlighting). You'll lose intellisence but it still has some nice features. I haven't used Textpad for VBA since the built-in editor in Excel is adequate for me, but I have used Textpad extensively for some other languages and find it easy to work with. You could edit code in Textpad (or maybe Notepad++) and copy it over to the VBA editor when you want to test.

John Coleman
  • 51,337
  • 7
  • 54
  • 119
  • that's a reasonable way of doing things @John Coleman. I'm just puzzled that this is such a big problem - Matlab, MathCad or Maple simply won't change the input file formatting before compiling, so I never encountered any of these problems. – svenr Sep 02 '15 at 02:23
0

Another solution is to force the distinction between coefficient and order of magnitude in the editor by not using the engineering notation, but powers of ten directly:

e = 3.472 * 10 ^ (-4) * d + 2.156 * 10 ^ (-7)

but I don't know if you think this also compromises readability. An option would be to further break the code down:

e1 = 3.472 * 10 ^ (-4)
e2 = 2.156 * 10 ^ (-7)
e = e1 * d + e2

Just remember, if you do use this notation, to be careful about spacing.

tplobo
  • 1
  • 2