Putting an Excel UDF into the Worksheet object (as opposed to in a module)

Question

I suspect the answer is "not possible" - but there is no harm in asking this fine community!

I have a Excel 2013 UDF (User Defined Function) that is unique to a certain worksheet.

Currently, I have this UDF in a "Module" in the containing Workbook. But what I would like to do, is to place the UDF code in the "Worksheet Object" so it is only available to that particular Worksheet.

I have tried, of course, but it is not visible, so I naturally get a #NAME? error. I've tried prefacing the Function with Public (which would defeat the object anyway), but to no avail.

Does anyone know of a particular trick?

Thanks in advance, Nic.

Answer 1

Depending on the requirements of your function, you could have a clause that only returns the correct answer on the intended worksheet.

Let's say your function takes a range as an input, i.e.

Function CheckSheet(r As Range)
    If r.Parent.Name = "Intended Sheet Name" Then
        CheckSheet = "The correct value!"
    Else
        CheckSheet = CVErr(xlErrNA)
    EndIf
End Function

EDIT:

the previous version can still be used on another sheet, if the Range it takes as a parameter is on the intended sheet. It can be modified to:

Function CheckSheet()
    If Application.Caller.Parent.Name = "Intended Sheet Name" Then
        CheckSheet = "The correct value!"
    Else
        CheckSheet = CVErr(xlErrNA)
    EndIf
End Function

Where Application.Caller returns the cell containing the function.