Since most users install the full Microsoft Office suite of tools, they have Word and Excel installed on the same computer. You might need to automate Word functionality along with your Excel application. You can automate Word functionality from your Excel VBA code. In this article, we'll show you how to automate Word functionality from your Excel spreadsheets.

Initializing Word Variables

Before you can use Word in your applications, you need to add it to your references. References are used in programming languages to allow the local application to use the third-party application's DLLs. Dynamic link libraries (DLLs) are a part of the Windows operating system and any Microsoft application to allow other developers to code for the application. In this case, you want to use Word DLLs, so you must add the reference to your Excel VB application.

In your VBE, you'll see the Tools menu item. This menu is where you can add references. Click "Tools" and then click "References." A dialog window opens with a list of available third-party applications. You might be surprised at how many applications you have to choose from. If it's installed on the local desktop, then you will probably see it in the list. You can use numerous applications in your VBA code, and all you need to do is add a reference to it from this dialog window to get started.

Scroll down to the check box labeled Microsoft Word Object Library. You'll also see a version number, but this just indicates the version for the DLLs you'll be using.

Click "OK" and the VBE includes the reference in your project.

With the Word DLLs referenced, you can now use VB code to work with Word automation. First, just like the other code we've worked with, we want to create a subroutine. Let's create a subroutine named WordDoc().

Sub WordDoc()

End Sub

With the subroutine set up, we can now create and initialize Word variables. Take a look at the following code that sets up and defines Word variables.

Sub WordDoc()

  Dim word As Word.Application

  Dim doc As Word.Document

End Sub

In the above code, we have two variables. You need both of them to work with Word applications. The first one initializes the Word application itself. You need an application variable to work with its DLLs. Next, we created a Word variable for the document component. You need this variable to create, edit and manipulate documents. Just remember that both of these variables are needed when you want to work with Word documents.

With the variables created, we can now assign objects to the variables. Let's add the variable objects.

Sub WordDoc()

  Dim word As Word.Application

  Dim doc As Word.Document

  Set word = New Word.Application

  word.Visible = True

  Set doc = word.Documents.Add

End Sub

We added three lines of code. The first one is the instantiation of the Word application class. You know this is an instantiation line of code because it uses the "New" statement.

The next statement makes the Word application visible. When you initialize Word, you can create documents in the background without the application being visible to the user. In this example, we make it visible to the user, so it can be manipulated or edited later. After we make the Word application visible, we now want to add a document to it. In this example, we call the Documents.Add method to add a new document to the open window.

The above code initializes the Word variables, but it essentially adds a new document as well. Once we create the Word document, we can write data to it. We'll cover opening and writing content to a Word document in the next section.

Opening and Editing Word Documents

Since we're working with Word documents, you should allow users to open a document manually or auto-open it for them. You can open a file automatically in code, but some applications require that you show the user an open dialog window and let them choose the file that they want to use. In this section, we'll show you both methods and then show you how to add content to the file.

We need the same variables from the previous section, so let's copy the code from the first section to our new EditDoc subroutine.

Sub WordDoc()

  Dim word As Word.Application

  Dim doc As Word.Document

  Set word = New Word.Application

  word.Visible = True

End Sub

Notice that we took away the "Add" function. We don't want to add a new document to the application. We want to open an existing document. We know that there are two ways to do this. Let's first open it in the code without prompting the user for a name.

Sub WordDoc()

  Dim word As Word.Application

  Dim doc As Word.Document

  Set word = New Word.Application

  word.Visible = True

  word.documents.Open ("C:\\doc.docx")

End Sub

In the above code, we use the word variable to open the document in the existing application instance. We open the doc.docx file and add it to our instance.

In many cases, you need to prompt the user to open the file on their hard drive. You don't need to write your own open dialog window. Microsoft Windows provides you with the tools to open a dialog window. The operating system on the user's local computer is used to draw and prompt the user. When you open a dialog window, the code's execution stops until the user chooses a file or clicks the Cancel button.

Let's remove the auto-open code and add the choice for the user to select a Word document from a dialog window.

Sub WordDoc()

  Dim word As Word.Application

  Dim doc As Word.Document

  Set word = New Word.Application

  word.Visible = True

  choice = Application.FileDialog(msoFileDialogOpen).Show

End Sub

We added the code that shows a dialog window to the user's interface. The "Show" command actually displays it to the user. Had we not used the Show method, the user would not see the window. Since the window is an internal Windows function, we use the Application class. Whatever file the user chooses is assigned an integer value and stored in the "choice" variable.

At this point, a file was chosen but it's not opened yet. We need to add the code to actually open the document in the current Word application instance. Let's take a look at the code.

Sub WordDoc()

  Dim word As Word.Application

  Dim doc As Word.Document

  Set word = New Word.Application

  word.Visible = True

  choice = Application.FileDialog(msoFileDialogOpen).Show

  If choice <> 0 Then

    strPath = Application.FileDialog( _

        msoFileDialogOpen).SelectedItems(1)

    word.documents.Open (strPath)

End If

End Sub

We added an If statement that first identified that the user chose a file. If the user clicks "Cancel," then this code would not run and the subroutine would end without doing anything more. If the user clicks a file, it's given a number to identify it from the other files. The next execution falls in the If statement.

We first assign the path of the file to a string variable. This string variable contains the drive letter and the full path including the directory tree. Notice we use the static value of 1 in the SelectedItems function. This is to indicate that the first file should be retrieved. Users can also choose multiple files at a time. In this example, we only choose the first file since we only want to allow one file to open in the application.

Once the path is assigned to the string variable, we can now open it. The Open method using the word application variable is used.  Once this line of code executes, the file is opened and added to the Word application window.

Writing Data to a Word Document

The Word document is now open and activated, so now we can write information to it. Since the focus of this article is to work with Excel data, we'll show you how to write data from an Excel spreadsheet to the Word document. We can use the existing subroutine that we've worked with and add some functionality to it to take data from the spreadsheet cells and send it to the word document.

First, copy the existing subroutine from the previous section.

Sub WordDoc()

  Dim word As Word.Application

  Dim doc As Word.Document

  Set word = New Word.Application

  word.Visible = True

  choice = Application.FileDialog(msoFileDialogOpen).Show

  If choice <> 0 Then

    strPath = Application.FileDialog( _

        msoFileDialogOpen).SelectedItems(1)

    word.documents.Open (strPath)

End If

End Sub

Now, we need to loop through each cell and add data to Word. The following code adds two variables for the loop and the loop to retrieve data.

Sub WordDoc()

  Dim word As Word.Application

  Dim doc As Word.Document

  Set word = New Word.Application

  word.Visible = True

  choice = Application.FileDialog(msoFileDialogOpen).Show

  If choice <> 0 Then

    strPath = Application.FileDialog( _

        msoFileDialogOpen).SelectedItems(1)

    word.documents.Open (strPath)

End If

Dim j As Integer

Dim input As String

For j = 1 To 5

    doc.Activate

    input = Cells(j + 1, 1)

    word.Selection.TypeText Text:= input

    word.Selection.TypeParagraph

Next j

End Sub

We created a j variable used for the loop, and the input string variable will be used to store data from the cells we capture.

Note that we first call the Activate function on the doc variable to ensure that the Word document is set to receive data. We then loop through the first 5 rows and assign the input to a property named TypeText. The TypeParagraph property adds a carriage return and line feed to the document.

Handling Errors

  When you write real-world applications, you must account for unforeseen logic or input errors. These errors cause bugs in your application. If you don't account for these errors, your application crashes and the Excel file closes. You can imagine that users would be very frustrated with this behavior since they lose any unsaved data. Fortunately, Visual Basic has error handling functionality that lets you send a message to the user, trap the error, and avoid crashing the application. This section discusses error handling and how you can work it into your code.

The On Error Resume Next Statement

The first and most common error handling statement is the On Error Resume Next statement. This statement has two parts.

The On Error statement is used to capture the error when it occurs. If you're familiar with other languages such as C#, you'll recognize the try-catch statement. The On Error statement is similar to a try-catch block.

When an error occurs, the On Error statement captures the event and holds it until you tell the compiler what to do with the bug. If you don't have this statement available, your program crashes.

You must place the On Error statement at the top of your code or where you suspect a bug or error could be thrown. Let's take a look at some code that uses the On Error Resume Next statement.

On Error Resume Next

j = 1 / 0

If Err.Number <> 0 Then

j = 1

End If

If you recall from basic math rules, you can't divide any number by 0. We have an On Error Resume Next statement at the top of our code, and then we attempt to divide by 0 and assign the result to a variable. We know this causes an error to our code, so the On Error statement captures it instead of allowing the program to crash. At this point, the Resume Next statement is used. The On Error statement captures the error, and any statement following it tells the compiler what to do next. The Resume Next statement tells the compiler to ignore the error and just continue to the next block of code.

In this example, we identify if an error was captured in the next statement. If the result is 0, no error was captured. In this example, we know that an error occurred, so we then assign the j variable a value of 1.

The On Error Resume Next statement is useful in short blocks of code. The statement has two issues, though. The first one is that the next line of code after any statement must identify if there was an error from the previous statement. This can make your code tedious and complicated.

The next issue is that it forces your code to ignore errors, so you never know if there are any bugs or errors. If we eliminated the If statement in the previous example, the code would execute normally and continue execution without any warning. This can create logic bugs and unintended issues with your application.

The On Error Resume Next statement should be used rarely and only when you know that resuming to the next code statement won't cause any significant issues with your application.

The On Error Goto Statement

The alternative to the On Error Resume Next statement is to use the On Error Goto statement. Instead of ignoring an error and moving to the next line of code, the Goto statement tells the compiler to jump to a location that specifies how you want to handle the error. This statement makes it much easier to handle errors, because you know the error occurs and control the next block of code that executes.

Let's take a look at an example using the divide-by-zero code we used in the previous section.

Sub ErrorSample()

On Error Goto ErrHandler

  j = 1 / 0

  j = 1

  MsgBox j

Exit Sub

  ErrHandler:

  MsgBox "You have an error in your code!"

End Sub

We've created an ErrorSample subroutine to demonstrate the way On Error Goto works. We used the same division code, which again throws an error.

Instead of moving to the next line of code, the On Error statement captures the bug and then sends the next execution directive to the label indicated in the Goto statement. In this example, we used the name ErrHandler, but you can give your labels any name. The name you choose for the Goto statement must match the name of the label.

The label code syntax is its name and then a colon. After this colon, the error handling statements are executed. We just used a message box to send a message to the user. In this example, we tell the user that there was an error in the application. Once the error handler runs, no other code in the subroutine runs.

You can test this code by executing the subroutine. Instead of assigning the j variable with the value of 1, the j variable contains no value and the message "You have an error in your code" displays. If no error occurs, the j variable is assigned the value of one and a message box displays the value of j. Of course, we purposely throw an error in this code, so the proper code will never run and the error handler always executes.

This type of error handling is the most popular in VB, so it will be a majority of your error handling code.

You can have multiple labels and error handling statements. Let' stake a look at some code that uses two error handling blocks.

Sub ErrorSample()

On Error Goto ErrHandler

  j = 1 / 0

  j = 1

  MsgBox j

Exit Sub

  ErrHandler:

  On Error Goto ErrHandler2

  Debug.Print 1/0 'this also throws an error

  ErrHandler2:

  MsgBox "You have an error in your code!"

End Sub

In the code above, we use two labels. The first one catches the original error. We then have another division statement in the error handler. Since this code also throws an error, we need another error handler. This error handler then tells the user that there is a bug in the code.

When you receive an error in your code, the best way to manage it is to use the debugger to step through the code and watch the way the compiler executes each statement. As soon as an error is found, the execution flow jumps to the error handler assigned to the subroutine.

The Resume Statement

We saw the Resume Next statement in the first section, but the Resume statement doesn't require "Next" to function. The Resume statement simply tells the compiler to resume to the next statement for execution. This statement can be controlled within your error handling blocks.

In the previous sections, we showed you error handlers that displayed a message to the user but then exited the subroutine with no follow-up statements. In many cases, once you trap the error, you want to continue with the rest of the subroutine statements.

For instance, your code could throw an error during a calculation. If the calculation fails you want to tell the user but then use an alternative value or resort to the previous value. Prematurely ending the subroutine could be an issue with some functions.

Luckily, the Visual Basic language has the Resume statement that lets you handle an error and then go back to the subroutines main statements.

Let's take a look at an example.

Sub ErrorSample()

On Error Goto ErrHandler

  Worksheets("NewSheet").Activate

  Exit Sub

  ErrHandler:

  If Err.Number = 9 Then

    Worksheets.Add.Name = "NewSheet"

   Resume

    End If

End Sub

In the code above, we work with a new worksheet. We first try to activate the worksheet. If the worksheet does not exist, then the Activate function will throw an error. Without capturing the error, your program would crash and the user would be forced to re-open Excel.

Since we capture the error, the program doesn't crash. Instead, program execution falls to the ErrHandler label. The error returned is an integer value of 9. You need to look up these values or run through your code using the debugger to identify the right integer value for different errors.

If the error is number 9, then we create a new worksheet and use the Resume statement. The Resume statement tells the error handler to return to the statement that caused the error. In this example, the Activate method for a worksheet named "NewSheet" caused the error. Code execution returns to this line of code. Now that NewSheet does indeed exist, the subroutine is able to activate the worksheet and continue functioning.

This type of error handling is the best way to handle bugs if you're able to work with it. You won't always be able to automatically fix the error and return to code execution, but it's the most convenient for your users.

Error handling is an important factor for good user experiences. These handlers stop your application from crashing, and they allow your users to work with your VBA code without losing any data that they haven't saved. It's also a convenient way to trap bugs that would otherwise cause critical logic bugs. Every subroutine should have an error handler label assigned to it even if you don't think it will throw an error. A common developer mistake is thinking that sections of code could never throw an error. Always write subroutines with error handlers regardless if you think they could never throw an error.