在 VBA 中使用变量进行自定义排序

Custom Sorting with variables in VBA

提问by Ship72

The macro below is a custom sort that I recorded. I replaced the actual table name with variable so I can run this for any other table name. I didn't want it to be specific to one name. When I run the macro I get a:

下面的宏是我录制的自定义排序。我用变量替换了实际的表名，所以我可以为任何其他表名运行它。我不希望它特定于一个名称。当我运行宏时，我得到一个：

Run-time error '1004': the item with the specified name wasn't found

运行时错误“1004”：未找到具有指定名称的项目

When I click the help button it sends me to Excel help menu. Could you guys let me know what I am missing? And if possible is there a way to shorten up my code. I know recordings can be long.

当我单击帮助按钮时，它会将我发送到 Excel 帮助菜单。你们能告诉我我错过了什么吗？如果可能的话，有没有办法缩短我的代码。我知道录音可能很长。

**Note all of this is in a table

**注意所有这些都在一张表中

Dim sh As Worksheet
Dim TableName As String

 Set sh = ActiveSheet
 TableName = sh.Name


    ActiveWorkbook.Worksheets(TableName).ListObjects(TableName).sort. _
      SortFields.Clear
    ActiveWorkbook.Worksheets(TableName).ListObjects(TableName).sort. _
      SortFields.Add Key:=Range(TableName, [BEVEL]), SortOn:=xlSortOnValues,
      Order:=xlAscending, _
      CustomOrder:="BEVEL_YES_MITER,BEVEL_NO_RADIUS", DataOption:=xlSortNormal _

    ActiveWorkbook.Worksheets(TableName).ListObjects("TableName").sort. _
      SortFields.Add Key:=Range(TableName, [MATERIAL]), SortOn:=xlSortOnValues, _
      Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets(TableName).ListObjects(TableName).sort. _
      SortFields.Add Key:=Range(TableName, [Length]), SortOn:=xlSortOnValues, _
      Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets(TableName).ListObjects(TableName).sort
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
     End With
Selection.AutoFilter
End Sub

采纳答案by RBarryYoung

First, I notice that though most of your table references are like this ..ListObjects(TableName).., one of them looks like this ..ListObjects("TableName").., so I'm guessing that's where the error is coming from.

首先，我注意到虽然你的大部分表引用都是这样的..ListObjects(TableName)..，但其中一个看起来像这样..ListObjects("TableName")..，所以我猜这就是错误的来源。

However, in general I'd recommend putting not just the table-name into a variable, but also the table itself, like this:

但是，总的来说，我建议不仅将表名放入变量中，还将表本身放入变量中，如下所示：

Dim sh As Worksheet
Dim TableName As String
Dim theTable As ListObject

 Set sh = ActiveSheet
 TableName = sh.Name
 Set theTable = ActiveWorkbook.Worksheets(TableName).ListObjects(TableName)

    theTable.sort.SortFields.Clear
    theTable.sort.SortFields.Add _
      Key:=Range(TableName & "[BEVEL]"), SortOn:=xlSortOnValues,
      Order:=xlAscending, _
      CustomOrder:="BEVEL_YES_MITER,BEVEL_NO_RADIUS", DataOption:=xlSortNormal 

    theTable.sort.SortFields.Add 
      Key:=Range(TableName & "[MATERIAL]"), SortOn:=xlSortOnValues, _
      Order:=xlAscending, DataOption:=xlSortNormal
    theTable.sort.SortFields.Add  _
      Key:=Range(TableName & "[Length]"), SortOn:=xlSortOnValues, _
      Order:=xlDescending, DataOption:=xlSortNormal
    With theTable.sort
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
     End With
Selection.AutoFilter
End Sub

---

---

(Note: corrected the line breaks)

（注意：更正了换行符）

---

---

(Note: corrected Range table-column name parameters)

（注意：更正的范围表列名称参数）