Appendix A. VBA Statements

     

In this Appendix

• VBA Statements

Throughout this book, I've introduced you to various VBA statements. (It's worth mentioning here that a statement is any VBA keyword or construct that isn't a function, object, property, or method.) These statements appeared on an "as-needed" basis whenever I wanted to explain a particular VBA topic (such as If...Then...Else and the other control structures you saw in Chapter 6, "Controlling Your VBA Code"). Although I covered many VBA statements in this book, I bypassed quite a few in the interests of brevity and simplicity.

In an effort to put some finishing touches on our VBA coverage, this appendix presents a brief, but complete, look at every VBA statement. I give you the name of the statement, the arguments it uses (if any; note, too, that required arguments are shown in bold type ), and a short description. For those statements that I didn't cover in this book, you can get full explanations and examples from the Statements section of the VBA Help file.

Table A.1. VBA Statements

Statement	Description
AppActivate title , wait	Activates the running application with the title or task ID given by title .
Beep	Beeps the speaker.
Call name, argumentlist	Calls the name procedure. (Because you can call a procedure just by using its name, the Call statement is rarely used in VBA programming.)
ChDir path	Changes the current directory (folder) to path .
ChDrive drive	Changes the current drive to drive .
Close filenumberlist	Closes one or more I/O files opened with the Open statement.
Const CONSTNAME	Declares a constant variable named CONSTNAME .
Date = date	Changes the system date to date .
Declare name	Declares a procedure from a dynamic link library (DLL).
DefBool letterrange	A module-level statement that sets the default data type to Boolean for all variables that begin with the letters in letterrange (for example, DefBool A-F).
DefByte letterrange	Sets the default data type to Byte for all variables that begin with the letters in letterrange .
DefCur letterrange	Sets the default data type to Currency for all variables that begin with the letters in letterrange .
DefDate letterrange	Sets the default data type to Date for all variables that begin with the letters in letterrange .
DefDbl letterrange	Sets the default data type to Double for all variables that begin with the letters in letterrange .
DefInt letterrange	Sets the default data type to Integer for all variables that begin with the letters in letterrange .
DefLng letterrange	Sets the default data type to Long for all variables that begin with the letters in letterrange .
DefObj letterrange	Sets the default data type to Object for all variables that begin with the letters in letterrange .
DefSng letterrange	Sets the default data type to Single for all variables that begin with the letters in letterrange .
DefStr letterrange	Sets the default data type to String for all variables that begin with the letters in letterrange .
DefVar letterrange	Sets the default data type to Variant for all variables that begin with the letters in letterrange .
DeleteSetting appname ,section , key	Deletes a section or key from the Registry.
Dim varname	Declares a variable named varname .
Do...Loop	Loops through one or more statements while a logical condition is True.
End keyword	Ends a procedure, function, or control structure.
Enum name	Module-level statement that declares an enumeration variable.
Erase arraylist	Frees the memory allocated to a dynamic array or reinitializes a fixed- size array.
Error errornumber	Simulates an error by setting Err to errornumber .
Event procedurename ( arglist )	Class module-level statement that declares a user -defined event.
Exit keyword	Exits a procedure, function, or control structure.
FileCopy source, destination	Copies the source file to destination .
For Each...Next	Loops through each member of a collection.
For...Next	Loops through one or more statements until a counter hits a specified value.
Function	Declares a user-defined function procedure.
Get # filenumber, varname	Reads an I/O file opened by the Open statement into a variable.
GoSub...Return	Branches to and returns from a subroutine within a procedure. (However, creating separate procedures makes your code more readable.)
GoTo line	Sends the code to the line label given by line .
If...Then...Else	Runs one of two sections of code based on the result of a logical test.
Implements InterfaceName, Class	Specifies the name of an interface or a class to be implemented in a class module.
Input # filenumber, varlist	Reads data from an I/O file into variables.
Kill pathname	Deletes the file pathname from a disk.
Let varname = expression	Sets the variable varname equal to expression . Let is optional and is almost never used.
Line Input # filenumber, var	Reads a line from an I/O file and stores it in var .
Load	Loads a user form into memory without displaying it.
Lock # filenumber , recordrange	Controls access to an I/O file.
LSet stringvar = string	Left-aligns a string within a String variable.
LSet var1 = var2	Copies a variable of one user-defined type into another variable of a different user-defined type.
Mid	Replaces characters in a String variable with characters from a different string.
MidB	Replaces byte data in a String variable with characters from a different string.
MkDir path	Creates the directory (folder) named path .
Name oldpathname As newpathname	Renames a file or directory (folder).
On Error	Sets up an error-handling routine.
On...GoSub, On...GoTo	Branches to a line based on the result of an expression.
Open pathname , etc.	Opens an input/output (I/O) file.
Option Base 01	Determines (at the module level) the default lower bound for arrays.
Option Compare TextBinary	Determines (at the module level) the default mode for string comparisons.
Option Explicit	Forces you to declare all variables used in a module. Enter this statement at the module level.
Option Private	Indicates that the module is private and can't be accessed by other procedures outside the module. Enter this statement at the module level.
Print # filenumber	Writes data to an I/O file.
Private varname	Declares the varname variable to be a private variable that can be used only in the module in which it's declared. Enter this statement at the module level.
Property Get	Declares a property procedure.
Property Let	Assigns a value to a property in a property procedure.
Property Set	Sets a reference to an object in a property procedure.
Public varname	Makes the varname variable available to all procedures in a module.
Put # filenumber, varname	Writes data from the variable varname to an I/O file.
RaiseEvent eventname , arguments	Fires the event given by eventname .
Randomize number	Initializes the random-number generator. Omit number to get a different random number each time.
ReDim varname	Reallocates memory in a dynamic array.
Rem comment	Tells VBA that the following text is a comment. The apostrophe (') is more widely used.
Reset	Closes all I/O files that were opened with Open .
Resume	After an error, resumes program execution at the line that caused the error.
RmDir path	Deletes a directory (folder).
RSet stringvar = string	Right-aligns a string within a String variable.
SaveSetting appname , etc.	Creates or saves a setting in the Windows Registry.
Seek # filenumber, position	Sets the current position in an I/O file.
Select Case	Executes one of several groups of statements based on the value of an expression.
SendKeys string, wait	Sends the keystrokes given by string to the active application.
Set objectvar = object	Assigns an object to an Object variable named objectvar .
SetAttr pathname, attr	Assigns the attributes given by attr (for example, vbReadOnly ) to the file given by pathname .
Static varname	Declares varname to be a variable that will retain its value as long as the code is running.
Stop	Places VBA in Pause mode.
Sub	Declares a procedure.
Time = time	Sets the system time to time .
Type varname	Declares a user-defined data type. (Used at the module level only.)
Unload	Removes a user form from memory.
Unlock # filenumber , recordrange	Removes access controls on an I/O file.
While...Wend	Loops through a block of code while a condition is True.
Width # filenumber, width	Assigns an output line width to an I/O file.
With...End With	Executes a block of statements on a specified object.
Write # filenumber	Writes data to an I/O file.