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.
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
Shell “C:Program FilesNotepad++
otepad++.exe -xlff “ & sFile
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
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