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?)
