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
Wednesday, June 13, 2007
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.
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.
ReplyDelete"For Each VBM In VBP.VBComponents"
Regards,
Jaipal
Hi Jaipal
ReplyDeleteVBM is a VBComponent. You can declare it as a VBComponent or simply an Object
Cheers
Shasur
it needs clear explanation and variables. It is no easy for who is new to VBA.
ReplyDeleteBe clear please!
For those of us who use Option Explicit, it helps to define the vars.
ReplyDelete