How PerfectXL and Excel link up with each other perfectly

In our last article about the new Risk Finder we casually mentioned that the Risk Finder refers back to the location of the found issue in the original Excel file with a link. This makes it a lot easier for a user to correct the problem immediately. But how does this work? Both PerfectXL and Excel are standalone software tools. In this article we’ll explain how we link from our software to Microsoft’s.

Similar results can be handled collectively

When the Risk Finder finds a risk, it might detect the same risk in many places. For example, when an extended formula contains a hard-coded number, this risk will be detected in each row that contains the formula, but it is only one issue that needs to be solved.

The user-interface of our new software groups these findings together as ‘a similar issue’ and makes sure that the most relevant issues are presented more prominently. The tool allows the user to check grouped issues off as one, marking it as unacceptable, up for discussion, acceptable or irrelevant. But of course, a user may also decide to fix the issue immediately. Our software presents the user with very specific solutions, and this is where the PerfectXL links (that jump right back to the Excel model) come in handy.

Introducing PXL links

Our software runs stand-alone (not integrated with Excel). There are several reasons for that, the most important reason being that it allows us the freedom to program both back-end and front-end more efficiently. This way, we can open and analyze Excel files faster than Excel itself, which is beneficial. But on the other hand, there is of course an important connection to Excel and that was clearly one of the weaknesses of the old Risk Eliminator; you want to be able to incorporate new insights (improvements and corrections) directly into your model without having to navigate through your model manually.

PXL link handler and custom links

To facilitate this service, we wrote a ‘PXL link handler’. The handler is a minor program that we activate in the background when you use our tools. All it does is generate custom links. Why custom? Well, everyone knows how a standard weblink looks and works: it starts with http:// or https:// and it is connected to an internet browser. When you click a weblink, your computer knows is has to open the browser and navigate to the address.

For the new Risk Finder, we have introduced a new type of link, one that starts with pxl:// and we have defined the type of links we need for all kinds of actions. For example, pxl://go-to-cell/ complemented with the filename, worksheet and cell address will take you to that specific cell in your model. But we have also created pxl://open-defined-names to jump to your name management dialogue box and pxl://open-vba that will take you to specific piece of VBA code. The PXL handler registers all these links and created different ‘entrances’ to Excel, so that we can take you to the problematic locations in your spreadsheet with one click.

We can’t do magic, but almost

There were a few connections that couldn’t be created. We can, for example, jump to the general screen for defined names, but we can’t select a single defined name. This will still have to be done manually. Another small issue occurs when the user has activated the formula bar or another dialogue box; then the PXL link won’t work, because Excel is waiting for another action. But it most cases, the PXL link will take you right where you want to be.

Every time you change your model or put through improvements, you can run a new Risk Finder analysis and all PXL links will be refreshed simultaneously.

See all blog articles