WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA UBOUND

VBA UBOUND

UBOUND or also known as Upper Bound, this function in VBA is used with its opposite function which LBOUND or also known as Lower Bound function, the use of this function is to define the length of an array in a code and 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 these while, then today is the end of it because we have a function called UBOUND to determine the maximum length of the array. Follow this article to have more knowledge of UBOUND function in Excel VBA.

UBOUND stands for Upper Bound. Often times in coding, we may require to find the maximum length of the array. For example, MyResult(24) means array name MyResult holds 25 values to it 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 Excel VBA UBOUND function represents the upper bound of array size.

VBA UBOUND Function

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

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. Follow the below steps to apply VBA UBOUND function.

Step 1: Start the excel macro and define the variable name.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Code:

Sub Ubound_Example1()
    Dim ArrayLength(0 To 4) As String

vba UBound Example 1

Step 2: I 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 will be shown in the message box.

vba UBound Example 1-4

Like this 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 is going to update on a daily basis, and you need to copy this data to the new sheet every time it updates. Updating this manually will take a considerable amount of time in your workplace, but I 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 by using the below code.

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 by 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 be equal to 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 a dynamic one because even when the data increases horizontally and vertically, it will automatically take the range. Now I will add some dummy lines to the data.

Example 2-7

Now I 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

  • UBOUND returns the maximum length of the array.
  • The array starts from 0, not from 1.
  • If you want the lower value of the array, then you need to use VBA LBOUND.
  • If the array has more than one dimension, then you need to specify the dimension number as well.

Recommended Articles

This has been a Guide to VBA UBound Function. Here we learn how to use 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 –

  • VBA Class
  • VBA Variant Data Type
  • Arrays Function in VBA
  • VBA StrConv
  • VBA Rename Sheet
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA UBound Function Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More