Hi Ted, Instead of "Excel.WorksheetFunction.Len" just use "Len". So your code would look like: iLen = Len(ary(iLoc))
Len function is available as VBA function too, so you don't need to use excel function. This is same as you are doing for conditional statement, IF. Note: The moment you press . (period or dot) all the functions, objects and properties appear alongwith. So, when you'll check, you won't find any LEN method under WorksheetFunction. ________________________________________ Thanks & Regards Ashish Jain McKinsey India Knowledge Center (Microsoft Certified Application Specialist) (Microsoft Certified Professional) http://www.excelitems.com http://www.openexcel.com ________________________________________ On Nov 8, 11:46 pm, Ted <suicid...@gmail.com> wrote: > In an attempt to develop my own sort for an array, I seem to have run > into a problem. I don't know what I 'm doing wrong specifically, but > as soon as I attempt to invoke the Len Function, the whole thing > breaks. subscript out of range error. I think I may be overthinking > this, so any guidance is much appreciated. > > Public Function arraySortByStringLength(ary As Variant) > Dim iLoc As Long, iLen As Integer, iAry As Variant, i As Long > Dim val As String, indx As Long > iLen = 9999 > ReDim iAry(UBound(ary)) > i = 0 > indx = 0 > Do > For iLoc = 0 To UBound(ary) > If Len(ary(iLoc)) < iLen Then > iLen = Excel.WorksheetFunction.Len(ary(iLoc)) > indx = iLoc > val = ary(iLoc) > End If > Next iLoc > iAry(i) = val > ReDim Preserve ary(UBound(ary) - 1) > ary = arraySlice(ary, indx) > i = i + 1 > Loop Until UBound(ary) < 1 > arraySortByStringLength = iAry > End Function -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
