VBA UBOUND

Updated on January 2, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

UBOUND, also known as Upper Bound, is a function in VBA with its opposite function, LBOUND or Lower Bound function. This function defines the length of an array in a code. As the name suggests, UBOUND is used to define the upper limit of the array.

VBA UBOUND Function

How do you tell the maximum length of the array in Excel? Yes, we can manually see and update the maximum length of the array but if you are doing it all this while, then today is the end because we have a function called UBOUND to determine the maximum length of the array. Follow this article to learn more about the UBOUND function in Excel VBA.

UBOUND stands for Upper Bound. In coding, we may often require finding the maximum length of the array. For example, MyResult(24) means the array name MyResult holds 25 values because the array starts from zero, not from one. So, 24 means +1, i.e., a total of 25 values.

Here, the maximum length of the array is 24. Instead of supplying the array length manually, we can use the built-in function UBOUND to get the maximum length of the array.

The code is: UBOUND (MyResult), i.e., UBOUND (24)

So, the Excel VBA UBOUND function represents the upper bound of the array size.

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA UBOUND (wallstreetmojo.com)

How to use the VBA UBound Function in Excel?

The formula of VBA UBOUND is very simple because it has only two parameters to it.

UBound (Arrayname [,Dimension])
  • Array Name: This is the name of the array name we have defined. For example, in the above example, MyResult is the array name.
  • [Dimension]: If the array has more than one dimension, then we need to specify the dimension of the array. If you ignore it, it will treat the first dimension by default.

The Excel VBA UBOUND function is very useful in determining the length of the loops while running.

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

Examples of UBOUND Function in Excel VBA

Below are the practical examples of the VBA UBound function.

You can download this VBA UBound Function Template here – VBA UBound Function Template

Example #1

To start the proceedings, let me write the simple code. Then, follow the below steps to apply the VBA UBOUND function.

Step 1: Start the excel macroExcel MacroA macro in excel is a series of instructions in the form of code that helps automate manual tasks, thereby saving time. Excel executes those instructions in a step-by-step manner on the given data. For example, it can be used to automate repetitive tasks such as summation, cell formatting, information copying, etc. thereby rapidly replacing repetitious operations with a few clicks. read more and define the variable name.

Code:

Sub Ubound_Example1()
    Dim ArrayLength(0 To 4) As String
vba UBound Example 1

Step 2: We will assign values to this array name.

Code:

Sub Ubound_Example1()
  Dim ArrayLength(0 To 4) As String
  ArrayLength(0) = "Hi"
  ArrayLength(1) = "Friend"
  ArrayLength(2) = "Welcome"
  ArrayLength(3) = "to"
  ArrayLength(4) = "VBA Class"
End Sub
vba UBound Example 1-1

Step 3: Now, using a message box with the UBOUND function, we will see the maximum length of the array.

Code:

Sub Ubound_Example1()
  Dim ArrayLength(0 To 4) As String
  ArrayLength(0) = "Hi"
  ArrayLength(1) = "Friend"
  ArrayLength(2) = "Welcome"
  ArrayLength(3) = "to"
  ArrayLength(4) = "VBA Class"
  MsgBox "Upper Bound Length is: " & UBound(ArrayLength)
End Sub
vba UBound Example 1-2

Step 4: Run this code by pressing the F5 key, or you can also run the code manually, as shown in the below screenshot.

vba UBound Example 1-3

The message box will show you the upper bound number of the array.

vba UBound Example 1-4

Using the Excel VBA UBOUND function, we can get the upper bound length of an array.

Example #2 – Using the Excel VBA UBOUND Function to Copy the Data

Assume you have a list of data in one Excel sheet like the below one.

vba UBound Example 2

This data will update daily. Therefore, you must copy it to the new sheet every time it updates. Updating this manually will take considerable time in your workplace, but we will show you a simple macro code to automate this.

Step 1: Create a Macro and define the array variable.

Code:

Sub Ubound_Example2()
  Dim DataRange() As Variant
End Sub
vba UBound Example 2-1

Step 2: Now, activate the datasheet by refereeing to its name.

Code:

Sub Ubound_Example2()
    Dim DataRange() As Variant
    Sheets("Data Sheet").Activate
End Sub
vba UBound Example 2-2

Step 3: Now, assign the data range to the defined variable using the code below.

Code:

Sub Ubound_Example2()
    Dim DataRange() As Variant
    Sheets("Data Sheet").Activate
    DataRange = Range("A2", Range("A1").End(xlDown).End(xlToRight))
End Sub
vba UBound Example 2-3

Step 4: Now, add a new worksheet to the workbook.

Code:

Sub Ubound_Example2()
    Dim DataRange() As Variant
    Sheets("Data Sheet").Activate
    DataRange = Range("A2", Range("A1").End(xlDown).End(xlToRight))    
    Worksheets.Add
End Sub
Example 2-4

Step 5: Now, add the data to the newly added sheet using the Excel VBA UBOUND function in the form of the below code.

Code:

Sub Ubound_Example2()
    Dim DataRange() As Variant
    Sheets("Data Sheet").Activate
    DataRange = Range("A2", Range("A1").End(xlDown).End(xlToRight))     
    Worksheets.Add    
    Range(ActiveCell, ActiveCell.Offset(UBound(DataRange, 1) - 1, UBound(DataRange, 2) - 1)) = DataRange
End Sub
Example 2-5

The above code will offset the cells by the maximum length returned by the UBOUND function, and this range will equal the value of the array name “DataRange.

Step 6: Now, run this code. It will paste the value to the new sheet.

vba UBound Example 2-6

This code is dynamic because even when the data increases horizontally and vertically, it will automatically take the range. So, now we will add some dummy lines to the data.

Example 2-7

Now, we will once again run this code. It will now add the newly added lines as well.

vbaUBound Example 2-8

Code:

Sub Ubound_Example2()
    Dim DataRange() As Variant
    Sheets("Data Sheet").Activate
    DataRange = Range("A2", Range("A1").End(xlDown).End(xlToRight))   
    Worksheets.Add   
    Range(ActiveCell, ActiveCell.Offset(UBound(DataRange, 1) - 1, UBound(DataRange, 2) - 1)) = DataRange  
    Erase DataRange
End Sub
vbaUBound Example 2-9

Things to Remember

This article is a guide to VBA UBound Function. Here, we learn how to use the VBA UBound function to find the Upper Bound Length in Excel, along with practical examples and downloadable templates. Below are some useful Excel articles related to VBA: –