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.

 

[bjdesa]

Norie,
Thanks for trying to help me. I would be just happy with the just the list. I can also get the names of all the variables by another method. I have developed a program that can extract all the words in a document that way I will have an index. The index will contain all the variables in the list and even more. So please don’t feel compelled to help me if you don’t really have a solution. As for VBA function they are all lame and I write my own functions using FORTRAN and then I import them into VBA Excel.

 

[Zack Barresse]

Congrats on reinventing the wheel. Personally I think you're a little crazy. Anyway, here is the code for a userform to extract all variables from any selected code module...

Option Explicit

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 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
    
    On Error Resume Next
    
    Set WB = Workbooks.Add(xlWBATWorksheet)
    Set WS = WB.Sheets(1)
    
    iLine = 1
    iRow = 4
    bLocked = 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 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)
        sLine = Right(sLine, Len(sLine) - 4)
        
        iCnt = Len(sLine) - Len(Replace(sLine, ",", ""))
        
        Erase aVars()
        ReDim aVars(iCnt) As String
        aVars = Split(sLine, ", ")
        
        For i = LBound(aVars) To UBound(aVars)
        
            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*"
            End If
            
            iRow = iRow + 1
            
        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"
    WS.Cells(3, 3).Value = "* assumed type"
    WS.Cells.EntireColumn.AutoFit
    
End Sub

Private 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

As sloppy as it may be, and for whatever reasons you want this for, it will list variables for you. Also attached is a userform with the code and controls already packaged. If you want to re-create the wheel, as it seems you do, the code needs a few controls: a listbox named lbModules and two command buttons, one called cmbCancel and the other cmbOK. This code will filter out any ReDim statements, any lines not starting with Dim, any commented lines, as well as the secondary portion of any split lines after a colon, regardless of any variables. Amend as desired. Post back if you have any questions.

Don't forget to set the needed reference.

Good luck.

HTH

 

[Norie]

bjdesa

If the VBA functions are so lame why are you using VBA/Excel?

 

[bjdesa]

Norie,
Here is an example why I don’t like using VBA functions. If I use a function that I created in FORTRAN and compiled into DLL the function actually runs much faster and beats VBA-EXCEl. My customer likes VBA EXCEL cause that way they don’t have to pay to train a person to use a software like Thermal Desktop nor do they have to have to buy a thermal desktop license. So here I am developing a two phase flow modeling software using VBA. But then I have to use REFPROP to provide the thermodynamic state and physical property of the fluid. NIST used FORTRAN to develop RefProp and provided this DLL file. Then they used VBA make calls to the functions in the DLL file. That code is slow and has too much overhead. So I am trying to reduce overhead.

'logarithm to base of 10
Public Declare Function logb10 Lib "Z:\Visual Studio 2008\Projects\MathFunctions\MathFunctions\Release\mathfunctions.dll" (A1 As Double) As Double
Private Function logb10(x As Double) As Double
logb10 = Log(x) / Log(10)
End Function
Option Explicit
'
Sub test()
Dim Time1 As Double
Dim Time2 As Double
Dim count1 As Long
Dim x As Double
Dim WallClockTime As Double
Dim Str1 As String
'
'Test VBA Speed
Time1 = Time
For count1 = 1 To 100000000
'x = Log10(1000)
Str1 = ColumnLetter(16300)
Next count1
Time2 = Time
WallClockTime = (Time2 - Time1) * 86400
Worksheets("Sheet1").Cells(1, 2) = WallClockTime
'MsgBox "Time Taken in Seconds: " & WallClockTime
'
'Test FORTRAN 90 Speed
Time1 = Time
For count1 = 1 To 100000000
'x = logb10(1000)
Str1 = ColumnLetter(16300)
Next count1
Time2 = Time
WallClockTime = (Time2 - Time1) * 86400
Worksheets("Sheet1").Cells(2, 2) = WallClockTime
'MsgBox "Time Taken in Seconds: " & WallClockTime
End Sub

 

[Zack Barresse]

DLL's will generally run faster as compiled code than VBA functions will. Not sure what you're expecting here. Write your own DLL for Excel.

Also, please use the CODE tags when posting. I've edited your post to contain them.

 

[shg]

Private Function logb10(x As Double) As Double
logb10 = Log(x) / Log(10)
End Function

Given the emphasis on efficiency, you might want to recode that:

Function Logb10(d As Double) As Double
    Const Log10 As Double = 2.30258509299405
    
    Logb10 = Log(d) / Log10
End Function

 

[meldoc]

Hello Zack,

I've seen you attach files to threads before, but I haven't been able to access them, are they for the OPs eyes only?

 

[Zack Barresse]

No, anyone logged in can access them. I zipped the files as a direct export from the VBIDE. You need to save the file to your system, unzip it, then import it.

 

[Norie]

bjdesa

I realise VBA/Excel might be slower than FORTRAN for this sort of calculation, kind of a given that I suppose.

I also thought you might be using Excel because it was the preferred medium.

What I don't quite understand is that you seem to be saying there is already a DLL for this but it's not what you want/need.

PS In the code you've posted what exactly is being tested?

You seem to have 2 logb10 functions, one a VBA UDF and the other imported from the mathfunctions DLL.

Or am I missing something?

 

[xenou]

Hello Zack,

I've seen you attach files to threads before, but I haven't been able to access them, are they for the OPs eyes only?

Zack, I've also noticed attachments in a few of your posts. I can never download them either (it's a puzzle to me how you do this as I was under the impression that this board doesn't allow attachments). I get this message when I click the attachment link in your post:

<img alt="message" src="http://northernocean.net/etc/mrexcel/20101017_message.png" />

Cheers,
ξ