我有一个保存在变量中的工作表名称字符串。如何对此工作表执行某些操作?
我认为我可以这样做:
nameOfWorkSheet = "test"
ActiveWorkbook.Worksheets(nameOfWorkSheet).someOperation()
如何完成这个任务?
有几种选项,包括使用您演示的方法、With语句和使用变量。
我更喜欢下面的第四个选项: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
最好的方法是创建一个类型为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")
Sheet(nameOfWorkSheet).Range("A4")....等等
- Jonson BylvaklovSheets
和Worksheets
将给出相同的结果。 - Jean-François Corbett