Visual Basic for Applications/Redundant Variables List
Summary
editThis 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
editThere 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
editGeneral Preparation
edit- 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
edit- 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
editUpdated and Tested 17 Sep 2017
editModified 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)
Option Explicit
Option Base 1
Sub TestVars()
'Be sure to set a reference to Visual Basic for Applications Extensibility 5.3
Dim vWB As Variant
'set reference to a workbook
'in the current workbooks collection
Set vWB = ThisWorkbook
RunVarChecks vWB
End Sub
Sub RunVarChecks(Optional vWB As Variant)
'runs a redundant variable check on a workbook's code project
'If no workbook supplied in vWB defaults to this workbook.
'Exclusions: "Declared", "Type" and "Const" declarations.
'CLEARS ALL WORKSHEETS AND REWRITES TO SHEETS 1 TO 5
'WHEN bUseWorkSheets IS TRUE
Dim sht As Worksheet, vDec As Variant, vX As Variant, vAB As Variant
Dim c As Long, n As Long, UDec2 As Long, sLN As Long, vT As Variant
Dim Ub1 As Long, Lb1 As Long, Lb2 As Long, Ub2 As Long
Dim vJ As Variant, vK As Variant, vL As Variant, vM As Variant
Dim vP As Variant, vR As Variant, vS As Variant, vN As Variant
Dim vU As Variant, vV As Variant, vW As Variant, vDisp As Variant
Dim sLS As String, sPN As String, sMN As String, sProc As String
Dim sVScope As String, sP As String, bOneToFind As Boolean
Dim bProcNamed As Boolean, bNotFirst As Boolean, Upper As Long
Dim bUseWorkSheets As Boolean
Dim Uform As UserForm
'==================================================================
bUseWorkSheets = False 'when true, overwrites all worksheets
' and displays test data in sheets 1 to 5,
' else when false, userform output only.
'==================================================================
'decide whether to use parameter wb or this one
If IsMissing(vWB) Then
Set vWB = ThisWorkbook
End If
'clear sheets - clears all sheets
'and unloads open userforms
For Each Uform In VBA.UserForms
Unload Uform
Exit For
Next Uform
If bUseWorkSheets = True Then
For Each sht In ThisWorkbook.Worksheets
sht.Activate
sht.Range("A1:Z65536").ClearContents
Next sht
End If
'PREPARE THE PROJECT ARRAY
sP = LoadProject(vP, vWB) '0 view original source data on sheet 1
'=========================================================================
If bUseWorkSheets = True Then PrintArrayToSheet vP, "Sheet1" 'raw project
'=========================================================================
TrimStr vP, vS '1 remove end spc and tabs-not newlines
JoinBrokenLines vS, vW '2 rejoin broken lines-leaves blank lines
RemoveApostFmQuotes vW, vJ '3
RemoveAllComments vJ, vL '4 remove all comments-leaves blank lines
RemoveBlankLines vL, vK '5 remove all blank lines-reduces line count
RemoveQuotes vK, vM '6 remove all double quotes and their contents
SplitAtColons vM, vV '7 make separate statement lines split at colons
NumbersToRow vV, vU, 6 '8 new line count row 6; originals still in row 1
'DO NOT RENUMBER LINES BEYOND MarkLineRanges()
MarkLineRanges vU, vR '9 mark array with line ranges for search later
'=========================================================================
If bUseWorkSheets = True Then PrintArrayToSheet vR, "Sheet2" 'mod project
'=========================================================================
'get bounds of modified project line array
Lb1 = LBound(vR, 1): Ub1 = UBound(vR, 1)
Lb2 = LBound(vR, 2): Ub2 = UBound(vR, 2)
'redim of declared variables array
ReDim vDec(1 To 12, 0 To 0)
ReDim vDisp(1 To 4, 0 To 0)
'MAKE THE DECLARED VARIABLES ARRAY
'get one line of project array at a time
'if a declaration line, parse it and extract variables
'to build the declared variables array vDec
For c = Lb2 To Ub2
DoEvents
'get one line of data from array
sLN = CStr(vR(1, c)) 'original line number
sPN = vR(3, c) 'project name
sMN = vR(4, c) 'module name
sProc = vR(5, c) 'procedure name
sLS = vR(8, c) 'joined line string
'get declared variables from the line string
If sProc <> "" Then bProcNamed = True Else bProcNamed = False
GetDeclaredVariables sLS, bProcNamed, sVScope, vM
If sVScope <> "" Then
'load declared variables array with dec vars for one line
If UBound(vM) >= 1 Then 'it is a declaration line
'mark the source array string as a declaration line
vR(13, c) = "Declaration"
'transfer found line variables to vDec
For n = LBound(vM) To UBound(vM)
ReDim Preserve vDec(1 To 12, 1 To UBound(vDec, 2) + 1)
UDec2 = UBound(vDec, 2) 'vDec line number
vDec(1, UDec2) = vM(n) 'Declared variable
vDec(2, UDec2) = sPN 'Project name
vDec(3, UDec2) = sMN 'Module name
vDec(4, UDec2) = sProc 'Procedure name
vDec(5, UDec2) = sVScope 'Scope of variable
vDec(6, UDec2) = StartOfRng(vR, sVScope, c) 'Nominal line search start
vDec(7, UDec2) = EndOfRng(vR, sVScope, c) 'Nominal line search end
vDec(8, UDec2) = "" 'Used marking
vDec(9, UDec2) = sLN 'Original line number
vDec(10, UDec2) = "" 'Use checked marker
vDec(11, UDec2) = vR(9, c) 'Module start line number
vDec(12, UDec2) = vR(10, c) 'Module end line number
Next n
End If
End If
Next c
EmptyTheDecLines vR, vT '10 replaces line string with empty string-no change line count
'DISPLAY CONDITIONED PROJECT ARRAY ON WORKSHEET
'=========================================================================
If bUseWorkSheets = True Then PrintArrayToSheet vT, "Sheet3" 'mod project
'=========================================================================
'NOTES
'AT THIS POINT vT CONTAINS THE PROJECT LINES SOURCE TO SEARCH FOR USED VARIABLES.
'vT WILL ALSO BE USED TO SEARCH FOR THE USE OF DECLARED VARIABLES LISTED IN vDec.
'vDec LISTS THE INITIAL LINE NUMBERS RANGE FOR USE-SEARCH, THOUGH THESE ARE LATER MODIFIED.
'The use-search sequence is all procprivate, all modprivate, then all varpublic.
'All declared variables marked as used at one stage need not have their search ranges
'searched again at the next. Eg: Same-name procprivate-used could never be Modprivate-used also.
'Same-name varpublic variables could only apply as used where neither procprivate or modprivate.
'Nominally assigned searched ranges are modified after each stage to narrow the search line ranges
'for the next stage.
'Same-name public variables in each of several module heads are not yet handled.
'MARK THE DECLARED VARIABLES ARRAY WITH USE STATUS
'FIRST - MARK USE OF PROCPRIVATE vDec ITEMS
MarkProcVarUse vDec, vT, vN
vDec = vN
MarkModVarUse vDec, vT, vAB
vDec = vAB
MarkPubVarUse vDec, vT, vX
vDec = vX
'DISPLAY DECLARED VARIABLES ARRAY ON WORKSHEET
'=======================================================================================
If bUseWorkSheets = True Then PrintArrayToSheet vDec, "Sheet4" 'declared variables list
'=======================================================================================
'LOAD REDUNDANT VARIABLES RESULTS ARRAY
For n = LBound(vDec, 2) To UBound(vDec, 2)
' check whether or not marked used
If vDec(8, n) = "" Then
'unused variable so transfer details
If bNotFirst = True Then
'not first data transfer
'so increment array before transfer
Upper = UBound(vDisp, 2) + 1
ReDim Preserve vDisp(1 To 4, 1 To Upper)
Else
'is first data transfer
'so just use first element
ReDim vDisp(1 To 4, 1 To 1)
Upper = UBound(vDisp, 2)
bNotFirst = True
End If
' transfer variable details to display array
vDisp(1, Upper) = vDec(1, n) 'variable name
vDisp(2, Upper) = vDec(4, n) 'procedure name
vDisp(3, Upper) = vDec(3, n) 'module name
vDisp(4, Upper) = vDec(2, n) 'project name
End If
Next n
' report if none found
If UBound(vDisp, 2) = 0 Then
MsgBox "No redundant variables found for display"
Exit Sub
End If
'DISPLAY REDUNDANT VARIABLES RESULTS ON WORKSHEET
'=========================================================================================
If bUseWorkSheets = True Then PrintArrayToSheet vDisp, "Sheet5" 'redundant variables list
'=========================================================================================
'DISPLAY REDUNDANT VARIABLES RESULTS ON USERFORM
AutoLayout vDisp, 1
End Sub
Function LoadProject(vR As Variant, wb As Variant) As String
' Loads local array with parameter workbook's
' whole VBA project string line by line,
' and other details, and returns in array vR.
' Whole project string can be found in LoadProject.
' Needs set reference to Microsoft VBA Extensibility 5.5
'==============================================
' Local String Array sW() Row Details.
' Each line record in one column
'==============================================
'Row 1: Orig proj line number
'Row 2: Orig line string working
'Row 3: Project name
'Row 4: Module name
'Row 5: Procedure name
'Row 6: Reduced proj line number
'Row 7: Temp use for continuation marking
'Row 8: Rejoined versions of lines
'Row 9: Module start number
'Row 10: Module end number
'Row 11: Procedure start number
'Row 12: Procedure end number
'Row 13: n/a
'Row 14: n/a
'==============================================
Dim VBProj As VBIDE.VBProject, VBComp As VBIDE.VBComponent
Dim VBMod As VBIDE.CodeModule, ProcKind As VBIDE.vbext_ProcKind
Dim sMod As String, sProj As String, sLine As String
Dim nLines As Long, n As Long, nC As Long, sW() As String
Dim Ub2 As Long
'redim dynamic array
Erase sW()
ReDim sW(1 To 14, 1 To 1)
'get ref to parameter workbook
Set VBProj = wb.VBProject
'loop through VBComponents collection
For Each VBComp In VBProj.VBComponents
Set VBMod = VBComp.CodeModule
nLines = VBMod.CountOfLines
sProj = sProj & VBMod.Lines(1, nLines) 'project string
sMod = VBMod.Lines(1, nLines) 'module string
If nLines <> 0 Then
With VBMod
For n = 1 To nLines
DoEvents
sLine = Trim(.Lines(n, 1)) 'line string
'Debug.Print sLine
'redim array for each record
ReDim Preserve sW(1 To 14, 1 To nC + n)
Ub2 = UBound(sW, 2)
'load lines of each module into array
sW(1, Ub2) = CStr(Ub2) 'orig proj line number
sW(2, Ub2) = sLine 'raw line string working
sW(3, Ub2) = VBProj.Name 'project name
sW(4, Ub2) = VBMod.Name 'module name
sW(5, Ub2) = .ProcOfLine(n, ProcKind) 'procedure name
sW(6, Ub2) = "" 'reduced proj line number
sW(7, Ub2) = "" 'continuation mark working
sW(8, Ub2) = "" 'long joined-up broken lines
sW(9, Ub2) = "" 'Module start number
sW(10, Ub2) = "" 'Module end number
sW(11, Ub2) = "" 'Procedure start number
sW(12, Ub2) = "" 'Procedure end number
sW(13, Ub2) = "" 'n/a
sW(14, Ub2) = "" 'n/a
Next n
End With
End If
nC = nC + nLines 'increment for next redim
Next VBComp
'Debug.Print sproj
LoadProject = sProj
vR = sW()
Set VBProj = Nothing: Set VBComp = Nothing
Set VBMod = Nothing
End Function
Private Sub TrimStr(vA As Variant, vR As Variant)
'trims leading and lagging spaces and tabs
'from all input array vA code lines
'Returns array in vR
Dim n As Long, c As Long
Dim vW As Variant, str As String
Dim Ub1 As Long, Lb1 As Long, Lb2 As Long, Ub2 As Long
'get bounds of project array
Lb1 = LBound(vA, 1): Ub1 = UBound(vA, 1)
Lb2 = LBound(vA, 2): Ub2 = UBound(vA, 2)
vW = vA
'modify the line strings of the array
For c = Lb2 To Ub2
'get the line string
str = vW(2, c)
n = Len(str)
Do 'delete tabs and spaces from left of string
If Left(str, 1) = Chr(32) Or Left(str, 1) = Chr(9) Then
n = Len(str)
str = Right(str, n - 1)
Else
'left is done
Exit Do
End If
Loop
Do 'delete tabs and spaces from right of string
If Right(str, 1) = Chr(32) Or Right(str, 1) = Chr(9) Then
n = Len(str)
str = Left(str, n - 1)
Else
'left is done
Exit Do
End If
Loop
'pass back the mod string
vW(2, c) = str
Next c
'transfers
vR = vW
End Sub
Sub JoinBrokenLines(vP As Variant, vR As Variant)
'Identifies and joins lines with continuation marks
'Whole lines placed into row 8
'Marks old broken bits as newlines.
'Newlines are removed later in RemoveBlankLines().
Dim vA As Variant, vW As Variant, IsContinuation As Boolean
Dim str As String, sAccum As String, n As Long, s As Long
Dim Ub1 As Long, Lb1 As Long, Lb2 As Long, Ub2 As Long
'get bounds of project array
Lb1 = LBound(vP, 1): Ub1 = UBound(vP, 1)
Lb2 = LBound(vP, 2): Ub2 = UBound(vP, 2)
ReDim vW(Lb1 To Ub1, Lb2 To Ub2)
'pass to work variable
vW = vP
'mark all lines that have a continuation chara
For n = LBound(vW, 2) To UBound(vW, 2)
str = vW(2, n) 'line string
IsContinuation = str Like "* _"
If IsContinuation Then vW(7, n) = "continuation"
Next n
'mark the start and end of every continuation group
For n = LBound(vW, 2) To (UBound(vW, 2) - 1)
If n = 1 Then 'for the first line only
If vW(7, n) = "continuation" Then vW(8, n) = "SC"
If vW(7, n) = "continuation" And vW(7, n + 1) <> "continuation" _
Then vW(8, n + 1) = "EC"
Else 'for all lines after the first
'find ends
If vW(7, n) = "continuation" And vW(7, n + 1) <> "continuation" Then
vW(8, n + 1) = "EC"
End If
'find starts
If vW(7, n) = "continuation" And vW(7, n - 1) <> "continuation" Then
'If vw(7, n) <> "continuation" And vw(7, n + 1) = "continuation" Then
vW(8, n) = "SC"
End If
End If
Next n
'make single strings from each continuation group
For n = LBound(vW, 2) To (UBound(vW, 2) - 1)
If vW(8, n) = "SC" Then 'group starts
'join strings to make one string per continuation group
s = n
vA = Split(CStr(vW(2, n)), "_")
str = CStr(vA(0))
sAccum = str
Do Until vW(8, s) = "EC"
s = s + 1
'handle other continued parts
vA = Split(CStr(vW(2, s)), "_")
str = CStr(vA(0))
sAccum = sAccum & str
vW(2, s) = Replace(vW(2, s), vW(2, s), vbNewLine)
Loop
vW(8, n) = sAccum 'place at first line level in array
End If
str = ""
sAccum = ""
s = 0
Next n
'write remaining strings into row 8 for consistency
'all string parsing and other work now uses row 8
For n = Lb2 To Ub2
If vW(8, n) = "" Or vW(8, n) = "SC" Or vW(8, n) = "EC" Then
vW(8, n) = Trim(vW(2, n))
End If
Next n
'transfers
vR = vW
End Sub
Sub RemoveApostFmQuotes(vB As Variant, vR As Variant)
'returns array vB as vR with apostrophies removed
'from between sets of double quotes,
'Remainder of quote and double quotes themselves left intact.
'for example s = "Dim eyes (Bob's)" becomes s = "Dim eyes (Bobs)"
Dim str As String, str1 As String, vA As Variant, c As Long
Dim n As Long, m As Long, bUnpaired As Boolean, r As Long
Dim Ub1 As Long, Lb1 As Long, Lb2 As Long, Ub2 As Long
'get bounds of project array
Lb1 = LBound(vB, 1): Ub1 = UBound(vB, 1)
Lb2 = LBound(vB, 2): Ub2 = UBound(vB, 2)
ReDim vR(Lb1 To Ub1, Lb2 To Ub2)
'set up loop to get one line at a time
For c = Lb2 To Ub2
str = vB(8, c)
'split string at double quotes
If str <> "" Then
vA = Split(str, """")
Else
'empty string
str1 = str
GoTo Transfers
End If
'recombine the splits
m = UBound(vA) - LBound(vA)
'as long as even num of quote pairs
If m Mod 2 = 0 Then
For n = LBound(vA) To UBound(vA)
If n Mod 2 = 0 Then 'even elements
str1 = str1 & vA(n)
Else
'odd elements
'apostrophies removed
str1 = str1 & Replace(vA(n), "'", "")
End If
Next n
Else
'unpaired double quotes detected
bUnpaired = True
End If
Transfers: 'transfer one row only
For r = Lb1 To Ub1
vR(r, c) = vB(r, c)
Next r
'if all pairs matched
If bUnpaired = False Then
vR(8, c) = str1
Else
'exit loop with str
End If
str1 = "" 'reset accumulator
bUnpaired = False
Next c
End Sub
Sub RemoveAllComments(vA As Variant, vR As Variant)
'Removes all comments from vA row 8 line strings
'Includes comments front, middle and end so
'apostrophed text in double quotes would result
'in a false line split if not first removed.
Dim bAny As Boolean, bStart As Boolean, bEnd As Boolean
Dim n As Long, m As Long, c As Long, r As Long
Dim bincluded As Boolean, l As Long, str As String
Dim Ub1 As Long, Lb1 As Long, Lb2 As Long, Ub2 As Long
'get bounds of project array
Lb1 = LBound(vA, 1): Ub1 = UBound(vA, 1)
Lb2 = LBound(vA, 2): Ub2 = UBound(vA, 2)
ReDim vR(Lb1 To Ub1, 0 To 0)
For c = Lb2 To Ub2
str = vA(8, c)
'detect any instance of a comment mark
bAny = str Like "*'*"
If Not bAny Then
'go for row INCLUSION action
'with original str
bincluded = True
GoTo Transfers
Else
'comment front, 'middle, or 'end
End If
'find whether or not has comment at front
bStart = str Like "'*"
If bStart Then
'go for row EXCLUSION action
'do not include row at all
bincluded = False
GoTo Transfers
Else
'might still have comment at end
End If
'find whether or not has comment at end
bEnd = str Like "* '*"
If bEnd Then
'remove comment at end
l = Len(str)
For n = 1 To l
If Mid(str, n, 2) = " '" Then
str = Trim(Left(str, n - 1))
'go for row INCLUSION action
'with modified str
bincluded = True
GoTo Transfers
End If
Next n
End If
'decide on how to do the default thing
Transfers:
If bincluded = True Then
'include the current row
m = m + 1
ReDim Preserve vR(Lb1 To Ub1, 1 To m)
For r = Lb1 To Ub1
vR(r, m) = vA(r, c)
Next r
vR(8, m) = str
Else
'do not include the current row
End If
Next c
End Sub
Sub RemoveBlankLines(vA As Variant, vR As Variant)
'removes all blank lines from proj array vA
'and returns with modified array in vR
'Changes line count
Dim vM As Variant, bNotFirst As Boolean
Dim c As Long, r As Long, Upper As Long
Dim Ub1 As Long, Lb1 As Long, Lb2 As Long, Ub2 As Long
'get bounds of project array
Lb1 = LBound(vA, 1): Ub1 = UBound(vA, 1)
Lb2 = LBound(vA, 2): Ub2 = UBound(vA, 2)
ReDim vM(Lb1 To Ub1, 1 To 1)
For c = Lb2 To Ub2
If vA(8, c) <> "" And vA(8, c) <> vbNewLine Then
If bNotFirst = True Then
'not first data transfer
'so increment array before transfer
Upper = UBound(vM, 2) + 1
ReDim Preserve vM(Lb1 To Ub1, 1 To Upper)
Else
'is first data transfer
'so just use first element
Upper = UBound(vM, 2)
bNotFirst = True
End If
'transfer data
For r = Lb1 To Ub1
vM(r, Upper) = vA(r, c)
Next r
End If
Next c
vR = vM
End Sub
Sub RemoveQuotes(vB As Variant, vR As Variant)
'returns array vB as vR with all text between pairs
'of double quotes removed, and double quotes themselves
'for example s = "Dim eyes" becomes s =
'A failed quotes pairing returns original string.
Dim str As String, str1 As String, vA As Variant, c As Long
Dim n As Long, m As Long, bUnpaired As Boolean, r As Long
Dim Ub1 As Long, Lb1 As Long, Lb2 As Long, Ub2 As Long
'get bounds of project array
Lb1 = LBound(vB, 1): Ub1 = UBound(vB, 1)
Lb2 = LBound(vB, 2): Ub2 = UBound(vB, 2)
ReDim vR(Lb1 To Ub1, Lb2 To Ub2)
'set up loop to get one line at a time
For c = Lb2 To Ub2
str = vB(8, c)
'split string at double quotes
If str <> "" Then
vA = Split(str, """")
Else
'empty string
str1 = str
GoTo Transfers
End If
'overwrite odd elements to be empty strings
m = UBound(vA) - LBound(vA)
'as long as even num of quote pairs
If m Mod 2 = 0 Then
For n = LBound(vA) To UBound(vA)
'accum even elements
If n Mod 2 = 0 Then
str1 = str1 & vA(n)
End If
Next n
Else
'unpaired double quotes detected
bUnpaired = True
End If
Transfers: 'transfer one row only
For r = Lb1 To Ub1
vR(r, c) = vB(r, c)
Next r
'if all pairs matched
If bUnpaired = False Then
vR(8, c) = str1
Else
'exit loop with str
End If
str1 = "" 'reset accumulator
bUnpaired = False
Next c
End Sub
Sub SplitAtColons(vA As Variant, vR As Variant)
'Because statements and other lines can be placed
'in line and separated by colons, they must be split.
'Splits such into separate lines and increases line count,
'Input array in vA and returns in vR.
'Note: The space after colon is distinct from named arguments
'that have no space after the colon.
Dim vF As Variant, vW As Variant
Dim n As Long, sLine As String, bNotFirst As Boolean
Dim Elem As Variant, m As Long, Upper As Long
Dim Ub1 As Long, Lb1 As Long, Lb2 As Long, Ub2 As Long
'get bounds of project array
Lb1 = LBound(vA, 1): Ub1 = UBound(vA, 1)
Lb2 = LBound(vA, 2): Ub2 = UBound(vA, 2)
ReDim vW(Lb1 To Ub1, Lb2 To Ub2)
ReDim vR(Lb1 To Ub1, 1 To 1)
'pass to work variable
vW = vA
For n = Lb2 To Ub2 'for each line existing
'get line string
sLine = Trim(vW(8, n))
'decide if has colons
'do the split
vF = Split(sLine, ": ")
'does it contain colons?
If UBound(vF) >= 1 Then 'there were non-arg colons
'make a new line in return array for each elem
For Each Elem In vF
Elem = Trim(CStr(Elem))
If Elem <> "" Then
If bNotFirst = True Then
'not first data transfer
'so increment array before transfer
Upper = UBound(vR, 2) + 1
ReDim Preserve vR(Lb1 To Ub1, 1 To Upper)
Else
'is first data transfer
'so just use first element
Upper = UBound(vR, 2)
bNotFirst = True
End If
'transfer line of vW to vR
For m = 1 To 8
vR(m, Upper) = vW(m, n)
Next m
vR(8, Upper) = Elem 'overwrite line string
End If
Next Elem
Else
'no colons - redim array and normal line transfer
If bNotFirst = True Then
'not first data transfer
'so increment array before transfer
Upper = UBound(vR, 2) + 1
ReDim Preserve vR(Lb1 To Ub1, 1 To Upper)
Else
'is first data transfer
'so just use first element
Upper = UBound(vR, 2)
bNotFirst = True
End If
ReDim Preserve vR(Lb1 To Ub1, 1 To Upper)
'transfer line of vW to vR
For m = Lb1 To Ub1
vR(m, Upper) = vW(m, n)
Next m
End If
Next n
End Sub
Sub NumbersToRow(vA As Variant, vR As Variant, Optional nRow As Long = 6)
'adds renumbering of current array lines to row 6.
'and returns vA array in vR. Original numbers still in row 1.
'Optional row number defaults to 6
Dim n As Long
Dim Ub1 As Long, Lb1 As Long, Lb2 As Long, Ub2 As Long
'get bounds of project array
Lb1 = LBound(vA, 1): Ub1 = UBound(vA, 1)
Lb2 = LBound(vA, 2): Ub2 = UBound(vA, 2)
ReDim vR(Lb1 To Ub1, Lb2 To Ub2)
For n = Lb2 To Ub2
vA(nRow, n) = n
Next n
vR = vA
End Sub
Sub MarkLineRanges(vA As Variant, vR As Variant)
'Input array in vA, returned in vR with markings.
'Adds any module and procedure line ranges
'that may apply, for every line of vA. These figures
'will be used for the nominal search line ranges.
Dim nS As Long, sS As String, vW As Variant, n As Long
Dim Ub1 As Long, Lb1 As Long, Lb2 As Long, Ub2 As Long
'get bounds of project array
Lb1 = LBound(vA, 1): Ub1 = UBound(vA, 1)
Lb2 = LBound(vA, 2): Ub2 = UBound(vA, 2)
ReDim vR(Lb1 To Ub1, Lb2 To Ub2)
vW = vA
'MODULE START RANGE
'get the start point values in place
sS = Trim(vW(4, 1)) 'get first module name
nS = CLng(Trim(vW(6, 1))) 'get line number for first module entry
vW(9, Lb2) = nS
For n = Lb2 To Ub2 - 1
'If vW(5, n) = "" Then 'it is a module entry
'count same items
If vW(4, n) = vW(4, n + 1) Then
'still same module name
'so mark start value same
vW(9, n + 1) = nS
Else
'n+1 when not same
sS = vW(4, n + 1)
vW(9, n) = nS
nS = vW(6, n + 1)
vW(9, n + 1) = nS
End If
Next n
'MODULE END RANGE
sS = Trim(vW(4, Ub2)) 'get last module name
nS = CLng(Trim(vW(6, Ub2))) 'get line number for first module entry
vW(10, Ub2) = nS
For n = Ub2 To (Lb2 + 1) Step -1
'If vW(5, n) = "" Then 'it is a module entry
'count same items
If vW(4, n) = vW(4, n - 1) Then
'still same module name
'so mark start value same
vW(10, n - 1) = nS
Else
'n+1 when not same
sS = vW(4, n - 1)
vW(10, n) = nS
nS = vW(6, n - 1)
vW(10, n - 1) = nS
End If
Next n
'PROCEDURE START RANGE
'get the start point values in place
sS = Trim(vW(5, 1)) 'get first procedure name
nS = CLng(Trim(vW(6, 1))) 'get line number proc entry
vW(11, Lb2) = nS
For n = Lb2 To Ub2 - 1
'If vW(5, n) = "" Then 'it is a module entry
'count same items
If vW(5, n) = vW(5, n + 1) Then
'still same module name
'so mark start value same
vW(11, n + 1) = nS
Else
'n+1 when not same
sS = vW(5, n + 1)
vW(11, n) = nS
nS = vW(6, n + 1)
vW(11, n + 1) = nS
End If
Next n
'PROCEDURE END RANGE
sS = Trim(vW(5, Ub2)) 'get last proc name
nS = CLng(Trim(vW(6, Ub2))) 'get line number proc entry
vW(12, Ub2) = nS
For n = Ub2 To (Lb2 + 1) Step -1
'If vW(5, n) = "" Then 'it is a module entry
'count same items
If vW(5, n) = vW(5, n - 1) Then
'still same module name
'so mark start value same
vW(12, n - 1) = nS
Else
'n+1 when not same
sS = vW(5, n - 1)
vW(12, n) = nS
nS = vW(6, n - 1)
vW(12, n - 1) = nS
End If
Next n
'ADD PUBLIC VARIABLE LINE RANGES
'public variable line ranges need not be marked
'since the whole project line range applies
'transfers
vR = vW
End Sub
Sub GetDeclaredVariables(sLine As String, bProcName As Boolean, sScope As String, vRet As Variant)
'Returns an array of declared variables in line string sLine.
'This is used to build the declared variables array (vDec) in RunVarChecks().
'bProcName input is true if sLine project record lists a procedure name, else false.
'sScope outputs scope of line declarations returned in vRet.
'sScope values are "PROCPRIVATE", "DECLARED", "MODPRIVATE", or "VARPUBLIC"
'=========================================================================
'sScope RETURNS:
'"PROCPRIVATE"; returned if declaration is private to a procedure
'"MODPRIVATE"; returned if declaration is private to a module
'"VARPUBLIC"; returned if declaration is public
'"DECLARED"; returned if declared with keyword "Declared" in heading
'=========================================================================
Dim IsDim As Boolean, nL As Long, vF As Variant
Dim Elem As Variant, vS As Variant, vT As Variant
Dim bPrivate As Boolean, bPublic As Boolean, bStatic As Boolean
Dim bPrivPubStat As Boolean, bDeclare As Boolean, bType As Boolean
Dim bSub As Boolean, bFunc As Boolean, bConst As Boolean
Dim n As Long, Upper As Long, bNotFirst As Boolean
' '----------------------------------------------------------------------------
' Handle exclusions: lines that contain any of the declaration keywords;
' "Declare", "Const", and "Type"
' '----------------------------------------------------------------------------
bDeclare = sLine Like "* Declare *" Or sLine Like "Declare *"
bConst = sLine Like "* Const *" Or sLine Like "Const *"
bType = sLine Like "* Type *" Or sLine Like "Type *"
If bDeclare Or bConst Or bType Then
GoTo DefaultTransfer
End If
'----------------------------------------------------------------------------
' Then, check declarations that were made with the "Dim" statement,
' at private module and at procedure level.
'----------------------------------------------------------------------------
'sLine = "Dim IsDim As Boolean, nL As Long, vF(1 to4,4 to 6,7 to 10) As Variant"
sLine = Trim(sLine)
ReDim vT(0 To 0)
IsDim = sLine Like "Dim *"
'could be proc or module level
If IsDim Then
nL = Len(sLine)
sLine = Right(sLine, nL - 4)
'do the first split
sLine = RemoveVarArgs(sLine)
vF = Split(sLine, ",")
'do the second split
For Each Elem In vF
Elem = Trim(CStr(Elem))
If Elem <> "" Then
vS = Split(Elem, " ")
'Optional might still preceed var name
For n = LBound(vS) To UBound(vS)
If vS(n) <> "Optional" Then
'redim the array
If bNotFirst = True Then
'not first data transfer
'so increment array before transfer
Upper = UBound(vT) + 1
ReDim Preserve vT(LBound(vT) To Upper)
Else
'is first data transfer
'so just use first element
ReDim vT(1 To 1)
Upper = UBound(vT)
bNotFirst = True
End If
vT(Upper) = vS(n)
Exit For
End If
Next n
End If
Next Elem
'return results
If UBound(vT, 1) >= 1 Then
If bProcName = True Then
Transfer1: sScope = "PROCPRIVATE"
Else
sScope = "MODPRIVATE"
End If
vRet = vT
Exit Sub 'Function
End If
Else: 'not a dim item so...
GoTo CheckProcLines
End If
CheckProcLines:
'---------------------------------------------------------------------------------
' Check declarations that were made in public and private procedure definitions.
' Procedure definitions made in the module heading with declare are excluded.
'---------------------------------------------------------------------------------
bSub = sLine Like "*Sub *(*[A-z]*)*"
bFunc = sLine Like "*Function *(*[A-z]*)*"
If bSub Or bFunc Then
'obtain contents of first set round brackets
sLine = GetProcArgs(sLine)
'obtain vars without args
sLine = RemoveVarArgs(sLine)
'first split
vF = Split(sLine, ",")
For Each Elem In vF
Elem = Trim(CStr(Elem))
If Elem <> "" Then
'second split
vS = Split(Elem, " ")
'any of Optional, ByVal, ByRef, or ParamArray might preceed var name
For n = LBound(vS) To UBound(vS)
If vS(n) <> "Declare" And vS(n) <> "Optional" And vS(n) <> "ByVal" And _
vS(n) <> "ByRef" And vS(n) <> "ParamArray" Then
'redim the array
If bNotFirst = True Then
'not first data transfer
'so increment array before transfer
Upper = UBound(vT) + 1
ReDim Preserve vT(LBound(vT) To Upper)
Else
'is first data transfer
'so just use first element
ReDim vT(1 To 1)
Upper = UBound(vT)
bNotFirst = True
End If
vT(Upper) = vS(n)
Exit For
End If
Next n
End If
Next Elem
'return results if any found in section
If UBound(vT) >= 1 Then
If bProcName = True Then
Transfers2: sScope = "PROCPRIVATE"
Else
'exits with empty sScope
sScope = ""
End If
vRet = vT
Exit Sub
End If
Else 'not a dec proc line so...
GoTo OtherVarDecs
End If
OtherVarDecs:
'--------------------------------------------------------------------------------------------
' Check variable declarations at module level outside of any procedures that
' use the private, public, or static keywords. Dim decs were considered in first section.
'--------------------------------------------------------------------------------------------
'test line for keywords
bSub = sLine Like "* Sub *"
bFunc = sLine Like "* Function *"
bPrivate = sLine Like "Private *"
bPublic = sLine Like "Public *"
bStatic = sLine Like "Static *"
If bPrivate Or bPublic Or bStatic Then bPrivPubStat = True
'exclude module procs but include mod vars
If bConst Then GoTo DefaultTransfer
If bPrivPubStat And Not bSub And Not bFunc Then
'remove variable args brackets altogether
sLine = RemoveVarArgs(sLine)
'first split
vF = Split(sLine, ",")
For Each Elem In vF
Elem = Trim(CStr(Elem))
If Elem <> "" Then
vS = Split(Elem, " ")
'any of private, public, or withEvents could preceed var name
For n = LBound(vS) To UBound(vS)
If vS(n) <> "Private" And vS(n) <> "Public" And _
vS(n) <> "WithEvents" Then
'redim the array
If bNotFirst = True Then
'not first data transfer
'so increment array before transfer
Upper = UBound(vT) + 1
ReDim Preserve vT(LBound(vT) To Upper)
Else
'is first data transfer
'so just use first element
ReDim vT(1 To 1)
Upper = UBound(vT)
bNotFirst = True
End If
vT(Upper) = vS(n)
Exit For
End If
Next n
End If
Next Elem
'return array and results
If UBound(vT) >= 1 Then
If bPrivate Then
Transfers3: sScope = "MODPRIVATE"
ElseIf bPublic Then
sScope = "VARPUBLIC"
End If
vRet = vT
Exit Sub
End If
Else 'not a mod private ,public, etc, so...
GoTo DefaultTransfer
End If
DefaultTransfer:
'no declarations in this line
'so hand back empty vT(0 to 0)
sScope = ""
vRet = vT
End Sub
Function GetProcArgs(str As String) As String
'Extracts and returns content of FIRST set of round brackets
'This releases the procedure arguments bundle,
'Brackets of arguments themselves removed in RemoveVarArgs.
Dim LeadPos As Long, LagPos As Long
Dim LeadCount As Long, LagCount As Long, Length As Long
Dim n As Long, sTemp1 As String, m As Long
Length = Len(Trim(str))
For n = 1 To Length
If Mid(str, n, 1) = "(" Then
LeadCount = LeadCount + 1
LeadPos = n
For m = LeadPos + 1 To Length
If Mid(str, m, 1) = "(" Then
LeadCount = LeadCount + 1
End If
If Mid(str, m, 1) = ")" Then
LagCount = LagCount + 1
End If
If LeadCount = LagCount And LeadCount <> 0 Then
LagPos = m
'extract the string from between Leadcount and LagCount, without brackets
sTemp1 = Mid(str, LeadPos + 1, LagPos - LeadPos - 1)
GetProcArgs = sTemp1 'return
Exit Function
End If
Next m
End If
Next n
End Function
Function RemoveVarArgs(ByVal str As String) As String
'Removes ALL round brackets and their content from str input.
'Returns modified string in function name RemoveVarArgs.
'============================================================
'Notes: REMOVES ALL ROUND BRACKETS AND THEIR CONTENTS
'the string: dim Arr(1 to 3, 3 to (6+3)), Var() as String
'becomes: dim Arr, Var as String
'============================================================
Dim bIsAMatch As Boolean, LeadPos As Long, LagPos As Long
Dim LeadCount As Long, LagCount As Long, Length As Long
Dim n As Long, sTemp1 As String, sTemp2 As String, m As Long
Do
DoEvents
bIsAMatch = str Like "*(*)*"
If Not bIsAMatch Then Exit Do
Length = Len(Trim(str))
For n = 1 To Length
If Mid(str, n, 1) = "(" Then
LeadCount = LeadCount + 1
LeadPos = n
For m = LeadPos + 1 To Length
If Mid(str, m, 1) = "(" Then
LeadCount = LeadCount + 1
End If
If Mid(str, m, 1) = ")" Then
LagCount = LagCount + 1
End If
If LeadCount = LagCount And LeadCount <> 0 Then
LagPos = m
'remove current brackets and all between them
sTemp1 = Mid(str, LeadPos, LagPos - LeadPos + 1)
sTemp2 = Replace(str, sTemp1, "", 1)
str = sTemp2
Exit For
End If
Next m
End If
bIsAMatch = str Like "*(*)*"
If Not bIsAMatch Then Exit For
Next n
LeadCount = 0
LagCount = 0
LeadPos = 0
LagPos = 0
Loop
RemoveVarArgs = str 'return
End Function
Sub EmptyTheDecLines(vA As Variant, vR As Variant)
'Input array in vA, returned in vR modified.
'Overwrites row 8 line string with empty string
'if line is marked in proj array as a declaration line,
'but leaves other parts of that record intact.
Dim c As Long
Dim Ub1 As Long, Lb1 As Long, Lb2 As Long, Ub2 As Long
'get bounds of project array
Lb1 = LBound(vA, 1): Ub1 = UBound(vA, 1)
Lb2 = LBound(vA, 2): Ub2 = UBound(vA, 2)
ReDim vR(Lb1 To Ub1, Lb2 To Ub2)
vR = vA
For c = Lb2 To Ub2
If vA(13, c) = "Declaration" Then
vR(8, c) = ""
End If
Next c
End Sub
Function MarkProcVarUse(vA As Variant, vT As Variant, vR As Variant) As Boolean
'Updates vDec declared variables array with use data for
'variables declared in procedures.
'Takes vDec in vA and returns modified with markup in vR.
'vT is the project code lines array.
Dim sD As String, sL As String, n As Long, m As Long
Dim Ub1 As Long, Lb1 As Long, Lb2 As Long, Ub2 As Long
'get bounds of vDec input as vA
Lb1 = LBound(vA, 1): Ub1 = UBound(vA, 1)
Lb2 = LBound(vA, 2): Ub2 = UBound(vA, 2)
ReDim vR(Lb1 To Ub1, Lb2 To Ub2)
vR = vA
'step through declared variables array names
For n = LBound(vR, 2) To UBound(vR, 2)
'get one declared variable at a time...
sD = vR(1, n)
'for its associated nominal search lines...
For m = vR(6, n) To vR(7, n)
'and if not a declaration line...
If vT(8, m) <> "" And vR(5, n) = "PROCPRIVATE" Then
'get project line to check...
sL = vT(8, m)
'check project line against all use patterns
If PatternCheck(sL, sD) Then
'mark declared var line as used
vR(8, n) = "Used"
Exit For
Else
End If
End If
Next m
Next n
End Function
Function MarkModVarUse(vA As Variant, vT As Variant, vR As Variant) As Boolean
'Updates vDec declared variables array with use data
'for variables declared at module-private level.
'Takes vDec in vA and returns modified with markup in vR.
'vT is the project code lines array.
Dim sA1 As String, sA2 As String, sL As String, q As Long
Dim sD As String, n As Long, m As Long, vRet As Variant
Dim Ub1 As Long, Lb1 As Long, Lb2 As Long, Ub2 As Long
'get bounds of vDec input as vA
Lb1 = LBound(vA, 1): Ub1 = UBound(vA, 1)
Lb2 = LBound(vA, 2): Ub2 = UBound(vA, 2)
ReDim vR(Lb1 To Ub1, Lb2 To Ub2)
vR = vA
'CHECK MODPRIVATE ALIAS NAMES IN WHOLE MODULES
'without any line restriction
'no harm in doing all modprivate this way first
'step through declared variables array
For n = Lb2 To Ub2
'If item is modprivate...
If vR(5, n) = "MODPRIVATE" Then
'get both alias names for one variable...
sA1 = vR(3, n) & "." & vR(1, n) 'mod.var
sA2 = vR(2, n) & "." & vR(3, n) & "." & vR(1, n) 'proj.mod.var
'for whole module line set...
For m = vR(11, n) To vR(12, n)
'get proj line
sL = vT(8, m)
'check line against vR use patterns...
If PatternCheck(sL, sA1) Then
'mark declared vRriable as used
vR(8, n) = "Used"
Exit For
End If
If PatternCheck(sL, sA2) Then
'mark declared vRriable as used
vR(8, n) = "Used"
Exit For
End If
Next m
Else
'action for not modprivate
End If
Next n
'then...
'CHECK MODPRIVATE SHORT NAMES AGAINST WHOLE MODULES
'excluding proc lines using vars with same names
'step through declared variables array
For n = Lb2 To Ub2
'if not already found to be used in above section...
If vR(5, n) = "MODPRIVATE" And vR(8, n) <> "Used" Then
'get its usual short form var name
sD = vR(1, n)
'get a modified search range to exclude proc same-names
NewRange vR, n, CLng(vR(6, n)), CLng(vR(7, n)), vRet
'search for pattern match in restricted range
For q = LBound(vRet) To UBound(vRet)
'if not a declaration line, and n is modprivate, and a permitted search line
If vT(8, q) <> "" And vR(5, n) = "MODPRIVATE" And vRet(q) = "" Then
'search in project array with line q
sL = vT(8, q)
If PatternCheck(sL, sD) Then
vR(8, n) = "Used"
Exit For
End If
End If
Next q
End If
Next n
End Function
Function MarkPubVarUse(vA As Variant, vT As Variant, vR As Variant) As Boolean
'Updates vDec declared variables array with use data
'for variables declared as public in module heads.
'Takes vDec in vA and returns modified with markup in vR.
'vT is the project code lines array.
Dim sA1 As String, sA2 As String, sL As String, q As Long
Dim sD As String, n As Long, m As Long, vRet As Variant
Dim Ub1 As Long, Lb1 As Long, Lb2 As Long, Ub2 As Long
'get bounds of vDec input as vA
Lb1 = LBound(vA, 1): Ub1 = UBound(vA, 1)
Lb2 = LBound(vA, 2): Ub2 = UBound(vA, 2)
ReDim vR(Lb1 To Ub1, Lb2 To Ub2)
vR = vA
GeneralChecks:
'CHECK VARPUBLIC ALIAS NAMES IN WHOLE PROJECT
'DO THIS IN EVERY CASE
'without any line restrictions
'do this for all varpublic items first
'step through declared variables array
For n = Lb2 To Ub2
'If item is varpublic...
If vR(5, n) = "VARPUBLIC" And vR(8, n) <> "Used" Then
'get both alias names for one variable...
sA1 = vR(3, n) & "." & vR(1, n) 'mod.vRr
sA2 = vR(2, n) & "." & vR(3, n) & "." & vR(1, n) 'proj.mod.vRr
'for whole project line set...
For m = LBound(vT, 2) To UBound(vT, 2)
'get proj line
sL = vT(8, m)
'check line against vR use patterns...
If PatternCheck(sL, sA1) Then
'mark declared vRriable as used
vR(8, n) = "Used"
Exit For
End If
If PatternCheck(sL, sA2) Then
'mark declared vRriable as used
vR(8, n) = "Used"
Exit For
End If
Next m
End If
Next n
'then...
'CHECK VARPUBLIC SHORT NAME USE DEPENDING ON ANY NAME DUPLICATION
'step through declared variables array
For n = Lb2 To Ub2
'if not already found to be used in above section...
If vR(5, n) = "VARPUBLIC" And vR(8, n) <> "Used" Then
'get its usual var name
sD = vR(1, n)
' Ambiguous returns true if other pub vars use same name
If Ambiguous(vR, n) Then
Ambiguous: 'CHECK VARPUBLIC SHORT NAME USE IN MODULES ONLY -similars already checked fully
'get a modified search range to exclude proc same-names
NewRange vR, n, CLng(vR(11, n)), CLng(vR(12, n)), vRet
'run through newly permitted module search lines
For q = LBound(vRet) To UBound(vRet)
'if not a declaration line, and n is modprivate, and a permitted search line
If vT(8, q) <> "" And vR(5, n) = "VARPUBLIC" And vRet(q) = "" Then
'search in project array with line q
sL = vT(8, q)
If PatternCheck(sL, sD) Then
vR(8, n) = "Used"
Exit For
End If
End If
Next q
Else
Unambiguous: 'resolve use when there is no ambiguous variable duplication anywhere
'CHECK VARPUBLIC SHORT NAME USE IN WHOLE PROJECT
'get a modified search range to exclude proc and module same-names
NewPubRange vR, n, LBound(vT, 2), UBound(vT, 2), vRet
'run through newly permitted project search lines
For q = LBound(vRet) To UBound(vRet)
'if not a declaration line, and n is varpublic, and a permitted search line
If vT(8, q) <> "" And vR(5, n) = "VARPUBLIC" And vRet(q) = "" Then
'search in project array with line q
sL = vT(8, q)
If PatternCheck(sL, sD) Then
vR(8, n) = "Used"
Else
End If
End If
Next q
End If
End If
Next n
End Function
Function Ambiguous(vA As Variant, n As Long) As Boolean
'Returns function name as true if the public variable
'in line number n of vDec has duplicated use of its
'name elsewhere in vDec declared variables listing,
'by another public variable, else it is false.
'Public variables CAN exist with same names.
Dim m As Long
Dim Ub1 As Long, Lb1 As Long, Lb2 As Long, Ub2 As Long
'get bounds of vDec input as vA
Lb1 = LBound(vA, 1): Ub1 = UBound(vA, 1)
Lb2 = LBound(vA, 2): Ub2 = UBound(vA, 2)
'step through vDec as vA checking item n against all others
For m = Lb2 To Ub2
'if rows different,names same,projects same,and both varpublic...
If m <> n And vA(1, n) = vA(1, m) And vA(2, n) = vA(2, m) And _
vA(5, n) = "VARPUBLIC" And vA(5, m) = "VARPUBLIC" Then
'there is duplication for public variable name in row n
Ambiguous = True
Exit Function
End If
Next m
End Function
Function NewPubRange(vA As Variant, n As Long, nS As Long, nE As Long, vR As Variant) As Boolean
'Input is vDec array in vA. Returns vR array with search restriction markings.
'Used for public variable use search in MarkPubVarUsewhen there is no ambiguous naming at all.
'The nominal search range is input as nS and nE,and this line range will be marked to search or not.
'Input n is vDec line number for the public variable name that needs a search data range returned.
'vR array elements are marked "X" to avoid that line and "" to search it in the project array.
Dim nSS As Long, nSE As Long
Dim strD As String, m As Long, p As Long
Dim Ub1 As Long, Lb1 As Long, Lb2 As Long, Ub2 As Long
'get bounds of vDec input as vA
Lb1 = LBound(vA, 1): Ub1 = UBound(vA, 1)
Lb2 = LBound(vA, 2): Ub2 = UBound(vA, 2)
'set size of return array equal to number of nominal search lines
'that is for this proc the entire project range
ReDim vR(nS To nE)
'get usual var name
strD = vA(1, n)
'search for variable name in declared variables array
For m = Lb2 To Ub2
'if not same rows, and var name same, and project same, and was used...
'then its proc or module search lines all need excluded from project search
If n <> m And strD = vA(1, m) And vA(2, n) = vA(2, m) And vA(8, m) = "Used" Then
'get item's range to exclude
nSS = vA(6, m) 'start nominal range for samename item
nSE = vA(7, m) 'end nominal range for samename item
'mark vR with exclusion marks
For p = nSS To nSE
vR(p) = "X" 'exclude this line
Next p
End If
Next m
NewPubRange = True
End Function
Function NewRange(vA As Variant, n As Long, nS As Long, nE As Long, vR As Variant) As Boolean
'Used for both public and module variable name search. For short form of name.
'Makes an array that is used to restrict the used-var search range.
'nS and nE are start and end nominal search line numbers.
'Input is vDec in vA, n is vDec line number for variable under test, vR is return array.
'returns array vR marked "X" for exclusion of search where a procedure has a
'same-name variable to that of line n in vDec. Restricts the nominal search range.
Dim nSS As Long, nSE As Long
Dim strD As String, m As Long, p As Long
Dim Ub1 As Long, Lb1 As Long, Lb2 As Long, Ub2 As Long
'get bounds of vDec input as vA
Lb1 = LBound(vA, 1): Ub1 = UBound(vA, 1)
Lb2 = LBound(vA, 2): Ub2 = UBound(vA, 2)
'set size of return array equal to number of nominal search lines
ReDim vR(nS To nE)
'get usual var name
strD = vA(1, n)
'search for variable name in declared variables array
For m = Lb2 To Ub2
'if not same rows, and var name same, and project same, and module same, and has a procedure name,
'and was used...then its proc search lines all need excluded from module search
If n <> m And strD = vA(1, m) And vA(2, n) = vA(2, m) And vA(3, n) = vA(3, m) And _
vA(4, m) <> "" And vA(8, m) = "Used" Then 'in a proc
'get item's range to exclude
nSS = vA(6, m) 'start nominal range for samename item
nSE = vA(7, m) 'end nominal range samename item
'mark vR with exclusion marks
For p = nSS To nSE
vR(p) = "X" 'exclude this line
Next p
End If
Next m
NewRange = True
End Function
Function StartOfRng(vA As Variant, sScp As String, n As Long) As Long
'Returns line number in function name that starts nominal search range.
'Information already on the project array.
Dim Ub1 As Long, Lb1 As Long, Lb2 As Long, Ub2 As Long
'get bounds of project array
Lb1 = LBound(vA, 1): Ub1 = UBound(vA, 1)
Lb2 = LBound(vA, 2): Ub2 = UBound(vA, 2)
'get ranges using new line numbers in row 6
Select Case sScp
Case "PROCPRIVATE"
StartOfRng = vA(11, n)
Case "MODPRIVATE"
StartOfRng = vA(9, n)
Case "VARPUBLIC"
StartOfRng = LBound(vA, 2)
Case "DECLARED"
'StartOfRng = vA(9, n)
Case Else
MsgBox "Dec var scope not found"
End Select
End Function
Function EndOfRng(vA As Variant, sScp As String, n As Long) As Long
'Returns line number in function name for end of used search
'Information already on the project array
Dim Ub1 As Long, Lb1 As Long, Lb2 As Long, Ub2 As Long
'get bounds of project array
Lb1 = LBound(vA, 1): Ub1 = UBound(vA, 1)
Lb2 = LBound(vA, 2): Ub2 = UBound(vA, 2)
'get ranges using new line numbers in row 6
Select Case sScp
Case "PROCPRIVATE"
EndOfRng = vA(12, n)
Case "MODPRIVATE"
EndOfRng = vA(10, n)
Case "VARPUBLIC"
EndOfRng = UBound(vA, 2)
Case "DECLARED"
'EndOfRng = vA(10, n)
Case Else
MsgBox "Dec var scope not found"
End Select
End Function
Sub PrintArrayToSheet(vA As Variant, sSht As String)
'Used at various points in project to display test info
'Writes input array vA to sSht with top left at cells(1,1)
'Sheet writing assumes lower bound of array is 1
'Makes use of Transpose2DArr()
Dim sht As Worksheet, r As Long, c As Long
Dim Ub1 As Long, Lb1 As Long, Lb2 As Long, Ub2 As Long, vRet As Variant
Transpose2DArr vA, vRet
'get bounds of project array
Lb1 = LBound(vRet, 1): Ub1 = UBound(vRet, 1)
Lb2 = LBound(vRet, 2): Ub2 = UBound(vRet, 2)
If Lb1 <> 0 And Lb2 <> 0 And Ub1 <> 0 And Ub2 <> 0 Then
Set sht = ThisWorkbook.Worksheets(sSht)
sht.Activate
For r = Lb1 To Ub1
For c = Lb2 To Ub2
sht.Cells(r, c) = vRet(r, c)
Next c
Next r
sht.Cells(1, 1).Select
Else
'MsgBox "No redundant variables found."
End If
End Sub
Function Transpose2DArr(ByRef vA As Variant, Optional ByRef vR As Variant) As Boolean
' Used in both user form and sheet output displays.
' Transposes a 2D array of numbers or strings.
' Returns the transposed vA array as vR with vA intact.
Dim loR As Long, hiR As Long, loC As Long, hiC As Long
Dim r As Long, c As Long
'find bounds of vA data input array
loR = LBound(vA, 1): hiR = UBound(vA, 1)
loC = LBound(vA, 2): hiC = UBound(vA, 2)
'set vR dimensions transposed
'If Not IsMissing(vR) Then
If IsArray(vR) Then Erase vR
ReDim vR(loC To hiC, loR To hiR)
'End If
'transfer data
For r = loR To hiR
For c = loC To hiC
'transpose vA into vR
vR(c, r) = vA(r, c)
Next c
Next r
Transfers:
'return success for function
Transpose2DArr = True
End Function
Sub StrToNextRow(sIn As String, sSht As String, Optional nCol As Long = 1)
'Writes to next free row of nCol.
'Optional parameter nCol defaults to unity.
'sIn: String input to display, sSht: Worksheet string name to write to.
Dim sht As Worksheet, nRow As Long
Set sht = ThisWorkbook.Worksheets(sSht)
sht.Activate
nRow = Cells(Rows.Count, nCol).End(xlUp).Row + 1
sht.Cells(nRow, nCol).Activate
ActiveCell.Value = sIn
End Sub
Function PatternCheck(sLine As String, sDec As String) As Boolean
'Used to determine whether or not a declared variable is used.
'Returns PatternCheck as true if sDec was used
'in sLine, else false. sDec is the declared variable
'and sLine is the previously modified code line. Modifications
'removed quotes and comments that can cause error.
'Checks against a set of common use patterns.
'Dim sLine As String, sDec As String
Dim bIsAMatch As Boolean, n As Long
Dim Lb2 As Long, Ub2 As Long
For n = Lb2 To Ub2
'if parameter found in format of pattern returns true - else false
'IN ORDER OF FREQUENCY OF USE;
'PATTERNS FOR FINDING WHETHER OR NOT A VARIABLE IS USED IN A LINE STRING
'A = Var + 1 or A = b + Var + c
bIsAMatch = sLine Like "* " & sDec & " *" 'spaced both sides
If bIsAMatch Then Exit For
'Var = 1
bIsAMatch = sLine Like sDec & " *" 'lead nothing and lag space
If bIsAMatch Then Exit For
'B = Var
bIsAMatch = sLine Like "* " & sDec 'lead space and lag nothing
If bIsAMatch Then Exit For
'Sub Name(Var, etc)
bIsAMatch = sLine Like "*(" & sDec & ",*" 'lead opening bracket and lag comma
If bIsAMatch Then Exit For
'B = C(n + Var)
bIsAMatch = sLine Like "* " & sDec & ")*" 'lead space and lag close bracket
If bIsAMatch Then Exit For
'B = "t" & Var.Name
bIsAMatch = sLine Like "* " & sDec & ".*" 'lead space and lag dot
If bIsAMatch Then Exit For
'B = C(Var + n)
bIsAMatch = sLine Like "*(" & sDec & " *" 'lead open bracket and lag space
If bIsAMatch Then Exit For
'B = (Var)
bIsAMatch = sLine Like "*(" & sDec & ")*" 'lead open bracket and lag close bracket
If bIsAMatch Then Exit For
'Var.Value = 5
bIsAMatch = sLine Like sDec & ".*" 'lead nothing and lag dot
If bIsAMatch Then Exit For
'A = Var(a, b)
'Redim Var(1 to 6, 3 to 8) 'ie: redim is commonly treated as use, but never as declaration.
bIsAMatch = sLine Like "* " & sDec & "(*" 'lead space and lag open bracket
If bIsAMatch Then Exit For
'Var(a) = 1
bIsAMatch = sLine Like sDec & "(*" 'lead nothing and lag open bracket
If bIsAMatch Then Exit For
'B = (Var.Name)
bIsAMatch = sLine Like "*(" & sDec & ".*" 'lead opening bracket and lag dot
If bIsAMatch Then Exit For
'SubName Var, etc
bIsAMatch = sLine Like "* " & sDec & ",*" 'lead space and lag comma
If bIsAMatch Then Exit For
'B = (Var(a) - c)
bIsAMatch = sLine Like "*(" & sDec & "(*" 'with lead open bracket and lag open bracket
If bIsAMatch Then Exit For
'Test Var:=Name
bIsAMatch = sLine Like "* " & sDec & ":*" 'lead space and lag colon
If bIsAMatch Then Exit For
'Test(A:=1, B:=2)
bIsAMatch = sLine Like "*(" & sDec & ":*" 'lead opening bracket and lag colon
If bIsAMatch Then Exit For
'SomeSub str:=Var
bIsAMatch = sLine Like "*:=" & sDec 'lead colon equals and lag nothing
If bIsAMatch Then Exit For
'test arg1:=b, arg2:=A + 1
bIsAMatch = sLine Like "*:=" & sDec & " *" 'lead colon equals and lag space
If bIsAMatch Then Exit For
'test arg1:=b, arg2:=A(1) + 1
bIsAMatch = sLine Like "*:=" & sDec & "(*" 'lead colon equals and lag opening bracket
If bIsAMatch Then Exit For
'SomeSub (str:=Var)
bIsAMatch = sLine Like "*:=" & sDec & ")*" 'lead colon equals and lag closing bracket
If bIsAMatch Then Exit For
'SomeSub str:=Var, etc
bIsAMatch = sLine Like "*:=" & sDec & ",*" 'lead colon equals and lag comma
If bIsAMatch Then Exit For
'SomeSub str:=Var.Value etc
bIsAMatch = sLine Like "*:=" & sDec & ".*" 'lead colon equals and lag dot
If bIsAMatch Then Exit For
'SomeModule.Var.Font.Size = 10
' bIsAMatch = sLine Like "*." & sDec & ".*" 'lead dot and lag dot
' If bIsAMatch Then Exit For
'SomeModule.Var(2) = 5
' bIsAMatch = sLine Like "*." & sDec & "(*" 'lead dot and lag opening bracket
' If bIsAMatch Then Exit For
'SomeModule.Var = 3
' bIsAMatch = sLine Like "*." & sDec & " *" 'lead dot and lag space
' If bIsAMatch Then Exit For
Next n
If bIsAMatch Then
PatternCheck = True
'MsgBox "Match found"
Exit Function
Else
'MsgBox "No match found"
'Exit Function
End If
End Function
Sub AutoLayout(vA As Variant, Optional bTranspose As Boolean = False)
' Takes array vA of say, 4 columns of data and
' displays on textbox in tabular layout.
' Needs a userform called ViewVars and a textbox
' called Textbox1. Code will adjust layout.
' Transpose2DArr used only to return data to (r, c) format.
Dim vB As Variant, vL As Variant, vR As Variant
Dim r As Long, c As Long, m As Long, sS As String
Dim nNumPadSp As Long, TxtLab As Control, MaxFormWidth As Long
Dim sAccum As String, sRowAccum As String, bBold As Boolean
Dim nLineLen As Long, BoxFontSize As Long, BoxFontName As String
Dim sLabAccum As String, nLabPadSp As Long, oUserForm As Object
Dim Backshade As Long, BoxShade As Long, BoxTextShade As Long
Dim ButtonShade As Long, ButtonTextShade As Long
Dim Lb1 As Long, Ub1 As Long, Lb2 As Long, Ub2 As Long
Dim TextLength As Long, bItalic As Boolean
' decide to transpose input or not
If bTranspose = True Then
Transpose2DArr vA, vR
vA = vR
End If
' get bounds of display array
Lb1 = LBound(vA, 1): Ub1 = UBound(vA, 1)
Lb2 = LBound(vA, 2): Ub2 = UBound(vA, 2)
ReDim vL(Lb2 To Ub2) ' make labels array
ReDim vB(Lb2 To Ub2) ' dimension column width array
'--------------------------------------------------------------
' SET USER OPTIONS HERE
'--------------------------------------------------------------
' set the name of the userform made at design time
Set oUserForm = ViewVars
' set limit for form width warning
MaxFormWidth = 800
' make column labels for userform - set empty if not needed
vL = Array("Variable", "Procedure", "Module", "Project")
' colors
Backshade = RGB(31, 35, 44) 'almost black - used
ButtonShade = RGB(0, 128, 128) 'blue-green - not used
BoxShade = RGB(0, 100, 0) 'middle green - used
ButtonTextShade = RGB(230, 230, 230) 'near white - not used
BoxTextShade = RGB(255, 255, 255) 'white - used
' Font details are to be found below
'--------------------------------------------------------------
' find maximum width of array columns
' taking account of label length also
For c = Lb2 To Ub2
m = Len(vL(c)) 'label
For r = Lb1 To Ub1
sS = vA(r, c) 'value
If Len(sS) >= m Then
m = Len(sS)
End If
Next r
'exits with col max array
vB(c) = m
m = 0
Next c
' For testing only
' shows max value of each column
' For c = LB2 To UB2
' MsgBox vB(c)
' Next c
For r = Lb1 To Ub1
For c = Lb2 To Ub2
If c >= Lb2 And c < Ub2 Then
' get padding for current element
nNumPadSp = vB(c) + 2 - Len(vA(r, c))
Else
' get padding for last element
nNumPadSp = vB(c) - Len(vA(r, c))
End If
' accumulate line with element padding
sAccum = sAccum & vA(r, c) & Space(nNumPadSp)
' get typical line length
If r = Lb1 Then
sRowAccum = sRowAccum & vA(Lb1, c) & Space(nNumPadSp)
nLineLen = Len(sRowAccum)
End If
Next c
' accumulate line strings
sAccum = sAccum & vbNewLine
Next r
' accumulate label string
For c = Lb2 To Ub2
If c >= Lb2 And c < Ub2 Then
' get padding for current label
nLabPadSp = vB(c) + 2 - Len(vL(c))
Else
' get padding for last element
nLabPadSp = vB(c) - Len(vL(c))
End If
' accumulate the label line
sLabAccum = sLabAccum & vL(c) & Space(nLabPadSp)
Next c
' load user form
Load oUserForm
'================================================================
' SET FONT DETAILS HERE. THESE AFFECT ALL AUTOSIZING.
'================================================================
BoxFontSize = 12 'say between 6 to 20 points
bBold = True 'True for bold, False for regular
bItalic = False 'True for italics, False for regular
BoxFontName = "Courier" 'or other monospaced fonts eg; Consolas
'================================================================
' make the labels textbox
Set TxtLab = oUserForm.Controls.Add("Forms.TextBox.1", "TxtLab")
' format the labels textbox
With TxtLab
.WordWrap = False
.AutoSize = True 'extends to fit text
.Value = ""
.font.Name = BoxFontName
.font.SIZE = BoxFontSize
.font.Bold = bBold
.font.Italic = bItalic
.ForeColor = BoxTextShade
.Height = 20
.Left = 20
.Top = 15
.Width = 0
.BackStyle = 0
.BorderStyle = 0
.SpecialEffect = 0
End With
'apply string to test label to get length
TxtLab.Value = sLabAccum & Space(4)
TextLength = TxtLab.Width
'MsgBox TextLength
'format userform
With oUserForm
.BackColor = Backshade
.Width = TextLength + 40
.Height = 340
.Caption = "Redundant variables list..."
End With
' check user form is within max width
If oUserForm.Width > MaxFormWidth Then
MsgBox "Form width is excessive"
Unload oUserForm
Exit Sub
End If
'format the data textbox
With oUserForm.TextBox1
.ScrollBars = 3
.WordWrap = True
.MultiLine = True
.EnterFieldBehavior = 1
.BackColor = BoxShade
.font.Name = BoxFontName
.font.SIZE = BoxFontSize
.font.Bold = bBold
.font.Italic = bItalic
.ForeColor = BoxTextShade
.Height = 250
.Left = 20
.Top = 40
.Width = TextLength
.Value = sAccum
End With
'show the user form
oUserForm.Show
End Sub