How to Declare Objects by Using Defined Names

Question

Disclaimer: I am still very new to VBA so hoping for your patience

I am trying to write a code that uses Range("Defined Name") instead of Range("Cell Address")

However, I keep encountering errors. In this code for example, I am trying to set the Source Document and the Destination Document by using the Workbook/Worksheet name I input in a cell

What I tried:
*assume content of cell is also their Defined Name and this table is in Destination Workbook where the VBA code is also embedded in

	A (Filepath)	B (Name of Worksheets)
1	SourceWB	SourceWS
2	DestinationWB	DestinationWS

Sub ImportData()
    Dim WB_S as Workbook 'source workbook
    Dim WS_S as Worksheet 'source worksheet
    Dim WB_D as Workbook 'destination workbook
    Dim WS_D as Worksheet 'destination worksheet


    Application.DisplayAlerts = True

'OpenWorksheets

    Set WB_D = Workbooks.Open(Range("DestinationWB")) 'referring to A2
    Set WS_D = WB_D.Sheets(Range("DestinationWS")) 'referring to B2 'ERROR
    Set WB_S = Workbooks.Open(Range("SourceWB")) 'referring to A1
    Set WS_S = WB_S.Sheets(WB_D.Range("DestinationWS")) 'referring to B2 'referring to B1 

I get error as soon as I reach line where I Set WS_D

Again, my goal is to refer to the file path and name of worksheets as the Defined Names

Thank you for your help

Answer 1

Try

Option Explicit

Sub ImportData()
    Dim WB_S As Workbook 'source workbook
    Dim WS_S As Worksheet 'source worksheet
    Dim WB_D As Workbook 'destination workbook
    Dim WS_D As Worksheet 'destination worksheet
    Dim ws As Worksheet, nm, msg As String

    Application.DisplayAlerts = True

    ' check named ranges
    Set ws = ThisWorkbook.ActiveSheet ' workbook where ranges are defined
    For Each nm In Array("SourceWB", "SourceWS", "DestinationWB", "DestinationWS")
        On Error Resume Next
        If ws.Range(nm) Is Nothing Then
             MsgBox "'" & nm & "' is not a named range", vbCritical
             Exit Sub
        Else
             msg = msg & vbLf & nm & " - " & ws.Range(nm)
        End If
        On Error Goto 0
    Next
    
    'OpenWorksheets
    Set WB_D = Workbooks.Open(ws.Range("DestinationWB")) 'referring to A2
    Set WS_D = WB_D.Sheets(ws.Range("DestinationWS").Value2) 'referring to B2
    
    Set WB_S = Workbooks.Open(ws.Range("SourceWB")) 'referring to A1
    Set WS_S = WB_S.Sheets(ws.Range("SourceWS").Value2) 'referring to B1
    
    MsgBox msg, vbInformation
End Sub