It serves no one for a programmer to take shortcuts in naming and other tasks to save typing. very descriptive names are essential to good coding. It is also essential to use subroutines to compartmentalize the code for simplicity in making changes and separating concerns. It is good to see someone make a video like this for VBA as I get the feeling these things are taken lightly by VBA programmers.
Been VBAing for 30years did not know about Ctrl+space autocomplete worked on a blank line thanks heaps! Have watched many of your videos and have implemented many changes to speed my code up thanks! Will watch all your videos now!
Hi Paul.. these are 7 super tips.. really helpful and will implement them right away.. especially declaring variables where I use them and giving variables friendly names. When first learning, it seems correct to mimic what we see others doing.. so.. bad habits get passed through the generations. Thanks for this great advice to chart my own course and develop my own style. Thumbs up!!
Hi everyone, Enjoy your watching and please leave your comments below. Don't forget to press SUBSCRIBE my channel for more videos. Thanks for your support.
Lover your videos. Quick question, when you click it makes little circles. Is that done in post processing or do you have a program that does that in real time?
Every vid from you is nice, thank you. I would not delcare variables when you need them, but declare them when you are at the top of your sub or Function. this makes it much easier to transition to another programming language where defining the variables at the beginning is mandatory.
You can get a speed up in code execution by using full referencing as well (e.g. .shData.Range or VBA.Instr). I would also suggest having a unique name for all you VBA projects rather then using the default project name "VBAProject" in order to reduce errors if multiple projects are running at the same time.
Great tutorial, thank you. I have a question regarding code behind userforms. I have Textbox, ListBox, ComboBox...with Click events, Functions, BeforeUpdate event, AfterUpdate event and Change event, the code is a mile long and disorganized. Is it possible separate, organize the code by Click event, Function, Update event in regular modules?
Your suggestion is pretty good practice of variable declaration for VBA because of its interpreter nature. But in compiling languages, declaration must be predefined before any use.
Thanks, now I understand why enum is better than constants in this special case! 🙂 A question: do you think 'With-End Width' helps readability? I use it very frequently, but it's so easy to overuse it. How about the readability of if, select case and iif? I would love to watch a video about this 🙂
Good stuff Paul, although the variable dimming in the body vs top of the procedure would be at the Brexit level of contentiousness within our profession.
I agree with your reasoning, but I don't use your execution regarding the declaration of variables. I use the top of the module to declare mine, grouping strings onto one or more lines, as well as other variable types. I also use the LNC, or a modified version of it, to add the type of variable to the front of the name and then supply a descriptive name. For example: Const cstrErrTitle as String = "ERROR MESSAGE TITLE HERE" Private wsMacro as Worksheet, wsSource as Worksheet, wsDest as Worksheet Private strName as String, strAddress1 as String Private lngCount as Long I do this instead of using Dim within each Sub, mostly because I want the value to travel as it's being used, rather than using it only within the Sub and then disposing of it. While I understand the purpose of declaring immediately before use, and it makes it easier to share the code, it also clutters up the code and restricts using the variable's value to that Sub only. I'm sure we won't see eye-to-eye on this, and I don't feel like you're teaching anything that needs to be corrected. It's just that the style of coding that I use is different.
The use of I, J etc for loops is a remnant of Fortran where all variables starting with I to N are integers and the rest are floating point (unless explicitly declared). For a loop it would be done an exact number of times so integers would be used for the counter - if a floating point variable was used due to rounding the loop may be executed an extra time.
In my case, first variable use in for-next loop if always "F" because in ZX81 / ZX spectrum it was same key: first push F to type "For" directly and second push for variable = F is more quickly.
1. You can group related constants using Enum. 2. You can use the enum as a type once you have created it. e.g. set it as a parameter type. 3. Intellisense shows all the available options when you use the enum in your code.
I really love your videos. I always considered myself a pro in VBA, but after seeing some videos of you I'm not that sure any more lol. However, sometimes I need to make trainingvideos myself for coworkers, what is the software you use for those red boxes to point out certain parts in the screen? I use Camtasia but I haven't found this feature.. Have a great day
I avoid using I and L in code and in excel they look to much like each other and 1 in certain fonts. Test which the lowercase L or I in text I l ? even google font is hard to tell. For for loops I use XA XB XC etc
I did not know that there was a checkbox in the settings to insist that all variables be declared. The first thing I have done for years, before starting a new module is type "OPTION EXPLICIT". I don't even understand why you are allowed to not declare variables! Shakes fist at Microsoft.
I prefer to declare the variables on the top of the function/sub. I think it is easier to find them. I am used to start all my variables that have the same type with the same letter (s for the string, a for the arrays, l for long...) For me, it is a good way to identify the king of variable without looking for the statement.
Great video, as always. I disagree, respectfully, with the practice of declaring variables immediately before they're used because in some cases it will be necessary to create more variables than would actually be needed if the declarations were centralized at the top of the method. Below is an admittedly contrived example that illustrates why one cannot always declare variables immediately before they're used. =============================================================== Public Sub TestCode() 'Purpose: If current weekday is Monday, print weekday names 'excluding Monday, in ascending order by weekday number 'If current weekday is NOT Monday, print weekday names 'in DESCENDING order by weekday number 'Cannot always declare variables immediately before 'they're used Dim blnIsMonday As Boolean blnIsMonday = (Weekday(Date) = vbMonday) If blnIsMonday Then Dim i As Integer For i = vbSunday To vbSaturday If i vbMonday Then Debug.Print WeekdayName(i) Next i Else 'Dim i As Integer '
Thanks for your comment. If you need extra variables then you code needs to be rewritten. In your example, the 2 for loops are not necessary. You can write the code so only one is required by using a variable for the start, end and step values. Then you only need one For loop no matter how many conditions.
my coworkers will have to thank you :)
Glad you like it😀
Enum - learn something new every time I watch your videos. Thanks, Paul!
You're welcome
Paul, thank u very much for ur videos, they are so super useful. i have learned a lot from u.
I didn't know about Enum, this can really make my code much more readable. Thank you so much for sharing these best practices!
Glad you like it Mikhail
Why? What is the profit of Enum?
@@walerij docs.microsoft.com/de-de/office/vba/language/reference/user-interface-help/enum-statement
Yes nice tricks Enum
I was able to learn to code by myself but thanking for teaching me best practices. Deeply appreciate it.
You're welcome Nelson.
Sir, one of the best Excel youtube channels.
Continuously watching your video... Great topics and useful in vba.
Many thanks, it really helps! Appreciate your anonymous effort.
Thank you for teaching the developers, these are very important principles.
It serves no one for a programmer to take shortcuts in naming and other tasks to save typing. very descriptive names are essential to good coding. It is also essential to use subroutines to compartmentalize the code for simplicity in making changes and separating concerns.
It is good to see someone make a video like this for VBA as I get the feeling these things are taken lightly by VBA programmers.
6:41. Magic numbers, leading into demonstration of ENUMS.
Thank you for your work
Been VBAing for 30years did not know about Ctrl+space autocomplete worked on a blank line thanks heaps!
Have watched many of your videos and have implemented many changes to speed my code up thanks!
Will watch all your videos now!
Good stuff! Putting the dim statements within the code rather than at the top was new to me.
Very useful advices, thanks.
You're welcome
Thank you, your tips are really helpful!
You're welcome!
Great 💯👍
Just found your channel...loving it! I’ve been a VBA Developer for about 7 years and I learn something from every one of your videos!
Thanks Richard
Its very good videos. Simple and clear
Thanks Teguh
Hi Paul.. these are 7 super tips.. really helpful and will implement them right away.. especially declaring variables where I use them and giving variables friendly names. When first learning, it seems correct to mimic what we see others doing.. so.. bad habits get passed through the generations. Thanks for this great advice to chart my own course and develop my own style. Thumbs up!!
Thanks very much Wayne. It's true what you say about bad habits being passed.
Very informative. Thanks
It is valuable knowledge. So useful and so practical. Thanks a lot.
You're welcome Truc
I really have learned much from watching your videos.
That's great to hear Jim.
Just amazing. Thank you for your videos and page.
You're always awesome. Very useful video
Thanks Daniel
Hi everyone,
Enjoy your watching and please leave your comments below. Don't forget to press SUBSCRIBE my channel for more videos.
Thanks for your support.
Very useful, many thanks 👌🏻
Awesome, Paul, like all the time
A different color for parentheses and array values helps a lot :)
I prefer to use i,o,p for nested loops. j looks too much like i and i,o,p are right next to each other.
Great video.
I don’t like i and j either. I use k and l.
Thanks for this
very informative!
Beautiful... Loving this channel
Thanks
Excelente tips, thank you!
Thank You
At 8:52 when using variables instead of cell references in a For/Next loop, should they be re-set back to 0 before iterating to the next i?
I don't use anymore "i" or "j" as a variable. I prefer to use "For Row = 1 to NbRows", Or "For Col = 1 to ..."
It is more clear.
Lover your videos. Quick question, when you click it makes little circles. Is that done in post processing or do you have a program that does that in real time?
Still exploring what VBA can offer and how to use it, but deffinitly this makes the jurney easier :)
Every vid from you is nice, thank you.
I would not delcare variables when you need them, but declare them when you are at the top of your sub or Function. this makes it much easier to transition to another programming language where defining the variables at the beginning is mandatory.
In most modern languages it's not mandatory😀.
You can get a speed up in code execution by using full referencing as well (e.g. .shData.Range or VBA.Instr). I would also suggest having a unique name for all you VBA projects rather then using the default project name "VBAProject" in order to reduce errors if multiple projects are running at the same time.
Great tutorial, thank you. I have a question regarding code behind userforms. I have Textbox, ListBox, ComboBox...with Click events, Functions, BeforeUpdate event, AfterUpdate event and Change event, the code is a mile long and disorganized. Is it possible separate, organize the code by Click event, Function, Update event in regular modules?
Very simple and powerful tips, thank you. Which software you use to record your tutorials?
Camtasia
Your suggestion is pretty good practice of variable declaration for VBA because of its interpreter nature. But in compiling languages, declaration must be predefined before any use.
I'm not sure what you mean. You can define variables as you use them in C# and C++.
Great
People sometimes underestimate the power of a macro...
Thanks Master!
Thanks for your vids so interesting and useful !
If I dared, I'd suggest you an eighth: comments. It's so obvious but so necessary.
Also make video on power bi
Thanks, now I understand why enum is better than constants in this special case! 🙂 A question: do you think 'With-End Width' helps readability? I use it very frequently, but it's so easy to overuse it. How about the readability of if, select case and iif? I would love to watch a video about this 🙂
Good stuff Paul, although the variable dimming in the body vs top of the procedure would be at the Brexit level of contentiousness within our profession.
Appears so😂.
I agree with your reasoning, but I don't use your execution regarding the declaration of variables. I use the top of the module to declare mine, grouping strings onto one or more lines, as well as other variable types. I also use the LNC, or a modified version of it, to add the type of variable to the front of the name and then supply a descriptive name.
For example:
Const cstrErrTitle as String = "ERROR MESSAGE TITLE HERE"
Private wsMacro as Worksheet, wsSource as Worksheet, wsDest as Worksheet
Private strName as String, strAddress1 as String
Private lngCount as Long
I do this instead of using Dim within each Sub, mostly because I want the value to travel as it's being used, rather than using it only within the Sub and then disposing of it.
While I understand the purpose of declaring immediately before use, and it makes it easier to share the code, it also clutters up the code and restricts using the variable's value to that Sub only.
I'm sure we won't see eye-to-eye on this, and I don't feel like you're teaching anything that needs to be corrected. It's just that the style of coding that I use is different.
The use of I, J etc for loops is a remnant of Fortran where all variables starting with I to N are integers and the rest are floating point (unless explicitly declared). For a loop it would be done an exact number of times so integers would be used for the counter - if a floating point variable was used due to rounding the loop may be executed an extra time.
I always thought "I" just stands for "index"...
Thanks for the info😀
In my case, first variable use in for-next loop if always "F" because in ZX81 / ZX spectrum it was same key: first push F to type "For" directly and second push for variable = F is more quickly.
That's a good reason. My spectrum was the +2 with a whopping 128k .
Thanks a lot for the awesome hints
Thank you Yasser.
Hi Paul,
Thank you for great advises! Anyway, I wonder, why Enum is better than Constants? I used constants so far. Can you please comment?
1. You can group related constants using Enum.
2. You can use the enum as a type once you have created it. e.g. set it as a parameter type.
3. Intellisense shows all the available options when you use the enum in your code.
I really love your videos. I always considered myself a pro in VBA, but after seeing some videos of you I'm not that sure any more lol. However, sometimes I need to make trainingvideos myself for coworkers, what is the software you use for those red boxes to point out certain parts in the screen? I use Camtasia but I haven't found this feature.. Have a great day
They are under Annotations in camtasia.
@@Excelmacromastery Cannot believe I missed that. Thanks!
I prefer variables at top not as I use. that way I k ow where to get tbem
I avoid using I and L in code and in excel they look to much like each other and 1 in certain fonts. Test which the lowercase L or I in text I l ? even google font is hard to tell. For for loops I use XA XB XC etc
Isn't there any tool to indent code automatically?
Yes, but no part of the Visual Basic Editor.
There is Smart Indenter for VBA, which makes your code Indented by only clicking one button.
Really convenient.
I did not know that there was a checkbox in the settings to insist that all variables be declared. The first thing I have done for years, before starting a new module is type "OPTION EXPLICIT". I don't even understand why you are allowed to not declare variables!
Shakes fist at Microsoft.
I prefer to declare the variables on the top of the function/sub. I think it is easier to find them. I am used to start all my variables that have the same type with the same letter (s for the string, a for the arrays, l for long...) For me, it is a good way to identify the king of variable without looking for the statement.
The variable naming you are referring to is Hungarian Notation which was used a lot in the 1990s and before.
I don’t indent my code, I exdent it.
Great video, as always.
I disagree, respectfully, with the practice of declaring variables immediately before they're used because in some cases it will be necessary to create more variables than would actually be needed if the declarations were centralized at the top of the method.
Below is an admittedly contrived example that illustrates why one cannot always declare variables immediately before they're used.
===============================================================
Public Sub TestCode()
'Purpose: If current weekday is Monday, print weekday names
'excluding Monday, in ascending order by weekday number
'If current weekday is NOT Monday, print weekday names
'in DESCENDING order by weekday number
'Cannot always declare variables immediately before
'they're used
Dim blnIsMonday As Boolean
blnIsMonday = (Weekday(Date) = vbMonday)
If blnIsMonday Then
Dim i As Integer
For i = vbSunday To vbSaturday
If i vbMonday Then Debug.Print WeekdayName(i)
Next i
Else
'Dim i As Integer '
Thanks for your comment. If you need extra variables then you code needs to be rewritten.
In your example, the 2 for loops are not necessary. You can write the code so only one is required by using a variable for the start, end and step values.
Then you only need one For loop no matter how many conditions.
Indenting under Sub is more annoying than helpful.
Thank you