What UDF's are being used?

[SkylineGTR]

Hi,

Is it possible to find all formula's in a workbook that are referencing
a User Defined Function? I have a spreadsheet with a couple of hundred
formulas, that reference a number of different UDF's, and I would like
to be able to find out what is referencing what ...

Any ideas?

Cheers

 

[Guest]

you could use the FIND method --- help is useful

Option Explicit
Sub FindTest()
Dim ws As Worksheet
Dim what As String
Dim firstAddress As String
Dim found
what = "MyFunction"
For Each ws In Worksheets
With ws
Set found = .UsedRange.Find(what, LookIn:=xlFormula)
If Not found Is Nothing Then
firstAddress = c.Address
Do
' do something with it
Set found = .FindNext(found)
Loop While found.Address <> firstAddress
End If
End With
Next

End Sub

the do something with bit I leave up to you. I'd add the cell reference,
with the sheet name to a ciollection,. After the FOR/NEXT loop finishes, add
a new sheet for the results, looping through the collection & putting the
data onto the sheet

 

[SkylineGTR]

Hi Patrick,

Thank you for your response. I sort of figured you could do that, but
what I really wanted (if it is possible) was to know if there was a way
to use some inbuilt Excel function to tell whether a formula contains a
UDF/or multiple UDF's, and if so which. Using the above method means
that if I add a new UDF, I have to specify the new function name. What
I want is a function that
effectively does:

ListAllUDFs

and gives me a list of formulas, and what UDF's they contain. Maybe
this is not possible, but I had the faintest hope that Excel would know
what its functions are, but also what UDFs are present in the formula -
maybe I am hoping too much.

Thank you once again.

 

[al007]

How should you define the variable c.
Pls give me the code & where to place it
Thxs