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.