VBA Functions - by Category

VBA Functions

VBA Functions - By Category

TECH ont he Net

String Functions:
ASC (VBA)
CHR (VBA)
Concatenate with & (WS, VBA)
CURDIR (VBA)
FORMAT Strings (VBA)
INSTR (VBA)
INSTRREV (VBA)
LCASE (VBA)
LEFT (WS, VBA)
LEN (WS, VBA)
LTRIM (VBA)
MID (WS, VBA)
REPLACE (VBA)
RIGHT (WS, VBA)
RTRIM (VBA)
SPACE (VBA)
STR (VBA)
STRCONV (VBA)
TRIM (WS, VBA)
UCASE (VBA)
VAL (VBA) / Numeric / Mathematical Functions:
ABS (WS, VBA)
ATN (VBA)
COS (WS, VBA)
EXP (WS, VBA)
FIX (VBA)
FORMAT Numbers (VBA)
INT (WS, VBA)
LOG (WS, VBA)
RND (VBA)
ROUND (VBA)
SGN (VBA)
SIN (WS, VBA)
TAN (WS, VBA) / Logical Functions:
CASE (VBA)
IF-THEN-ELSE (VBA)
Lookup / Reference Functions:
CHOOSE (WS, VBA)
SWITCH (WS)
Information Functions:
ISDATE (VBA)
ISERROR (WS, VBA)
ISNULL (VBA)
ISNUMERIC (VBA) / Data Type Conversion Functions:
CBOOL (VBA)
CBYTE (VBA)
CCUR (VBA)
CDATE (VBA)
CDBL (VBA)
CDEC (VBA)
CINT (VBA)
CLNG (VBA)
CSNG (VBA)
CSTR (VBA)
CVAR (VBA)
Date & Time Functions:
DATE (VBA)
DATEADD (VBA)
DATEDIFF (VBA)
DATEPART (VBA)
DATESERIAL (VBA)
DATEVALUE (WS, VBA)
DAY (WS, VBA)
FORMAT Dates (VBA)
HOUR (WS, VBA)
MINUTE (WS, VBA)
MONTH (WS, VBA)
MONTHNAME (VBA)
NOW (WS, VBA)
TIMESERIAL (VBA)
TIMEVALUE (WS, VBA)
WEEKDAY (WS, VBA)
WEEKDAYNAME (VBA)
YEAR (WS, VBA) / Financial Functions:
DDB (WS, VBA)
FV (WS, VBA)
IPMT (WS, VBA)
IRR (WS, VBA)
MIRR (WS, VBA)
NPER (WS, VBA)
NPV (WS, VBA)
PMT (WS, VBA)
PPMT (WS, VBA)
PV (WS, VBA)
RATE (WS, VBA)
SLN (WS, VBA)
SYD (WS, VBA) / File/Directory Functions:
CHDIR (VBA)
CHDRIVE (VBA)
CURDIR (VBA)
DIR (VBA)
FILEDATETIME (VBA)
FILELEN (VBA)
GETATTR (VBA)
MKDIR (VBA)
SETATTR (VBA)

Note:(WS) = Worksheet function, (VBA) = Visual Basic for Applications function

String Functions:

MS Excel: ASC Function (VBA)

In Excel, theASCfunction returns theASCII valueof a character or the first character in a string.

The syntax for theASCfunction is:

Asc( string )

stringis the specified character to retrieve the AscII value for. If there is more than one character, the function will return the AscII value for the first character and ignore all of the characters after the first.

Applies To:

Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function:

VBA function (VBA)

VBA Function Example:

TheASCfunction can only be used in VBA code. Here are some examples of what theASCfunction would return:

Asc ("W") / would return 87
Asc ("Wednesday") / would return 87
Asc ("x") / would return 120

For example:

Dim LResult As Integer

LResult = Asc ("W")

In this example, the variable called LResult would now contain the value 87.

MS Excel: CHR Function (VBA)

In Excel, theCHRfunction returns the character based on theASCII value.

The syntax for theCHRfunction is:

Chr( ascii_value )

ascii_valueis the ASCII value used to retrieve the character.

Applies To:

Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function:

VBA function (VBA)

Worksheet Function Example:

Chr (87) / would return "W"
Chr (105) / would return "i"

VBA Function Example:

TheCHRfunction can only be used in VBA code. For example:

Dim LResult As String

LResult = Chr(87)

In this example, the variable called LResult would now contain the value "W".

MS Excel: Concatenate with & (WS, VBA)

To concatenate multiple strings into a single string in Excel, you can use the"&"operator to separate the string values.

The syntax for the "" operator is:

string1 & string2 [& string3 & string_n]

Applies To:

Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function:

Worksheet function (WS)
VBA function (VBA)

VBA Function Example:

The "&" operator can be used to concatenate strings in VBA code. For example:

Dim LValue As String

LValue = "Alpha" & "bet"

The variable LValue would now contain the value "Alphabet".

Frequently Asked Questions

Question:For an IF statement in Excel, I want to combine text and a value.

For example, I want to put an equation for work hours and pay. If I am paid more than I should be, I want it to read how many hours I owe my boss. But if I work more than I am paid for, I want it to read what my boss owes me (hours*Pay per Hour).

I tried the following:

=IF(A2<0,"I owe boss" abs(A2) "Hours","Boss owes me" abs(A2)*15 "dollars")

Is it possible or do I have to do it in 2 separate cells? (one for text and one for the value)

Answer:There are two ways that you can concatenate text and values. The first is by using the& characterto concatenate:

=IF(A2<0,"I owe boss " & ABS(A2) & " Hours","Boss owes me " & ABS(A2)*15 & " dollars")

Or the second method is to use theCONCATENATE function:

=IF(A2<0,CONCATENATE("I owe boss ", ABS(A2)," Hours"), CONCATENATE("Boss owes me ", ABS(A2)*15, " dollars"))

MS Excel: INSTR Function (VBA)

In Excel, theINSTRfunction returns the position of the first occurrence of a substring in a string.

The syntax for theINSTRfunction is:

InStr( [start], string, substring, [compare] )

startis optional. It is the starting position for the search. If this parameter is omitted, the search will begin at position 1.

stringis the string to search within.

substringis the substring that you want to find.

compareis optional. It is the type of comparison to perform. It can be one of the following values:

VBA Constant / Value / Explanation
vbUseCompareOption / -1 / Uses option compare
vbBinaryCompare / 0 / Binary comparison
vbTextCompare / 1 / Textual comparison
vbDatabaseCompare / 2 / Comparison based on your database

Applies To:

Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function:

VBA function (VBA)

VBA Function Example:

TheINSTRfunction can only be used in VBA code. Here are some examples of what theINSTRfunction would return:

InStr(1, "Tech on the Net", "the") / would return 9
InStr("Tech on the Net", "the") / would return 9
InStr(10, "Tech on the Net", "t") / would return 15

For example:

Dim LPosition As Integer

LPosition = InStr(10, "Tech on the Net", "t")

In this example, the variable called LPosition would now contain the value 15.

MS Excel: INSTRREV Function (VBA)

In Excel, theINSTRREVfunction returns the position of the first occurrence of a string in another string, starting from the end of the string. This is similar to theINSTR functionwhich returns the position of the first occurrence, starting from the beginning of the string.

The syntax for theINSTRREVfunction is:

InStrRev ( string, substring [, start [ , compare] ] )

stringis the string to search within.

substringis the substring that you want to find.

startis optional. It is the starting position for the search. If this parameter is omitted, the search will begin at position -1 which is the last character position.

compareis optional. It is the type of comparison to perform. It can be one of the following values:

Note:

Ifstring2is not found withinstring_being_searched, theINSTRREVfunction will return 0.
Ifstring_being_searchedis zero-length, theINSTRREVfunction will return 0.
Ifstring_being_searchedis null, theINSTRREVfunction will return null.
Ifstartis null, theINSTRREVfunction will return #Error.

Applies To:

Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function:

VBA function (VBA)

VBA Function Example:

TheINSTRREVfunction can only be used in VBA code. Here are some examples of what theINSTRREVfunction would return:

InStrRev ("alphabet", "a") / would return 5
InStrRev ("alphabet", "a", -1) / would return 5
InStrRev ("alphabet", "a", 1) / would return 1
InStrRev ("alphabet", "a", 2) / would return 1
InStrRev ("alphabet", "a", 3) / would return 1
InStrRev ("alphabet", "a", 4) / would return 1
InStrRev ("alphabet", "a", 5) / would return 5
InStrRev ("alphabet", "a", 6) / would return 5
InStrRev ("alphabet", "a", 7) / would return 5
InStrRev ("alphabet", "a", 8) / would return 5
InStrRev ("alphabet", "a", 9) / would return 0

For example:

Dim LPosition As Integer

LPosition = InStrRev ("alphabet", "a")

In this example, the variable called LPosition would now contain the value 5.

MS Excel: LCASE Function (VBA)

In Excel, theLCASEfunction converts a string to lower-case.

The syntax for theLCASEfunction is:

LCase( text )

Textis the string that you wish to convert to lower-case.

Applies To:

Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function:

VBA function (VBA)

VBA Function Example:

TheLCASEfunction can only be used in VBA code. Here are some examples of what theLCASEfunction would return:

LCase("ALPHABET") / would return "alphabet"
LCase("Tech on the Net") / would return "tech on the net"
LCase("124ABC") / would return "124abc"

For example:

Dim LResult As String

LResult = LCase("This is a TEST")

In this example, the variable called LResult would now contain the value "this is a test".

MS Excel: LEFT Function (WS, VBA)

In Excel, theLEFTfunction allows you to extract a substring from a string, starting from the left-most character.

The syntax for theLEFTfunction is:

LEFT( text, [number_of_characters] )

textis the string that you wish to extract from.

number_of_charactersis optional. It indicates the number of characters that you wish to extract starting from the left-most character. If this parameter is omitted, only 1 character is returned.

Applies To:

Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function:

Worksheet function (WS)
VBA function (VBA)

VBA Function Example:

TheLEFTfunction can also be used in VBA code. For example:

Dim LResult As String

LResult = Left("Alphabet",3)

The variable LResult would now contain the value of "Alp".

MS Excel: LEN Function (WS, VBA)

In Excel, theLENfunction returns the length of the specified string.

The syntax for theLENfunction is:

LEN( text )

textis the string to return the length for.

Applies To:

Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function:

Worksheet function (WS)
VBA function (VBA)

VBA Function Example:

TheLENfunction can also be used in VBA code. For example:

Dim LResult As Long

LResult = Len ("

In this example, the variable called LResult would now contain the value 20.

MS Excel: LTRIM Function (VBA)

In Excel, theLTRIMfunction removes leading spaces from a string.

The syntax for theLTRIMfunction is:

LTRIM( text )

textis the string that you wish to remove leading spaces from.

Applies To:

Excel, 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function:

VBA function (VBA)

VBA Function Example:

TheLTRIMfunction can only be used in VBA code. Here are some examples of what theLTRIMfunction would return:

LTRIM(" Tech on the Net") / would return "Tech on the Net"
LTRIM(" Alphabet") / would return "Alphabet"
LTRIM(" Alphabet ") / would return "Alphabet "

For example:

Dim LResult As String

LResult = LTrim(" Alphabet ")

The variable LResult would now contain the value of "Alphabet ".

MS Excel: MID Function (WS, VBA)

In Excel, theMIDfunction extracts a substring from a string (starting at any position).

The syntax for theMIDfunction is:

MID( text, start_position, number_of_characters )

textis the string that you wish to extract from.

start_positionindicates the position in the string that you will begin extracting from. The first position in the string is 1.

number_of_charactersindicates the number of characters that you wish to extract.

Applies To:

Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function:

Worksheet function (WS)
VBA function (VBA)

VBA Function Example:

TheMIDfunction can also be used in VBA code. For example:

Dim LResult As String

LResult = Mid("Alphabet", 5, 2)

The variable LResult would now contain the value of "ab".

MS Excel: REPLACE Function (VBA)

In Excel, theREPLACEfunction replaces a sequence of characters in a string with another set of characters. Please note that theworksheet version of the REPLACE functionhas different syntax.

The syntax for theREPLACEfunction is:

Replace ( string1, find, replacement, [start, [count, [compare]]] )

string1is the string to replace a sequence of characters with another set of characters.

findis the string that will be searched for instring1.

replacementwill replacefindinstring1.

startis optional. This is the position instring1to begin the search. If this parameter is omitted, theREPLACEfunction will begin the search at position 1.

countis optional. This is the number of occurrences to replace. If this parameter is omitted, theREPLACEfunction will replace all occurrences offindwithreplacement.

compareis optional. This can be one of the following values:

Parameter Value / Description
vbBinaryCompare / Binary comparison
vbTextCompare / Textual comparison
vbDatabaseCompare / Performs a comparison based on information in your database

Applies To:

Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function:

VBA function (VBA)

VBA Function Example:

TheREPLACEfunction can be used in VBA code. Here are some examples of what theREPLACEfunction would return:

Replace("alphabet", "bet", "hydro") / would return "alphahydro"
Replace ("alphabet", "a", "e") / would return "elphebet"
Replace("alphabet", "a", "e", 2) / would return "lphebet"
Replace("alphabet", "a", "e", 1, 1) / would return "elphabet"

For example:

Dim LResult As String

LResult = Replace("alphabet", "a", "e")

In this example, the variable called LResult would now contain the value "elphebet".

MS Excel: RIGHT Function (WS, VBA)

In Excel, theRIGHTfunction extracts a substring from a string starting from the right-most character.

The syntax for theRIGHTfunction is:

RIGHT( text, [number_of_characters] )

textis the string that you wish to extract from.

number_of_charactersis optional. It indicates the number of characters that you wish to extract starting from the right-most character. If this parameter is omitted, only 1 character is returned.

Applies To:

Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function:

Worksheet function (WS)
VBA function (VBA)

VBA Function Example:

TheRIGHTfunction can also be used in VBA code. For example:

Dim LResult As String

LResult = Right("Alphabet",3)

The variable LResult would now contain the value of "bet".

MS Excel: RTRIM Function (VBA)

In Excel, theRTRIMfunction removes trailing spaces from a string.

The syntax for theRTRIMfunction is:

RTrim( text )

textis the string that you wish to remove trailing spaces from.

Applies To:

Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function:

VBA function (VBA)

VBA Function Example:

TheRTRIMfunction can only be used in VBA code. Here are some examples of what theRTRIMfunction would return:

RTrim("Tech on the Net ") / would return "Tech on the Net"
RTrim(" Alphabet ") / would return " Alphabet"

For example:

Dim LResult As String

LResult = RTrim(" Alphabet ")

The variable LResult would now contain the value of " Alphabet".

MS Excel: SPACE Function (VBA)

In Excel, theSPACEfunction returns a string with a specified number of spaces.

The syntax for theSPACEfunction is:

Space( number )

numberis the number of spaces to be returned.

Applies To:

Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function:

VBA function (VBA)

VBA Function Example:

TheSPACEfunction can only be used in VBA code. Here are some examples of what theSPACEfunction would return:

Space(3) / would return " "
Space(7) / would return " "

For example:

Dim LResult As String

LResult = Space(5)

In this example, the variable called LResult would now contain the value " ".

MS Excel: STR Function (VBA)

In Excel, theSTRfunction returns a string representation of a number.

The syntax for theSTRfunction is:

Str( number )

numberis the value to convert to a string.

Applies To:

Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function:

VBA function (VBA)

VBA Function Example:

TheSTRfunction can only be used in VBA code. Here are some examples of what theSTRfunction would return:

STR(12) / would return "12"
STR(450) / would return "450"

For example:

Dim LResult As String

LResult = Str(16)

In this example, the variable called LResult would now contain the value "16".

MS Excel: STRCONV Function (VBA)

In Excel, theSTRCONVfunction returns a string converted as specified.

The syntax for theSTRCONVfunction is:

StrConv ( text, conversion, LCID )

textis the string that you wish to convert.

conversionis the type of conversion to perform. The following is a list of valid parameters forconversion.

Parameter / Value / Description
vbUpperCase / 1 / Converts the string to all uppercase.
vbLowerCase / 2 / Converts the string to all lowercase.
vbProperCase / 3 / Converts the first letter to every word to uppercase. All other characters are left as lowercase. This option is similar to the InitCap function in Oracle.
vbUnicode / 64 / Converts the string to Unicode.
vbFromUnicode / 128 / Converts the string from Unicode to the default code page of the system.

LCIDis optional. If this parameter is omitted, theSTRCONVfunction assumes the system LocaleID.

Applies To:

Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function:

VBA function (VBA)

VBA Function Example:

TheSTRCONVfunction can only be used in VBA code. Here are some examples of what theSTRCONVfunction would return:

StrConv("tech on the net", 1) / would return "TECH ON THE NET"
StrConv("TECH ON THE NET", 2) / would return "tech on the net"
StrConv("TECH ON THE NET", 3) / would return "Tech On The Net"

For example:

Dim LResult As String

LResult = StrConv("TECH ON THE NET", vbProperCase)

In this example, the variable called LResult would now contain the value "Tech On The Net".

MS Excel: TRIM Function (WS, VBA)

In Excel, theTRIMfunction returns a text value with the leading and trailing spaces removed.

The syntax for theTRIMfunction is:

TRIM( text )

textis the text value to remove the leading and trailing spaces from.

Applies To:

Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function:

Worksheet function (WS)
VBA function (VBA)

VBA Function Example:

TheTRIMfunction can also be used in VBA code. For example:

Dim LResult As String

LResult = Trim (" Alphabet ")

The variable LResult would now contain the value of "Alphabet".

MS Excel: UCASE Function (VBA)

In Excel, theUCASEfunction converts a string to all upper-case.

The syntax for theUCASEfunction is:

UCase( text )

textis the string that you wish to convert to upper-case.

Applies To:

Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function:

VBA function (VBA)

VBA Function Example:

TheUCASEfunction can only be used in VBA code. Here are some examples of what theUCASEfunction would return:

UCase("Tech on the Net") / would return "TECH ON THE NET"
UCase("Alphabet") / would return "ALPHABET"

For example:

Dim LResult As String

LResult = UCase("This is a TEST")

In this example, the variable called LResult would now contain the value "THIS IS A TEST".