Return to VBA Code Examples

VBA Functions List

Below you will find a list of all of VBA’s built-in functions. The most commonly used functions contain links to pages discussing the functions in detail.

 

FunctionDescription
String/Text Functions
ASCReturns ASCII value of a character
CHRReturns the character based on the ASCII value
CONCATENATE with &Used to join 2 or more strings together using the & operator
FORMAT STRINGSTakes a string expression and returns it as a formatted string
INSTRReturns the position of the first occurrence of a substring in a string
INSTRREVReturns the position of the first occurrence of a string in another string, starting from the end of the string
LCASEConverts a string to lowercase
LEFTExtract a substring from a string, starting from the left-most character
LENReturns the length of the specified string
LTRIMRemoves leading spaces from a string
MIDExtracts a substring from a string (starting at any position)
REPLACEReplaces a sequence of characters in a string with another set of characters
RIGHTExtracts a substring from a string starting from the right-most character
RTRIMRemoves trailing spaces from a string
SPACEReturns a string with a specified number of spaces
SPLITUsed to split a string into substrings based on a delimiter
STRReturns a string representation of a number
STRCOMPReturns an integer value representing the result of a string comparison
STRCONVReturns a string converted to uppercase, lowercase, proper case or Unicode
STRREVERSEReturns a string whose characters are in reverse order
TRIMReturns a text value with the leading and trailing spaces removed
UCASEConverts a string to all uppercase
VALReturns the numbers found in a string
FormatApplies a format to an expression and returns the result as a string.
StringCreates a string consisting of a number of repeated characters.
Date/Time Functions
DATEReturns the current system date
DATEADDReturns a date after which a certain time/date interval has been added
DATEDIFFReturns the difference between two date values, based on the interval specified
DATEPARTReturns a specified part of a given date
DATESERIALReturns a date given a year, month, and day value
DATEVALUEReturns the serial number of a date
DAYReturns the day of the month (a number from 1 to 31) given a date value
FORMAT DATESTakes a date expression and returns it as a formatted string
HOURReturns the hours (a number from 0 to 23) from a time value
MINUTEReturns the minutes (a number from 0 to 59) from a time value
MONTHReturns the month (a number from 1 to 12) given a date value
MONTHNAMEReturns a string representing the month given a number from 1 to 12
NOWReturns the current system date and time
TIMESERIALReturns a time given an hour, minute, and second value
TIMEVALUEReturns the serial number of a time
WEEKDAYReturns a number representing the day of the week, given a date value
WEEKDAYNAMEReturns a string representing the day of the week given a number from 1 to 7
YEARReturns a four-digit year (a number from 1900 to 9999) given a date value
SecondReturns the second component of a supplied time.
TimeReturns the current time.
TimerReturns the number of seconds that have elapsed since midnight.
Math/Trig Functions
ABSReturns the absolute value of a number
ATNReturns the arctangent of a number
COSReturns the cosine of an angle
EXPReturns e raised to the nth power
FIXReturns the integer portion of a number
FORMAT NUMBERSTakes a numeric expression and returns it as a formatted string
INTReturns the integer portion of a number
LOGReturns the natural logarithm of a number
RANDOMIZEUsed to change the seed value used by the random number generator for the RND function
RNDUsed to generate a random number (integer value)
ROUNDReturns a number rounded to a specified number of digits
SGNReturns the sign of a number
SINReturns the sine of an angle
SQRReturns the square root of a number
TANReturns the tangent of an angle
MODReturns the remainder after division operator (Integer).
XORThe bitwise exclusion operator.
Logical Functions
ANDReturns TRUE if all conditions are TRUE
CASEHas the functionality of an IF-THEN-ELSE statement
FOR...NEXTUsed to create a FOR LOOP
IF-THEN-ELSEReturns a value if a specified condition evaluates to TRUE or another value if it evaluates to FALSE
ORReturns TRUE if any of the conditions are TRUE
SWITCHEvaluates a list of expressions and returns the corresponding value for the first expression in the list that is TRUE
WHILE...WENDUsed to create a WHILE LOOP
ISCompares two object reference variables.
LIKEThe pattern matching operator.
Information Functions
ENVIRONReturns the value of an operating system environment variable
ISDATEReturns TRUE if the expression is a valid date
ISEMPTYUsed to check for blank cells or uninitialized variables
ISERRORUsed to check for error values
ISNULLUsed to check for a NULL value
ISNUMERICUsed to check for a numeric value
IsArrayTests if a supplied variable is an array.
IsMissingTests if an optional argument to a procedure is missing.
IsObjectTests if a supplied variable represents an object variable.
Financial Functions
DDBReturns the depreciation of an asset based on the double-declining balance method
FVReturns the future value of an investment
IPMTReturns the interest payment for an investment
IRRReturns the internal rate of return for a series of cash flows
MIRRReturns the modified internal rate of return for a series of cash flows
NPERReturns the number of periods for an investment
NPVReturns the net present value of an investment
PMTReturns the payment amount for a loan
PPMTReturns the payment on the principal for a particular payment
PVReturns the present value of an investment
RATEReturns the interest rate for an annuity
SLNReturns the depreciation of an asset based on the straight-line depreciation method
SYDReturns the depreciation of an asset based on the sum-of-years' digits depreciation method
File/Directory Functions
CHDIRUsed to change the current directory or folder
CHDRIVEUsed to change the current drive
CURDIRReturns the current path
DIRReturns the first filename that matches the pathname and attributes specified
FILEDATETIMEReturns the date and time of when a file was created or last modified
FILELENReturns the size of a file in bytes
GETATTRReturns an integer that represents the attributes of a file, folder, or directory
MKDIRUsed to create a new folder or directory
SETATTRUsed to set the attributes of a file
FileAttrReturns the mode of a file that has been opened using the Open statement.
FILECOPYCopies a file from one directory to another.
FREEFILEReturns the next valid free file number (Integer).
GETReads data from a text file into a record.
INPUTReturns the open stream of an Input or Binary file (String).
EOFReturns the value indicating if the end of a file has been reached (Boolean).
WRITEWrites data to a sequential file.
PRINTWrites display-formatted data to a sequential file.
PUTWrites data from a record into a text file.
KILLDeletes an existing file.
SEEK - FunctionReturns the current read/write position within a file opened using the Open statement (Long).
SEEK - StatementRepositions where the next operation in a file will occur.
OPENOpens a text file or CSV file.
LINE INPUTReads a single line from an Open sequential file and assigns it to a string.
CLOSECloses a text file.
LOADPICTURELoads a picture from a file into a Picture or Image control (IPictureDisp).
LOCReturns the current read/write position within an open file (Long).
LOCKLocks access to parts of a file for other processes.
LOFReturns the length or size of an open file, in bytes (Long).
NAMERenames an existing file or directory.
SAVEPICTURESaves a graphic image from an objects Picture or Image property to a file.
Data Type Conv. Functions
CBOOLConverts a value to a boolean
CBYTEConverts a value to a byte (ie: number between 0 and 255)
CCURConverts a value to currency
CDATEConverts a value to a date
CDBLConverts a value to a double
CDECConverts a value to a decimal number
CINTConverts a value to an integer
CLNGConverts a value to a long integer
CSNGConverts a value to a single-precision number
CSTRConverts a value to a string
CVARConverts a value to a variant
CLNGLNGReturns the expression converted to a longlong (64 bit platform) data type.
CLNGPTRReturns the expression converted to a longptr data type.
CVDATEReturns the expression converted to a date variant-subtype (Variant).
FormatCurrencyApplies a currency format to an expression and returns the result as a string.
FormatDateTimeApplies a date/time format to an expression and returns the result as a string.
FormatNumberApplies a number format to an expression and returns the result as a string.
FormatPercentApplies a percentage format to an expression and returns the result as a string.
HexConverts a numeric value to hexadecimal notation and returns the result as a string.
OctConverts a numeric value to octal notation and returns the result as a string.
MACIDConverts a four character constant to a value that can be used by Dir, Kill, Shell and AppActivate.
VBA Array Functions
ArrayCreates an array, containing a supplied set of values.
FilterReturns a subset of a supplied string array, based on supplied criteria.
JoinJoins a number of substrings into a single string.
LBoundReturns the lowest subscript for a dimension of an array.
UBoundReturns the highest subscript for a dimension of an array.
REDIMInitialises and resizes a dynamic array.
VBA Message Functions
InputBoxDisplays a dialog box prompting the user for input.
MsgBoxDisplays a modal message box.
VBA Error Handling Functions
CVErrProduces an Error data type for a supplied error code.
ErrorReturns the error message corresponding to a supplied error code.
Lookup/Ref Functions
CHOOSEReturns a value from a list of values based on a given position
VBA Program Flow Functions
IIfEvaluates an expression and returns one of two values, depending on whether the expression evaluates to True or False.
Others
Vlookup in VBAUse the Excel spreadsheet Vlookup function from within VBA
APPACTIVATEActivates an application or window currently running on Windows.
BEEPProduces a single beep noise.
CALLTransfers control to a subroutine or function.
CALLBYNAMEReturns, sets or executes a method or property of an object (Variant).
COMMANDReturns the argument portion of the command line used to launch the application (Variant).
CREATEOBJECTReturns a reference after creating a new ActiveX or OLE object (Variant).
DELETESETTINGRemoves (or deletes) a key or section from the registry.
DOEVENTSPauses execution to let the system process other events.
EQVThe bitwise comparison operator.
ERASEReinitialises the elements of an array.
GETALLSETTINGSReturns the list of key settings and their values from the registry (Variant).
GETOBJECTReturns the reference to an object provided by an ActiveX component.
GETSETTINGReads from the registry and returns the value or key from the registry (String).
GOTOTransfers control to the subroutine indicated by the line label.
IMESTATUSReturns the current Input Method Editor mode of Microsoft Windows (Integer).
IMPThe logical implication from two values (Variant).
IMPLEMENTSSpecifies an interface or class that can be implemented in a class module.
LETComputes a value and assigns it to a new variable.
LOADLoads an object but doesn't display it.
LSETLeft aligns a string within a string variable.
NOTThe logical 'NOT' operator (Boolean).
OBJPTRReturns a LongPtr on a 64 bit version and a Long on a 32 bit version.
PARTITIONReturns a string indicating which particular range it falls into (String).
QBCOLORReturns the RGB colour corresponding to the specified colour number (Long).
RAISEEVENTFires an event declared at module level within a class, form or document.
REMSpecifies a single line of comments.
RESETCloses all files open with the Open statement.
RGBReturns the number representing an RGB colour value (Long).
RMDIRRemoves an existing directory.
RSETRight aligns a string within a string variable.
SAVESETTINGWrites to the registry and saves a section or key in the registry.
SENDKEYSSends keystrokes to an application.
SETAssigns an object reference to an object variable.
SHELLReturns the program's task id from running an executable programs (Double).
SPCInserts a specified number (n) of spaces when writing or displaying text.
STOPSuspends execution.
STRPTRReturns a LongPtr on a 64 bit version and a Long on a 32 bit version.
TABUsed with the Print # statement or the Print method to position output.
TYPENAMEReturns the data type of the variable as a string (String).
TYPEOFReturns the object data type.
UNLOADRemoves an object from memory.
UNLOCKControls access to a file.
VARPTRReturns a LongPtr on a 64 bit version and a Long on a 32 bit version.
VARTYPEReturns the number indicating the data type of a variable (Integer).
WIDTHAssigns an output line width (characters) for the open file.

 

We hope you found this list useful!

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! vba save as


Learn More!