By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,593 Members | 2,006 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,593 IT Pros & Developers. It's quick & easy.

Out Of Stack Problem

P: 8
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::
Expand|Select|Wrap|Line Numbers
  1. Sub RecursiveSort(ByVal llow As Long, ByVal lHigh As Long)
  2. Dim lStart As Long
  3. Dim lEnd As Long
  4. Dim vTemp As Variant
  5. Dim vPivot As Variant
  6. 'Set new extremes to old extremes
  7.  
  8. lStart = lHigh
  9. lEnd = llow
  10. vPivot = a_vRowElements((lStart + lEnd) \ 2)
  11. 'Till the count is less or equal to the max limit
  12. Do While lEnd <= lStart
  13.  
  14.    If bChkFlag = True Then
  15.        ' While a_vRowElements(lEnd) < vPivot
  16.         While Compare(a_vRowElements(lEnd), vPivot)
  17.               lEnd = lEnd + 1
  18.         Wend
  19.         'While a_vRowElements(lStart) > vPivot
  20.          While Compare(vPivot, a_vRowElements(lStart))
  21.               lStart = lStart - 1
  22.         Wend
  23.    Else
  24.         'While a_vRowElements(lEnd) > vPivot
  25.          While Compare(vPivot, a_vRowElements(lEnd))
  26.               lEnd = lEnd + 1
  27.         Wend
  28.         'While a_vRowElements(lStart) < vPivot
  29.         While Compare(a_vRowElements(lStart), vPivot)
  30.               lStart = lStart - 1
  31.         Wend
  32.  
  33.    End If
  34.    '
  35.    If lStart >= lEnd Then
  36.         If lStart <> lEnd Then
  37.             vTemp = a_vRowElements(lEnd)
  38.             a_vRowElements(lEnd) = a_vRowElements(lStart)
  39.             a_vRowElements(lStart) = vTemp
  40.         End If
  41.         lStart = lStart - 1
  42.         lEnd = lEnd + 1
  43.    End If
  44. Loop
  45. If llow <= lStart Then
  46.     RecursiveSort llow, lStart
  47. End If
  48. If lEnd < lHigh Then
  49.     RecursiveSort lEnd, lHigh
  50. End If
  51. End Sub
Dec 17 '07 #1
Share this Question
Share on Google+
8 Replies


QVeen72
Expert 100+
P: 1,445
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.
Dec 17 '07 #2

P: 8
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?
Dec 17 '07 #3

Expert 5K+
P: 8,434
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.
Dec 17 '07 #4

Expert 5K+
P: 8,434
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.
Dec 17 '07 #5

P: 8
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??
Dec 17 '07 #6

Expert 5K+
P: 8,434
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.
Dec 17 '07 #7

QVeen72
Expert 100+
P: 1,445
Hi,

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

Regards
Veena
Dec 17 '07 #8

Expert 5K+
P: 8,434
Excel has an In-built Data Sort. Why dont you use it..?
Excellent point!
Dec 17 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.