A while ago, I started a project for a client that involved updating a pre-existing Excel Workbook which contained ... no lie ... more than 1,000 Shapes across a dozen-or-so sheets along with double figures of Tables and Named Ranges.
A large number of the Shapes triggered VBA code via their OnAction property. And almost all of the Shapes, Tables and Named Ranges were referenced from the VBA code ... setting whether Shapes were visible and/or enabled, pulling values from Shapes (e.g. text boxes, list boxes etc), referencing Tables and Named Ranges to get values from them etc. Individual Shapes, Tables and Named Ranges would sometimes be references in 20 or 30 different places in the VBA code. And the only way to find those places was to use the VBE's Find dialog.
My patience ran out pretty quickly ... and so the 'VBE_Extras Helper' Excel AddIn was born!
'VBE_Extras Helper' works with (and requires) VBE_Extras itself and provides some pretty essential functionality if you want to work with VBA code and Shapes, Tables and Named Ranges. You can access the functionality either via the main 'ribbon' menu (a tab named VBE_Extras is added with various buttons) ...
... or using the right-click / context menus ...
Shapes
Uses of Shape's name
For the selected Shape, list all String literals matching the Shape's name property:
In the VBA Project of the Workbook that the Shape is within ... if no matches are found, then
If the active VBA Project is an AddIn, matches within the AddIn
If one or more matches are found, a dialog listing them and their locations within the VBA Project (Module name, line etc) will be shown and you can select one match to jump to (i.e. jump to its position within the VBA code).
This functionality is not 'code-context sensitive' ... all matching String literals will be returned:
Whether they refer to a Shape name or not, and whether they refer to a Shape in the right Worksheet or Chart sheet or not
Matches are not case-sensitive
The reverse action to jump from a String literal that matches the name of a Shape in the VBA code to the Shape itself in the Excel user-interface can be done using VBE_Extras ... place the cursor in the String literal then select: Extras > Goto > UI Object in this Project's Workbook > Shape with this name or that calls this Procedure. If multiple Shapes with the same name exist then you will be shown a dialog to allow you to select the relevant Shape.
Go to OnAction
For the selected Shape, go to the procedure that is the target of the Shapes' OnAction property (as set using 'Assign Macro').
The reverse action to jump from a procedure that is the target of the Shapes' OnAction property to the Shape itself in the Excel user-interface can be done in the same way as above for Uses of Shape's name ... except this time place the cursor on the name of the procedure then select: Extras > Goto > UI Object in this Project's Workbook > Shape with this name or that calls this Procedure.
Tables
Uses of Table's name
When a cell is selected within a Table, list all String literals matching the Table's name property:
In the VBA Project of the Workbook that the Table is within ... if no matches are found, then
If the active VBA Project is an AddIn, matches within the AddIn
If one or more matches are found, a dialog listing them will be shown and you can select one to jump to (i.e. jump to its position within the VBA code).
The same comment about "all matching String literals will be returned" applies here as for Shapes.
The reverse action to jump from a String literal that matches the name of a Table in the VBA code to the Table itself in the Excel user-interface can be done using VBE_Extras ... place the cursor on the String literal then select: Extras > Goto > UI Object in this Project's Workbook > Table with this name.
Names
Uses of Name's name
When a cell is selected within a Named Range, list all String literals matching the Name's name property:
In the VBA Project of the Workbook that the Named Range is within ... if no matches are found, then
If the active VBA Project is an AddIn, matches within the AddIn
If one or more matches are found, a dialog listing them will be shown and you can select one to jump to (i.e. jump to its position within the VBA code).
The same comment about "all matching String literals will be returned" applies here as for Shapes.
The reverse action to jump from a String literal that matches the name of a Named Range in the VBA code to the Named Range itself in the Excel user-interface can be done using VBE_Extras ... place the cursor on the String literal then select: Extras > Goto > UI Object in this Project's Workbook > Name with this name.
Finally ...
'VBE_Extras Helper' also allows you to view/copy the names of Shapes, Tables and Named Ranges as well as view/copy (as text) the OnAction property for Shapes. When the right-click / context menu has been activated for a Shape, then the type of Shape (e.g. 'Form Control' in the image above) is shown, in bold, at the top of the sub-menu.
I can't explain just how much time I saved using 'VBE_Extras Helper'. Sure, it took plenty of time to develop it. But that time was hugely out-weighed by the time saved by not having to manually navigate to-and-fro between the Excel UI and the VBE.
Obviously, 'VBE_Extras Helper' works in Excel only and requires VBE_Extras to also be installed:
'VBE_Extras Helper' is, itself, free and can be downloaded from the Download VBE_Extras page.
VBE_Extras can be downloaded from the same page. VBE_Extras comes with a free trial (60 days at the time of writing) but then needs a licence - see the VBE_Extras licences page for details.
Comments