vba 如何在excel vba中调用ThisWorkbook中的模块
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25807069/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to call a module in ThisWorkbook in excel vba
提问by Harish Rachakonda
I have some macros in Module1 inside modules i.e. Module1 has:
我在模块内部的 Module1 中有一些宏，即 Module1 有：
Sub Macro1
' Code
End Sub
Sub Macro2
' Code
End Sub
Now, I want to call this entire Module1in the ThisWorkbookavailable within the Microsoft Excel Objects i.e.
现在，我想在 Microsoft Excel 对象Module1中的ThisWorkbook可用内容中调用整个，即
Inside ThisWorkbook:
内部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.
但是，这不是正确的调用程序。请告诉我调用模块的正确程序。
回答by Siddharth Rout
Like Rory mentioned above you call specific routines, not an entire module. However if you want to call all the routines Macro1, Macro2, Macro3etc from a module then is it possible?
像上面提到的 Rory 一样，您调用特定的例程，而不是整个模块。但是，如果您想从模块中调用所有例程Macro1、Macro2、Macro3等，那么有可能吗？
YES
是的
Please note that if your Module1has SIMPLE ROUTINESas shown below then yes, it is possible to call all the procedures in a module.
请注意，如果您Module1有如下所示的简单程序，那么是的，可以调用模块中的所有过程。
Let's say you have these in Module1
假设你有这些 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
Now simply paste this code in Module2. You also need to set a reference to Microsoft Visual Basic For Applications Extensibility xx.xxlibrary.
现在只需将此代码粘贴到Module2. 您还需要设置对Microsoft Visual Basic For Applications Extensibility xx.xx库的引用。
'~~> 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.
当您运行该例程时CallModule1()，Module1 中的每个过程都将自动运行。
