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.

All materials on the site are licensed Creative Commons Attribution-Sharealike 3.0 Unported CC BY-SA 3.0 & GNU Free Documentation License (GFDL)

If you are the copyright holder of any material contained on our site and intend to remove it, please contact our site administrator for approval.