Excel VBA Arrays - A Complete Guide

The event will cover arrays in VBA from the very basic to advanced.

It includes:

* What are arrays and why do you need them?
* A very simple example of using an array?
* Static arrays versus dynamic
* How to best use them in Excel

This webinar will feature live VBA coding by me as I show how anyone can use the power of arrays in their VBA code.

Useful Links
==========

[TheGentleTotoro]

Your channel is "Excel"ent!!

[krn]

Thank-you Paul. I'm glad you recorded this. I was busy and could not join the webcast. Great job.

[jesseaugustoribeiro]

Thanks for the guide, Paul. Very well explained. Cheers from Brazil!

[Mitubishi]

Thanks Paul, this has made it really easy to understand arrays. Excellent.

[khaledal-sayyary]

Thanks Powell, excellent explanation.
Greetings from Yemen.

[ryanmariner]

Very well explained and helpful! Thank you for sharing this.

[rizwansvlog]

Many thanks. You made it so simple and easy to understand...

[Deepak_Singh]

Hello,

I am from india and I am working as a VBA Developer in top MNC in India. I would like to say thanks to you for your videos. Your videos is so helpful to inhance the skills in VBA.

Thanks one again.!!!

[farmarcos]

I definitely need to understand arrays once and for all. Hopefully I'll have reached it at the end of this class🤞.

[natashak]

I can't thank you enough for this video.

[glennlangford]

Thanks Paul, I learned so much from this - not just on VBA arrays (for which your explanations and demos are probably the clearest I've seen) but also great example of using break points and locals window. Thanks also for the great questions from those who were able to participate.

[bodybarder]

Hey Paul, you should do all your website into videos, you are really good,
thanks!

[krisstaniszewski]

You are great, Paul, thanks a lot for you help

[solmenir]

Thanks a lot for recording! I couldn't join the webcast.

[shoki]

First of all thanku so much sir for this great learning, actually I want to know, Is it possible to get value in array from Excel Data model?

[DerickPitcher]

Hi Paul. This video and your webpage is my go to for arrays.
I see you're using the For Loop but can you use the For Each Loop? If so, when would you use the For Each Loop instead of the For Loop.
I've learned from you that the For Each Loop is faster.

[michaelling]

I have been using VBA to help my work for a long time. This is by far the best course. Thank you Paul.
I have a question - I have a sub that loops through a few hundreds of product, and in each loop a product object is created. When initiating each object, the program reads from a spreadsheet (20, 000+ rows) to set properties. The coding was easy but it takes 10+ min to run. I wonder whether there is a way, when workbook opens, I read in the 20K+ row datasheet into a public array, and then in each loop the product object can be initiated from the array instead of reading the spreadsheet again. I tried to add codes in workbook.open method but didn't work. Can this be done? Thank you so much!

[oleksijm]

Thanks so much for this, Paul.


Do you have a Dictionaries tutorial as well?

[regginaldsantos]

Hi Paul,


I must have missed something when retyping the code because it returned only the last 199 value when I had increased the arrays to 500 rows


Sub dynamic2()

Dim mark() As Long

Dim lastrow As Long
lastrow = shNumbers.Range("A" &

ReDim mark(1 To lastrow)

Dim i As Long
For i = 1 To lastrow
mark(i) = shNumbers.Range("A" & i)
Next i

For i = 1 To lastrow
Debug.Print mark(i)
Next i

End SubSub dynamic2()

Dim mark() As Long

Dim lastrow As Long
lastrow = shNumbers.Range("A" &

ReDim mark(1 To lastrow)

Dim i As Long
For i = 1 To lastrow
mark(i) = shNumbers.Range("A" & i)
Next i

For i = 1 To lastrow
Debug.Print mark(i)
Next i

End Sub

[georgemathew]

How to use the combination of array, class and dictionary in real time excel workbook like advancefilter, sir can u show an example of this pls…