Keep setting of variables for all macros of a procedure

[BBHRVS]

Hi;

I am using in all the macros of a workbook abbreviations for the worksheets.
Since then, at the beginning of each macro, there was something like :
Set wsh1=Thisworkbook.Worksheets("name1")
Set wsh2=Thisworkbook.Worksheets("name2")
...
I want now to define only once the abbreviations of the worksheets and be able to use them in any macro of the procedure.
I tried with a public sub, but this does not work properly.
What is the correct way to do it ?

 

[erik.van.geit]

Hi, BBHRVS,

see if this works for you
put this in the workbookmodule

Private Sub Workbook_Open()
Set wsh1 = ThisWorkbook.Worksheets("name1")
Set wsh2 = ThisWorkbook.Worksheets("name2")
End Sub

and this in a "normal" module

Public wsh1 As Worksheet
Public wsh2 As Worksheet

Sub test()
wsh1.Select
MsgBox wsh2.Name, 64, "title"
End Sub

kind regards,
Erik

 

[BBHRVS]

Dear Erik;

This is exactly what I did, but the issue is that during macro testing, any interruption, due to bug or voluntary stop, cancels everything which is stored in the variables. Then it begins quite tiring, as you have to reset the wshts ...