Skip to main content

Excel 2000 VBA: Programmers Reference

Excel 2000 VBA: Programmers Reference

John Green, Stephen Bullen, Felipe Martins, Brian Johnson

ISBN: 978-0-764-55849-8

Aug 2004

744 pages

Select type: E-Book

Product not available for purchase


What is this book about?

Excel 2000 is an important part of the Office 2000 program suite, and will be available in the Premium, Professional, Standard and Small Business editions of Office 2000. Excel has traditionally been the Office suite spreadsheet program par excellence. It still remains that way, but with Office 2000 there is a strong emphasis on between-application automation, ease of use, and the smart new bells and whistles that 2000 brings.

Using VBA (Visual Basic for Applications), the user can program his or her own programs in what is essentially a subset of the Visual Basic programming languages. This is tremendously powerful, as it allows you to create great User Interfaces (forms etc), as a front end to actual spreadsheet and database storage and manipulation. This continues to be one of the great strengths of programming Excel VBA.

What does this book cover?

This book presents a full reference to the Excel object model — which is essentially the object-oriented system of organizing the functional capacities that make up the Excel program. There is a short introduction to VBA itself, and the rest of the book documents aspects of programming Excel through that object model.

This book is in three broad sections:

  • The first part introduces Excel and VBA.
  • The second offers interesting, thematic discussions of some of the capacities available to Excel VBA.
  • The third and final part offers a full reference to the object model of Excel.

Who is this book for?

This book is for the Excel developer or user who already has a knowledge of spreadsheets, and the basic objects of an Excel spreadsheet, and now wants a solid and detailed reference to the main object models present in the Excel structure with examples of how to use these models.

Chapter 1: Introduction.

Chapter 2: Primer in Excel VBA.

Chapter 3: The Application Object.

Chapter 4: Workbooks and Worksheets.

Chapter 5: Using Ranges.

Chapter 6: Using Names.

Chapter 7: Filtered Lists.

Chapter 8: Generating Charts.

Chapter 9: Event Procedures.

Chapter 10: Adding Controls.

Chapter 11: UserForms.

Chapter 12: Command Bars.

Chapter 13: Class Modules.

Chapter 14: Addins.

Chapter 15: Interacting with other Office Applications.

Chapter 16: International Issues.

Chapter 17: Programming the VBE.

Chapter 18: Programming with the Windows API.

Appendix A: Excel 2000 Object Model.

Appendix B: VBE Object Model.

ChapterPageDetailsDatePrint Run
0Index entry errors

The index errors reported in the above erratum have been corrected for the second edition of the book published in January 2000.


56Page 56 - Code correction

The code at the top of page 56, GetFileName(), should read:

Sub GetFileName()
Dim BackSlash As Integer, Point As Integer
Dim FilePath As String, Filename As String
Dim i As Integer

FilePath = ActiveWorkbook.Fullname
For i = Len(FilePath) To 1 Step -1
If Mid$(FilePath,i,1) = "." Then
Point = i
Exit For
End If
Next i

If Point=0 Then Point = Len(FilePath) + 1

For i = Point - 1 To 1 Step -1
If Mid$(FilePath, i, 1) = "\" Then
BackSlash = i
Exit For
End If
Next i

Filename = Mid$(FilePath, BackSlash + 1, Point - BackSlash - 1)
MsgBox Filename
End Sub

Thanks to John Mitchell.


697Index entry errors

Unfortunately an error in the automatic production of the Index from the indexed chapter files was not detected until after publication. As a result some index entries have page numbers 120 too low, either instead of, or, as well as, the correct page numbers.

If a page number in the index is wrong, then correct it by adding 120.

More specifically:
1. All page numbers for entries under, "Command Bars" and, "Menus" should be raised by 120.
2. Page numbers for entries under "Toolbars" in the range 50-100 should be raised by 120. Higher and lower page numbers remain unchanged.
3. Where two page numbers differing by 120 are listed for the same index entry, the higher member of the pair is correct, while the lower should be disregarded.

All the errors in the index fall in one of the three categories listed above.

Wrox Press would like to apologise for any inconvenience experienced as a result of this error.