Using Notepad++ to Edit Visio ShapeSheet formulas

Visio shapes get their smartness from the formulas that are entered into the ShapeSheet, but editing these formulas can be extremely tricky and prone to error because of the lack of a modern programmer’s interface. Formulas can be quite long (up to 64k characters) but even medium size ones like the one in User.GetWorkdays cell below from an earlier article can become a challenge. I have been using Notepad++ for many years to copy and paste the formulas from a cell so that I can split the text into multiple lines, and to see where the bracket pairs are. My good friend John Goldsmith pointed out that I can record a macro in Notepad++ and create a custom language style. So together we improved our environment for editing ShapeSheet formulas.

Note that I used CTRL+Mouse Wheel to enlarge the font size of the Edit Formula dialog for the above screenshot, but it is still quite difficult to debug and edit ShapeSheet formulas. So, I copy and paste into Notepad++. This does give me highlighting of selected words and the pairing of brackets.

Recording macros in Notepad++

Notepad++ has the ability to record a macro, save it with a name, and re-run later. I decided to record replacing )) with )\n) , which inserts a new line between two closing brackets, and naming it Add2LinesShapeSheetFormula. I also recorded the reverse called RemoveLines2ShapeSheetFormula, although pasting multi-line code into the Edit Formula window is okay too, but sometimes leaves some hidden characters.

I know it is not exactly like I would like to split lines up, but it is far more legible than having a single word-wrapped line!

Creating a custom language in Notepad++

Another cool feature of Notepad++ is the ability to define a language, so we defined one called VisioFormula, and used the Styler for options in the Folder & Default, Keywords Lists, Operators & Delimiters tabs of the User Defined Language dialog, as shown below:

Here is a list of Visio ShapeSheet functions (courtesy of John Goldsmith) to copy and paste into the Keywords | 1st Group text block….

ABS ACOS AND ANG360 ANGLEALONGPATH ANGLETOLOC ANGLETOPAR ARG ASIN ATAN2 ATAN BITAND BITNOT BITOR BITXOR BKGPAGENAME BLEND BLOB BLUE BOUNDINGBOXDIST BOUNDINGBOXRECT BOUND CALLOUTCOUNT CALLOUTTARGETREF CALLTHIS CATEGORY CEILING CHAR COMPANY CONTAINERCOUNT CONTAINERMEMBERCOUNT CONTAINERSHEETREF COSH COS CREATOR CY DATA1 DATA2 DATA3 DATETIME DATEVALUE DATE DAYOFYEAR DAY DECIMALSEP DEFAULTEVENT DEG DEPENDSON DESCRIPTION DIRECTORY DISTTOPATH DOCCREATION DOCLASTEDIT DOCLASTPRINT DOCLASTSAVE DOCMD DOOLEVERB EVALCELL EVALTEXT FIELDPICTURE FILENAME FIND FLOOR FONT FONTTOID FORMATEX FORMAT FORMULAEXISTS GetRef GetVal GOTOPAGE GRAVITY GREEN GUARD HASCATEGORY HELP HOUR HSL HUEDIFF HUE HYPERLINKBASE HYPERLINK ID IFERROR IF INDEX INTERSECTX INTERSECTY INTUP INT IS1D ISERRNA ISERROR ISERRVALUE ISERR ISTHEMED KEYWORDS LANGUAGE LEFT LEN LISTMEMBERCOUNT LISTORDER LISTSEP LISTSHEETREF LN LOCALFORMULAEXISTS LOCTOLOC LOCTOPAR LOC LOG10 LOOKUP LOWER LUMDIFF LUM MAGNITUDE MANAGER MASTERNAME MAX MID MINUTE MIN MODULUS MONTH MSOSHADE MSOTINT NAME NA NEARESTPOINTONPATH NOT NOW NURBS OPENFILE OPENGROUPWIN OPENSHEETWIN OPENTEXTWIN OR PAGECOUNT PAGENAME PAGENUMBER PAR PATHLENGTH PATHSEGMENT PI PLAYSOUND PNTX PNTY PNT POINTALONGPATH POLYLINE POW QUEUEMARKEREVENT RAD RAND RECTSECT RED REF REPLACE REPT REWIDEN RGB RIGHT ROUND RUNADDONWARGS RUNADDON RUNMACRO SATDIFF SAT SECOND SEGMENTCOUNT SETATREFEVAL SETATREFEXPR SETATREF SETF SHADE SHAPETEXT SHEETREF SIGN SINH SIN SQRT STRSAMEEX STRSAME SUBJECT SUBSTITUTE SUM TANH TAN TEXTHEIGHT TEXTWIDTH THEME THEMECBV THEMEGUARD THEMERESTORE THEMEVAL TIMEVALUE TIME TINT TITLE TONE TRIM TRUNC TYPEDESC TYPE UNICHAR UPPER USERUI USE VERSION WEEKDAY YEAR

I will probably add more cell names into the 5th group as I need them, and we separated out User.msv and User.vis in the 2nd group because they are the normal prefix of Microsoft reserved row names. We also like to distinguish between Prop and User rows with different colours.

I have saved my settings in VisioFormula.xml file to download, which can then be imported into Notepad++ from Language | User Defined Language | Define Your Language | Import.

Running a custom command in Notepad++

There is no intellisense in Notepad++, as far as I know, but it is possible to highlight a word in the file and run a custom command with the selected text. I decided to use this feature to open the Microsoft Learn web site with parameters to hopefully get the correct results.

I did this by editing the Shortcuts.xml file and adding the following two lines into the UserDefinedCommands section:

        <Command name="Visio Function in Microsoft Learn" Ctrl="yes" Alt="no" Shift="no" Key="112">https://learn.microsoft.com/search/?terms=$(CURRENT_WORD)%20function%20visioshapesheet&category=Documentation</Command>
        <Command name="Visio Section in Microsoft Learn" Ctrl="no" Alt="no" Shift="yes" Key="112">https://learn.microsoft.com/search/?terms=$(CURRENT_WORD)%20section%20visioshapesheet&category=Documentation</Command>

The Function line does a better search than the Section line, but still quite useful.

Related articles

Enhancing Visio Event Monitor Output on John Golsmith’s visLog

Synchronizing Visio Shape Fill Color (or almost any cell) across pages

I was recently asked how the color of one shape can be changed and for other shapes to be automatically updated to the same color … even if they are on different pages! Well, it is possible with Microsoft Visio’s awesome ShapeSheet formulas. In fact, this capability is not limited to the FillForegnd cell ……

Positioning Visio Shape Text Block with a Control Handle

I was recently asked how a control handle can be added to a Visio shape so that it can be used to re-position the text block. Fortunately, it is extremely easy to setup, and requires just two formulas to be updated in the ShapeSheet. This is a great use of the SETATREF(…) function. (more…)

Understanding Segments of Visio Geometry

I recently had to revise my understanding of the POINTALONGPATH(…) function in Visio because I was getting a #REF! error in some cases. My particular scenario requires a line with a number of vertices that are initially all in a straight line but can be moved by dragging controls around that each vertex is bound…

Custom Color Themes in Visio?

I was recently looking into custom color themes for corporate branding in desktop Microsoft Visio and became re-aware how different Visio still is from the rest of the Microsoft Office applications. A Visio page or document does not need to have any theme applied, but the documents of the other Office applications always have a…

When is a Visio Callout not a Callout?

I have been a Visio user/developer since the mid-1990’s and seen the word “callout” used as part of the name of many master shapes in Visio. The images below show five ways that the term “callout” has been applied to the name of Visio master shapes. Generally, each evolution has been an advance on the…

Using Visio Color by Value on Connectors

Data Graphics in Visio Plan 2 and Visio Professional is great, but it only enables us to use them with 2D shapes in Visio, i.e. not on connectors. So, what if you want to change the line colour of the connectors between the 2D shapes because of the data flowing between them? Well, it is…