Defining and using names in VBA formulas

Question

Alright, I'm pretty new to VBA and I ran into this roadblock.

I want to define a name for a column of dates ("dates") in VBA, and then use that name in a VBA formula.

Here's where I define the name in excel:

Sub Build_dates(as_of_date As String, curve_source As String)
'Code
Range("B14").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Names.Add Name:="dates", RefersTo:=Selection.Address
'More Code
End Sub

And here's the sub I wish to use it in:

Sub Build_times(interp_count As String, as_of_date As String)
    Dim First As Range, Last As Range, fillRange As Range
    Set First = Range("C14")
    Set Last = Range("B14").End(xlDown).Offset(0, 1)
    Set fillRange = Range(First.Address & ":" & Last.Address)
    Select Case interp_count
        Case "Act/360"
            First.Formula = "=(dates-$B$14)/360"
            First.AutoFill Destination:=fillRange
        Case "Act/365"
            First.Formula = "=(dates-$B$14)/365"
            First.AutoFill Destination:=fillRange
    End Select
    'Add name to the time column
    Range("C14").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Names.Add Name:="times", RefersTo:=Selection.Address
End Sub

The problem is that every cell in the "times" column has #VALUE! in it. I looked in the "Show Calculation Steps" option in the contextual menu that appears over each of the cells and saw this:

("$B$14:$B$81"-36526)/360

Which evaluates the parenthesized value to #VALUE!

Neither of the names that I define appear in the drop-down names menu in the upper-left, but I can find both dates and times in the names manager, with the each referring to

="$B$14:$B$81"

and

="$A$15:$A$81"

respectively. Whereas one that I define by manually highlighting a range and typing the name in the name box yields a reference with something like

='Bootstrap Validation'!$H$13:$H$46

How should I change my code so that I can use the named ranges in VBA defined formulas?

Answer 1

To answer the direct question, pass a Range (not a String) to RefersTo, like this

ActiveWorkbook.Names.Add Name:="dates", RefersTo:=Selection

That said, you should not use Select/Selection for code like this. See this answer for some examles of how to avoid these.