Array in vba

[rgandy]

how do i get the following to function properly?

in excel, =max(len(range)) functions by turning the formula into an array. what is the syntax for doing this within excel?

i am trying to set test = to the outcome

test = {application.worksheetfunction.max(len(range))}

but it is not working. any help is appreciated. thanks!

 

[firefytr]

You can't do it that way as there isn't a native way to evaluate array [worksheet] functions. You can, however, set the formula to an actual cell and use the FormulaArray property to evaluate it as such, which would look like ...

Range("D16").FormulaArray = "=MAX(LEN(D2:D13))"

The other option, if you want to use pure VBA, is to set the array, loop through it's elements checking each one. If you set the values to an array it would be quite fast, even in large data sets.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[Zathras]

Darn that Zack! He's just too quick. I had this almost ready to post, but here it is anyway:

Sub test()
  MsgBox MaximumLength(Range("D4:D13"))
End Sub

Function MaximumLength(ARange As Range)
Dim c As Range
Dim nLength As Integer
Dim nMax As Integer
  nMax = 0
  For Each c In ARange
    nLength = Len(c.Text)
    If nLength > nMax Then nMax = nLength
  Next c
  MaximumLength = nMax
End Function