VBA Arrays Explained in 3 Minutes

Рет қаралды 31,970

Күн бұрын

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
SUBSCRIBE TO THE CHANNEL: bit.ly/36hpTCY
DOWNLOAD THE SOURCE CODE FOR THIS VIDEO: bit.ly/3wEoZiM
Related Training
The Excel VBA Handbook Course(TheExcelVBAHandbook.com)
Webinar Archives - 60+ Hours of VBA training(excelmacromastery.com/excel-v...)
Free Excel VBA Resources
Excel VBA Articles (excelmacromastery.com/vba-art...)
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)

Пікірлер: 48

@Excelmacromastery 2 жыл бұрын

Make sure to download the complete source code for arrays in the description.

@DeepakJaiswal-pg8zg 2 жыл бұрын

Thank you. I have been working for above 10 years on XL, just watched it and loved it. And glad to subscribed your channel :)

@songueraphael7366 2 жыл бұрын

Thanks

@TheChandra83 2 жыл бұрын

He is one of the best VBA instructors I have ever encountered on the net.

@DavidRymell 2 жыл бұрын

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

@Excelmacromastery 2 жыл бұрын

Thanks David. Glad you like them.

@7Denial7 2 жыл бұрын

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 :(.

@DavidRymell 2 жыл бұрын

@@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.

@7Denial7 2 жыл бұрын

@@DavidRymell thank you for such a comprehensive answer! Do your colleges use Vba as well, or are you the only one?

@DavidRymell 2 жыл бұрын

@@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.

@Cc-vu9mr Жыл бұрын

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

@bredmond812 2 жыл бұрын

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.

@Excelmacromastery 2 жыл бұрын

Glad you found it useful. Arrays are a tricky area in VBA.

@free3690 2 жыл бұрын

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!

@Excelmacromastery 2 жыл бұрын

No problem Kari.

@softly001 2 жыл бұрын

عمل جيد، شرح مختصر وشامل 👍 👏❤️

@johnwayne8059 2 жыл бұрын

Clean instruction for newbes! Great job Paul!👍👍👍😉🤟😎

@Excelmacromastery 2 жыл бұрын

Thanks John

@TheGentleTotoro Жыл бұрын

Love it, thx!

@johnbutler2750 2 жыл бұрын

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)

@Excelmacromastery 2 жыл бұрын

Thanks John.

@two_stones 2 жыл бұрын

Amazing lecture in under 4 minutes. Thank you.

@Excelmacromastery 2 жыл бұрын

You're welcome.

@nadermounir8228 2 жыл бұрын

Thank u Paul for this video i would be great if u could show us more examples in your Upcoming videos

@jenshaglof8180 2 жыл бұрын

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! :)

@Excelmacromastery 2 жыл бұрын

Glad you liked it. You can see more about split here: excelmacromastery.com/vba-split/

@joedough5546 2 жыл бұрын

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

@kingpin11111 2 жыл бұрын

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)

@anssimakela 2 жыл бұрын

I'm an arborist and I like yours VBA content 😄

@Excelmacromastery 2 жыл бұрын

Thanks. I think 😄

@vbaclasses3553 2 жыл бұрын

Excellent video.

@Excelmacromastery 2 жыл бұрын

Thanks Andre

@TheManiek25 2 жыл бұрын

Thank you for the video. I still have a question, how can I import and save data to listbox from an external .csv file?

@Pedritox0953 2 жыл бұрын

Excellent video

@Excelmacromastery 2 жыл бұрын

Thanks

@CollDott 2 жыл бұрын

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!

@ahmedfreelancer3010 Жыл бұрын

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.

@nirjasharma6886 2 жыл бұрын

I have a few questions. how can I reach you.

@hammeedabdo.82 2 жыл бұрын

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?

@lonnieo4676 Жыл бұрын

i use excel with python but only from another IDE

@hammeedabdo.82 Жыл бұрын

@@lonnieo4676 Why Python and not VBA?

@lonnieo4676 Жыл бұрын

@@hammeedabdo.82 python has more tools when it comes to calculations...

@hammeedabdo.82 Жыл бұрын

@@lonnieo4676 whate ist the another IDE?

@lonnieo4676 Жыл бұрын

@@hammeedabdo.82 well, i'm not a pro,,, at all,,, lol... i just use pycharm from jetbrains...

@vividdreamer5025 2 жыл бұрын

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