问题描述
我正在尝试编写一个要从单元格调用的 VBA 函数.在这个函数中,我需要使用 CurrentRegion 属性,但它返回了一个意外的结果.
I'm trying to write a VBA function to be called from a cell. In this function I need to use the CurrentRegion property but it returns an unexpected result.
功能:
Function GetVarRange() As String
Dim rngRangeToLeft As Range, wks As Worksheet
Set wks = Application.Caller.Worksheet
Set rngRangeToLeft = wks.Range("A1").CurrentRegion
GetVarRange = rngRangeToLeft.Address
End Function
我在单元格中得到的结果是 $A$1.
The result I get in the cell is $A$1.
在 A1 中有值,在它周围,CurrentRegion 实际上是 A1:AD618,这是我在代码编辑器中访问属性时(不在此函数中)并按 ctrl+A 时得到的结果选择 A1 时.
There are values in A1, and all around it, the CurrentRegion is actually A1:AD618 and this is the result I get when I access the property within the code editor (not in this function) and when I press ctrl+A when A1 is selected.
大家有什么想法吗?
推荐答案
CurrentRegion 在从工作表单元格调用的 UDF()'s 中无法正常工作.我还发现了 FindNext 和 SpecialCells 的问题.
CurrentRegion does not work correctly in UDF()'s called from worksheet cells. I have also found problems with FindNext and SpecialCells.
从 Sub 调用,这是一个不同的故事:
Called from a Sub, it is a different story:
Function GetVarRange(rng) As String
Dim rngRangeToLeft As Range, wks As Worksheet
Set rngRangeToLeft = rng.CurrentRegion
GetVarRange = rngRangeToLeft.Address
End Function
Sub MAIN()
MsgBox GetVarRange(Range("A1"))
End Sub
似乎工作得很好.
这篇关于在从单元格调用的函数中使用时,CurrentRegion 的结果不正确的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!