vba 按名称引用excel工作表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9628690/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Reference excel worksheet by name?
提问by Jonson Bylvaklov
I have the name of a worksheet stored as a string in a variable. How do I perform some operation on this worksheet?
我将工作表的名称作为字符串存储在变量中。如何在此工作表上执行某些操作？
I though I would do something like this:
我虽然我会做这样的事情：
nameOfWorkSheet = "test"
ActiveWorkbook.Worksheets(nameOfWorkSheet).someOperation()
How do I get this done?
我如何完成这项工作？
回答by Ryan
There are several options, including using the method you demonstrate, With, and using a variable.
有多种选择，包括使用您演示的方法、使用和使用变量。
My preference is option 4 below: Dima variable of type Worksheetand store the worksheet and call the methods on the variable or pass it to functions, however any of the options work.
我的偏好是下面的选项 4：Dim一个类型的变量Worksheet并存储工作表并调用变量上的方法或将其传递给函数，但是任何选项都有效。
Sub Test()
Dim SheetName As String
Dim SearchText As String
Dim FoundRange As Range
SheetName = "test"
SearchText = "abc"
' 0. If you know the sheet is the ActiveSheet, you can use if directly.
Set FoundRange = ActiveSheet.UsedRange.Find(What:=SearchText)
' Since I usually have a lot of Subs/Functions, I don't use this method often.
' If I do, I store it in a variable to make it easy to change in the future or
' to pass to functions, e.g.: Set MySheet = ActiveSheet
' If your methods need to work with multiple worksheets at the same time, using
' ActiveSheet probably isn't a good idea and you should just specify the sheets.
' 1. Using Sheets or Worksheets (Least efficient if repeating or calling multiple times)
Set FoundRange = Sheets(SheetName).UsedRange.Find(What:=SearchText)
Set FoundRange = Worksheets(SheetName).UsedRange.Find(What:=SearchText)
' 2. Using Named Sheet, i.e. Sheet1 (if Worksheet is named "Sheet1"). The
' sheet names use the title/name of the worksheet, however the name must
' be a valid VBA identifier (no spaces or special characters. Use the Object
' Browser to find the sheet names if it isn't obvious. (More efficient than #1)
Set FoundRange = Sheet1.UsedRange.Find(What:=SearchText)
' 3. Using "With" (more efficient than #1)
With Sheets(SheetName)
Set FoundRange = .UsedRange.Find(What:=SearchText)
End With
' or possibly...
With Sheets(SheetName).UsedRange
Set FoundRange = .Find(What:=SearchText)
End With
' 4. Using Worksheet variable (more efficient than 1)
Dim MySheet As Worksheet
Set MySheet = Worksheets(SheetName)
Set FoundRange = MySheet.UsedRange.Find(What:=SearchText)
' Calling a Function/Sub
Test2 Sheets(SheetName) ' Option 1
Test2 Sheet1 ' Option 2
Test2 MySheet ' Option 4
End Sub
Sub Test2(TestSheet As Worksheet)
Dim RowIndex As Long
For RowIndex = 1 To TestSheet.UsedRange.Rows.Count
If TestSheet.Cells(RowIndex, 1).Value = "SomeValue" Then
' Do something
End If
Next RowIndex
End Sub
回答by stenci
The best way is to create a variable of type Worksheet, assign the worksheet and use it every time the VBA would implicitly use the ActiveSheet.
最好的方法是创建一个类型的变量Worksheet，分配工作表并在每次 VBA 隐式使用ActiveSheet.
This will help you avoid bugs that will eventually show up when your program grows in size.
这将帮助您避免在程序变大时最终会出现的错误。
For example something like Range("A1:C10").Sort Key1:=Range("A2")is good when the macro works only on one sheet. But you will eventually expand your macro to work with several sheets, find out that this doesn't work, adjust it to ShTest1.Range("A1:C10").Sort Key1:=Range("A2")... and find out that it still doesn't work.
例如，Range("A1:C10").Sort Key1:=Range("A2")当宏仅在一张纸上工作时，类似的东西就很好。但是您最终将扩展您的宏以处理多张工作表，发现这不起作用，将其调整为ShTest1.Range("A1:C10").Sort Key1:=Range("A2")......并发现它仍然不起作用。
Here is the correct way:
这是正确的方法：
Dim ShTest1 As Worksheet
Set ShTest1 = Sheets("Test1")
ShTest1.Range("A1:C10").Sort Key1:=ShTest1.Range("A2")
回答by j boschiero
To expand on Ryan's answer, when you are declaring variables (using Dim) you can cheat a little bit by using the predictive text feature in the VBE, as in the image below. 
为了扩展 Ryan 的答案，当您声明变量（使用 Dim）时，您可以通过使用 VBE 中的预测文本功能来作弊，如下图所示。
If it shows up in that list, then you can assign an object of that type to a variable. So not just a Worksheet, as Ryan pointed out, but also a Chart, Range, Workbook, Series and on and on.
如果它出现在该列表中，那么您可以将该类型的对象分配给一个变量。因此，正如 Ryan 指出的那样，不仅仅是工作表，还有图表、范围、工作簿、系列等等。
You set that variable equal to the object you want to manipulate and then you can call methods, pass it to functions, etc, just like Ryan pointed out for this example. You might run into a couple snags when it comes to collections vs objects (Chart or Charts, Range or Ranges, etc) but with trial and error you'll get it for sure.
您将该变量设置为等于您要操作的对象，然后您可以调用方法，将其传递给函数等，就像 Ryan 在此示例中指出的那样。当涉及到集合与对象（图表或图表、范围或范围等）时，您可能会遇到一些障碍，但通过反复试验，您肯定会得到它。
