Why do I need a space before the '^' operator in VBA for Excel 2013 or it will produce a compile time error "Expected list or separator"

Question

If I type for example Sqr(a ^ 2 + b ^ 2) there is no error. But when I type Sqr(a ^ 2 + b^ 2) it produces a compile error which I do not understand:

What is the function of ^ in VBA7 as opposed to _^ (underscore to show space) which denotes exponentiation?

VBA Editor should add the extra spaces between `r ^ 2` for you. If it's not doing that there is something wrong it but what's actually wrong I don't know :O — , Mar 13 '15 at 15:26
No repro in Excel 2010, so this must be an Excel 2013 issue. — Jean-François Corbett, Mar 13 '15 at 15:27
Well I was expecting for the editor to add the spaces, but it doesn't. It produces the compile error. The error comes when I type `r^ 2` also. I have 64-bit Office 2013 — John Alexiou, Mar 13 '15 at 15:41

score 3 · Accepted Answer · answered Jul 07 '18 at 08:59

This is a 64 bit issue.

^ can confuse the compiler as to whether an operator versus operand value as LongLong is being used.

This problem occurs because the circumflex character (^) is ambiguous in this context. For 64-bit versions of VBA, the circumflex has two meanings:

It can designate an exponent operation, such as x to the power of y ("x^y").
For 64-bit Office 2010 VBA editions only, it can designate that an operand value should be treated as a LongLong, 64-bit integer value data type (for example, "234^").

Consider the following scenario:

You have the 64-bit edition of Microsoft Office 2010 installed on your computer. In the Visual Basic IDE, you create a new project.

In your Visual Basic for Applications (VBA) code, you type a statement that resembles the following:

LongLongVar2 = LongLongVar1^IntegerVar

When you type x^y in the 64-bit Office 2010 VBA editions, the VBA IDE editor does not know how to interpret the "x^" part of the string. In this context, "x^" can be treated as being the LongLong data type. If "x^" is interpreted in this manner, the string will produce an error because there is no operator that is specified for the "y" value to apply to the "x" value. If you type the string by using a space between the value name and the circumflex (for example, x ^y), you indicate that you intend the symbol to be an operator and not a data type designator. By doing this, you can avoid the error condition. Therefore, the example expression ... should be rewritten as follows:

LongLongVar2 = LongLongVar1 ^ IntegerVar

So `2^^2=4` as it will be interpreted as `(2^)^2 = (2)^2 = 4`? — John Alexiou, Jul 07 '18 at 19:05
So, if I want to raise `foo` to the `bar` power, where both are of type `Double`, how does that happen? — jvriesem, Mar 09 '22 at 02:09

score 0 · Answer 2 · answered Aug 10 '22 at 16:14

Using ^^ seems to still have some conflicts:

?(-1)^^2 
"Compile Error: Expected: expression"

But I've found enclosing both terms in parentheses for ^ seems to work.

?(-1)^(2)
1

Same with variables:

foo = -1
bar = 2
?foo^^bar
"Compile Error: Type-declaration character does not match declared data type"

(foo)^(bar)
1

Why do I need a space before the '^' operator in VBA for Excel 2013 or it will produce a compile time error "Expected list or separator"

2 Answers2

Linked

Related