Excel VBA User Defined Function To Generate A Dynamic Array - Episode 2516

Microsoft Excel Tutorial: Can a VBA UDF generate a dynamic array?

Welcome to another episode of Excel VBA Tips and Tricks! In this video, we will be tackling two important questions: 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? These questions were posted by Executive Engineer on YouTube and sparked a great discussion with some amazing solutions from our community.

Unfortunately, the original question and solutions were deleted, but luckily, they were all captured in a thread on MrExcel.com. I highly recommend checking it out, as there were some fantastic answers using Power Query, LAMBDA, and LET. Special thanks to Smozgur and XLLambda for their contributions.

But as always, my first thought was to use VBA. So, in this video, I will be showing you how to write a User Defined Function in VBA that acts like SORT, SEQUENCE, and FILTER. We will be using the EXPLODE function to transform a table with data like "Apple, Banana, Cherry" and "Four apple, three banana, two cherry" into a dynamic array with "four apple, three banana, two cherry" as the output.

To get started, open the VBA editor by pressing Alt+F11 and insert a new module. Then, simply copy and paste the code that I will be demonstrating in this video. We will then go back to Excel and see the code in action. This is the first time I am writing VBA to return a dynamic array to the grid, so it's a pretty exciting moment for me.

In the code, we will be using a variable called R to represent the range that we will be passing to the function. The first column of this range will contain the items we want to repeat, and the second column will contain the number of times to repeat each item. We will then use this information to build our answer in a new variable called Answer. We will also use the r.Rows.Count function to determine the number of rows in the range that is being passed to the function.

One of the most interesting parts of this code is the use of the Application.Transpose function to turn the horizontal array into a vertical one. This is necessary because all UDFs must be assigned a variable with the same name as the function, and in this case, the function is returning an array that stretches across the page. But don't worry, I will walk you through each step of the code and explain it in detail.

In the end, we will have a fully functional UDF that can generate a dynamic array on the fly. This is a great solution for those who prefer to use VBA in their Excel projects. And as always, I encourage you to check out the amazing Power Query and formula solutions in the thread on MrExcel.com. Thank you for watching, and I'll see you in the next video for more Excel tips and tricks.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

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

This video answers these common search terms:
Creating a VBA UDF for generating dynamic arrays in Excel
Demonstrating the use of the EXPLODE function in Excel
Exploding a table in Excel using VBA
Exploring the use of VBA in Excel for dynamic arrays
Solutions for generating dynamic arrays using Power Query and formulas
Step-by-step guide on writing VBA code to generate dynamic arrays
Using Power Query, LAMBDA, and LET functions for dynamic arrays

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1217239/

Check out the amazing answers from Smozgur and XLLambda here:
https://www.mrexcel.com/board/threads/repeatbynumber.1216836/