Tips, tricks, must_know and must_avoid in VBA

AI & new technology
, ,
1

Hi guys and girls,
do you have any suggestions about which practices in VBA should be avoided, which are good to implement in your code etc.? I will start with some simple things:
-begin each modul with option explicit. It will force you to declare each variable. It may seem annoying to declare them, but it’s helpful. Let’s say you have created a variable called check_if_true, which may have values 0 if something is false, and 1 if something is true. Then you say that check_if_true = 1 and later in code you have line if(check_is_true = 1) then do X, and for some reason this line never does X. The reason is that you’ve made a typo, and you have written check_is_true instead of check_if_true, and every variable at the start has value 0, so check_is_true = 0, not 1. If you have option explicit turned on then you can’t even run this code, because VBA will tell you, that this variable doesn’t exist.
-different way of avoiding typos is delibetely using uppercase letters instead of lowercase letters or vice versa. For example you create variable check_if_true and later in code you always type Check_if_true or check_If_True, etc. The point is to put an uppercase letter in place of lowercase letter. Then VBA automatically changes to correct size and you see, that the variable name is correct. However if you type check_is_TRue then it won’t change those letters, because you have done a typo (is instead of if) and you will know, that you have to correct it.
That’s it from me, I’m opened for more tips and tricks :smiley:

2

Hi, I have one basic advice for beginners - don’t select anything if not necessery. For example if your recording macro to change color of a cell you will get a code, which may look like this:

Range("B2").Select
Selection.Interior.Color = vbWhite

For one cell this won’t be a problem, but if you want color, copy, change value etc. of several cells this will slow down the program and also cause the screen to flicker. Instead you can simply write"

Range("B2").Interior.Color = vbWhite

In the long run this will make a huge difference :slight_smile: