UDF working when called from Sub, doesn't work as worksheet function

[stevenf]

hi

I have written a user-defined function that searches for a small range within a larger range. The function requires two input parameters: the range you are looking for, and the range you want to look within.

For example, I might look for the string of values in cells A1:D1 in a larger range E1:H20. The function returns the row number in the larger range where the smaller range is found.

My problem is this: The function is working fine when I call it from another sub procedure. However, when I try to run it as a worksheet function, I get a "#VALUE!" error. The function pops up in the "insert function" menu, and it prompts for the two input parameters. I just does not appear to run properly, and I can't figure out why, so any help on this would be great.

Here's the function. See below for an example sub procedure I used to call the function and get it to run (by hard-coding in the ranges to search for/search in).

Please Login or Register  to view this content.

Sub procedure for checking the function functionality:

Please Login or Register  to view this content.

[Mallycat]

can you post a sample sheet

[shg]

This works fine for me both in VBA and as a worksheet function:

Please Login or Register  to view this content.

[stevenf]

wow - tremendous: yes, that code works fine for me too! Many thanks for your help (and for smartening up the code...!).

However, I am still curious why my original code didn't work as a worksheet function. I'd like to figure this out, so I can avoid getting into this problem in the future. Could it have something to do with the how the input parameter variable types are defined?

In response to Mallycat, I have attached an example spreadsheet in which the problem is illustrated.

Also, a related question I came upon when trying to resolve this question myself: is there a way to step into the execution of a user-defined function when it is run as a worksheet function? I tried setting breakpoints etc, but it doesn't seem to work...?

[shg]

I am still curious why my original code didn't work as a worksheet function.

I'm going to beg off answering that if I may; I don't see anything at a glance, and don't feel motivated to debug.

is there a way to step into the execution of a user-defined function when it is run as a worksheet function

Just set a breakpoint in the usual way.

If that sorts it, would you please mark the thread as Solved?

Click the Edit button on your first post in the thread

Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes

[Leith Ross]

Hello stevenf,

Here is a link to help you better understand what you can and can not do with a UDF and why.

http://support.microsoft.com/kb/170787

[shg]

A useful link in general, Leith, though not relevant to the OP's function.