VBA Functions for Excel VBA Programming

Part of the Excel VBA Programming For Dummies Cheat Sheet

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

SERIES
Excel VBA Programming For Dummies Cheat Sheet

Advertisement

Inside Dummies.com

Dummies.com Sweepstakes

Win $500. Easy.