我正在尝试编写一个带有参数的可调用函数，这个参数将返回列号。列号将通过参数传入。正如你所见，我有一个名为jobs的变量，其中包含我需要引用的表。我也知道这个变量确实会起作用，因为如果我把.Match参数中的函数调用替换成一个数字，它就能工作，这也说明是我的tblIndex函数在出错。此外，当我尝试运行这段完全相同的代码时，在tblIndex = Application.Match行上遇到了424错误。据我所知，我已经正确使用Application.Match，并传入了所有正确的参数。到底发生了什么？有没有更好的做法？

Sub Create_Workorders()
'
' Create_Workorders Macro
' Creates New Sheets work each workorder
'
' Keyboard Shortcut: Ctrl+Shift+P


'Variable Declarations

    ' im using this to essentially just alias the gui test environment worksheet
    ' when the time comes that this can be made to work on the main Gui, only change it here (i think)
    Dim ws As Object
    Set ws = Worksheets("Gui Test Environment")

    'references the number of active jobs on the GUI
    Dim numJobs As Integer
    numJobs = ws.Range("numJobs").Value

    'references the customers name on the GUI
    Dim customerName As String
    customerName = ws.Range("cName").Value

    'References GUI for todays date
    Dim tDate As Date
    tDate = Date

    'references GUI for the Table
    Dim jobs As Variant
    Set jobs = ws.ListObjects("Jobs1242")

    Dim pType As String


' Macro Code

'    Sheets("Blank Work Order").Select
 '   Sheets("Blank Work Order").Copy After:=Sheets(5)
  '  ActiveSheet.Name = customerName
   ' ActiveSheet.Range("D_Date") = "Dropoff Date: " & tDate
    'ActiveSheet.Range("B23") = jobs

    For i = 1 To numJobs

        pType = jobs.DataBodyRange(i, tblIndex("Piece"))
        MsgBox (pType)
        ' MsgBox (pCol)

    Next i

 MsgBox ("I Ran")
End Sub

Public Function tblIndex(column) As Integer

    tblIndex = Application.Match(column, jobs.HeaderRowRange, 0)

End Function

解决方案

如评论中所述，jobs变量不在你的辅助函数的作用域内。你可以把它作为参数传递给这个函数。（或者，至少把它提升到全局作用域。）

你可以将函数定义为

Public Function tblIndex(listObject as variant, column as string, Optional searchValue as integer ) As Integer
Dim search as integer
    If IsMissing(searchValue) then
       search=0
    Else
       search=searchValue
    End If
    tblIndex = Application.Match(column, listObject.HeaderRowRange, search)
End Function

并像下面这样调用该函数

    Dim findIdx as Variant
    ....

    findIdx=tblIndex(jobs,"Piece",0)
    If Not IsError(findIdx) Then
       pType = jobs.DataBodyRange(i,findIdx)
    Else ' not find
       '... do something
    End If