Appendix B: VBA Statements and Functions Reference

Overview

This Appendix contains a complete listing of all Visual Basic for Applications (VBA) statements and built-in functions. For details, consult Excel's online help.

Note  

There are no new VBA statements in Excel 2007.

Table B-1: SUMMARY OF VBA STATEMENTS
Open table as spreadsheet

Statement	Action
AppActivate	Activates an application window
Beep	Sounds a tone via the computer's speaker
Call	Transfers control to another procedure
ChDir	Changes the current directory
ChDrive	Changes the current drive
Close	Closes a text file
Const	Declares a constant value
Date	Sets the current system date
Declare	Declares a reference to an external procedure in a Dynamic Link Library (DLL)
DefBool	Sets the default data type to Boolean for variables that begin with specified letters
DefByte	Sets the default data type to Byte for variables that begin with specified letters
DefCur	Sets the default data type to Currency for variables that begin with specified letters
DefDate	Sets the default data type to Date for variables that begin with specified letters
DefDec	Sets the default data type to Decimal for variables that begin with specified letters
DefDbl	Sets the default data type to Double for variables that begin with specified letters
DefInt	Sets the default data type to Integer for variables that begin with specified letters
DefLng	Sets the default data type to Long for variables that begin with specified letters
DefObj	Sets the default data type to Object for variables that begin with specified letters
DefSng	Sets the default data type to Single for variables that begin with specified letters
DefStr	Sets the default data type to String for variables that begin with specified letters
DefVar	Sets the default data type to Variant for variables that begin with specified letters
DeleteSetting	Deletes a section or key setting from an application's entry in the Windows Registry
Dim	Declares variables and ( optionally ) their data types
Do-Loop	Loops through a set of instructions
End	Used by itself, exits the program; also used to end a block of statements that begin with If , With , Sub , Function , Property , Type , or Select

Table B-1: SUMMARY OF VBA STATEMENTS
Open table as spreadsheet

Statement	Action
Enum	Declares a type for enumeration
Erase	Re-initializes an array
Error	Simulates a specific error condition
Event	Declares a user -defined event
Exit Do	Exits a block of Do-Loop code
Exit For	Exits a block of Nor- Next code
Exit Function	Exits a Function procedure
Exit Property	Exits a property procedure
Exit Sub	Exits a subroutine procedure
FileCopy	Copies a file
For Each-Next	Loops through a set of instructions for each member of a series
For-Next	Loops through a set of instructions a specific number of times
Function	Declares the name and arguments for a Function procedure
Get	Reads data from a text file
GoSub Return	Branches to and returns from a procedure
GoTo	Branches to a specified statement within a procedure
If-Then-Else	Processes statements conditionally
Implements	Specifies an interface or class that will be implemented in a class module
Input #	Reads data from a sequential text file
Kill	Deletes a file from a disk
Let	Assigns the value of an expression to a variable or property
Line Input #	Reads a line of data from a sequential text file
Load	Loads an object but doesn't show it
Lock Unlock	Controls access to a text file
Lset	Left-aligns a string within a string variable
Mid	Replaces characters in a string with other characters
MkDir	Creates a new directory
Name	Renames a file or directory
On Error	Gives specific instructions for what to do in the case of an error
On GoSub	Branches on a condition
On GoTo	Branches on a condition
Open	Opens a text file
Option Base	Changes the default lower limit for arrays
Option Compare	Declares the default comparison mode when comparing strings
Option Explicit	Forces declaration of all variables in a module
Option Private	Indicates that an entire module is Private
Print #	Writes data to a sequential file
Private	Declares a local array or variable
Property Get	Declares the name and arguments of a Property Get procedure
Property Let	Declares the name and arguments of a Property Let procedure
Property Set	Declares the name and arguments of a Property Set procedure
Public	Declares a public array or variable
Put	Writes a variable to a text file
RaiseEvent	Fires a user-defined event
Randomize	Initializes the random number generator
ReDim	Changes the dimensions of an array
Rem	Specifies a line of comments (same as an apostrophe [ ˜ ])
Reset	Closes all open text files
Resume	Resumes execution when an error-handling routine finishes
RmDir	Removes an empty directory
RSet	Right-aligns a string within a string variable
SaveSetting	Saves or creates an application entry in the Windows Registry
Seek	Sets the position for the next access in a text file
Select Case	Processes statements conditionally
SendKeys	Sends keystrokes to the active window
Set	Assigns an object reference to a variable or property
SetAttr	Changes attribute information for a file
Static	Declares variables at the procedure level so that the variables retain their values as long as the code is running
Stop	Pauses the program
Sub	Declares the name and arguments of a Sub procedure
Time	Sets the system time
Type	Defines a custom data type
Unload	Removes an object from memory
While Wend	Loops through a set of instructions as long as a certain condition remains true
Width #	Sets the output line width of a text file
With	Sets a series of properties for an object
Write #	Writes data to a sequential text file