How to define the length (number of elements) of an array with N elements in 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
- Declare all you variables(Consider using
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.
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
A = Array(12,9,8,12,16,19,3,2,5,20)
] 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
Option Explicitat the top of your module to force yourself to declare all your variables.
- 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.YourFormulabut 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
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
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.