CODEWITHSUNDEEP
vbaexcel
Harish Rachakonda
Harish Rachakonda

Reputation: 1611

How to call a module in ThisWorkbook in excel vba

I have some macros in Module1 inside modules i.e. Module1 has:

Sub Macro1
   ' Code
End Sub

Sub Macro2
   ' Code
End Sub

Now, I want to call this entire Module1 in the ThisWorkbook available within the Microsoft Excel Objects i.e.

Inside ThisWorkbook:

Sub CallingModule
     **Call Module1 (I want to call in this way)**
End Sub

but, this is not the correct procedure to call. Please tell me the correct procedure to call a Module.

Upvotes: 2

Views: 19548

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149325

Like Rory mentioned above you call specific routines, not an entire module. However if you want to call all the routines Macro1, Macro2, Macro3 etc from a module then is it possible?

YES

Please note that if your Module1 has SIMPLE ROUTINES as shown below then yes, it is possible to call all the procedures in a module.

Let's say you have these in Module1

Sub Sample1()
    MsgBox "I am Sample1"
End Sub

Sub Sample2()
    MsgBox "I am Sample2"
End Sub

Sub Sample3()
    MsgBox "I am Sample3"
End Sub

Sub Sample4()
    MsgBox "I am Sample4"
End Sub

enter image description here

Now simply paste this code in Module2. You also need to set a reference to Microsoft Visual Basic For Applications Extensibility xx.xx library.

'~~> Code adapted from http://www.cpearson.com/excel/vbe.aspx
Sub CallModule1()
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule
    Dim LineNum As Long, NumLines As Long
    Dim ProcName As String
    Dim ProcKind As VBIDE.vbext_ProcKind
    Dim MyAr() As String
    Dim n As Long

    Set VBProj = ActiveWorkbook.VBProject
    Set VBComp = VBProj.VBComponents("Module1")
    Set CodeMod = VBComp.CodeModule

    With CodeMod
        LineNum = .CountOfDeclarationLines + 1
        Do Until LineNum >= .CountOfLines
        ReDim Preserve MyAr(n)
            ProcName = .ProcOfLine(LineNum, ProcKind)

            '~~> Store the routine names in an array
            MyAr(n) = ProcName
            n = n + 1

            LineNum = .ProcStartLine(ProcName, ProcKind) + _
                    .ProcCountLines(ProcName, ProcKind) + 1
        Loop
    End With

    '~~> This is where I am running every routine from Module1
    For n = LBound(MyAr) To UBound(MyAr)
        Run "Module1." & MyAr(n)
    Next n
End Sub

Function ProcKindString(ProcKind As VBIDE.vbext_ProcKind) As String
    Select Case ProcKind
        Case vbext_pk_Get
            ProcKindString = "Property Get"
        Case vbext_pk_Let
            ProcKindString = "Property Let"
        Case vbext_pk_Set
            ProcKindString = "Property Set"
        Case vbext_pk_Proc
            ProcKindString = "Sub Or Function"
        Case Else
            ProcKindString = "Unknown Type: " & CStr(ProcKind)
    End Select
End Function

When you run the routine CallModule1(), then each and every procedure from Module1 will run automatically.

Upvotes: 6

Related Questions