因此，我经常发现自己在Excel上执行任务，涉及将文本字符串作为数组进行评估。一般来说，我只是使用它：
Function EVAL(Ref As String)
EVAL = Evaluate(Ref)
End Function
例如：例如：
=EVAL("{"&CHAR(34)&SUBSTITUTE(TEXTJOIN(";",TRUE,MID(Index[Industries],2,LEN(Index[Industries])-2)),";",CHAR(34)&";"&CHAR(34))&CHAR(34)&"}")
此示例中的单元格将具有以下内容：（
;Automotive;Rail;Energy;
;Automotive;Rail;
;Energy;
;Automotive;Aerospace;
碰巧的是，这是我现在遇到的确切问题过去的方式。）
过去这对我有用，但是最近我遇到了困难。
我得出的结论是不起作用的，因为application.evaluate，事实证明，字符限制为255。 - 当我使用它将文本字符串解释为数组而不是公式时，他们似乎不起作用的人似乎不起作用。
是否有一些技巧可以使它起作用？ （或者，实际上，是否有一些完全实现这一目标的替代方法？）
So I quite often find myself doing tasks on Excel which involve evaluating a text string as an array. Generally speaking I just use this:
Function EVAL(Ref As String)
EVAL = Evaluate(Ref)
End Function
So the formula will be, for example:
=EVAL("{"&CHAR(34)&SUBSTITUTE(TEXTJOIN(";",TRUE,MID(Index[Industries],2,LEN(Index[Industries])-2)),";",CHAR(34)&";"&CHAR(34))&CHAR(34)&"}")
The cells in this example will have contents like:
;Automotive;Rail;Energy;
;Automotive;Rail;
;Energy;
;Automotive;Aerospace;
(As it happens this is the precise problem I'm stuck on right now, though it has come up in different ways in the past.)
This has worked for me in the past, but I've been running into difficulties lately.
I have come to the conclusion it isn't working because application.evaluate, it turns out, has a character limit of 255. I've seen examples of VBA tricks to bypass this for text strings that are formulas rather than arrays, but copy-pasting those they don't seem to work for when I'm using it to interpret a text string as an array rather than as a formula.
Is there some trick to get this to work? (Or, indeed, is there some alternative method to achieve this altogether?)
发布评论
评论（1）
是的，根据我的评论，如果您使用的是MS365，则可以避免使用工作簿为XLSM，只是因为您需要将值拆分为数组。利用本机函数可用的内容，例如：
公式在
c2中：d2中的公式：注释1：< /strong>根据写作时间，您需要启用beta-channel才能访问
textsplit（），如果我没记错的话，允许您的版本（2203）开始使用此功能。只需 google 如何获取访问并更新excel。显然，这两种选项都可以嵌套在 unique（） 功能。
注2：如果在任何时候
concat（）达到了限制（32767个字符，谢谢@scottcraner），也许您可以避免在Lambda的Helper helper函数的帮助下避免使用它=“ https://support.microsoft.com/en-us/office/reduce-function-42E39910-B345-45F3-84B8-0642B568B7CB” /a>：注释3 ：如果您还不能更新excel，并且您想知道如何使用
filts> filterxml（）写一会儿在这里。Right, as per my comments, if you are using ms365, you could avoid your workbook to be xlsm just because you need to split values into an array. Make use of what is available with native functions, for example:
Formula in
C2:Formula in
D2:Note 1: As per time of writing you'd need to enable the BETA-channel to gain access to
TEXTSPLIT(), and if I recall correctly your version (2203) is allowed to start using this function. Just google how to get access and update your Excel.Both options can obviously be nested inside the
UNIQUE()function.Note 2: If at any point
CONCAT()'s limits are reached (32767 characters, thanks @ScottCraner), maybe you can avoid using that with help of the lambda's helper functionREDUCE():Note 3: In case you can't update your Excel just yet, and you wonder how to use
FILTERXML(), don't mind me refering you to another post I wrote a while back here.