Excel VBA - Any performance benefits between const string range or define names for ranges?

Question

G'Day,

I have a question more towards helping me understand on more about how Excel VBA can effectively manage defined ranges that have been declared in one place in order to execute data well. Just wanting to work out which two options (I know so far) is better or not as preferred best practice before working more on this project.

The problem I'm solving is to make a small table containing a number of failures across a set of fictional suppliers, thus the table looks like this (sorry it is in raw form)

"Company Name" "No. of Failures"
"Be Cool Machine" 7
"Coolant Quarters" 5
"Little Water Coolants 3
"Air Movers Systems" 7
"Generals Coolant" 5
"Admire Coolants" 4

My first option (Const String) is this module/formula as follows.

Option Explicit
Public Const CountofFailures As String = "J7:J12"
Sub btnRandom()
    ' Declaration of variables
    Dim c As Range

    ' Provide a random number for failures across Suppliers
    For Each c In ActiveSheet.Range(CountofFailures)
        c.Value = Random1to10
    Next c
End Sub

Function Random1to10() As Integer
    'Ensure we have a different value each time we run this macro
    Randomize
    ' Provide a random number from 1 to 10 (Maximum number of Failures)
    Random1to10 = Int(Rnd() * 10 + 1)
End Function

Second option (Defined Name) is this module/formula as follows.

Option Explicit
Sub btnRandom()
    ' Declaration of variables
    Dim c As Range
    Dim iLoop As Long

    ' Provide a random number for Suppliers with Defined Range
    For Each c In ActiveWorkbook.Names("CountofFailures").RefersToRange
        c.Value = Random1to10
    Next c
End Sub

Function Random1to10() As Integer
    'Ensure we have a different value each time we run this macro
    Randomize
    ' Provide a random number from 1 to 10 (Maximum number of Failures)
    Random1to10 = Int(Rnd() * 10 + 1)
End Function

Any suggestions - I would do a macro timer test later if this helps?

Would there be a third option if I fetch a range listed in a cell as value? I haven't seen a code that does this in practice?

Answer 1

Both of your codes loop through ranges which will be the bottleneck. I suggest you

1. Use a range name to automatically "locate" your data - ie if you insert/delete rows and columns your reference remains intact. My experience though is that many range names in a file can end up obfuscating what the workbook is doing
2. Do a single write to this range

code

Sub QuickFill()
Randomize
Range("CountofFailures").Formula = "=Randbetween(1,10)"
Range("CountofFailures").Value = Range("CountofFailures").Value
End Sub