CODEWITHSUNDEEP
vbaexcelexcel-2013excel-udf
Nic
Nic

Reputation: 88

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

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.

Upvotes: 3

Views: 959

Answers (2)

citizenkong
citizenkong

Reputation: 679

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.

Upvotes: 2

Rory
Rory

Reputation: 34045

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

Upvotes: 2

Related Questions