Excel VBA editor issue

user5922 0
Viewed 60

In my years of using Excel VBA I have not seen this before. On a different computer than my usual one, when I type in some code, like the following:

Function Square(x)

   Square=x^2

End Function

Normally the editor will add spaces before and after the equal sign, and spaces before and after the exponent symbol. For the new computer, it does not add the spaces, and it errors because of the lack of spaces. I have to manually add the spaces for it to work, and that slows me down. Any ideas? I assume it is because of some setting, but I can't find it.

vba excel
2 Answers

Short answer

The reason is a ambiguity of ^ symbol - it's used both, for Exponentiation (e.g. x to power of 2) and Declaring LongLong datatype.

This is related to 64-bit Excel (not 32-bit).

When aiming for exponentiation on 64-bit Excel, you could save some time by using space only before ^ symbol:

Square = x ^ 2 '4 spaces
Square=x ^2    '1 space

Longer Explanation

A similar question was raised in 2015 with respect to Excel 2013. QHarr's answer back then was (basically):

in 64-bit Excel versions circumflex character (^) [...] has 2 meanings: A: to designate exponent operation B. designate operand value as LongLong data type.

Current documentation from Microsoft supports this explanation . Notice the difference between:

  • A. Exponentiation - under Arithmetic operations here
  • B. declaring LongLong type here

I like to test things, so I ran the following code on 64-bit Excel 365, resulting in the mentioned output :

Debug.Print 2 ^ 2, VarType(2 ^ 2)
'output: 4, 5

Debug.Print 2^, VarType(2^)
'output: 2, 20

VarType 20 stands for LongLong and 5 for Double (link). In Other words, correct spacing is needed when working with ^ symbol on 64-bit Excel.

simpatak0

In 64-bit Excel VBA, the caret is the type declaration character for the LongLong variable type.

If you have only used 32-bit Excel up to this point, that would explain it. You just need to change your coding habit a little when using the ^ character after a variable.

Nothing needs to be patched. It is working as intended.

Ref: LongLong data type

Ref: Type characters

braX0
Related