A Practical Introduction to Dynamic Arrays in Excel VBA || Manipulate data faster like a Pro
HTML-код
- Опубликовано: 22 июл 2024
- So, you know the For Loop. What's next? Take your VBA programming skills to the next level by learning about Dynamic Arrays. Dynamic Arrays allow you to load Excel datasets into virtual tables so that you can loop and perform complex operations over data, much faster.
In this video, we will look at a beginner's introduction to Dynamic Arrays that will allow you to transition to Intermediate level programming.
#vba #learnvba #excelvba #excel #exceltutorial #exceltips
Code available at:
skillsandautomation.com/usefu...
Macro file can be downloaded from Github:
github.com/skillsandautomatio...
00:00 Intro
04:39 Basics of Arrays
05:44 Load Up Data into a Dynamic Array
11:04 Manipulate Data within the Array
13:09 Exclude Header Rows
13:58 Resize the Array
16:12 Do a Lookup
19:00 Create a 2nd Dynamic Array 
Наука
Hello, Links for the code and macro file are given within the description. See you in the next video!
Sir , do you have a paid course on VBA , I will buy that right away !! Kindly suggest
VERY USEFULL
Nice Explained.
great video
In the real world, nobody works with ranges anymore. We create tables for our data and use power query and power pivot. And still there are situations where vba is the only way to go. It would be interesting to watch a video using dynamic arrays in this scenario
Sir i need your help for create a cutting optimization program.
please upload video regularly
Sir Make Video On Listbox having more than 10 columns and also explained listbox formatting . Thanks in advance.
Hi. Thanks for all your comments. I will pick up this request as well. But, video will take a while. Will update once uploaded. Cheers, Ash
Hi, in your first dynamic array example (6:00 - 6:54), wouldn't it be simpler to form your data into a named table? If it was a named table, all you need to do is load the named table into memory. Is that correct? Or am I missing something?
Hi,
Named table is definitely, one way to do it. But, I would say, it would apply when you are in control of the Input data source. However, dynamic arrays would have a more generic use-case. e.g. suppose you are picking up data from a csv file. or, from a excel file coming from a third party source, which doesn't have a table in it.
Also, using tables would be a design choice e.g. do we want to maintain 100% logic within our VBA code, or do we want to share the logic with other Excel elements such as tables, formulas? what is the estimated size of data? is our data connected to another data sourcce such as SQL table? etc.
Besides that, VBA is notorious for having alternative solutions to the same problem/ task. Hence, in my videos, I will tend to try and accomplish every task using the topic for the video i.e. in this case, dynamic array.
Cheers
Can you use VBA to take data from drop down box and filter it from a table dataset to populate anothe drop down next to it and so on?
Hi, Do you mean creating dependent dropdown lists? Short answer, Yes. If that is all you want, you could achieve it using activeX combo boxes and populate the next combo box, based on change event of the first. If you wanted to achieve it without combo boxes, you could dynamically populate the second data validation list each time
Range.Validation.Add xlValidateList, Formula1:=sComments -> where sComments is string of comma separated filtered values based on the selected value in previous list, and tie this macro to an event or button trigger.
@@SkillsandAutomation Brilliant thanks, I think dynamically populate a second data validation list sounds good. I'll try and figure out how to do that. thanks
Sir , do you have a paid course on VBA , I will buy that right away !! Kindly suggest
Hi, Thanks for your comment. No paid course. But, I will be releasing a detailed zero to hero VBA series on RUclips starting April 2024. Please check it out if it interests you.
hello , I tried to add the wsOut.Cells.Clear into my code but it says"The Varible is not defined" what might be the poblem?
Hi, This code uses the Worksheet Code Name e.g. wsOut. If your code names don't match as per the video, the code will not run for you. I briefly mention the code name setup at the 04:29 mark. Cheers