6.9 C
New York
Sunday, December 4, 2022

An ode to VBA: Tips and hints for Microsoft's old yet still useful programming language – Interesting Engineering

Microsoft made Visual Basic for Applications (VBA) in 1993, and it comes with all Microsoft Office products. Whether it be Access, Excel, PowerPoint, Publisher, Word, or Visio, VBA is used to build programs for the Windows operating system.
VBA allows users to personalize MS Office host applications beyond what is generally possible and is an excellent feature for individuals or companies looking to get the most out of their MS Office suite.
But how do you do that? Let's find out.
Visual Basic for Applications (VBA) is an example of an event-driven tool that lets you tell the computer to do a specific action or a series of actions. If you don't know, "event-driven" means that a program is mainly controlled by user events or other similar input. Event-driven programming is a fundamental idea in application development and different types of programming. A program that responds to events is also called an event-driven application.
Microsoft/Interesting Engineering 
It has led to the creation of resources like event handlers. But, we digress.
One of the most common uses of VBA is the creation of macros (or macroinstructions) by typing commands into an editing module (or having an Office product "Record" one). Macros let users more easily complete repetitive processes by crunching data, copying and pasting data, creating charts, formatting documents and sheets, and a near-infinite number of other processes. You can also change toolbars, menus, dialogue boxes, and forms with VBA. All fantastic stuff, but there is more to VBA than that too.
Most importantly, VBA can be used to make user-defined functions (UDFs), and/or get access to Windows application programming interfaces (APIs), and automate specific computer procedures and calculations.
The former would include a set of instructions developed by a programmer to help administration staff create invoices or reports and, if needed, add them as attachments to an email. Sharing data between Office products (as described above) would also require some use of VBA's integrated API functions, of course.
One of the beauties of VBA, with some slight differences in terms used, is that VBA can be used to make MS Office applications effectively "talk" to one another by sharing data or triggering events within one another. This "quasi-API" ability lets you share data between applications, such as MS Access and an SQL database. This can include an online hosted SQL database on the same network or, for more advanced users.
The sky is the limit when it comes to VBA if you are willing to take the time to learn it.
For specific sectors, how you use VBA will vary widely.
In finance, for example, since this discipline is fundamentally about manipulating massive volumes of data, VBA is a potent tool to help lighten the load.
If you already work in this sector, VBA is probably operating within at least some of the apps you use daily, whether or not you are aware of it. One key giveaway is the amount of VBA-based positions constantly being advertised.
But, we digress.
In finance and accounting, VBA can let you perform various tasks, including but not limited to the following:
VBA can also operate in non-Microsoft environments via its native COM interface technology, which allows commands to communicate across computer boundaries. Many companies have included VBA in their proprietary and commercial programs, including AutoCAD, ArcGIS, CATIA, Corel, raw, and SolidWorks.
To a certain extent, you can also use VBA to build your own "proprietary" software tools for any service you can think of. If you want to automate your internal processes or provide a project management suite for clients, VBA is an excellent low-cost choice.
Put simply, it is great. It is also fun to learn, honestly.
VBA can be daunting when you first learn about it but rest assured, there are plenty of free resources to get you up to speed. It is not that tricky once you get to grips with the basics.
It is considered a beginner-friendly language, and VBA coders typically do not require prior coding knowledge to master it. Moreover, the VBA community is now very well established, with many highly experienced users more than happy to share their wealth of knowledge.
VBA has also often been seen as a "gateway" programing language that gives users an excellent grounding in "coding theory" that can be applied in more complex languages like C or C++.
Numerous online forums also supply VBA code examples, allowing you to copy and paste them. Prime examples include Stack Overflow, VBA Express Forum, Excel Forum, etc.
But (and word to the wise), while these are excellent resources, use caution while utilizing code written by others.
They can include some "spammy" or even potentially dangerous bits of code that could be malicious. This is especially true if you are unfamiliar with the code's source, author, or logic.
It is always best to attempt to design your code first, then look for ways to refine or correct it once you understand the fundamentals and syntax of functions.
In short, you don't. It comes as standard with all Microsoft Office products like Excel, Access, and Word.
Now all you need to do is fork out the cash to get yourself a copy of a version of Microsoft Office.
You'll need to open the Visual Basic Editor (VBE) to start writing or editing VBA. You can do this by either clicking the option to open it through the menus at the top of each software package or the keyboard shortcut Alt-F11. From there, you can access the wonderful world that is Visual Basic (VBA).
Your current file (Word, Excel Workbook, etc.) will continue running, but a new VBE window should pop up. When you first open it, it should open in front of any existing open files.
Like any window in the Windows Operating System, you can minimize, manually shrink or stretch, or pin them side by side. This is useful when testing your code.
Once it is open, the current projects you have open (i.e., the files) will be displayed in the upper left corner of the VBA window. You can usually identify them by their saved file name.
Automating repetitive tasks is incredibly valuable. All Office suite applications use the same programming languages and can incorporate VBA code to enhance their functionality. Due to the repetitive nature of spreadsheets, data analytics, and data organization, VBA fits Excel more naturally than other Office suite tools.
When you open the VBE graphical user interface (GUI), the pane displays the properties of the selected project in the bottom left corner. A list of properties is generated as distinct projects or workbooks. These characteristics are listed alphabetically by default but can also be sorted by category.
A new window displays in the upper-left corner when a project is double-clicked. There is no content in this section, although there are two dropdown menus labeled "(General)" and "(Specific)" (Declarations). This is the window where the VBA code can be entered directly.
Finally, the toolbar has numerous functional buttons and tools. The yellow below are the toggles for a run, break, and reset for the VBA code. The run button executes the code, the break button pauses the code's activity, and the reset button halts the code's execution and returns the process to the beginning of the code.
This might all sound a little complex, but the best way to get to grips with it is just to get your hands dirty and start coding. It'll soon all become second nature to you.
Absolutely not!
Many organizations still use VBA worldwide and will continue to do so as long as Microsoft Office maintains its large market share.
While VBA is not the most contemporary programming language, it is still critical. Even though Microsoft provided a new, modern JavaScript API for Office Add-ins in the cloud with Microsoft 365, which has the advantage of running in the cloud, VBA remains considerably superior in the context of desktop client applications.
When local components, files, and procedures outside of Office documents are involved, Microsoft Office automation is the preferred method. This will not change within the foreseeable future.
That said, modern programming languages such as Python, C#, and R can be utilized instead of VBA. Moreover, new tools, such as Power Query, may be able to execute tasks that were previously possible only with VBA.
Whether you are a VBA veteran or a complete novice, the following hints and tips will prove invaluable. Every experienced VBA programmer with have their box of tricks when it comes to using VBA, but these are some of the main points we believe most will agree are "must know."
Dilok Klaisataporn/iStock 
Like any coding project, you must sit down and think hard about your goal. What will data inputs (and types) be processed? What outcomes or functions does the code need to perform? How will any results of calculations made by a piece of VBA code be returned to the user?
These are some of the questions you need to address before opening VBE.
If you are trying to automate a process, try to identify the key steps taking place and see where you can automate it. Let's say, for example, you want to generate a letter from an Excel spreadsheet. You know you need to have a way of getting an address for the recipient and their name and title. You'll also want a return address, date, signature, and content for it as well.
Presuming you'll be using Microsoft Word, you'll also need to figure out how to tell Excel where to put any data and information on the Word document. Will you compile everything as one long chunk of formatted text and then simply dump it into a new blank Word document?
Or will you need fields or reference anchors in a Word template to populate?
Get your ducks in a row before doing anything else.
If you have no idea where to start, there is usually a way to "record" a Macro of you completing tasks like exporting sheets, copying and pasting data, etc. This is a handy tool, but it is not foolproof.
Often, especially with Excel, the code generated will be unwieldy and chocka-block with irrelevant bits of code, like you randomly click on a cell before canceling the recording. However, it gives you some excellent insight into the "mind" of VBA.
Macros recorded in this way are also a great teaching tool for a novice. You'll see how the code is structured and its syntax and get to grips with some basic logic. However, Macros recorded in this manner are not the most efficient and often bloat.
Often, it is a far superior idea to write out your code. But, if you don't know where to start, record away.
Microsoft/Interesting Engineering  
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 above example, we've created a simple message box. When you press enter, the Word document should display, and a message box popup will appear with the text written between the speech marks within the brackets. Pretty neat.
If you want to use the Intermediate Box for yourself, open VBE, and simply use the shortcut ALT+G (Excel and Office) and get testing.
It is important to note that although VBA is great, it does have "limitations." These are mainly to do with its age, but there are some inherent issues with it.
These include, but are not limited to:
Microsoft/Interesting Engineering  
Nothing is worse than errors in your code crashing the whole process for no apparent reason. This could be because you haven't factored in zero or null values or the free text field contains some special characters (more on that later).
Whatever the case, minor issues you have overlooked can result in your VBA code simply sputtering and stalling.
One way to deal with this (if you are confident there are no severe bugs with your code) you can use the "On Error Resume Next" statement in VBA.
In Visual Basic, a statement is a complete set of instructions. It can have keywords, operators, variables, constants, and expressions.
This statement tells the computer to "ignore" errors thrown by the code and move on to the following line.
This keeps our program running but does not solve the underlying problem. This function is excellent when you are fleshing out your code and just want to test it from time to time.
But, before you deploy your project, you must first find the root cause of the problem.
Like any programing language or code, having a robust way to handle errors is essential. While you can use the function mentioned above to get you out of some common hangups, it is only plaster for a broken arm.
In other words, it doesn't solve the underlying problem with your code.
This is where a well-developed error-handling section in your code comes into its own. This can be a standalone object stored in a module, or you can create error handlers ad hoc per object.
Related but separate is a standard VBA method called Debug.Print. When you run your code, this method will display the output in the immediate window instead of "properly" running in your file.
Error handling can be a dark art, but with experience, it will come as second nature to you. Get used to building them from the off, and you'll become a VBA veteran in no time at all.
An in-depth look into them is out of the scope of this piece, but there are plenty of good resources on them.
Microsoft/Interesting Engineering  
You've seen a few of these in the image for point 4 above, but you should check your code with comments whenever possible. In VBA, you simply add a single quote mark (') 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.
You'll need to start each line with another quotation mark for multiple-line comments.
There are often many programmers working in a team on the same project, with each person working on a section of the program. So, well-commented functions and logic help other programmers understand the code better. Comments also help others understand the reasoning behind how to solve any problem.
If you are on your own and looking to change the code in the future, comments can also help you remember the logic you used while writing that code. Lazy programmers who don't comment on their code properly sometimes forget what they did and spend much more time figuring out what went wrong. That wastes a LOT of time.
When and where you comment can be a matter of personal choice, but it is standard to have an introduction to each object you create. When working in teams, you will likely want to add team member names, dates, etc., for bits added or changed and why.
You can also use comments to "comment out" or disable lines of code when bug finding.
Users will save a lot of time and be able to understand your code better if you give your VBA variables and functions names that are easy to understand. People who try to read your VBA code will have a lot of trouble understanding what words like "test1" or "first integer" for variables or functions are referring to.
It is also helpful for you later down the line when you attempt to reference them or debug your code later. Spend extra time at the start to make the code read, understand, and digest as quickly as possible.
No problem, no matter how large, is that hard if you can break it down into more easily manageable chunks? Death (of the problem) by a thousand cuts, if you like.
No matter how complicated your work problem or situation is, it's likely made up of several smaller steps or processes. So cut things up into smaller pieces.
This way of putting code together is called modularization.
Modularization has several benefits. The first is that you reuse the bits you make in other processes. This is, after all, the core philosophy behind object-oriented programming.
The second is that smaller pieces of code are also easier to test and fix bugs in.
VBA has a lot of pre-existing methods and functions that can be used to do many different things. Many of these can perform everyday tasks you might want to integrate into your code, like splitting strings, searching for a character, closing or opening a file, etc.
There is no need to reinvent the wheel, so make sure you know what VBA already has to offer first. This will save you a ton of time and stress.
When you learn VBA, it's normal to be excited about everything you can do with it. But keep in mind that if you use it too much, it can make your work harder.
You can, in other words, find yourself going OTT with OOP.
Specific Microsoft Office programs, like Excel, come with various built-in features that are good on their own. These tools can solve everyday problems like conditional formatting, pivot tables, formulas, data validation, form controls, etc., so you don't need to build your own.
VBA should only be used when you can't solve a problem quickly with Excel alone. Or, of course, you have a non-standard or overly complex problem to solve.
And that is your lot for today.
The above is just the tip of the iceberg regarding the beautiful world of VBA. But, we hope you have now gained an appreciation and basic understanding of it and that, just perhaps, has inspired you to dip your toe in.
Can we wish you the best of luck? You are about to enter a literal rabbit hole of despair and utter.
The author of a new study explains how adding light could dramatically increase the electrical conductivity of bacteria-grown nanowires.


Related Articles


Please enter your comment!
Please enter your name here

Latest Articles