Creating an array of 1:N in VBA

For completeness, there is a more concise way to create contiguous arrays using the Evaluate function:

Public Function ReturnArrayWithEvaluate(ByVal M As Long, ByVal N As Long) As Variant
    Dim vArr1 As Variant
    vArr1 = Application.Transpose(Evaluate("ROW(" & M & ":" & N & ")"))
    ReturnArrayWithEvaluate = vArr1
End Function

As Vityata observes in the comments, this method comes with some shortcomings:

• It fails if N > ActiveSheet.Rows.Count
• It has limited portability, as ROWS does not exist in other VBA versions, such as Access VBA
• Performance-wise it is not worth it

I report hereby some tests, in which I vary the value of N from 10,000 to 1,000,0000 and run the two methods (for M=1).

Benchmark setup

Functions used:

Private Declare Function GetTickCount Lib "kernel32.dll" () As Long

Public Function ReturnArrayWithEvaluate(ByVal M As Long, ByVal N As Long) As Variant
    Dim vArr1 As Variant
    vArr1 = Application.Transpose(ActiveSheet.Evaluate("ROW(" & M & ":" & N & ")"))
    ReturnArrayWithEvaluate = vArr1

End Function

Public Function ReturnArrayAtoB(ByVal M As Long, ByVal N As Long) As Variant

    Dim lngCounter  As Long
    Dim arrReturn   As Variant

    ReDim arrReturn(N - M)

    For lngCounter = 0 To N - M
        arrReturn(lngCounter) = M + lngCounter
    Next lngCounter

    ReturnArrayAtoB = arrReturn

End Function

Sub test()
    Dim M As Long, N As Long
    Dim lTicks As Long
    Dim lCnt As Long, lStep As Long, lCnt2 As Long

    Dim vArrReturn As Variant
    Dim vArrResults As Variant

    M = 1
    N = 10000
    lStep = 9900 / 2
    lCnt2 = 1

    ReDim vArrResults(1 To 99 * N / lStep + 1)

    For lCnt = N To N * 100 Step lStep
        lTicks = GetTickCount
        vArrReturn = ReturnArrayAtoB(M, lCnt)
        vArrResults(lCnt2) = GetTickCount - lTicks
        lCnt2 = lCnt2 + 1
    Next lCnt
    Range("B2").Resize(lCnt2 - 1, 1).Value2 = Application.Transpose(vArrResults)
    lCnt2 = 1
    For lCnt = N To N * 100 Step lStep
        lTicks = GetTickCount
        vArrReturn = ReturnArrayWithEvaluate(M, lCnt)
        vArrResults(lCnt2) = GetTickCount - lTicks
        lCnt2 = lCnt2 + 1
    Next lCnt
    Range("C2").Resize(lCnt2 - 1, 1).Value2 = Application.Transpose(vArrResults)
End Sub

Results

The horizontal axis shows N and the vertical axis the time used by each method.

For large arrays, evaluate is faster than looping.

The assertion that Evaluate is faster than looping is actually incorrect!

On average, the two methods consume about the same time:

-- Average Looping is 57 ticks
-- Average Evaluate is 62 ticks
-- Median ticks are 62 for both methods

Overall, I think sticking to looping is probably a better option.