将Excel UDF放入工作表对象(而不是模块中) [英] 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!

我有一个Excel 2013 UDF(用户定义函数)，它对于某些工作表是唯一的.

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

当前，我在包含工作簿的模块"中拥有此UDF.但是我想做的是将UDF代码放在工作表对象"中，以便该特定工作表仅 可用.

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.

我当然尝试过，但是它不可见，所以我自然会得到一个#NAME吗?错误.我曾尝试使用Public作为Function的前缀(无论如何都会破坏对象)，但无济于事.

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?

提前，Nic.

推荐答案

根据函数的要求，您可能有一个子句，只能在预期的工作表上返回正确的答案.

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

以前的版本仍然可以在另一张纸上使用，如果它作为参数使用的范围在预期的纸上.可以将其修改为:

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

Application.Caller返回包含函数的单元格.

Where Application.Caller returns the cell containing the function.

这篇关于将Excel UDF放入工作表对象(而不是模块中)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！