更新时间:2022-11-12 20:28:02
我今天也刚刚遇到了这个问题.真的很不幸,这有这个限制.我认为我们希望做的***的事情就是以下事情.
I just came across this issue today also. Really unfortunate that this has this limitation. I think the best thing we can hope to do is the following.
Function MakeValid(ByVal sSearchTerm as string) as string
if len(sSearchTerm) > 255 then
MakeValid = Left(sSearchTerm,254) & "*"
else
MakeValid = sSearchTerm
end if
End Function
Sub Test()
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= MakeValid(sSearchTerm), Operator:=Excel.XlAutoFilterOperator.xlFilterValues
End Sub
最终,它的工作方式是通过使用模式搜索来绕过该问题(因此匹配前253个字符,然后从那里搜索任何模式).这并不总能奏效,事实上它在某些时候注定会失效,但这似乎是我们***的选择(除了围绕这个问题设计系统之外)
Ultimately, the way it works, is it bypasses the problem by using a pattern search (so matches the first 253 characters, and then it searches for any pattern from there). This won't always work, in fact it is bound to not work at some points, but it seems this is the best option we have (other than designing our systems around this issue)
类似的方法也适用于数组:
Seems like this also works for arrays:
Function MakeValid(ByVal sSearchTerm as string) as string
if len(sSearchTerm) > 255 then
MakeValid = Left(sSearchTerm,254) & "*"
else
MakeValid = sSearchTerm
end if
End Function
Sub Test()
Dim i as long
for i = lbound(sSearchTerms) to ubound(sSearchTerms)
sSearchTerms(i) = MakeValid(sSearchTerms(i))
next
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= sSearchTerms, Operator:=Excel.XlAutoFilterOperator.xlFilterValues
End Sub
这是一个不好的解决方案,但可以解决问题
It's kinda a bad solution but it sort of works