12

I feel foolish for asking and I'm sure there's a simple answer. I'm trying to get a power for a number as follows:

Sub test()

Dim number As Long

number = 2^8 ' Expect to get 256 here

End Sub

This produces an error: 'Expected: list separator or )'.

I'm surprised this doesn't work. I'm aware that there's an Excel defined power function. But is the ^ operator not supported in VBA - it is in VB6?

FURTHER INFO

If I do the same in the Immediate window, I get the following strangeness (no error):

?2^8
 2  8

It looks like just spaces between the 2 and the 8.

IT WORKS?

So I was messing about with various combinations of symbols and found that in the Immediate window:

?2^^8
 256

What the...? Anyone know why this might be?

MORE EXPERIMENTS

I'm guessing my installation of Excel must be broken (although absolutely everything else works fine). Some further things the Immediate window:

If I insert a space before ^:

?2 ^3
 8

?2!^3
 8

?2"^3
 2 ^3

?2£^3
 2  0  3

?2%^3
 8

?2@^3
 8

?2#^3
 8
Shog9
  • 156,901
  • 35
  • 231
  • 235
Guillermo Phillips
  • 2,176
  • 1
  • 23
  • 40

2 Answers2

17

Confirmed: Also happens in MS Word 2010, 2013, 2016 all 64 bit Office*1

*1 I suspect this applies to most of, if not all, the 64 bit office applications

This seems to be an issue with the 64 bit version of MS Office. In the 32 bit version the caret ^ is used for the power operator, however in the 64 bit operator it is also used for variables of type LongLong (learn.microsoft.com/.../vba/.../longlong-data-type).

In 64 bit VBA, when using Debug.Print*2 the IDE auto corrects 2^2 to 2^; 2 but 2 ^2 is auto-corrected to 2 ^ 2; making the code compile-able.

By way of a solution, this is just one of those annoying things that users of 64bit VBA 'just need to know'.

Edit: Microsoft confirmed issue https://support.microsoft.com/en-gb/help/2454019/the-64-bit-office-2010-vba-compiler-shows-an-error-when-it-encounters

*2 I've also come to learn that the VBA behavior is different when using debug.Print than assigning a variable (thanks to @ErikvonAsmuth)

SlowLearner
  • 3,086
  • 24
  • 54
  • 1
    It's a breaking change, but it's the intended effect of adding the LongLong type, so probably best not to use loaded words like "issue" or "properly corrected". – Ben Voigt Sep 17 '18 at 02:48
  • @BenVoigt lol - fixed, tnx – SlowLearner Sep 17 '18 at 03:14
  • 1
    @BenVoigt hmmm breaking change... not sure on that, the language syntax has not changed; existing code will not stop working... In the 64 bit VBA IDE the auto-correct simply does not work as one may expect from using the 32 bit version. I imagine had this been VS (and not the VBA IDE) that the behavior would be modified fairly quickly; I do see where you're coming from though, but I reckon it could be fixed and the Rubberduck project (http://rubberduckvba.com/) might just do that so I don't think the terms are overly loaded... VBA just needs some love. Cheers – SlowLearner Sep 17 '18 at 03:37
  • I guess you're saying that existing code that's been run through autocorrect in the 32-bit version will not stop working. But existing code that's been edited without autocorrect help might contain `2^y`, and the way that parses has changed (was a binary operator, now a type suffix). – Ben Voigt Sep 17 '18 at 04:13
  • 1
    Basically yes - but on reflection I imagine the biggest trap would probably come from copying and pasting chunks of code from places like SO... – SlowLearner Sep 17 '18 at 04:16
  • It's been a while since I used VBA, but I thought pasting triggered autocorrect (if enabled)? – Ben Voigt Sep 17 '18 at 04:16
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/180171/discussion-between-slowlearner-and-ben-voigt). – SlowLearner Sep 17 '18 at 04:17
  • Thanks for this answer. It's not broken! I shouldn't have been so hasty to close the question. The real question is, what @?!* thought it was a good idea to use a ^ to denote a longlong? Why not use some other symbol. Maybe an @ perhaps? Oh well. – Guillermo Phillips Sep 17 '18 at 09:08
  • Yeah... I was thinking they could have used ```~``` – SlowLearner Sep 17 '18 at 09:13
  • 1
    As a further thing to note. You'll get the same behaviour with other 'overloaded' symbols, such as & and ! . If you leave the space out before the symbol it will be treated as a numerical type instead of an operator. – Guillermo Phillips Sep 17 '18 at 09:24
  • I'd never really noticed that... I often use ```Debug.Print``` and that seems to give different results. Good learning today :-D – SlowLearner Sep 17 '18 at 10:00
  • In response to: https://stackoverflow.com/questions/51264287/vba-power-operator-not-working-as-expected-in-64-bit-vba/52360094#comment91672643_52360094 (what @?!* thought it was a good idea to use a ^ to denote a longlong?), I'd like to draw your attention to this (https://learn.microsoft.com/en-us/office/vba/api/word.selection.goto) and suggest it was the same person. – SlowLearner Sep 19 '18 at 10:51
  • So...this should be considered a "compiler bug" and not an "undocumented feature", right? – jvriesem Mar 09 '22 at 02:06
2

You can use the powerfunction. For example 

Sub Test()
Dim number As Long
number = Application.WorksheetFunction.power(2, 8)
End Sub

But the ^ operator must have been working too .

  • 1
    There should be no need for a developer to resort to a `WorksheetFunction` for one of the languages built-in operators. – jvriesem Mar 09 '22 at 02:05