本文介绍了循环虽然项目中的所有UDF名称的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
此问题:得到我想到了一个自动搜索电子表格中使用的所有UDF的过程。一些沿着以下行:
This question: Searching for function usage in Excel VBA got me thinking about a process for automating a search for all UDFs being used in a spreadsheet. Something along the lines of:
For Each UDF in Module1
If Cells.Find(What:=UDF.Name, After:="A1", LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False) Then
MsgBox UDF.Name & " is in use"
End If
Next UDF
这是可能的,如果是,循环遍历所有UDF的语法是什么?
Is this possible and if so, what would the syntax be for looping through all UDFs?
推荐答案
Option Explicit
' Add reference to Microsoft Visual Basic for Applications Extensibility 5.3 Library
Public Sub FindFunctionUsage()
Dim udfs
udfs = ListProcedures("Module1")
If Not IsArray(udfs) Then _
Exit Sub
Dim udf
Dim findResult
For Each udf In udfs
Set findResult = Cells.Find(What:="=" & udf, After:=Cells(1), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If Not findResult Is Nothing Then _
MsgBox udf & " is in use"
Next udf
End Sub
' Source for ListProcedures : http://www.cpearson.com/excel/vbe.aspx
Private Function ListProcedures(moduleName As String)
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long
Dim NumLines As Long
Dim WS As Worksheet
Dim rng As Range
Dim ProcName As String
Dim ProcKind As VBIDE.vbext_ProcKind
Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents(moduleName)
Set CodeMod = VBComp.CodeModule
Dim result
With CodeMod
LineNum = .CountOfDeclarationLines + 1
Do Until LineNum >= .CountOfLines
ProcName = .ProcOfLine(LineNum, ProcKind)
If ProcKindString(ProcKind) = "Sub Or Function" Then
If IsArray(result) Then
ReDim Preserve result(LBound(result) To UBound(result) + 1)
Else
ReDim result(0 To 0)
End If
result(UBound(result)) = ProcName
End If
LineNum = .ProcStartLine(ProcName, ProcKind) + _
.ProcCountLines(ProcName, ProcKind) + 1
Loop
End With
ListProcedures = result
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
' Content of Module1
Public Sub Sub1()
End Sub
Public Function Func1(ByRef x As Range)
End Function
Public Sub Sub2()
End Sub
这篇关于循环虽然项目中的所有UDF名称的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!