Excel VBA Introduction Part 25 - Arrays
HTML-код
- Опубликовано: 8 июл 2024
- If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!
You can buy our Introduction to Excel VBA book here www.lulu.com/shop/andrew-goul...
By Andrew Gould
www.wiseowl.co.uk - An array is a lot like a variable, only with an array you can store more than one value under the same variable name. This video explains how to work with arrays in VBA, including how to declare basic, fixed-size arrays, populate and read from an array and how to detect the lower and upper bounds of an array. The second half of the video demonstrates more sophisticated arrays including dynamic arrays and multi-dimensional arrays, as well as covering some techniques for speeding up calculations by using arrays. You'll also see how to resize arrays dynamically, and how to transpose an array.
Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio, SQL Server, Reporting Services, Analysis Services, Integration Services, Visual Studio, ASP.NET, VB, C# and more!
For Efficient Reference:
What You'll Learn 00:11
What is an Array? 00:42
Declaring a Fixed Size Array 01:30
Using the Option Base Statement 02:24
Declaring the Lower and Upper Bounds 03:11
Populating an Array 04:07
Reading from an Array 04:56
Erasing an Array 05:38
Looping Over an Array 07:02
The LBound and UBound Functions 11:10
Declaring Multi-Dimension Arrays 13:02
Populating a Multi-Dimension Array 15:49
Looping Over Multi-Dimension Arrays 18:31
LBound,UBound and Multi-Dimensions 22:13
Reading from Muti-Dimension Arrays 24:57
Dynamic Arrays and ReDim 26:59
Writing a Range to a Dynamic Array 31:44
Erasing Dynamic Arrays 34:16
Writing a Dynamic Array to a Range 35:25
Preforming Calculations in Arrays 38:24
Outputting Answers in New Range of Cells 45:39
Resizing Arrays Dynamically 47:25
LCase Function 49:26
Preserving the Content of Array (ReDim Preserve) 54:20
Transposing an Array (application.transpose) 57:31
Thanks Thomas!
Thanks
Wise Owl for President! I can’t believe the teaching quality and methodological rigor of this series. Every chapter is a winner. One of my absolute favorite goto VBA sources. Bravo!
31:44 is hilarious. COMPLETELY over the top *Tearfully shreds all my notes up to now*
These are amazing videos, you are single handedly doubling my work productivity. If you are ever in Dublin, there is a pint owed. I have never learned so much from a RUclips video
I love this comment because it reminds me of the first time I discovered this!
Thanks for the offer, how could I refuse? I'll let you know if I make it over, thanks!
WiseOwl, you helped teach me how to implement a macro at my work that saved hours of work each month (getting praise from my boss + departments) - thank you for your crystal clear and simple tutorials!!! soo appreciative of your work here
I'm so happy to hear that the videos have helped you in your work! Thanks for watching and taking the time to leave a comment, I appreciate it!
@@WiseOwlTutorials no problem at all :) i'm so excited to watch more of your videos to further simplify tasks for my department at work !
@@WiseOwlTutorials thank you! We love you
@@7Denial7 Thank you Artem!
Since I am new to this field, I took a week to understand this lecture. Currently, I am almost certain that I can write a book for this lecture due to the comprehensiveness of this lecture, which explains the array.
Really so so so so thanks
You're very welcome! I'm glad that you found it useful and thank you for watching!
You just cleared up something about LBOUND and UBOUND that has plagued me for years. No one else has ever explained it that way. Thank you!
I love how you talk fast. I can't stand waiting for others to finish their sentences.
@@WiseOwlTutorials I knew that. But I suppose the speech speed is usually indicative of getting more information into the tutorial. Thanks for response! I'm doing some pretty intense coding this weekend, and I'm more of a matlab guy, so nice to know I might get a response if I have a question. Thanks!
Wow! You know that feeling when you've found the tutorial that you've been looking for?...I just got that feeling. I landed on part 25 because I needed a refresher on arrays. I am now a subscriber and will be working through the entire series. Well done!
Thank you so much Andrew! This is better than the VBA class I paid for!
Thank you for explaining the Arrays in relation to the "dimensions" of the Sheets in excel. It is such an elegant way to describe what is happening in the script language. And i have never seen yet a better way to use (and explain) the expressions window.
By far the best VBA tutorial I've ever watched.
Wow.....what an amazing collection of tutorials. I have really enjoyed learning from all your videos, and watched them many times now.
Thank you so much for all the VBA excel tutorial you've uploaded. Each one of your material is better than any of the paid tutorial in the internet.
I have been relying on RUclips when it comes to learning such as programming and high level mathematics. But this is by far the best learning experience that I have encountered so far. Not to mention that this is my very first reply on RUclips. Thanks so much for sharing your expertise with everyone!
Andrew / WiseOwl, your pace and level of explanation is just perfect. I write very large models for supply chain optimization. Speed is everything especially in large models. Using arrays as you explained is key way to make my models run amazingly fast. Thanks a ton.
i've always had trouble understanding arrays dimensions.
you explained it so clearly, i think i finally understood. amazing! thank you so much!
your videos are incredible, from the content to the production value. i'm obsessed.
Andrew, You make the concepts so clear ! Thank you WiseOwl :)
I have watched many instructional videos on RUclips, and without a doubt, yours are the best. I can't thank you enough.
You sir are a life saver ! I knew what I was doing was possible somehow and you just nudged it home! Very good instructions!
Thank you!
After watching you run through 12,000 rows yesterday in a flash using an array, I had to rewatch this one again. Love how quick you can calculate hundreds or thousands of rows in a few seconds compared to an individual for each loop. Thanks Andrew.
Andrew, must say this is just what I was looking for. I've spent ages looking through forums, books etc to get a grasp of arrays! I'll look no further, this is Top Drawer Stuff Many thanks for posting
***** Andrew, if you have a moment. How did you get started in VBA? I think I noticed you were a Biologist. I'm just interested in best paths taken to become as proficient as possible. Would this be to build applications and learn along the way (i.e have a problem to solve). I'm currently learning by solving problems as they arise, but going through your videos, you kind of realise that there are more efficient ways of coding. Regards Rob
Thank you sooo much Wise owl. You've helped me so much to understand vba better
Thank you! Your videos are great. You save my day... You deserve a medal
i love how you said genre for twilight was "awful"
I like twilight :(
Great tutorial not only for begginers but also for those more advanced in VBA :)!
Thank you, Anna!
i sow all your videos, its my main source for learning, after i search the web many days .
I have really learnt a lot of useful skills from you. Great teaching skill!
man i never get bored listening to your videos :)
Another great video. Thanks a ton for putting it together!
Thank you Andrew :) Learning about array dimensions is helpful for my work.
Your right, my confusion was overlooking the fact that using -1 caused results to be
Dimension1(0 to12,0 to 4) instead of Dimension1(1 to 13, 1 to 5). Thanks again for your powerful and engaging series!
Hi WiseOwlTutorials, thank you for sharing you knowledge. It is really helping me.
I wonder why would someone dislike an educational video.
Thanks brother, by the way.
Amazing tutorial for beginner, many thanks!
28 down, 74 to go :-D And each has revolutionized some aspect of my work.Greetings and thanks from Namibia
Superb videos :) Thanks so much. U r helping all excel enthusiasts so much
Very helpful video. Arrays have always been difficult for me and your video finally made "the light bulb go off". LOL it finally makes sense. Thanks.
Dear Andrew, great stuff !!! Thanks a lot. Greetings from Germany
So many useful tips in One video!
Thank you, Peter!
Sir, you are AWSOME! A great explanation... best regards from Portugal...
Hello Andrew .......It was awesome. Got to know many things in arrays.
Cheers !!!! Thanks.
Excellent course !
Unbelievable nice tutorial. Thank you so much.
Brilliant as usual.
I donated! So worth it. Thanks again.
Thank you for all your effort.
You're very welcome, George! Thank you for watching!
Better than most. Yet since this is an introduction, it would have been very helpful if you had run your VBA statements after each change so that we could see what that does.
Brilliant! Thank you very much!
I loved the neat trick at 33:00 !
Very helpful Video, thank you for sharing
thank you sir,
I am fan of yours.
I am vba developer.
upload more videos like this.
Hi , Very nice Explanation and learnt a lot. We can use dynamic variables as lastrow and lastcolumn to loop across rows and columns instead of LBound and UBound. See the below code I have used:
Dim a, b As Integer
Sheet1.Activate
Range("a1").Activate
a = ActiveCell.Cells(Rows.Count, 1).End(xlUp).Row - 1
b = ActiveCell.Cells(1, Columns.Count).End(xlToLeft).Column
ReDim toptenfilms(0 To a, 0 To b) As Variant
Dim i, j As Long
For i = 0 To a
For j = 0 To b
toptenfilms(i, j) = Range("a2").Offset(i, j).Value
Next j
Next i
Thanks,
Nitish
Absolutely Awesome!!!
very well explained! thank you!
Excellent. Thank you.
Excellent... Thank you
Thanks! This helped me a lot!
Love your videos!
BrIlliant material ❤ thank you sir!
Thank you for watching!
For anybody trying to redim preserve the first dimension in a multi dimension array, you have to transpose the array. Make the first dimension equal to the second dimension and change the second dimension to the number of dimensions desired then transpose again.
Very productive tutorial
Thanks for watching!
Thank you so much bro your really great..
Oh thanks a lot.. awesome videos... thanks for sharing the knowledge with us.. :-)
49:20 where you talk about case there is also a side point about a variable instead of = that enables wildcards.
Instead of:
Value = "action"
Value Like "*ctio*"
So "action" will be detected but also any film criteria containing ctio so "action film" will be picked up.
So you are catching similar criteria, if multiple people are inserting different but synonymous terms.
Thank you!
Thank you once again... maybe i should just watch the rest.
Thank you Andrew Sir
1000 times thank you so much, sir
One thing I learned that if it is possible to avoid looping than avoid it because it slow the code
You are, as always, very welcome Pradeep!
great work this !!!
I [very rarely] comment on content but I am very impressed with your video and it explained to me in excellent language that I was able to understand
Keep up the good work and thank you very much,
Andy
Excelent, Your videos are awesome, thank you very much, I learned arrays thanks to you. but using irregular dynamic arrays in a loop is very complicated, actually ı dont know that if it is possible, so I also wonder very much irregular or jagged arrays especially for your "resizedynamicarray example" ?
Sir, your presentation method is excellent. I watch many "HOW TO" videos and I find I have to repeat the video over and over and try to disect the information from the data. You assumed nothing from the viewer, went through step by step and explained every step and the pitfalls and errors, it was so easy to see and comprehend what was going on (except for 1 thing, something like Range D3 to D2 exceldown, but another time it was D3 to D3 exceldown, can you explain the rationale? )I'm only going to give 9999.9 /1000.
@@WiseOwlTutorials
Shocked that you replied I was expecting a fellow viewer to enlighten me. However as you did can I ask another question (BTW I stumbled on the Array video, working my way through all your vids now). When you declare an array why use (0 to 9, 0 to 8) can't you use (9,8) like in old BASIC. I'm a 50 yr old ex sinclair, BBC novice programmer(very losely). I'm trying to create a sudoku /helper if that helps you understand where I'm coming from. Ps if I declare an array can I examine how many empty cells in board empty? Dim array for empty cells (num) capture address, work through possible vals. If I say cell (3,2) can be 6, 9, can I do a count of possibilities? Eg if possibilities =1 then fill cell.
I don't expect you to build this for me I'm just posing scenarios for you to do another brilliant video. Kind regards Craig
Thank you for this!!
You're very welcome!
thanks again :)
Hi Andrew,
Awesome learning videos thankyou very much.
Is there a way to Redim an array while preserving but using the "quick" method? I need to redim preserve a large array but then add in lots of data as i loop through sheets. I'm after efficiency as i'm turning the sub into a function and right now it's too slow. The end of your video described the redim preserve but it was looping through each cell in the sheet range.
Hi, thanks for explaining arrays. I have a question. How do I apply array stored values to filter pivot table? Please advise. Thank you.
Wonderful
Thanks Muhammed!
Hi Andrew, trust you are doing well. I need your expertise. I have different values in range which consists of positive and negative value. like 112, 36, -158, 62, 10. I need to apply a logic where the positive values when sums up and equals to the negative value -158, then the code should highlight all cells which made up to this combination like 122, 36, 10 and -158 should be highlighted in yellow color. The code should be so dynamic to handle the various positive values and keep on adding up on various combination of positive numbers until it matches the negative amount.
I like your Irish accent and your videos too
Thank you for your tutorials, very useful and structured information.
I have one question, may be it sounds stupid but i'm just curious... Is it possible to populate the arrays not only with values, but lets say with objects or with other arrays?
Quick question, I have an array, ranging between 10 by 15 to 80 by 120 in size, with three different formulas being calculated and inputted into the cells. I need to be able to run the formulas say 7 times, populating the array with different values each time, but in the end I need it to display the sum of the formulas for each cell. So when everything is said and done, cell C4 for example needs to show formula1+formula2+formula3+. . .
How would I go about that exactly? Thank you>
I love these videos! I've learned so much already. I do have a question that has been confusing me since the beginning though. I'm not sure why it is that when you are selecting a dynamic range, like at 33:06, you select the top left corner (cell A3), but then when you are giving the second half of the range, you select the one above it (cell A2). I've noticed this seems to be your standard procedure, but I'm not sure why. Is there a reason you don't just use A3 for both parts?
Elaine McKenzie It's just a good practice, this will work by selecting the same starting cell (A3). But it's a good practice to know where the actual starting point of your list is.
or to use this: e.g "abc = WorksheetFunction.Subtotal(3, Columns(1))" or rows(1) but it's good if you don't have any data below table
Dear Andrew,
i've a question on the video segment of CalculateWithArray. In this instance, you wrote a for next loop with array to compute and populate the elements of filmlength into 2 different columns (F & G)
i'm looking to do something very similar, but in my current work i'm writing a nested for next loop. Bascially, i'm looking to repeat the process several time for the other columns.
Right now, i'm struck (keep getting an error prompt "subscript out of range"). Are you able to provide some advice on how to overcome this?
Very educative video. I have 1 question. Can you make a video in which data (in time format) is present in columns with start time and end time and this data have to be sort in ascending order...
Hi Wise Owl,
Is the array only for cells inside the excel or for even files outside excel?
Hi Wiseowl, great video. I tried running the quick dynamic array following your code video 37:17 when I get to this line of code Range(ActiveCell, ActiveCell.Offset(UBound(TopFilms, 1) - 1, UBound(TopFilms, 2) - 1)).Value = TopFilms I get the Run-time error 1004 Application-Defined or object-defined error. However if I remove Worksheets.Add and allow the data to update sheet1 it works. How do I get it to work on a new or different sheet?
Hi Andrew, i have a question with regards to performing a simple subtraction calculation using arrays. If i have sales price and cost price and want to calculate the difference between the two (cash margin) how can i do it using arrays?
Very interesting topics found on timeline 39:00 but a clarification required on timeline 40:55 as why End Range begins from D2 while same results can be obtained by using End Range as D3 because begin range is D3. Please help.
Hi Andrew, , great video on Arrays, keep coming back and revisit this brilliant tutorial as reference guide.
I wasn’t quite sure whether to post my question within the scope of this video, but I thought to ask you whether you might have some ideas how to do the following task I am trying to accomplish:
I have more than 600 workbooks in a folder containing multiple spreadsheets. Each of spreadsheets contains some sort of data which I need to access their range and copy to new workbooks.
From sheets stored in one workbook for example I only need one, which is predominantly stored at Sheets index position 1, although in more than 100 workbook that is not the case. I already looped through all files in folder with Dir() and know which workbooks do have sheets on wrong index position. I need also to copy a range of that specific sheet to another workbook, which I am able to do but how to use VBA to find dynamically workbook I need and sheet by its index position. I really want to avoid opening via Dir function in a loop workbooks and do work manually.
How can I dynamically use an array or any other method and find those workbooks and sheets within workbooks that I am interested and either flag them through Boolean if statement and either have msg box pop out saying: “This sheet needs work”, or just copy the range to new workbook and save it along with the remainder of workbooks in a folder.
Your ideas would be much appreciated.
Thanks
Denin
This video has been enormously helpful in my understanding with VBA. At 28:53 however, I still don't understand the need for having the ...range("A2"). What is the reasoning or logic behind having this here?
So when the range for Dimension1 is calculated, it counts from "A3", then..I get confused how the "A2" plays in there..
Awesome video, really helped me out a lot. I have 1 issue though. I have an array that I populate with a loop. I am preserving the array like you showed in the video to dynamically grow the array. I am attempting to transpose it to a sheet and I get a type-mismatch error. If I do not transpose, it works great.
Range(Activecell, Activecell.offset(UBound(answers, 2) - 1, 15)).Value = Application.Transpose(answers)
Any thoughts or help.
Hi Andrew,
Your teaching technique is excellent... I have learnt a lot from all of you videos.
I have small question on the calculating the time from the length of the movie:
Which one is more efficient as per you experience considering time it takes to do the job
1) using loop as you did in your previous videos
2) using this technique of arrays - where we do the calculation in Array variable
Thanks,
KP
Really thanks for this great video Andrew. I only wonder how to test dynamic array for empty. When Redim Preserve, I intentionally changed all Action films to other so ActionCounter is 0 and I seem not to test if variant array is empty using IsEmpty, IsNull, Ismissing or other functions. Even Locals show (blank) instead of "Empty"
I am sorry, I found a simple way. It is just to move ActionCounter = ActionCounter + 1 below lines of Loop that populates the array. Then to change counters from (1 to 5) to (0 to 4). And finally before transposing array, to test if ActionCounter = 0 (if yes, then e.g. exit sub). I found that once dynamic variant array is initiated, it is never Empty again.
Hello. I would like to ask a situation. How can I output the store data in the array to a new worksheet but in a different order in a quick way?
For example, in your excel, when out put data to a bew sheet it follow this order:
1 ,film length ,film name, blank column, xyz...
How can this be done in a quick way?
First of all ... thank you for the video!! I have one question .... when you create the Quick Array "TopFilms = Range("A3", Range("A2").End(xlDown).End(xlToRight))" how is it possible to read out just the Action videos from the Array and write them to a new sheet, the Fantasy in another sheet, etc....??? Is there also a short solution for that?
Again ... thank you for you videos!!
Hi, on the 28th min you say that adding a pair of parentheses is absolutely necessary to set apart an array from a regular variable.
But something like this works even without adding ()
Dim R
R = Range("A1").CurrentRegion
Can you please clarify?
Haha! So should I conclude the parentheses aren't absolutely necessary? Or What?
Good evening,
Is there a way when you add items to an array to also know the cell address from whence they came? I am adding dates to an array, then I need to find the oldest date ( which I haven't figured out yet either, min() is returning a 0 for some reason it appears to be pulling a time of 12:00:00 from a date formatted cell) at any rate, when I find the oldest date, I then need the cell address of where it was pulled from to finish out my action...thoughts?
TIA
David
This is great, much simpler than what i was working on.... what if the array needed to be dynamic was added to?
Andrew, your videos are high quality and you're a very patient teacher:) This makes for an outstanding learning experience! Thank you for sharing your wisdom!!!
I also wanted to ask you a question about a Compile Error: Type Mismatch that I'm receiving. In the code below, I'm receiving this error on the first < sign in the IF statement. Would you mind assisting? Thanks for your help and let me know if you need any further information.
Sub CalculatingEndDateStatus()
Dim DaysToCloseout() As Variant
Dim Answer2() As Variant
Dim Dimension2 As Long, Counter As Long
Sheet2.Activate
DaysToCloseout = Range("G2", Range("G2").End(xlDown))
Dimension2 = UBound(DaysToCloseout, 1)
ReDim Answer2(1 To Dimension2, 1 To 2)
For Counter = 1 To Dimension2
If DaysToCloseout < 0 Then
Answer2(Counter, 1) = "Past End Date"
ElseIf DaysToCloseout < 30 Then
Answer2(Counter, 1) = "Less Than 1 Month"
ElseIf DaysToCloseout < 60 Then
Answer2(Counter, 1) = "Less Than 2 Months"
ElseIf DaysToCloseout < 90 Then
Answer2(Counter, 1) = "Less Than 3 Months"
Else
Answer2(Counter, 1) = "More Than 3 Months"
End If
Next Counter
Range("H2", Range("H2").Offset(Dimension2 - 1, 1)).Value = Answer2
Erase DaysToCloseout
Erase Answer2
End Sub
Hi, maybe you could help me with a question:
I have two different bidimensional arrays more or less like this:
arr1(1 to 800, 1 to 45)
arr2(1 to 500, 1 to 45)
I want to compare each entire line (45 columns) of each array one another... how could I do that?
Hi Andrew, thanks for the video.
How do you manage au write your variables automatically? You just start typing TopT and it seems that TopThreeFilms is automatically written
+WiseOwlTutorials Great, I didn't see the previous video, thanks for the tips ;)