Out Of Stack Problem

[santhescript01]

Hi all,

I am creating a macro that performs sorting. I am using quicksort algorithm. It's working fine for data below 5000 but as soon as data exceeds beyond it, it shows Run-time error - 28 out of stack.
Is there any way to avoid too many recursions?
Please help me out.

Below is sample code::
[CODE=vb]Sub RecursiveSort(B yVal llow As Long, ByVal lHigh As Long)
Dim lStart As Long
Dim lEnd As Long
Dim vTemp As Variant
Dim vPivot As Variant
'Set new extremes to old extremes

lStart = lHigh
lEnd = llow
vPivot = a_vRowElements( (lStart + lEnd) \ 2)
'Till the count is less or equal to the max limit
Do While lEnd <= lStart

If bChkFlag = True Then
' While a_vRowElements( lEnd) < vPivot
While Compare(a_vRowE lements(lEnd), vPivot)
lEnd = lEnd + 1
Wend
'While a_vRowElements( lStart) > vPivot
While Compare(vPivot, a_vRowElements( lStart))
lStart = lStart - 1
Wend
Else
'While a_vRowElements( lEnd) > vPivot
While Compare(vPivot, a_vRowElements( lEnd))
lEnd = lEnd + 1
Wend
'While a_vRowElements( lStart) < vPivot
While Compare(a_vRowE lements(lStart) , vPivot)
lStart = lStart - 1
Wend

End If
'
If lStart >= lEnd Then
If lStart <> lEnd Then
vTemp = a_vRowElements( lEnd)
a_vRowElements( lEnd) = a_vRowElements( lStart)
a_vRowElements( lStart) = vTemp
End If
lStart = lStart - 1
lEnd = lEnd + 1
End If
Loop
If llow <= lStart Then
RecursiveSort llow, lStart
End If
If lEnd < lHigh Then
RecursiveSort lEnd, lHigh
End If
End Sub[/CODE]

[QVeen72]

Hi,

I always avoid recursions (unless there is no way out).

During such sorting issues, I prefer to create a temp table in an Access DB, save all the data in the temp table, and get data back using "Order By Column Name" and fill the data to new sheet/grid or whatever.

Regards,
Veena.

[santhescript01]

Hi,

I always avoid Reccurrsions (Unless, until there is no way out....)

During Such Sorting Issues, I prefer to Create a Temp Table in an Access DB, Save all the Data in the Temp Table, and Get data back using
"Order By Column Name" and Fill the data to New Sheet/Grid or whatever..

Regards
Veena

Thanks for reply but i m creating macro for excel. Is there exist any another way to avoid too many recursion?

[Killer42]

Thanks for reply but i m creating macro for excel. Is there exist any another way to avoid too many recursion?

You could try using a different, non-recursive sort algorithm. There are plenty of different sorts available, though quicksort does seem to be just about the best general-purpose one.

Have you double-checked your code against the algorithm to ensure you've got it right?

One tweak which won't solve the problem but may help to alleviate it a little is using smaller data types wherever possible. For example, changing a Variant (probably the most expensive data type) to a Long, or a Longs to an Integer. And so on.

[Killer42]

Not sure whether this applies in Excel, but one "quick and dirty" technique that VB programmers have long used is to put all the data into a listbox with the Sorted property set to True.

[santhescript01]

You could try using a different, non-recursive sort algorithm. There are plenty of different sorts available, though quicksort does seem to be just about the best general-purpose one.

Have you double-checked your code against the algorithm to ensure you've got it right?

One tweak which won't solve the problem but may help to alleviate it a little is using smaller data types wherever possible. For example, changing a Variant (probably the most expensive data type) to a Long, or a Longs to an Integer. And so on.

thanks for reply..
can we implement non recursive quicksort ie iterative quicksort??

[Killer42]

thanks for reply..
can we implement non recursive quicksort ie iterative quicksort??

I'm no expert in the area, but I think the quicksort is inherently a recursive technique.

Feel free to correct me if appropriate. I recommend checking out the Wikipedia article referenced in my earlier post to get the full story.

[QVeen72]

Hi,

Excel has an In-built Data Sort. Why dont you use it..?

Regards
Veena

[Killer42]

Excel has an In-built Data Sort. Why dont you use it..?

Excellent point!