Range name created by QueryTable in Excel VBA macos

Question

I am running a QueryTable in Excel for Mac 2016, with destination to range 'data_value', name for cell A1 in spreadsheet 'hs', QueryTable assigns the name 'ExternalData_1' to the same cell A1 after extracting the data, I want to delete name 'ExternalData_1' so I can refer to the value in 'data_value' at the end of the script but can't.

Function get_data(input_id As String, input_date As String)

Dim sqlstring As String
Dim connstring As String
Dim sLogin As String

 sLogin = "DATABASE=[DB];UID=[USER];PWD=[PWD]"
sqlstring = "SELECT data_value FROM tb_data_values WHERE data_date='" & input_date & "'"
connstring = "ODBC;DSN=myodbc;" & sLogin

With
    Worksheets("hs").QueryTables.Add(Connection:=connstring, Destination:=Range("data_value"), Sql:=sqlstring)
   .FieldNames = False
   .BackgroundQuery = False
   .Refresh
   .RefreshStyle = xlOverwriteCells

End With

get_data = Range("data_value")

Dim qt As QueryTable
For Each qt In Worksheets("hs").QueryTables
   qt.Delete
Next qt

Worksheets("hs").Names("ExternalData_1").Delete
Worksheets("hs").Range("data_value").ClearContents

End Function

VBA stops running in line Worksheets("hs").Names("ExternalData_1").Delete.

Answer 1

Simply remove that line. When you delete each QueryTable, you already delete its named range. Therefore, your call Worksheets("hs").Names("ExternalData_1").Delete is redundant and would error out since Name object does not exist. Had you wrapped function in proper error handling, the error would raise:

Application-defined or Object-defined error

To test, run a Name range loop just after your QueryTables loop. From below, you would see that only one "ExternalData_1" will print with Debug.Print.

Dim qt As QueryTable, RngName As Name

For Each qt In Worksheets("hs").QueryTables
    Debug.Print qt.Name                           ' "ExternalData_1" WILL PRINT
    qt.Delete
Next qt

For Each RngName In Worksheets("hs").Names
    Debug.Print RngName.Name                      ' "ExternalData_1" WILL NOT PRINT
Next RngName

Alternatively, you can check after running code without line above under Ribbon > Formulas > Name Manager (under Defined Names section) (may differ on Mac), and see ""ExternalData_1" not listed.