VBA Arrays Explained in 3 Minutes
ฝัง
- เผยแพร่เมื่อ 14 ก.ค. 2024
- 👉 Ready to master VBA?
- Check out my full courses: courses.excelmacromastery.com/
- Subscribe to the channel here: bit.ly/36hpTCY
-Want to download the source code for this video? Go here: bit.ly/3wEoZiM
(Note: If the download page doesn't work then make sure to turn off any popup blockers)
VBA Arrays in 3 Minutes
This video provides a complete explanation of arrays in VBA. It covers, using dim and redim to declare the array.
using erase to delete items or the array, copying from a worksheet range to an array, array functions such as
split, join, filter,lbound and ubound etc.
#vbaarray #excelvbaarray #vbarray
Useful VBA Shortcut Keys
========================
Debugging:
Compile the code: Alt + D + L OR Alt + D + Enter
Run the code from the current sub: F5
Step into the code line by line: F8
Add a breakpoint to pause the code: F9(or click left margin)
Windows:
View the Immediate Window: Ctrl + G
View the Watch Window: Alt + V + H
View the Properties Window: F4
Switch between Excel and the VBA Editor: Alt + F11
View the Project Explorer Window: Ctrl + R
Writing Code:
Search keyword under cursor: Ctrl + F3
Search the word last searched for: F3
Auto complete word: Ctrl + Space
Get the definition of the item under the cursor: Shift + F2
Go to the last cursor position: Ctrl + Shift + F2
Get the current region on a worksheet: Ctrl + Shift + 8(or Ctrl + *)
To move lines of code to the right(Indent): Tab
To move lines of code to the left(Outdent): Shift + Tab
Delete a Line: Ctrl + Y(note: this clears the clipboard) - วิทยาศาสตร์และเทคโนโลยี
Make sure to download the complete source code for arrays in the description.
Thank you. I have been working for above 10 years on XL, just watched it and loved it. And glad to subscribed your channel :)
Thanks
He is one of the best VBA instructors I have ever encountered on the net.
Thank you, Paul. Although I work with VBA daily (I'm at a stage where it flows from my fingers and I rarely have to google anything). But I STILL find your videos insightful and interesting! Thank you for making and sharing them
Thanks David. Glad you like them.
What do you you, David? Im interested in what it is one Can do for a living that makes one still use Vba. I love it, But unfortunately people say its a dead language :(.
@@7Denial7 Yes, I didn't want to be an expert in VBA! But when I started working in Nissan's European R&D centre, I was automating processes but I was located in the business (not in IT), so IT wouldn't give me a web-server (I've got one now, but for 10+ years I didn't have one). So I used Excel as a browser, connecting to an SQL Server database and creating a personalised project dashboard, so that if you're working on the new Qashqai (say), it will take all of the disparate project data (part design schedule, supplier test schedule, in-house test schedule, cost data etc) and present it to you, so you can answer the question: "What do I have to focus on today?". I also use it create a searchable, dynamic Org Chart so you can find expertise in the business. And I also create forms that automate business processes. Things like that.
@@DavidRymell thank you for such a comprehensive answer! Do your colleges use Vba as well, or are you the only one?
@@7Denial7 I have one other colleague who also uses VBA to automate processes for the business. There is another person in a different department who also uses VBA although he has made more progress than me in transitioning to a web server.
I've been having a lot of troubles finding how to use arrays in VBA. I had errors and the syntax I found dind't work. But this video helped me a lot. I wish I found it sooner, thank you man
Ever since I have started watching your video, I have been trying to implement what you have taught me. I have struggled a bit with the arrays and learned a lot, but I think with this new video, I understand arrays much better and I can get "arrays" at work.
Glad you found it useful. Arrays are a tricky area in VBA.
Amazing overview of one of the basic elements that make up VBA. I did not know about half of the items covered here. Thank you so much for this great video!
No problem Kari.
عمل جيد، شرح مختصر وشامل 👍
👏❤️
Clean instruction for newbes! Great job Paul!👍👍👍😉🤟😎
Thanks John
Love it, thx!
Perfect resource to help with arrays when coding, broad, concise & detailed. (Tho would recommend several of the previous videos if you’ve never used an array before)
Thanks John.
Thank you thank you thank you! Didn't know the Join command. No more For loops to put the array back together again after a split! This will make my code so much cleaner! :)
Glad you liked it. You can see more about split here: excelmacromastery.com/vba-split/
Amazing lecture in under 4 minutes. Thank you.
You're welcome.
At 0:22 be sure NOT to loop through ranges with this code: range("A" & i)
This causes VBA to create strings at runtime and joining them together, which costs memory and slows down your code.
Rather you should use: range("A1"). offset(i)
Thank u Paul for this video i would be great if u could show us more examples in your Upcoming videos
I'm an arborist and I like yours VBA content 😄
Thanks. I think 😄
Excellent video.
Thanks Andre
I thought this is in response to my prior question whether array is case sensitive in comparison! 😆 But it seemed that wasn't mentioned here also!
Excellent video
Thanks
Thank you Paul. I have an Excel Table and I want to show or hide columns in the table dependent on the value in Column 1 in the selected row. This is dreadfully slow in operation - could this be made more efficient using arrays? Thanks
Hallo
You are incredible ❤️ person
I want to ask you if I have serach with multiple criteria is possible arrays help me .
I think if i fill one dim array with 2dim array or make multiple dim array will help but i don't know how.
Thank you for the video.
I still have a question, how can I import and save data to listbox from an external .csv file?
I have a few questions. how can I reach you.
Thank you Mr. Paul,
1- Can the Excel add-in be programmed in another programming language such as C#, C ++ , Python or only through VBA?
2- Can we replace VBA in Excel with C#? If it is impossible, why not?
i use excel with python but only from another IDE
@@lonnieo4676 Why Python and not VBA?
@@hammeedabdo.82 python has more tools when it comes to calculations...
@@lonnieo4676 whate ist the another IDE?
@@hammeedabdo.82 well, i'm not a pro,,, at all,,, lol... i just use pycharm from jetbrains...
Hi ,
Sub GoTo_Example1()
Application.Goto Reference:=Worksheets("Jan").Range("C5"),Scroll:=True
End Sub
This is VBA code which i use to go to a cell , but i want to use this code in VBA code for a automation tool , where the range value varies for every sheet. So for that , i have a range value in a cell , which i want to go, so can u please help me out , how the Above code should be used to take the range from a cell (which has range value, which changes from sheet to sheet). Thanks