能不能列出一个工作簿中所有已使用的自定义函数

徒然客

能不能列出一个工作簿中所有已使用的自定义函数？

自定义函数和excel自带函数有什么本质的区别？

LangQueS

一起等待解答。

northwolves

Sub UDFSOFACTIVEWORKBOOK()
Dim sh As Worksheet, r As Range, dic As Object, i As Long, temp As String, vbcomp, s() As String, UDF As String
For i = 1 To ActiveWorkbook.VBProject.VBComponents.Count
Set vbcomp = ActiveWorkbook.VBProject.VBComponents(i)
If vbcomp.Type = 1 Then temp = temp & vbCrLf & vbcomp.CodeModule.Lines(1, 65536)
Next
s = Split(temp, vbCrLf)
temp = ""
For i = 0 To UBound(s)
If s(i) Like "Function * As *" Then temp = temp & "@" & "=" & Trim(Split(Split(s(i), "(")(0), "Function")(1)) & "(" '--->All functions with or without parameters
Next
Set dic = CreateObject("scripting.dictionary")
For Each sh In Sheets
For Each r In sh.UsedRange
If r.HasFormula Then
If InStr(temp, "@" & Split(r.Formula, "(")(0)) > 0 Then
UDF = r.Formula & "udf"
Else
UDF = ""
End If
If Not dic.exists(r.Formula) Then dic.Add r.Formula, UDF
End If
Next
Next
Debug.Print "All functions used in activesheet" & vbCrLf & String(50, "-") & vbCrLf & Join(dic.keys, vbCrLf) & vbCrLf & vbCrLf '列出一个工作簿中所有函数
Debug.Print "All user define functions used in activesheet" & vbCrLf & String(50, "-") & vbCrLf & Replace(Join(Filter(dic.items, "udf"), vbCrLf), "udf", "") '列出一个工作簿中所有已使用的自定义函数
Set dic = Nothing
End Sub

northwolves

 需要添加对VB项目的信任

hysj02

多谢狼版主解决了困扰了我很长时间的一个问题，就是有些电脑运行关于VBComponents的代码会报安全错误。
这里顺便说一下“需要添加对VB项目的信任”的方法。Excel -> 工具 -> 宏 -> 安全 -> 第二张选项卡 -> 选中添加对VB项目的信任，因为我用的是英文版Excel，有的表达可能有问题，请见谅。