20

Is there a tool, method or setting in the standard VBA Editor to warn about variables that have been Dim'med, but aren't being used?

Greedo
  • 4,967
  • 2
  • 30
  • 78
Lance Roberts
  • 22,383
  • 32
  • 112
  • 130
  • 5
    I'm not convinced this question should have been put on-hold; the close reason states that _"[the question is] asking us to recommend [...] a tool [...] or other off-site resource"_. In fact the question is aimed at tools within the MS-Excel built in editor (VBE), not asking for a 3rd party tool or off-site resource. Although some options supplied in the answers rely on 3rd party tools, I don't think that necessarily makes them _"opinionated answers"_ or _"spam"_. Indeed [one of the answers](https://stackoverflow.com/a/43231349) I gave works directly in the VBE without any additional tools. – Greedo Aug 24 '18 at 09:57
  • 1
    This is an excellent question. Reopen it please. – Excel Hero May 05 '20 at 16:51
  • @ExcelHero, Done for now. This question wasn't about searching for tools, even if that came up to be the best answer. I was hoping for an internal VBA editor method. – Lance Roberts May 06 '20 at 17:06
  • I wish there were a way. You can always use the Find capability form the Edit menu. In fact I often do this. But it's certainly not great. – Excel Hero May 06 '20 at 20:21

2 Answers2

21

MZ-Tools will search through your code and tell you what is not being used. The version for VBA can be found here.

The specific feature in MZ-Tools that performs what you asking about is Review Source Code:

The Review Source Code feature allows you to review the source code to detect some unused declarations (constants, variables, parameters, procedures, etc.).

Ray
  • 187,153
  • 97
  • 222
  • 204
Jon Fournier
  • 4,299
  • 3
  • 33
  • 43
  • 2
    Yes: It is free, easy, and very efficient. Once you notice how much you save time by using it, please do like me: donate! – Philippe Grondier Oct 10 '08 at 12:35
  • 2
    Unfortunately no longer free - https://www.mztools.com/v8/purchase.aspx – Darren Bartrup-Cook Apr 05 '17 at 12:59
  • 1
    Version 8 is a "free 30 day trial" (with nags), but I wouldn't recommend it. The "Dead Code Review" option in the "Review Quality" operation does check for unused variables and procedures, but returns garbage too (like the rest of the review check). Perhaps some people want to be told where to put comments and how to capitalize their variables and procedure names, I found it kind of annoying, especially given that never seen these naming conventions it's insisting upon. – ashleedawg May 24 '18 at 17:24
12

Another method (because I don't have admin privileges to install that software), is to put Option Explicit at the top of your module. Then comment out all the Dim'med variables, and debug through your code with F8 or recompile with Alt+d+l

Uncomment the Dims one by one until Option Explicit returns no compiler errors, at which point any variables still commented out can be ignored, as they are not used in your code

Similar to MZ-Tools, RubberDuck has a VariableNotUsed Inspection (amongst many other things) that should show precisely what you're after. It's free and, as of v2.2.0.3086, it supports non-admin installation.

Greedo
  • 4,967
  • 2
  • 30
  • 78