First of all... You would think that Application.WorksheetFunction would apply to ALL worksheet functions. But in reality, it does not. ONLY those WorksheetFunctions that were written in a manner accessible to VBA are available. It's something about the environment where they were developed.
That's a long way of saying that the Len() function is not available as a Worksheet function. But you don't need it as a worksheet function, because it is available as a VBA function! Simply use: iLen = Len(ary(iLoc)) which, is exactly what you did with: If Len(ary(iLoc)) < iLen Then so... Paul ----- Original Message ---- > From: Ted <suicid...@gmail.com> > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com> > Sent: Mon, November 8, 2010 1:46:07 PM > Subject: $$Excel-Macros$$ array sort issue > > 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 > -- ---------------------------------------------------------------------------------- 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
