在从单元格调用的函数中使用CurrentRegion时，结果不正确 [英] Incorrect result from CurrentRegion when used in a function called from a cell

问题描述

我正在尝试编写要从单元格调用的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()的中无法正常工作.我还发现 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时，结果不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！