0

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?

brb
  • 1,123
  • 17
  • 40
  • 1
    You should get in the habit of changing the codename of your worksheets. See [Using sheet codenames in Microsoft Excel VBA](https://www.wiseowl.co.uk/blog/s112/microsoft-excel-vba-worksheet-sheet-name-codename.htm) – TinMan Oct 03 '22 at 08:33
  • Thank you. I will read up on this. Appreciate it. – brb Oct 03 '22 at 08:47

1 Answers1

2

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
   (...)
FunThomas
  • 23,043
  • 3
  • 18
  • 34
  • Perfect. This is what I was looking for, a number of different options. Nice answer. – brb Oct 03 '22 at 08:37
  • Sorry, can I ask a follow-up (but related question so don't think a new SO question required). I got method 1 (Const) working, but I would like to place that in a separate module, GlobalVariables, as I have structured my code to have certain functions in certain modules to keep it clean. If I move Const MySheetName = "Sheet1" to a new module, it doesn;t work. – brb Oct 03 '22 at 08:46
  • Declare it as Global: `Global Const MySheetname = "Sheet1"` – FunThomas Oct 03 '22 at 08:49
  • Perfect, thanks so much. I have learnt a few things from this answer. Appreciated. – brb Oct 03 '22 at 08:57