Excel VBA Macro Programming

Overview

All Microsoft Office applications use VBA as their underlying macro language, and they all have their own object model in the same way that Excel has.

Because of this, there is the enormous advantage over other non-Microsoft programming languages that Excel VBA can be used to drive other Office programs. For example, you can create a Word document from within Excel without Word ever appearing onscreen. This may sound farfetched, but it is very easy to do. For example, you may design some code to manipulate data on a spreadsheet. Your user may require the output to end up in a Word document or as part of that document. Excel gives you the facility to open an external Word document, enter your data into it, and then save it, without even having any knowledge of how the file structure works in Word.

This can be done by using the CreateObject method in VBA. In order to use CreateObject. you must first add a reference in your application to the appropriate Microsoft Office Object Library file ‚ in this case, the Word Object Library. If you have Office installed, this file will already be available and will automatically appear in the References list without your having to browse for its location. If you do not have all of Microsoft Office installed, then you may not have this library file available unless it has been installed previously as part of another application.

When you add a reference to an Object Library, it then allows you to create objects for that application and to use the object model of that application, just as if you were programming in VBA inside that application.

You can add a reference by selecting Tools References from the menus . All available reference files will appear in a dialog. You need to select the Microsoft Word Object Library and check the check box next to it, as shown in Figure 14-1.

Figure 14-1: Selecting the Microsoft Word Object Library

Note that the location shown at the bottom of the References window points to an OLB file that is basically the Object Library for Word. The location points to the OLB file in the directory where Microsoft Office was originally installed. Click OK and you will be ready to use Word in VBA.

Here is a sample of code to create a new Word document and to save it to the local hard drive. This code will produce the same results whether Word is loaded or not, but it always looks more spectacular if Word is not running onscreen ‚ it will look as if you have done something very clever!

Sub Test_Word() Dim oWd As Word.Application, oWdoc As Word.Document Set oWd = CreateObject("Word.Application") Set oWdoc = oWd.Documents.Add oWdoc.Sections(1).Range.Text = "My new Word Document" oWdoc.SaveAs ("c:\MyTest.doc") oWdoc.Close oWd.Quit Set oWdoc = Nothing Set oWd = Nothing End Sub

The first line declares the variables for the application and the document objects. Because you have a reference to the Object Library included, you will see the Word objects, methods , and properties appearing in the drop-down lists as you type the code in. If you did not have the reference to the Object Library, VBA would not recognize these object types and the application would fail.

You then use the application variable ( oWd ) to hold the created object for the Word application. Note that in the CreateObject parameter, the description goes inside quote marks. This description uses the class name for the object and does not appear automatically because it is being entered as a string. You are not offered a list of choices for it. The string "Word.Application" is the class name for the application object within Word, which is why it is used here.

The oWdoc variable is now set to hold a new document based on the application variable ( oWd ). This is exactly the same as if you had loaded Word and then selected File New from the menu.

The text ‚“My new Word Document ‚½ is then added to the first section of the document. This appears on the first section because you loaded the Word document, effectively in the same way as if you had opened Word and then opened the file in Word. The cursor automatically defaults to the top of the document when this happens, and exactly the same thing happens in your code, because you are emulating the document being opened.

The document is saved as C:\MyTest.doc to the local hard drive, and the Word document is then closed. Leaving it open causes reservation problems and can cause problems when exiting Excel. Because this is a virtual application, the document stays in memory if it is not closed (even if Excel is shut down) properly, even though Excel only had a reference to it. However, this particular instance of Word does not appear on the Windows taskbar because it was created virtually in code. The only way to detect its presence is to look in Task Manager.

If you do not close the virtual object down properly, when Windows shuts down, the virtual Word application will ask if the document needs saving because it still considers that there is an open document. This can be extremely confusing to the user since they may have no idea that a Word application was open. To avoid this situation, the variables oWd and oWdoc are set to Nothing, and all memory held by them is released.

Try loading your newly created file into Word and you will see that it works as a perfectly normal Word document, just as if you created it using Microsoft Word.

This is a very simple example of manipulating Word from within Excel using VBA. This can be very useful ‚ for example, you could have a standard document with tables that the macro populated from the spreadsheet data. You can run your macro and the data will be transferred into the document tables.

Recently, I had the task of writing a program to populate an SLA (Service Level Agreement) Report. The SLA Report was a Word document with many tables of data and charts, but the input came from nine spreadsheets. Using the methods just detailed, I was able to write VBA code that worked through the individual spreadsheets, extract the relevant data, and place the figures in the correct tables or charts in the Word document. Previously, it had taken someone half a day to do this manually, but the code accomplished it in under five minutes.

Sub Test_Word() Dim oWd As Word.Application, oWdoc As Word.Document Dim or As Word.Range, ot As Word.Table Set oWd = CreateObject("Word.Application") Set oWdoc = oWd.Documents.Add Set or = oWdoc.Range Set ot = oWdoc.Tables.Add(r, 4, 5) ot.Cell(1, 1).Range.Text = "test" oWdoc.SaveAs ("c:\MyTest.doc") oWdoc.Close oWd.Quit Set oWdoc = Nothing Set oWd = Nothing End Sub

This example cannot be done from within Excel unless you manually copy and paste, which could get laborious if there is a lot of data. This is a good example of the macro language giving the user enormous power to automate tasks within Microsoft Office and enabling you to work outside the menu structure that Microsoft has put in place.