How to speed up macro in excel using array?

Question

Dear all,
'sheet2 have intnumbers span 1~45
With Sheet2
Sheet3.Range("b2:at46"). _
ClearContents
For h = 2 To 258
For i = 9 To 14
For j = i + 1 To 15
Sheet3.Cells(.Cells(h, i) + 1, .Cells(h, j) + 1) = Sheet3.Cells(.Cells(h, i) + 1, .Cells(h, j) + 1) + 1
Sheet3.Cells(.Cells(h, j) + 1, .Cells(h, i) + 1) = Sheet3.Cells(.Cells(h, j) + 1, .Cells(h, i) + 1) + 1
Next
Next
Next
End With
Regards,

maybe find from book "excel power programming with vba"(jhon walkenbach)
Dim temparray(1 To 45, 1 To 45) As Integer
Application.ScreenUpdating = False
With Sheet2
Sheet3.Range("b2:at46").ClearContents
For h = 2 To 258
For i = 9 To 14
For j = i + 1 To 15
temparray(.Cells(h, i), .Cells(h, j)) = temparray(.Cells(h, i), .Cells(h, j)) + 1
Debug.Print temparray(.Cells(h, i), .Cells(h, j))
temparray(.Cells(h, j), .Cells(h, i)) = temparray(.Cells(h, j), .Cells(h, i)) + 1
Debug.Print temparray(.Cells(h, j), .Cells(h, i))
Next
Next
Next
End With
With Sheet4
.Range("b2:at46") = temparray
End With
Application.ScreenUpdating = True

hi,devilishblueyes
thanks for replying.
say more detail,sheet2 have 258 rows. Each row have 7 numbers span 1~45. sheet3 has 45X45 TABLE.
In sheet2, combination of 7 numbers for each row like x-y values and count x_y pairs to sheet3.
Sorry for not making problem in detail

Answer 1

I know and am reading the book you are referring to. However, you need to add some notes to your macro to make it so that someone can look at it and halfway understand what in the world you are trying to do. I've never seen anyone use a cells property inside of a cells property.

The increased speed of a macro through arrays happens because the computer is working with the values in its memory and not working with the cells instead. Also it does one paste of the entire group instead of pasting the 45 x 45 cell range all on cell at a time.

Your macro is taking forever because you have a three layers of loops.

It looks like you really almost need to create a 3 dimensional array and then use that three dimensional array to paste your data to a range. Although you may get by with a two dimensional array.

You also have no Dim's or Redims in your macro code. What kind of data are you working with?

Look at page 335 of Walkenbach's book Excel 2003 Power Programming with VBA. First you have to give the arrays type.

If you are working with a number, looking at what you wrote you might be able to get away with defining it as an integer. Although Excel normally works with numbers that are the Double data type.

Dim TempArray1 As Integer

ReDim TempArray1(1 To 45,1 To 45)

'Enter code here to determine the value for each cell

Sheet3.Range("b2:at46").Value = TempArray


You probaby will need a second array to make your macro run fast. You may need a second array like this:

Dim TempArray2 As Integer

ReDim TempArray2(2 To 258, 9 To 14, 10 To 15)

And yes, turning the screen updating temporarily off will help.

Answer 2

this is fairly fairly uncomplicated. All you could desire to do to jot down your very own purposes is click on the Developer tab (in case you could no longer see it, pass to Excel concepts and click the "instruct Developer" verify container below "familiar") and click on the "seen consumer-friendly" icon on the top left of the ribbon. as quickly as the editor comes up, click "Insert" => "Module." this could pull up the text fabric editor. variety "Sub NewFunctionName" (or maybe though call you prefer) and end Sub below that, and then enter you code in between the Sub and end Sub headings. Any subroutine which you write will then be available by making use of typing = NameOfSubroutine in a cellular in basic terms such as you could use the different Excel function. you could specify arguments, return values, etc., in basic terms such as you could in a C or Java function. you will, of direction, could desire to look up a internet site with the consumer-friendly seen consumer-friendly syntax. maximum of it works in basic terms like C and Java, yet there are some transformations interior the way VB bargains with variables (i.e. making use of the Dim command and what-no longer). EDIT - in case you persevere with the stairs I defined above, and you have an outstanding wisdom of C and Java purposes already, it is going to possibly no longer take you extra beneficial than an afternoon or 2 write some fairly state-of-the-artwork purposes for Excel. In a VB subroutine you're able to do very practically something, and you will continuously insert a "classification module" in case you prefer to get into some deeper OOP stuff.