Evaluate the Maximum Length amongst variable length strings in an one-dimensional Array using Excel VBA

I have a one-dimensional Array that contains variable length strings. I want to find the maximum length amongst these strings using a combination of the EVALUATE, MAX & LEN functions.

I know for a worksheet Range declared Rng, the following code works:

MX_LEN = EVALUATE("MAX(LEN(" & Rng.Address & "))")

But i am not able to achieve something like this with an one-dimensional array Arr.

MX_LEN = EVALUATE("MAX(LEN(" & Arr & "))")

EDIT:

I am getting Runtime error 13: Type Mismatch with the below code. This one is a 2-D array, of which i am only accessing the 1st dimension.

Observations:

The strings here are slightly long viz., approx 100-200 characters each.
I see, somehow the starting and ending double quotes are missing in the Join.

The code is:

MX = Application.Evaluate("MAX(LEN({""" & Join(Application.Transpose(Application.Index(Arr, 0, 1)), """, """) & """}))")

P.S: I don't want to loop the array as the array is very small.

Solution

Nice question. You need to Join the array into a string:

Sub Filtering()

Dim arr As Variant: arr = Array("Hello", "World", "Wide", "Web")

With Application
    Dim MX_LEN As Long: MX_LEN = .Evaluate("MAX(LEN({""" & Join(arr, """,""") & """}))")
End With

End Sub

To elaborate a bit more on why this works:

.Evaluate needs to be fed a string in the form of a valid formula. Therefor we need to think how we would write this out in an excel sheet. OP mentioned that MX_LEN = EVALUATE("MAX(LEN(" & Rng.Address & "))") worked. Very logical because on a worksheet that would read (lets assume rng to be A1:A5):

=MAX(LEN(A1:A5))

Now, because Evaluate will recognize that we meant to enter this as an array formula, it will return the correct value.

OP said he want's to feed to function an array. Therefor we can reverse the logic and think how a function on the sheet would look like:

=MAX(LEN({"Hello", "World", "Wide", "Web"}))

Now we just need to build that string using Join and not forget the curly paranthesis. To check if we build it up correctly one can check with Debug.Print "MAX(LEN({""" & Join(arr, """,""") & """}))"

If we want to mimic this, but instead for the first dimension of a two-dimensional array we can apply the following:

With Application
    MX_LEN = .Evaluate("MAX(LEN({""" & Join(.Transpose(.Index(arr, 0, 1)), """,""") & """}))")
End With

One catch, just as with a lot more occasions, your string cannot exceed more than 255 characters. Otherwise Evaluate will return an error (as per documentation)

That means .Evaluate cannot be used on large strings, making a loop over the array the most effective go-to strategy.

However, if you really don't want to loop you can populate a cell with a formula instead, and read the cell's resulting value2:

With Application
    Sheet1.Range("A1").Formula = "=MAX(LEN({""" & Join(.Transpose(.Index(arr, 0, 1)), """,""") & """}))"
End With

Now you can read this through:

MX_LEN = Sheet1.Range("A1").Value2