Formula Editing Made Easier

I’ve been working a bit more with editing formulas in Notepad++. I changed my xml file names to be different than Bob’s. I’m unable to Remove a user defined language without causing errors and having to reinstall Notepad++. The two files I have are

XLFF.xml – goes in ..Program FilesNotepad++pluginsAPIs
xlf_udl.xml – import from within NP++ under View – User Defined Language

That should give you an entry under languages called XLFF (after you restart NP++). You can download the xml files in a zip folder at the end of this post.

My next step was to put this macro in my UIHelpers.xla add-in. You can put it in your Personal.xls or wherever you store general purpose code.

Sub EditFormulaInNPP()
   
    Dim sForm As String
    Dim lFile As Long, sFile As String
    Dim lLineCnt As Long
   
    If ActiveCell.HasFormula Then
        sForm = ActiveCell.Formula
        sForm = Replace(sForm, Chr$(32), vbTab)
       
        lFile = FreeFile
        sFile = Environ(“TEMP”) & “” & Format(Now, “yyyymmddhhmmss”) & “.xlf”
       
        Open sFile For Output As lFile
        Print #lFile, sForm
        Close lFile
       
        Shell “C:Program FilesNotepad++
otepad++.exe -xlff “
& sFile
    End If
   
End Sub

This code copies the formula from the activecell into a text file, then open it in NP++. All of the CHR(32) carriage returns are replaced with vbTab so that it formats properly in NP++. Once in NP++, the formula can be edited and copied back into Excel. Before pasting in Excel, edit the cell and select everything (F2 – Control+Home – Control+Shift+End – Control+V).

In my Auto_Open and Auto_Close procedures in UIHelpers.xla, I added, respectively

Application.OnKey “^+e”, “EditFormulaInNPP”
Application.OnKey “^+e”

Now I can select a cell, press Control+Shift+e and my formula will open in NP++ for editing. Despite passing the -xlff parameter in the Shell command, I still have to select the XLFF language in NP++.

Next I want to paste the formula back into Excel without line breaks so my Formula Bar doesn’t take up the whole screen. For that, I’ll have to dig through Rob van Gelder’s AudXL Formula Auditor and steal his tokenizing code.

You can download XLFF.zip

Posted in Uncategorized

6 thoughts on “Formula Editing Made Easier

  1. Just parse & insert line breaks in the formulas. That way you keep cell highlighting, intellisense, etc. No need for the roundtrip. Besides, that way you could easily format ALL formulas in a workbook.

  2. Of course you loose brackets collapsing, but intellisense more than makes up for it. And you’d need to change the formula field font to a fixed-width one.

  3. Hi Dick
    Thanks for the link.

    About a year ago, I posted new tokenizer code to DDoE: Formula Tokenizer Post
    The newer one is more robust than the last.

    I should have used regex, but the complexity of the task was daunting, and being a relative newbie to parsing in general, I opted to build a character by character parser (I think that’s called lexical parsing?)
    As a proof of concept, it does a “sort of” tabbing, and allows selection by token. The picture says it all.

    Hope that helps

    Cheers,
    Rob

  4. Why not using the office suite ? (i.c. Word)

    Sub put_the_cells_formula_in_Word()
      GetObject(, “word.application”).ActiveDocument.Paragraphs(1).Range.insertafter ActiveCell.Formula
    End Sub

    Sub return_the_adapted_formula()
      GetObject(, “Excel.application”).ActiveCell.Formula = Replace(ActiveDocument.Paragraphs(1).Range.Text, vbCr, “”)
    End Sub

  5. One more thing. I’ve tried to use the OMath object with no luck. If you pull that off, that’d make for some seriously easier experience editing formulas.

Leave a Reply

Your email address will not be published. Required fields are marked *