Getting UDF to run against the sheet it's been entered on...

[Guest]

I've got a problem with a UDF that I've written where it's running against
the active sheet, rather than the sheet that it's been entered on.

Is there anyway to limit the function so that it runs against the sheet it
was entered on rather than running against the active sheet?

I've tried looking around for this and have discovered the following which
don't seem to work...unless I'm using them wrong

Application.Caller
Application.ThisCell.Worksheet

Any ideas?

Thanks

 

[JW]

Can you post up your UDF code?

 

[Guest]

JW - sure I can, probably should have done that in the original post!

**************************
Function countTesters(testerGroup As String, presentIndicator As Boolean) As
String
Application.Volatile
testerCount = 0
' Look for this particular group in the first column of the allocation

startRow = Range("A:A").Find(testerGroup).Row + 1

currentRow = startRow
Do
If Cells(currentRow, TESTER_COL).Value = "" Then
endRow = currentRow - 1
Exit Do
End If
currentRow = currentRow + 1
Loop

presenceCheck = "YES"
If Not presentIndicator Then presenceCheck = "NO"

' we have the rows we need to calculate
For currentRow = startRow To endRow
If Cells(currentRow, PRESENT_COL).Value = presenceCheck And Not
Cells(currentRow, EXCLUDE_COL).Value = "Exclude" Then
testerCount = testerCount + 1
End If
Next

countTesters = "" & testerCount ' for string return
End Function
******************************

That's essentially it...what's happening is that when I change the contents
of a cell on a different worksheet this UDF is being run against *that* sheet
rather than the sheet with the cell with the formula in...

 

[Mark Lincoln]

What happens if you preface your range with the sheet name in
question? For instance, if it's Sheet1:

startRow = Range("Sheet1!A:A").Find(testerGroup).Row + 1

Mark Lincoln

 

[Guest]

Mark - I would imagine that it would work although I haven't actually tested
this

One of the reasons for this (and again I should probably have been more
specific with my original query) is that this UDF gets used across different
sheets in the workbook (in the same place on the sheet though) and as such it
needs to detect which sheet it's been entered onto (or is current "running"
on) so that it can work out the necessary details.

Does that explain it all?

 

[Peter T]

George, these lines -

startRow = Range("A:A").Find(testerGroup).Row + 1 : Range?
If Cells(currentRow, TESTER_COL).Value = "" Then : Cells ?
If Cells(currentRow, PRESENT_COL).Value = presenceCheck And Not : Cells ?
Cells(currentRow, EXCLUDE_COL).Value = "Exclude" Then : Cells ?

In the above Range & Cells will refer to the active sheet. If they should
refer to the sheet that the UDF is called from -

Dim ws as worksheet
Set ws = Application.Caller.Parent

and qualify all objects that implicitly refer to the activesheet with 'ws.',
eg
startRow = ws.Range("
If ws.Cells(

It's probably worth including an error handler in case the Find function
doesn't find a cell (note Find in a UDF doesn't work in xl2000).

Regards,
Peter T

 

[Randy Harmelink]

So close -- try something along these lines:

Function ShowIt()
Application.Volatile
ShowIt = Sheets(Application.Caller.Worksheet.Name).Range("A1")
End Function

 

[Guest]

Peter - spot on. Thanks very much for this. Have reduced the number of lines
in the routine now (there's no need to go through the loop twice) and
included the Application.Caller.Parent and it's all working fine!

Great help!
George

 

[Guest]

For reference the function now looks like this...just incase anyone else
reads this thread and wants to know

***********
Option Explicit
Const TESTER_COL = 1
Const PRESENT_COL = 7
Const EXCLUDE_COL = 11
Dim ws As Worksheet
Dim currentRow As Integer

Function countTesters(testerGroup As String, presenceCheck As String) As
String
Application.Volatile

countTesters = 0
' Look for this particular group in the first column of the allocation
Set ws = Application.Caller.Parent
currentRow = ws.Range("A:A").Find(testerGroup).Row + 1

Do
If ws.Cells(currentRow, TESTER_COL).Value = "" Then
Exit Do
Else
If ws.Cells(currentRow, PRESENT_COL).Value = presenceCheck And
Not ws.Cells(currentRow, EXCLUDE_COL).Value = "Exclude" Then
countTesters = countTesters + 1
End If
End If
currentRow = currentRow + 1
Loop
End Function
*******************