VBA：编码时的不同要求 [英] VBA: Different requirements when coding

问题描述

这是上一篇文章的后续问题。

所以我工作的公司最近从2003年到2013年更新了Excel。我现在有一些非常基本的VBA代码的问题。 Cells.AutoFilter（x，y）特别是给我的问题。

So the company I work for has recently updated their Excel from 2003 to 2013. I am now having issues with some pretty basic VBA code. The line Cells.AutoFilter(x, y) in particular is giving me issues.

我在几个月前写了一个非常难看的程序，看起来像这样：

I wrote a very ugly program months ago which looks something like this:

...
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=11, Criteria1:= _
    "0"
If wf.CountA(r) > 0 Then
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=8, Criteria1:= _
    Array("baseunitprice", "burden", "MTLBURRATE", "PurPoint", "Vendornum"), Operator _
    :=xlFilterValues
Range("K2:K50000").SpecialCells(xlCellTypeVisible).Select
ActiveCell.FormulaR1C1 = "MACROUSE"
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=8
...

这是我写的第一个程序，由于明显的原因必须重写。

This is the first program I wrote and must be rewritten for obvious reasons.

为了以更优雅，可读的方式镜像上述代码，我在同一个模块中创建了另一个Sub：

In an attempt to mirror the above code in a more elegant, readable way, I created another Sub in the same module:

Sub ActualProgram()

Dim firstRow As Integer
Dim lastRow As Integer
Dim firstCol As Integer
Dim lastCol As Integer
Dim allRange As Range
Dim vRange As Range
Dim bRange As Range
Dim commentsCol As Integer
Dim commentsColRng As Range
Dim fieldNameCol As Integer
Dim userCol As Integer

If Cells(2, 1) <> "" Then

    DeleteEmptyRows

    firstCol = 1
    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    firstRow = 2
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row

    commentsCol = Rows(1).find("Comments").Column '11
    fieldNameCol = Rows(1).find("Field Name").Column '8
    userCol = Rows(1).find("User").Column '4

    Set allRange = Range(Cells(firstRow, firstCol), Cells(lastRow, lastCol))
    Set commentsColRng = Range(Cells(firstRow, commentsCol), Cells(lastRow, commentsCol))

    ActiveSheet.ListObjects("Table1").Range.AutoFilter 11, "0"     'WORKS
    Cells.AutoFilter commentsCol, "0"                              'FAILS

    Call MarkFieldNames(fieldNameCol, commentsColRng)

    Call MarkNonSMFields(commentsColRng)

    Call TargetFieldNames(fieldNameCol, commentsCol)

End If

End Sub

ub从来没有在以前的程序中被调用，当然。我只想把这两个代码放在一起，所以我可以在写新的代码时参考上一个。

This sub is never called in the previous program, of course. I just wanted to have both codes together so I could refer to the previous one while writing the new one.

我在新代码中遇到的问题是
Cells.AutoFilter commentsCol，0。

The line I'm having issues with in the new code is Cells.AutoFilter commentsCol, "0".

我在旧代码中使用的行是 ActiveSheet.ListObjects（Table1）。Range.AutoFilter字段：= 11，Criteria1：=0

The line which I used in the old code is ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=11, Criteria1:="0"

旧代码仍然可以正常工作。新的一个抛出一个 AutoFilter方法的Range类失败运行时错误。在我看来，这两行完全相同，我使用了 Cells.AutoFilter（x，y）的行数太多，无错Excel

The old code still works fine. The new one throws an AutoFilter method of Range class failed run-time error. In my eyes, these two lines do the exact same thing, and I've used the line Cells.AutoFilter(x, y) too many times to count without error on Excel 2013.

是否有需要更改的设置？我问，因为我在 VBA>工具>选项>编辑器中看到有代码设置选项，例如需求变量声明 ，这使我相信可能会有一个设置，禁用我称为 AutoFilter（）方法的方式。

Is there a setting I need to change? I ask because I see in VBA > Tools > Options > Editor there are Code Settings options such as Require Variable Declaration, which leads me to belive that there may be a setting which disables the way I call the AutoFilter() method.

谢谢你的时间。

推荐答案

。因此， ActiveSheet.ListObjects（Table1）... 表格与普通单元格不同，因此在自动过滤时必须具体定位。

I am working with a table. Hence ActiveSheet.ListObjects("Table1")... Tables are treated differently than regular cells, and therefore, must be specifically targeted when autofiltering.

这篇关于VBA：编码时的不同要求的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！