Function and needing to Refresh

[Mike]

Hi. I've written the following simple function that
appears to work well. However, at various times I will
receive the #VALUE error and will need to hit F9
(recalculate) to get the correct answers. This happens
repeatedly if I open another workbook, change something,
and come back.

My code is:

Option Explicit

Function InviteBack(myType, myMonth)
Application.Volatile True
Dim myRange As range
Dim j As Integer
Set myMonth = Worksheets(myMonth)
Dim myCell As range
j = 0
Set myRange = myMonth.range("n4:n39")
For Each myRange In myRange.Cells
If myRange.Formula = myType Then
If UCase((myRange.Cells.Offset(0, 4).Formula))
= "Y" Then
j = j + 1
End If
End If
Next
InviteBack = j
End Function

Thanks,
Mike.

 

[J.E. McGimpsey]

Without testing it, I suspect that the problem is the qualification
of your objects.

Worksheets(myMonth)

is, by default, evaluated for the ActiveWorkbook. If a calculation
occurs with another workbook active, the subscript out of range
error will generate a #VALUE! error.

If the function is in the same workbook as the calling cell,
qualifying Worksheets(myMonth) with ThisWorkbook:

ThisWorkbook.Worksheets(myMonth)

should prevent that error. If it's not in the same workbook, try

Application.Caller.Parent.Parent.Worksheets(myMonth)

 

[Mike]

Thanks!

ThisWorkbook.Worksheets(myMonth) solved the problem.

Mike.