List of variables in a Module

[bjdesa]

Yes I have been looking for a way to get VBA to list all the variables in a module. I did find some code that lists all the functions from Microsoft’s MSDN website. In the VBA editor I know there is a local window that lists all the variables but the stupid thing allows me to copy only one variable at a time. The same is true if I use the F2 key to bring up the object browser. In FORTRAN the IDE allows me to copy the whole list. Would appreciate any help you could provide. Having a list of variables helps me to debug code.

 

[Norie]

Zack

The file can't be saved either.

Isn't the link to a php file/function/module or something?

That's what it seems to be when you right click and select Save as...

 

[Zack Barresse]

I can download just fine by clicking on it. The file is a zip file. I'll look into it, not really sure what's going on.

Edit: regardless, the code is supplied. There were only three controls on the userform in which this ran. Oh, and the columns of the listbox obviously need to be increased to 3. Other than that, change the names of the controls and plug-n-play the code.

 

[meldoc]

Hello again Zack

Sorry I had to post and run, I get the same message as xenou, when I try.
I also get the same message when I try to access the link below, and others.

http://www.mrexcel.com/forum/showpost.php?p=1982777&postcount=241

Link from this thread.

http://www.mrexcel.com/forum/showthread.php?t=399380

 

[Zack Barresse]

Try a direct download link:

http://exceltables.com/files/excel/ListAllVariables_WithUF.xlsm

 

[meldoc]

That works thanks.

 

[Zack Barresse]

Code updated to handle multiple lines, separated by an underscore:

Option Explicit

'REFERENCE SET:
'Microsoft Visual Basic for Applications Extensibility 5.3

Private Sub cmbCancel_Click()
    Unload Me
End Sub

Private Sub cmbOK_Click()

    Dim i As Long
    
    With Me.lbModules
    
        For i = 0 To .ListCount - 1
            If .Selected(i) = True Then
                Call ListAllVariables(.List(i, 0), .List(i, 1))
                Exit For
            End If
        Next i
        
    End With
    
    Unload Me
    
End Sub

Private Sub lbModules_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Call cmbOK_Click
End Sub

Private Sub UserForm_Initialize()

    Dim VBproj As VBIDE.VBProject
    Dim VBmod As VBIDE.VBComponent
    Dim iLine As Long, aVals() As Variant
    Dim sName As String
    
    iLine = 1
    
    On Error Resume Next
    
    For Each VBproj In Application.VBE.VBProjects
    
        If Len(VBproj.Filename) = 0 Then GoTo SkipProj
        
        If VBproj.Protection <> vbext_pp_locked Then
        
            For Each VBmod In VBproj.VBComponents
            
                ReDim Preserve aVals(1 To 3, 1 To iLine)
                sName = Right(VBproj.Filename, Len(VBproj.Filename) - _
                    InStrRev(VBproj.Filename, Application.PathSeparator))
                aVals(1, iLine) = sName
                aVals(2, iLine) = VBmod.Name
                aVals(3, iLine) = ComponentTypeToString(VBmod.Type)
                iLine = iLine + 1
                
            Next VBmod
            
        End If
        
SkipProj:

    Next VBproj
    If iLine > 1 Then
        Me.lbModules.List = Application.Transpose(aVals())
    End If
    
End Sub

Private Sub ListAllVariables(sProjName As String, sModName As String)

    Dim vbeProj As VBIDE.VBProject, vbeComp As VBIDE.VBComponent
    Dim VBmod As VBIDE.CodeModule
    Dim WB As Workbook, WS As Worksheet
    Dim iLine As Long, sLine As String
    Dim iCnt As Long, iRow As Long, i As Long
    Dim aVars() As String, bLocked As Boolean
    Dim bNextLine As Boolean, bAssumed As Boolean
    
    On Error Resume Next
    
    Set WB = Workbooks.Add(xlWBATWorksheet)
    Set WS = WB.Sheets(1)
    
    iLine = 1
    iRow = 4
    bLocked = False
    bNextLine = False
    bAssumed = False
    
    Set vbeProj = Application.Workbooks(sProjName).VBProject
    If vbeProj.Protection = vbext_pp_locked Then
        bLocked = True
        GoTo SkipModule
    End If
    
    Set vbeComp = vbeProj.VBComponents(sModName)
    Set VBmod = vbeComp.CodeModule
    
    For iLine = 1 To VBmod.CountOfLines
    
        sLine = Trim(VBmod.Lines(iLine, 1))
        
        If InStr(1, sLine, "Dim", vbTextCompare) = 0 Then
            If bNextLine = False Then GoTo SkipLine
        End If
        If InStr(1, sLine, "Dim""", vbTextCompare) <> 0 Then GoTo SkipLine
        If InStr(1, sLine, "ReDim", vbTextCompare) <> 0 Then GoTo SkipLine
        If Left(Trim(sLine), 1) = "'" Then GoTo SkipLine
        If InStr(1, sLine, ": ", vbTextCompare) <> 0 Then sLine = Left(sLine, InStr(1, sLine, ": ", vbTextCompare) - 1)
        If bNextLine = True Then
            bNextLine = False
        Else
            sLine = Right(sLine, Len(sLine) - 4)
        End If
        
        iCnt = Len(sLine) - Len(Replace(sLine, ",", ""))
        
        Erase aVars()
        ReDim aVars(iCnt) As String
        aVars = Split(sLine, ", ")
        
        For i = LBound(aVars) To UBound(aVars)
            
            If Trim(aVars(i)) = "_" Then
                bNextLine = True
                GoTo SkipVar
            End If
            
            If InStr(1, aVars(i), " As ", vbTextCompare) <> 0 Then
                WS.Cells(iRow, 1).Value = Trim(Split(aVars(i), " As ")(0))
                WS.Cells(iRow, 2).Value = Trim(Split(aVars(i), " As ")(1))
            Else
                WS.Cells(iRow, 1).Value = Trim(aVars(i))
                WS.Cells(iRow, 2).Value = "Variant*"
                bAssumed = True
            End If
            
            iRow = iRow + 1
            
SkipVar:
            
        Next i
            
SkipLine:
        
    Next iLine
    
SkipModule:

    If iRow = 4 Then
        WB.Close False
        If bLocked = True Then
            MsgBox "The project is locked.", vbInformation
        Else
            MsgBox "No variables were found.", vbInformation
        End If
        Exit Sub
    End If
    
    WS.Cells(1, 1).Value = sProjName
    WS.Cells(1, 2).Value = sModName
    WS.Cells(3, 1).Value = "VARIABLE"
    WS.Cells(3, 2).Value = "TYPE"
    If bAssumed = True Then WS.Cells(3, 3).Value = "* assumed type"
    WS.Cells.EntireColumn.AutoFit
    
End Sub

Function ComponentTypeToString(ComponentType As VBIDE.vbext_ComponentType) As String
    Select Case ComponentType
        Case vbext_ct_ActiveXDesigner
            ComponentTypeToString = "ActiveX Designer"
        Case vbext_ct_ClassModule
            ComponentTypeToString = "Class Module"
        Case vbext_ct_Document
            ComponentTypeToString = "Document Module"
        Case vbext_ct_MSForm
            ComponentTypeToString = "UserForm"
        Case vbext_ct_StdModule
            ComponentTypeToString = "Code Module"
        Case Else
            ComponentTypeToString = "Unknown Type: " & CStr(ComponentType)
    End Select
End Function

Edit: Added double-click event to the listbox.

 

[bjdesa]

shg4421<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Yes your are 100% right but an even faster thing would be <o></o>
<o></o>
Logb10 = Log(d) *0.43429448190325<o></o>
<o></o>
Division is more costly operation<o></o>
<o> </o>

 

[bjdesa]

Norie,
You are right I had a typo the two function should not have the same names.