User defined function referencing data on another sheet in the same workbook

Question

I am using Office 2010.  How do I get a user defined function to reference an array of data within the same wookbook but on another sheet in order for it to do its calculations?  The user defined function routine currently only works if the data is on the same sheet as the function call.  I am passing along three variables through the function call.  The data required to complete the calculations is on the separate data sheet.  I would like to know the syntax required to get the user defined function to take the three variables from the current sheet and move to the data sheet to do the analysis and return the result back to the current sheet. (I have been searching through the literature but have not yet found what I need or perhaps didn't recognize it when I did find it.)

Regards

Answer 1

You can't get a UDF to move sheets like you would in regular macro.

Sheets("Part Data Entry").select

lastrow= Range("CalcLastRow").End(xlUp).Row

would not work

but this (from a UDF I use) would

lastrow = Sheets("Part Data Entry").Range("CalcLastRow").End(xlUp).Row

You can reference data on other sheets, you just have to specify the sheets and do a little more work to reference the correct data.

The key is to specify the sheet and range or cells you want to use.

Hope this helps, if you still have some questions providing some specifics on how you need to use the data would help.

Answer 2

Thanks for the response.

The answer I received from the first responder is working and appears to fit my functionality the best.  What you are proposing would be useful for some function operations but would be more cumbersome in my case.

Regards

Answer 3

The user defined function routine currently only works if the data is on the same sheet as the function call.

Hi,

i think that you are wrong,

try this sample:

on sheet1 in range A1:A10

are 1,2,...10

and on sheet2, in cell A1 write:

=aaa1(Sheet1!A1:A10)

now,

in a regular module..

Function aaa1(rng As Range)

For Each r In rng

aaa1 = aaa1 + r.Value

Next

End Function

(returns 55 in cell A1)