Decide when to use the built-in Visual Basic for Applications tools or take your projects to the next level
If you’ve ever tried to tackle a Microsoft Office Visual Basic for Applications (VBA) project yourself, then you know how daunting that can be. While there are built-in Office tools to help you create macros and perform advanced data analysis, at some point you may realize that the default tools are not good enough. By learning Visual Basic for Applications on your own, you can extend the functionality of your Office programs.
What is a macro? How can it help me?
In all of Microsoft Office, you have the option to automate a series of tasks with the click of one button. This is called a ‘macro’. Building your own macros is one of the most time-saving tricks that you can perform. According to Udemy, “Macros are little programs that run within Excel and help automate common repetitive tasks. Macros are one of Excel’s most powerful, yet underutilized feature. Using macros, you can save hours and boost productivity manifold.”
For example, if you were given a CSV file of data, and were asked to import that into an Excel format, you may find that you need to perform the same, repetitive tasks on that data each time you needed to import. Maybe you need to clean up blank rows and columns, remove extra text, or format the appearance of the text. While each time you needed to perform these actions you took several steps, with a macro you could complete all of these same steps with one click.
How? When you record a macro, Excel translates all of your actions into Visual Basic code and stores that as a procedure. Then, the entire procedure can be called at any time. The procedure can be attached to a control form button or put on your Ribbon. The options of how to use a macro are endless.
What if I need to edit my Macro? I don’t know Visual Basic for Applications code!
Writing a macro does mean that your Microsoft Office program is “translating” your key strokes and commands to code behind the scenes. In order to edit this code, you need to put on your developer’s cap and dive in!
First, you may want to turn on the ‘Developer’ tab of the ribbon by going to Excel Options, choose ‘Customize Ribbon’ and check the box for Developer tab. Click ‘OK’ to exit the Options screen and you now see a new tab on your Ribbon.
On the Developer tab, you can click ‘Visual Basic’ and open the code editor window. While this may look like a foreign land for your first time looking at this screen, you should try and familiarize yourself with the key areas.
More importantly, from the Developer tab you can also click ‘Macros’ and then ‘Edit’ and you will view your specific macro. At this point, review the VBA code and learn more about how macros are written for you. Some edits will be easy for you to make yourself. For example, if you changed a font size as part of a macro, you will see the code for that and should be confident to change the font size number as needed. More advanced edits require more skill and VBA knowledge however.
Going to the next level…write your own VBA code
While there are a large number of benefits, there are also some limitations to using the Macros tool. If you find that the process you are trying to automate require more advanced logic structures (such as If then. For loops, etc), you are trying to store variables, or you need to create your own custom forms then you need to start writing VBA code yourself.
In general, VBA is a user-friendly and intuitive language to learn. One of the key concepts to learn more about is the document object model or DOM in which all of Office works within. This allows you to control the procedures, actions, and events of any part of the program.
One thing to be aware of when writing your own code is that you are on your own. If you create a custom script and it has unexpected results on your data, then you are responsible to fix. For that reason, always keep a backup of your original file and create many minor versions of the file as you do your development work.
The best advice I can offer is to treat VBA like learning any foreign language. On day 1 of a Spanish class, you are not going to speak fluent Spanish. You need time to learn the key parts of the language and then you can start to speak it yourself. Or, in this case, write the VBA code yourself.
Dive into macros and VBA and find out how it can help you and your Office projects to extend to a while new level!
If you have any questions on custom VBA projects for your home or office project, please fill out the following form and we are here to help.