Creating an array of 1:N in VBA

Question

This is a super simple question and I haven't yet been able to piece together a solution I want from previous answers.

I have N images on a sheet and simply wish to group them. Normally I would use:

Sheets("Mail").Shapes.Range(Array(1,2,3,4,5)).Group

but obviously this doesn't work if I want to go from 1 to N. I am currently trying:

For i = 0 To Y / 33
  ReDim Preserve test(i)
  test(i) = i
Next i

Sheets("Mail").Shapes.Range(Array(test())).Group

But I am not sure how to use my test object to replicate the format I used in the non-general case. Any help appreciated!

Answer 1

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.

Answer 2

This is a function, returning numeric arrays from M to N :

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

This is how you call it:

arrA = ReturnArrayAtoB(1, 5)
arrB = ReturnArrayAtoB(10, 12)

The first one returns Array(1,2,3,4,5) and the second one returns Array(10,11,12)