Fun with VBA and Training Your Office Assistant

by Dian Chapman, Technical Consultant
This article originally appeared in All 'Bout Computers Ezine

In this article, I'll give you a mini lesson in Word VBA (Visual Basic for Applications). Even if you've never written a single line of code, you'll be able to follow along with me and have a little fun.

VBA is the programming language used in Microsoft Office. The overall VBA language is similar in each application. However, just as the programs are different and allow you to perform different types of tasks, such as run calculations in Excel, create graphics in PowerPoint, design text documents in Word or manipulate data in Access, each of the object models in these programs are different too. So, just because you know VBA in Word, you'd have to learn about the object model in another program and know that other program fairly well in order to write code in it. In this article, however, we won't worry too much about the object model itself and just have a little fun messing around with some standard codes that are the same throughout Visual Basic.

NOTE! There's a lot you'd need to learn to understand how to write useful VBA code than what we'll do here. But if you want to get a little taste to see if it's something you might enjoy learning, follow along and let's see how much trouble we can get into!

The first thing we need to do is move into the VB Editor (VBE). So open Word and hit Alt/F11. Depending on whether you have any previous macros, you may or may not have a module already inserted into the Normal.dot. But just so we don't mess up any of your previous macros, let's insert a module so we have our own playground. Click Insert/Module, as shown in the image below.

This will insert a new module into your Project files. Notice in the image below that I now have a new Module1 code module.

And notice the default command of Option Explicit is automatically added to my new module. This is an important command that forces you to declare all your variables and helps you combat typos that could make you crazy when debugging. If you don't have this option set (under Tools/Option/Editor/Require Variable Declaration) you can type it in manually just to help you write better code.

Also, when you write code, the words must be all one word. In order to make the words you use easier to read, programmers use upper and lower case text. So be sure to type the information exactly as I do.

Simple Message Box
We'll start with a simple message box. The code for a message box is MsgBox. The basic message box is easy. You just use the code word and then add some text in quotes. That's it! Then when you run the code, a message box will display your information. You need to give the sub procedure a name. We'll call this one myFirstMessage. Type the code below into your module. You can write whatever you want between the two quotes, but don't use any additional quotes and make sure your message starts and ends with double quotes.

Sub myFirstMessage()

     MsgBox "This is so cool!"

End Sub

Now you can run this code to see how it works. Hit the F5 key to run your procedure. Click the OK button on the message and it'll close and you'll go back to the code module.

More Complex Message Box
There are lots of additional options you can add to the code to turn this simple message box into a more complex information gathering device. To learn more about what you can do with a message box, place your cursor within the code word MsgBox and hit the F1 key. This will open the VBA Help files for the message box and you can read a lot more about your options.

NOTE! If the VBA help files don't open, you probably did not install them. They do not install by default. So you'll have to get out your Office CD, insert it into the drive to have it startup. Then choose Add or Remove and go into the program files for the installation and be sure you check to install the VBA Help files. You can also get lots of information at the Microsoft Developer's Network site for Office at this URL: http://msdn.microsoft.com/office.

The syntax (rules for where to position information in code) for the MsgBox is as follows:

MsgBox(prompt[, buttons] [, title] [, helpfile, context])

Now I'll use some of the optional arguments to change this code to make it a question and I'll also add code to figure out what button the user pushed.

Sub mySecondMessage()

  Dim myVariable As Integer
  myVariable = MsgBox("Cool, yes?",
  vbQuestion + vbYesNo, "Dian's Message Box")

  If myVariable = 6 Then
     MsgBox "You answered Yes!"
  Else
    MsgBox "You answered No!"
  End If

End Sub

Explanation
The first part of the above code is the name of the subprocedure. I've called this one mySecondMessage( ).

The next line is the variable declaration. You need to warn your computer that you need it to put a little space on the side for a coming piece of information. Dim stands for dimension and it is how you declare a variable or warn the computer that information is coming. A variable is like a virtual bucket. It's where information is held when running code. So I've told the computer to be ready for an answer, a variable that will hold some information. Integer is the data type. You not only have to give the variable a name, but you need to tell the computer what data type it will be. This is so the computer knows how much space to save for this variable. Data types have size limits. This is also something you can look up in the VBA Help files.

Then I set the variable, myVariable, equal to the value that will be returned from the message box when the user clicks a button.

The If...Then statement that follows evaluates the value of the information contained in the variable when a button is pushed. The information that it will hold will be the value of the button that is pushed on the message box. In the VBA Help files, you can learn what these values are for various commands. In the case of a message box, 6 means someone pushed the Yes button. The number 7 stands for the No button. But since there are only two possible values being returned and I've already asked it if the variable value is equal to 6, I use the Else statement to decipher any other possible answer—which in this case can only be 7 or No.

Enter the code above into your code module and again hit the F5 key to run the code. You'll see the various messages appear depending on what button you push.

You can imagine how useful this type of code can be! Say you were creating an automated form in Word. You could ask the user a question and then do something different depending on what answer they give. If they say they don't have children, for instance, you could jump them over all the questions about children, since that information doesn't apply. Why make a user tab through a bunch of fields when you can make the form slicker by making it smarter.

Input Box
You can also use a simple Input Box to acquire information from the user. You can find this by putting your cursor in the word InputBox and hitting F1 to read the VBA Help files. Below is the syntax for using an InputBox.

InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])

Enter the code below in your module and hit F5 to run it.

Sub myThirdMessage()

 Dim myVariable As String
  myVariable = InputBox("Please enter your name: ", _
     "Dian's Input Box")  
   MsgBox "Your name is: " & myVariable

End Sub

In the code above, the variable is now declared as a string data type. This is because the variable will now contain a text string rather than an integer value.

Training Rocky
Ok, now for some real fun! Some people hate the Office Assistant. Personally, I love having Rocky, the dog, hang around my screen to keep me company when I'm working. Folks often ask me how I get Rocky to pose for me so I can capture his image to use in some of my training screen shots, as I did at the beginning of this article. I simply program Rocky to do what I want and then capture a screen shot while he's doing his thing. You can train Rocky, too!

Below I've added a simple procedure to get Rocky (or whatever your default Office Assistant is) to do what I want. You can look up more information in the VBA Help files. But also, if you've used the Option Explicit command at the beginning, the code sensitive help window will provide you with all the available commands for the code you are writing. So when you get to the Animation line of code, the intellisense help will display all the possible moves Rocky can make. See the image below. Notice as I get to the animation code line, VBA Help shows me a list of actions. You can choose any option and then run the code to see all Rocky's moves!

Sub TrainingRocky()

  Dim oAssistant As Assistant
  With Application.Assistant
    .Visible = True
    .Animation = msoAnimationGetArtsy
  End With

End Sub

By changing the animation code, you can train Rocky to do whatever you want!

Have fun!

Learn More!
If you want to learn more about writing VBA code, you can find several free articles linked here: www.mousetrax.com/techpage.html#autoforms or you can take my VBA online course, see www.mousetrax.com/techcourses.html for details.