Out of stack space

[Costertje]

Hi,

I've made a VBA macro which filters the number of orders per month and per webshop. But when he's almost ready I get an out of stack space error. I've tried to find a solution for this, but I've don't find a working solution. I don't think that the code runs in a loop, because each code is the same except the filtering.

So maybe someone can help me with this.

There's are 3 files:
export order 2021.xml
Overzicht bestellingen webshop per maand.xlsx
Example orders.xlsm

I open first manually the files export order 2021.xml and Example orders.xlsm. I select the file export order 2021.xml and then I start via "developer" the macro "Start".

I'm not an VBA expert, so maybe I used something wrong in the code.

Thanks,
Peter

[6StringJazzer]

"Out of stack space" happens almost always because of runaway recursion. A Sub calls itself, and this happens so many times you build up this very deep stack of calls to this sub and you run out of available space.

I ran an analysis on your code and found that the code filter_MBO_promo_shop_xxx for each month calls filter_VO_promo_shop_xxx for the following month. This leads to more code that eventually calls the following month, and then in December it calls Start. I think this creates a loop which never ends, which would explain why you are running out of stack space. You should do a detailed trace of this code to see if this is the problem, and then re-think you logic to see what you really want the code to do.

Second you need to improve your code structure. Many subs end by calling another sub. Instead of daisy-chaining these subs together you should have one master sub that calls them all in sequence.

You have a copy of the code for each month that is identical except for a couple of small things. You should have one copy of the code that uses an argument for this. Otherwise, if you ever have to change your code, you will have make the same change in 12 places.
As an example here is the first part of one sub:

Sub filter_VO_docent_aug()
Dim NewRng As Long
    NewRng = Range("A" & Rows.Count).End(xlUp).Row
Dim rFiltered As Range
Dim ColNum As Integer
Dim ws As Worksheet
    
    

'bestand selecteren

    

    'Sheets("1").Select
    Rows("1:1").Select
    Selection.AutoFilter

Set rFiltered = ActiveSheet.AutoFilter.Range

'geeft aan welke kolommen je gaat filteren en wat de filter criteria is

    With Range("$A1:$D" & NewRng)
        
        .AutoFilter Field:=2, Criteria1:= _
        "VO Docent webshop"
        .AutoFilter Field:=4, Criteria1:="<>"
        .AutoFilter Field:=3, Criteria1:="*aug*"

I would rewrite it like this and call it as shown below.

Sub filter_VO_docent(MonthCode As String)
Dim NewRng As Long
    NewRng = Range("A" & Rows.Count).End(xlUp).Row
Dim rFiltered As Range
Dim ColNum As Integer
Dim ws As Worksheet
    
    

'bestand selecteren

    

    'Sheets("1").Select
    Rows("1:1").Select
    Selection.AutoFilter

Set rFiltered = ActiveSheet.AutoFilter.Range

'geeft aan welke kolommen je gaat filteren en wat de filter criteria is

    With Range("$A1:$D" & NewRng)
        
        .AutoFilter Field:=2, Criteria1:= _
        "VO Docent webshop"
        .AutoFilter Field:=4, Criteria1:="<>"
        .AutoFilter Field:=3, Criteria1:="*" & MonthCode & "*"

filter_VO_docent MonthCode:="aug"

Other things I noted but are not causing your problem:

You declare variable ColNum but never use it.

[Costertje]

Hi Jeff,

Thank you for explaining the cause of the error message. I didn't know you can't use unedited subs. So we learned that again.

I hope I understand your explanation correctly and I have modified the code slightly. I'm going to use 6 subs now (there are 6 webshops), so that's a lot less than the previous code.

If you have any comments or if I misunderstood you, please let me know.

It's nice to learn from an expert.

This is a little piece of my new code

Sub filter_VO_docent()
Dim MonthCode As String
Dim webshop As String
Dim NewRng As Long
    NewRng = Range("A" & Rows.Count).End(xlUp).Row
Dim rFiltered As Range
Dim ws As Worksheet
    

    webshop = "VO Docent webshop"
    MonthCode = "jan"

    Rows("1:1").Select
    Selection.AutoFilter

    Set rFiltered = ActiveSheet.AutoFilter.Range

'geeft aan welke kolommen je gaat filteren en wat de filter criteria is

    With Range("$A1:$D" & NewRng)
        
        .AutoFilter Field:=2, Criteria1:=webshop & "*"
        .AutoFilter Field:=4, Criteria1:="<>"
        .AutoFilter Field:=3, Criteria1:="*" & MonthCode & "*"
        
        
'als er geen filter resultaat is dan kan er niets gekopieerd worden en loopt het programma vast
'daarom wordt hier gekeken of er minder dan 1 resultaat is
'als dit zo is gaat hij naar de volgende filtering
'is dit wel zo dan gaat hij verder met het kopieerten van het resultaat
        
    If .Columns("A").SpecialCells(xlCellTypeVisible).Count > 1 And Columns("B").SpecialCells(xlCellTypeVisible).Count > 1 And .Columns("C").SpecialCells(xlCellTypeVisible).Count > 1 And Columns("D").SpecialCells(xlCellTypeVisible).Count > 1 Then



'hier gaat hij de kolommen kopieeren en plakken op het tabblad van de desbetreffende maand
    
    rFiltered.Offset(1, 0).Resize(rFiltered.Rows.Count - 1).Columns("A:D").SpecialCells(xlCellTypeVisible).Copy
    Sheets(1).Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    
    Set ws = Worksheets(1)
    
    With ActiveSheet
    
    i = Application.WorksheetFunction.CountA(ws.Range("A2:A1000000"))
    
    Windows("Overzicht bestellingen webshop per maand.xlsx").Activate
    Worksheets("Januari").Select
    Range("C5").Select
    ActiveCell.FormulaR1C1 = i
    
    End With
    End If

'hier gaat hij naar de volgende filtering
'hij zet ook eerst de filter weer uit, anders gaat de volgende macro mis

    Windows("Export uit Magento.xlsx").Activate
    Worksheets(1).Select
    Cells.Clear
    Sheets(2).Select
    Selection.AutoFilter
    
    Rows("1:1").Select
    Selection.AutoFilter
    
    MonthCode = "feb"

    Set rFiltered = ActiveSheet.AutoFilter.Range

'geeft aan welke kolommen je gaat filteren en wat de filter criteria is

    With Range("$A1:$D" & NewRng)
        
        .AutoFilter Field:=2, Criteria1:=webshop & "*"
        .AutoFilter Field:=4, Criteria1:="<>"
        .AutoFilter Field:=3, Criteria1:="*" & MonthCode & "*"
         
'als er geen filter resultaat is dan kan er niets gekopieerd worden en loopt het programma vast
'daarom wordt hier gekeken of er minder dan 1 resultaat is
'als dit zo is gaat hij naar de volgende macro
'is dit wel zo dan gaat hij verder met het kopieerten van het resultaat
        
    If .Columns("A").SpecialCells(xlCellTypeVisible).Count > 1 And Columns("B").SpecialCells(xlCellTypeVisible).Count > 1 And .Columns("C").SpecialCells(xlCellTypeVisible).Count > 1 And Columns("D").SpecialCells(xlCellTypeVisible).Count > 1 Then

'hier gaat hij de kolommen kopieeren en plakken op het tabblad van de desbetreffende maand
    
    rFiltered.Offset(1, 0).Resize(rFiltered.Rows.Count - 1).Columns("A:D").SpecialCells(xlCellTypeVisible).Copy
    Sheets(1).Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    
    Set ws = Worksheets(1)
    
    With ActiveSheet
    
    i = Application.WorksheetFunction.CountA(ws.Range("A2:A1000000"))
    
    Windows("Overzicht bestellingen webshop per maand.xlsx").Activate
    Worksheets("Februari").Select
    Range("C5").Select
    ActiveCell.FormulaR1C1 = i
    

    End With
    End If
        
        
End With
End With
End Sub

[6StringJazzer]

I didn't know you can't use unedited subs.

Not sure what this means. I didn't say this and I don't even know what you mean by "unedited subs."

The problem seems to be that you have a closed chain of subs calling each other and it never ends. I did not take the time to go line-by-line through at least 12 and maybe 48 subs to verify it.

The point about daisy-chaining subs is not causing the error but it violates best practices.

[Costertje]

Sorry for my bad English, but what I mean with unedited subs is; it is not possible to make infinite use of subs.

And I understand that you don't go line-by-line trough the code. So thanks to your explanation I finally managed to rewrite the code and now it works fine.

Thanks!