VBA : How best for multiple subroutines to refer to the same worksheet

October 3, 2022

I am relatively inexperienced in VBA and don’t know how to best structure my code.

I have a number of subs that all operate on an particular sheet, let’s say Sheet1.

Each of my subs starts by setting the worksheet as follows:

Set ws = Sheets("Sheet1")

but I am conscious that down the track I may change the name of Sheet1 to something else, and this would require me to make changes to all of my subs. Ideally it is better to set this declaration once so that I only have to change it once.

What would be the best way to do this?

>Solution :

Several ways to do so. Btw, when using Sheet, you should always specify the workbook, else VBA will try to access the sheet from the active Workbook, and that is not always the workbook you want to work with. If the sheets and your code are in the same book, best is to refer to ThisWorkbook

o Define a constant at the top of your code. When sheetname is changed, you just need to change the const definition

Const MySheetName = "Sheet1"

Sub MySub1
    Dim ws as Worksheet
    Set ws = ThisWorkbook.Sheets(MySheetName)
    (...)

o Use the Code name. A code name is a technical name of a sheet that can be changed only in the VBA-environment, so usually it never changes. See https://stackoverflow.com/a/41481428/7599798

o If the sheet is always the first (and maybe the only) sheet of a workbook, you can use the index number instead

 Set ws = ThisWorkbook.Sheets(1)

o Use a function that returns the sheet:

Function getMySheet() As Worksheet
    Set getMySheet = ThisWorkbook.Sheets("Sheet1")
End Function

Sub MySub1
    Dim ws as Worksheet
    Set ws = getMySheet
   (...)