Faster way of looping through named ranges in VBA

Question

I'm trying to learn how to loop through named ranges quicker, potentially by calculating off-sheet, since I understand that going to cell references on sheets is very slow. Here's a snippet of the code I'm trying to re-write - any advice on 1) how to "update" the call before the loop and 2) how to efficiently re-do the loop is appreciated

Sub test()

Application.Goto Reference:="namedcell0"
ActiveCell.Formula = "0"

For Each c in Range("named_range1")
Application.Goto Reference:="namedcell1"
ActiveCell.FormulaR1C1 = c
Calculate

For Each d in Range("named_range2")
Application.Goto Reference:="namedcell2"
ActiveCell.FormulaR1C1 = d
Calculate

Next
Next

End Sub

Don't `GoTo` or rely on `ActiveCell` - see [this question](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) why, it's the same idea. And you don't need to loop to write a formula. — BigBen, Nov 05 '19 at 17:58

score 2 · Accepted Answer · answered Nov 05 '19 at 18:21

That VBA code is basically simulating user clicks and not leveraging much of the object model.

Familiarize yourself with Workbook.Names and Worksheet.Names, and the Excel.Name class: you can get the Range referred to by a Name through its RefersToRange property.

Assigning to Range.Formula assigns the formula to every cell of that range:

With ThisWorkbook.Names
    .Item("namedcell0").RefersToRange.Formula = "0" ' why is this a string literal?
    .Item("namedcell1").RefersToRange.Formula = c
    .Item("namedcell2").RefersToRange.Formula = d
End With

No need to Calculate explicitly (assuming calculation mode is automatic - otherwise, consider calculating only once, after writing all the formulas).

Faster way of looping through named ranges in VBA

1 Answers1