Slow copy paste (1.3 seconds) - is there a faster alternative?

[dispelthemyth]

I have a loop that copy / paste and recalculates a number of times until the variance is under materiality, these might happen 50+ times.

The code I have been using is below but the copy / paste part tame about 1.2 - 1.3 seconds.

I have the below set before I run the code

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

Copy paste code

Set RngCopy = Names("Master_Copy").RefersToRange
Set RngPaste = Names("Master_Paste").RefersToRange

RngPaste.Value2 = RngCopy.Value2
Applcation.calculate

Application.calculate takes around 0.3 - 0.4 seconds, so each iteration takes ~ 1.6 seconds

The copy / paste range is 35 rows by 86 columns, roughly half of the cells have a formula and half are blank
The data in the copy range is a mixture of formulas with numbers or blanks (no formula or number)

 

[offthelip]

Any loop that is reading and writing to and from the worksheet is going to be slow. This is because VBA is comparatively slow at transferring data to and from the worksheet. So the best way of speeding up you iterative calculation is to do the whole calculation in VBA. Once you have loaded all the data into a variant array VBA is extremely fast.

 

[dispelthemyth]

Hi Offthelip

Thanks for your answer and suggestion, Are you aware of any resources that discuss this method as its not something I have heard of before, be it books, videos or courses.

Would this method still be available if a couple of the calculations were in the table due to breaking a circularity, Its not something as simple of rolled-up interest

 

[DanteAmor]

It is correct what @offthelip mentioned, to make the process faster, you must perform all the calculations in memory, using arrays.

There is no particular method, you will need to create each of your formulas in the VBA code.

The copy / paste range is 35 rows by 86 columns, roughly half of the cells have a formula and half are blank

If you have 35 * 86 = 3010 cells, half 1500 are formulas, you will need to reproduce the 1500 formulas in VBA.
You have to analyze the formulas and see if some can enter a cycle. That is, you must analyze each formula.

I show you an example:
Column A is a constant number; column B is a random number between 1 and 16; column C is the result of multiplying A * B; column D is the cumulative of the results in column C.

Dante Amor
	A	B	C	D
1	Constant	Random	Multiply	Accumulate
2	12	5	60	60
3	13	3	39	99
4	14	10	140	239
5	15	16	240	479
6	16	2	32	511
Hoja3

Cell Formulas
Range	Formula
B2:B6	B2	=RANDBETWEEN(1,16)
C2:C6	C2	=A2*B2
D2:D6	D2	=SUM(D1,C2)

_________________________________________________________________________________________
For a macro doing the calculations with the cells, it takes 15 seconds to process 5,000 records.

Sub sample2()
  Dim i As Long, acumm As Double
  Application.ScreenUpdating = False
  For i = 2 To Range("A" & Rows.Count).End(3).Row
    Range("B" & i).Value = WorksheetFunction.RandBetween(1, 6)
    Range("C" & i).Value = Range("A" & i) * Range("B" & i).Value
    acumm = acumm + Range("C" & i).Value
    Range("D" & i).Value = acumm
  Next
  Application.ScreenUpdating = True
End Sub

_________________________________________________________________________________________
The following macro, with the calculations in memory, using an array with the original data and another array for the output, process 100,000 records, the response is in 1.14 seconds.

Sub sample()
  Dim a As Variant, b As Variant, i As Long
  Dim Rndnumber As Double, Multiply As Double, Acummulate As Double
  
  a = Range("A2", Range("A" & Rows.Count).End(3)).Value2
  ReDim b(1 To UBound(a), 1 To 3)
  Randomize
  For i = 1 To UBound(a)
    Rndnumber = Int((16 * Rnd) + 1)
    Multiply = a(i, 1) * Rndnumber
    Acummulate = Acummulate + Multiply
    b(i, 1) = Rndnumber
    b(i, 2) = Multiply
    b(i, 3) = Acummulate
  Next
  Range("B2").Resize(UBound(b), 3).Value = b
End Sub

_________________________________________________________________________________________
It is only an example of how you can improve times by performing calculations in memory.

 

[dispelthemyth]

Hi @DanteAmor

Thank you for your reply and the example code.
Unfortunately going this route might not be the best idea as having all of the answers going through some custom VBA/UDFs would become an audit issue when the files get sent to the IFC/World Bank etc.

I have managed to solve one of my issues by creating a macro to recreate the entire workbook in a fresh file and the hanging disappeared. previously the pasting was taking over 1 second but now is quite rapid so it appears the Excel file had some cache issues even after I ran software on it to clear things down. Doing this managed to reduce the overall run time by over 50% which gives me an overall 83% reduction since I starting reviewing the solving time.

Regards
Graeme