Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Awkward Names Code (VBA/Excel/All)

    When I need to store data between different instances of firing a macro, I like to use a named value.
    I set the value with code similar to:
    <font color=blue> ActiveWorkbook.Names.Add Name:="PrevColl", RefersToR1C1:="5"</font color=blue>

    In order to retreive the numerical value, I use code similar to:
    <font color=blue> Result = Val (Right(Names("PrevColl"), Len(Names("PrevColl")) - 1))</font color=blue>

    I take this awkward approach because, <font color=blue>Names("PrevColl").Value</font color=blue> returns the string " <font color=blue>=5</font color=blue> "

    Is anyone aware of a better approach?

    TIA
    Regards
    Don

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

    Re: Awkward Names Code (VBA/Excel/All)

    If the macro is to be run several times in the same session, you can use a static variable in the macro:

    Sub MyMacro()
    Static lngPrevColl As Long
    ...
    End Sub

    lngPrevColl will keep its value between calls of the macro.

    If you want the value to be preserved when the workbook containing the macro is closed, you can store it in a cell in a hidden worksheet, or in a custom document property. To create one:

    ActiveWorkbook.CustomDocumentProperties.Add _
    Name:="PrevColl", _
    LinkToContent:=False, _
    Type:=msoPropertyTypeNumber, _
    Value:=5

    To retrieve the value, use something like

    MsgBox ActiveWorkbook.CustomDocumentProperties("PrevColl" )

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Awkward Names Code (VBA/Excel/All)

    You should be able to use simply :

    Result = Application.Evaluate("PrevColl"),

    which can also be written as :

    Result = [PrevColl]

    Andrew C

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Awkward Names Code (VBA/Excel/All)

    Thank you Hans
    Of course, you're absolutely correct.
    <hr>If the macro is to be run several times in the same session, you can use a static variable in the macro:

    Sub MyMacro()
    Static lngPrevColl As Long
    ...
    End Sub

    lngPrevColl will keep its value between calls of the macro.<hr>

    This worked exactly as you stated, and as I want. However, There's something that I don't understand. The following quote from the Help file led me to believe that the value would not be retained once the current macro completed its suite of code. What am I misunderstanding?
    <hr>Used at procedure level to declare variables and allocate storage space. Variables declared with the Static statement retain their values as long as the code is running.<hr>
    Regards
    Don

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Awkward Names Code (VBA/Excel/All)

    Thank you for that Andrew.
    That's going to make my life easier.
    Regards
    Don

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

    Re: Awkward Names Code (VBA/Excel/All)

    If you read on, the meaning of Static is explained more clearly (and correctly) further down in the help subject: variables declared as Static keep their values until the module is reset or restarted.

  7. #7
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Awkward Names Code (VBA/Excel/All)

    Thanks again Hans.
    Regards
    Don

  8. #8
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Awkward Names Code (VBA/Excel/All)

    Yes this is a bit of a pain how a single value is returned with three extra characters (one = and two "). I always have a code that strips these characters but then I have to know in advance if this is a named variable that's a single value, not an address. You can also use the shortcut syntax of [named_var] but this ONLY works for named variables that do not refer to an address. So there isn't one solution that works for both cases (unless you use 'on error' to trap the invalid use of the shortcut syntax which tells you the named variable contains an address reference instead).

    In your instance, it's probably best to store the value on a hidden sheet and retrieve it when needed.

    Deb

Posting Permissions

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