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 |
