我正在为工作编写一些VBA函数,遇到了一个本应很容易解决的问题,但不知何故,尽管我尽了最大努力在这里和谷歌上找到了答案,但我还是没能解决。我写了一个函数,它应该给我一列中两个字符串之间的范围:
Function FindRng(StartRng As String, EndRng As String) As Variant
Dim TopOfRange As Single
Dim BottomOfRange As Single
TopOfRange = WorksheetFunction.Match(StartRng, Sheets("InfCom").Range("B:B"), 0)
BottomOfRange = WorksheetFunction.Match(EndRng, Sheets("InfCom").Range("B:B"), 0)
FindRng = Range(Sheets("InfCom").Cells(TopOfRange, 2), Sheets("InfCom").Cells(BottomOfRange, 2))
End Function
因此,如果输入A和B在第100行和第105行,它应该返回B100:B105。当我通过调整代码以读取FindRng=Range(…).Address来测试这一点时,我确实得到了$B$100:$B$105。
然而,当我将FindRng的结果输入到自定义的Index Match函数中时,我得到了一个错误。功能如下:
Function subsetPBPC(rngReturn As Range, LookupValueH As Variant, TopOfRange As String, BottomOfRange As String, LookupValueV As Variant) As Variant
subsetPBPC = sPBPC(rngReturn, LookupValueH, FindRng(TopOfRange, BottomOfRange), LookupValueV)
End Function
问题是,它似乎不是将FindRng的输出读取为范围,而是将其读取为该范围的内容:当我在嵌入另一个公式的FindRng上使用Evaluate Formula工具时,它将FindRn的输出显示为{a,B,C,D,E},而不是$B$100:$B$105,其中a到E是该范围中单元格的内容。我觉得这个解决方案真的很简单,但我看不出来。自定义Index Match函数的底层功能已经过测试,所有功能都很有魅力。
Set而不是let。让将表达式的值赋给变量。集合为变量指定对象引用。您希望返回对范围对象的引用,而不是返回由范围对象的默认属性产生的值。
在VBA中编写
FindRng = Range(...)
正在隐式写入
Let FindRng = Range(...)
不管你想要
Set FindRng = Range(...)
编辑1:
理解VBA中对象引用和值之间的区别非常重要。这是一个类似于通过值或引用传递参数的概念。希望这两个链接能帮助一些人:
MSDN上的Let语句
MSDN 上的Set语句
编辑2:
哦,我想我应该谈谈默认属性!某些对象(如range)具有默认属性。如果将范围视为一个值而不是一个对象,则它将使用默认属性,而不是抛出错误,因为它是一个对象而不是值。在范围的情况下,默认属性为Value
。所以如果你说A = Range("A1")
,你实际上说的是Let A = Range("A1").Value
,而你的意思可能是Set A = Range("A1")
。因此,您得到的是单元格A1中包含的值,而不是表示该单元格的范围对象。
发现您当前的代码应该是
- 根据AndADM的通信网使用
Set
- 将
SetRng
标注为范围,而不是变体
你可以简化你的函数如下(如果你重复调用它,这可能会节省时间)
此外,您可以测试这个范围为Nothing
(如果找不到两个字符串),而如果缺少任何一个字符串,则当前代码将出错。
Function SetRng(str1 As String, str2 As String) As Range
With Sheets("infCom").Columns(2)
Set SetRng = Range(.Find(str1, , xlValues, xlWhole), .Find(str2, , xlValues, xlWhole))
End With
End Function