Excel 2016 Power Programming with VBA (2016)
Part V. Appendix
· Appendix A
VBA Statements and Function Reference
A. VBA Statements and Function Reference
This appendix contains a complete listing of all Visual Basic for Applications (VBA) statements (Table A.1) and built-in functions (Table A.2). For details, consult Excel’s online help.
Note
Excel 2016 has no new VBA statements.
Table A.1 Summary of VBA Statements
Statement |
Action |
AppActivate |
Activates an application window |
Beep |
Sounds a tone through 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 |
DefDbl |
Sets the default data type to Double for variables that begin with specified letters |
DefDec |
Sets the default data type to Decimal 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 |
Enum |
Declares a type for enumeration |
Erase |
Reinitializes 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 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 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, 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 |
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 |
Table A.2 Summary of VBA Functions
Function |
Action |
Abs |
Returns the absolute value of a number |
Array |
Returns a variant containing an array |
Asc |
Converts the first character of a string to its ASCII value |
Atn |
Returns the arctangent of a number |
CallByName |
Executes a method, or sets or returns a property of an object |
CBool |
Converts an expression to a Boolean data type |
CByte |
Converts an expression to a Byte data type |
CCur |
Converts an expression to a Currency data type |
CDate |
Converts an expression to a Date data type |
CDbl |
Converts an expression to a Double data type |
CDec |
Converts an expression to a Decimal data type |
Choose |
Selects and returns a value from a list of arguments |
Chr |
Converts a character code to a string |
CInt |
Converts an expression to an Integer data type |
CLng |
Converts an expression to a Long data type |
Cos |
Returns the cosine of a number |
CreateObject |
Creates an Object Linking and Embedding (OLE) Automation object |
CSng |
Converts an expression to a Single data type |
CStr |
Converts an expression to a String data type |
CurDir |
Returns the current path |
CVar |
Converts an expression to a variant data type |
CVDate |
Converts an expression to a Date data type (for compatibility, not recommended) |
CVErr |
Returns a user-defined error value that corresponds to an error number |
Date |
Returns the current system date |
DateAdd |
Adds a time interval to a date |
DateDiff |
Returns the time interval between two dates |
DatePart |
Returns a specified part of a date |
DateSerial |
Converts a date to a serial number |
DateValue |
Converts a string to a date |
Day |
Returns the day of the month of a date |
DDB |
Returns the depreciation of an asset |
Dir |
Returns the name of a file or directory that matches a pattern |
DoEvents |
Yields execution so the operating system can process other events |
Environ |
Returns an operating environment string |
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 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 |
Filter |
Returns a subset of a string array, filtered |
Fix |
Returns the integer portion of a number |
Format |
Displays an expression in a particular format |
FormatCurrency |
Returns an expression formatted with the system currency symbol |
FormatDateTime |
Returns an expression formatted as a date or time |
FormatNumber |
Returns an expression formatted as a number |
FormatPercent |
Returns an expression formatted as a percentage |
FreeFile |
Returns the next available file number when working with text files |
FV |
Returns the future value of an annuity |
GetAllSettings |
Returns a list of settings and values from the Windows Registry |
GetAttr |
Returns a code representing a file attribute |
GetObject |
Retrieves an OLE Automation object from a file |
GetSetting |
Returns a specific setting from the application’s entry in the Windows Registry |
Hex |
Converts from decimal to hexadecimal |
Hour |
Returns the hour of a time |
IIf |
Evaluates an expression and returns one of two parts |
Input |
Returns characters from a sequential text file |
InputBox |
Displays a box to prompt a user for input |
InStr |
Returns the position of a string within another string |
InStrRev |
Returns the position of a string within another string from the end of the string |
Int |
Returns the integer portion of a number |
IPmt |
Returns the interest payment for a given period of an annuity |
IRR |
Returns the internal rate of return for a series of cash flows |
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 a Null value |
IsNumeric |
Returns True if an expression can be evaluated as a number |
IsObject |
Returns True if an expression references an OLE Automation object |
Join |
Combines strings contained in an array |
LBound |
Returns the smallest subscript for a dimension 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 number of characters in a string |
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 |
Minute |
Returns the minute of a time |
MIRR |
Returns the modified internal rate of return for a series of periodic cash flows |
Month |
Returns the month of a date as a number |
MonthName |
Returns the month of a date as a string |
MsgBox |
Displays a modal message box |
Now |
Returns the current system date and time |
NPer |
Returns the number of periods for an annuity |
NPV |
Returns the net present value of an investment |
Oct |
Converts from decimal to octal |
Partition |
Returns a string representing a range in which a value falls |
Pmt |
Returns a payment amount for an annuity |
Ppmt |
Returns the principal payment amount for an annuity |
PV |
Returns the present value of an annuity |
QBColor |
Returns a red/green/blue (RGB) color code |
Rate |
Returns the interest rate per period for an annuity |
Replace |
Returns a string in which a substring is replaced with another string |
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 |
Returns a rounded number |
RTrim |
Returns a copy of a string with no trailing spaces |
Second |
Returns the seconds portion of a specified 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 |
SLN |
Returns the straight-line depreciation for an asset for a period |
Space |
Returns a string with a specified number of spaces |
Spc |
Positions output when printing to a file |
Split |
Returns a one-dimensional array containing 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 converted string |
String |
Returns a repeating character or string |
StrReverse |
Returns a string, reversed |
Switch |
Evaluates a list of Boolean expressions and returns a value associated with the first True expression |
SYD |
Returns the sum-of-years’ digits depreciation of an asset for a period |
Tab |
Positions output when printing to a file |
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 spaces and/or trailing spaces |
TypeName |
Returns a string that describes the data type of a variable |
UBound |
Returns the largest available subscript for a dimension of an array |
UCase |
Converts a string to uppercase |
Val |
Returns the number formed from any initial numeric characters of a string |
VarType |
Returns a value indicating the subtype of a variable |
Weekday |
Returns a number indicating a day of the week |
WeekdayName |
Returns a string indicating a day of the week |
Year |
Returns the year of a date |
Invoking Excel Functions in VBA Instructions
If a VBA function that’s equivalent to one you use in Excel isn’t available, you can use Excel’s worksheet functions directly in your VBA code. Just precede the function with a reference to the WorksheetFunction object. For example, VBA doesn’t have a function to convert radians to degrees, but Excel has a worksheet function for this procedure, so you can use a VBA instruction such as the following:
Deg = Application.WorksheetFunction.Degrees(3.14)
Note
Excel 2016 has no new VBA functions.