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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>
Similar Threads
