vba 检查函数/子是否存在于另一个工作簿中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16192144/
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
Check if function/sub is present in another workbook
提问by AdamDynamic
I am writing a macro that includes a call on a sub in a different workbook. I can get the sub in the other workbook to run using code in the original workbook. I'm trying to first check whether the sub/function exists (error handling in case the sub has been renamed etc.).
我正在编写一个宏，其中包括对不同工作簿中的子程序的调用。我可以使用原始工作簿中的代码运行另一个工作簿中的子程序。我试图首先检查子/函数是否存在（如果子被重命名等的错误处理）。
回答by Our Man in Bananas
you can either handle an error from a missing procesure, or you can use Visual Basic for Applications Extensibility Library
您可以处理丢失过程中的错误，也可以使用Visual Basic for Applications Extensibility Library
This is well documented on Chip Pearson's website
here is the code I found on Chip Peartsons website ages agoand modified:
这是我很久以前在Chip Peartsons 网站上找到并修改过的代码：
Option Explicit
Function checkProcName(wBook As Workbook, sModuleName As String, sProcName As String) As Boolean
' ===========================================================================
' Found on http://www.cpearson.com at http://www.cpearson.com/excel/vbe.aspx
' then modified
'
' USAGE:
' to check if a procedure exists, call 'checkProcName' passing
' in the target workbook (which should be open), the Module,
' and the procedure name
'
' ===========================================================================
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim ProcName As String
Dim LineNum As Integer
Dim ProcKind As VBIDE.vbext_ProcKind
checkProcName = False
Set VBProj = wBook.VBProject
Set VBComp = VBProj.VBComponents(sModuleName)
Set CodeMod = VBComp.CodeModule
With CodeMod
LineNum = .CountOfDeclarationLines + 1
Do Until LineNum >= .CountOfLines
ProcName = .ProcOfLine(LineNum, ProcKind)
If ProcName = sProcName Then
checkProcName = True
Exit Do
End If
Debug.Print ProcName
LineNum = .ProcStartLine(ProcName, ProcKind) + .ProcCountLines(ProcName, ProcKind) + 1
Loop
End With
End Function
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
