CODEWITHSUNDEEP
vbarange
indigochild
indigochild

Reputation: 372

Creating a Range in VBA

I'm working on my first VBA function. The goal is to have a function that accepts two integers as inputs, and outputs an array containing all the integers in between the two inputs (end-points included).

Example: If I input 5 and 9, the output should be an array of 5, 6, 7, 8, 9.

VBA doesn't seem to have any of the objects or functions I'm used to in other languages. Python has a range() function, but most other languages I know about have list-like types which can be appended to. How does this work in VBA?

I'm not looking to create an Excel range, but rather an array which contains a range between two values.

Upvotes: 2

Views: 1058

Answers (3)

Byron Wall
Byron Wall

Reputation: 4010

Applies only in the context of Excel (not sure based on question and tags)

If you want a one-liner that doesn't deal with arrays, you can take advantage of the ROW function and Application.Evaluate within Excel.

Code

Function RangeArr(int_start As Integer, int_end As Integer) As Variant

    RangeArr = Application.Transpose( _
                    Application.Evaluate("=ROW(" & int_start & ":" & int_end & ")"))

End Function

Results

array results

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166126

Function RangeArray(startAt As Long, endAt As Long) As Long()
    Dim arr() As Long, i As Long, n As Long
    n = endAt - startAt
    ReDim arr(0 To n)
    For i = 0 To n
        arr(i) = startAt + i
    Next i
    RangeArray = arr
End Function

Sub Tester()
    Dim arr
    arr = RangeArray(5, 10)
    Debug.Print arr(LBound(arr)), arr(UBound(arr))
End Sub

Upvotes: 2

Vasily
Vasily

Reputation: 5782

if you asking about Cells multiple range then you can use this:

Sub test1()
    Dim nStart&, nEnd&
    Dim Rng As Range
    nStart = 5: nEnd = 9
    Set Rng = Range("A" & nStart)
    While nStart <> nEnd
        Set Rng = Union(Rng, Range("A" & nStart + 1))
        nStart = nStart + 1
    Wend
    Debug.Print Rng.Address(0, 0)
End Sub

output

A5:A9

if you asking not about cells.range but about an array range then you can use this:

Sub test2()
    Dim nStart&, nEnd&
    Dim Rng() As String, i&
    nStart = 5: nEnd = 9
    For i = 0 To nEnd - nStart
        ReDim Preserve Rng(i)
        Rng(i) = nStart
        nStart = nStart + 1
    Next i
    For i = 0 To UBound(Rng)
        Debug.Print Rng(i)
    Next i
End Sub

or you can use this:

Sub test3()
    Dim nStart&, nEnd&
    Dim Rng As Object, key As Variant
    Set Rng = CreateObject("Scripting.Dictionary")
    nStart = 5: nEnd = 9
    While nStart <> nEnd + 1
        Rng.Add nStart, Nothing
        nStart = nStart + 1
    Wend
    For Each key In Rng
        Debug.Print key
    Next key
    Set Rng = Nothing
End Sub

output for both last two variants will be the same

 5 
 6 
 7 
 8 
 9

Upvotes: 2

Related Questions