Office Development – The Ugly

In the short introduction in my previous post “Office Development–The Good, The Bad and The Ugly?” I was showing three of the main options to build Office add-ins (again I will use Office Add-ins with the capital A to indicate talking about the ‘new’ Office JS Add-ins as opposed to add-ins in general). In this post, I’m now going into the first of each of the options a bit: how to start and where to look for things.

The first option is Visual Basic for Applications (VBA). If you go back a long time, like I do, this was about your first option to customize your Office environment. None of the other options were around at the time. VBA is hosted within the individual host applications such as Word, Excel, Outlook, PowerPoint, Excel or even some other applications not limited Microsoft to but licensed by Microsoft to these other vendors.

Disabled by default

Straight out of the box you need to activate the option to be able to use VBA from the host application. In this case Word, you need to go into the File menu option, select Customize Ribbon and tick the option on the right under Main Tabs called “Developer”.

After activating the option you will notice that the “Developer” Tab becomes visible:

By selecting the tab, you’ll see options like “Visual Basic”, “Macros”, “Record Macro” and more.

Entering your first code

Ok, now it’s time to enter your first VBA code … we won’t be creating the good old “Hello World” but it sure comes close. Click the “Visual Basic” button to open the VBA Integrated Development Environment (IDE) the environment where you build and (test)run your application.

Let’s dive in now … open the VBA IDE as explained above, and enter the following lines:

Sub test()

   Dim DefinedVariable As String
UndefinedVariable = “test”
MsgBox DefinedVariable

End Sub

Like this:

Running the code with function key F5 or stepping into the code with F8 until End Sub (the yellow line) you will see that Word is showing an empty dialog box where you expected the word “test” to appear if all variables were used correctly:

The Ugly I

You will notice that I defined the variable “DefinedVariable” but I initialized the variable “UndefinedVariable” with the value of “test”. I did this to show you the first “Ugly” in VBA … Although you won’t get an error your application will show an unexpected result (well … if the mistake was made in real life)

What happened here is that VBA didn’t throw an error as you are perfectly allowed to initialize an undefined variable (the one called UndefinedVariable in the code) but you’ll find that the variable was displayed that you did not initialize, called DefinedVariable.

This, especially in larger applications will cause extremely unexpected results and if the variables just look alike it will take you hours to notice that you made a typo somewhere.

To avoid these issues “Option Explicit” was invented. By adding the line, VBA requires you to define all variables used in your application. So, if you use this and runs the code your will now see that an exception will be thrown to point out that you missed something here:

Where is your code stored?

You built your first application, but you wonder … where is it stored. Well that is both the beauty and evil thing about VBA, it travels with the document (template, workbook, presentation … depending on your host application). The beauty because it is very easy to distribute, you just share the document, if it is about Word, to another person in your organization and they can run it. That is also -the risk- of being able to just share it, shady virus writers realized this and used it as a vehicle to spread their viruses in a fairly easy manner. Microsoft tried to solve this issue a little bit by differentiating between file naming. Documents containing VBA code are using extensions with an m like document.docm while ‘plain’ no code documents are named document.docx. It is good to know though, that the old extension .doc still exists.

The Ugly II

Lack of security and the risk of writing faulty code makes VBA the “Ugly” one of the three options. You are perfectly capable of creating the best and most intelligent tools using VBA and there have been some great examples that boosted productivity but it is just not “Enterprise” ready. It is hard to control and can cause a large impact on your support division within your company.

So, should you use it? Of course, you can use it whenever you feel it will help you being productive! I still use it myself … if I need to create some quick ‘n dirty solutions to avoid repetition in my “task for the day” it is really easy to record a few lines of code, edit it to fit your needs and run it.

I deliberately didn’t go into details of the VBA language. The reason for that is that I just wanted to provide a primer showing the three development options and my goal will be to dive into the “Good” (as considered today) option. If your want to know how to proceed with VBA I suggest you dive into the documentation behind the links below or find some introduction sites. The benefit of a toolset that has been around for such a long time is that there is a boatload of information available on the interwebs to get you started. This post is merely an introduction as part of the big picture called “Office Development” or as I called it “Office Development 2017”.

Documentation

https://msdn.microsoft.com/VBA/VBA-Language-Reference

Wikipedia

https://en.wikipedia.org/wiki/Visual_Basic_for_Applications