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

duggie33

Board Regular
Joined
Nov 19, 2018
Messages
77
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,486
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

Board Regular
Joined
Nov 19, 2018
Messages
77
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,486
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

Board Regular
Joined
Nov 19, 2018
Messages
77
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,486
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

Well-known Member
Joined
Dec 23, 2017
Messages
1,560
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Nov 19, 2018
Messages
77
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,755
Messages
5,574,044
Members
412,565
Latest member
roberttaekim
Top