按名称引用Excel工作表? [英] Reference excel worksheet by name?

问题描述

我有一个工作表的名称以字符串形式存储在变量中.如何在此工作表上执行一些操作?

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()

如何完成此操作?

推荐答案

有几种选择，包括使用您演示的方法，使用和使用变量.

There are several options, including using the method you demonstrate, With, and using a variable.

我的偏好是下面的选项4:Dim类型为Worksheet的变量，并存储工作表并在变量上调用方法或将其传递给函数，但是任何选项都可以工作.

My preference is option 4 below: Dim a variable of type Worksheet and store the worksheet and call the methods on the variable or pass it to functions, however any of the options work.

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

这篇关于按名称引用Excel工作表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！