Solved: Question About Global Variables

[Opv]

Could someone please explain the advantage of defining a Global Variable in light of the following.

From the examples I've found on the Net, it does appears that about all one can do is define a Global Variable name that can then be used within any Subroutine or Function. However, I don't find where it is possible to actually assign a value to Global Variables outside a Subroutine or Function.

For example:


Global myTest as Integer
myTest = 25

Sub justTesting()
msgbox (myTest)
End Sub

This produces a "Compile Error: Invalid outside procedure" error. If the value of myTest can't be set outside of a Subroutine, I don't understand what is gained by defining Global Variables.

Thanks,

Opv

[mdmackillop]

You can use a Constant if the value is not to be changed by the code


Const Pi = 3.14159

Sub justTesting()
MsgBox "area = " & Pi * InputBox("enter radius") ^ 2
End Sub

[Opv]

You can use a Constant if the value is not to be changed by the code


Const Pi = 3.14159

Sub justTesting()
MsgBox "area = " & Pi * InputBox("enter radius") ^ 2
End Sub



Thanks. I think that is what I think I'm needing. I'm just wanting to be able to call the same variable (having the same value) in multiple Subroutines.

On testing this, it appears to only work if the Constant is defined in the same Worksheet as the Subroutine. I'm not getting it to work by defining the Constant in the "This Workbook" area or in a separate Module. Is there a way to make it work across all Worksheets within a Workbook?

Thanks,

Opv

[mdmackillop]

Put this in a Standard module

Public Const Pi = 3.14159

[Opv]

Put this in a Standard module

Public Const Pi = 3.14159



Thanks. That does the trick. This will save me a lot of time and effort.

Opv

[Opv]

Everything is working fine as it relates to simple numeric constants. I receive an error when I try to type the following in Module1 above all of my Subroutines:


Option Explicit
Public Const myRange as Range = Range("A5").Address


Is there a way to include a Range as a public constant?

Thanks,

Opv

[lucas]

I think you have to not set it as a constant and then set the range within the procedure. I may be wrong:

Option Explicit
Public myRange As Range
Sub a()
Set myRange = Sheets("Sheet1").Range("A5")
MsgBox myRange
End Sub

[Opv]

I think you have to not set it as a constant and then set the range within the procedure. I may be wrong:

Option Explicit
Public myRange As Range
Sub a()
Set myRange = Sheets("Sheet1").Range("A5")
MsgBox myRange
End Sub
I was afraid of that. It kind of defeats the purpose having a constant if that is the case, as I'm trying to avoid having to specify the range in all of my subroutines. It gets cumbersome every time I change the design of a project having to remember all the places to go to change references to the same range. I was hoping for a way around that. Oh well, such is life, I guess.

Thanks

[lucas]

I don't think it's as bad as you think. Once it is set, it can be used by other modules, like this:

Public myRange As Range
Sub a()
Set myRange = Sheets("Sheet1").Range("A5")
Call b
End Sub
Sub b()
MsgBox myRange
End Sub

[Opv]

I don't think it's as bad as you think. Once it is set, it can be used by other modules, like this:

Public myRange As Range
Sub a()
Set myRange = Sheets("Sheet1").Range("A5")
Call b
End Sub
Sub b()
MsgBox myRange
End Sub

Hmmmm. That would work if I needed to run all of the subroutines in a particular order. I may want to run them individually or at different times and in a different order.

I'm getting close to what I'm wanting to do. I'm experimenting with the Constant as a string, i.e., startRange = "A5" and endRange = "C5" and then referencing the range in the subroutines as follows:


Range(startRange, Range(endRange).End(xlDown)).Select


I haven't quite got it working yet but it looks promising.

Thanks,

Opv

[Opv]

I figured out why my most recent example wasn't working. There aren't entries in all of the cells in Column C. I changed my script to:


Option Explicit
Public Const startRange as String = "A5"
Public Const EndRange as String = "C5"


Sub testing()
Range(startRange, Range(startRange).End(xlDown).Offset(0, 2)).Select
End Sub

This selects the entire range as desired. I think I might can work with that (or a variation thereof) in all of my subroutines.

[GTO]

REference:

Public Const myRange As Range = Range("A5").Address

As Steve pointed out, anythiing that you have to 'Set' as an object cannot be declared as a Constant, as you are not assigning a constant value to it, but rather, Setting a reference to a "something" (range, worksheet, so on), ie an Object.

AFAIK, you can use a Constant for any type Value, such as a Date, a String, or, as you pointed out, a number, sucha s a Long or Double.

If you have a number of consistently used ranges, as Steve also showed, you can use a small Sub or Function to Set them, having declared them as Public.

Just as I'd already typed up an admittedly overly simplified example...

Option Explicit

Public rngOne As Range, rngTwo As Range

Function SetRanges() As Boolean
On Error GoTo errHndl
Set rngOne = Sheet1.Range("B10")
Set rngTwo = Sheet3.Range("C23:F345")
SetRanges = True
On Error GoTo 0
Exit Function
errHndl:
SetRanges = False
Set rngOne = Nothing
Set rngTwo = Nothing
End Function

Sub Main()
If Not SetRanges Then
MsgBox "Oh-No!"
Exit Sub
End If

Call MyFunction
Call MyOtherFunction
Call CallAFunction

End Sub

Function MyFunction()
MsgBox rngOne.Address
End Function

Function MyOtherFunction()
MsgBox rngTwo.Address

rngTwo.FormulaArray = "=ROUND(RAND()*100,0)"
rngTwo.Value = rngTwo.Value
End Function

Function CallAFunction()

rngOne.Value = CalledFunction(rngTwo.Value)

End Function

Function CalledFunction(MyArray As Variant) As Long
Dim x As Long, y As Long, i As Long

For x = LBound(MyArray, 1) To UBound(MyArray, 1)
For y = LBound(MyArray, 2) To UBound(MyArray, 2)
i = i + MyArray(x, y)
Next
Next

CalledFunction = i
End Function

Hope that helps,

Mark

[lucas]

I don't think it's doing what you think it is doing.

startRange as string with "" means that it is just the letter A and number 5, not a range.

When I run your code above in 11, I don't get the same results you do. can you attach a workbook that demonstrates it?

for instance, try this:

Public Const startRange As String = "A5"
Public Const EndRange As String = "C5"
Sub testing()
MsgBox startRange
End Sub

[Opv]

[
Hope that helps,

Mark

Thanks, Mark. I think I posted my last reply about the same time as you posted your response. Check out what I've done and let me know what you think.

Thanks again,

Opv

[Opv]

I don't think it's doing what you think it is doing.

startRange as string with "" means that it is just the letter A and number 5, not a range.

When I run your code above in 11, I don't get the same results you do. can you attach a workbook that demonstrates it?



Attached is the sample of what I've done. It does seem to select the entire range as designed. Let me know what you think.

Thanks,

Opv

[GTO]

Afraid I am back off to the sack for a few more hours; just woke up for a bit and poked my nose in, hopefully not intrusively.

Fortunately you are already getting great help, just thought to add my 'two-cents'...

Mark

Edit: FYI, I'm bleary eyed, but I think you missed the attachment... :-)

[Opv]

Afraid I am back off to the sack for a few more hours; just woke up for a bit and poked my nose in, hopefully not intrusively.

Fortunately you are already getting great help, just thought to add my 'two-cents'...

Mark

Edit: FYI, I'm bleary eyed, but I think you missed the attachment... :-)

An offer to help is never intrusive. I appreciate all the help offered.

[lucas]

insert another sheet in your workbook at post #15 and run your code Opv.

[Opv]

insert another sheet in your workbook at post #15 and run your code Opv.

Interesting. It selects blank cells. I modified the code to active the desired sheet and included the Worksheet name in the script and it seems to avoid that problem.

Or, was you trying to get me to see something different?

[lucas]

it selects blank cells not included in the range you tried to define, right?

[Opv]

it selects blank cells not included in the range you tried to define, right?

Yes, it did before I changed the script as described in my previous reply. If the proper worksheet is activated in the script, it avoids this problem.

[SamT]

Here's way to fake Public Object Constants
Put this in a Standard Module, ie Module1.

Public Function MyRange() As Object
Set MyRange = Sheets("Sheet1").Range("A1")
End Function


the run this somewhere else


Sub Test()
Sheets("Sheet1").Range("A2").Value = MyRange
End Sub

[Opv]

Here's way to fake Public Object Constants


Thanks for the suggestion. I think I have things working now. I appreciate everyone's help.

Opv

[Mis_Sailesh]

Please correct me if I am wrong:-

Should we not :think: use class module, if we require to access the same value in different sub routines (places)?

:help

[mdmackillop]

You should also consider dynamic range names to identify your ranges

[Mis_Sailesh]

Yes, I misssed that...

It has been great learning here...

:help

[Bob Phillips]

Here's way to fake Public Object Constants
Put this in a Standard Module, ie Module1.

Public Function MyRange() As Object
Set MyRange = Sheets("Sheet1").Range("A1")
End Function


the run this somewhere else


Sub Test()
Sheets("Sheet1").Range("A2").Value = MyRange
End Sub


Sam,

That is a nice trick, but it doesn't have to be a function, it can be a Public Property, even in a standard module



Public Property Get MyRange() As Range
Set MyRange = Sheets("Sheet1").Range("A1")
End Property