Reference excel worksheet by name

Question

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

User · Answer

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.

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.

Here is the correct way:

Dim ShTest1 As Worksheet
Set ShTest1 = Sheets("Test1")
ShTest1.Range("A1:C10").Sort Key1:=ShTest1.Range("A2")

User · Answer

There are several options  including using the method you demonstrate  With  and using a variable   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

User · Answer

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

[excel] Reference excel worksheet by name?

Examples related to excel

Examples related to vba

Examples related to worksheet