Project Analyzer and VBA Plug have a few limitations consisting of unanalyzed elements, unsupported features and unverified dead code.
Being a Visual Basic code analyzer, Project Analyzer does not analyze any non-VBA elements. The following objects and non-VBA code are not analyzed:
The VBA code that runs behind non-code objects is analyzed normally. To name an example, UserForm events and Access report events are analyzed as long as they are written in VBA.
Project Analyzer functions for VBA the same way it does for regular Visual Basic projects. There are a few differences and unsupported features, though.
Dead code detection is fully functional for VBA code. However, there are certain procedures whose deadness cannot be verified, not even when there are no calls. These functions and subs are shown as "exposed", which means no calls were found to them but they may be in use. Thus, an exposed procedure might be dead but this was not verifiable by the analysis.
In Office VBA projects, certain procedures are exposed to be executed by the host application. The host application may execute certain Subs as macros in response to a menu or toolbar action. Excel worksheets may call certain VBA functions, and in Access certain functions may execute as user-defined functions in response to events. Exposed Subs are usually listed in the host application's Tools|Macro dialog box, while exposed Functions are listed in the function or expression builder window.
Project Analyzer does not detect this type of calls from the host application to the VBA project. This is why the deadness of such procedures is unknown to Project Analyzer. These procedures may or may not execute, and this cannot be deducted by reading the VBA project code alone. Project Analyzer shows the procedures as exposed. You can treat them as dead procedures or as live procedures according to your preferences and knowledge about the application in question. Use the exposed code setting in the Problem Options dialog, Dead code tab to control this behaviour. The default is exposed = live. This is a safe setting because if you don't know if it's dead, you assume it's not. You can also use exposed = dead. This lets you list exposed procedures as dead code and consider for each procedure whether it should be removed or not.
Exposed procedures cause some degree of unaccuracy in dead VBA code detection. When writing new code, a few recommended practices help the dead code detection and also keep the code more manageable. These practices should be used to improve the code, not just because they happen to help in the analysis.
Declare Private. It is advisable to declare procedures as Private where possible. This limits the scope of the procedure. It helps you to avoid errors by preventing accidental calls to procedures that should not be executed as macros, and it also prevents accidental calls from other modules or other documents. Systematic use of Private Subs makes dead code detection more accurate. However, Private Functions do not have this effect, since they can be exposed in Excel and Access.
Use parameters, not globals. Another good practice is to pass data as procedure parameters instead of using global variables. This keeps the data flow clear and it may also help prevent accidental changes of the value of the global variable. Besides the data flow effect, adding parameters to Subs also makes dead code detection more accurate, since the host application cannot call such a Sub as a macro. It can only do so when the Sub has no compulsory parameters. Adding parameters to Functions does not help to find dead functions, though, it is only the Subs for this trick.
Go object-oriented. Encapsulating functionality in classes instead of keeping all code in standard modules is always an idea to consider. It is especially useful if the use of a module-level variable seems inevitable, which suggests there is a natural object with an internal state. Classes help you to encapsulate data with the related code, and as it happens, class methods are not exposed if the class is private. VBA classes are private by default, and their dead methods are thus easy to spot.
Flag code as live. You can use Project Analyzer's comment directive feature to flag any procedure as live code. You do this by adding
'$PROBHIDE DEAD immediately above the Sub or Function line. If you know for sure that a procedure is in use as a macro, as an Excel worksheet function or as an Access user-defined function, you can flag it as live code to prevent Project Analyzer from reporting it as dead. This technique has a drawback, though. If the procedure should later become dead, Project Analyzer will still report it as live instead of dead.
©Aivosto Oy - Project Analyzer Help Contents