Advertisement
Online Test Banks
Score higher
See Online Test Banks
eLearning
Learning anything is easy
Browse Online Courses
Mobile Apps
Learning on the go
Explore Mobile Apps
Dummies Store
Shop for books and more
Start Shopping

Cheat Sheet

Excel VBA Programming For Dummies

The Visual Basic for Applications (VBA) programming language lets you automate many aspects of Excel by writing Sub and Function procedures (also known as macros). A good knowledge of VBA can save time and make you a much more productive Excel user.

Common VBA Statements for Excel VBA Programming

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

VBA Functions for Excel VBA Programming

The VBA language contains a number of functions that you can use to build code in Excel. The following table provides descriptions of the most useful functions. When writing code, type the word VBA followed by a period, and you’ll see a drop-drop list of these functions. See Excel’s Help system for more details.

VBA Function What It Does
Abs Returns the absolute value of a number
Array Returns a variant that contains an array
Asc Converts the first character of string to its ASCII value
Atn Returns the arctangent of a number
CBool Converts an expression to Boolean data type
CByte Converts an expression to byte data type
CCur Converts an expression to currency data type
CDate Converts an expression to date data type
CDbl Converts an expression to double data type
CDec Converts an expression to decimal data type
Choose Selects and returns a value from a list of arguments
Chr Converts an ANSI value to a character
CInt Converts an expression to integer data type
CLng Converts an expression to long data type
Cos Returns the cosine of a number
CreateObject Creates an OLE Automation object
CSng Converts an expression to single data type
CStr Converts an expression to string data type
CurDir Returns the current path
CVar Converts an expression to variant data type
CVDate Converts an expression to date data type
CVErr Returns a user-defined error type
Date Returns the current system date
DateAdd Returns a date with a specific date interval added to it
DateDiff Returns the difference between two dates as a time interval
DatePart Returns an integer containing a specific part of a date
DateSerial Returns a date for a specified year, month, and day
DateValue Converts a string to date
Day Returns the day of the month of a date
Dir Returns the name of a file or directory that matches a pattern
DoEvents Yields execution so the operating system can process other events
EOF Returns True if the end of a text file has been reached
Error Returns the error message that corresponds to an error number
Exp Returns the base of the natural logarithms (e) raised to a power
FileAttr Returns the file mode for a text file
FileDateTime Returns the date and time when a file was last modified
FileLen Returns the number of bytes in a file
Fix Returns the integer portion of a number
Format Returns an expression in a particular format
FormatCurrency Returns a number as a string, formatted as currency
FormatDateTime Returns a number as a string, formatted as a date and/or time
FormatNumber Returns a number as a formatted string
FormatPercent Returns a number as a string, formatted as a percentage
FreeFile Returns the next file number available for use by the Open statement
GetAll Returns a list of key settings and their values (originally created with SaveSetting) from an application’s entry in the Windows registry
GetAttr Returns a code representing a file attribute
GetObject Retrieves an OLE Automation object from a file
GetSetting Returns a key setting value from an application’s entry in the Windows registry
Hex Converts from decimal to hexadecimal
Hour Returns the hour of a time
IIf Returns one of two parts, depending on the evaluation of an expression
Input Returns a specific number of characters from an open text file
InputBox Displays a box to prompt a user for input, and returns the value entered
InStr Returns the position of a string within another string
InStrRev Returns the position of a string within another string, beginning at the back end of the string
Int Returns the integer portion of a number
IsArray Returns True if a variable is an array
IsDate Returns True if a variable is a date
IsEmpty Returns True if a variable has not been initialized
IsError Returns True if an expression is an error value
IsMissing Returns True if an optional argument was not passed to a Procedure
IsNull Returns True if an expression contains no valid data
IsNumeric Returns True if an expression can be evaluated as a number
IsObject Returns True if an expression references an OLE Automation object
Join Returns a string created by joining a number of substrings contained in an array
LBound Returns the lower bound of an array
LCase Returns a string converted to lowercase
Left Returns a specified number of characters from the left of a string
Len Returns the length of a string, in characters
Loc Returns the current read or write position of a text file
LOF Returns the number of bytes in an open text file
Log Returns the natural logarithm of a number
LTrim Returns a copy of a string with no leading spaces
Mid Returns a specified number of characters from a string
MidB Returns a specified number of bytes from a specified position in a string string
Minute Returns the minute of a time
Month Returns the month of a date
MonthName Returns a string indicating the specified month
MsgBox Displays a modal message box and returns the ID of the button clicked
Now Returns the current system date and time
Oct Converts from decimal to octal
Replace Returns a string in which one substring is replaced with another
RGB Returns a number representing an RGB color value
Right Returns a specified number of characters from the right of a string
Rnd Returns a random number between 0 and 1
Round Rounds a number to a specific number of decimal places
RTrim Returns a copy of a string with no trailing spaces
Second Returns the second of a time
Seek Returns the current position in a text file
Sgn Returns an integer that indicates the sign of a number
Shell Runs an executable program
Sin Returns the sine of a number
Space Returns a string with a specified number of spaces
Split Returns an array consisting of a number of substrings
Sqr Returns the square root of a number
Str Returns a string representation of a number
StrComp Returns a value indicating the result of a string comparison
StrConv Returns a string variant converted as specified
String Returns a repeating character or string
StrReverse Returns the characters of a string in reverse order
Switch Evaluates a list of expressions and returns a value associated with the first expression in the list that is True
Tab Positions output in an output stream
Tan Returns the tangent of a number
Time Returns the current system time
Timer Returns the number of seconds since midnight
TimeSerial Returns the time for a specified hour, minute, and second
TimeValue Converts a string to a time serial number
Trim Returns a string without leading and spaces and replaces multiple spaces with a single space
TypeName Returns a string that describes the data type of a variable
UBound Returns the upper bound of an array
UCase Converts a string to uppercase
Val Returns the numbers contained in a string
VarType Returns a value indicating the subtype of a variable
Weekday Returns a number representing a day of the week
Weekday Name Returns a string indicating the specified weekday
Year Returns the year of a date
blog comments powered by Disqus
Advertisement
Advertisement

Inside Dummies.com

Dummies.com Sweepstakes

Win an iPad Mini. Enter to win now!