Using UBound for Array

ubound vba
how to use lbound and ubound in vba
vba array
vba ubound empty array
vba ubound 2d array
vba array length
redim array vba
lbound vba

I have a variable declared as Variant in my code

Dim All_WorkWeeks_Entered As Variant

And I am assigning the values as below

With Worksheets("workweeks")
    All_WorkWeeks_Entered = Application.Transpose(.Range(.Range("A1"), .Cells(.Rows.Count, "A").End(xlUp)))
End With

If my Column "A" has only one element then my All_WorkWeeks_Entered will be a string.

and in further part of the code I am using

For Counter = 1 To UBound(All_WorkWeeks_Entered)

Next Counter

UBound cannot be used for a string. How will I take All_WorkWeeks_Entered as array always?

If you transpose, you are going to end up with a zero-based array, not a one-based array. Save yourself some grief by sticking to the Lower Boundary as well as the Upper Boundary for loop iteration.

A simple row check will tell you whether there are more than a single worksheet name in column A. If it's a single value, Split on an illegal worksheet name character and you will end up with an array of one element.

Dim All_WorkWeeks_Entered As Variant, counter as long

With Worksheets("workweeks")
    if .Cells(.Rows.Count, "A").End(xlUp).row>1 then
        All_WorkWeeks_Entered = Application.Transpose(.Range(.cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp)))
    else
        All_WorkWeeks_Entered = split(.Range("A1"), "/")
    end if
End With

For Counter = LBound(All_WorkWeeks_Entered) To UBound(All_WorkWeeks_Entered)
    'do stuff with All_WorkWeeks_Entered(Counter)
Next Counter

Loop through Array with VBA UBound, The VBA UBound function returns the highest subscript for a dimension of a supplied array. The syntax of the function is: UBound( ArrayName, [Dimension] ). The UBound function is used with the LBound function to determine the size of an array. Use the LBound function to find the lower limit of an array dimension. UBound returns the following values for an array with these dimensions:

Indeed, the single member array is presented as its type, and not as an array. A good workaround is to check the array, with the IsArray() function and act correspondingly.

Public Sub TestMe()

    Dim alldata As Variant    
    With Worksheets(1)
        'works ok, returning array:
        'alldata = Application.Transpose(.Range("A1:A2"))

        'problematic - does not return an array, but a type not supporting Ubound():
        alldata = Application.Transpose(.Range("A1"))
    End With

    If IsArray(alldata) Then
        Debug.Print UBound(alldata)
    Else
        Debug.Print "Single member array!"
    End If

End Sub

In the condition, one may use some logic to return a single member array:

If IsArray(alldata) Then
    Debug.Print UBound(alldata)
Else
    Debug.Print "Single member array!"
    ReDim alldata(1)
    alldata(1) = Application.Transpose(Worksheets(1).Range("A1"))
    Debug.Print UBound(alldata)
End If

VBA UBOUND Function, UBOUND - Returns the largest subscript in a given dimension of an array (Long). You can use these functions to determine the size of an existing array. If the default lower bound is 0 then UBound is one less that the actual number of elements in the array. Like this using 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. 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.

The problem is that Range.Value can be either a single Variant or an Array of Variants, so you have to branch when reading the value like this:

With Worksheets("workweeks")
    Dim rngTarget As Range: Set rngTarget = .Range(.Range("A1"), .Cells(.Rows.Count, "A").End(xlUp))
    If IsArray(rngTarget.Value) Then
        All_WorkWeeks_Entered = Application.Transpose(rngTarget.Value)
    Else
        Dim varArray(1 To 1) As Variant: varArray(1) = rngTarget.Value
        All_WorkWeeks_Entered = varArray
    End If
End With

And the second loop would look better like this:

For Counter = LBound(All_WorkWeeks_Entered) To UBound(All_WorkWeeks_Entered)
' ...
Next Counter

VBA Arrays - Upper Lower Bounds, UBound. To determine the upper bounds of an array, use VBScript's UBound method. The UBound method returns the largest available� However, you can automate it and get the maximum length or upper bound of an array using a VBA function called UBound. Moreover, it has the most beautiful way to looping through. Like, if you are using a For loop in your array, you definitely wanted to loop all the elements of the array.

VBA using ubound on a multidimensional array, Tip: Use the UBound function with the LBound function to determine the size of an array. Syntax. UBound(arrayname[,dimension]). Parameter, Description. The UBound function returns the largest subscript for the indicated dimension of an array. Tip: Use the UBound function with the LBound function to determine the size of an array.

Array Dimensions & Upper Bounds with VBScript, Using UBound with single dimension arrays. In below example we will create 2 static VBA Arrays. The first array will be� The name of the array variable (Variant data type). dimension Optional. The index number of the dimension for which the upper bounds are to be returned. Example usage of VBA UBound function Using UBound with single dimension arrays. In below example we will create 2 static VBA Arrays.

VBScript UBound Function, VBA - UBound Function - The UBound Function returns the largest subscript of the specified array. Hence, this value corresponds to the size of the array. Arrays for which dimensions are set by using the To clause in a Dim, Private, Public, ReDim, or Static statement can have any integer value as a lower bound. Example This example uses the LBound function to determine the smallest available subscript for the indicated dimension of an array.

Comments
  • What you are trying to do?
  • I want my All_WorkWeeks_Entered variable to be a Array always.
  • @urdearboy But it is working perfectly.