Custom function acting weird

[Riddler]

I have a custom function (see below) that is used on several cells on
sheet 1. It all works fine except for when I make a copy of the sheet
into the same workbook and it calculates with some new numbers. What
happens is that sheet 1 and sheet 2 now show the same results. When I
recalc on the sheet that is wrong it messes up the other one. It seems
that when I watch the myRange.address as it recalculates all the
occurences of this function on sheet 1 & 2 it uses the same cell range
(from the active sheet) for all and this is why the other sheets do not
get their data calculated correctly.
So how do I make this custom function use the range from the sheet that
contains each of the cells that use this function?

Thanks for any help or direction you can give me.

Scott

Function LowestRepeatableNumber(myRange As Range)
Application.Volatile
'This function returns the second lowest number above zero if there are
no repeating numbers
LowestRepeatableNumber = Evaluate("MIN(IF(" & myRange.Address &
">0," & "IF(COUNTIF(" & myRange.Address & "," & myRange.Address &
")>1," & _
myRange.Address & ")))")
If LowestRepeatableNumber = 0 Then LowestRepeatableNumber =
Evaluate("small(IF(" & myRange.Address & ">0," & "IF(COUNTIF(" &
myRange.Address & "," & myRange.Address & ")=1," & _

myRange.Address & ")),2)")


End Function

 

[Charles Williams]

The Application.Evaluate method (which is the default for Evaluate) always
assumes that any unqualified range reference refers to the active sheet.
Therefore its much safer to use the Worksheet.evaluate method:

Application.Caller.Parent.Evaluate( ....)

this will use the parent of the cell containing the custom function (ie the
worksheet containing the custom function) as the worksheet for any
unqualified range reference.

(note there are some other "quirks" of Evaluate that are worth knowing if
you make much use of it: see
http://www.decisionmodels.com/calcsecretsh.htm for details)

hth
Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

 

[Riddler]

Application.Caller.Parent.Evaluate( ....) Worked great!

Thanks a bunch

Scott