CODEWITHSUNDEEP
excelvba
eirikdaude
eirikdaude

Reputation: 3254

Performance issues when automatically putting a large amount of links in spreadsheet

I am using this code snippet to place links to another workbook into my own:

Sub legg_inn_lekkjer()
  Dim i As Long

  Call deaktiver

  For i = 0 To 740
    Loktider.Range("C4").Offset(i, 0) = "='[Simulering Arbeidsplan Ovn 3 28h.xls]Lokklegging'!R" & CStr(11 + i) & "C6"
    Loktider.Range("D4").Offset(i, 0) = "='[Simulering Arbeidsplan Ovn 3 28h.xls]Lokklegging'!R" & CStr(11 + i) & "C5"
    Loktider.Range("E4").Offset(i, 0) = "='[Simulering Arbeidsplan Ovn 3 28h.xls]Lokklegging'!R" & CStr(11 + i) & "C16"
    Loktider.Range("F4").Offset(i, 0) = "='[Simulering Arbeidsplan Ovn 3 28h.xls]Lokklegging'!R" & CStr(11 + i) & "C15"
    Loktider.Range("G4").Offset(i, 0) = "='[Simulering Arbeidsplan Ovn 4 30h.xls]Lokklegging'!R" & CStr(11 + i) & "C6"
    Loktider.Range("H4").Offset(i, 0) = "='[Simulering Arbeidsplan Ovn 4 30h.xls]Lokklegging'!R" & CStr(11 + i) & "C5"
    Loktider.Range("I4").Offset(i, 0) = "='[Simulering Arbeidsplan Ovn 4 30h.xls]Lokklegging'!R" & CStr(11 + i) & "C16"
    Loktider.Range("J4").Offset(i, 0) = "='[Simulering Arbeidsplan Ovn 4 30h.xls]Lokklegging'!R" & CStr(11 + i) & "C15"
  Next

  Call reaktiver
End Sub

Sub deaktiver()
  Application.EnableEvents = False
  Application.ScreenUpdating = False
  Application.DisplayStatusBar = False
  Application.Calculation = xlCalculationManual
  ' ActiveSheet.DisplayPageBreaks = True 'note this is a sheet-level setting
End Sub

Sub reaktiver()
  Application.EnableEvents = True
  Application.ScreenUpdating = True
  Application.DisplayStatusBar = True
  Application.Calculation = xlCalculationAutomatic
  ' ActiveSheet.DisplayPageBreaks = True 'note this is a sheet-level setting
End Sub

It works correctly, but takes quite a while to run. It probably won't be a problem, as I won't need to update the link-addresses very often, but still I wondered if there is a more efficient way to do this, using some kind of relative reference to input the addresses for each column all at once for instance? Quite a lot of googling indicates "no", but I figured it wouldn't hurt to ask.

Upvotes: 0

Views: 34

Answers (1)

Zerk
Zerk

Reputation: 1593

I can imagine it would be quite slow, your for loop is entering cell values individual in 10 columns for 741 iterations, so around 7.4k cell entries done individually.

You could either create the cell values in an array and then paste that array into the workbook, or more simply enter the expected formulae in the top row and fill down as appropriate.

As an example you could replace:

  For i = 0 To 740
    Loktider.Range("C4").Offset(i, 0) = "='[Simulering Arbeidsplan Ovn 3 28h.xls]Lokklegging'!R" & CStr(11 + i) & "C6"
    Loktider.Range("D4").Offset(i, 0) = "='[Simulering Arbeidsplan Ovn 3 28h.xls]Lokklegging'!R" & CStr(11 + i) & "C5"
    Loktider.Range("E4").Offset(i, 0) = "='[Simulering Arbeidsplan Ovn 3 28h.xls]Lokklegging'!R" & CStr(11 + i) & "C16"
    Loktider.Range("F4").Offset(i, 0) = "='[Simulering Arbeidsplan Ovn 3 28h.xls]Lokklegging'!R" & CStr(11 + i) & "C15"
    Loktider.Range("G4").Offset(i, 0) = "='[Simulering Arbeidsplan Ovn 4 30h.xls]Lokklegging'!R" & CStr(11 + i) & "C6"
    Loktider.Range("H4").Offset(i, 0) = "='[Simulering Arbeidsplan Ovn 4 30h.xls]Lokklegging'!R" & CStr(11 + i) & "C5"
    Loktider.Range("I4").Offset(i, 0) = "='[Simulering Arbeidsplan Ovn 4 30h.xls]Lokklegging'!R" & CStr(11 + i) & "C16"
    Loktider.Range("J4").Offset(i, 0) = "='[Simulering Arbeidsplan Ovn 4 30h.xls]Lokklegging'!R" & CStr(11 + i) & "C15"
  Next

With:

Edit -- R1C1 reference system seems to use absolute values, standard cell reference added instead

with Loktider
    .Range("C4") = "='[Simulering Arbeidsplan Ovn 3 28h.xls]Lokklegging'!F11"
    .Range("D4") = "='[Simulering Arbeidsplan Ovn 3 28h.xls]Lokklegging'!E11"
    .Range("E4") = "='[Simulering Arbeidsplan Ovn 3 28h.xls]Lokklegging'!P11"
    .Range("F4") = "='[Simulering Arbeidsplan Ovn 3 28h.xls]Lokklegging'!O11"
    .Range("G4") = "='[Simulering Arbeidsplan Ovn 4 30h.xls]Lokklegging'!F11"
    .Range("H4") = "='[Simulering Arbeidsplan Ovn 4 30h.xls]Lokklegging'!E11"
    .Range("I4") = "='[Simulering Arbeidsplan Ovn 4 30h.xls]Lokklegging'!P11"
    .Range("J4") = "='[Simulering Arbeidsplan Ovn 4 30h.xls]Lokklegging'!O11"
Range(.Cells(4,3),.Cells(744,10)).FillDown
End With

This is on a few assumptions, that Loktider is a worksheet variable you've created, and that you'll be using that specific range as you hardcoded your loop counter.

Upvotes: 1

Related Questions