Programming the VBE

Up until now, the book has focused on writing VBA procedures to automate Excel. While writing the code, you have been working in the Visual Basic Editor (VBE), otherwise known as the Visual Basic Integrated Design Environment (VBIDE).

An object library is provided with Office 2007 that is shown as Microsoft Visual Basic for Applications Extensibility 5.3 in the VBE's Tools O References list. The objects in this library and their methods, properties, and events enable you to:

□ Programmatically create, delete, and modify the code, UserForms, and references in your own and other workbooks

□ Program the VBE itself to create useful Add-ins to assist you in your development efforts and automate many of your development tasks

There have been no significant changes to the Visual Basic for Applications Extensibility library between Office 2000 and Office 2007, so all the examples in this chapter apply equally to all versions.

The only responsible way to start this chapter is with a warning. Macro viruses work by using the methods shown in this chapter to modify the target file's code, thus infecting it. To prevent this, Microsoft has made it possible to disable access to all workbooks' VBProjects. By default the access is disabled, so none of the code in this chapter will work. To enable access to the VBProjects, place a check mark next to the Trust Access to the VBA Project Object Model checkbox in Excel 2007's Office Menu O Excel Options O Trust Center O Trust Center Settings O Macro Settings dialog.

This chapter explains how to write code to automate the VBE by walking you through the development of an Excel-related VBE Toolkit to speed up your application development. You will then add a few utilities to the toolkit that demonstrate how to programmatically manipulate code, UserForms, and references. For simplicity, most of the code examples in this chapter have not been provided with error handling. You can find the completed toolkit Add-in at

0 0

Post a comment