Chapter twoAutomating office Application

Introduction to Visual Basic for Applications

What is VBA and what you can do with it?

Visual Basic for Applications (VBA for short) is a programming

environment designed to work with Microsoft’s Office applications

(Word, Excel, Access, and PowerPoint). Components in each application

(for example, worksheets or documents) are exposed as objects to the

programmer to use and manipulate to a desired end. Almost anything you

can do through the normal use of the Office application can also be

automated through programming.

Microsoft Visual Basic for Applications (VBA) is an embeddable

programming environment designed to enable developers to build custom

solutions using the full power of Microsoft Visual Basic.

.

Developers using applications that host VBA can automate and extend the

application functionality, shortening the development cycle of custom business

solutions.

Visual Basic for Applications (VBA) is an implementation of Microsoft's event-

driven programming language Visual Basic, and associated integrated development

environment (IDE), which is built into most Microsoft Office applications.

As its name suggests, VBA is closely related to Visual Basic, but can normally only

run code within a host application rather than as a standalone application.

VBA is a complete programming language, but you can’t use it outside the

application in which it is integrated. This does not mean VBA can be integrated

only with Office programs. Any software vendor that decides to implement VBA

can include it with their application.

Cont……

VBA is relatively easy to learn, but to use it in a new application, you must first become familiar with

the object model of the application. For example, the Document and Dictionary objects are specific to

the Word object model, whereas the Workbook, Worksheet, and Range objects are specific to the Excel

object model. You use VBA to automate operation in the application that support it.

As a beginning language, VBA will suit your needs well. VBA is not as vast as many

popular languages because such extensiveness is simply unnecessary. VBA was built

to work with and extend the abilities of Office applications, so it doesn’t need the

substance of a programming language used to build full-blown applications from

scratch. The relative simplicity of VBA makes it less intimidating and easier for you to

learn. VBA, however, does share many of the programming constructs common to all

languages, so it also serves as a great introduction to programming.

Cont …….

What is a macro? If you perform a task repeatedly in Word or Excel , you can automate the task by using a

macro. A macro is a series of steps that is grouped together as a single step and then executed when necessary.

Macro is the sequence of command you can repeat at will .you can repeat the action by using a single command to run macro.

A macro is a tool that allows you to automate tasks and add functionality to your forms, reports, and controls. For example, if you add a command button to a form, you associate the button's On Click event to a macro, and the macro contains the commands that you want the button to perform each time it is clicked.

The term macro is often used to refer to standalone macro objects (that is, the objects that you see under Macros in the Navigation Pane), but in reality, one macro object can contain multiple macros. In that case, it is referred to as a macro group. A macro group is displayed in the Navigation Pane as a single macro object, but a macro group actually contains more than one macro. Of course, it is possible to create each macro in a separate macro object, but often it makes sense to group several related macros into a single macro object. The name in the Macro Name column identifies each macro.

Introduction to Macro

The easiest way to create a macro in Excel is by recording your steps with Excel’s macro recorder. For example, if you would like to automate a task in Excel, you record yourself performing the task by turning on a little virtual tape recorder, which will record each step as you enter it. When you are finished entering your steps, you turn off the recorder.

Here are the basic steps for recording a macro:

1. Turn on the macro recorder.

2. Enter a name for the macro.

3. Indicate a desired shortcut key for the macro (optional).

4. Indicate where you want the macro to be stored.

5. Enter a description for the macro (optional).

6. Start the recorder.

7. Enter your steps.

8. Stop the recorder.

Recording Macros

Once you have recorded a macro, you can run your macro in one of two ways:

1. If you assigned your macro to a key combination, you can press that key combination at any time to run the macro.

2. Whether or not you assigned your macro to a key combination, you can access your macro from the ribbon. You’ll display a macro list, choose the macro you want to run, and then choose Run.

We’ll go through each of these steps of recording and running macros and learn what they mean, while actually recording some real-life macros.

Displaying the Developer Ribbon Macro making is made much easier with the use of the Developer ribbon, a special

toolbar designed to help you with creating, running, and revising your macros. You can access some of the macro commands from the View toolbar, but you’ll find your macro experiences will be much easier if you have the Developer ribbon on display.

Cont.…..

The first macro we’re going to create is a simple macro that places your name in a cell. Usually when we record macros, we try to record a task that we expect to use over and over again. The easiest way to quickly create a simple macro in Word or Excel is to use the macro recorder. The Macro Recorder creates a copy of the commands you select with your mouse.

So for our first macro, here are the steps to follow—all the steps:

1. With your Excel open ,place pointer in cell A1.This way, we’ll all be in exactly the same place when we start recording this macro.

2. Click the Developer tab to open the Developer ribbon.

3. Click the Record Macro option on the ribbon.

4. In the Record Macro dialog box, enter NAME1 as the name of this macro.

5. In the Shortcut Key field, hold down the shift key and type n so that ctrl+shift+n will be the keyboard shortcut for this macro.

6. In the Store Macro In field, choose “Personal Macro Workbook.” This is the universal workbook that is accessible by all Excel spreadsheets.

7. Enter an optional Description for this macro.

8. Click OK. The macro recorder is now running and will record all of your keystrokes.

9. Back in the spreadsheet, in cell A1, enter your name as you wish to record it in this macro, leaving a space between each word.

10. Press enter when you have finished entering your name.

11. Click the Stop Recording option on the Developer ribbon

Creating a Macro

Figure 2- 1 Recording a macro

Conti……

When you created your three NAME macros, you were given three options for saving the macros. We chose to save all three of the macros to the Personal Macro Workbook.

For example, a Word macro that would quickly format a header and footer with the date, time and page number would be saved so that all Word files could execute the macro, not just one file.

Saving to the Personal Macro Workbook You’ll find that when you attempt to close Excel, you will receive a message asking if you

want to save the changes you made to your Personal Macro Workbook. If you click Yes, your macros will be saved and available to you the next time you open Excel.

The Personal Macro Workbook is a hidden workbook, not normally accessible as a workbook you can view.

Saving to This (the Current) Workbook Another option is to save the macros to this workbook. Some macros relate to specific

workbooks and aren’t of use in other workbooks. For example, if you create a macro that offers the user the option of performing certain

calculations on the data in an existing workbook, and the calculations relate only to that workbook.

Storing macros

When you choose to save a macro to your current workbook, you must also save the workbook in order to keep the macro.

When you are ready to save a workbook that contains a macro, you must choose Macro-Enabled Workbook as your workbook type.

When the Save As window appears, follow these steps:

1. Choose the folder where you want to save the workbook in the Save In

2. Enter the workbook name in the File Name field.

3. Choose Excel Macro-Enabled Workbook in the Save as Type field.

4. Click Save

Cont…..

Figure 2.2 Saving a worksheet that contains macros

Cont…

The third option is to save a macro to a new workbook. Some people want to create macros that are available to them for use in other workbooks, but they don’t want to make them available universally through the Personal Macro Workbook.

By saving macros in a new workbook, you choose when you want the macro to be available by simply opening that workbook.

As soon as you choose to save your macro to a new workbook, Excel opens a new workbook on your screen. Excel gives the new workbook the name Book followed by a number.

When you want to use the macro that has been saved to a new workbook, you must open that workbook. As long as that file is open, your macro will be available to all Excel workbooks that are open on your computer.

Saving to a New Workbook

If the macro has not been assigned to a keyboard command or a toolbar then do the following to run the macro; 1. Tools, Macro, Macros 2. Under Macro Name, click the macro you want to run 3. Click the Run button

Running Macros

Word 2007: 1. When opening More Add-in installer, if there is a security warning underneath the ribbon, then click the Options… button. Select Enable this content, then click OK. Proceed with installation of More Add-in. 2. Otherwise, open File menu and click on Word Options 3. Click Trust Center 4. Click Trust Center Settings… 5. In Macro Settings Select “Disable all macros with notification” 6. Click OK. Now open or re-open Word and proceed from step 1. First, the easy case: When you open the More Add-in Installer in Word 2007, look carefully for a security warning immediately underneath the ribbon. If you see it…

1. Click the Options button:

2. Click the “Enable this content” button, then click OK

Macros in MS – Word 2007

Security warning

Macros in MS – Word 2007

Click the “Enable this content” button, then click OK

This will enable macros on this occasion only. You should now be able to proceed to install More Add-in.

You may find that: There is no security warning when you open More Add-in Installer; and More Add-in Installer doesn’t work, i.e. nothing happens when you click the “Click here to install More Add-in” button.

If so, you need to set Word’s macro security settings to “Disable all macros with notification”

1. Open the File menu and click on the Word Options button:

Cont

2. In the Word Options dialog box, click Trust Center:

Cont…

3. Click on Trust Center Settings…

Cont….

4. In the Trust Center, in Macro Settings ensure that “Disable all macros with notification” is selected:5. Click OK. Now open (or re-open) the More Add-in Installer file. Proceed as per the easy case, above.

Cont…

To delete a macro, you use the VBA

1. Open the workbook that contains the macro you want to delete.

2. On the Tools menu, point to Macro, and then click Macros.

3. In the Macros in list, click This Workbook.

4. In the Macro name box, click the name of the macro that you want to delete.

5. Click Delete

Delete a macro

Exploring the Visual Basic Editor in Excel If you use Excel a lot, you are familiar with its spreadsheet layout. The menu bar

has changed considerably in Excel 2007 and now uses interactive ribbon controls for the submenu structure instead of the menu structure that previous users were familiar with.

Fortunately, the Visual Basic Editor (VBE) window has stayed much the same as in older versions of Excel, so if you have used Excel 2003 to design VBA code, you will not find too many differences.

You can insert data and formulas into the cells, format the cells or the entire worksheet, and insert graphics and graphs. You may even have tried recording a macro by clicking on the Developer item in the menu bar and clicking on the Record Macro icon in the Code control of the ribbon.

Using the Visual Basic Editor

Most users are unaware that, in addition to the spreadsheet application of Excel,there is an extremely powerful programming language built into Excel that you can use to design your own applications.

A macro is a procedure written in VBA code that performs certain tasks. However, before you can start programming in Excel, you need to know where the

macros are stored. This is not as obvious as it used to be when text-based macros were entered into a special macro spreadsheet. In the old macro language, you simply inserted a macro sheet and entered your commands anywhere. Now that the macro language has grown and become a full object-oriented language, the method of storing it has also changed.

Macros are now kept inside hidden VBA projects that are stored and saved within the workbook.

The language works in conjunction with object structures and hierarchies, and you can even create your own objects by using class modules.

These VBA projects can be accessed through a companion program called the Visual

Basic Editor (VBE). Press ALT-F11 to see the window shown in Figure 2-1.

Cont….

Figure 2-1

Cont…..

At first glance, this window with its new menu bar, containing menus for File, Edit, View,Insert, Format, Debug, Run, Tools, Add-Ins, Window, and Help, might be confusing. It opens as a separate application window, but it is still very much a part of the Excel application. In fact, this window opens up a whole new ball game in terms of what you can do with Excel.

In the next section, I’ll explain the windows in more detail.

VBA Project Explorer and Code Windows The Project Explorer, which shows a Project tree, is in the left-hand side of the screen, just below

the menu and toolbar. It shows the VBA project for the active workbook as it stands, displaying the details in tree form

so that you can easily navigate between them. If you click a branch of the tree, you’ll enter that particular workbook or worksheet from the

Visual Basic Editor. The VBA project is the root of the tree, and the workbook and worksheet objects are

the branches coming off the tree. As you add and delete worksheets or workbooks, the You can also add in other objects such as UserForms and modules.

Cont…

Remember, VBA is an object-oriented language. The first branch on the tree coming from the root of the VBA project says Microsoft Excel Objects. Coming off this branch are objects for the workbook that contain worksheets.

This is a very important concept to understand because the workbook is an object that can be referred to, and each sheet is also an object that can be referred to.

Double-click ThisWorkBook, and the code window for the Workbook object will open.

The code window automatically shows the statements

PrivateSub Workbook_Open() and End Sub: L 1-1 Private Sub Workbook_Open()

End Sub

This gives you a code area to write your VBA code against the Workbook_Open event.

Cont ….

Figure 1-3 Code window for the Workbook_Open event

Cont….

To edit a macro, you use the VBA

1. On the Developer tab, under Visual Basic, click Macros.

2. In the list, click the macro that you want to edit, and then click Edit. The Visual Basic Editor opens.

3. Make the changes that you want.

4. When you are finished, on the Excel menu, click Close and Return to Microsoft Excel.

Editing macro