How to Public Set - Public Declared variables?

Question

I have a separate module to declare public variables

Public ws1, ws2 As Worksheet

On each module, on each procedure I must repeat the following :

Set ws1 = Sheets("Sun")
Set ws2 = Sheets("Moon")

...and then - write the code.

So, WHERE and HOW can I Set this variables so they are allready set on each module, on each Sub or Function - as Sheets Sun and Moon ?

brettdj · Accepted Answer · 2012-09-17 08:39:12Z

3

Once you have it set it will remain available to the other modules. So I suggest you set it on Workbook_Open. Also your current code does not dimension ws1 as a worksheet, so I suggest you use

Standard Module

Public ws1 As Worksheet, ws2 As Worksheet

ThisWorkbook Module

Private Sub Workbook_Open()
Set ws1 = ThisWorkbook.Sheets("Sun")
End Sub

edited Sep 17, 2012 at 8:39

answered Sep 17, 2012 at 4:13

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alegro Over a year ago

ThankYou, brettdj. Are you sure about comma separated declaration. I Have string variables declared as (for example) - Poblic a, b, c, d, As String and they all works ?

Doug Glancy Over a year ago

@Alegro. He's sure. cpearson.com/excel/DeclaringVariables.aspx Scroll down to "Pay Attention To Variables Declared With One Dim Statement"

Alegro Over a year ago

Ok, it's clear. Thanks brettdj and Doug. Stack Owerflow is a really nice place.

Collectives™ on Stack Overflow

How to Public Set - Public Declared variables?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related