Results 1 to 12 of 12
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can you Const an Object? (Excel VBA)

    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
    Regards,
    Rudi

  2. #2
    3 Star Lounger
    Join Date
    Nov 2004
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can you Const an Object? (Excel VBA)

    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
    <font color=448800><font face="Comic Sans MS"><big>Lyra J </font color=448800></font face=comic></big>
    <img src=/S/flags/UK.gif border=0 alt=UK width=30 height=18> Ducking the arrows in Robin Hood country <IMG SRC=http://www.wopr.com/w3tuserpics/Lyra_J_sig.gif ALT="No, Admins, no! I'm sorry, okay!" title="No, Admins, no! I'm sorry, okay!">

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Can you Const an Object? (Excel VBA)

    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.

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can you Const an Object? (Excel VBA)

    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

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can you Const an Object? (Excel VBA)

    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
    Regards,
    Rudi

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Can you Const an Object? (Excel VBA)

    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)

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can you Const an Object? (Excel VBA)

    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
    Regards,
    Rudi

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can you Const an Object? (Excel VBA)

    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

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can you Const an Object? (Excel VBA)

    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Can you Const an Object? (Excel VBA)

    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...
    <code>
    Public Function MyVar() As Variant
    Static varValue as Variant
    if IsEmpty(varValue) then
    varValue=Range("B2").Value
    End If
    MyVar = VarValue
    End Function
    </code>

    StuartR

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can you Const an Object? (Excel VBA)

    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).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #12
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can you Const an Object? (Excel VBA)

    Yes...that was my error!

    Thanks for the info Jan and Stuart. I'll look into your suggestions when I get chance!
    Tx
    Regards,
    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •