+ Reply to Thread
Results 1 to 11 of 11

Excel VBA address a declared Constant by name in a VBA routine

  1. #1
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,937

    Excel VBA address a declared Constant by name in a VBA routine

    Hi Guys,
    I'm tring to figure out how to simplify my code so I don't have to add too many if statements and make it more 'dynamic'

    Here is what I have:

    Please Login or Register  to view this content.
    Now I have a Userform with four Labels and four Textboxes

    in the UserForm_Initialize

    Please Login or Register  to view this content.
    The thing is it places the text "G_VARIABLE1" in the first TextBox but not the actual value of the constant G_VARIABLE1

    I just can't figure out how to address the actual constant and not just the contants' name

    Thanks for looking
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,835

    Re: Excel VBA address a declared Constant by name in a VBA routine

    Please Login or Register  to view this content.

  3. #3
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,937

    Re: Excel VBA address a declared Constant by name in a VBA routine

    Hi Jindon,
    That's exactly what I thought but ... it doesn't work
    It shows the text 'G_VARIABLE & x' in the textbox but the value that this variable has what I would need to see in case of A_VARIABLE1 = ABC and not G_VARIABLE1
    I tried this
    Please Login or Register  to view this content.
    But that won't work either
    See the result in my Userform ... It shows the names but not the values
    Attached Images Attached Images

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,835

    Re: Excel VBA address a declared Constant by name in a VBA routine

    Put Option Explicit on the top of the module, so that it tells you if the variables are properly declared.

  5. #5
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,937

    Re: Excel VBA address a declared Constant by name in a VBA routine

    @Jindon, please, I appreciate your suggestions but if do not understand my question then ask for a better explanation.
    If you look at my track-record you might maybe think that I have a basic idea.
    This has nothing to do with Option Explicit.
    Thank you anyway for trying

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: Excel VBA address a declared Constant by name in a VBA routine

    Why not use a dictionary to store the values?

    Then you can refer to them by name.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  7. #7
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,937

    Re: Excel VBA address a declared Constant by name in a VBA routine

    All good, but not the solution.
    I've attached a simple file with part of the code there is a textbox in the worksheet explaining it and the userform code to show what I want.
    Thanks for trying, I know it must be simple but I just cannot seem to grasp it.
    Attached Files Attached Files

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: Excel VBA address a declared Constant by name in a VBA routine

    Deleted - inappropriate.
    Last edited by Norie; 11-29-2020 at 06:52 PM. Reason: Inappropriate poat

  9. #9
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,937

    Re: Excel VBA address a declared Constant by name in a VBA routine

    @Norie: No problem, the dictionary idea did pop up (and yes I know how these work ), but I think what I want to do is not really possible.
    I just wanted to do this to make it dynamic, but I think I'll have to conform to the idea that it will have to include a certain number of ifs.
    My idea now is to have a on workbook open event to populate the worksheet I postend in my question file checking if there is a value in it or not.
    Then just use the userform to edit the value and save that to the sheet of a configuration log.
    Thanks anyway all for looking, I'm going to mark this as solved

  10. #10
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    15,027

    Re: Excel VBA address a declared Constant by name in a VBA routine

    On second thought...deleted...Not sure it would work...for required use...
    Last edited by Sintek; 11-30-2020 at 06:14 AM.
    Good Luck...
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the [★ Add Reputation] to left of post window...
    Also....Add a comment if you like!!!!
    And remember...Mark Thread as Solved...
    Excel Forum Rocks!!!

  11. #11
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,937

    Re: Excel VBA address a declared Constant by name in a VBA routine

    @sintek: you never know, VBA in many cases is trial and error and loads of imagination

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Scope of Array declared in Excel Macro-Enabled Add-In
    By eadamy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-11-2017, 07:47 PM
  2. [SOLVED] Excel 2016 Sort routine issue - recording macro has sheet name in sort routine
    By pongmeister in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-05-2016, 02:45 PM
  3. Replies: 0
    Last Post: 11-11-2015, 04:26 AM
  4. Declared variables in excel vba
    By RALIR in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-12-2015, 06:05 PM
  5. [SOLVED] naming a routine and calling it in another routine - not a loop
    By HeyInKy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-16-2015, 11:54 AM
  6. Pass a variable from one sub-routine to another sub-routine
    By gowtham_pec in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 05-01-2013, 07:07 PM
  7. How to pass a worksheet address to a sub routine
    By j_Southern in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-09-2012, 02:58 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1