# Visual Basic for Applications/Redundant Variables List
## Summary

This very long code module lists an Excel project's redundant variables.
Running the top procedure checks the VBA project of ThisWorkbook, that is, the workbook in which the code is run. It produces both worksheet and user form outputs. The code is self-contained in the one module, but in addition, the user needs to make a user form called ViewVars, with a textbox in it called TextBox1. The details are not too important since the display is adjusted in code to fit the contents. However, the user form's property ShowModal should be set to False, and Multiline set to True. A testing mode of sorts can be had by setting boolean variable bUseWorkSheets in RunVarChecks to True. Be advised however, that this will clear all existing worksheets before writing to sheets one to five. To labor the point, if your intention is to not disturb the contents of project sheets one to five, then be sure that bUseWorkSheets of RunVarChecks is set to False; redundant variables will still be listed in the user form ViewVars after a few seconds of code run.

## Points to Note

There are some limitations:
** The listing can only work for code that compiles correctly; that is, sensible constructs if not necessarily working code.
** API variable declarations and enumerations of constants are not handled. That is to say, they will not be listed even if they are redundant.
** The module is coded to work with the VBAProject of ThisWorkbook . There is however, an optional parameter to access another workbook object for those who intend to check some other.
* The module works with the usual VBA variable naming methods. This includes the use of public same-names, and fully expressed variable descriptions. It does so by searching for compound similars as well as their simple forms. For example, although rare, the three forms myvar, Module1.myvar, and VBProject.Module1.myvar could all be used in code for the same variable. The use of these forms allows the same variable names to be used in any module heading without conflict.
* Several worksheet listings are made for output results and testing. The user should make sure that sheets 1 to 5 exist, since the code will not create them in this listing. The user might want to restrict or change these in the main procedure if they will conflict with other uses. A separate user form output makes use of procedure AutoLayout.
* The user form styles might not suit everyone, but the colorings and fonts can be changed in two user-sections of the procedure AutoLayout. Bear in mind however, that the chosen font must be monospaced for a neat layout. Apart from this restriction, the layout will handle any regular font size from about 6 to 20 points, as well as the bold and italic variants. That is to say, the code will auto-adjust the userform layout and sizes to produce a useful display.
* Procedures have not been marked as module Private. There is the remote possibility of same-name procedures being encountered when users also make use of other modules in this series. In future I will try to remember to mark them module-private if they look as though they were used elsewhere.
* Interested parties might like to advise of any bugs. Please use only the Discussion page and I will get to them as soon as I can.

## Work Method Used
### General Preparation

* The general method is to make a declared variables list then test each variable entry to see if it is used.
* The project string contains all of the code in the project. The string is loaded into a work array line by line, and is passed in variants from process to process.
* Procedure, module, and project name information is also added. Every code line is marked with this information.
* Quotes and comments are removed, since they could contain any text at all, and might confuse the decision process.
* Other confusions arise from continuation lines, so these are all joined up into single long lines prior to interpretation.
* Shared label lines and line numbers can also cause difficulty, so labels are given lines of their own, and line numbers are separated prior to any decision process.
* Blank lines are not needed so they are removed. Because there is a changed line count, the project work array is renumbered.
* Each code line is marked with its residential line ranges. Each line is given the code line range for the procedure and for the module in which it resides. This data is then easily found later.

### The Declared Variables

* The declared variables list, the array vDec, contains every declared variable in the project.
* It lists all other pertinent data about each variable. The scope of each variable is determined and added. The nominal search line ranges are also added. These are the line ranges suggested at first sight after knowing the scope of the variable. For example, a procedure level declaration would show the procedure line range, and a module-private item the module's line range.
* The variables are marked on vDec when they are found as used. The search sequence is, all of the procedure level variables, then the module private variables, then lastly the public variables. When there are same-name variables with different scopes, this sequence is useful, in that it progressively reduces the required search ranges.
* Every variable is checked for naming ambiguity before deciding which search method to use. Only if there is no names ambiguity can a so-called normal approach be taken; ie; searching the entire nominal line range. Otherwise, the nominal search range needs modified to avoid regions where same-name variables were already found. For example, a module variable search would not look in a procedure where a same-named variable had been both declared and used, but would check anyway if no same-name item were declared there.
* Public and module level variables have to be checked in three names. Variables' full names can include project, module, and variable names, or just module and variable names, in addition to the better known short forms.
* Public variables are handled a bit differently. These variables can exist in each module with the same name. There are two kinds of duplicated names possible for public variables; firstly, there is the kind where there is a public variable with the same name as a variable in any number of procedures, and secondly, there is the kind where the same name is used for a public variable in more than one module heading. In these same-name cases the use of public variables need at least the module and variable name when their use is not in the module where declared.
** Most of the time a public variable's name is entirely unique. That is, there is no other variable in the project with the same name. In this case the use of the variable can be searched throughout the project without restriction.
** If the public variable has no same-names in other module heads, but there are same-names in module or procedure variables, then the whole project must be searched for its use, taking into account line restrictions from modules and procedures where such same-names were already found as used.
** If the public variable has same-name variables in more than one module heading, then the determination of variable use must be handled in two stages;
*** The entire project must be searched without restriction using both compound forms of the public variable
*** Then search in the module where the public variable is declared, taking account of any procedure restrictions that apply from same-names there.
* After all this, any variables not marked as used can be listed as redundant.

## VBA Code Module
### Updated and Tested 17 Sep 2017

Modified changed word aliases to similars (15 Jan 2018). Modified AutoLayout to avoid wrap back in form. Label length plus 4 spaces now, not 2. (17 Sep 2017). Added a note on need for VBA Extensibility 5.3 and tested code - working OK.(31 Dec 2016)
Modified AutoLayout to reduce control count.(17 Nov 2016). Modified AutoLayout for better font choice.(16 Nov 2016). Added simpler options for fonts in AutoLayout.(16 Nov 2016) Modified code in dynamic arrays and added test mode switch bUseWorkSheets in RunVarChecks.(15 Nov 2016) Removed one redundant procedure and corrected TrimStr error.(13 Nov 2016) Corrected code call to NewPubRange in MarkPubVarUse. Parameter lines now whole project.(8 Nov 2016) Changes made to user form display procedures. (7 Nov 2016)