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

Question

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.

score 4 · Answer 1 · answered Oct 02 '19 at 15:09

Declare all you variables(Consider using Option Explicit).
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

score 1 · Answer 2 · answered Oct 02 '19 at 15:08

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

Damian · Answer 3 · 2019-10-02T15:13:26.760

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

Use always Option Explicit at the top of your module to force yourself to declare all your variables.
Don't use integer use a Long.
UBound(array) gives you the index of the last member, LBound(array) gives you the index of the first one.
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

score 0 · Answer 4 · answered Oct 02 '19 at 15:37

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

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

4 Answers4