View Full Version : Solved: Question About Global Variables
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
03-27-2010, 07:17 AM
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
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
03-27-2010, 07:59 AM
Put this in a Standard module
Public Const Pi = 3.14159
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
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
04-01-2010, 02:14 PM
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 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
04-01-2010, 02:41 PM
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
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
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.
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
04-01-2010, 03:05 PM
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
[
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
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
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... :-)
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
04-01-2010, 03:27 PM
insert another sheet in your workbook at post #15 and run your code 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
04-01-2010, 03:36 PM
it selects blank cells not included in the range you tried to define, right?
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.
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
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
04-02-2010, 01:25 AM
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
04-02-2010, 06:02 AM
You should also consider dynamic range names to identify your ranges
Mis_Sailesh
04-02-2010, 09:09 PM
Yes, I misssed that...
It has been great learning here...
:help
Bob Phillips
04-03-2010, 02:22 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.