How to Use Arrays Instead of Ranges in Excel VBA
HTML-код
- Опубликовано: 8 июл 2024
- Learn how to write real-world Excel VBA code: 👉courses.excelmacromastery.com/
FREE CHEAT SHEET: Get the free cheat sheet on VBA arrays here: (bit.ly/2MXsnz9)
Subscribe to the channel here: bit.ly/36hpTCY
How to Use Arrays Instead of Ranges in Excel VBA
Most people don't realise that Arrays are exponentially faster than Ranges in Excel VBA.
In this video I cover in detail how to replace ranges with arrays and make your code run faster.
Useful VBA Shortcut Keys:
View the Immediate Window: Ctrl + G
View the Watch Window: Alt + V + H
Tab: To move lines of code to the right(Indent).
Shift + Tab: To move lines of code to the left(Outdent).
Shift + F2: Get the definition of the item under the cursor.
Ctrl + Shift + F2: Go to the last cursor position.
Ctrl + Space: AutoComplete Word.
Alt + F11: Switch between Excel and the VBA Editor.
Ctrl + G: view the Immediate Window
Ctrl + R: View the Project Explorer Window.
Ctrl + Shift + 8(or Ctrl + *): Get the current region on a worksheet.
F4: View the Properties Window.
F5: Run the code from the current sub.
F8: Step into the code.
F9(or click left margin): Add a breakpoint to pause the code.
Learn how to write real-world Excel VBA code: 👉courses.excelmacromastery.com/
This is exactly what I am looking for. I work in telecom industry. It took me hours to process raw data.. now .. down to less than 20 mins with a humble system. Thank you .. where have you been 10 years ago ? I can't thank you enough .
Thank you Paul for all your content!!!
Dictionaries, arrays, collections and classes are the best.
Best vba channel i ever found !
X2
Dear Kelly Thank you and I want to learn more about Excel VBA
Magnificent.... I have very complex range operations which take my program 281 seconds to perform. With this method I reduced the time to under 5%.... Best ever YT content I've encountered. Keep doing, don't stop. Ever! It has the potential to be great...
I am self-taught and I didn't fully grasp this content the first couple of times I looked at it. I decided I needed to better understand it and it now makes sense.
Great job Paul. Love using arrays now. So much more efficient.
Absolutely fabulous instruction. Seeing the array filled with one line of code and then watching it crunch 2500 records in a blink, was the most fun I've had in a while... sad, but still fun! Thank you Paul.
Glad you liked it Michael.
From someone putzing around with VBA for a few years, finding R Studio was a godsend. Perhaps there are special instances where you might have to use Excel, but I'd advise anyone who thinks they should spend time learning to code VBA to give R a try.
Amazing!! I watched tons of vba videos (I'm newbie) and by far this video gives a incredible edge because of its clear and concise information. Excellent!! Great work, please keep it up!!
Absolutely a clear and concise explanation of using arrays. Well done! You gained another subscriber.
Another really 1st class video, really simple and robust code that covers so many practical situations
Incredible how efficient you codes are, yet explain it so simple. Genius!
From Algeria, thank you Paul for this vidéo.
Thanks a lot! A very clear explanation that addressed exactly what I was looking for. You're a great teacher!
Wow...i now really appreciate arrays. Very efficient compared to ranges. Thanks Paul
Where have you been all my life? This is awesome!
I like the format of your videos and the way you explain things. Even if I know some of them, I still watch just because you're one of the very few on youtube that is actually enjoyable to listen to.
Thanks
Amazing tutoring as always! Keep these videos coming!! They're making a world of a difference to my (currently limited) VBA skills.
I had a thought, would you consider doing a video on how to use VBA generically? Like copy+paste files across different folders, or using VBA to send a standard email message? Not pushing for this but thought it could be interesting. Thanks!!
The information you give us is so clean, easy-to-understand and implement… I just love your channel, Im hoping to buy the handbook soon
Thanks Gonza
Clear and concise, I love it!!!
I have done some of this before, but not the resize aspect. Great tips, thanks Paul.
as always all your videos are amaizing and greats!
This video got me up a high wall...by making my code cut thru a dead end(error) and in a fast way...Genius!
You sir, are next level. AMAZING!!!
Thanks Man! you and Leila are the best in excel tips!
Absolutely loved this tutorial! Thank you sir!
Best Video on the Interweb , Internet & Galaxy !! Star Paul !! Your an Amazing Star !!
Amazing explanation! Congrats!
Great VBA teacher, I've tried various different tutorials and keep coming back here!
Thanks for your support my videos Andrew
Excellent video.I can see the value of arrays, as opposed to ranges, and am in the process of changing one of my user forms.However, what the video does not cover and I am interested in is the following:Placing the data from the user form back into the table/spreadsheet.
Thanks for uploading this Paul.
I stumbled upon something quite interesting when playing around with this. If you try to set an array, it will return the properties of each cell in a range instead of the value of the cells in the range. Very useful to get a number of properties information
If you use Set with a variant to a range it will return the range rather than the array.
Great video !! Thanks for sharing with us !
Thaks!!! Have just changed the code in one of my macros to use arrays. I go through 200k lines in a table. Before, it took 3m20s, now it finishes in 12s, 16x faster!
That's great to hear.
Good stuff.... taking baby steps with my VBA project but every step is in the right direction with these videos!!... (oh and of course I have liked this video!)..
Thank you Paul a brilliant video again,
Awesome work!
Thank you so much. Great video. I am learning a lot. Very useful tips.
Has to be the best vba tutorial I've seen on RUclips. Lots of garbage stuff out there. I'm a self taught VBA user, picked up a few very helpful codes and ideas watching this. Thank you
Glad you liked it.
Thank you. Great video.
Thank you so much! This is great.
Learning a lot. Thanks!!
설명감사합니다
You helped so much, thank you!
Mindblowing 💯💯
Mr Kelly, you are a champion 🏆 😎👍
Great tips, thanks!
superb sir
Great work Sir, you real world examples are so fascinating. I have a question to in the same situation.
Awesome paul.... U r the master of vba... Great to have u as a teacher
Glad you like it.
Great video ! Thanks !!!
Great stuff Paul, big fan From Florida USA....
Thanks a lot, this is awesome.
Thank you so much for the very useful tips using arrays in vba!! :)
BEST VBA ADVICE EVER!!!! Wow, you are awesome :-)
Glad you think so!
Amazing teacher, each video I watch I learn a new trick. Thanks a lot
Thanks for your comment George
Excellent
20 Minutes to 33 seconds!!! Thank you very much.
Always great stuff, very well presented
Thanks Gary
Mille merci Mr
Malgré que je suis très faible en anglais mais j'essaye le maximum de vous suivre car je sais bien que votre vidéo est très utile
mahdi zahzah je vous applaudis d’avoir pris le temps pour comprendre ce qui n’est évidement pas facile à apprendre surtout en une autre langue. Continuez de faire de votre mieux et je vous assure que vous réussirez!
Thank you 💜
Very nice explanation..... keep it up ....
You're a beast. Your content is the best.
Great,thanx
This guy is a frickin' genius.
you are a G.O.A.T. probably also take us through append
very useful
This is a fascinating topic. I think I can adapt this to copying non-contiguous ranges from one workbook to another, but I'm not quite sure.
You can @ Eduardo Chavarria
Many thanks, excellent and well explained, it is crystal clear, your video helped me a lot.
Glad you liked it Adam.
Fatastic video. Thanks
Paul, Thank you for great instruction. Is it possible if I do samething with a recordset pulled out of query?
amazing video, thanks, you are my best vba mastery legend.
Wow, thanks
Thank you for the video :) It was helpful for me.
Glad it helped!
thank you very much!
EXCELLENT SUPER
A million thank you, could you please suggest if we substarct same value what you have mention but I want to add column in the end and calculation value mention . Can we do that?
Thank you Paul! I was wondering if it is possible to get other cell attributes (like cell background color) and not only cell value into an array ( without iterating through the entire range)? I have a very large range which I need to get the background colors of each cell, and it takea very long to run...
Awesome!
Awesome content. Keep sharing useful tips and tricks.
It is much helpful for developers life. :) :)
Always loved watching your videos.
Just a quick question.
How can I adjust this to lets say I want Columns A, C to E.
Really awsem, never seen such effective and shorter code in my life, again thanks for creating this video, love you, really helped me a lot
You're welcome!
Thankyou so much for this tutorial, really helped me a lot! and saved my time.. thanks a lot yet again!! :))
this is on fire guys...the arrays functions can makes us our projects much easier and fastest....i am also trying to learning this arrays functions codings,....can someone pls explain me why the sir has used i,1 and i,5 in 6:43 minutes
i want to use count of columns so what would be just basic formula for counting columns in arrays.
Thanks a lot!!!))
Hello. Wonderful lecture! Thanks.
My question: is there a way by which I store my database table in an array when my workbook opens AND use/call the array at different times wherever required??
Excellent video.!!!!!!!!!!!!!
Thank you very much!
What a legend
Great
Nice
Why does this have only 69k views? It should be on the top of the recommendations on RUclips!
Great video as usual. Quick one. I've been coding VBA for approx 5 years and consider myself a moderate to advanced user. One thing I encounter when setting ranges and pasting arrays back it pastes values and I loose formulas. Is there a method of inputting formulas into the array when it is pasting back out?
Yooo this is sick
hi thanks for your videos... is it possible to redim preserve arr(1 to x,1) when arr is a variant ? thanks
Thank you very much. I have a question: what is the best way to delete a row of data from array without changing the range
Loving your videos. A lot of my data is in word documents and I want to use VBA to move it to excel. Is there a similar way to copy a word table into an array without looping through all cells or using a copy/paste command?
Wau, it is very very good 👍😲
Really awesome! Never seen such effective and shorter code in my life, again thanks for creating this video, love you, really helped me a lot!!
Wow, thanks 😊😊
You're welcome!
Awesome! Can we access to the cell properties and methods within the array?