VBA中Evaluate函数长度限制问题及可行解决办法咨询

背景 #

我之前针对需基于1、2或3个不同列值过滤数组的场景，编写了多个函数，但很快遇到了Evaluate函数的字符长度限制。

当时使用的代码如下：

filterArr = Evaluate(Worksheetfunction.Concat("=let(x, Iferror((", col1.address(,,,true), "=", filterVal1, ")*(", col2.address(,,,true), "=", filterVal2, "),false), if(x=1, true, if(x=true, true, false)))"))

尝试的改进思路 #

为了更灵活地组合过滤条件，我尝试用已生成的filterArr来迭代拼接过滤规则，写了如下测试代码：

Private Sub test2()

Dim filterArr() As Variant
Dim col1 As Range
Dim col2 As Range
Dim filterVal1 As Variant
Dim filterVal2 As Variant

Dim test As Variant

Set col1 = Sheets("listSheet").ListObjects("listTable").ListColumns("listCol").DataBodyRange
Set col2 = Sheets("listSheet").ListObjects("listTable").ListColumns("listCol2").DataBodyRange

filterVal1 = "test"
filterVal2 = "test2"

filterArr = Evaluate(WorksheetFunction.Concat("=let(x,iferror((", col1.Address(, , , True), "=""", filterVal1, """),false),if(x=1,true,if(x=true,true,false)))"))
filterArr = Evaluate(WorksheetFunction.Concat("=let(x,iferror((", col2.Address(, , , True), "=""", filterVal2, """)*(", filterArr, "),false),if(x=1,true,if(x=true,true,false)))"))
End Sub

当前遇到的问题 #

这种方法在短数据列表里能正常工作，但我的工作表有5万多行数据，且数据量还会持续增长。问题出在Concat函数会把之前生成的filterArr转换成完整的数值列表，导致生成的公式字符串异常庞大，完全无法适配大数据量的场景。

内容的提问来源于stack exchange，提问作者ImKnownAsG