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. 👏 🌟 🌟 🌟
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 UA-cam 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!
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.
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?
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
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?
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.
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.
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.
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.
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.
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 ...
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?
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.
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
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.
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
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. 👏 🌟 🌟 🌟
Thanks Salim.
Thank you.. It was nice video..
This must be some record - 3 days of VBA training squeezed into 28 minutes. Bravo!
😜
You are the KING of VBA
Thanks
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 UA-cam 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!
Thanks Robin
Hi everyone. Please enjoy the videos and add any comments you may have below😀😀
Easy to follow and understand tutorial. Thank you
AWESOME VIDEO! Perfect for finding yourself in this forest of knowledge!
Glad you like it.
God of excel VBA..🙏
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.
Thanks Lord. Glad you like the material.
Thank you so much paul.
My Employer thanks you for making me more efficient!
Great, dictionary was new for me!
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.
Great channel! Thanks!
You are a Champ, thanks and God bless you.
✌😀
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?
Awesome! I'm learning a lot. Thank you for sharing your knowledge.
You're welcome Victor
Thanks Paul.
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
Nice video to refresh and revise ur understanding... Thanks paul
You're welcome.
Well done Paul.
As always, very clear and didactic.
Thanks Joao
Hi Paul. It's amazing knowledge. Thanks for sharing.
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?
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.
Thanks Brian.
Excellent video again, thanks!
You're welcome Rob
Really enjoyed this, gave a great overview 👌🏻
Thanks Graham
Very useful for VBA novice like me, Thanks
Glad you like it Vivek.
Excelente, saludos desde Salta Argentina
Very smart and simple. Now, for me is easy to filter thousands of data. Thank you very much indeed.
Glad to help
Excellenttttt..... Thanks for the video..
Glad you like it.
Very nice as always!
Glad you like it.
Amazing Video
Thank you.
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.
Hi Paul, big fan of your channel. Do you do vba consultancy?
Hi Jonny,
I do consultancy but I am not currently available due to the number of ongoing projects.
Well done
Thanks Stephan
Great , Thanks
hey, great content
@4:11 can you send arrays from one sub/function to another in this way?
Could you teach the use of listviews instead of listboxs?
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.
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
Hi. I am getting Compile error: Variable not defined when I use sub UseRanges(). How to solve it?
Havent watched the video yet but thank you. Your videis provide me invaluable lessons in VBA.
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.
For an array you need to use lbound/unbound. For other objects 1 to Count.
It’s that simple? Thanks for the clarification
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.
It's read-only so reading from the last time the file was saved: bit.ly/2m5QLn6
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 ...
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?
It's part of the dictionary object.
Ahh. I see that you've changed the name of the sheet to shdata. That answers my question.
Why shData not error "variable not defined" sir?
But is the efficiency so much bigger working in VBA instead of working direct in the spreadsheet?
Yes. Exponentially faster if you have a large data set.
@@Excelmacromastery Thank you for the answer. This could also be taken up in your videos on youtube. You are great on youtube!
I would add 2 more areas to that:
Forms (developing apps) and classes/interfaces 😄
ShData isn't an object, at least in Office 365 version of Excel.
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.
What line is causing the error?
Can we use AND in case statement??... How do i write?? For example the below code doesn't work
case > 1500 and < 2000..
Case 1501 to 1999
or
Case Is > 1500, Is < 2000
excelmacromastery.com/vba-select-case/#Case_Statement_Quick_Guide
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
Merci
hello there ! General Kenobi !
Hit like and watch the video
Go raibh maith agat. An-úsáideach mar i gcónaí.
You're welcome.
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.
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!
.
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