1

I am running the below algorithm on an array of 100k items (I would like to expand it to 1m if possible) and am facing performance issues. I would like to reduce the processing time down to a few seconds. I am pretty sure the first loop is causing the code to slow down. Is it possible to automatically shuffle the array 250 times, to populate a matrix and then simply paste it? - instead of going through 250 separate connections between vba and excel.

For a = 0 To 249        
        For i = UBound(myArray , 1) To 1& Step -1&
                NewRow= Int(Rnd() * i) + 1&
                temp = myArray (i, 1)
                myArray (i, 1) = myArray (NewRow, 1)
                myArray(NewRow, 1) = temp
        Next i            
    Range(Cells(1, a + 1), Cells(LastRow, a + 1)).Value = myArray                     
Next a

any help is appreciated!! Thanks! John

Improve this question
17
  • 2
    Why are you shuffling 250 times? Implement a correct shuffle and execute it once. Adding iterations doesn't make it "more random". Commented Jan 10, 2018 at 19:05
  • As you can see I want to populate a 100,000x250 matrix which is why I need to shuffle the array 250 times. The first loop is used to increment the columns of the matrix. Commented Jan 10, 2018 at 19:11
  • Oh I see what you're doing now. Yes, you can set up a 2d array and assign it to a range. Example: stackoverflow.com/questions/34733061/… Commented Jan 10, 2018 at 19:18
  • 1
    This question is best answered by someone (probably you) running some benchmarks and seeing where the actual hotspots are. Without knowing where the time is spent your guesses are probably wrong. Commented Jan 10, 2018 at 19:20
  • 2
    Taking the cell-writing instruction out of the outer loop would probably speed this up by an order of magnitude already. Populate one single 2D array, drop it onto the worksheet once. Commented Jan 10, 2018 at 19:24
Related questions
5
Is there a better/faster way of randomly shuffling a matrix in MATLAB?
0
Speed up my VBA using arrays
0
How to show entries of a shuffled array in VBA / Excel
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.