The 7 Keys Areas of Excel VBA (with code examples)

[@sasavienne]

Every time I feel happy when I get an information that you uploaded a new video because I know that it is going to be very helpful and quite useful. Thanks indeed Paul. 👏 🌟 🌟 🌟

[@Excelmacromastery]

Thanks Salim.

[@rajkumarpalle8217]

Thank you.. It was nice video..

[@SolidSnake59]

This must be some record - 3 days of VBA training squeezed into 28 minutes. Bravo!

[@Excelmacromastery]

😜

[@robinhahn6987]

I've written quite a few apps in Excel VBA (probably using very kludgy code - this was over years before the great resource that is KZbin and excellent videos such as yours, Paul) and am currently in process of revising many of them, only just to simplify the code, clean up the bugs and most probably speed those apps up a bit. Of all the tutorials and videos I've read or seen, yours easily offer the most truly game-changing suggestions! Thank you!

[@Excelmacromastery]

Thanks Robin

[@rrrprogram8667]

You are the KING of VBA

[@Excelmacromastery]

Thanks

[@mxrvxn08]

Easy to follow and understand tutorial. Thank you

[@LordBransty]

You're a very gifted educator Paul. I've been programming for years in VBA, and you keep showing me all kinds of things I had no idea about. Excellent content taught in a very friendly manner.

[@Excelmacromastery]

Thanks Lord. Glad you like the material.

[@Excelmacromastery]

Hi everyone. Please enjoy the videos and add any comments you may have below😀😀

[@jlzpacheco]

AWESOME VIDEO! Perfect for finding yourself in this forest of knowledge!

[@Excelmacromastery]

Glad you like it.

[@akshayc7152]

God of excel VBA..🙏

[@clandeszipp4564]

Thank you. Sometimes I wonder what is the gain in VBA speed all over the world (and economical gain for that matter) thanks to these lessons.

[@Whatarewefixingtoday]

My Employer thanks you for making me more efficient!

[@redangrybird7564]

You are a Champ, thanks and God bless you. ✌😀

[@MrVicmanr90]

Awesome! I'm learning a lot. Thank you for sharing your knowledge.

[@Excelmacromastery]

You're welcome Victor

[@henrikijonkoping4694]

Great, dictionary was new for me!

[@hadibq]

Great channel! Thanks!

[@CuriousEngineer955]

Thank you so much paul.

[@alterchannel2501]

Thank you for the basics. I'm a bit confused, in the example you are Debug.printing the Dictionary results, but how do I transfer the results to Excel?

[@ferkik.3245]

Thank you for your video. I'm using vba for a while but learnt a lot from your video to simplyfy my code. Currentregion is a very usefull code i didnt know that realy like it. Nowdays im working with data where there are blank rows what i cant delete. Do you have any tool how to handle this type of database?

[@joaocustodio2094]

Thanks Paul.

[@rrrprogram8667]

Nice video to refresh and revise ur understanding... Thanks paul

[@Excelmacromastery]

You're welcome.

[@BrianJones_cswp]

Thanks for making these videos, they've been extremely helpful to me! Clear, concise explanations in terms I can grasp, and the pace is spot on👍,subscribed.

[@Excelmacromastery]

Thanks Brian.

[@rrrprogram8667]

Can u please give ur suggestions on how to use intellisense... Some times it doesn't work and make feel worry sometimes.. Example.. Activesheet.paste will not be shown with intellisense... I know that it has to do something with object library... Can u please make video how to use object library

[@eddai84]

Hi Paul. It's amazing knowledge. Thanks for sharing.

[@joaocustodio2094]

Well done Paul. As always, very clear and didactic.

[@Excelmacromastery]

Thanks Joao

[@grahamparker7729]

Really enjoyed this, gave a great overview 👌🏻

[@Excelmacromastery]

Thanks Graham

[@xxrobcollinsxx]

Excellent video again, thanks!

[@Excelmacromastery]

You're welcome Rob

[@sebastiansalazar3553]

Very smart and simple. Now, for me is easy to filter thousands of data. Thank you very much indeed.

[@Excelmacromastery]

Glad to help

[@Funintherain13]

Hi Paul, big fan of your channel. Do you do vba consultancy?

[@Excelmacromastery]

Hi Jonny, I do consultancy but I am not currently available due to the number of ongoing projects.

[@vivekphadke16]

Very useful for VBA novice like me, Thanks

[@Excelmacromastery]

Glad you like it Vivek.

[@guss372]

Excelente, saludos desde Salta Argentina

[@meldeebueno]

Havent watched the video yet but thank you. Your videis provide me invaluable lessons in VBA.

[@grigull]

Could you teach the use of listviews instead of listboxs?

[@rrrprogram8667]

Excellenttttt..... Thanks for the video..

[@Excelmacromastery]

Glad you like it.

[@thearchibaldtuttle]

Very nice as always!

[@Excelmacromastery]

Glad you like it.

[@Shady_Lane]

hey, great content @4:11 can you send arrays from one sub/function to another in this way?

[@subhramanyemdudey2941]

Amazing Video

[@Excelmacromastery]

Thank you.

[@landor703]

Great video - as always. I do have a question in the area of going through a range using an array vs a dictionary. You used a different technique for each. For array, you used " dim arr as variant" , got the array and went through it from LBound to UBound. For dictionary, you used "dim rg as range" and went through it from 1 to rg.rows.count. Why was there a difference? Could I have used one technique in both cases? Or do I have to use one for array and one for dictionary? Thanks again.

[@Excelmacromastery]

For an array you need to use lbound/unbound. For other objects 1 to Count.

[@landor703]

It’s that simple? Thanks for the clarification

[@paulkarch3318]

At 10:56 when accessing a different workbook I got a little nervous but I guess the condition locks you out if someone is changing data in the external workbook.

[@Excelmacromastery]

It's read-only so reading from the last time the file was saved: bit.ly/2m5QLn6

[@stephanweaver1960]

Well done

[@Excelmacromastery]

Thanks Stephan

[@PrincePedia]

Great , Thanks

[@andywong6025]

Dear Sir, for the dictionary part, if the value (amount) of the key (Fruit) is zero in sum, will it be shown in the dictionary? i just wanna be using the dictionary for my inventory record, sometimes it may be zero in number.

[@vdpeer]

Ahh. I see that you've changed the name of the sheet to shdata. That answers my question.

[@chrstfer2452]

Is there a reason you use Long for the row in your dynamic ranges example? Naively I'd think that should be an integer; like, what would happen if you gave it a fractional value? I'm totally new to excel vba, so please forgive me if excel or vba does something I don't know about which makes this the best practice.

[@Excelmacromastery]

Because the integer range is -32,768 to 32,767 which is low. The Long range is -2,147,483,648 to 2,147,483,647

[@alexandrumarcel3696]

Except the dictionary part I can say I know the other ones, but still I m afraid to apply for a vba developer job because I don t know which are the expectations ...

[@sebastian1200]

I would add 2 more areas to that: Forms (developing apps) and classes/interfaces 😄

[@Snafuey]

Question about the dictionary. The for each loop at the end you say currentKey in dict.Keys. The currentKey make sense cause you declared it as a variant. Where did the .Keys plural come from?

[@Excelmacromastery]

It's part of the dictionary object.

[@bassisessaidexcel]

Merci

[@АнтуанДеляМоль]

hello there ! General Kenobi !

[@vdpeer]

ShData isn't an object, at least in Office 365 version of Excel.

[@henrikijonkoping4694]

But is the efficiency so much bigger working in VBA instead of working direct in the spreadsheet?

[@Excelmacromastery]

Yes. Exponentially faster if you have a large data set.

[@henrikijonkoping4694]

@@Excelmacromastery Thank you for the answer. This could also be taken up in your videos on youtube. You are great on youtube!

[@mrsachu88]

Hi. I am getting Compile error: Variable not defined when I use sub UseRanges(). How to solve it?

[@SAMSITARAGUYANG]

Why shData not error "variable not defined" sir?

[@rrrprogram8667]

Can we use AND in case statement??... How do i write?? For example the below code doesn't work case > 1500 and < 2000..

[@Excelmacromastery]

Case 1501 to 1999 or Case Is > 1500, Is < 2000 excelmacromastery.com/vba-select-case/#Case_Statement_Quick_Guide

[@rrrprogram8667]

Hit like and watch the video

[@johnphillips6851]

The example of the Ranges doesn't work and gives me the Run Time Error 424 and I have meticulously verified the code I typed to what is in the video. does anyone know why this would be? I'm currently on Office Home and Student 2016. Thanks in Advance.

[@Excelmacromastery]

What line is causing the error?

[@basemsat4601]

Please , i have this problem, my code for i = 1 to 100 if worksheets(“sheet1”).cells(i,1).value = i ----this dont work when i change i to 1 it works like this if worksheets(“sheet1”).cells(i,1).value = 1----work i try convert the cell covert the every value no way , thanks

[@bondniko]

Go raibh maith agat. An-úsáideach mar i gcónaí.

[@Excelmacromastery]

You're welcome.

[@tughanozsezer9332]

.

[@Molnify]

Awsome video! If you want to, check out my channel where I show how you can turn Excel files into web apps without coding or extra software!

[@Morphineck]

I really like your videos and respect your work... however, that has got to be the worst ever example of what a function is and how to use it. Bro, the main reason you use functions is to be able to include them as part of formulas (i.e. a text formula that includes regular expresions) or to consult data from external DB's (a function that gets a Part Number from a DB of vendors). All without the need to include buttons or to map them on your interface, you just type = then start typing however you named your function and voila.

[@rrrprogram4704]

Can u please give ur suggestions on how to use intellisense... Some times it doesn't work and make feel worry sometimes.. Example.. Activesheet.paste will not be shown with intellisense... I know that it has to do something with object library... Can u please make video how to use object library