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.

Thank you... I suspected as much after I tried. If you'd put this as an answer I could reward you for the answer! But thanks again anyway. — Nic, Sep 19 '14 at 12:17

score 2 · Answer 1 · answered Sep 19 '14 at 12:30

2

It's not possible. UDFs have to be in a normal module. :)

answered Sep 19 '14 at 12:30

Rory

32,730
5
32
35

score 2 · Accepted Answer · answered Sep 19 '14 at 13:20

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.

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

2 Answers2

Linked

Related