运行时错误"483“对象不支持此属性或方法”

Question

我试图为特定工作表的表进行自定义排序，但是我得到了运行时错误"483“"Object不支持此属性或方法”。

我将工作表名称和自定义列表顺序作为来自用户的字符串输入。

Option Explicit    
Sub SortRiskArea()

Dim wk As Worksheet
Dim Tb, Rb

Dim shtName As String
    shtName = InputBox(Prompt:="Enter the Worksheet Name that you want to sort." & vbNewLine & " Ex: Risk Register ", Title:="Hello", Default:="Risk Register")
    shtName = Trim(shtName)

Dim strName As String
    strName = InputBox(Prompt:="Enter the Sort Order for Risk Area" & vbNewLine & " Ex: Commercial, Technological, Management, Reputational, Governance, Operational", Title:="Hello", Default:="Commercial, Technological, Management, Reputational, Governance, Operational")
    strName = Replace(strName, " ", "")

Set wk = Sheets(shtName)

If shtName = "Risk Register" Then Tb = "Table1"
If shtName = "SAP BI" Then Tb = "Table13"
If shtName = "SAP BO" Then Tb = "Table14"
If shtName = "SAP BW" Then Tb = "Table15"
If shtName = "SAP PM" Then Tb = "Table16"
If shtName = "Mobility" Then Tb = "Table17"
If shtName = "SAP FI" Then Tb = "Table18"
If shtName = "SAP Service Desk" Then Tb = "Table19"

Rb = "[Risk Area]"
Rb = Tb & Rb

     Error Lines   > ActiveWorkbook.wk.ListObjects(Tb).Sort. _
                     SortFields.Add Key:=Range(Rb), SortOn:=xlSortOnValues, _
                     Order:=xlAscending, CustomOrder:= _
                     strName, _
                     DataOption:=xlSortNormal
    With ActiveWorkbook.wk.ListObjects(Tb).Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("B5").Select
End Sub

Answer 1

您在代码中非常不走运，因为大多数元素几乎都是正确的，但不幸的是，关键的元素缺少了最后一点的准确性。下面是清单：

1. 行ActiveWorkbook.wk.ListObjects(Tb).Sort试图访问不存在的ActiveWorkbook属性。wk本身就是一个Sheet对象，由于它在这一行中不存在，Set wk = Sheets(shtName)就会假定为ActiveWorkbook。因此，在这两种情况下，行都应该是wk.ListObjects(Tb).Sort。更好的是，您还可以像这样显式地设置wk：Set wk = ActiveWorkbook.Sheets(shtName)或Set wk = ThisWorkbook.Sheets(shtName)
2. 因为您还没有显式地设置它，所以这一行Key:=Range(Rb)假设ActiveSheet与目标表相反。所以应该说是Key:=wk.Range(Rb)
3. 定制的排序顺序是狡猾的野兽。我担心您的代码将无法工作，即使您会感到您实际上完全复制了自动生成的宏代码。它的实际工作方式是在CustomList对象中创建一个Application，然后用一个Integer引用它的索引。在下面的示例代码中，您将看到如何做到这一点，但是您应该知道，只有当您的自定义项是Strings时，它才能工作。
4. 您的最后一行可能不会做您想做的事情，因为Range("xx").Select将再次发生在ActiveSheet上，而您希望选择目标表。

还有其他几个更一般的编码点：

1. 您应该显式地声明每个变量。因此，这一行Dim Tb, Rb并不像Variants那样伟大，这只是增加了不必要的处理时间，使调试变得更加困难。
2. 用户输入框要求的用户太多了。他/她必须确保没有任何一个错误或错误的工作表/自定义值条目，否则将发生未处理的错误。此任务非常适合于Userform，其中可以有一个带有所有目标表名称的ComboBox和一个带有自定义订单项的ListBox。如果您将ComboBox ColumnCount更改为2，那么您也可以创建一个工作表名称映射。也许可以快速阅读一下Userforms，看看如何做到这一点；这真的很容易。
3. 如果您创建了Sheet到ListObject的映射，代码将更容易管理。您只需要这样做一次，您可以根据自己的意愿多次运行您的程序，而不需要每次运行所有的If语句。您还可以对任何更改和对象设置保持更多的控制。

下面的代码向您展示了如何做到这一点。这不是完美的编码，但它使每个点都没有不必要的分心：

Sub SortRiskArea()
    Dim tableMapping As Collection
    Dim map(1) As Variant
    Dim sortItems As Variant
    Dim sortSheet As Worksheet
    Dim sortObject As ListObject
    Dim sortKey As Range
    Dim sortOrder As Integer
    Dim userInput As String

    'Create the map of sheets to tables
    'Note: you'd do this at module-level if there are repeated sorts.
    Set tableMapping = New Collection
    Set map(0) = ThisWorkbook.Sheets("Risk Register")
    Set map(1) = map(0).ListObjects("Table1")
    tableMapping.Add map, map(0).Name
    Set map(0) = ThisWorkbook.Sheets("SAP BI")
    Set map(1) = map(0).ListObjects("Table13")
    tableMapping.Add map, map(0).Name
    Set map(0) = ThisWorkbook.Sheets("SAP BO")
    Set map(1) = map(0).ListObjects("Table14")
    tableMapping.Add map, map(0).Name
    Set map(0) = ThisWorkbook.Sheets("SAP BW")
    Set map(1) = map(0).ListObjects("Table15")
    tableMapping.Add map, map(0).Name
    Set map(0) = ThisWorkbook.Sheets("SAP PM")
    Set map(1) = map(0).ListObjects("Table16")
    tableMapping.Add map, map(0).Name
    Set map(0) = ThisWorkbook.Sheets("Mobility")
    Set map(1) = map(0).ListObjects("Table17")
    tableMapping.Add map, map(0).Name
    Set map(0) = ThisWorkbook.Sheets("SAP FI")
    Set map(1) = map(0).ListObjects("Table18")
    tableMapping.Add map, map(0).Name
    Set map(0) = ThisWorkbook.Sheets("SAP Service Desk")
    Set map(1) = map(0).ListObjects("Table19")
    tableMapping.Add map, map(0).Name

    'Acquire the target sheet
    On Error Resume Next
    Do
        userInput = InputBox(Prompt:="Enter the Worksheet Name that you want to sort." & vbNewLine & " Ex: Risk Register ", Title:="Hello", Default:="Risk Register")
        sortItems = Empty
        sortItems = tableMapping(userInput)
        If IsEmpty(sortItems) Then MsgBox "Invalid entry."
    Loop Until Not IsEmpty(sortItems)
    On Error GoTo 0

    Set sortSheet = sortItems(0)
    Set sortObject = sortItems(1)
    Set sortKey = sortSheet.Range(sortObject.Name & "[Risk Area]")

    'Acquire the custom sort order
    userInput = InputBox(Prompt:="Enter the Sort Order for Risk Area" & vbNewLine & " Ex: Commercial, Technological, Management, Reputational, Governance, Operational", Title:="Hello", Default:="Commercial, Technological, Management, Reputational, Governance, Operational")
    userInput = Replace(userInput, " ", "")
    Application.AddCustomList Split(userInput, ",")
    sortOrder = Application.CustomListCount

    'Conduct the sort
    With sortObject.Sort
        .SortFields.Clear
        .SortFields.Add Key:=sortKey, SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:=sortOrder, DataOption:=xlSortNormal
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    'Safe select "B5"
    sortSheet.Activate
    sortSheet.Range("B5").Select
End Sub