VBA- Put range of values into array

Question

I'm a pretty new self-taught programmer and I hope to learn from all of you.

Here, I'd like to put columns A of excel into an array and form a combination of three columns. The code is written and it runs, however, in a very slow speed.

If I put Array(1,2,3...,9,10) it works. However, if I define the Dim nums(): nums = Array(Range("A1:A5").Value) it does not work.
Even if I put Array(1,2,3...,9,10), the code runs very slowly.

The following is necessary because it's gonna be used when my array gets long. Indeed, my array would get over 2 thousand combinations. (Values of column A would change).

For x = 0 To 60
    For y = 0 To 2
        Cells(x + 1, y + 2).Value2 = arValues(x, y)
    Next
Next

My entire code below,

Sub AllCombinations()

    Dim nums(): nums = Array(Range("A1:A5").Value)
    Dim arValues(999999, 5)
    Dim n1 As Integer, n2 As Integer, n3 As Integer, n4 As Integer, n5 As Integer, n6 As Integer, x As Long
    Dim y As Integer

    For n1 = 0 To UBound(nums)
        For n2 = n1 + 1 To UBound(nums)
            For n3 = n2 + 1 To UBound(nums)

                arValues(x, 0) = nums(n1)
                arValues(x, 1) = nums(n2)
                arValues(x, 2) = nums(n3)

                x = x + 1

            Next
        Next
    Next

    For x = 0 To 60
        For y = 0 To 2

            Cells(x + 1, y + 2).Value2 = arValues(x, y)

        Next
    Next

    For x = 61 To 120
        For y = 0 To 2

            Cells(x - 60, y + 6).Value2 = arValues(x, y)

        Next
    Next

End Sub

Here is a preview of the data

I don't follow what you mean. Here, I'd like to put Columns A of excel into an array and forms combination of three Columns.. What combinations? E.g. If you've got numbers 1 - 10 how do you get that into a combination of three columns? — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Jul 2, 2018 at 14:28
Thanks Darren. My bad. A simple example. 1-10, could be grouped as 1,2,3 and 1,3,4 and 1,4,5 so on and so forth. So it is basically Combination without repetition — vincentshiusun
– vincentshiusun, Commented Jul 2, 2018 at 14:30
@DarrenBartrup-Cook, Let me narrow down the issues. The issue only happens when I try to define Sheets Col. A's values into an Array. It works perfect (Just slow) when it was explicitly defined {1,2,3,...,10} — vincentshiusun
– vincentshiusun, Commented Jul 2, 2018 at 14:33
What issue? I'd like to put columns A of excel into an array and form a combination of three columns. The code is written and it runs, however, in a very slow speed. You said it works perfectly just slowly - is speed the issue? If it is then CodeReview is probably better suited. — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Jul 2, 2018 at 14:42
@DarrenBartrup-Cook, No. I'd like to reference the array to a range. If I define the Dim nums(): nums = Array(Range("A1:A5").Value) it does not work. The error would be "Execution phase error 9. Array index out of range" — vincentshiusun
– vincentshiusun, Commented Jul 2, 2018 at 14:49

CLR · Accepted Answer · 2018-07-02 14:19:12Z

0

Try:

Dim nums()
nums = Range("A1:A5").Value

The nums array will look like:

answered Jul 2, 2018 at 14:19

CLR

12.3k1 gold badge13 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

vincentshiusun Over a year ago

Thanks. But it shows undefined arValues(x, 0) = nums(n1) in return. But if I directly set the array = (1,2,3...,10), it works.

CLR Over a year ago

The array has 2 dimensions because a range has 2 dimensions. nums(1,1) will contain the value of Cell A1, nums(3,1) = A3, , nums(4,1) = A4...

vincentshiusun Over a year ago

understood this part. But the thing is after adopting your change, the code stops at arValues(x, 0) = nums(n1).

CLR Over a year ago

so change it to arValues(x, 0) = nums(n1,1) ?

CLR Over a year ago

Just noticed your code starts at 0. As you'll see from my answer the array starts at 1. So your For loop needs to look like: For n1 = 1 To UBound(nums)

|

Collectives™ on Stack Overflow

VBA- Put range of values into array

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related