Wednesday, June 13, 2007

Extract Procedure Names from all Modules - VBA

Count No of Programs in a Workbook

Workbooks have Macros, If you need to know if the Workbook contains macros and then the following proc will get you the macros in the workbook

Sub Extract_Program()

Dim VBP As VBProject
Dim VBModule As CodeModule
Dim VBProc As VBComponent
Dim sLastProcName As String
Dim arProcName() As String
Dim iProcCount As Integer

Set VBP = ThisWorkbook.VBProject

For Each VBM In VBP.VBComponents

Set VBModule = VBM.CodeModule

i = 1
Do Until i >= VBModule.CountOfLines

procname = VBModule.ProcOfLine(i, vbext_pk_Proc)
i = i + 1
If LenB(procname) <> 0 Then
If procname <> sLastProcName Then
iProcCount = iProcCount + 1
ReDim Preserve arProcName(iProcCount)
arProcName(iProcCount) = procname
sLastProcName = procname
End If
End If
Loop
Next

' List all procedures
For i = 1 To UBound(arProcName)
MsgBox arProcName(i)
Next i
End Sub

4 comments:

  1. wats is the meaning of VBM, where is it declared, i am trying to execute this logic using c# but i am stuck at vbm.

    "For Each VBM In VBP.VBComponents"

    Regards,
    Jaipal

    ReplyDelete
  2. Hi Jaipal

    VBM is a VBComponent. You can declare it as a VBComponent or simply an Object

    Cheers
    Shasur

    ReplyDelete
  3. Anonymous1:13 PM

    it needs clear explanation and variables. It is no easy for who is new to VBA.
    Be clear please!

    ReplyDelete
  4. Anonymous1:12 PM

    For those of us who use Option Explicit, it helps to define the vars.

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.