使用“TO”理解VBA代码和“步骤” [英] Understanding VBA code using "TO" and "STEP"

问题描述

我被赋予了这个代码来运行在excel中，但我正在试图理解它的含义。我特别想知道可变部分？任何帮助将不胜感激。

  sub SortNames 
 
 Dim LR As Long 
 
 Application.ScreenUpdating = False 
 
 Sheets（Stores）。选择
 
 LR = Cells（Rows.Count，1）.End（xlUp）.Row 
 
对于i = LR到2步骤-1 
 
'这里可以添加名称
 
如果Range（f& i ）< Amanda WeaverAnd Range（f& i）< Debra Wiesemann和_ 
 Range（f& i）<> Drew Simpson和Range（f& i）< 詹姆斯·霍华德和_ 
范围（f& i）<> Jeff HrubyAnd Range（f& i）< Jessica Czupryn和_ 
 Range（f& i）<> Kevin JankeAnd Range（f& i）< Matthew Hudspeath和_ 
 Range（f& i）<> Maurey Peterson和范围（f& i） Noah Hadro和_ 
 Range（f& i）<> Thomas McHenryAnd Range（f& i）< Thomas McHenry然后
 
 Range（f& i）.EntireRow.Delete Shift：= xlUp 
 End If 
 
 Next i 
 
 Application.ScreenUpdating = True 
 
 End Sub 
  

解决方案

我们一行一行地浏览代码。如果我太基础，我提前道歉;但是，其他人可能会发现有用的信息。

  Sub SortNames（）
  

这是程序的名称

  Dim LR As Long 
  

创建一个类型为 Long 的变量来存储行号。

这里正在使用 Long 类型的原因（而不是整数）是因为在较新版本的Excel（2007和更高版本）中，有更多的行（1,048,576）可以适合 Integer 。所以在Excel中获取行号/计数时，您应该始终使用 Long ，否则数字变大时可能会收到错误。

  Application.ScreenUpdating = False 
  

这是一个很好的一般的VBA编码技术;它可以防止屏幕在执行过程中闪烁，其中A.提供了更愉悦的用户体验，B使代码运行更快。请注意，一旦VBA程序完成，ScreenUpdating将自动返回（否则您将无法再与Excel进行交互！）。

 表格（商店）。选择
  

显然：选择名为商店的工作表。由于之前的 ScreenUpdating 行，用户在该过程完成之前将不会看到此工作表。我不知道为什么程序的作者选择使用在这里选择我认为最好使用与...结束与块。一般来说，如果使用选择可以避免，应该是。

  LR = Cells（Rows.Count，1）.End（xlUp）.Row 
  

code> Cells（）函数将返回一个范围对象，引用 ActiveSheet （这是商店）。 单元格（Rows.Count，1）返回第一列最后一行的Range对象（Excel 2007或更高版本中的单元格A1048576）。 .End（）方法从一些起始位置的行或列中获取最后使用的 单元格。 .End（xlUp）可以从下面开始列出一列最后一个使用的单元格。所以这整个声明说：

转到A列的最下方，然后上升直到找到第一个使用的单元格，然后告诉我什么行数字，并将其存储在 LR 中。

辅助：如果一个根据我上面的建议，正在使用块，这一行将是：

  LR = .Cells（Rows.Count，1）.End（xlUp）.Row 
  

请注意，作者这个程序似乎知道他们在做什么;发现最后一个使用单元格开始，而不是从整个工作簿中的最后一个单元格开始，是一个非常好的主意。否则，在其余的程序中，Excel将删除工作簿中的每一个空行，没有理由！除了浪费电脑电源（呃，谁在乎，对吗？），需要更长时间才能完成。以这种方式从最后一个使用的行开始是一个非常好的技术，用于更快的程序。

  i = LR到2 Step -1 
  

这个对于循环开始于 LR （对于i = LR ），执行循环体，然后减1从 i （步骤-1 表示从 i中减去1 为每个步骤），并循环。直到 i 等于2（ To 2 ）。请注意，在最后一次执行循环时， i = 2（它不 skip 2; To 语句包含在内）。

 '这里可以添加名称
  

这只是一个评论。

 如果Range（f& i）< Amanda WeaverAnd Range（f& i）< Debra Wiesemann和_ 
 Range（f& i）<> Drew Simpson和Range（f& i）< 詹姆斯·霍华德和_ 
范围（f& i）<> Jeff HrubyAnd Range（f& i）< Jessica Czupryn和_ 
 Range（f& i）<> Kevin JankeAnd Range（f& i）< Matthew Hudspeath和_ 
 Range（f& i）<> Maurey Peterson和范围（f& i） Noah Hadro和_ 
 Range（f& i）<> Thomas McHenryAnd Range（f& i）< Thomas McHenry然后
  

这是一个相当大的如果语句，它测试条件或条件，而然后执行一些代码，如果条件是 True 。您可以猜测如果语句在这里做，但我会解释。

该语句测试以查看 Range（f& i）* some name *评估值为 True 。 Range（f& i）返回一个范围对象在 ActiveSheet 中，单元格地址为列F，行 i （记住 i 从 LR 开始，然后倒数到2）。 < / code> part是一个运算符，只是意味着不等于。 And part是另一个运算符，它只是同时评估多个条件，所以如果 AND 运算符两边的BOTH条件是 True ，整个语句的结果为true，如果一个或者两个都是 False ，那么你会得到一个错误，当你键入 AND 一起，他们都必须是 True 以获得 True 。第一次通过对于循环，如果行 LR 中的值，列F不等于列表中的任何名称，那么如果语句的正文将执行。

关于这里正在发生的一切，还有一个解释的话：VBA为了使事情变得更容易，自动地做一些事情。其中一个事情是解释你的意图，即使它实际上没有意义。如果您考虑到这一点，将 Range 对象与Amanda Weaver之类的字符串进行比较并不重要。 Range 对象不仅仅是它包含的字符串值;这里是一个小小的列表，它们是 Range 的一部分，而不是其值：公式，名称，背景颜色，文本颜色，文本格式，地址，一个父，一行和一列，还有很多其他的东西。但是，VBA假定当您说 IF Range（F100）<> Amanda Weaver，您希望将单元格F100的值与Amanda Weaver进行比较，而不是其他任何部分。

  Range（f& i）.EntireRow.Delete Shift：= xlUp 
  

这是如果块的正文。如果您以上的测试条件评估为 True ，则会发生任何事情。

以前介绍了 Range（f& i）部分。部分 EntireRow.Delete 意味着删除 Range 对象所属的整个行（记住：它只会删除行如果列F中的值不包含列表中的任何名称）。最后一点， Shift：= xlUp ，告诉Excel 如何删除该行。这意味着删除的单元格下方的所有单元格都将向上移动（相对于删除的单元格右侧的所有单元格左移）。

 结束如果
  

Duh - 这将结束你的如果阻止。

  Next i 
  内为循环。从到的所有内容将再次执行，除了 i 等于值小于1最近的值（直到等于2，包含）
  Application.ScreenUpdating = True 
  
如上所述，这行可能不是必需的。但是，这并不是一件坏事，而且习惯上明确地说你打算在你的代码中发生的事情，即使这些事情自动发生在魔术上，原因有二：
 
 
后来当你回来看看你的代码，你会忘记你的意思。真的，你完全是。不要争论
 
 
当其他人使用你的代码时，你不能认为他们知道你所知道的一切。所以尽可能地明白你打算发生什么。 
 
 
 _ 
  End Sub 
  
结束了这个过程。 YA。
 
 
如何学习VBA 
 
 
 
学习VBA可能是一个令人沮丧的经历（对我而言） ，找到自己的答案通常会非常令人满意。考虑到这一点，让我提出一些建议。 
 
 
 
我找到了一个很好的地方来获取关于陌生VBA代码的意义的信息是MSDN;然而，实际上，在MSDN上发现任何东西都是一个真正的伎俩，所以我改用Google。一个很好的搜索技术是：MSDN VBA 这里不熟悉的代码。 
 
 
 
 这是MSDN页面描述 For To Next 循环结构。 
 
 
 
无论何时遇到您以前未见过的功能或关键字，请花时间查阅并阅读。我发现这是最有效的学习技巧之一。
 
I was given this code to run in excel but I am trying to understand what it means. I especially would like to know the Variable portion? Any help would be greatly appreciated.
sub SortNames

Dim LR As Long

Application.ScreenUpdating = False

Sheets("Stores").Select

LR = Cells(Rows.Count, 1).End(xlUp).Row

For i = LR To 2 Step -1

 'Here is where you can add the names

 If Range("f" & i) <> "Amanda Weaver" And Range("f" & i) <> "Debra Wiesemann" And _
   Range("f" & i) <> "Drew Simpson" And  Range("f" & i) <> "James Howard" And _
   Range("f" & i) <> "Jeff Hruby" And Range("f" & i) <> "Jessica Czupryn" And _
   Range("f" & i) <> "Kevin Janke" And Range("f" & i) <> "Matthew Hudspeath" And _
   Range("f" & i) <> "Maurey Peterson" And Range("f" & i) <> "Noah Hadro" And _
   Range("f" & i) <> "Thomas McHenry" And Range("f" & i) <> "Thomas McHenry" Then

  Range("f" & i).EntireRow.Delete Shift:=xlUp
 End If

Next i

Application.ScreenUpdating = True

End Sub

解决方案
Let's go through the code line by line. I apologize in advance if I get too basic; however, other people might find the information helpful. 
Sub SortNames()
This is the name of the procedure
Dim LR As Long
Creates a variable of type Long to store the row number. 

The reason Long type is being used here (as opposed to Integer) is because in newer versions of Excel (2007 and later), there are more rows (1,048,576) than can fit in an Integer. So you should always use Long when getting a row number/count in Excel or you could get an error when the numbers get large.
Application.ScreenUpdating = False
This is a good general VBA coding technique; it prevents the screen from flickering during execution of a procedure, which A. provides a more pleasant user experience, and B. makes the code run faster. Note that once the VBA procedures have completed, ScreenUpdating automatically gets turn back on (otherwise you wouldn't be able to interact with Excel anymore!). 
Sheets("Stores").Select
Obviously: selects the Sheet named "Stores". Because of the previous ScreenUpdating line, the user will not see this sheet selected until the procedure completes. I'm not sure why the writer of the procedure chose to use Select here; I think it would be better to use a With... End With block. Generally speaking, if usage of Select can be avoided, it should be.
LR = Cells(Rows.Count, 1).End(xlUp).Row
The Cells() function will return a Range object referring to the ActiveSheet (which is "Stores"). Cells(Rows.Count, 1) returns the Range object for the very last row in the first column (which is cell A1048576 in Excel 2007 or later). The .End() method gets the last used cell in a row or a column from some starting place. .End(xlUp) gets you the last used cell in a column starting from below and going up. So this entire statement says: 

"go to the very bottom of column A, then go up until you find the first used cell, then tell me what row number that is and store it in LR."

Parenthetically: if a With block were being used as I suggested above, this line would be:
LR = .Cells(Rows.Count, 1).End(xlUp).Row
Note that the writer of this procedure seems to know what they are doing; finding that last used cell to start with, and to not start with the very last cell in the entire workbook, is a very good idea. Otherwise, in the rest of the procedure, Excel would delete every empty row in the workbook, for no reason! Apart from being a waste of computer power (meh, who cares, right?), it would take much longer to complete. Starting with the last used row in this manner is a very good technique for faster procedures.
For i = LR To 2 Step -1
This For loop with starts at LR (For i = LR), executes the body of the loop, then subtracts 1 from i (Step -1 means "subtract 1 from i for each step") and loops again. It does this until i is equal to 2 (To 2). Note that on the last execution of the loop, i = 2 (it does not skip 2; the To statement is inclusive). 
 'Here is where you can add the names
This is just a comment.
 If Range("f" & i) <> "Amanda Weaver" And Range("f" & i) <> "Debra Wiesemann" And _
   Range("f" & i) <> "Drew Simpson" And Range("f" & i) <> "James Howard" And _
   Range("f" & i) <> "Jeff Hruby" And Range("f" & i) <> "Jessica Czupryn" And _
   Range("f" & i) <> "Kevin Janke" And Range("f" & i) <> "Matthew Hudspeath" And _
   Range("f" & i) <> "Maurey Peterson" And Range("f" & i) <> "Noah Hadro" And _
   Range("f" & i) <> "Thomas McHenry" And Range("f" & i) <> "Thomas McHenry" Then
This is a rather large If statement, which tests a condition or conditions, and Then executes some code if that condition is True. You can probably guess what the If statement is doing here, but I'll explain anyway. 

The statement tests to see if Range("f & i) <> "*some name*" evaluates to the value of True. Range("f" & i) returns a Range object in the ActiveSheet with the cell address of column F, and row i (remember i starts at LR, then counts down to 2). The <> part is an operator that just means "is not equal to". The And parts are another operator; it just evaluates multiple conditions at the same time. So if BOTH conditions on both sides of the AND operator are True, the entire statement evaluates to true. If either one or both are False, then you get a false. When you string the ANDs together, they ALL have to be True to get a True. So on the first time through the For loop, if the value in row LR, column F is not equal to any of the names in the list, then the body of the If statement will execute. 

A further word of explanation as to what exactly is happening here: VBA does some things auto-magically in order to makes things a bit easier. One of those things is interpreting what you meant to do even though it doesn't actually make sense. If you think about it, it doesn't really make sense to compare a Range object to a string like "Amanda Weaver". The Range object is much more than just the string value it contains; here is a small list of things that are part of a Range other than its value: a formula, a name, a background color, text color, text formatting, an address, a parent, a row, and column, and many other things. However, VBA assumes that when you say IF Range(F100) <> "Amanda Weaver", that you want it to compare the value of cell F100 to "Amanda Weaver", and not any of the other parts.
  Range("f" & i).EntireRow.Delete Shift:=xlUp
This is the body of the If block. Anything in here will occur if your test condition above evaluated to True. 

The Range("f" & i) part was previously explained. The part EntireRow.Delete means to delete the entire row to which that Range object belongs (remember: it only deletes the row if the value in column F did not contain any of the names in the list). The last bit, Shift:=xlUp, tells Excel how to delete the row. It means that all of the cells below the deleted cell will be shifted up (as opposed to all the cells to the right of the deleted cell being shifted left).
 End If
Duh - this ends your If block.
Next i
Remember that this entire time we have been inside of that For loop. Everything from the For up to this point will execute again, except with i equal to a value of 1 less than the most recent value (until it is equal to 2, inclusive).
Application.ScreenUpdating = True
As I explained above, this line is probably not necessary. However, it's not a bad thing, and it's good practice to be in the habit of explicitly saying things you intend to happen in your code, even if they happen auto-magically, for two reasons:

Later when you come back and look at your code, you are going to forget what you meant. Seriously, you totally are. Don't argue. 
When other people are going to use your code, you can't assume they know everything you know. So make it as obvious as possible what you intend to happen. 
_
End Sub
This ends the procedure. Yay.

How to learn VBA

Learning VBA can be a frustrating experience (it has been for me), and finding your own answers can often be very satisfying. With that in mind, let me offer some advice. 

I have found a good place to get information on the meaning of unfamiliar VBA code is MSDN; however, actually finding anything on MSDN can be a real trick, so I use Google instead. A good search technique is: "MSDN VBA unfamiliar code here". 

Here is the MSDN page describing the For To Next loop structure. 

Anytime you run into a function or keyword that you haven't seen before, take the time to look it up and read about it. I have found this to be one of the most effective learning techniques.

                        
这篇关于使用“TO”理解VBA代码和“步骤”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！