VBA Rubberducking (Part 2)

This post is the second in a series of post that walk you through the various features of the Rubberduck open-source VBE add-in. The first post was about the navigation features.

Code Inspections


Back when the project started, when we started realizing what it meant to parse VBA code, we knew we were going to use that information to tell our users when we’re seeing anything from possibly iffy to this would be a bug in their code.

The first one to be implemented was OptionExplicitInspection. The way Rubberduck works, a variable that doesn’t resolve to a known declaration simply doesn’t exist. Rubberduck is designed around the fact that it’s working against code that VBA compiles; is also needs to assume you’re working with code that declares its variables.

Without ‘Option Explicit’ on, Rubberduck code inspections can yield false positives.

Because it’s best-practice to always declare your variables, and because the rest of Rubberduck won’t work as well as it should if you’re using undeclared variables, this inspection defaults to Error severity level.

OptionExplicitInspection was just the beginning. As of this writing, we have implementations for 35 inspections, most with one or more one-click quick-fixes.

35 inspections?

And there’s a couple more left to implement, too. A lot of inspections rely on successful parsing and processing of the entire project and its references; if there’s a parsing error, then Rubberduck will not produce new inspection results. When parsing succeeds, inspections run automatically and the “status bar” indicates Ready when it’s completed.

  1. AssignedByValParameterInspection looks for parameters passed by value and assigned a new value, suggesting to either extract a local variable, or pass it by reference if the assigned value is intended to be returned to the calling code.
  2. ConstantNotUsedInspection looks for constant declarations that are never referenced. Quick-fix is to remove the unused declaration.
  3. DefaultProjectNameInspection looks for unnamed projects (“VBAProject”), and suggests to refactor/rename it. If you’re using source control, you’ll want to name your project, so we made an inspection for it.
  4. EmptyStringLiteralInspection finds “” empty strings and suggests replacing with vbNullString constants.
  5. EncapsulatePublicFieldInspection looks for public fields and suggests making it private and expose it as a property.
  6. FunctionReturnValueNotUsedInspection locates functions whose result is returned, with none of the call sites doing anything with it. The function is used as a procedure, and Rubberduck suggests implementing it as such.
  7. IdentifierNotAssignedInspection reports variables that are declared, but never assigned.
  8. ImplicitActiveSheetReferenceInspection is Excel-specific, but it warns about code that implicitly refers to the active sheet.
  9. ImplicitActiveWorkbookReferenceInspection is also Excel-specific, warns about code that implicitly refers to the active workbook.
  10. ImplicitByRefParameterInspection parameters are passed by reference by default; a quick-fix makes the parameters be explicit about it.
  11. ImplicitPublicMemberInspection members of a module are public by default. Quick-fix makes the member explicitly public.
  12. ImplicitVariantReturnTypeInspection a function or property getter’s signature doesn’t specify a return type; Rubberduck makes it return a explicit Variant.
  13. MoveFieldCloserToUsageInspection locates module-level variables that are only used in one procedure, i.e. its accessibility could be narrowed to a smaller scope.
  14. MultilineParameterInspection finds parameters in signatures, that are declared across two or more lines (using line continuations), which hurts readability.
  15. MultipleDeclarationsInspection finds instructions containing multiple declarations, and suggests breaking it down into multiple lines. This goes hand-in-hand with declaring variables as close as possible to their usage.
  16. MultipleFolderAnnotationsInspection warns when Rubberduck sees more than one single @Folder annotation in a module; only the first annotation is taken into account.
  17. NonReturningFunctionInspection tells you when a function (or property getter) isn’t assigned a return value, which is, in all likelihood, a bug in the VBA code.
  18. ObjectVariableNotSetInspection tells you when a variable that is known to be an object type, is assigned without the Set keyword – this is a bug in the VBA code, and fires a runtime error 91 “Object or With block variable not set”.
  19. ObsoleteCallStatementInspection locates usages of the Call keyword, which is never required. Modern form of VB code uses the implicit call syntax.
  20. ObsoleteCommentSyntaxInspection locates usages of the Rem keyword, a dinosaurian syntax for writing comments. Modern form of VB code uses a single quote to denote a comment.
  21. ObsoleteGlobalInspection locates usages of the Global keyword, which is deprecated by the Public access modifier. Global cannot compile when used in a class module.
  22. ObsoleteLetStatementInspection locates usages of the Let keyword, which is required in the ancient syntax for value assignments.
  23. ObsoleteTypeHintInspection locates usages of type hints in declarations and identifier references, suggesting to replace them with an explicit value type.
  24. OptionBaseInspection warns when a module uses Option Base 1, which can easily lead to off-by-one bugs, if you’re not careful.
  25. OptionExplicitInspection warns when a module does not set Option Explicit, which can lead to VBA happily compiling code that uses undeclared variables, that are undeclared because there’s a typo in the assignment instruction. Always use Option Explicit.
  26. ParameterCanBeByValInspection tells you when a parameter is passed ByRef (implicitly or explicitly), but never assigned in the body of the member – meaning there’s no reason not to pass the parameter by value.
  27. ParameterNotUsedInspection tells you when a parameter can be safely removed from a signature.
  28. ProcedureCanBeWrittenAsFunctionInspection locates procedures that assign a single ByRef parameter (i.e. treating it as a return value), that would be better off written as a function.
  29. ProcedureNotUsedInspection locates procedures that aren’t called anywhere in user code. Use an @Ignore annotation to remove false positives such as public procedures and functions called by Excel worksheets and controls.
  30. SelfAssignedDeclarationInspection finds local object variables declared As New, which (it’s little known) affects the object’s lifetime and can lead to surprising/unexpected behavior, and bugs.
  31. UnassignedVariableUsageInspection locates usages of variables that are referred to before being assigned a value, which is usually a bug.
  32. UntypedFunctionUsageInspection recommends using String-returning functions available, instead of the Variant-returning ones (e.g. Mid$ vs. Mid).
  33. UseMeaningfulNamesInspection finds identifiers with less than 3 characters, without vowels, or post-fixed with a number – and suggests renaming them. Inspection settings will eventually allow “white-listing” common names.
  34. VariableNotAssignedInspection locates variables that are never assigned a value (or reference), which can be a bug.
  35. VariableNotUsedInspection locates variables that might be assigned a value, but are never referred to and could be safely removed.
  36. VariableTypeNotDeclaredInspection finds variable declarations that don’t explicitly specify a type, making the variable implicitly Variant.
  37. WriteOnlyPropertyInspection finds properties that expose a setter (Property Let or Property Set), but no getter. This is usually a design flaw.

Oops, looks like I miscounted them… and there are even more coming up, including host-specific ones that only run when the VBE is hosted in Excel, or Access, or whatever.

The Inspection Results toolwindow

If you bring up the VBE in a brand new Excel workbook, and then bring up the inspection results toolwindow (Ctrl+Shift+I by default) you could be looking at something like this:


Most inspections provide one or more “quick-fixes”, and sometimes a quick-fix can be applied to all inspection results at once, within a module, or even within a project. In this case Option Explicit can be automatically added to all modules that don’t have it in the project, using the blue Fix all occurrences in project link at the bottom.

Or, use the Fix drop-down menu in the top toolbar to apply a quick-fix to the selected inspection result:


Each inspection has its own set of “quick-fixes” in the Fix menu. A common one is Ignore once; it inserts an @Ignore annotation that instructs the specified inspection to skip a declaration or identifier reference..


The bottom panel contains information about the selected inspection result, and fix-all links that always use the first quick-fix in the “Fix” menu. Disable this inspection turns the inspection’s “severity” to DoNotShow, which effectively disables the inspection.

You can access inspection settings from the Rubberduck | Settings menu in the main commandbar, or you can click the settings button in the inspection results toolwindow to bring up the settings dialog:


If you like using the Call keyword, you can easily switch off the inspection for it from there.

The Copy toolbar button sends inspection results into the clipboard so they can be pasted into a text file or an Excel worksheet.

As with similar dockable toolwindows in Rubberduck, the way the grid regroups inspection results can be controlled using the Grouping menu:


The refresh button causes a re-parse of any modified module; whenever parser state reaches “ready”, the inspections run and the grid refreshes – just as it would if you refreshed from the Rubberduck command bar, or from the Code Explorer toolwindow.

To be continued…




2 thoughts on “VBA Rubberducking (Part 2)”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s