Visual Basic for Applications

  • Something that came up in my job is a need to learn and use Visual Basic to automate certain processes that I have to do in Microsoft Office. We use Office 2016 where I work and when we upgraded to it a several macros broke, and I needed to figure out how to fix them. I hadn’t originally written them, you see. I had found them on the Internet, and didn’t really know how they worked. But learning new things is always good, right?

So, I started my journey. Step one was revealing the tools needed to work with Visual Basic effectively. Office 2016 of course uses the Ribbon interface, and the trick is to go to the File menu, select Options, then Customize Ribbon, and finally put a check mark in the box on the right that says Developer.

Customize Ribbon option

Customize Ribbon option

 

 

 

 

 

 

 

This adds the Developer tab to your ribbon.

With this bit of beginning housekeeping, we are ready to start our exploration of Visual Basic for Applications as applied to Microsoft Office 2016.

Visual Basic

Microsoft created Visual Basic as an evolutionary outgrowth of the old Dartmouth BASIC, but there is not a lot on the surface to show the relationship. When I was in college, I wrote programs in BASIC, which was purely text-based, and looked like:

10 PRINT "Hello, World!"
20 END

Visual Basic, as you might guess from the name, is not so text-based, and brings in a GUI, and creates visual applications using a drag-and-drop interface. This went through versions up to 6.0, before being moved to Visual Basic .NET, and incorporated into Microsoft’s Visual Studio. The old Visual Basic 6.0 stopped development in 2008, and the development is now all in Visual Basic .NET.

Visual Basic for Applications (VBA)

VBA is actually based on the older Visual Basic 6.0, but has received further development, which moved VBA to a 7.0 in 2010. Nonetheless, there hasa been discussion about whether it would continue to be used going forward since it based on a “sunsetted” technology of Visual Basic 6.0. Here I think that we can look forward to some more years, since it is supported in Microsoft Office 2016, and does continue to be used. It is quite possible that at some future date Microsoft will move to a Cloud-based or Visual Studio-based solution for automating Office application tasks, but even in that case anything we know about VBA will help us in moving to the new solution.

So, what is VBA? Essentially, it is a scripting language that is built into Microsoft Office applications like Excel, Access, Word, Powerpoint, Outlook, and Project. As a Project Manager, my particular interest is in using VBA to to help me with data analysis in Project, which can help me with my job. VBA is event-driven, and can be sued to create macros that automate tasks within an application, and also to link applications together through Object Linking and Embedding (OLE). The kinds of things I am interested in include the following scenario:

  • Select data from Project using criteria I enter. This would mean creating a form to enter parameters (e.g. date ranges, resource names, etc.) to generate the data extract.
  • Clean up the data using rules, such as stripping alpha characters from Project tables that combine numbers with letters.
  • Export the data in a format readable in Excel.
  • Open Excel and insert the data.

And then I might want to automate what happens in Excel with yet another macro, and so on. As you can see, this can be very useful, and this is why I want to learn how to use it.

 Save as PDF