PDA

View Full Version : Sleeper: Using a UDF in Multiple Worksheets



Icestone
05-18-2009, 12:45 AM
Hi guys, i've built a little UDF and it's working fine until i use it in another worksheet.

For example, i used it in Sheet1 i get all the correct results. However, when i tried to use it in Sheet2, it messed up the answers in Sheet1 because the results in Sheet1 are updated when i used the UDF in Sheet2.

Can somebody please tell me how to overcome this? Thanks in advance!

JONvdHeyden
05-18-2009, 12:56 AM
We will need to see the UDF and a brief explanation on what it's supposed to do.

Bob Phillips
05-18-2009, 01:02 AM
Sounds as if you are not defining which sheet it should use, defaulting to Activesheet.

Icestone
05-18-2009, 01:16 AM
i'm sorry for the lack of information. the file resides in my PC at the moment and i'm at work. i'll be able to paste the actual one up later after work. If i'm not mistaken, here is more or less how it should look like, though i think one line is missing:



Option Explicit

Public Function MSum(cRow as Integer, cCol as Integer, blocks as Integer)
MSum = Application.WorksheetFunction.Sum(Range(Cells(cRow + 1, cCol), Cells(cRow + blocks, cCol)))
End Function

basically, the UDF is supposed to sum up a specific number of cells below it, depending on the number of elements listed on the first column (captured by the blocks parameter).

i did suspect that it's a problem of not defining the worksheet properly but i'm not quite sure how to overcome it.

Bob Phillips
05-18-2009, 01:34 AM
One of the big problems with tha UDF is that it has no reference to cells on the sheet, therefore it will not automatically update if the data it is summing changes.

In other words, if that is it it is not a good UDF.