Excel VBA Programming For Dummies, 6th Edition
Book image
Explore Book Buy On Amazon
The following table provides a list of commonly used VBA statements that you might use when creating macros for Excel. For more information on a particular statement, consult Excel’s Help system.
VBA Statement What It Does
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)
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
Erase Re-initializes an array
Error Simulates a specific error condition
Exit Do Exits a block of Do-Loop code
Exit For Exits a block of For-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 collection
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 (the Else part is optional)
Input # Reads data from a sequential text file
Kill Deletes a file
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
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, based on a condition
On...GoTo Branches, based 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
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 and the project hasn't been reset.
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 Allows a shorthand way of accessing multiple properties for an object
Write # Writes data to a sequential text file

About This Article

This article is from the book:

About the book authors:

Michael Alexander is a Microsoft certified application developer and author of several books on advanced business analysis with Microsoft Access and Microsoft Excel. He has been named a Microsoft MVP for his ongoing contributions to the Excel community. Dick Kusleika has been helping users get the most out of Microsoft Office products for more than 25 years through online forums, blogging, books, and conferences.

This article can be found in the category: