1

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.

braX
  • 11,506
  • 5
  • 20
  • 33
user5922
  • 11
  • 1
  • I suppose the x may have to be in brackets? (x)^2 – Davesexcel Sep 13 '22 at 21:02
  • Normally I don't have to do that. Maybe that version of Excel needs a patch. – user5922 Sep 13 '22 at 21:31
  • The code is ok. To test if an add-in might be acting on the VBE, you can open a clean session of Excel with `WIN+R` on Windows, then type `Excel /s` and try to edit the VBE as usually. If it's ok, something might be acting on the VBE and you'll have to close this instance and normally open a new one. So go into Excel Options and selectively disable each one that might be active. A kind of trial and error. – ALeXceL Sep 13 '22 at 22:15
  • It looks like it's treating `^` as a type declaration character. If you use `*` instead, it adds the spaces. (not that it solves the problem, it just helps you identify it) - `Square=x ^2` works as expected. – braX Sep 13 '22 at 22:20
  • There is not auto format option in excel VBE. It is baked into the program. – Sorceri Sep 13 '22 at 22:42
  • Does this answer your question? [type-declaration character does not match declared data type](https://stackoverflow.com/questions/25172899/type-declaration-character-does-not-match-declared-data-type) - In 64-bit Excel VBA, the caret is the type declaration character for the LongLong variable type. – braX Sep 13 '22 at 23:13

2 Answers2

1

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.

simpatak
  • 86
  • 11
0

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

braX
  • 11,506
  • 5
  • 20
  • 33
  • 1
    Sorry I was out sick for a couple days. I will give these answers a try. I think it is a 64 bit issue because I have only been using 32 bit on my computer. – user5922 Sep 16 '22 at 13:22
  • Thanks for the help. I understand what is going on now. I was looking at Youtube videos, and the ^ implicit type declaration for LongLong was the only special character that was not mentioned. So it must be a fairly new thing for Excel to be 64 bit. – user5922 Sep 22 '22 at 17:03