将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屋!