VBA 新手：编译错误无效限定符。

New to VBA: Compile Error Invalid Qualifier.

提问by Thomas

This code is designed to take a spreadsheet of raw data, omit several columns, and reformat what remains. I welcome any and all critiques of the code as I am new to VBA and know nothing. The key problem is at the end of the code on the line indicated with astriscs below. This is the spot where the "Compile Error: Invalid Qualifier" comes up. I'm trying to apply the formatting to Columns B and F, but I only want it to go as far as the last row of date. The last row of data will vary from one sheet to the next.

此代码旨在获取原始数据的电子表格，省略几列，并重新格式化剩余的内容。我欢迎对代码的任何和所有批评，因为我是 VBA 新手并且一无所知。关键问题是在下面用 astriscs 指示的行上的代码末尾。这是出现“编译错误：无效限定符”的地方。我正在尝试将格式应用于 B 列和 F 列，但我只希望它一直到日期的最后一行。最后一行数据会因一张纸而异。

When the error is triggered, the debugger highlights the word "count".

当错误被触发时，调试器会突出显示“count”这个词。

Thanks in advance for your help.

在此先感谢您的帮助。

Sub Macro2()
'
' Macro2 Macro
'
Union(Range("A:A"), Range("F:F"), Range("K:Q"), Range("S:V")).Delete
Range("A1").Select
ActiveCell.FormulaR1C1 = "FIRST"
Range("B1").Select
ActiveCell.FormulaR1C1 = "LAST"
Range("C1").Select
ActiveCell.FormulaR1C1 = "G"
Range("D1").Select
ActiveCell.FormulaR1C1 = "PHONE"
Range("E1").Select
ActiveCell.FormulaR1C1 = "ADDRESS"
Range("F1").Select
ActiveCell.FormulaR1C1 = "CITY"
Range("G1").Select
ActiveCell.FormulaR1C1 = "STATE"
Range("H1").Select
ActiveCell.FormulaR1C1 = "ZIP"
Range("I1").Select
ActiveCell.FormulaR1C1 = "MONTH"
Range("J1").Select
ActiveCell.FormulaR1C1 = "YEAR"
Columns("e:h").Insert Shift:=xlToRight
Columns("A:B").ColumnWidth = 12
Columns("C:C").ColumnWidth = 2
Columns("D:d").ColumnWidth = 13
Columns("e:e").ColumnWidth = 0.38
Columns("F:F").ColumnWidth = 5
Columns("G:G").ColumnWidth = 11
Columns("H:H").ColumnWidth = 0.38
Columns("I:N").ColumnWidth = 14
**Union(Range("B:B"),Range("F:F")).Rows.Count.End(xlUp).Row**
Range("B1").Activate
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent5
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With


End Sub 

回答by Tony Dallimore

I use this technique when I get confused about the type of object I have created by stringing properties.

当我对通过串接属性创建的对象类型感到困惑时，我会使用这种技术。

Within Excel's Visual Basic Editor, create a new module if you do not have an existing one that you wish to use in this way. If you select the module in Project Explorer and click F4, you can change the module's name to "Experiments" say.

如果您没有要以这种方式使用的现有模块，请在 Excel 的 Visual Basic 编辑器中创建一个新模块。如果在 Project Explorer 中选择模块并单击 F4，则可以将模块的名称更改为“Experiments”。

Type or copy:

输入或复制：

Option Explicit
Sub TestA()

End Sub

I alwaysstart my modules with Option Explicit. Look Option Explicitup in VBA Help and it will tell you why this is a good idea.

我总是以Option Explicit. Option Explicit在 VBA 帮助中查找，它会告诉您为什么这是一个好主意。

I have also created an empty sub-routine into which I will type some statements.

我还创建了一个空子例程，我将在其中键入一些语句。

Start typing a new statement so you have:

开始输入新语句，以便您：

Sub TestA()

  Debug.Print Range("B:B").

End Sub

When you type the period at the end of this new line, a pop-up window will show you the available methods and properties. This list will show, as expected, all the methods and properties of a Range. Type "Address" or select Address from the list to get:

当您在新行的末尾键入句点时，弹出窗口将显示可用的方法和属性。该列表将按预期显示 Range 的所有方法和属性。键入“地址”或从列表中选择地址以获取：

Sub TestA()

  Debug.Print Range("B:B").Address

End Sub

Click F5 to run this macro and the following will appear in the Immediate Window:

单击 F5 运行此宏，立即窗口中将显示以下内容：

$B:$B

This is the address of all rows in column B which is what you would expect.

这是 B 列中所有行的地址，这正是您所期望的。

Now add two further statements to the macro:

现在向宏添加两个进一步的语句：

  Debug.Print Range("F:F").Address
  Debug.Print Union(Range("B:B"), Range("F:F")).Address

Run this macro again and you will get:

再次运行这个宏，你会得到：

$B:$B
$F:$F
$B:$B,$F:$F

Again this is what was expected.

这再次是预期的。

Now add:

现在添加：

  Debug.Print Union(Range("B:B"), Range("F:F")).Rows.

The pop-up window that appears will be unchanged because Range.Rowsis still a range.

出现的弹出窗口将保持不变，因为Range.Rows仍然是一个范围。

Complete the statement by adding or selecting "Address" and run the macro again to get:

通过添加或选择“地址”来完成语句并再次运行宏以获得：

$B:$B
$F:$F
$B:$B,$F:$F
$B:$B,$F:$F

This may not be what you expected but think about it. $B:$B,$F:$Fis all rows in columns B and F so adding the property Rowsdoes not change the address.

这可能不是您所期望的，但请考虑一下。 $B:$B,$F:$F是 B 列和 F 列中的所有行，因此添加属性Rows不会更改地址。

Now add the following statements to the macro:

现在将以下语句添加到宏中：

  Debug.Print Union(Range("B:B"), Range("F:F")).Count
  Debug.Print Union(Range("B:B"), Range("F:F")).Rows.Count

Run the macro and these statements will each output an integer. I am using Excel 2003 so I get:

运行宏，这些语句将每个输出一个整数。我使用的是 Excel 2003，所以我得到：

 131072 
 65536 

If you are using a later version of Excel, you will get larger integers. The second integer is the number of rows in a worksheet for your version of Excel. The first integer is the number of cells in two columns of a worksheet for your version of Excel.

如果您使用更高版本的 Excel，您将获得更大的整数。第二个整数是您的 Excel 版本的工作表中的行数。第一个整数是您的 Excel 版本的工作表的两列中的单元格数。

Now add:

现在添加：

  Debug.Print Union(Range("B:B"), Range("F:F")).Rows.Count.

When you type the final period, no pop-up window will appear because an integer has no method or property that you can select in this way. Method .End(xlUp)operates on a range; it is not a property of Countwhich is why you get "Invalid qualifier".

当您键入最后一个句点时，不会出现弹出窗口，因为整数没有您可以通过这种方式选择的方法或属性。方法.End(xlUp)对一个范围进行操作；它不是Count您得到“无效限定符”的属性。

It is very easy to get oneself confused when stringing properties together. Personally I avoid stringing properties because even if it is faster to run, it takes longer for me to understand and debug. There are situations in which minimising runtime is the top priority but is this one of those cases? How many hours have you wasted with this approach?

将属性串在一起时很容易让自己感到困惑。就我个人而言，我避免串接属性，因为即使它运行得更快，我也需要更长的时间来理解和调试。在某些情况下，最小化运行时间是重中之重，但这是其中一种情况吗？你用这种方法浪费了多少小时？

Consider:

考虑：

  Dim Rng1 As Range
  Dim Rng2 As Range
  Dim Rng3 As Range
  Dim RowMax As Long

  Set Rng1 = Range("B:B")
  Set Rng2 = Range("F:F")
  Set Rng3 = Union(Rng1, Rng2)
  RowMax = Rng3.Count

  Debug.Print RowMax

  Debug.Print Rng3.Find("*", Range("B1"), xlValues, xlWhole, xlByRows, xlPrevious).Row

You do not need RowMaxbut I have included it so you are absolutely clear what Rng3.Countreturns. I have also gone OTT with the ranges. I would be happy to type: Set Rng3 = Union(Range("B:B"), Range("F:F"))because I find it easy to understand.

您不需要，RowMax但我已将其包含在内，因此您绝对清楚Rng3.Count返回的是什么。我也使用了 OTT 的范围。我很乐意打字：Set Rng3 = Union(Range("B:B"), Range("F:F"))因为我觉得它很容易理解。

Method .End(xlUp)operates on a cell. MultiCellRange.End(xlUp).Rowis valid syntax but I cannot get it to return useful information. If you want to use .End(xlUp)consider:

方法.End(xlUp)对单元格进行操作。 MultiCellRange.End(xlUp).Row是有效的语法，但我无法让它返回有用的信息。如果您想使用，请.End(xlUp)考虑：

  Dim RowMaxColB As Long
  Dim RowMaxColF As Long

  RowMaxColB = Cells(Rows.Count, "B").End(xlUp).Row
  RowMaxColF = Cells(Rows.Count, "F").End(xlUp).Row

I agree with Siddharth, Findappears to be the best approach in this situation. However, you should look at this answer of mine, https://stackoverflow.com/a/20849875/973283, to a different question. It includes a macro that demonstrates a selection of methods of finding last rows and columns and shows the situations in which they fail.

我同意悉达多，Find在这种情况下似乎是最好的方法。但是，你应该看看我的这个答案，https://stackoverflow.com/a/20849875/973283，另一个问题。它包含一个宏，该宏演示了查找最后一行和最后一列的方法的选择，并显示了它们失败的情况。

回答by Tony Dallimore

You have not asked the question to which this is an answer. See Requested answerfor the answer you requested. Your code works so I doubt you know it is bad VBA.

你还没有问这个问题的答案。有关您请求的答案，请参阅请求的答案。您的代码有效，所以我怀疑您是否知道它是糟糕的 VBA。

From the comment at the top, I assume this code was created by the Macro Recorder. The Macro Recorder outputs syntactically correct code but it is poor code. At least part of the reason for the poor code is that statements are being recorded as you type. The Recorder does not know your objective. If the cursor is in cell A1 and you press Right, the Recorder will select B1 because that is what happens on the screen. There may be situations in VBA for which Selectis a useful method but they are rare.

从顶部的评论来看，我假设这段代码是由宏记录器创建的。宏记录器输出语法正确的代码，但代码很差。糟糕代码的至少部分原因是在您键入时记录了语句。记录器不知道您的目标。如果光标在单元格 A1 中并按Right，记录器将选择 B1，因为这就是屏幕上发生的情况。在 VBA 中可能存在Select一些有用的方法，但这种情况很少见。

The Recorder also operates on the active worksheet which is rarely a good idea. If the wrong worksheet is active when this macro is started, it will be destroyed with 13 columns deleted.

记录器还对活动工作表进行操作，这很少是一个好主意。如果在启动此宏时错误的工作表处于活动状态，它将被销毁并删除 13 列。

I will assume you only have one workbook open so I can assume the active workbook is the required workbook. It is only a little more complicated to handle multiple workbooks but I would rather ignore that complication.

我假设您只打开了一个工作簿，因此我可以假设活动工作簿是所需的工作簿。处理多个工作簿只是稍微复杂一点，但我宁愿忽略这种复杂性。

Consider:

考虑：

Sub TestB()

  With Worksheets("Sheet1")
    .Range("A1").Value = "FIRST"
  End With

End Sub

With Worksheets("Sheet1")states this code is to operate on the worksheet named "Sheet1" of the active workbook. End Withterminates a With statement.

With Worksheets("Sheet1")声明此代码将在活动工作簿的名为“Sheet1”的工作表上进行操作。 End With终止 With 语句。

You can nest With statements:

您可以嵌套 With 语句：

  With Worksheets("Sheet1")
    With .Range("A1")
      .Value = "FIRST"
      .Font.Bold = True
      .Font.Color = RGB(0, 255, 255)
    End With
  End With

The period at the beginning of .Range("A1")states .Range("A1")is to operate within the current With which is Worksheets("Sheet1"). If I omit that period, Range("A1")operates on the active worksheet.

.Range("A1")状态开始时的周期.Range("A1")是在当前的 With 内运行，它是Worksheets("Sheet1")。如果我省略那段时间，则Range("A1")在活动工作表上进行操作。

The period at the beginning of .Valuestates it is to operate on the current With which is Range("A1")within Worksheets("Sheet1"). If I omit that period, Valueoperates on the active cell.

在年初的周期.Value状态是对与作为当前工作Range("A1")中Worksheets("Sheet1")。如果我省略那段时间，则Value在活动单元格上运行。

The use of With statements makes your code more compact and much clearer. However, you mustinclude the periods. Consider:

With 语句的使用使您的代码更加紧凑和清晰。但是，您必须包括句点。考虑：

  Dim Rng1 As Range

  With Worksheets("Sheet2")
    Set Rng1 = Union(Range("A:A"), Range("C:D"), Range("F:G"))
    Debug.Print Rng1.Address
    Debug.Print Rng1.Worksheet.Name
    Set Rng1 = Union(.Range("A:A"), .Range("C:D"), .Range("F:G"))
    Debug.Print Rng1.Address
    Debug.Print Rng1.Worksheet.Name
  End With

If the active worksheet is "Sheet1", the output is:

如果活动工作表是“Sheet1”，则输出为：

$A:$A,$C:$D,$F:$G
Sheet1
$A:$A,$C:$D,$F:$G
Sheet2

The range addresses look the same but they apply to different worksheets.

范围地址看起来相同，但它们适用于不同的工作表。

You could replace a lot of your code with:

您可以将很多代码替换为：

  With Worksheets("Sheet1")
    .Range("A1").Value = "FIRST"
    .Range("B1").Value = "LAST"
    .Range("C1").Value = "G"
    .Range("D1").Value = "PHONE"
    .Range("E1").Value = "ADDRESS"
    .Range("F1").Value = "CITY"
    .Range("G1").Value = "STATE"
    .Range("H1").Value = "ZIP"
    .Range("I1").Value = "MONTH"
    .Range("J1").Value = "YEAR"
  End With

You can also write:

你也可以写：

  With Worksheets("Sheet1")
    .Range("A1:J1").Value = Array("FIRST", "LAST", "G", "PHONE", "ADDRESS", _
                                  "CITY", "STATE", "ZIP", "MONTH", "YEAR")
 End With

This VBA is a lot more advanced. You can copy values from a worksheet range to an array or from an array to a worksheet range. There are a number of questions with answers that explore this capability and I will not repeat those answers here. This is just a demonstration for you to explore later if you are interested.

这个VBA要先进得多。您可以将值从工作表区域复制到数组或从数组复制到工作表区域。有许多问题和答案探索了这种能力，我不会在这里重复这些答案。这只是一个演示，如果您有兴趣，以后可以探索。

I do not like what you are doing. In your first statement you delete some columns so column B becomes column A and column R becomes column I. You then change the column headings in their new positions. Since you are only moving the data, why do you need to change the column headings? More importantly, in nine months someone is going to add a column or rearrange existing columns. Your code will carry on regardless and your name will be mud.

我不喜欢你在做什么。在您的第一条语句中，您删除了一些列，因此 B 列变为 A 列，R 列变为 I 列。然后您将列标题更改为新位置。既然你只是在移动数据，那为什么还需要改变列标题呢？更重要的是，在九个月内，有人将添加一列或重新排列现有的列。无论如何，您的代码都会继续运行，而您的名字将变得泥泞。

I would prefer something like:

我更喜欢这样的：

  With Worksheets("Sheet1")
    If .Range("B1").Value = "FIRST" And .Range("C1").Value = "LAST" And _
       .Range("D1").Value = "G" And .Range("E1").Value = "PHONE" And _
       .Range("G1").Value = "ADDRESS" And .Range("H1").Value = "CITY" And _
       .Range("I1").Value = "STATE" And .Range("J1").Value = "ZIP" And _
       .Range("R1").Value = "MONTH" And .Range("W1").Value = "YEAR" Then
      ' Column headings as expected.  Continue with macro.
    Else
      Call MsgBox("I am sorry but I cannot proceed because the column " & _
                  "headings are not as I expected.", vbOKOnly)
      Exit Sub
    End If
  End With

If your code requires a worksheet to be in a particular format and that format might change over time then check that format.

如果您的代码需要使用特定格式的工作表，并且该格式可能会随时间发生变化，请检查该格式。