Custom Function being called when it shouldn't.

[Ron]

Hi guys,

I have a custom function that wasn't written by me [partial code below], it
finds the standard deviation from a list of numbers. It works fine and is
only used in one sheet in the workbook.

The problem I have is that it is being called when I run another piece of
code, but I'm not calling it.

Ive tried making it private in its own module but that doesn't fix it.

I've got a workaround at the moment in that I've put the lines in the
function

If ActiveSheet.Name = "Standard Dev" Then
Exit Function
End If

This seems to work, however if I step through the code then this function
is still called but steps through the If statement the same number of times
as theres data in the Standard Dev worksheet.

I've checked for the accidental use of the same variable in both bits of
code but there is none.

Am i missing something simple here?

Ron

Private Function WPAM(data As Range, DatEntry As Long)
Dim C() As Double, k1 As Variant, DatNum As Long, info As Long
Dim i As Long, j As Long, temp As Double, result As Double

If ActiveSheet.Name = "Standard Dev" Then '**
Exit Function '** My workaround
End If '**

DatNum = 0
For Each k1 In data
DatNum = DatNum + 1
Next k1

ReDim C(1 To DatNum, 1 To 2)

For j = 1 To DatNum
C(j, 1) = data(j, 1)
C(j, 2) = 20
Next
For i = 1 To 2
temp = C(DatEntry, i)
C(DatEntry, i) = C(1, i)
C(1, i) = temp
Next
result = 0
If C(1, 1) > 0 Then
For i = 0 To 300
temp = 1
For j = 2 To DatNum
temp = temp * Application.WorksheetFunction.NormDist(i, C(j, 1), C(j, 2),
True)
Next
temp = temp * Application.WorksheetFunction.NormDist(i, C(1, 1), C(1, 2),
False)
result = result + temp
Next
WPAM = result
Else
WPAM = 100000000
End If
End Function

 

[Ron]

Sorry chaps,

In both instances of the below the equals should be <> as I had changed it
to equals to debug.

This gist of it is, if the active sheet isn't the sheet with the function
in then it exits the function.

If ActiveSheet.Name = "Standard Dev" Then
Exit Function
End If

Ron

 

[Tom Ogilvy]

If any values that the formula is dependent on change, then it will
recalculate.

 

[Tom Ogilvy]

set rng = Application.Caller
if rng.Parent.Name <> Activesheet.Name then exit Function

However, this doesn't stop your function from being reevaluated, even if
only partially, - and would return a value of zero. Better would be to find
out why it is being recalculated when you think it shouldn't.

 

[Ron]

If any values that the formula is dependent on change, then it will
recalculate.

Hi Tom,

I'm running code in a seperate worksheet with no [that I can see]
references to the function at all, yet various pieces of small and simple
code are calling this function and trying to run it on the active cell.

Here's the simple code that's the second piece to [unwantedly] call this
function.

Sub ChangeZeros()
Range("a12").Select
Do Until Selection.Value = "end"
ActiveCell.Offset(1, 0).Select
If ActiveCell.Offset(0, 3).Value = 0 Then
ActiveCell.Offset(0, 3).Value = 1
End If
Loop
End Sub

When I get to the first cell that actually has a zero to be changed to 1 it
calls the function.

I'm at a loss as to why.

Ron

 

[Tom Ogilvy]

My guess would be that one of the cells that your function depends on has
some dependency on the cell that gets changed to 1.

--
Regards,
Tom Ogilvy

If any values that the formula is dependent on change, then it will
recalculate.

Hi Tom,

I'm running code in a seperate worksheet with no [that I can see]
references to the function at all, yet various pieces of small and simple
code are calling this function and trying to run it on the active cell.

Here's the simple code that's the second piece to [unwantedly] call this
function.

Sub ChangeZeros()
Range("a12").Select
Do Until Selection.Value = "end"
ActiveCell.Offset(1, 0).Select
If ActiveCell.Offset(0, 3).Value = 0 Then
ActiveCell.Offset(0, 3).Value = 1
End If
Loop
End Sub

When I get to the first cell that actually has a zero to be changed to 1 it
calls the function.

I'm at a loss as to why.

Ron

 

[Ron]

My guess would be that one of the cells that your function depends on has
some dependency on the cell that gets changed to 1.

Thanks Tom,

I'm tearing my hair out now

I'll create a new book with a new function and new code, then step through
it all 'till I hit a problem.

Ordinarily this wouldn't bother me but the function has a for/next loop of
300 which is used for every value in the data set. Sometimes 24 values in
the data set so this causes the function to take up a lot of time. If it
keeps being called when it shouldnt be it causes a huge delay in other
code.

Thanks again Tom.

Ron