7 Simple Practices for Writing Super-Readable VBA Code
Vložit
- čas přidán 19. 07. 2024
- 👉 Ready to master VBA?
- Check out my full courses: courses.excelmacromastery.com/
- Subscribe to the channel here: bit.ly/36hpTCY
- FREE CHEAT SHEET: Get the free cheat sheet on VBA arrays here: bit.ly/2MXsnz9
(Note: If the download page doesn't work then make sure to turn off any popup blockers)
7 Simple Practices for Writing Super-Readable VBA Code
Having readable code is vital. It makes understanding your code easy to update and easy to understand.
But most important of it will slash the number of errors in your code. You can waste a lot of time fixing errors so this is very important.
In this video I show 7 simple techniques that will make your code readable.
Useful VBA Shortcut Keys:
Tab: To move lines of code to the right(Indent).
Shift + Tab: To move lines of code to the left(Outdent).
Shift + F2: Get the definition of the item under the cursor.
Ctrl + Shift + F2: Go to the last cursor position.
Ctrl + Space: AutoComplete Word.
Alt + F11: Switch between Excel and the VBA Editor.
Ctrl + R: View the Project Explorer Window.
Ctrl + Shift + 8(or Ctrl + *): Get the current region on a worksheet.
F4: View the Properties Window.
F5: Run the code from the current sub.
F9(or click left margin): Add a breakpoint to pause the code.
my coworkers will have to thank you :)
Glad you like it😀
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.
Sir, one of the best Excel youtube channels.
I was able to learn to code by myself but thanking for teaching me best practices. Deeply appreciate it.
You're welcome Nelson.
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
Enum - learn something new every time I watch your videos. Thanks, Paul!
You're welcome
Thank you for teaching the developers, these are very important principles.
Good stuff! Putting the dim statements within the code rather than at the top was new to me.
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.
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!
Thank you for your work
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
Excelente tips, thank you!
Just amazing. Thank you for your videos and page.
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
Paul, thank u very much for ur videos, they are so super useful. i have learned a lot from u.
Very useful, many thanks 👌🏻
Awesome, Paul, like all the time
Very useful advices, thanks.
You're welcome
Many thanks, it really helps! Appreciate your anonymous effort.
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 informative!
Continuously watching your video... Great topics and useful in vba.
Thanks for this
Thank You
Beautiful... Loving this channel
Thanks
Thank you
It is valuable knowledge. So useful and so practical. Thanks a lot.
You're welcome Truc
A different color for parentheses and array values helps a lot :)
Still exploring what VBA can offer and how to use it, but deffinitly this makes the jurney easier :)
Thank you, your tips are really helpful!
You're welcome!
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 🙂
Great 💯👍
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.
6:41. Magic numbers, leading into demonstration of ENUMS.
You're always awesome. Very useful video
Thanks Daniel
People sometimes underestimate the power of a macro...
Thanks Master!
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.
Its very good videos. Simple and clear
Thanks Teguh
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?
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.
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😀.
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?
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++.
I really have learned much from watching your videos.
That's great to hear Jim.
Great
Very simple and powerful tips, thank you. Which software you use to record your tutorials?
Camtasia
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!
Thanks a lot for the awesome hints
Thank you Yasser.
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 .
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.
Also make video on power bi
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?
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 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
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.
Isn't there any tool to indent code automatically?
Yes, but no part of the Visual Basic Editor.
I don’t indent my code, I exdent it.
Whatever benefit there may be to declaring variables at the point of use is far outweighed by the abject clutter that it creates...
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.