VBA WorkSheetFunction.Mode of a Dynamic Populated Array

I can't figure out how to find the mode of a dynamic array that I fill out. Here is the code:

Sub test_array()

Dim test() As Integer
Dim i As Integer

For i = 1 To 3
ReDim Preserve test(i)
test(i) = i
Mode = Application.WorksheetFunction.Mode(test(i), 1)
Next i

End Sub

I don't know how to tell the mode function to process the array value as a number. In this example the mode is forced to be 1 ''cause it would be like Mode(1,1,2,3)

P.S. The mode is a function that returns the most recurring number.

Solution

You are testing the mode of each number individually not the whole array. Move the function after the loop.

But it will not like the use of an array and another number. So add the 1 to the end of the array and then use the array in the mode

Sub foo()


Dim test() As Integer
Dim i As Integer
Dim md As Integer

ReDim test(1 To 3) As Integer

For i = 1 To 3
    test(i) = i
Next i

ReDim Preserve test(1 To UBound(test) + 1)
test(UBound(test)) = 1

md = Application.WorksheetFunction.Mode(test)

MsgBox md
End Sub

First we fill the standard array. And to add the extra 1 into the array, because the MODE Function does not like an array and a single variable.

The ReDim Preserve test(1 To UBound(test) + 1) finds the upper bound of the test array. In this case it would be 3. It then add one more box into which we can add an integer.

So now the upper bound is 4 and we use test(UBound(test)) = 1 to put 1 in the last space.

This is a way to ensure that no matter how large or small the array is it will always add one more box into the array and in that box put the 1.