Excel VBA functions

1.1. How to use the ARRAY function

The ARRAY function creates a Variant variable containing array values.

1.1.1 Array Function VBA Syntax

Array(arglist)

Back to top

1.1.2 Array Function Arguments

arglist

A list of values. Text strings must have a beginning and ending double quote. The values must be separated by a comma. If omitted a zero-length array is created.

Back to top

1.1.3 Array Function example

'Name macro
Sub Macro1()

'Populate array variable
MyArray = Array("Cat", "Dog", "Rabbit")

'For Next statement using lower and upper boundaries of the array variable
For i = LBound(MyArray) To UBound(MyArray)

'Save array value to variable txt and a new line
txt = txt & MyArray(i) & vbNewLine

'Continue with next value
Next i

'Show message box containing contents of variable txt
MsgBox txt

'Exit macro
End Sub

The macro above populates variable MyArray with values Cat, Dog and Rabbit.

A FOR ... NEXT statement loops through the array variable using the Lbound and Ubound function to determine the lower and upper limit of the array.

An ampersand then concatenates the values and lastly, the msgbox shows the concatenated string.

Back to top

1.1.4 Alternative way to populate an array variable

The following macro demonstrates another way to create an array variable. [arglist] means evaluate.

'Name macro
Sub Macro2()

'Populate array variable using brackets and curly brackets
MyArray = [{"Cat", 5, "Rabbit"}]

'For Next statement using lower and upper boundaries of the array variable
For i = LBound(MyArray) To UBound(MyArray)

    'Save array value to variable txt and a new line
    txt = txt & MyArray(i) & vbNewLine

'Continue with next value
Next i

'Show message box containing contents of variable txt
MsgBox txt

'Exit macro
End Sub

Back to top

1.1.5 Populate a two-dimensional array variable

This macro creates array variable MyArray with values in two dimensions.

'Name macro
Sub Macro3()

'Populate array variable using two Array functions
MyArray = Array(Array("Cat", "Dog"), Array("Rabbit", "Squirrel"))

'For Next statement for rows
For r = 0 To 1

    'For Next statement for columns
    For c = 0 To 1

        'Save array value to variable txt and a new line
        txt = txt & MyArray(r)(c) & " "
    'Continue with next value
    Next c

    'Add a new line to variable txt
    txt = txt & vbNewLine

'Continue with next value
Next r

'Show message box containing contents of variable txt
MsgBox txt

'Exit macro
End Sub

Back to top

1.1.6 Alternative way to populate a two-dimensional array variable

This macro populates array variable MyArray with values in two dimensions using the evaluate characters.

'Name macro
Sub Macro4()

'Populate array variable using brackets and curly brackets
MyArray = [{"Cat", "Dog"; "Rabbit", "Squirrel"}]

'For Next statement for rows using lower and upper boundaries
For r = LBound(MyArray, 1) To UBound(MyArray, 1)

    'For Next statement for columns using lower and upper boundaries
    For c = LBound(MyArray, 2) To UBound(MyArray, 2)

        'Save array value to variable txt and a new line
        txt = txt & MyArray(r, c) & vbNewLine

    'Continue with next value
    Next c

'Continue with next value
Next r

'Show message box containing contents of variable txt
MsgBox txt

'Exit macro
End Sub

Back to top

1.2. How to use the FIX function

The FIX function removes the decimals from the argument.

Excel Function VBA Syntax

Fix(number)

Arguments

number

Required. Is a Double or any valid numeric expression. If number is 0 (zero) then 0 (zero) is returned.

Comments

I used the following macro to show how the Fix function behaves with different arguments.

Sub Macro1()
   For Each cell In Range("B3:B12")
       cell.Offset(, 1) = Fix(cell.Value)
    Next cell
End Sub

Formula equation

Fix(number) = Sgn(number) * Int(Abs(number))

1.3. How to use the INT function

The INT function removes the decimals if the numeric value is above 0 (zero) and returns a negative integer less than or equal to the given argument.

The image above demonstrates how the INT function works. The fractional part of a positive number is removed leaving only the positive integer, example: 10.9 => 10, however, a negative number, example -10.3 is converted to the negative integer that is less than or equal to the argument. -10.3 => -11.

INT Function VBA Syntax

Int ( number )

INT Function Arguments

number

Required. Is a Double or any valid numeric expression. If number is 0 (zero) then 0 (zero) is returned.

INT Function example

I used the following macro to show how the INT function behaves with different arguments.

Sub Macro1()
   For Each cell In Range("B3:B12")
       cell.Offset(, 1) = Int(cell.Value)
    Next cell
End Sub

1.4. How to use the JOIN function

The JOIN function concatenates a set of substrings in an array, the image shows a user-defined function entered in cell C11.

Join Function VBA Syntax

JOIN(sourcearray, [delimiter])

Join Function Arguments

sourcearray	Required. A one-dimensional array.
[delimiter]	Optional. A delimiting character, the default value is " " (space character).

Join Function Example

The following user-defined function demonstrates the JOIN function.

Function JoinStrings(rng As Range, del As String)

rng1 = Application.Transpose(rng.Value)
JoinStrings = Join(rng1, del)

End Function

The JoinStrings function lets you use two arguments, the first one being a cell range and the second one a text string. The JOIN function concatenates the substrings in array variable rng1 using the delimiting character found in variable del.

1.5. How to use the LBOUND and UBOUND functions

The Lbound and Ubound functions calculate the size of of an array. The Lbound returns the lower limit of an array and the Ubound function returns the upper limit.

Sub Macro4()

Rng = Range("D2:F6").Value

MsgBox "Lower bound(1): " & LBound(Rng, 1) & vbNewLine & _
        "Upper bound(1): " & UBound(Rng, 1) & vbNewLine & _
        "Lower bound(2): " & LBound(Rng, 2) & vbNewLine & _
        "Upper bound(2): " & UBound(Rng, 2)

End Sub

The macro above populates variable Rng with values from range D2:F6, the Lbound and Ubound functions then return the lower and upper limits for the first dimension (rows) and the second dimension (columns).

LBOUND and UBOUND functions VBA Syntax

LBound( arrayname,  [dimension] )

UBound( arrayname,  [dimension] )

LBOUND and UBOUND functions Arguments

arrayname	Required. The array variable.
[dimension]	Optional. Which dimension to use, the default value is 1.

LBOUND and UBOUND functions Example

The following macro creates array variable  TestArray with one dimension, lower limit of 1 and upper limit of 3.

Sub Macro1()

Dim TestArray(1 To 3)
MsgBox LBound(TestArray) & vbNewLine & UBound(TestArray)

End Sub

This macro creates array variable TestArray with two dimensions, the first dimension has the lower limit of 1 and an upper limit of 3, the second dimension has the lower limit of 1 and the upper limit of 10.

Sub Macro2()

Dim TestArray(1 To 3, 1 To 10)
MsgBox "Lower bound(1): " & LBound(TestArray, 1) & vbNewLine & _
        "Upper bound(1): " & UBound(TestArray, 1) & vbNewLine & _
        "Lower bound(2): " & LBound(TestArray, 2) & vbNewLine & _
        "Upper bound(2): " & UBound(TestArray, 2)
        
End Sub

This macro populates array variable TestArray with values from cell range B3:B8, the first dimension has the lower limit of 1 and an upper limit of 6, the second dimension has the lower limit of 1 and the upper limit of 1.

Sub Macro3()

Rng = Range("B3:B8").Value
MsgBox "Lower bound(1): " & LBound(Rng, 1) & vbNewLine & _
        "Upper bound(1): " & UBound(Rng, 1) & vbNewLine & _
        "Lower bound(2): " & LBound(Rng, 2) & vbNewLine & _
        "Upper bound(2): " & UBound(Rng, 2)

End Sub

1.6. How to use the SGN function

The SGN function returns an integer that shows the sign of the number.

Argument	SGN returns
Number greater than 0 (zero).	1
Number equal to 0 (zero).	0
Number less than 0 (zero).	-1

Excel Function VBA Syntax

SGN(number)

Arguments

number

Required. Any valid numeric expression. If number is 0 (zero) then 0 (zero) is returned.

Comments

I used the following macro to show how the SGN function behaves with different numerical values demonstrated in the picture above.

Sub Macro1()
   For Each cell In Range("B3:B12")
       cell.Offset(, 1) = SGN(cell.Value)
    Next cell
End Sub

 

1.7. How to use the SPLIT function

The picture above shows a user-defined function (UDF) that splits the string in cell C3 using a delimiting character ",". It then returns an array of values to cell range D3:F3.

The SPLIT function is a function you can use in Visual Basic for Applications, abbreviation VBA.

It returns a one-dimensional array containing substrings from a divided string based on a delimiting character.

The returning array is zero-based meaning the first substring starts with index 0 (zero), example below.

Array(0) = "a"
Array(1) = "b"
Array(2) = "c"
Array(3) = "d"

1.7.1 SPLIT Function Syntax - VBA

Split(expression ,[delimiter], [limit], [compare])

1.7.2 SPLIT Function Arguments - VBA

expression	Required. A string you want to split.
[delimiter]	Optional. The delimiting character, default value is " ", in other words, a space character.
[limit]	Optional. How many substrings to be returned, default value is all substrings.
[compare]	Optional. A numeric value determining how Excel compares when processing.

The following table shows the numeric values you can use in the compare parameter above.

Constant	Value	Description
vbUseCompareOption	-1	Comparison using the setting of the Option Compare statement.
vbBinaryCompare	0	Binary comparison
vbTextCompare	1	Textual comparison
vbDatabaseCompare	2	Microsoft Access only. Performs a comparison based on information in your database.

1.7.3 Example 1

The following UDF is demonstrated in the picture above in cell C3, it uses "|" to split the string in cell B3. The substrings are then concatenated with a space character between substrings. The returning value is shown in cell C3.

'Name User Defined Function and arguments
Function SplitValues(rng As Range)

'Dimension variables and declare data types
Dim Arr() As String
Dim Str As Variant
Dim result As String

'Split value in variable Rng using delimiting character |
Arr = Split(rng, "|")

'Iterate through each substring
For Each Str In Arr

    'Concatenate each substring and save to variable result
    result = result & Str & " "
Next

'Return variable result to User Defined Function on worksheet
SplitValues = Trim(result)

End Function

Back to top

1.7.4 Example 2

The second UDF is shown in cell C4, it does the same thing as the first UDF above except it returns the last substrings first, in other words, substrings are concatenated backward.

Function SplitValues1(rng As Range)
Dim Arr() As String
Dim Str As Variant
Dim result As String
Dim i As Integer

Arr = Split(rng, "|")

For i = UBound(Arr) To 0 Step -1
    result = result & Arr(i) & " "
Next i

SplitValues1 = Trim(result)

End Function

Back to top

1.7.5 Split cell value using a delimiting character

The image above demonstrates a UDF in cell C1 that splits the cell value in cell A1 to substrings using a delimiting character.

The first argument is the delimiting character or characters, the second argument is a cell reference to a cell. The UDF is entered as an array formula if you use an Excel version that is older than Excel 365.

'Name User Defined Function and arguments
Function SplitValues(a As String, b As String)

'Dimension variables and declare data types
Dim Text() As String

'Split variable b using variable a as the delimiting character, save to variable Text
Text = Split(b, a)

'Return output stored in variable Text to User Defined Function
SplitValues = Text
End Function

Back to top

1.7.5.1 How to use it

1. Select cell range C1:E1.
2. Type =SplitValues(",",A1) in the formula bar.
3. Press and hold CTRL + SHIFT simultaneously.
4. Press Enter once.
5. Release all keys.

If you did this right, the formula has now a leading { and an ending }, like this {=SplitValues(",",A1)}. They appear automatically, don't enter these characters yourself.

Back to top

1.7.5.2 Explaining the user-defined function

Function name and arguments

A user defined function procedure always start with "Function" and then a name. This udf has two arguments, a and b. Both a and b are strings.

Function SplitValues(a As String, b As String)

Declaring variables

Dim Text() As String

Text() is a dynamic string array. Read more about Defining data types.

Split function

Text = Split(b, a)

The Split function accepts a text string and returns a zero-based, one-dimensional array containing all sub strings. Split allows you also to specify a delimiting character, default is the space character. The substrings are stored as multiple strings in Text array.

The udf returns the substrings in Text

SplitValues = Text

End a udf

End function

A function procedure ends with the "End function" statement.

Back to top

1.7.5.3 Where to put the code?

To build a user-defined function, follow these steps:

1. Press Alt + F11 to open the visual basic editor
2. Press with left mouse button on "Insert" on the menu
3. Press with left mouse button on "Module"
4. Copy the code above and paste it to the code module.

Back to top

2.1 How to use the WITH ... END WITH statement

The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property.

The picture above shows a macro that changes a few properties of the Range Object.

Statement Syntax

With object
[ code]
End With

The following macro changes the cells in cell range B2:B4 to bold, indents the text and changes the column width to 15.

Sub Macro1()
With Range("B2:B4")
    .Font.Bold = True
    .InsertIndent 1
    .ColumnWidth = 15
End With
End Sub

2.2 How to use the SET statement

The SET statement allows you to save an object reference to a variable, the image above demonstrates a macro that assigns a range reference to a range object.

For example, the DIM statement (among others) only declare a variable but it will be empty, nothing is referred until you assign an object using the SET statement.

What is an Excel object? It can be a chart, worksheet, a cell range or a shape among other things basically. Each object has usually a number of properties that you may change, for example, a worksheet may be hidden by changing the visible property to False.

The following macro is used in the above example.

'Name macro
Sub Macro1()

'Declare variable
Dim rng As Range

'Assign object
Set rng = Worksheets("Sheet1").Range("B2:C9")

'Show object address
MsgBox rng.Address

End Sub

Excel Statement Syntax

Set objectvar = {[ New ] objectexpression | Nothing }

Syntax Explained

Excel Name	Description
objectvar	The name of the object variable.
New	Optional. Is used to create an object.
objectexpression	A name of an object or another object variable with the same object type.
Nothing	Deletes object. Recommended if an object holds a lot of memory that you don't need anymore.

Comments

How can I create a new worksheet and assign it to an object variable?
The following macro creates a new worksheet and then shows the worksheet name using the assigned object.

Sub Macro2()

Set obj = Sheets.Add
MsgBox obj.Name

End Sub

2.3 How to use the GOTO statement

The image above demonstrates the GoTo statement. It "jumps" or "navigates" to another place in the code, in this case, "Start".

VBA code

Sub HowToGoTo()

a = 1
Start:

    MsgBox Range("B2:B4").Cells(a)
    If a = 3 Then Exit Sub
    a = a + 1

GoTo Start

End Sub

Explaining subroutine

1. The subroutine begins with variable a setting it equal to 3.
2. Start: is a label which the GoTo statement use in order to know where to "jump".
3. The message box appears and shows the value in cell range B2:B4 based on what variable a contains.
4. The IF THEN statement checks if variable a is equal to 3 and exits the subroutine if the condition is met.
5. Variable a is added with number 1.
6. The GoTo statement makes the code "jump" to label "Start:"
7. The subroutine is repeated until a is equal to 3.

The GoTo statement is mostly used in error handling techniques. I recommend using For Next, Do While or Do Loop since they make your code easier to follow and understand.

2.4 How to use SELECT CASE statement

The SELECT CASE statement allows you to compare an expression to multiple values.

It is similar to the IF THEN ELSE statement except that the IF THEN ELSE statement can evaluate multiple expressions. The SELECT CASE statement evaluates one expression.

2.4.1. SELECT CASE statement Syntax

Select Case testexpression

[ Case expressionlist-n [ statements-n ]]
[ Case Else [ elsestatements ]]

End Select

Back to top

2.4.2. SELECT CASE statement Parameters

testexpression	Required. Any expression: numeric expression - You can use variables, constants, keywords, and operators. The result is a number. string expression - Any expression that returns a string.
expressionlist-n	Required. True - ignore empty cells in the third argument. False - adds empty cells to the result.
statements-n	Required. The cell range you want to concatenate.
elsestatements	Optional. Up to 254 additional cell ranges.

Back to top

2.4.3. SELECT CASE statement example 1

The User Defined Function above compares the score to several criteria and returns the corresponding grade.

The UDF is entered in column D and takes the score from column C and returns the grade.

UDF in cell D3:

VBA Code

Function Grade(score)

Select Case score
Case Is < 60
Grade = "F"
Case 61 To 64
Grade = "D"
Case 65 To 69
Grade = "D+"
Case 70 To 74
Grade = "C"
Case 75 To 79
Grade = "C+"
Case 80 To 84
Grade = "B"
Case 85 To 89
Grade = "B+"
Case 90 To 94
Grade = "A"
Case Else
Grade = "A+"
End Select

End Function

Back to top

2.4.4. SELECT CASE statement example 2

Formula in cell D3:

VBA Code

Function PriceSize(size)

Select Case size
    Case Is = "Small"
        PriceSize = 100
    Case Is = "Medium"
        PriceSize = 120
    Case Is = "Large"
        PriceSize = 135
    End Select
    
End Function

2.4.5. SELECT CASE statement - VBA alternative

Formula in cell D3:

VBA Code

Function Grade1(score)

If score < 60 Then
    Grade1 = "F"
    
ElseIf score <= 64 And score >= 61 Then
    Grade1 = "D"
    
ElseIf score <= 65 And score >= 69 Then
    Grade1 = "D+"
    
ElseIf score <= 74 And score >= 70 Then
    Grade1 = "C"

ElseIf score <= 79 And score >= 75 Then
    Grade1 = "C+"
    
ElseIf score <= 84 And score >= 80 Then
    Grade1 = "B"

ElseIf score <= 89 And score >= 85 Then
    Grade1 = "B+"

ElseIf score <= 94 And score >= 90 Then
    Grade1 = "A"

Else
    Grade1 = "A+"
    
End If

End Function

2.4.6. SELECT CASE statement - Excel Function alternative

Formula in cell D3:

Get Excel *.xlsm file

How to use SELECT CASE statement.xlsm

3.1 How to use the EVALUATE method

The Evaluate method converts an Excel name to an object or value.

The picture above demonstrates a macro that uses the Evaluate method to sum numbers in cell range B3:B10.

Excel Name	Description
A1-style reference	Excel handles references as absolute references.
Range	The following characters are valid: Range (colon) Intersect (space) Union (comma)
Excel defined name	Any name.
External reference	You are allowed to refer to a cell in another workbook.
Chart object	Any chart object name.

3.1.1 EVALUATE Method Syntax

expression.Evaluate( Name )

3.1.2 EVALUATE Arguments

Name	Required. The object name, data type: Variant.

3.1.3 EVALUATE example

Square brackets are the same as using the Evaluate method with a string parameter, examples:

Evaluate	Square brackets
Evaluate("D4").Value = 10	[D4].Value = 10
Evaluate("MATCH(1,{2,3,4,1},0)")	[MATCH(1,{2,3,4,1},0)]

4.1 How to use the RANGE.OFFSET property

The OFFSET property returns a Range object based on a row and column number. The image above shows a macro that uses cell E7 and applies an offset with 6 rows and 4 columns. The Msgbox displays the address of the Range object.

Excel Property Syntax

expression.Offset(RowOffset, ColumnOffset)

Arguments

expression	Required. Range object.
RowOffset	Optional. How many rows the range object is to be offset. If omitted 0 (zero) is used. The argument may be a positive or negative value.
ColumnOffset	Optional. How many columns the range object is to be offset. If omitted 0 (zero) is used. The argument may be a positive or negative value.

Comments

It is possible to offset a cell range containing multiple cells as well. The image below demonstrates the OFFSET property with a ColumnOffset argument of -2.

 

4.2 How to use Application.Caller property

Syntax

expression.Caller (Index)

Arguments

Name	Required/Optional	Data type	Description
Index	Optional	Variant	An index to the array. This argument is used only when the property returns an array.

The first example is not utilizing the application caller property at all, it is only there to demonstrate the difference.

Example 1

The values are returned horizontally and #N/A are returned when there are no more values to show.

User defined function

Function BasicUDF(Rng As Range)
Dim Cell As Variant
'Create an array
Dim temp() As Variant
ReDim temp(0)
'Process every cell in Range
For Each Cell In Rng
    'Check if cell is empty
    If Cell <> "" Then
        'Copy cell value to the last position in array
        temp(UBound(temp)) = Cell
        'Increase array size with 1
        ReDim Preserve temp(UBound(temp) + 1)
    End If
Next Cell
'Remove the last value in array
ReDim Preserve temp(UBound(temp) - 1)
'Return values
BasicUDF = temp
End Function

You can return values vertically by changing the last line to BasicUDF = Application.Transpose(temp). See animated gif below.

Values are transposed vertically.

Example 2

This user defined function returns values vertically and replaces #N/A with blanks.

Application.Caller.Rows.Count returns the number of rows from the cell range where you entered the udf. This makes it possible to add blanks when the udf is out of values.

User defined function

Function BasicUDF2(Rng As Range)
Dim Cell As Variant
Dim row As Single
'Create an array
Dim temp() As Variant
ReDim temp(0)
'Process every cell in Range
For Each Cell In Rng
    'Check if cell is empty
    If Cell <> "" Then
        'Copy cell value to the last position in array
        temp(UBound(temp)) = Cell
        'Increase array size with 1
        ReDim Preserve temp(UBound(temp) + 1)
    End If
Next Cell
'Add remaining blanks to array
For row = UBound(temp) To Application.Caller.Rows.Count
    temp(UBound(temp)) = ""
    ReDim Preserve temp(UBound(temp) + 1)
Next row
'Remove last blank
ReDim Preserve temp(UBound(temp) - 1)
'Transpose temp array from horizontal to vertical and return values to sheet
BasicUDF2 = Application.Transpose(temp)
End Function

Example 3

This user defined function returns values in every cell, also if you entered the user defined function in multiple columns. The remaining cells are returned as blanks. See animated gif.

Values are transposed to fill the entire selection, #N/A are replaced with blank cells.

User defined function

Function BasicUDF3(Rng As Range)
Dim Cell As Variant
Dim row As Single, i As Single
'Create an array
Dim temp() As Variant
'Dim array with same size as selection
ReDim temp(Application.Caller.Columns.Count - 1, 0)
i = 0
'Process every cell in Range
For Each Cell In Rng
    'Check if cell is empty
    If Cell <> "" Then
        'Copy cell value to the last position in array
        temp(i, UBound(temp, 2)) = Cell
        i = i + 1
        'Add a new row to the array
        If i = Application.Caller.Columns.Count Then
            i = 0
            ReDim Preserve temp(UBound(temp, 1), UBound(temp, 2) + 1)
        End If
    End If
Next Cell
'Process remaining cells in selection
Do
    'Remaining value are blanks
    temp(i, UBound(temp, 2)) = ""
    'Count current column
    i = i + 1
    'Check if current column is equal to the number of columns in selection
    If i = Application.Caller.Columns.Count Then
        'Start over at column 1 (0)
        i = 0
        'Add a new row in array
        ReDim Preserve temp(UBound(temp, 1), UBound(temp, 2) + 1)
    End If
Loop Until UBound(temp, 2) > Application.Caller.Rows.Count - 1
'Remove last row in array
ReDim Preserve temp(UBound(temp, 1), UBound(temp, 2) - 1)
'Return values
BasicUDF3 = Application.Transpose(temp)
End Function

How to create an array formula

1. Select a cell range
2. Type user defined function in formula bar
3. Press and hold Ctrl + Shift
4. Press Enter

Where do I copy the vba code?

1. Open VB Editor or press Alt+ F11
2. Press with left mouse button on "Insert" on the menu
3. Press with left mouse button on "Module"
4. Copy/Paste vba code to code module
5. Return to excel

Get excel *.xlsm file

udf basics.xlsm

More than 1300 Excel formulas

Excel categories

Comments (7)