« Fog Creek Software
A New Daily Dose Look »

Excel Formula Formatter

1st April 2005, 09:45 pm by Rob van Gelder

A few weeks ago I came across a post on the blog of Eric W. Bachtal. He has written an Excel Formula Parser in Javascript. All I could think was.. WOW!
If you haven’t seen it yet, you should click here

I’ve always wanted to build a fully blown expression parser but didn’t really know where to start.
There is some great information in that post - the extra reading was helpful too.

So, I set about writing my own in VBA using the points and methods learned from that post. There sure are a lot of things to think about. Here is some of the trickery I encountered…

  • A space is not always whitespace, sometimes it’s an Intersect operator
  • The text either side of a colon are not always cell references. Sometimes they are numbers (eg. $25:26)
  • A plus is not always a plus, sometimes it’s a unary operator, sometimes a binary operator, sometimes the significant figure in scientific notation. eg. 12E+20

    • A formula parser-tokeniser is not very useful unless you do something with it - so I’m starting with something simple:
    An add-in for formatting and editing a formula. I think they call it a ‘beautifier’.
    Do a search on this website for “ugly formula” and you’ll see where this tool might be handy.

    I intend to build more around the add-in as time permits. Perhaps an advanced search/replace tool? FindLinks? MultiWkb/MultiSheet Precedents grapher? Who knows.

    I tried my best to accept International Regional Settings - no guarantees there.
    F9 will evaluate a portion of the formula too.

    It’s still beta as version 0.9.

    Download AudXL.zip from my website: http://www.vangelder.co.nz/excel

    Category: Worksheet Functions, Add-ins  |  Comment (RSS)  |  Trackback

    21 Comments

    1. Eric W. Bachtal:

      This is great - nice job! I’ll be loading this on startup from now on, and looking forward to version 1.0!

      2 April 2005, 8:12 am

    2. Juan Pablo González:

      Great job Rob !

      I really like what you did. I’ll try it one some ugly formulas to see how it does :)

      2 April 2005, 9:06 pm

    3. jkpieterse:

      That looks interesting indeed.

      It might come in handy in combination with my Name manager too. (www.jkp-ads.com/OfficeMarkerPlaceNM-EN.htm)

      Would using the treeview control be an idea?

      3 April 2005, 5:18 am

    4. jkpieterse:

      Hi Rob,

      Found the first (little) problem: If the formula it long and hence doesn’t fit in the textbox, the vertical scroll bar doesn’t show automatically so it looks as if you can only view a part of the formula.

      Suggestions:

      1. Make the form resizable.
      2. Use tooltips

      Questions:

      1. If I edit the formula in the textbox, when will it update to the cell(s)?
      2. What is the empty textbox at the bottom for?

      3 April 2005, 5:27 am

    5. doco:

      [quote]
      Would using the treeview control be an idea?
      [/quote]
      [quote]
      1. If I edit the formula in the textbox, when will it update to the cell(s)?
      2. What is the empty textbox at the bottom for?
      [/quote]

      Is there a utility that will do the reverse? That is turn VBA macros into formulas?

      This is cool! :-D

      3 April 2005, 9:31 am

    6. Rob van Gelder:

      JK: The Textbox scrollbar problem. I've seen it, I can't work it out to fix. I may have to work around. The treeview is an option.
      The textbox at the bottom contains status messages and the results of pressing F9 on the whole or part of the formula.
      I don't really want to touch Names just yet. Your Name Manager add-in is an excellent, polished, mature product. Anything I write would simply be imitation. That said, I'll likely bring in Names formulas as an option.

      Doco: What you are suggesting doesn't sound like a huge leap from what is already there. I'll sleep on it.

      Cheers,
      Rob

      3 April 2005, 1:49 pm

    7. jkpieterse:

      Hi Rob,

      I was thinking in the direction of vastly improving the analyse name dialog that's currently in the Name Manager, which has a very crude parser. Maybe we could include (some of) your logic into the NM.
      I wasn't suggesting you to rewrite the NM .

      4 April 2005, 12:43 am

    8. Eric W. Bachtal:

      Had a reason to use this tonight and noticed that the "Write to Selection" button is dropping the quotes around literal text tokens.

      I'll second the suggestion above regarding a treeview. I think that would be a great presentation mechanism.

      Also, thanks for the tip on the JKP name manager. Looks like a real time saver. Can't wait to try it out.

      4 April 2005, 12:52 am

    9. Rob van Gelder:

      Doco: I just re-read that comment - VBA to Formulas. No thanks :)

      4 April 2005, 2:47 am

    10. Michael Markov:

      Rob,
      Very nice add-in to have when tracing errors!
      However, if you select part of the formula and CTRL + you get strsnge behavior: instead of evaluating the selected item, you toggle between normal and a very small font. Evaluating the selected item would be more useful.
      I would like to look at the code - what is the project password?

      4 April 2005, 9:08 am

    11. Rob van Gelder:

      Eric: a new version will be updated later today (NZST)

      Jan Karel: I played with the TreeView last night. It looks sexy but it didn't allow multiline selection. I'll stick with Textbox.
      I did manage to get resizable forms. I thought it would be more difficult!
      Yes, there may be opportunity to enhance your Name Manager. I'll take a closer look.

      Michael: F9 allows selection evaluation.

      I may password unprotect, but I want to wait until some of these issues are ironed out. Theres nothing more annoying than dud source code floating around.

      Cheers,
      Rob

      4 April 2005, 2:09 pm

    12. Rob van Gelder:

      Hi.

      I uploaded version 1.00 to my website this evening.
      It resolves a couple of bugs and introduces a general token viewer.

      The token viewer takes a range selection (over one or more selected worksheets) and presents a list of token values and token types.
      Handy for identifying functions, external workbooks/worksheets, 3d ranges, string occurences, etc...
      Click the column headings for sorts.
      Doubleclick a row to jump to that formula.

      Cheers,
      Rob

      15 April 2005, 4:41 am

    13. DM/Diddy:

      Rob,

      Brilliant tool! I've been using it almost daily for about a year. Thanks!

      One s-m-a-l-l suggestion: I didn't know until stumbling across this page that F9 would evaluate the selected text. Very cool! Knowing that would have saved me hours in my last formula debugging marathon. So maybe include a tooltip or better yet a caption under the textbox at the bottom of the Formula Formatter saying, "(F9 to evaluate selection)". Please :)

      Thanks again, you rock!

      Dave

      29 January 2007, 11:59 am

    14. Rob van Gelder:

      When I eventually get around to purchasing Office 2007, I'll look at producing a compatible formula formatter. At that point I'll introduce changes as you suggest.

      Cheers,
      Rob

      29 January 2007, 12:59 pm

    15. Jason ON:

      I have encounter some problems and wondering if anyone of you who can help me or not. My questions are:

      How can I represent EDI file format in microsoft excel?

      Or

      How can I convert a number e.g. 1.33 to 000000000001.33 (length =15) in mircrosoft excel format?

      Thanks!

      6 January 2008, 9:26 pm

    16. kanwaljit:

      Hi Jason,

      You need to create a custom number format. To do this, select a cell, then go to Format->Cell->Number tab. From the various options available in the "Category" box, select "custom". Now on the right in the "Type" box, write
      000000000000.00 and click "Ok". Now if you type 1.33 in that cell, it will display 000000000001.33

      Regards
      Kanwaljit

      7 January 2008, 11:18 am

    17. Joga:

      Hi, I am working on a library in C++, which will be used to create excel based reports. Initially a report template will be created in excel and saved as xml. Then the programmer will simply fill the data into respective rows/columns in the program. This library will allow the programmer to load the xml template and manipulate the related objects (elements), which includes adding sheets, defining styles, adding/deleting columns, rows and cells etc. Then the output is again saved to a xml file, which can be again opened in excel.

      I am almost done with the other things. Now important thing I need to do is normalize the formule i.e. when a row/column is deleted or insrted affected formule should be changed as they are done in excel application.

      I was wondering if there is any c++ class, which could read the formula and allow the changes, and then reconstruct the formula string.

      Thanks & Regards,

      Joga

      2 May 2008, 4:37 am

    18. Rob van Gelder:

      Joga:

      There are two notations used for referencing ranges. A1 and R1C1.
      Most Excel users work in A1. Excel itself works in R1C1. Try using R1C1, and see if that helps your app.

      For example, I have a formula in C1: =B1*0.5
      Then I insert a column at A:A
      The fomula now reads =C1*0.5
      However, in R1C1 reference style, the formula doesnt change: =RC[-1]*0.5

      (Tools > Options General > R1C1 reference style)

      Cheers
      Rob

      2 May 2008, 5:36 pm

    19. Joga:

      Thanks Rob. I think in Excel xml, it is always in RC format. Am I right? Here the problem is that the templat excel files will be created by the end users. So my library should adapt to whatever the format they have used.

      5 May 2008, 10:33 pm

    20. Brett:

      Rob,

      Where'd your website disappear to?

      13 May 2008, 9:38 am

    21. Rob van Gelder:

      My website is having issues.
      If you are looking for the Excel Add-in for Formula Formatting, click this link

      14 May 2008, 4:11 pm

    Certain comments are subject to moderation and may not appear immediately. You can use HTML tags in your comment. If you include a greater-than or less-than sign or anything else that could be interpreted as HTML, your comment won't look nice. You need to escape those characters. To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

    Leave a comment