Backus–Naur Form (BNF) of Excel formulae

[ChrisGreaves]

I found some excellent web pages for anyone (like me) interested in parsing formulae in Excel cells, preferably with Backus-Naur form.
Apart from playing with Rob Van Gelder's tokeniser ("Wheeeee!"), I've not yet worked my way through the material.
Formula Tokeniser
Excel Grammar
Excel Formula parsing
Where am I going with this? An enhanced version of STAIN.

[HansV]

Have you tried applying Edsger Dijkstra's shunting-yard algorithm to Excel formulas?

(See Shunting-yard algorithm on Wikipedia)

[ChrisGreaves]

Have you tried applying Edsger Dijkstra's shunting-yard algorithm to Excel formulas?

Yup. Years ago when I was engaged in the compiler/interpreter writing business. We got to the point where we could be handed a language description in BN form (e.g. A language to support a new application for the Offset printer crowd) at 9am, and had a working compiler, compiled to run on an ICL1903A, ready to ship out the door at 5pm.
It was a chain of state-tables, transition matrices etc. The first version ran on what was basically a punched-card implementation of APL - which we had written. Given the speed of those days machines, and the unpredictable job queue, it was in all a minor miracle.

I took a closer look at one of the links above. Rob Van Gelder's tokeniser. If I've read it correctly it's a super-fast way to play around converting cell references to range names and vice-versa.

[Jan Karel Pieterse]

My reftreeanalyser (free demo version) tries to parse cell formulas too:
http://www.jkp-ads.com/reftreeanalyser.asp

[ChrisGreaves]

My reftreeanalyser (free demo version)

Hi Jan Karel.
I have broken your analyzer.
I got the same result (image below) with two separate workbooks.
Please let me know here, or via PM or email, how I can best help you to resolve the issue.
I am (on this machine) WinXP-SP3 Office 2000.

Code: Select all

05/18/2010 10:38:50 AM      RefTreeAnalyserDemo         Error 13: Type mismatch in modMenu.CreateMenu
05/18/2010 10:44:30 AM      RefTreeAnalyserDemo         Error 13: Type mismatch in modMenu.CreateMenu
05/18/2010 10:46:28 AM      RefTreeAnalyserDemo         Error 13: Type mismatch in modMenu.CreateMenu
05/18/2010 10:46:30 AM      RefTreeAnalyserDemo         Error 13: Type mismatch in modMenu.CreateMenu
05/18/2010 10:46:31 AM      RefTreeAnalyserDemo         Error 13: Type mismatch in modMenu.CreateMenu

I have RevoUninstalled the package because I couldn't load Excel without these messages, followed by the familiar "Microsoft Excel has encountered a problem ..."

1.JPG

[Jan Karel Pieterse]

Hi Chris

I have had users report this error before. Never could find out what causes it.
Could you go into the registry and find this key:

HKEY_CURRENT_USER\Software\VB and VBA Program Settings\XLRefTreeAnalyser\Settings

Please tell me what it lists for the toolbar values.

[ChrisGreaves]

Please tell me what it lists for the toolbar values.

Jan Karel: It doesn't!
As far as I can see, it doesn't get as far as the registry settings.
Screen Snapshots
"Under" and "Stain" are my two utilities.
Stain is an Execl application; Under is a Word application.

P.S. PM follows ....

[Jan Karel Pieterse]

Hi Chris,

I fixed the issue, I've updated the downloads on my website.

[ChrisGreaves]

I fixed the issue,

Very good! Might we learn, in a general way, the nature of the bug? I mean in the sense of "I forgot to .." or similar that might have an impact on others of us distributing applications for a variety of office versions. I'm not asking for any proprietary code or secrets here. Just that you mentioned that this had been a (nagging?) problem with other installations.

1.JPG

[Jan Karel Pieterse]

The tool comes with an addin that handles the shortcut keys. In the original version, the COM addin loaded the Excel addin, now I install the excel addin and the com addin does not open the excel addin anymore.

[ChrisGreaves]

... and the com addin does not open the excel addin anymore.

Thanks Jan Karel, for the explanation.
Since I'm not creating COM addins, it's not something I need worry about.

Yet.