Static code analysis can find hundreds of opportunities in VBA code.
Rubberduck builds its own internal representation of the code, and then proceeds to analyze it. Each individual inspection can easily be disabled, or configured to issue inspection results at different severity levels ranging from Hint to Error.
Use the Inspection Results toolwindow to review Rubberduck’s findings, search, filter, regroup results by inspection, location, type, or severity. Each inspection result comes with a detailed description of what’s being flagged and why, so you can make an enlightened decision.
Unless configured otherwise, Rubberduck automatically runs inspections after the a parser/resolver cycle completes (regardless of whether the inspection results toolwindow is displayed or not).
For the best experience, it would be recommended to first try Rubberduck with an empty project, add a new module, and write, say, a loop that counts 1 to 10 and outputs to the debug pane - then to parse that and review the inspection results; carefully review the inspection settings, and consider disabling the inspections that irreconcilably clash with your preferences: use meaningful names alone can easily produce hundreds upon hundreds of results if you’re not that much into using vowels, or if you, say, prefix all your variable names; these inspections can be re-enabled anytime you’re ready!
These inspections flag a number of obsolete code constructs and statements that should not be used in new VB6 or VBA7 projects, and hint about implicit language defaults and various other opportunities.
Warns about Def[Type] statements.
Default severity: Suggestion
These declarative statements make the first letter of identifiers determine the data type.
Flags uses of an empty string literal ("").
Default severity: Warning
Standard library constant 'vbNullString' is more explicit about its intent, and should be preferred to a string literal. While the memory gain is meaningless, an empty string literal still takes up 2 bytes of memory, but 'vbNullString' is a null string pointer, and doesn't.
Warns about host-evaluated square-bracketed expressions.
Default severity: Warning
Host-evaluated expressions should be implementable using the host application's object model. If the expression yields an object, member calls against that object are late-bound.
Locates unqualified Worksheet.Range/Cells/Columns/Rows member calls implicitly referring to ActiveSheet.
Default severity: Warning
Implicit references to the active worksheet (ActiveSheet) rarely mean to be working with *whatever worksheet is currently active*. By explicitly qualifying these member calls with a specific Worksheet object, the assumptions are removed, the code is more robust, and will be less likely to throw run-time error 1004 or produce unexpected results when the active sheet isn't the expected one.
This inspection will only run if the Excel library is referenced.
Locates unqualified Workbook.Worksheets/Sheets/Names member calls that implicitly refer to ActiveWorkbook.
Default severity: Warning
Implicit references to the active workbook rarely mean to be working with *whatever workbook is currently active*. By explicitly qualifying these member calls with a specific Workbook object, the assumptions are removed, the code is more robust, and will be less likely to throw run-time error 1004 or produce unexpected results when the active workbook isn't the expected one.
This inspection will only run if the Excel library is referenced.
Highlights implicit Public access modifiers in user code.
Default severity: Hint
In modern VB (VB.NET), the implicit access modifier is Private, as it is in most other programming languages. Making the Public modifiers explicit can help surface potentially unexpected language defaults.
Warns about 'Function' and 'Property Get' procedures that don't have an explicit return type.
Default severity: Hint
All functions return something, whether a type is specified or not. The implicit default is 'Variant'.
Warns about module-level declarations made using the 'Dim' keyword.
Default severity: Suggestion
Private module variables should be declared using the 'Private' keyword. While 'Dim' is also legal, it should preferably be restricted to declarations of procedure-scoped local variables, for consistency, since public module variables are declared with the 'Public' keyword.
Locates explicit 'Call' statements.
Default severity: Suggestion
The 'Call' keyword is obsolete and redundant, since call statements are legal and generally more consistent without it.
Locates legacy 'Rem' comments.
Default severity: Suggestion
Modern VB comments use a single quote character (') to denote the beginning of a comment: the legacy 'Rem' syntax is obsolete.
Locates legacy 'Error' statements.
Default severity: Suggestion
The legacy syntax is obsolete; prefer 'Err.Raise' instead.
Locates legacy 'Global' declaration statements.
Default severity: Suggestion
The legacy syntax is obsolete; use the 'Public' keyword instead.
Locates explicit 'Let' assignments.
Default severity: Suggestion
The legacy syntax is obsolete/redundant; prefer implicit Let-coercion instead.
Flags declarations where a type hint is used in place of an 'As' clause.
Default severity: Suggestion
Type hints were made obsolete when declaration syntax introduced the 'As' keyword. Prefer explicit type names over type hint symbols.
Flags obsolete 'On Local Error' statements.
Default severity: Suggestion
All errors are "local" - the keyword is redundant/confusing and should be removed.
Warns about 'Sub' procedures that could be refactored into a 'Function'.
Default severity: Suggestion
Idiomatic VB code uses 'Function' procedures to return a single value. If the procedure isn't side-effecting, consider writing is as a 'Function' rather than a 'Sub' the returns a result through a 'ByRef' parameter.
Flags MSForms controls being accessed from outside the UserForm that contains them.
Default severity: Hint
MSForms exposes UserForm controls as public fields; accessing these fields outside the UserForm class breaks encapsulation and couples the application logic with specific form controls rather than the data they hold. For a more object-oriented approach and code that can be unit-tested, consider encapsulating the desired values into their own 'model' class, making event handlers in the form manipulate these 'model' properties, then have the code that displayed the form query this encapsulated state as needed.
Identifies redundant module options that are set to their implicit default.
Default severity: Hint
Module options that are redundant can be safely removed. Disable this inspection if your convention is to explicitly specify them; a future inspection may be used to enforce consistently explicit module options.
Locates ThisWorkbook.Worksheets and ThisWorkbook.Sheets calls that appear to be dereferencing a worksheet that is already accessible at compile-time with a global-scope identifier.
Default severity: Suggestion
Sheet names can be changed by the user, as can a worksheet's index in ThisWorkbook.Worksheets. Worksheets that exist in ThisWorkbook at compile-time are more reliably programmatically accessed using their CodeName, which cannot be altered by the user without accessing the VBE and altering the VBA project.
This inspection will only run if the Excel library is referenced.
Locates 'For' loops where the 'Step' token is omitted.
Default severity: DoNotShow
This inspection will not run by default, it must be manually enabled in Code Inspections configuration settings.
Out of convention or preference, explicit 'Step' specifiers could be considered mandatory; this inspection can ensure the consistency of the convention.