はじめに

この方法はExcel VBAのみで適用可能であり、その他Word等のVBAではつかえない

答え

Function vbRange(ByVal ini As Integer, ByVal tal As Integer) As Variant
    Dim rng As Range
    ReDim arr(tal - 1) As Variant
    For Each rng In Range(Cells(ini + 1, 1), Cells(ini + tal, 1))
        arr(rng.Row - 1 - ini) = rng.Row - 1
    Next
    vbRange = arr
End Function

つかいかた

    Dim tes As Variant
    tes = vbRange(1, 100)

FizzBuzz

vbRangeを使ったFizzBuzz

    Dim tmp As Variant
    For Each tmp In vbRange(1, 100)
        Debug.Print (IIf(tmp Mod 15 = 0, "FizzBuzz", IIf(tmp Mod 3 = 0, "Fizz", IIf(tmp Mod 5 = 0, "Buzz", CStr(tmp)))))
    Next

こういう書き方もできる

    Dim rng As Variant
    For Each rng In Range(Cells(1, 1), Cells(100, 1))
        Debug.Print (IIf(rng.Row Mod 15 = 0, "FizzBuzz", IIf(rng.Row Mod 3 = 0, "Fizz", IIf(rng.Row Mod 5 = 0, "Buzz", CStr(rng.Row)))))
    Next