code inspection for square-bracketed names in Excel VBA

[joespiff]

Justification
In Excel VBA, a named range can be used in code by typing the name in square brackets. This causes the code inspection message "Expression '[rangename]' cannot be validated at compile-time". The description for this inspection suggests to "Consider using the host application's object model instead." However, these square-bracketed ranges are a very useful way to validate which named ranges are used in code, so it can be a good practice to use them, as long as they are converted to a strongly-typed variable as soon as possible.

Description
If the developer only uses the square-bracketed expression to set a strongly-typed variable, then this message should not display. For other uses of square-bracketed names in Excel, a refactor should be offered that will declare a range and set it equal to the square-bracketed name.

[retailcoder]

Thanks for the feedback!

If I understand correctly, the idea would be to avoid issuing a result for this inspection when the bracketed expression is found in the RHS of a SetStmt that has an Excel.Range variable its LHS, only when the host application is EXCEL.EXE:

Dim Cell As Range
Set Cell = [expression]

This seems sensible, however I would point out that the bracketed expression is, as pointed out by the inspection, inherently late-bound: there is no way to correctly evaluate what's in it without having Excel perform its evaluation - so it's a little bit "wishful thinking" to assume it's only ever contain range names or addresses (they can be literally anything Excel is capable of evaluating), and validating this would be creeping into attempting to evaluate late-bound expressions, which Rubberduck should not be trying to do.

If I may suggest an alternative, I would submit that embracing early binding and using Workbook.Names and Worksheet.Names collections to retrieve scoped Name objects (Name.RefersToRange resolves the Range for it) makes it even easier to track what names are referenced in code; because Rubberduck knows about every single place a Name object is being used, listing all references to Names collections would list all the places these collections are being referenced: "Find all references" works for anything that has a name, regardless of whether it's VBA project code or compiled referenced library code.