[RESOLVED] VBA Questions about Variables

[HowdyTeddy]

1. I wanted to know if a code can be written to make more variables if needed? I am asking the user on a userform for a list if information. I would like to set each item that is listed on the form to be set to a different variable, and I would like to let the user have as many items they list as they like. So, I would like the code to create the number of variables to match the list of items provided by the user. For example, if there are 2 items, the program could create MyVar1 and MyVar2. Most of the time it will just be 8 variables; however, I could see in the future there being a need for possibly 30 or more variables. I would like to keep it flexible.

As a last resort, I could list the items on a hidden page and refer to that page as needed; however, I was trying to avoid that.

2. I was also wondering how the computer allocates space for variables. Specifically, if you have a private sub within your program that declares variables, but when the person used the program, they never used that portion of the code, would the computer allocate memory for those unused variables? If not, I could create private subs that are rarely used that house more variables.

3. I was also wondering if textboxes, command buttons, labels, etc. are all considered variables. Should I declare each of them? If so, what do I declare them as? A string? An object?

4. I have the user fill in data in a bunch of textboxes on a userform. With each answer, I am setting a new variable equal to the textbox that I then use in the program. Should I just be using the textbox as a variable and not have new variables for that information?


Thanks,

Teddy

[westconn1]

3 textboxes, labels etc are not variables as such and do not need to be declared, but the contents of them can be accessed from your program so do not need variables to keep the same information, unless you want to keep that information in memory while adding new information, but i would assume that you are going to save the information from the textboxes to some document, spreadsheet or database, so need to keep in variables

the program could create MyVar1 and MyVar2

while i am bad at using names like mystr etc, it is much better to give all variables meaningful names, including an indication of type
sname, saddr for strings
icnt for integer countert
you can also use arrays for bunches (groups or lists) of data

[Hack]

And if the variables you are using will be used throughout the form, don't declare them in events or sub or functions. Use the declaration section of the UserForm.

[HowdyTeddy]

Thanks guys. I appreciate the advice on variable naming. I feel like I mostly understand the answers to Questions 3 and 4 now but not 1 and 2. Also, I was not quite sure what the "declaration section of the Userform" is. Is that the "Private Sub UserForm_Initialize()"?

-Teddy

[HowdyTeddy]

Also how do you use arrays for groups or lists of data?

-Teddy

[si_the_geek]

For 1, the answer is arrays. There is a good explanation of them in the article What are arrays and how do I use them? from our Classic VB FAQs (in the FAQ forum, which is shown near the top of our home page). While the article is for VB6, 'Data Types/Variables' is one of the areas where VBA is exactly the same.


For 2, variables declared in a sub only exist (and use memory) while that sub is running - however, I am concerned about what you are intending to do.

Also, I was not quite sure what the "declaration section of the Userform" is. Is that the "Private Sub UserForm_Initialize()"?

No, it is the section above all of the subs/functions - in the dropdown lists at the top of the code window, select "General" and then "Declarations".

I think you may find another of the VB6 FAQ articles useful: What is the difference between Dim/Private/Public/Global/Static/Const?

[HowdyTeddy]

Thank you very much. I learned a great deal reading those articles. I have been experimenting with my new found knowledge. However, before I learned of the general declaration, the variables cleared after the program was done. Now using the general declaration, I am finally able to use variable between each of sub in the program. However, a new dilemma has arose. Now the variables keep their value, even after the program finished. I setup a tiny program that just added 1 to a variable. Each time I ran it, the number kept increasing. Is there a way to declare a variable that is used and shared by all the subs in that program but does not retain its value from the last time the program was run?

-Teddy

[westconn1]

public /global variables in vba retain there values for the duration of the instance of the application, so when you close word, excel or whatever program, your variables are cleared

it is good practise to declare variables in least scope for which they are to be used, so variables only used within a proceedure should be declared within that proceedure, also you can at any time reset your variable back to the default or uninitialised state, so you could do that in form load /initialise event, if you are using a form, variables declared in the general section of a form are only available in that form and should be cleared when the form is closed, that is the scope of a variable declared in a form, is not the same as the scope of a variable declared in a module

[HowdyTeddy]

Thanks once again. You have answered many of my questions and pointed me in directions that I did not know existed. The arrays sound very useful. In the example given, they used Emails(8) as an array. Could it be Emails(x), where x is itself a variable?

Also I was wondering about the following situation: a macro has many subs in it, but the selections the user made on the userform dictated only a few of the subs being used. In this run of the macro, would the variables declared in the subs not used not have space allocated by the computer?

Thanks for your continued advice.

-Teddy

[westconn1]

dim emails() as string   ' declare none fixed array
redim emails(0)     ' give array starting dimention
for i = 1 to somenumber
  if somecondition then
     emails(ubound(emails)) = "mytest " & i      ' put value in last element
     redim preserve emails(ubound(emails) +1)   ' add extra element, keeping existing values
  end if
next

i would not really write like this, but it is ok for an example and would work, in this code the last element would always be empty in this loop, to determine the size (number of elements) of the array
msgbox ubound(emails), remember in most cases starts at zero, so
emails(7) is the same as emails(0 to 7), which would mean 8 elements

[HowdyTeddy]

Thanks once again, Pete.

-Teddy