1

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.

sifar
  • 1,086
  • 1
  • 17
  • 43
  • 1
    Surely if the array is very small, looping would be extremely fast. – Rory Nov 20 '19 at 11:57
  • Do you mean the array is very big? – SJR Nov 20 '19 at 11:58
  • Check my answer. The total string to be evaluated cannot exceed more than 255 characters. Possibly making this not the way you want to approach this issue =) – JvdV Nov 20 '19 at 13:14

1 Answers1

3

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
JvdV
  • 70,606
  • 8
  • 39
  • 70
  • Wondering why that works. Doesn't Join just produce a string? – SJR Nov 20 '19 at 11:59
  • @SJR I wsa just about to ask that :) – Zac Nov 20 '19 at 12:00
  • It indeed does so @SJR, but that's why I included the parenthesis in the full string to be evaluated. Check `Debug.Print "MAX(LEN({""" & Join(arr, """,""") & """}))"` also. – JvdV Nov 20 '19 at 12:01
  • 1
    @Zac, it may be confusing, but the `Evaluate` needs to be "fed" a string to evaluate. Therefor this works. Note: `MX_LEN = EVALUATE("MAX(LEN(" & Rng.Address & "))")` worked for OP because `rng.Address` returned a string to be concatenated within the `Evaluate`. – JvdV Nov 20 '19 at 12:02
  • Ah yes, in fact I had missed the double quotes round the commas which presumably turns it into 4 strings. And the curly brackets tell it to evaluate each one separately? – SJR Nov 20 '19 at 12:04
  • It maybe my lack of knowledge but seems a very clever way of doing things! – Zac Nov 20 '19 at 12:04
  • 1
    @SJR, if you find this stuff interesting, you could also be interested in [this](https://stackoverflow.com/questions/58505728/create-one-dimensional-array-from-evaluate) and [this](https://stackoverflow.com/q/58524525/9758194). In fact, you have replied over there :) – JvdV Nov 20 '19 at 12:17
  • @Zac, if you find this stuff interesting, you could also be interested in [this](https://stackoverflow.com/questions/58505728/create-one-dimensional-array-from-evaluate) and [this](https://stackoverflow.com/q/58524525/9758194) – JvdV Nov 20 '19 at 12:17
  • Very interesting reading. I do particularly like the use of `CHOOSE` – Zac Nov 20 '19 at 12:27
  • @sifar, no issues on my end. I have tried it. Can you include example data in your question? And a result of `Debug.Print "MAX(LEN({""" & Join(Application.Transpose(Application.Index(Arr, 0, 1)), """, """) & """}))"` – JvdV Nov 20 '19 at 12:41
  • @JvdV, you are right. i think it would because some of the text is >255 characters. Guess, there is no other way than to use a loop, right? – sifar Nov 20 '19 at 13:35
  • @Sifar, well....do you know the amount of items that would be in your array beforehand? – JvdV Nov 20 '19 at 13:35
  • it would be max 5 to 20 items. not more. – sifar Nov 20 '19 at 13:36
  • 1
    Without a fixed size, I guess a loop will have to be your go to. Otherwise there would be another way. Now looping is your best bet (which as @Rory mentioned should be extremely fast anyway) – JvdV Nov 20 '19 at 13:37
  • I checked, the max size was 242. Will that make the Evaluate function not work? Anways, i have alternatively used a Loop now. – sifar Nov 20 '19 at 13:53
  • 1
    @Sifar, that would indeed be the culprit. The whole string: `=MAX(LEN({"Hello", "World", "Wide", "Web"}))` must not exceed 255 characters. I'm still trying to find another way, but I guess a loop is your best bet =) – JvdV Nov 20 '19 at 13:54
  • Thanks @JvdV, your advice is valuable for me. – sifar Nov 20 '19 at 14:03
  • @Sifar, there is another way, which I would not recommend, but I will update the answer with it. – JvdV Nov 20 '19 at 14:15