How to define the length (number of elements) of an array with N elements in VBA

array length vba
excel vba populate array with cell values
redim array vba
vba number of elements in array
dynamic array vba
vba multidimensional array
excel vba dynamic multidimensional array
vba array function

I am just starting to learn to use VBA and I am writing a VBA code to find the average of an array with N elements. I am testing it with the array A defined at the start. If it were a MATLAB code I could use N=Length(A); and that would work, is there any function like that for VBA? Note: I am using Option Base 1 and I am getting a 'Run-time error '13': Type mismatch

The code is as follows:

Option Base 1

Sub Question1()

    A = [12,9,8,12,16,19,3,2,5,20]
    i = 1
    Dim N As Integer
    N = UBound(A)

    summ = A(1)

    For i = 1 To (N - 1)
        summ = summ + A(i + 1)
    Next i

    AVG = summ / N

    MsgBox "Average is: " & AVG

End Sub

Thanks.

  1. Declare all you variables(Consider using Option Explicit).
  2. Use Array() not []

Option Base 1
Option Explicit

Sub Question1()
    Dim A()
    A = Array(12, 9, 8, 12, 16, 19, 3, 2, 5, 20)

    Dim i As Long
    i = 1

    Dim N As Long
    N = UBound(A)

    Dim summ As Double
    summ = A(1)

    For i = 1 To (N - 1)
        summ = summ + A(i + 1)
    Next i

    Dim AVG As Double
    AVG = summ / N

    MsgBox "Average is: " & AVG

End Sub

Declaring arrays (VBA), An array whose size is specified is a fixed-size array. Each numeric Variant element of the array uses 16 bytes. the ReDim statement to change the number of dimensions, to define the number of elements, and to define  Introduction to VBA Array Length. Basically, an array is a set of elements that is in two dimensions. In excel we use arrays in our day to day lives. To calculate the length of an array in excel we either do it manually or use some functions to do so. But how do we get the length of an array in Excel VBA? We use two separate functions to do so.

First, Dim A As Variant - always declare your variable, always start your modules with Option Explicit. You can also turn this on by default (Tools > Options > Require Variable Declaration).

Next, I believe you probably want the Array function:

A = Array(12,9,8,12,16,19,3,2,5,20)

Using [ and ] has a completely different meaning in VBA - for example [A1] will (in Excel VBA) reference cell A1 of the ActiveSheet

Arrays, An array is a set of values, which are termed elements, that are logically ReDim Preserve numbers(15) ' Redefine the size of an existing array and reset the values. Dim index = 0 For Each task In tasks Dim n = Task.Result  The Array.Length property gives the number of elements that the array contains. It is different from the amount of memory storage required by the array. You can learn more about array handling in VBA with this awesome course .

You don't need to do so in Excel, you could just use a worksheet function like this:

Option Explicit
Sub Test()

    Dim A As Variant
    A = Array(12,9,8,12,16,19,3,2,5,20)

    Dim AVG As Single
    AVG = Application.Average(A)
    MsgBox "Average is: " & AVG

End Sub
  1. Use always Option Explicit at the top of your module to force yourself to declare all your variables.

  2. Don't use integer use a Long.

  3. UBound(array) gives you the index of the last member, LBound(array) gives you the index of the first one.
  4. You can use the same formulas you can use on your sheet like this Application.WorksheetFunction.YourFormula but if you skip the WorksheetFunctionand give the value to an array it won't raise an error if there is one.

Although your way would be like this:

Sub Test2()

    Dim A As Variant
    A = [12,9,8,12,16,19,3,2,5,20]

    Dim i As Long, Summ As Long
    For i = 1 To UBound(A)
        Summ = Summ + A(i)
    Next i

    AVG = Summ / UBound(A)

    MsgBox "Average is: " & AVG

End Sub

VBA Arrays - Dynamic Arrays, These are also known as Variable Arrays or Variable-Length Arrays. This type of A dynamic array does not have a pre-defined number of elements. Resizing  The length property sets or returns the number of elements in anlength is a property of arrays in JavaScript that returns or sets the number of elements in a given array. length == 0 for an empty array. The value of the iterable expression, which can be used as a template input variable.

First, since you're using Excel VBA, you don't need to use any special code to calculate the average of an array. You can use WorksheetFunction.Average

Option Explicit

Sub Question1()
    Dim a() As Variant ' has to be a variant array, since that's what the `Array` function returns.
    Dim i As Integer
    Dim summ As Integer
    Dim avg As Double

    a = Array(12, 9, 8, 12, 16, 19, 3, 2, 5, 20)

    avg = Excel.WorksheetFunction.Average(a)

    MsgBox "Average is: " & avg

End Sub

If you want your code to be more portable (i.e. not have to depend on Excel's worksheet functions), then I would recommend splitting your average-calculation into a separate function you can call with any array and any base. To calculate the length of an array, you want to use both the LBound and UBound, as documented here:

n = UBound(a) - LBound(a) + 1

Assuming, of course, that a is an array, this will always work, regardless of whether your array is 0-based, 1-based, or something completely different.

Since you're looping over the array to get the sum anyways, you can also use that loop to get the length anyways. Something like this:

Option Explicit

' assuming that arr is an array containing things we can sum, to get an average.
Public Function ArrayAverage(arr As Variant) As Double
    Dim n As Long, sum As Double
    Dim i As Long

    n = 0: sum = 0
    For i = LBound(arr) To UBound(arr)
        n = n + 1
        sum = sum + arr(i)
    Next i

    ArrayAverage = sum / n
End Function

Public Sub Question1()
    Dim a() As Variant, avg As Double

    a = Array(12, 9, 8, 12, 16, 19, 3, 2, 5, 20)
    avg = ArrayAverage(a)
    MsgBox "Average is: " & avg
End Sub

Excel VBA Array - The Complete Guide, With a static array you must set the length to the largest possible number of students. The following code will split the string into an array of three elements: In a procedure within the array's scope, use the ReDim statement to change the number of dimensions, to define the number of elements, and to define the upper and lower bounds for each dimension. You can use the ReDim statement to change the dynamic array as often as necessary.

Dynamic Array in Excel VBA, Next, we initialize each element of the array. We use a loop. For i = 1 To size numbers(i) = Cells(i, 1).Value Arrays in Excel VBA. An array is like a table that contains elements of the same data type. By using an array as a variable you can avoid declaring a lot of variables. Instead you collect them in one place, your array, and then you can look them up, when you need to read or write a value.

Excel Programming Weekend Crash Course, Arrays. A static array has a fixed size or number of elements. You specify the array ArrayName(n) As Type ArrayName must follow the usual VBA naming rules,  NumPy Array Object Exercises, Practice and Solution: Write a NumPy program to find the number of elements of an array, length of one array element in bytes and total bytes consumed by the elements.

How to Implement Market Models Using VBA, If omitted, the size of an array can be modified dynamically: use ReDim to modify When using the ReDim statement, all the elements of the array are erased. n2) ' first element is number 10 You can also set the lower bound of every array in a MatrixName(n,n) Another example: to declare a three-dimensional fixed-size​  By using Javascript Array Length property can get the numbers of elements in a Array. A Lenght property returns the number of elements in that array. Length of Array can be stored in JavaScript variable. Now, you might be thinking what is use of Array Length?