Sleeper: Declare Global Variable - Private Worksheet

[dj44]

folks,

good day again
I would like to declare my ranges as a global variable to be used within my worksheet code.

Dim oStart: A1
Dim oFinish: A10

private Sub Global_variables_test()
For Each oCell In ActiveSheet.Range("oStart:oFinish")
    oCell.Value = "Test"
Next
End sub

I know this is wrong, how may I declare my global range to be used within my private worksheet code module only

[SamT]

Private GlobalRange As Range

Sub SomeSub()
Set GlobalRange = Some Range
End Sub

[dj44]

Hello Sam,

do you mean like this

 Private GlobalRange As Range
 
Sub SomeSub()
    Set GlobalRange = ActiveSheet.Range("A1:A10")
End Sub
  
Sub Global_variables()
For Each oCell In GlobalRange
    oCell.Value = "Test"
Next
End Sub

[dj44]

I couldnt understand how to do this, so i made a function like this

Private Function MyRange() As Range
Set MyRange = ActiveSheet.Range("A1:A10")
End Function
    
Sub test()
For Each oCell In MyRange.Cells
    oCell.Value = "Hello"
Next oCell
End Sub

A range is an object and its not allowed to declare it as i was trying previously.

I wanted to list all my worksheet ranges at the top in my private worksheet, i suppose this will do for now

[Paul_Hossler]

I'm not sure what a private worksheet is, but something like this maybe

In a standard module - I called it mod_Global in the attachment

Option Explicit
Option Private Module

'only needed in in this module for InitRanges
Const wsDataName As String = "Sheet1"
Const addrNames As String = "A1:A10"
Const addrStreet As String = "B1:B10"
Const addrCity As String = "C1:C10"

'needed as public so other modules can use them
Public rNames As Range, rStreet As Range, rCity As Range
Public wsData As Worksheet

Sub InitRanges()
    Set wsData = Worksheets(wsDataName)
    Set rNames = wsData.Range(addrNames)
    Set rStreet = wsData.Range(addrStreet)
    Set rCity = wsData.Range(addrCity)

End Sub

In another standard module that contains processing macros

Option Explicit

Sub Process()
    rNames.Interior.Color = vbRed
    rStreet.Interior.Color = vbYellow
    rCity.Interior.Color = vbGreen
End Sub

In ThisWorkbook

Option Explicit

Private Sub Workbook_Open()
    InitRanges
End Sub

[dj44]

Thank you for the workbook Paul,

I will set it up and report back later.

The private worksheet - what i meant was when you Right click worksheet > View Code

thats where i normally put my code.

I will list all my constants at the top so all the code within that worksheet can use it

[snb]

You don't need a public variable, you only need a private variable that can be used in all procedures in the same codemodule of 1 sheet.

Better read a primary on VBA before continuing.

[Paul_Hossler]

Thank you for the workbook Paul,


The private worksheet - what i meant was when you Right click worksheet > View Code

thats where i normally put my code.

Well ... each of the 4 types of modules has a specific purpose that it's designed for. You should read this. Chip does a great job explaining

http://www.cpearson.com/excel/codemods.htm


I think it's usually considered better programming practice to reserve Worksheet modules (right-click, View Code) for event handlers and variables specific to that worksheet. I stretch it a bit to include small subs and functions that are only used in that worksheet module


In my example attachment,

1. ThisWorkbook has the FileOpen event handler that calls a sub in a standard module mod_Globals

Option Explicit

Private Sub Workbook_Open()
    InitRanges
End Sub

2. mod_Globals is a standard module

Option Explicit
Option Private Module

'only needed in in this module for InitRanges
Const wsDataName As String = "Sheet1"
Const addrNames As String = "A1:A10"
Const addrStreet As String = "B1:B10"
Const addrCity As String = "C1:C10"

'needed as public so other modules can use them
Public rNames As Range, rStreet As Range, rCity As Range
Public wsData As Worksheet

Sub InitRanges()
    Set wsData = Worksheets(wsDataName)
    Set rNames = wsData.Range(addrNames)
    Set rStreet = wsData.Range(addrStreet)
    Set rCity = wsData.Range(addrCity)

End Sub

a. is declared Option Private Module so that the subs and functions (InitRanges) are not exposed to the user, but the VBA project can use them

b. has 4 Const that are scoped to just this module, but has 4 variables scoped as Public to that other modules and use then

3. mod_Process is a standard module that the user can see and run

Option Explicit

Sub Process()
    rNames.Interior.Color = vbRed
    rStreet.Interior.Color = vbYellow
    rCity.Interior.Color = vbGreen
End Sub

[mikerickson]

Declaring a variable as Public in an object's module (like a worksheet's code module) does not create a global variable. It (essentialy) creates a property of that object. And is addressed as such outside of that module

' in code module for Sheet 1

Public myThing as String

 ' in other module

If Sheet1.myThing = "cat" Then
    MsgBox "meow"
End If

If you want to declare a global variable to be used throughout the Project without qualification (i.e. without "Sheet1."), it has to be declared in a normal module (not sheet, not Class, not ThisWorkbook), before any proceedures

' in normal module

Public myOtherThing as String

Sub First()
'...
End Sub

[dj44]

Thank you Paul and Mike for the step by step instructions

I have copied this over to my note book.

Well i know i have to be very organised but i really do always misplace my functions and modules.

I dont know how they end up in different workbooks when i placed them in 1 module initially.

Sometimes i end up with code - and i have no clue what it belongs to or how it got there

#coders problems

ok let me continue on my reading up on this