0

I have refactored my code so that it allows me to be more flexible when inserting or moving columns by using absolute reference cells(x,y) instead of relative reference cell.offset(r,c).

For example:

Set employee = Info.Range("Info[Payroll]").Find(cell)

Schedule.Cells(x, 2) = _
employee.Offset(0,1)

becomes

Set employee = Info.Range("Info[Payroll]").Find(cell)

Schedule.Cells(x, Schedule.Range("Schedule[Name]").Column) = _
Info.Cells(employee.row, Info.Range("Info[Name]").Column)

This has slowed my code 100x when looping through 400 employees and extracting their schedule from their shift codes. Is the above bad practice/code and how should I improve performance?

hellocng
  • 114
  • 3
  • 9
  • What are `Info` and `Schedule`? Worksheets, tables? Maybe it would help if you provide screenshots. Did you try to achieve what you want with a formula? – Ike Nov 18 '22 at 11:45
  • I avoid formulas because the users can manually overwrite any field. The worksheet change will trigger extracting or updating the other fields. Info and Schedule are named worksheets. – hellocng Nov 18 '22 at 11:52
  • 1
    If you do not show all the code, nobody can appreciate in a direction. I am tempted to think that placing only that last way **in the same global code** will not gain anything. At a first glance, evaluating the table columns by header (two times) looks to take at least as `Offset` calculation, if not more... Both cases use the implicit `Value` and should be fast, not using clipboard. – FaneDuru Nov 18 '22 at 11:52
  • You could place a formula (Index/match) by code - then do `.value=.value` which turns the formula into values. – Ike Nov 18 '22 at 11:56
  • I would think index/match for 10 fields in a record would be slower than one find operation and using that row and getting the column from the table headers. It's definitely more cumbersome to write but I will try it out. – hellocng Nov 18 '22 at 12:00
  • Could you provide infos about what `Info` and `Schedule` are in your code? – Ike Nov 18 '22 at 12:03
  • @Ike They probably cannot be something else than sheet objects... – FaneDuru Nov 18 '22 at 12:37
  • @FaneDuru `"Info[Payroll]"` sounds like a listobject - if so coding could be different as well – Ike Nov 18 '22 at 12:40
  • @Ike This is obvious... It is a `lisObject` in the `Info` sheet. The same for `Schedule`: a table named "Schedule" in the `Schedule` sheet. – FaneDuru Nov 18 '22 at 13:06
  • @FaneDuru you are correct – hellocng Nov 18 '22 at 13:07
  • Write the value of `Schedule.Range("Schedule[Name]").Column` and `Info.Range("Info[Name]").Column` into variables before you start to loop and use that variables as column argument – FunThomas Nov 18 '22 at 13:08
  • @FunThomas Excellent suggestion! Will try now. I think this is where the performance drop occurs when the column of every field is calculated again and again – hellocng Nov 18 '22 at 13:10
  • Or use methods and properties of the ListObject, e.g. ListColumns, Databodyrange etc. – Ike Nov 18 '22 at 13:15
  • In my opinion the code you show consumes most of the time for writing in each cell. Writing each found value in an array and dropping the result at once at the code end will diminish considerably the consumed time, working mostly in memory. The whole code context will probably allow an initial `ReDim` avoiding frequent `ReDim Preserve`... – FaneDuru Nov 18 '22 at 13:32
  • I tried FunThomas's suggestion and no improvement to performance. @FaneDuru, can you please elaborate on your suggestion? – hellocng Nov 18 '22 at 13:51
  • Suggested solution depends on your used ranges. How `x` is determined, for instance. Then, are there already some values in B:B, where the code writes? If so, you can declare an array `Dim arr As Variant`. Then, `arr = Schedule.Range("B2:B" & lastR).Value`. Supposing that `lastR` is last row where the iteration finishes. Probably, last row in A:A, but I cannot know it. Then, instead of using `Schedule.Cells(x, 2) = employee.Offset(0,1)` you can use `arr(x, 1) = employee.Offset(0,1).value`. And after iteration ends: `Schedule.Range("B2").Resize(Ubound(arr), 1).value = arr`... – FaneDuru Nov 18 '22 at 14:01
  • This is the true solution: https://stackoverflow.com/questions/30871703/slow-vba-macro-writing-in-cells – hellocng Nov 19 '22 at 19:02

0 Answers0