按名称引用excel工作表?
问题描述:
我有一个工作表的名称作为字符串存储在变量中。我如何在这个工作表上执行一些操作?按名称引用excel工作表?
我以为我会做这样的事情:
nameOfWorkSheet = "test"
ActiveWorkbook.Worksheets(nameOfWorkSheet).someOperation()
我要如何做?
答
有几种选择,包括使用您演示的方法,使用和使用变量。
我的首选项是下面的选项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
答
为了扩大对瑞安的答案,当你声明变量(使用DIM),你可以通过使用VBE的预测文本功能,如下图中作弊一点点。
如果它显示在该列表中,则可以将该类型的对象分配给变量。因此,不仅仅是一张工作表,正如Ryan指出的那样,而且还包括一张图表,范围,工作簿,系列等等。
您将该变量设置为您想要操作的对象,然后您可以调用方法,将它传递给函数等,就像Ryan为此示例指出的一样。当涉及到集合与对象(图表或图表,范围或范围等)时,您可能遇到一些困难,但试验和错误您肯定会得到它。
答
最好的方法是创建一个类型为Worksheet
的变量,分配工作表并在每次VBA隐式使用ActiveSheet
时使用它。
这将帮助您避免在程序增长时最终显示的错误。
例如像Range("A1:C10").Sort Key1:=Range("A2")
这样的东西当宏只在一张纸上工作时很好。但是,您最终将扩展您的宏以使用多张工作表,发现这不起作用,请将其调整为ShTest1.Range("A1:C10").Sort Key1:=Range("A2")
...并发现它仍然无效。
这是正确的做法:
Dim ShTest1 As Worksheet
Set ShTest1 = Sheets("Test1")
ShTest1.Range("A1:C10").Sort Key1:=ShTest1.Range("A2")
嗯,我想在我的挫折我问这个问题过早。我找到了一个类似于这样的解决方案 工作表(nameOfWorkSheet).Range(“A4”)....等 – 2012-03-09 04:01:01
这应该是工作表(nameOfWorkSheet).Range(“A4”)。您错过了“表格”中的“S” – 2012-03-09 08:06:47
您发布的代码工作正常......发布前您是否尝试过? 'Sheets'和'Worksheets'会给你相同的结果。 – 2012-03-09 08:35:48