如何使用定义的名称声明对象-解网

问：

免责声明：我对VBA仍然很陌生，所以希望您的耐心等待

我正在尝试编写一个使用 Range（“定义的名称”） 而不是 Range（“单元格地址”)

但是，我不断遇到错误。例如，在此代码中，我尝试使用我在单元格中输入的工作簿/工作表名称来设置源文档和目标文档

我尝试过的：
*假设单元格的内容也是它们的定义名称，并且此表位于目标工作簿中，其中VBA代码也嵌入在

	A（文件路径）	B（工作表的名称）
1	来源WB	来源WS
2	目的地WB	目的地WS

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

我一到达我所在的行就会出错Set WS_D

同样，我的目标是将工作表的文件路径和名称称为定义的名称

谢谢你的帮助

Excel VBA VBA7 VBA6

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

如何使用定义的名称声明对象

How to Declare Objects by Using Defined Names

评论

评论