vba - 将工作表作为参数传递给子例程

Question

我知道关于这个主题有很多问题，但我仍然在为下标超出范围而苦苦挣扎？是的，我正在导入的工作簿确实有一个名为 LSL Recon 的工作表，我已验证。我已经调试并用 Import Sheets(1) 替换了 LSL Recon (如 Sheet1 中所示)，然后该过程确实继续了一点，但确实不将任何内容导入到数组中。

Option Explicit

Public FILENAME, c_DATE, cNAME As String
'Public ws As Worksheet

Sub main()
    Application.DisplayAlerts = True
    Application.ScreenUpdating = False

    CLEAR
    Import Sheets("LSL Recon")
    Display_Import
End Sub()

Sub Import(ws As Worksheet)  
    Workbooks.Open FILENAME
    Set TempBook = ActiveWorkbook
    ws.Activate

    cNAME = "Entity"
    cA = Sheets(1).Rows.Find(What:=UCase(cNAME), LookAt:=xlWhole, SearchDirection:=xlNext).Column
    cNAME = "Sector"
    cB = Sheets(1).Rows.Find(What:=UCase(cNAME), LookAt:=xlWhole, SearchDirection:=xlNext).Column
    cNAME = "Date"
    cC = Sheets(1).Rows.Find(What:=UCase(cNAME), LookAt:=xlWhole, SearchDirection:=xlNext).Column
    cNAME = "Client"
    cD = Sheets(1).Rows.Find(What:=UCase(cNAME), LookAt:=xlWhole, SearchDirection:=xlNext).Column
    ...
End Sub()

有用的问题:
VBA: Calling a sub on another worksheet with multiple arguments
pass sheet to a function (excel vba)
Passing a Worksheet to a subroutine

Answer 1

摆脱公共(public)作用域变量，声明所有变量，并根据需要传递参数:

Option Explicit

Sub main()

    Dim FILENAME$
    Dim c_DATE$
    Dim cNAME$
    Dim wsName$
    wsName = "LSL Recon"
    Application.DisplayAlerts = True
    Application.ScreenUpdating = False

    CLEAR
    Import (wsName)
    Display_Import
End Sub

Sub Import(wsName$)  
    Dim wb as Workbook
    Dim cNames, itm, found
    ' Use an array of items to search for
    cNames = Split("Entity,Sector,Date,Client",",")

    Set wb = Workbooks.Open(FILENAME)

    Set ws = wb.Sheets(wsName)

    For Each itm in cNames
        found = ws.Rows.Find(What:=UCase(cNAME), LookAt:=xlWhole, SearchDirection:=xlNext).Column
        Debug.Print cName " found in column: " & found
    Next

End Sub

如果需要将.Find的结果返回到主程序，则将其更改为Function，并返回一个集合对象，然后像这样调用它:

Set foundItems = Import(wsName)
Dim itm
For each itm in foundItems
    Debug.Print itm
Next

然后是函数:

Function Import(wsName$)  
    Dim wb as Workbook
    Dim ret as New Collection
    Dim cNames, itm, found
    ' Use an array of items to search for
    cNames = Split("Entity,Sector,Date,Client",",")

    Set wb = Workbooks.Open(FILENAME)

    Set ws = wb.Sheets(wsName)

    For Each itm in cNames
        ret.Add ws.Rows.Find(What:=UCase(cNAME), LookAt:=xlWhole, SearchDirection:=xlNext).Column
        Debug.Print cName " found in column: " & ret(ret.Count)
    Next
    'return the collection to the calling procedure
    Set Import = ret
End Function