Introduction
Sometimes we do some actions or activities in a certain way, and reach a satisfactory outcome by the way we do it. But as always mentioned here, you can often do your Work a little bit better.
And this rule also applies to the VBE - VBA Editor - used when writing Macros in Excel or in Access Modules, for example.
So now we’ll learn some very interesting tips and tricks for those who are interested in this kind of programming - and of course to us, that we use the VBE almost daily to write/modify our Hunter Macros.
These are simple and quick tips. For those who already know it, may not seem too significant, but if you do NOT know or use, may be surprised with these real ‘secrets’ that only professionals know.
Download
To download files used in this tutorial, click here telecomHall_BestVBE(VBAEditor)TipsEver.zip (138.5 KB)
VBE Limitations
Well, for beginners, the VBE editor is not one of the best ones. This can be especially noticeable because its interface is almost the same as it was many years ago - while to new languages like ‘#C’ Editors have much more resources.
But that’s not the case, because if you have already created or will create a Macro, you necessarily have used or will use it.
If there’s no way we will at least know some good practices that help improve our experience using it.
Note: When referring to procedures that includes ‘Sub’, ‘Function’, ‘Constant’ and ‘Declarations’, okay?
Enabling the Editor
To start, a simple hint, and maybe you already know: To switch between the VBE and the Program you’re writing the code, simply press ‘ALT + F11’.
But that you may not know: to close the VBE, press ‘ALT + Q’!
Is everything declared?
When you write a code, you can include a few lines at the beginning.
One of them, and, very interesting, is the ‘Option Explicit’. This option forces the declaration of variables, leading to creation of a code less error-prone.
See what happens when you try to run code that uses one or more ‘undeclared’ variables.
The recommendation then is to always include ‘Option Explicit’ at the beginning of your codes. Of course there are times when you do not need to ‘force’ the declaration of variables, then just omit this statement.
Yes, okay, I know that!
Sometimes you may need to copy and paste some code into a new location. And when you’re still editing - not finished - it may be that it is in an unexpected ‘format’, and VBE ‘always’ shows an ‘error’.
You know it is wrong and will fix / finish editing, but the VBE reports the error anyway.
If you want to work without being bothered by this compilation error message, go to Menu: ‘Tools’ (1) -> ‘Options’ (2). And uncheck ‘Auto Syntax Check’ (3).
Note that the VBE is still telling you that something is wrong - highlighting errors in Red - but you can fix the statement without bothering to get always responding OK.
Comment … comment again, and again!
Unfortunately, the VBE does not let you comment entire blocks (several lines) of code - like ‘Begin Comment’ and ‘End Comment’.
To comment a line, you enter a ’ in front of it. And if it is multiline comment, you must enter this same character in front of each line, line by line.
But - at least - there is a way to insert the character into multiple lines at once (and of course remove multiple lines at the same way if desired).
To do this, right-click on the ‘Taskbar’ (1), and enable the ‘Edit’ Toolbar (2). You can also do this by accessing the menu: ‘View’ -> ‘Toolbars’ -> ‘Edit’.
Now, whenever you need to comment out (or remove the comments) just select the desired lines, and then clicking the appropriate button. For example we selected the lines as shown below and clicked the ‘Comment Block’ button.
Note: to remove comments, use the button to the right side of the ‘Comment Block’ button.
Always see everything, or just what matters?
By default, the area where you write your code has a ‘Full Mode’ characteristic. This means that the edit mode lets you move through all the code - all Procedures - from begin to end using the scroll bar.
That’s good, but sometimes, depending on the size of your code, and the number of procedures, it can become a bit tiresome.
In addition, you can end up confusing when scrolling the bar, and not knowing if you are changing the desired section (you may have scrolled too much, and ended up in another similar Procedure).
To avoid this, you can temporarily change between the options ‘Full Mode View’ and ‘Procedure View’.
The following is a ‘Full Mode’ vision of our example module, with the option ‘View Full Mode’ (1). Imagine that we are working only in the sample Procedure shown in red (2). We need to keep using the scroll bar enough, no?
Now, see how vision of our area is temporarily changed with ‘Procedure View’ (1).
Of course, with a few lines in this example, the perception of gains is not as great, but as mentioned, everything will depend on your code.
Note: no matter which ‘View’ the complete code remains unchanged, and you can navigate through Procedures using the shortcut in the upper right.
Navigating the Procedures and Modules
And speaking of navigating the procedures and modules, a good way of doing this is through keyboard shortcuts.
Use ‘CTRL + TAB’ to move between modules.
To move between Procedures in a module, use ‘CTRL + PAGE DOWN’ and ‘CTRL + PAGE UP’.
Go … but come back!
Sometimes we are writing a piece of code, and for some reason, we need to go to another location. But then we want to return to the original point from which we emerge.
A more ‘traditional’ is through the use of Bookmarks - also available in the bar ‘Edit’.
To navigate between Bookmarks (and thus return to the place previously bookmarked), just click on the button ‘Next Bookmark’. Or right-click in the code and choose that option.
However, there is another ‘independent’ solution, actually more a ‘workaround’. But it is interesting to know.
The workaround is to write a ‘wrong’ or ‘unknown’ word (eg, slide your fingers over the keyboard and hit something like ‘asdfg’ or ‘lkjhg’) at the point where you want to quickly return later after collecting the desired information in elsewhere.
After typing the word at the point of return, go to wherever you want, and when you want to return to the original point, simply go to the ‘Debug Menu’ (1) -> ‘Compile’ (2).
The ‘unknown’ word will be identified by the compiler that goes straight for it, and on top of detaching it (give it up for you to paste directly a part of any code you have copied from elsewhere - just click ‘OK’ ).
Then there are two suggestions, choose the one that fits your way of programming.
Go straight to the Procedure! (And also come back!)
When our code has various procedures, and also when it is large, it is difficult to move between them.
Explaining: sometimes we are in a code that calls a certain procedure - and we need to go there. Many people still scrolls the scroll bar, or do a search.
But there is a much more interesting way to go straight to a particular procedure.
Simply click on the procedure (1), and hit ‘SHIFT + F2’ (2).
Ready, Editor goes straight to the procedure, even if it is in another module, as shown below.
And now, just want to go back to the place where you came from? Press ‘CTRL + SHIFT + F2’!
Note: Remember that here we are using sample data, and procedures are very close to each other - there was no need for this search here. But as mentioned earlier, depending on your code, this tip can definitely be used.
Watch all your variables!
When you debug code using the key ‘F8’, it runs line by line, and you can see the current value of any variable, simply placing the mouse cursor over it.
But you can check the current value of one or several variables at once. For this, you may want to use ‘Watch …’.
The traditional way to add a variable to the ‘Watches’ Window, using the ‘Add Watch’ (3) - which can be accessed for example with a right mouse click on the desired variable (1), and clicking ‘Add Watch …’ (2).
For example, our ‘Z’ variable is now being monitored, and when we run the code with the F8 key, it informs its values.
But there is an even better way! Just drag the desired variable to the ‘Watch’ Window!
There, our ‘X’ variable has also been added!
Move the yellow line - Control the Execution!
You should already know how to use - and we already said today - code execution with the F8 key. When we press it in a procedure, the current row becomes yellow (1), and also comes a yellow arrow on its left (2).
A very interesting tip here is that you can move it anywhere online! And the code continues to run from that point forward. That’s right, you have total control over that line, just drag the yellow arrow!
As an example, the following procedure, move (1) the Yellow line directly to the line of the variable ‘MyName’ (2), and then F8 to run the line.
See that in the ‘Watches’ Window (1) we have the value of the variable ‘MyName’ with the name ‘Leo’ assigned in the code. Note that ‘only’ this variable has some value - because we pulled the other lines!
And more interesting. You can drag the line again anywhere. For example, we drag the line back to the variable ‘MyName’ (1). Then - with the line highlighted in yellow - edit the name value, eg from ‘Leo’ to ‘Leonardo’ (2).
Pressing F8 again (3), see what happens.
Note: You can ‘drag’ the yellow arrow also via keyboard shortcuts. To do this, place the cursor on the desired line (which will be yellow) and press ‘CTRL + F9’.
Improving F8
Also speaking in code execution with F8 key, a great tip is the use of ‘CTRL + F8’. This key combination executes the code to the cursor. See what it does.
Indenting, Moving …
Some Text functions in VBE are the same as in other editors available like ‘MS Word’, but sometimes we do not use it.
For example, here is advisable to make indentation of code, making it easier to read.
When we have only one row is easy, just use the old and familiar ‘TAB’ key. But the most interesting is that this also applies to a set of lines. Simply select the set of lines to be indented, and press the same ‘TAB’ key.
To move back (left), the keys are now ‘SHIFT + TAB’.
Another tip, as in editors such as ‘MS Word’, you can move (instead of copy and paste) the text excerpts. To do this, select the desired text, and drag with the mouse.
Use Keyboard Shortcuts
You may have noticed that several of these tutorial tips can be accessed through the various Editor available menus. And also that most have ‘Keyboard Shortcuts’.
It is then our final tip: explore the Editor menus (and shortcuts), and use those that best suit your programming needs.
Well, that’s it. We hope you enjoyed, and can apply to your work, especially you that you are an Hunter User - you can now continue to practice like the pros!
Conclusion
This was a brief summary containing several random tips for VBE.
Whether you’re an advanced user or beginner or just curious, knowing these tips may represent a considerable saving of time, which can be spent on improvements to your own code.
In other words, you can now write your macros just like the professionals do: in the best way!
We hope you enjoyed. Till our next meeting.