Author: Oscar Cronquist Article last updated on October 22, 2018

The Lbound and Ubound functions calculate the size of of an array. The Lbound returns the lower limit of an array and the Ubound function returns the upper limit.

Sub Macro4()

Rng = Range("D2:F6").Value

MsgBox "Lower bound(1): " & LBound(Rng, 1) & vbNewLine & _
        "Upper bound(1): " & UBound(Rng, 1) & vbNewLine & _
        "Lower bound(2): " & LBound(Rng, 2) & vbNewLine & _
        "Upper bound(2): " & UBound(Rng, 2)

End Sub

The macro above populates variable Rng with values from range D2:F6, the Lbound and Ubound functions then return the lower and upper limits for the first dimension (rows) and the second dimension (columns).

Excel Function VBA Syntax

LBound( arrayname,  [dimension] )

UBound( arrayname,  [dimension] )

Arguments

arrayname Required. The array variable.
[dimension] Optional. Which dimension to use, the default value is 1.

Comments

The following macro creates array variable  TestArray with one dimension, lower limit of 1 and upper limit of 3.

Sub Macro1()

Dim TestArray(1 To 3)
MsgBox LBound(TestArray) & vbNewLine & UBound(TestArray)

End Sub

This macro creates array variable TestArray with two dimensions, the first dimension has the lower limit of 1 and an upper limit of 3, the second dimension has the lower limit of 1 and the upper limit of 10.

Sub Macro2()

Dim TestArray(1 To 3, 1 To 10)
MsgBox "Lower bound(1): " & LBound(TestArray, 1) & vbNewLine & _
        "Upper bound(1): " & UBound(TestArray, 1) & vbNewLine & _
        "Lower bound(2): " & LBound(TestArray, 2) & vbNewLine & _
        "Upper bound(2): " & UBound(TestArray, 2)
        
End Sub

This macro populates array variable TestArray with values from cell range B3:B8, the first dimension has the lower limit of 1 and an upper limit of 6, the second dimension has the lower limit of 1 and the upper limit of 1.

Sub Macro3()

Rng = Range("B3:B8").Value
MsgBox "Lower bound(1): " & LBound(Rng, 1) & vbNewLine & _
        "Upper bound(1): " & UBound(Rng, 1) & vbNewLine & _
        "Lower bound(2): " & LBound(Rng, 2) & vbNewLine & _
        "Upper bound(2): " & UBound(Rng, 2)

End Sub

LBOUND and UBOUND functions examples

test
Break up values in a cell range into separate cells based on a space character as a delimiter
This article demonstrates how to convert a range of cells containing strings separated by a delimiter into a range of […]
test
Compare file names in two different folder locations and their sub folders
Today I would like to share a macro that compares the content in two different folders and their subfolders. It […]
test
Count matching strings using regular expressions
This article demonstrates a User Defined Function (UDF) that counts how many times a string exists in a cell value […]
test
Fetching values from ThingSpeak programmatically
  Picture above shows cheap chinese IoT modules: a temp sensor and an esp8266-01 (25 mm x 15 mm) Thingspeak […]
test
Filter duplicate files in a folder and subfolders
This is a follow up to my last post Compare file names in two different folder locations and their subfolders, the obvious […]
test
Filter unique distinct records (case sensitive) [UDF]
The User Defined Function demonstrated above extracts unique distinct records also considering upper and lower case letters. For example, a record […]
test
Filter unique distinct values (case sensitive) [UDF]
The User Defined Function demonstrated in the above picture extracts unique distinct values also considering lower and upper case letters. […]
test
Filter words containing a given string in a cell range [UDF]
The image above demonstrates a User Defined Function that extracts all words containing a given string that you can specify. In […]
test
Find the most/least consecutive repeated value [VBA]
This post Find the longest/smallest consecutive sequence of a value has a few really big array formulas. Today I would like to […]
test
Finding the shortest path – A * pathfinding
Two months ago I posted some interesting stuff I found: Shortest path. Let me explain, someone created a workbook that calculated […]
test
How to copy Excel tables programmatically
The image above demonstrates a macro linked to a button. Press with left mouse button on the button and the […]
test
How to create an interactive Excel chart [VBA]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]
test
How to use the ARRAY function [VBA]
The ARRAY function creates a Variant variable containing array values. Table of Contents Array Function VBA Syntax Array Function Arguments […]
test
How to use the FOR NEXT statement
Table of Contents FOR NEXT statement FOR NEXT with a counter variable FOR each NEXT example FOR NEXT with counter […]
test
How to use the LBOUND and UBOUND functions
The Lbound and Ubound functions calculate the size of of an array. The Lbound returns the lower limit of an array and […]
test
Multiply numbers in each row with remaining rows in cell range (UDF)
This article demonstrates a User Defined Function (UDF) that multiplies numbers in each row with the remaining rows in a […]
test
Reorganize data [UDF]
Sean asks: Sheet1A B C D 8 Country Europe 9 Lights 100 10 Type A 200 11 12 Country USA […]
test
SUMIF across multiple sheets [UDF]
This post describes a User Defined Function that searches multiple ranges and adds corresponding values across worksheets in a workbook. A […]
test
Two-way lookup using multiple tables [UDF]
This article describes a User Defined Function that lookups values in multiple cross reference tables based on two conditions. A […]
test
Working with ARRAY VARIABLES (VBA)
This post will teach you how to work with Excel arrays in visual basic for applications (VBA). Why do you […]

Excel VBA Reference

This extensive vba reference list provides detailed information including syntax, arguments, return values, and examples for Visual Basic for Applications.