Excel VBA functions

1. How to use the ARRAY function

The ARRAY function creates a Variant variable containing array values.

1.1 Array Function VBA Syntax

Array(arglist)

Back to top

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.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.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.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.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

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))

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

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.

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

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

 

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"

7.1 SPLIT Function Syntax - VBA

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

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.

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

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

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

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

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

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.

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.

More than 1300 Excel formulas

Excel categories

Leave a Comment