vba-psa

Many excel models use VBA code which loops and copies and pastes values down a spreadsheet (PSA/CEACs etc).

The standard/rubbish approach is something like this:

Do Until i > numpatients

    Sheets("Sheet1").Select
        Range("B2:H2").Select
        Selection.Copy
        Range("B3").Select
            Selection.Offset(i - 1, 0).Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    i = i + 1

Loop

However this way is very slow. It's taught in the Briggs book which is probably why some of us do it.

You can speed it up significantly by using this

Do Until i > numpatients

    Sheet1.Range("B3:H3").Offset(i - 1, 0).Value = Sheet1.Range("B2:H2").Value

  i = i + 1

Loop

In a test simulation using a multivariate normal on 10000 patients, it used to take 34 seconds but now takes 4. A significant speed-up. It's also cleaner.

Note: This copies values only (not formulae).