Assuming you will be using Microsoft Word, you will also need to figure out how to tell Excel where to put the data and information on the Word document. Are you going to compile everything into one long piece of formatted text, then just dump it into a new, blank Word document?
Or will you need reference fields or anchors in a Word template to populate?
Round up your ducks before you do anything else.
2. Use the recorder if you don’t know where to start
If you don’t know where to start, there is usually a way to “record” a macro you perform tasks such as exporting sheets, copying and pasting data, etc. It’s a handy tool, but it’s not foolproof.
Often, especially with Excel, the generated code will be unwieldy and full of irrelevant bits of code, like randomly clicking on a cell before unsaving. However, it gives you a great insight into the “spirit” of VBA.
Macros recorded in this way are also a great educational tool for a novice. You will see how the code is structured and its syntax and you will become familiar with some basic logic. However, macros recorded this way aren’t the most efficient and often bloat.
Often it’s a far superior idea to write your code. But, if you don’t know where to start, save.
3. The immediate window in VBE is a great tool
Often overlooked, the intermediate window is an invaluable tool when creating code in VBA. You can type almost any VBA statement in this window and get results quickly.
In the example above, we have created a simple message box. When you press Enter, the Word document should display and a message box appears with the text written between the speech marks in square brackets. Great.
If you want to use the middle box for yourself, open VBE and just use the shortcut ALT+G (Excel and Office) and do the test.
4. Know the Limits of VBA
It is important to note that although VBA is excellent, it has “limits”. These are mostly related to his age, but there are some inherent issues with that.
These include, but are not limited to:
- Memory leak is a serious problem. If you don’t know, a memory leak is a gradual decrease in system performance over time due to fragmentation of a computer’s RAM. This is caused by poorly designed or poorly programmed applications that do not free up memory segments when they are no longer needed.
- It has relatively poor built-in error handling (more on that later).
- It lacks a real legacy. In object-oriented programming (OOP), inheritance is the process of basing an object or class on another object (prototype-based inheritance) or class (class-based inheritance), maintaining a similar implementation .
- It’s not ideal for web development, but not impossible.
- It is not suitable for creating items outside of a Windows environment.
- It can be very slow compared to other programming languages. This is especially true when performance degrades over time, thanks to its memory leak.
- It tends to have many DLL calls. (The dynamic link library (DLL) is a collection of small programs that larger programs can load when needed to perform specific tasks.) This can consume a ton of memory.
- There is also a series of physical limitations (like maximum string lengths etc.) which is not specific to VBA.
4. On Error Resume Next is your friend, but use it sparingly
Nothing is worse than errors in your code that block the whole process for no apparent reason. This could be because you didn’t account for zero or zero values or the free text field contains special characters (more on that later).
Whatever the case, minor issues that you overlooked can cause your VBA code to sputter and crash.
One way to handle this (if you are sure there are no serious bugs with your code) you can use the Statement “On Error Resume Next” in VBA.
In Visual Basic, a statement is a complete set of statements. It can have keywords, operators, variables, constants and expressions.
This instruction tells the computer to “ignore” errors generated by the code and move on to the next line.
This allows our program to work but does not solve the underlying problem. This feature is great when you are fleshing out your code and just want to test it once in a while.
But, before deploying your project, you must first find the root cause of the problem.
5. The use of Debug.Print and the development of error sensors are however preferable
Like any programming language or code, having a robust way to handle errors is essential. While you can use the feature mentioned above to get out of some common hang-ups, it’s just plaster for a broken arm.
In other words, it doesn’t solve the underlying problem in your code.
This is where a well developed error handling section in your code makes perfect sense. It can be a standalone object stored in a module or you can create ad hoc error handlers per object.
Related but distinct is a standard VBA method called Debug.Print. When you run your code, this method displays the output in the immediate window instead of executing “correctly” in your file.
Error handling can be a dark art, but with experience it will become second nature to you. Get used to building them from the start and you’ll be a VBA veteran in no time.
A thorough review of these is beyond the scope of this article, but there are many good resources about them.
6. Make heavy use of comments
You saw a few of these in the image in point 4 above, but you should check your code with comments as much as possible. In VBA, you simply add a single quote (‘) as the first character in a line of code. Unlike other programming languages, you don’t need to close the comment; a line break is all you need.