Results 1 to 7 of 7

Thread: How return built-in constant value from its name
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular pbornemeier's Avatar
    Join Date
    May 2005
    Location
    Virginia Beach, VA USA
    Posts
    3,573
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default How return built-in constant value from its name

    Cell A1 contains the string: rgbAliceBlue

    rgbAliceBlue is a built-in VBA constant with the numeric value of 16775408

    In the VBE Immediate window if I enter ?rgbAliceBlue it shows the value: 16775408

    Application.Evaluate("rgbAliceBlue") returns Error 2029 - Type mismatch

    How can I extract the numeric value of a built-in excel constant using the name of a built-in excel constant?
    Phil

    - Use CODE tags to keep your code formatted. See: BB Tags
    - How to attach Screenshots
    - Try searching for your answer first, see how
    - Test and validate results for all code on a copy of your worksheet!! How do you use the code you just found?
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,886
    Post Thanks / Like
    Mentioned
    403 Post(s)
    Tagged
    42 Thread(s)

    Default Re: How return built-in constant value from its name

    How about
    Code:
    val(rgbAliceBlue)
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular pbornemeier's Avatar
    Join Date
    May 2005
    Location
    Virginia Beach, VA USA
    Posts
    3,573
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How return built-in constant value from its name

    val(rgbAliceBlue) does return the numeric value, but when I retrieve the value of a text string in Range("A1") it is processed as a string and Val("rgbAliceBlue") returns 0

    I want to retrieve the name of a color from A1

    A1 = rgbAliceBlue

    which is a text value

    In code
    x = Range("A1") .Value
    or
    x = Range("A1") .Value2
    or
    x = Range("A1") .Text

    all result in x having the value of "rgbAliceBlue" (a string)

    and Val(X) returns 0, not 16775408 (the value of the constant: rgbAliceBlue)

    Is there a function (or code) that will return the numeric value of a built-in constant using its name as input?
    Phil

    - Use CODE tags to keep your code formatted. See: BB Tags
    - How to attach Screenshots
    - Try searching for your answer first, see how
    - Test and validate results for all code on a copy of your worksheet!! How do you use the code you just found?
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,886
    Post Thanks / Like
    Mentioned
    403 Post(s)
    Tagged
    42 Thread(s)

    Default Re: How return built-in constant value from its name

    I missed the part about it being in A1.
    And have no idea how to convert a string to a constant
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,886
    Post Thanks / Like
    Mentioned
    403 Post(s)
    Tagged
    42 Thread(s)

    Default Re: How return built-in constant value from its name

    From what I can find you either need to set up a select case (or similar) or use this
    https://stackoverflow.com/questions/...nto-a-constant
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,737
    Post Thanks / Like
    Mentioned
    88 Post(s)
    Tagged
    31 Thread(s)

    Default Re: How return built-in constant value from its name

    I am pretty sure you cannot retrieve the value directly as, to my understanding, all constants are replaced by their value at the time the code is compiled (which is before the code itself physically executes), so the running VB code has no knowledge that a constant name was used originally and, hence, does not know that rgbAliceBlue is the name of a constant nor that it has an associated value.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  7. #7
    Board Regular pbornemeier's Avatar
    Join Date
    May 2005
    Location
    Virginia Beach, VA USA
    Posts
    3,573
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How return built-in constant value from its name

    Thank you both for your suggestions and comments.

    I had thought there might be some way to use the functionality of the immediate window since it returns numeric values when evaluating the built-in constants.
    Last edited by pbornemeier; Jun 14th, 2019 at 08:28 AM. Reason: grammar
    Phil

    - Use CODE tags to keep your code formatted. See: BB Tags
    - How to attach Screenshots
    - Try searching for your answer first, see how
    - Test and validate results for all code on a copy of your worksheet!! How do you use the code you just found?
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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