Can you Const an Object? (Excel VBA)

Topic

New Reply

Hi,
Can I store an object (Range) as a Const? I get a compile error with “Const myR = Range(“B2″).Value”
For instance, I want to store the value in cell B2 and preserve it for use in other subs in the same project!

If it is possible, please show me how to Code it! Tx

Reply | Quote

Replies

Have you tried defining the range as a static variable Rudi? ie

“Static myR as range”

That should keep the variable value for use in all the Subs in your project.

“Const” defines/declares a symbolic constant so in this case won’t work with something that can change, ie cell B2

HTH

Reply | Quote

Hans, tx I understand what you are saying. Your answer does give a method that can work…but is there any other way to do this? I’m would like to know if I can preserve a value that comes from a cell as static without having to populate the variable each time I need it to run.
Hi Lyra, Thanx too. I did not consider Static. This might be the answer. If B2 is 20 and this is assigned to the variable, then whenever I use the variable it must give 20.

I may be way off track, but this code gives an error…it says Object required!!

Sub One()
Static myR As Range
Set myR = Range(“B2”)
End Sub

Sub Two()
Sheets(2).Select
Range(“A1”).Value = myR.Value
End Sub

Reply | Quote

Static only works within one procedure or function – the value of a static variable is preserved between runs of the procedure/function. You’ll have to declare a variable at the top of a module. Once you assign it a value, it will keep that value for the rest of the session (or until you assign a new value)

Reply | Quote

An object is almost by definition not constant.

You can use a module-level or project-level variable:

Private varValue As Variant

will be available to all functions and procedures in the same module, while

Public varValue As Variant

will be available to all functions and procedures in the same project. You must assign the variable a value somewhere in a function or procedure:

varValue = Range(“B2”).Value

Or you can refer to the value of B2 when you need it, of course.

Reply | Quote

Hi Rudi

You certainly can’t define a const in that way, since its value needs to be calculated. I think you’re restricted to a literal string or a number. What might suit is to declare a global variable – valB2 for instance, then assign it a value in an appropriate initialization event – WorkBook_Open, Form_Load etc.

Alan

Reply | Quote

Alan, you may be on to something.

Something like this: Public myVal as variant in the global declaration area
and
Private Sub Workbook_Open()
myVar = Range(“B2”).Value
End Sub

Sub Test()
Sheet(2).select
Range(“A1”).value = myVal
…other code…
End Sub

Sub Test2()
Sheet(4).select
Range(“C4”).value = myVal
…other code…
End Sub

Reply | Quote

That’s the kind of thing I envisaged you needed.
I assume you meant myVar and myVal to be the same thing though, in your sample code.

Alan

Reply | Quote

Yes…that was my error!

Thanks for the info Jan and Stuart. I’ll look into your suggestions when I get chance!
Tx

Reply | Quote

You could also use something like this:

Public Function MyVar() As Variant
MyVar=Range(“B2”).Value
End Function

And then use this in code elsewhere:

Dim Whatever as Variant ‘(or appropriate type)
Whatever=MyVar

This way, if e.g. you decide it is better to store the variable in the registry, all you need to change is the public function.

Reply | Quote

I also think that it is better to hide this in a function call, but I think you would need to define a static variable to hold the value that MyVar will return each time, something like…

Public Function MyVar() As Variant
Static varValue as Variant
if IsEmpty(varValue) then
varValue=Range("B2").Value
End If
MyVar = VarValue
End Function


StuartR

Reply | Quote

I guess you are right when getting the value takes considerable time or when the function is called many times (and certainly when both are true).

Reply | Quote