Excel VBA Editor incorrect parser error.

Question

In Microsoft Visual Basic for Applications 7.1

Version 1123 VBA: Retail 7.1.1123

Module level

The below produces a parser error. That is, typing f=x^2 (no spaces) gives:

Function f(x)

f=x^2
End Function

In other versions that I have tried, the parser error is not produced. That is, typing f=x^2 (no spaces) gives:

Function f(x)

f = x ^ 2
End Function

As shown, the code is correctly spaced and interpreted. The workaround is to type "f=x ^2" with a space between the x and the carrot ^.

Could this be added to the list of problems with Excel 2019? The error initially confused me too!

The error does not occur when operators + - / * are used.

In another build, Excel 2019, and also in 2016, the typed expression is interpreted and reformatted correctly. To summarize:

Office 2019, Visual Basic for Applications 7.1 Version 1123  (ERROR for f=x^2, typing f=x^2 (no spaces) does not reformat and code changes to red color) OS Win10 Office 2019,  Visual Basic for Applications 7.1 Version 1121  (no error, typing f=x^2 is correctly reformatted to f = x ^ 2). OS Win11Office 2016, Visual Basic for Applications 7.1 Version 1048  (no error, typing f=x^2 is correctly reformatted to f = x ^ 2). OS Win7

HOWEVER, a colleague of mine reported the following (confirming a bug but in Version 1121):

Microsoft 365 Apps for enterprise, Visual Basic for Applications 7.1 Version 1121", and I found ERROR for f=x^2. Typing f=x^2 does not reformat and changes to red color, but the other calculations (*,/,-, and +) are correctly reformatted. My VBA version is same as Office 2019, Visual Basic for Applications 7.1 Version 1121, but it doesn't work properly on my computer. OS is "Microsoft Windows 11 Home.

Evidently the VBA parser has unknown versions that do not match the VBA version. Is it worth it to make a poll? Or check your machine? Possibly a developer can mention it to Microsoft. It is an unfortunate bug that wastes time in teaching science and engineering with VBA.

Answer 1

Hello and thanks for your advice. The problem noted above is in the VBA Editor before execution.

Please type

f=x^2

(and enter) without spaces in VBA editor as part of a function or subroutine. If you have a version that correctly parses the statement, it will space out and format as:

f = x ^ 2

and the statement will execute properly as in your example.

Unfortunately, if one has a VBA editor with (possibly) a different build, the statement will turn red (i.e. format error):

f=x^2

and the statement will neither space out nor execute.

I have several machines and some do parse it correctly (like yours) and some machines do not. It seems that newer machines have this problem. We are uncertain about the root of the problem.

The "workaround" is to type:

f=x ^2

that is, a space AFTER the x and then the text will be reformatted after hitting enter as:

f = x ^ 2

and the statement will be correctly parsed and interpreted. This is not intuitive (or proper) for an editor and especially not for teaching. Strange, but this problem needs attention as we have noticed it on a number of computers.

Answer 2

Re: somethings wrong?

'---

Function f(x)
f = x ^ 2
End Function

Sub testing()
Dim temp, answer

temp = 3
answer = f(temp)

MsgBox answer
End Sub

'---

Nothing Left to Lose