VBA UBOUND

Excel VBA UBOUND Function

UBOUND stands for Upper Bound in VBA. VBA UBOUND is listed under the array function library. It retrieves the upper limit (largest subscript) of an array, and the output of this function is always a numerical value.

In VBA, we often work with arrays and use the array variable name to loop through and perform a set of tasks. When we use an array variable in VBA programming, eventually, we need to loop through the array variable. So, we need to be sure of the maximum length of the array, so the UBOUND function will help us to find the maximum length of the array variable.

For example, consider the array variable MyArray(20).

Here, the size of the array is 20. In VBA, the array length starts from 0. Hence, eventually, we will have 21 array values. The UBOUND function will return a maximum size of 20.

Syntax Of UBOUND In Excel VBA

Excel VBA UBOUND function is very simple, let’s look at the following syntax of the UBOUND function in VBA.

UBOUND(ArrayName, [,Dimension])

Array Name – In this argument, we need to enter the array name that we have defined to store our values.

[Dimension] – In this argument, we can specify the dimension of the array. This is an optional argument, so if we ignore this, it will take one dimension by default.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How To Use UBOUND In VBA?

Let us look at the simple example of the UBOUND function to understand the functionality better. Follow the steps listed below.

Step 1 – Start the new sub-procedure in the Visual Basic Editor window by naming the macro.

Step 2 – Define a variable as an array variable. Give the array size 0 to 4 and assign the data type as a string.

Step 3 – Assign city names for the array variable. Assign 5 cities for the array variable starting from array position 0.

Step 4 – Since we have assigned the array size from 0 to 4, we know that the upper limit of the array is 4. However, let’s find the upper limit by using VBA UBOUND function.

Define another variable to hold the result of the UBOUND function. The output of the UBOUND function is numerical; hence, assign a LONG data type for the variable.

Step 5 – Enter this new variable name and enter an equal sign followed by the UBOUND function.

Step 6 – For the UBOUND function, give the array variable name i.e., ArrayLength.

Step 7 – Finally, use the message box and show the result of the variable ArrayLength.

Sub Example_UBOUND()

Dim CityNames(0 To 4) As String

CityNames(0) = “London”
CityNames(1) = “Melbourne”
CityNames(2) = “Sydney”
CityNames(3) = “Colombo”
CityNames(4) = “Durban”

Dim ArrayLength

ArrayLength = UBound(CityNames)

MsgBox ArrayLength

End Sub

Let’s run the code by pressing the F5 key, and we will see the following result in a message box.

The length of the variable is 4 (but holds 5 values because the position starts from 0), and the UBOUND function returns the same.

Examples

Example #1 – Return The Final Value Of The Array Variable

When we use an array variable, it is important to find the upper limit of the array variable. Let’s take the same code from the previous example.

Sub Example_UBOUND()

Dim CityNames(0 To 4) As String

CityNames(0) = “London”
CityNames(1) = “Melbourne”
CityNames(2) = “Sydney”
CityNames(3) = “Colombo”
CityNames(4) = “Durban”

Dim ArrayLength As Long

ArrayLength = UBound(CityNames)

MsgBox ArrayLength

End Sub

Using this code, we will find what is the last value assigned to the array variable CityNames. The code is almost the same, but we need to tweak only the message box line.

We have changed only the yellow highlighted line from the previous code. We are using the same array variable name in the message along with the UBOUND function.

This code will return the last value of the array variable CityNames.

If we look at the code, the last assigned value of the array value is Durban. Hence, the code is returned the same.

How Does It Work?

When we give the array variable name along with the position of the array variable, it returns the value assigned to the array variable in that position.

For example, CityNames(1) will return the city name Melbourne because CityNames 1 holds this city name.

Similarly, to return the last value of the array variable, we have used the VBA UBOUND function, and UBOUND returns the highest size of the array variable i.e., 4. The 4^th positioned value of the array variable is “Durban”.

Example #2 – Loop Through All The Values Of The Array Variable

Now, let us look at how to loop all the values of the array variable. We will use the same code from the previous example here as well.

Once the values are assigned to the array variable, we will store these values into cells using the FOR NEXT loop.

Sub Example1_UBOUND()

Dim CityNames(0 To 4) As String

CityNames(0) = “London”
CityNames(1) = “Melbourne”
CityNames(2) = “Sydney”
CityNames(3) = “Colombo”
CityNames(4) = “Durban”

Dim k As Long
Dim j As Long

j = 1

For k = LBound(CityNames) To UBound(CityNames)

Cells(j, 1).Value = CityNames(k)
j = j + 1
Next k

End Sub

We have used the FOR NEXT loop to loop through all the values of the array variable. When we designed the FOR NEXT loop, we used the LBOUND and UBOUND in VBA functions to find the starting and ending point of the array variable.

This will store all the values to cells of the worksheet like the following image.

Example #3 – Select The Data Range And Copy To Another Range

For example, look at the following data in Excel.

We need to copy the data from this table to another worksheet and paste it there. Look at the following code.

Sub Example2_UBOUND()

Dim DataRng() As Variant

DataRng = Range(“A1”, Range(“A1”).End(xlToRight).End(xlDown))

Worksheets.Add

Range(ActiveCell, ActiveCell.Offset(UBound(DataRng, 1) – 1, UBound(DataRng, 2) – 1)) = DataRng

End Sub

Code Explanation:

Line 1 – We have defined the variable DataRng with the variant data type.

Line 2 – For this variable, we assign the data range starting from cell A1.

Line 3 – Next, we are adding the new worksheet.

Line 4 – Using the UBOUND function, we find the last used cell of the range, and we offset those many cells using the OFFSET function and setting the value equal to the DataRng variable.

This will copy the above data to the new worksheet without any formatting.

Example #4 – Split Names Into Array

Now, we will look at the example of splitting names using the array and UBOUND function. We have a list of names combined together with the common delimiter (;) colon.

Andrew;Jamiew;Angelo;Tyler;Simon

First, define a variable as an array variable.

For this variable name, use the SPLIT function and give the above names as shown in the following image.

Define another variable to loop through all these names.

Enter the FOR NEXT LOOP along with the LBOUND and UBOUND in VBA functions to decide the length of the loop.

Sub Split_Name()

Dim Names() As String

Names = Split(“Andrew;Jamiew;Angelo;Tyler;Simon”, “;”)

Dim k As Long
Dim j As Long

j = 1

For k = LBound(Names) To UBound(Names)

  Cells(j, 1).Value = Names(k)

j = j + 1
Next k

End Sub

This will store the names in cells, as shown in the below image.

In this way, by using the UBOUND function, we can read through array variable values.

Important Things To Note

• UBOUND stands for Upper Bound in VBA.
• VBA UBOUND is listed under the array function library.
• Array position starts from position 0, so when we need to store the values from the array variable, we need to define a new variable and increment it by one whenever the loop runs.
• When the array is multi-dimensional, we need to specify the array dimension for the UBOUND function.
• To find the lower position of the array, we need to use the LBOUND function.

Frequently Asked Questions

Download Template

This article must be helpful to understand the VBA UBOUND, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA UBOUND. Here we explain how to Use UBOUND in Excel VBA with examples and downloadable excel template. You may learn more from the following articles –

• VBA Macros
• Add-ins In Excel
• Analysis ToolPak In Excel