Extractng Variable Names from VBA Scripts

Question

I want to Get List of Variables used in the Script i.e. VariableName13, strDLink, strZone.
A single file contains about 150+ events and each project contains about 700-900 files.

In VBA environment, I want to parse through each file, loop through each event and extract the Variable names declared or referenced by the Events.

I did find some material like Roslyn or TypeLib but unable to understand how to use them?

Can someone please share a proper approach to extract the variables?

Environment: VBA 6, SCADA HMI

Private Sub Rect13_Click()
Dim lResult As Long
Dim strDLink As String
Dim strZone As String
On Error GoTo ErrorHandler
lResult = OpenFuncUpdate
If lResult = SomeValue Then
    'DoThis
ElseIf lResult = SomeOtherValue Then
    strDLink = "FullPathLink"
    FuncDisassemblePath strDLink, , , , , , , , , , , , strZone
    If Len(strZone) > 0 And (InStr(VariableName13.CurrentValue, "%") = 0) Then
        SubLoadIA strZone & "%" & VariableName13.CurrentValue, Me
    Else
        SubLoadIA VariableName13.CurrentValue, Me
    End If
End If
Exit Sub
ErrorHandler:
    SubHandleError
End Sub

Answer 1

Depending on how you define what a "variable" is, you can try to parse VBA code with VBA code and regular expressions.

If all your declarations are consistently made, and consistently declare a single variable, and variables are consistently declared (Option Explicit is in every module), then capturing Dim|Private|Public|Friend|Global {identifier} should be good-enough... but that makes a lot of "ifs"

Real-life projects have Dim statements that can declare a list of local or private variables. Or there's a ReDim statement somewhere that's actually declaring an array on-the-spot. Or they don't always specify Option Explicit and variables aren't always even declared at all. Or there's a line continuation in the middle of the statement that breaks the regular expression. Or, or, or... so many things can go wrong, parsing VBA code is a rabbit hole.

For example suppose you need to pick up and list undeclared variables. A regular expression can't tell its usage from a procedure or function call, because they're syntactically identical. Regular expressions are missing the context of the grammar - and it's by tokenizing (aka "lexing") the source code and then parsing the tokens using parser rules that we can be 100% certain of what we're looking at.

Fortunately this is a solved problem, and there's free, open-source VBIDE tooling available for this, and get you 100% correct results every time without writing a single line of code or worrying about what legal declarations might be left unaccounted for.

Rubberduck (I manage this OSS project and own its website) will correctly parse any legal VB6/vBA code (and if it doesn't, we're extremely interested in a repro!), and then you can simply click a "copy" button to instantly have every single declaration in the clipboard:

Ctrl+V /paste onto a worksheet (or a Word document, or in Notepad!) and then you can easily turn it into a filter-enabled table; in your case you'd want to filter the [Declaration Type] for "Variable":

Above, the exported declarations for a VBA project that has a Sheet1 module with a test procedure that uses (but doesn't declare) a variable named undeclared:

Sub test()
    undeclared = 42
    Debug.Print undeclared
End Sub

---

Here's the same table for the code you've provided:

Note that SubHandleError and other Sub and Function calls would parse as and resolve to a procedure/function in your project. Here they're being picked up as undeclared variables because I didn't parse anything other than the code you supplied, so these identifiers are undefined.