Passing an array to a recursive sub, best practice for optimization

[duggie33]

I have a table with Bill of Material information in four columns; Parent PN, Child PN, Quantity, Units of Measure.

Given a parent part number as an input I am returning the "exploded" or "indented" bill of material. Essentially that means I am returning all the children of the parent, all the children of the children, etc. At most I have seen five generations. The order of operations is to loop through an array to find the first child, write the row data to a worksheet, recursively call the sub again to look for a child of the child, and so on and so forth until no more children are found.

I have this working but am curious to know if it is not optimized for speed. Say I am at the fourth generation (the first was input by the user), the call stack has three of the same procedure running, are there three of the same arrays loaded into memory or one? If I define the array within the called procedure I think it would be in memory for each, but what if I pass the array into the first call and then recursively call the same array with the same argument name? I could be way overthinking this, but I have tried it both ways and do not know how to tell what it is doing by looking a the Locals window.

I am not changing the array so I have defined it as ByVal. I was expecting an error based on what I was reading on Chip's website but his example was probably doing something different. To give you an idea of size, the array is (1 to 23300, 1 to 4) and I have a BOM with over 300 items so it loops through whole array that many times.

I am guessing it might be faster to build an output array rather than writing to the worksheet but I have not taken that step yet. Any insight into passing arrays recursively or how to tell how many arrays are stored in memory would be greatly appreciated.

Thanks,

Doug

 

[Eric W]

With a maximum depth of 5, you'd probably never notice a difference in speed or memory usage. But if you don't change the array, you'd be better off defining it as ByRef. When you define it as ByRef, it just passes a pointer to the original array, and any updates to the array will update the original one. If you define it as ByVal, you're only passing the values in the array at that moment in time. This means that VBA has to make a copy of the array, so each successive call makes another copy. You can look at the call stack by going to the View > Call Stack menu item while in Debug mode.

As far as writing to the sheet, if you write more than a hundred items or so, it's definitely faster to write to an array and write the whole array at one time.

 

[duggie33]

Thanks Eric. I was looking at the call stack and see the multiple calls. Is there a way in debug mode to know how many arrays there are in memory for sure? I know you say each call makes a copy...I am looking for a way to see that in VBA/Excel or other.

Thanks for confirming, I will change the output to an array and then write to the sheet at one time.

Doug

 

[Eric W]

I'm not aware of a way to look at the storage associated with a given instance on the call stack. There are some more technical people here who might know.

 

[duggie33]

No problem, thanks. The ByRef change did make a significant difference.

Admission forthcoming...

I was unhappy with the performance because it took between 10 and 80 seconds to complete the task. I had a separate workbook with a copy of the data table and was experimenting with different ways of extracting the right information. It turned out that, for this situation, both advanced filtering followed by some merging of generations and looping through arrays showed the most promise. In my test workbook I was initiating the code with a form control button on the worksheet but not doing the cell color, text alignment, and font adjustments that I do on the "live" workbook. It was pretty fast like 10-20% of the time that the "live" workbook takes to complete the task. I thought it was all related to the formatting of the cells.

Oh man, I wasted most of the day trying to sort this out mainly out of determination to make this thing work faster...

On the "live" workbook I have ActiveX buttons and they apparently really slow it down. Not a little, 5-10 times slower to execute. I have no idea why, if it is a known thing, a bug, or whatever. I changed to a form control button and relatively speaking, it is lightning fast now. Result is mostly happy with some unhappy with how long I spent on it.

 

[Eric W]

I hadn't heard that ActiveX controls slow things down. I suppose it's possible. I do know that they are "bigger" than form controls. I had a spreadsheet once that was megabytes big, and I needed to shrink it. I replaced about 20-30 ActiveX controls with form controls, and it reduced the size by several hundred K. That was a few versions back though, I'm not sure that's still the case.

 

[offthelip]

I am guessing it might be faster to build an output array rather than writing to the worksheet but I have not taken that step yet.


Doug

this will always speed a macro that is reading or writing to worksheet in loop and is one of the easiest ways of speeding up a macro. just load your entire output sheet as a variant array and then you just change the cell addresses to array addresses. Don't forget to write the array back to the worksheet

 

[duggie33]

Searching, I found someone says there is a bug with ActiveX controls that are not visible and VBA makes them visible before writing to the worksheet and then makes them not visible again. In a loop over thousands of cells, that would be some extra time. In my case, I had six ActiveX buttons and all were visible all the time. Related or not, I do not know.