This document is intended as an introductory guide to the development of customised applications using Microsoft Office 97 or Office 2000. We concentrate on using the Excel application, although the general skills gained are equally applicable to the other Office applications. It is not an exhaustive review of all of the features of these two packages - there are too many! I hope, however, to be able to describe the essential features of programming in Visual Basic for Applications (VBA) and to illustrate some of its capabilities by means of a range of illustrative examples | OFFICE AUTOMATION with VBA Office 97 2000 CONTENTS Section Topic Page 1 Introduction 3 Macro Basics 5 2 Office Objects 9 3 Visual Basic for Applications 15 Control Structures 18 4 Excel Objects and Collections 21 5 Code Optimisation 27 6 ActiveX Controls and Dialog Boxes 31 Using ActiveX Controls 34 7 Working With Events 37 Worksheet Events 41 Chart Events 42 Workbook Events 43 Application Events 44 8 Using Custom Dialog Boxes 47 9 Menus and Toolbars 55 Menus 57 Toolbars 60 10 Word Objects 69 11 Interacting with Other Applications 93 12 Using DLLs and the Windows API 97 Jeff Waldock SHU Science Maths. July 2000 1 2 Introduction 1. INTRODUCTION This document is intended as an introductory guide to the development of customised applications using Microsoft Office 97 or Office 2000. We concentrate on using the Excel application although the general skills gained are equally applicable to the other Office applications. It is not an exhaustive review of all of the features of these two packages - there are too many I hope however to be able to describe the essential features of programming in Visual Basic for Applications VBA and to illustrate some of its capabilities by means of a range of illustrative examples. The macro language VBA is a variant of the popular Visual Basic programming language. It offers the programmer the facility to automate and enhance the Office application and to develop a customised application for an end user who may not have the interest or desire to do so for themselves. These techniques can also be used to develop applications which streamline the use of the Office applications for a more expert user - they may be able to apply some level of customisation themselves. In Office95 the macro language for Word was WordBasic but this has now been replaced by VBA. VBA can also be used to control Access Powerpoint and Outlook as well as an increasing range of third-party applications . not made by Microsoft .
