VBA UBound

VBA UBound 

The UBound or Upper Bound function in VBA is used to specify the length of an array and returns the highest subscript for a dimension for the specified array. It is the vice versa for LBOUND or Lower Bound function. For example, if you specify an array object with upper bound as 20, MyArray(20). This array object can hold 21 values as the array calculation starts from 0. So, 20 means +1 i.e., total of 21 values.

Syntax

UBound(arrayname, [ dimension ])

Parameters

Arrayname (required)- This parameter represents the array for which you want to find the highest subscript.

Dimension (optional) – This parameter specifies the dimension for the array (you need the highest subscript). It could be one dimensional, two dimensional or multi-dimensional. The integer value 1 is used to represent the first dimension, and 2 is used for signifying the second dimension, and so on. The default value is 1.

Return

The UBOUND function returns the highest subscript for a dimension for the specified array.

Example 1

Write a macro to demonstrate the example of UBOUND Function.

Code:

Sub UBOUND_Example1()
 'defining the array with UBOUND as 5
     Dim arry(5) As Variant
     arry(0) = 31               'Integer Value
     arry(1) = "Hello VBA"      'String Value
     arry(2) = "@#$%"           'symbol as string 
     arry(3) = 12.415            'Decimal Number
     arry(4) = #10/7/2013#      'Date
     'Displaying the output of Upper Bound function
     MsgBox ("The UBOUND function value for the specified array: " & UBound(arry))
 End Sub 

Let’s work with the set-by-step code of lines:

Step 1: Open the VBA developer tab either by using the shortcut keywords Alt +F11 or click on developer window -> visual basic editor.

Step 2: Visual Basic Editor will open. The next step is to create a module. Right-clicking on the VBA Project-> Click on Insert-> Click on Module.

Step 3: In the VBA Module window, within the sub-block, introduce your macro name.

Step 4: Define the array object and we will only specify the UBOUND value. Unlike here, we have defined the array object as Arry(5), where 5 represents the upper bound value.

Step 5: Specify the array length one by one with respective values.

Note: The array size starts with zero. So, in total, it can store 6 values.  

Step 6: At last, we will display the upper bound’s value with the help of MsgBox.

Output

Step 7: Execute the above code either by pressing the F5 shortcut key or by clicking on the Run button.

Step 8: You will notice that the message dialog box has pop up displaying the upper bound value.

Example 2

Write a Code to demonstrate the example of Upper to copy the data of one worksheet automatically to another.

Code:

Sub Ubound_Example2()
     'declaring an array variable
     Dim Rnge() As Variant
     'activating the Sheet2
     Sheets("Sheet2").Activate
     'selecting the active data in the sheet 
     Rnge = Range("A1", Range("A1").End(xlDown).End(xlToRight))
     Worksheets.Add
     'With the help of UBound Function we will offset the cells by the maximum length
     'and the retuned range value will be equal to the "Rnge" array value
     Range(ActiveCell, ActiveCell.Offset(UBound(Rnge, 1) - 1, UBound(Rnge, 2) - 1)) = Rnge
 End Sub 

Let’s work with the set-by-step code of lines:

Step 1: Open the VBA developer tab either by using the shortcut keywords Alt +F11 or click on developer window -> visual basic editor.

Step 2: Visual Basic Editor will open. The next step is to create a module. Right-clicking on the VBA Project-> Click on Insert-> Click on Module.

Step 3: In the VBA Module window, within the sub-block, introduce your macro name

Step 4: Define an array variable.

Step 5: Next, we will update the sheet where our data is kept. Here we have mentioned ‘Sheet2’.

Step 6: Allocate the variable range of cells containing your data. We have specified the first cell as A2 and, with the help of xlDown and xlToRight, have selected all the rightmost and bottom filled cells.

Step 7: We will add a new worksheet so as we can paste the data over here.

Step 8: With the help of UBound Function, we will offset the cells by the maximum length and the retuned range of cells we will paste the preciously stored array variable’s (“Rnge”) data.

Output

Step 9: Execute the above code either by pressing the F5 shortcut key or by clicking on the Run button.

Step 10: You will notice that another sheet has been added and your data has been successfully pasted on that sheet.