Can Excel VBA return a dynamic array from a user defined function to the grid?
Can you repeat each cell in column A based on the number in column B?
Check out the amazing answers from Smozgur and XLLambda here:
www.mrexcel.com
Table of Contents
(0:00) Two titles
(1:11) Using VBA
(2:05) How many cells to return
(2:50) Building the array
(3:22) Return array to grid
(4:00) Using Hash array operator
Can you repeat each cell in column A based on the number in column B?
Check out the amazing answers from Smozgur and XLLambda here:
REPEATBYNUMBER
REPEATBYNUMBER function takes a range (or table) that has the first column as the item name and the last column as the repeat number then returns a new column of rows with the item names repeated by the required number of times. (I learned the main idea from Karina Adcock, and used the TAKE...
Table of Contents
(0:00) Two titles
(1:11) Using VBA
(2:05) How many cells to return
(2:50) Building the array
(3:22) Return array to grid
(4:00) Using Hash array operator
Transcript of the video:
This is another episode with two titles.
First title, Can we Generate N Copies of Each Row Based on the Values in the Cells?
And more importantly, Is it Possible for VBA to Return a Dynamic Array?
Can I write a UDF, a User Defined Function, in VBA that acts like SORT, SEQUENCE, FILTER?
The question posted at YouTube by Executive Enigneer.
Has data like this: Apple, Banana, Cherry. Four apple, three banana, two cherry.
And wants to explode that table into this. So, four apple, three banana, two cherry.
From this post at YouTube, there were amazing, amazing solutions posted.
And then, this guy deleted his question, which deleted the solutions.
They're all in a thread right here at MrExcel.com. I'll put that down in the YouTube description.
Amazing answers there using Power Query, LAMBDA, LET.
Either one with dynamic arrays and one without dynamic arrays.
Thanks to Smozgur and XLLambda for those amazing answers.
But just like the old dueling Excel podcast with Mike Girvin, my first thought is VBA.
Alt+F11 to the VBA editor, Insert, Module, and then paste this code.
We'll go back to Excel and look at it side by side.
It's the first time that I'm ever writing VBA to return a dynamic array to the grid.
So, here's my version of the table.
I even threw a gotcha in there with banana of zero.
I have a formula here =EXPLODE, and I pass this two-column array.
The first column is going to be the items we want to repeat, the second column is going to be how many times to repeat.
So, that variable R becomes the range.
I'm going to build my answer in a new variable called Answer.
I don't know how large it will be right now, I just have to DIM Answer as a Variant. I'm going to re-size that later.
We're going to figure out how many rows we have using r.Rows.Count.
R is the variable that we're passing, so the caller, how many rows did it give us?
We're going to know that.
And then, this awesome little bit of code right here.
The final size of the array that we're going to return is the sum of all of these answers here in column two of the R range.
Once we know what the final size is going to be, then we redimension Answer to be 1 to FinalSize Now, the really weird thing here is that is building an array that stretches across the page, and we'll have to deal with that one later. All right.
So, we're going to build these one at a time. I loop from one to the number of rows.
The answer for a particular position is going to be the value in this row, column one.
So, apple, the first time, the second time, the third time, the fourth time.
Banana, because it's zero, will never run. Cherry will be the fifth time and the sixth time.
Dill, you get it. Increment counter there, next J, next I.
Now, all UDFs, if it's called =EXPLODE, you need to assign a variable at the end with that name.
And unfortunately, this was going across every time, so we had to use Application.Transpose of the answer to turn it back into a vertical array. But wow, I'm really happy with this.
Let's just take a look at what we could do here.
So, =EXPLODE, choose the array, close paren. It creates it as the numbers change.
So, two apple, one banana, three cherry. It automatically updates.
Can I refer to that entire array using the array operator, the #?
So, equal count A of D4#. Look at that, it's beautiful.
Something that the chart SERIES function can't do yet, but this little VBA UDF works beautifully.
This was a great question with amazing answers.
I'm just really happy that I was able to figure out how to create a little bit of VBA that will generate a Dynamic Array on the fly.
Definitely go check out the amazing Power Query and formula answers at this thread.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
First title, Can we Generate N Copies of Each Row Based on the Values in the Cells?
And more importantly, Is it Possible for VBA to Return a Dynamic Array?
Can I write a UDF, a User Defined Function, in VBA that acts like SORT, SEQUENCE, FILTER?
The question posted at YouTube by Executive Enigneer.
Has data like this: Apple, Banana, Cherry. Four apple, three banana, two cherry.
And wants to explode that table into this. So, four apple, three banana, two cherry.
From this post at YouTube, there were amazing, amazing solutions posted.
And then, this guy deleted his question, which deleted the solutions.
They're all in a thread right here at MrExcel.com. I'll put that down in the YouTube description.
Amazing answers there using Power Query, LAMBDA, LET.
Either one with dynamic arrays and one without dynamic arrays.
Thanks to Smozgur and XLLambda for those amazing answers.
But just like the old dueling Excel podcast with Mike Girvin, my first thought is VBA.
Alt+F11 to the VBA editor, Insert, Module, and then paste this code.
We'll go back to Excel and look at it side by side.
It's the first time that I'm ever writing VBA to return a dynamic array to the grid.
So, here's my version of the table.
I even threw a gotcha in there with banana of zero.
I have a formula here =EXPLODE, and I pass this two-column array.
The first column is going to be the items we want to repeat, the second column is going to be how many times to repeat.
So, that variable R becomes the range.
I'm going to build my answer in a new variable called Answer.
I don't know how large it will be right now, I just have to DIM Answer as a Variant. I'm going to re-size that later.
We're going to figure out how many rows we have using r.Rows.Count.
R is the variable that we're passing, so the caller, how many rows did it give us?
We're going to know that.
And then, this awesome little bit of code right here.
The final size of the array that we're going to return is the sum of all of these answers here in column two of the R range.
Once we know what the final size is going to be, then we redimension Answer to be 1 to FinalSize Now, the really weird thing here is that is building an array that stretches across the page, and we'll have to deal with that one later. All right.
So, we're going to build these one at a time. I loop from one to the number of rows.
The answer for a particular position is going to be the value in this row, column one.
So, apple, the first time, the second time, the third time, the fourth time.
Banana, because it's zero, will never run. Cherry will be the fifth time and the sixth time.
Dill, you get it. Increment counter there, next J, next I.
Now, all UDFs, if it's called =EXPLODE, you need to assign a variable at the end with that name.
And unfortunately, this was going across every time, so we had to use Application.Transpose of the answer to turn it back into a vertical array. But wow, I'm really happy with this.
Let's just take a look at what we could do here.
So, =EXPLODE, choose the array, close paren. It creates it as the numbers change.
So, two apple, one banana, three cherry. It automatically updates.
Can I refer to that entire array using the array operator, the #?
So, equal count A of D4#. Look at that, it's beautiful.
Something that the chart SERIES function can't do yet, but this little VBA UDF works beautifully.
This was a great question with amazing answers.
I'm just really happy that I was able to figure out how to create a little bit of VBA that will generate a Dynamic Array on the fly.
Definitely go check out the amazing Power Query and formula answers at this thread.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
