CODEWITHSUNDEEP
vbaexcel
Mythranor
Mythranor

Reputation: 1

How to set a global variable range usable in multiple procedures/modules

I have a large series of ranges (>500) that I have defined in my workbook using general declarations: Public r1 As Range, Public r2 As Range etc. Each range always refers to the same cell in the document. ie r1 always refers to Sheet 1, B2.

Many of my events take action on all 500 ranges individually and I am frequently bumping into the 64k module memory limit because I have to declare the range for each variable in each module which I have been doing using: Set r1 = Sheets("Sheet 1").Range"B2"

Is there a way to declare the ranges globally, and then call them freely in all of the various modules that use them? This would free up 30k of memory for each module.

Upvotes: 0

Views: 1721

Answers (2)

Slai
Slai

Reputation: 22866

I am pretty sure there is something terribly wrong with the macro design if it really needs more than 500 Range variables, so I would recommend https://codereview.stackexchange.com/ for that.

The general recommended way is to add them as Named Ranges as those are saved in the workbook the first time they are added, and auto-adjusted if rows/columns are inserted before them or other similar changes. You can add Named Ranges to the currently active workbook like this:

Names.Add "n1", "='Sheet 1'!B2"  ' results in error if already added

Range("n1").Value2 = 42          ' sample use

Another alternative might be global string constants:

Public Const r1$ = "'Sheet 1'!B2"

Sub test()
    Range(r1).Value2 = 42
End Sub

Upvotes: 1

ballsy26
ballsy26

Reputation: 155

It's possible to create a sub that sets all the ranges and then following subs just refer to the variable.

Option Explicit

Public r1 As Range

Sub setup()
Set r1 = Sheet1.Range("a1")
End Sub

Sub test()
r1.Value = 1
End Sub

Sub test2()
r1.Value = 2
End Sub

Upvotes: 1

Related Questions