Question

Dan Elgaard on Tue, 30 May 2017 12:44:44


Is it possible to test, from VBA, if a cell contains one or more User-Defined Functions?

Maybe a User-Defined Function for testing it?  :-)


Sponsored



Replies

Deepak Saradkumar Panchal on Wed, 31 May 2017 01:22:09


Hi Pistolprinsen,

Excel object model only provide 2 function to know that whether cell or range contains function/ formula or not.

  1. Range.HasFormula Property (Excel)
  2. WorksheetFunction.IsFormula Method (Excel)

Excel object model does not provide any method or property to check whether cell contains more then one user defined functions or not.

so you can check easily if you want to just check that cell contains UDF or not.

for checking more then one UDF you can try to refer example below.

I create below 2 UDF using VBA.

Function Demo()
MsgBox ("HI...")
End Function

Function Demo1()
MsgBox ("Hello...")
End Function

you can see I use that in sheet.

now I try to use code below to check whether it contains these functions or not.

I try to print the address of range which contains these exact functions.

Sub CheckUDF()
Dim rng1 As Range

Set rng1 = Cells.Find("=Demo1(),Demo()", , xlFormulas, xlPart)
Do While Not rng1 Is Nothing
rng1.Value = rng1.Value
Debug.Print (rng1.Address)
Set rng1 = Cells.Find("=Demo1(),Demo()", , xlFormulas, xlPart)
Loop


End Sub

Output:

Note that if you change the pattern of function then it will not find that range.

this example is just for a demo purpose. you need to change the logic and use your own logic as per your requirement.

Regards

Deepak

Deepak Saradkumar Panchal on Wed, 28 Jun 2017 09:13:54


Hi Pistolprinsen,

I find that , after creating this thread, you did not responded to this thread.

is your issue is solved?

if your issue is solved then post the solution here and mark it as an answer.

so that we can close this thread.

if your issue is still exist , then I suggest you to check the suggestion given by me. it may solve your issue.

if you have any further question then let us know about that.

I will try to provide further suggestion to you to solve the issue.

Regards

Deepak

Dan Elgaard on Mon, 02 Oct 2017 10:34:00


Solved the problem myself, by making af list of all built-in functions, and then comparing all functions used in a cell formula, with the functions in the list - any function used, notr on the list must be a UDF - not pretty, but it works :-)