Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)
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. |
| 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 |
| 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 |