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 YEARI 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
Adding a second Function header bar to Visio swimlanes
I was recently asked if a second function header bar can be added to the swimlanes in the cross-functional flowchart templates in Visio. Some swimlanes can get quite wide, so it can be useful to have a duplicate function header shape on the far-side too. It is quite simple to duplicate the existing function header…
More Parsing XML Data in Visio Shapes
My last article looked at parsing an XML string with a known structure and order of elements and attributes. This can be acceptable in some scenarios, but what if the elements or attributes are in a different order? Then it is necessary to use the element and attribute names rather than their index position. This…
Parsing XML data in Visio Shapes
One of my current projects uses XML data, and some of the values in the XML data control the display and content of Visio shapes. Therefore, I looked deeper into how the XML data can be used directly to control parts of the graphics. Although the external data linking feature in Visio Professional and Visio…
Highlighting dirty text in Visio shapes
I suppose I should explain what I mean by dirty text first 🙂 I often display the value of a Shape Data row in Visio text, but sometimes the solution requires that the value is also editable. This is ok if the client accepts that all editing is done with the Shape Data window or…
My new book on Visualizing Processes with Microsoft Visio has launched
Back in the early 1990s, there was an application called ABC Flowcharter that was the market leader for diagramming business flowcharts, but some of the brains behind Aldus PageMaker saw an opportunity to create something smarter, and left to write the Visio product, with the stated aim to overtake ABC Flowcharter within 2 years. They…
Interactive Demographics of the European Union in Visio
I was recently contacted by a reader, Stanley M. Max (Towson University lecturer), who had started creating a Visio map of the 27 countries in the European Union in 2020, along with the demographics culled from Wikipedia. He wanted to know if the map and stats could be combined to make a more appealing presentation.…
Leave a Reply