Excel VBA Arrays, Resize (ReDim, Preserve) Arrays, Create Functions That Return Dynamic Arrays

Learn how Excel VBA arrays work. In this tutorial we'll cover fundamentals of VBA arrays, how to declare, resize, use ReDim Preserve, create functions that return dynamic arrays.

#excel #vba #arrays

[lucasmoreira]

Finally, I found a video that made me understand arrays. Thanks for the great explanation!

[kartickchakraborty]

Thank you, Sir, for uploading the video on my request. A few years ago, I commented this question to you. Your explanation is superb. No one can explain these topics so easily. Sir, would you kindly please make a video on this topic - "How to split Strings that contain no Delimiter using User Defined Function in Excel VBA?" For Example, - You have some client names on your Sheet1 from Range("A2") to Range("A16"):

Raw Data:


Names | Middle Names | Last Names

AlexanderWilliamJamesBrown
AmandaTaylor
AriaNguyen
AubreyFoster
AveryAnnPhillips
Beerus
BenjaminThomasHarris
CharlotteMitchell
ChristopherJackson
DanielJamesMiller
DavidAndrewJohnWilliams
Dende
DylanJamesWard
ElijahWatson
EllaSophiaRoberts



Here, some clients have First Names, Middle Names and Last Names (For Example: Alexander William James Brown etc.); some have First Names and Last Names (For Example: Amanda Taylor etc.) and some have only First Names (For Example: Beerus etc.). Now, I want to delimitate each name based on the Capital Letter. Desired Output mentioned below:

Desired Output:


Names | Middle Names | Last Names

James | Brown
| Taylor
| Nguyen
AubreyFoster | Foster
| Phillips

Harris
| Mitchell
| Jackson
Miller
John | Williams

Ward
| Watson
Sophia | Roberts

[aNDy-qhem]

In VBA I'd rather prefer to use collections or dictionaries instead arrays. Especially when it comes to manipulate data in classes. But for spilling data into range that seems nice. Thank you!

[fabim]

A such perfectly video! It was exactly what I was looking for. Now I'm able to create TwoDimensional Arrays using Preserve. Thanks a lot!

[heavyferrum]

Again and again I come back to your channel to get help with some solutions I find nowhere!

[rrki]

I was searching for this type of explanation. Thanks dude !

[siddharthjain]

Nice video sirji. I loved it. I appreciate your work

[yiannis_p]

Thank you very much, this was very helpful

[benoit.gerin-lajoie]

Hi ! can we split the type of an array into the column/rows ?
For a start, when we declare an array, the *hole* array is of the type you declared it to be. Can we declare a specific row/column to be of a specific type different one from the other ?

[allaiba.R]

my vba its not working can u help me please how i solve my problem