+ Reply to Thread
Results 1 to 13 of 13

Can I Automatically Generate Variable Names?

  1. #1
    Registered User
    Join Date
    08-30-2012
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    83

    Can I Automatically Generate Variable Names?

    Hi There,

    I am trying to set short names for sheets automatically using an array containing the short and full names for each sheet.
    e.g. instead of having to do this for each individual sheet name:
    Please Login or Register  to view this content.
    I want to do something like this:
    Please Login or Register  to view this content.
    I want to then be able to refer to the sheet later on by "BW". Unfortunately the 2nd line of code above would require me to refer to it as "AllTheSheetsArr(1,1)"!
    So really, I am wanting to know if I can name the variables automatically.

    I have looked online for something like this before, and have either not managed to search for the correct phrase or it is not something that is possible. either way, I thought the best way to find out for certain was to post and see if anyone can help!

    Also, you'll see that I add to the array a "1" if the sheet name exists and "-1" if it does not. This is to avoid an error if the sheet name was called and it doesn't exist!

    Thank you for reading!

    Code is below, including the long way that I have to set the sheetnames using Select Case...



    Please Login or Register  to view this content.

  2. #2
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Can I Automatically Generate Variable Names?

    No, you can't, not automatically anyway. There's potentially a bit of a hacky way through automating the VBA IDE if you only need to do it once, but wouldn't really be distributable to others. You could use a collection to shorten things somewhat so you'd end up with something like:

    Please Login or Register  to view this content.
    But I don't think it adds a great deal

  3. #3
    Registered User
    Join Date
    08-30-2012
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    83

    Re: Can I Automatically Generate Variable Names?

    Thanks Kyle,

    Thanks for your reply.
    That's disappointing news!

    I'll leave this thread open for a while in the hope that someone knows something that we don't! :o)

  4. #4
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Can I Automatically Generate Variable Names?

    What's the problem with select case? If you have more variables, you'll have to add them manually whichever way you do it.

    Another approach is to look a what you're actually doing with the variables once you have them.

    Ps I don't really see why:
    Please Login or Register  to view this content.
    Is much more arduous than:

    Please Login or Register  to view this content.

  5. #5
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Can I Automatically Generate Variable Names?

    Post updated

  6. #6
    Registered User
    Join Date
    08-30-2012
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    83

    Re: Can I Automatically Generate Variable Names?

    Hi Kyle,

    Apologies if I seemed dismissive of your suggestion - that was not the intention.

    To explain a little - the example I have given was a generic one. In reality, I have created a program for work which requires users to move between various sheets depending on what they are doing. I am using the sheet names to move around and to extract relevant data depending on the request.

    If I have misunderstood what you have written, then apologies, but it is quicker to write
    Please Login or Register  to view this content.
    than
    Please Login or Register  to view this content.
    For the specific task in which I am aiming this at, it is for resizing the various sheets that users will enter data into dependant on the device they are using e.g. Tablet type 1, Tablet type 2, PC type 1, etc.
    Not all sheets are available in all modes, so rather than have to check each time whether the specific sheet is in existence , I just wanted a quick way to protect against an error (without having to add another On error resume function).

    There is nothing wrong with the Case Select method - I am not dissing it! I agree that I'd have to add them in manually anyway, but being able to write them in on one or a few lines and split them rather than writing say 100 individual cases.

    In short, I was wanting to know if it was possible to automate for extending my (limited) understanding of VBA. Anything that requires a hack so it couldn't be replicated on other machines would not be considered a solution!

    I hope that is a little clearer as to me request and objective from posting? :o)

  7. #7
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Can I Automatically Generate Variable Names?

    It's not a hack, just a little hacky and faffy. Have a look here: http://www.cpearson.com/excel/vbe.aspx. It allows you to write VBA with code, essentially automate the IDE like you would Excel, however there are a few gotchas that limit things a bit that may or may not be relevant:

    1. It's not going to work on a mac
    2. An extra permission needs to be set in Excel which is off by default for security reasons, to use the code users would need to enable this - in some corporate environments it can't be enabled
    3. It's permanent, you're adding code into modules, there's no undo. You have to delete manually (or through more code), this starts to become problematic when you add more variables, for example you need to check to see if they exist (or delete them all and recreate them)
    4. It resets the VBA project

    For the specific task in which I am aiming this at, it is for resizing the various sheets that users will enter data into dependant on the device they are using e.g. Tablet type 1, Tablet type 2, PC type 1, etc.
    Not all sheets are available in all modes, so rather than have to check each time whether the specific sheet is in existence , I just wanted a quick way to protect against an error (without having to add another On error resume function).
    I don't really understand how what you're looking for helps here, are you just wanting to do this due to the speed of typing out bw vs say as("bw")?

  8. #8
    Registered User
    Join Date
    08-30-2012
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    83

    Re: Can I Automatically Generate Variable Names?

    Hi Kyle,

    Thank you - I will look into that at some point when I have the time to invest! Looks a little hair raising, especially if a virus checker can delete the modules dependent on how it sees the vba writing behaviour!

    As I said, the main goal was to find out if it is possible to automatically assign variables without naming them directly e.g. instead of having to type
    Please Login or Register  to view this content.
    they can be set indirectly using a reference from an array. e.g.
    Please Login or Register  to view this content.
    where
    Please Login or Register  to view this content.
    and I can then refer to the sheet using bw rather than array(I,2).

    The bigger picture was expanding my understanding of VBA's capabilities - not just about saving a few keystrokes. There are some amazing solutions that are on this forum - a one/two line piece of code that saves dozens of lines - so I wondered if this would be a similar thing!

    I was/am looking for a solution that does not require tinkering, but for now I shall use the longer method for my current project and look into the method above at some point in the near future!

    Thanks again for your expansion. Sorry if it's not particularly clear what I'm after!

  9. #9
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Can I Automatically Generate Variable Names?

    Thinking a little outside the box, could something like this work for you? - no typos since you're using an enum - you'll get intellisense, the sheetcodes will auto dropdown and you'll get compile time validation.

    You just need to make sure you add a new enum item for each sheetname in the string (and they'e in the same order)

    Please Login or Register  to view this content.
    Last edited by Kyle123; 12-02-2016 at 12:23 PM.

  10. #10
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Can I Automatically Generate Variable Names?

    Or if you wanted a check built in, something like:

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    08-30-2012
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    83

    Re: Can I Automatically Generate Variable Names?

    Hi Kyle,

    Apologies for my ignorance, but I am unfamiliar with enum (having looked up the definition, it is a collection e.g. according to wiki...

    "the four suits in a deck of playing cards may be four enumerators named Club, Diamond, Heart, and Spade, belonging to an enumerated type named suit. If a variable V is declared having suit as its data type, one can assign any of those four values to it."

    I tried adding this into my code to see exactly what it is doing, but there is an error coming up regarding "s" function. Could you please elaborate a little more on what this is doing as I do not use debug.print although I have seen the result in other code that has incorporated it... Once again, probably something I should be using!

    I have assumed that the two code snippets are independent - have I misunderstood and I am actually meant to use them in combination?

    Kind Regards,

    John

  12. #12
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Can I Automatically Generate Variable Names?

    What's the error?

    For the second snippet you will still need the following from the first:
    Please Login or Register  to view this content.
    Just put it in a new workbook rather than your existing one as the sheet names probably won't match.

    Debug print outputs to the "immediate window", you can turn this on in the View menu

    PS Enums in VBA are really just named long data types, so aa = 0, bb = 1, cc = 2 etc..

    The output of the test should for the second snippet should be:
    Please Login or Register  to view this content.
    Last edited by Kyle123; 12-05-2016 at 06:23 AM.

  13. #13
    Registered User
    Join Date
    08-30-2012
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    83

    Re: Can I Automatically Generate Variable Names?

    Thanks Kyle.

    Ok, so I can see that the sheet names are printed to the immediate window as you show. I can extend this by writing the full sheet name into Const sheetNames "|" and the shortened version into PublicEnum .. (Does cd stand for collection then?)
    I can use Debug.Print to print the short and full length sheet names to the immediate window for all of the sheet names as you have demonstrated..

    So how would I then select a sheet e.g.
    Please Login or Register  to view this content.
    ?

    I am not sure that I understand
    HTML Code: 
    Where would the sheetcodes autodropdown from or during?
    I am uncertain what compile time validation means in vba context....

    Kind Regards,

    John

+ 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. Automatically Generate dynamic names in Excel (with or without VBA)
    By tristan_lux in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-20-2015, 12:14 PM
  2. [SOLVED] Generate random names
    By Falconair in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-11-2013, 02:31 PM
  3. [SOLVED] Search a list of names and automatically return any names not already included in table
    By bishbash89 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-21-2013, 09:06 AM
  4. How do you use a name/names to generate a number?
    By Paul Harris115 in forum Excel General
    Replies: 2
    Last Post: 12-03-2012, 06:33 PM
  5. How to declare variable names depending on worksheet names
    By Dynelor in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-28-2008, 08:43 AM
  6. Using variable names to select sheet names
    By mattsgr1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-13-2007, 06:38 AM
  7. VBA automatically updating variable names
    By Mike T in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-31-2006, 01:25 PM

Tags for this Thread

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