Download Microsoft Excel VBA User Manual

Excel Visual Basic for Applications
Table of Contents
Errors ......................................... 23
Syntax Errors ............................ 23
Run-Time errors ........................ 23
Line Continuation ......................... 24
Project Explorer Window ................ 24
Properties Window ........................ 24
Object Browser ............................ 25
Locals Window ............................. 25
Watch Window ............................. 26
Immediate Window ....................... 26
Splits and Bookmarks.................... 26
VBA Memory Variables and Constants 27
The role of Option Explicit .............. 27
Variable Declaration ...................... 27
Data Types .................................. 27
Summary of Data Types ............. 28
How to determine the Data Type .... 28
Variable Scope and Lifetime ........... 29
Public Module Scope................... 29
Private Module Scope ................. 29
Procedure Scope........................ 29
Public and Private ......................... 29
When to use Set........................... 29
Declaring the Data Type of Object
Variables ..................................... 30
Use of Constants .......................... 31
Data Type Conversion Functions ..... 31
Naming Conventions ..................... 31
Should I declare my variables? ....... 31
Functions ....................................... 32
Calling VBA functions .................... 32
The Format function...................... 32
Calling Excel Worksheet Functions .. 32
Creating a Function procedure ........ 33
Creating a Custom Function for Excel
................................................. 33
Creating an Add-In ....................... 34
Protecting a Project....................... 35
Events ........................................... 35
The role of event driven procedures 35
Using the event code shells ............ 35
Reserved Procedure Names............ 35
On Methods ................................. 36
OnKey Method........................... 36
OnTime Method ......................... 36
User Interaction .............................. 37
Message Box................................ 37
Statement form ......................... 37
Function Form ........................... 37
MsgBox Buttons and Return values
............................................... 38
Input Boxes ................................. 39
VBA Input Box Function .............. 39
Excel Input Box Method .............. 39
Excel’s Status Bar and Caption ....... 40
Menus and Toolbars ...................... 41
The Process...................................... 4
Terminology ..................................... 5
The Basics of VBA ............................. 5
Data storage.................................. 5
Subroutine Calls and passing values.. 5
Control Structures .......................... 6
Decision making ............................. 6
If-Then-Else .................................. 6
In-Line Form ............................... 6
Block Form ................................. 6
Case Statements ............................ 7
CHOOSE and SWITCH ..................... 7
Choose function........................... 7
Switch Function ........................... 8
Looping......................................... 9
Conditional Loops ........................... 9
Counter Loops................................ 9
Collection Loops ........................... 10
Understanding the Excel Object Model 11
Review of theory: Objects, Methods
and Properties ............................. 11
The Excel Object Model ................. 12
Object references: Cells, Sheets and
Workbooks .................................. 12
Non-specific Object References .... 12
Specific Object References, various
styles....................................... 12
Square brackets ........................... 13
With…End With ............................ 13
Recording and Editing ...................... 13
Recording a macro........................ 13
Relative and Absolute recordings .... 14
Personal Macro Workbook ........... 14
Macro Buttons.............................. 15
The Button Tool ........................... 15
The CommandButton Tool.............. 15
Command Bars ............................ 15
Editing and optimising recorded code
................................................. 16
Toggles ....................................... 17
Removing Selection statements ...... 17
Common Tasks in Excel Macros ......... 17
Printing ....................................... 17
Copying ...................................... 19
Measuring areas and lists .............. 19
Locating data on a worksheet......... 20
Manipulating cells ......................... 20
Application Settings ...................... 21
Using the Visual Basic Editor ............. 22
Code Window............................... 22
Context Help................................ 22
Complete Word ............................ 22
Commenting/ Uncommenting ......... 22
Running and Stepping Into statements
................................................. 22
Breakpoints and Break Mode .......... 23
Page 2