Globally defining a variable when writing VBA for User Form

[Joe4]

I am writing a User Form to updates values on a spreadsheet. As such, there are multiple macros associated with the User Form (initialize, verify, update, etc.).

Basically, I am updating 30 funds on the user form. In multiple macros, I am looping through each fund. I have dimensioned a variable named NumFunds, which I set equal to 30. So all my loops begin...

For i = 1 to NumFunds

Is there a way I can globally define NumFunds? I would like to set it to 30 just once, rather than within each and every macro. That way, if the number of funds ever increases, I will only need to change one variable.

 

[Andrew Poulsom]

Re: Globally defining a variable when writing VBA for User F

Maybe:

Public Const NumFunds As Integer = 30

at the top of a General module, before any procedures.

 

[Mudface]

Re: Globally defining a variable when writing VBA for User F

At the top of any standard module type in-

Public Const NumFunds As Integer = 30

 

[gnaga]

Re: Globally defining a variable when writing VBA for User F

I am not sure. How about declaring this as constant in the general declaration section?

 

[Jaafar Tribak]

Re: Globally defining a variable when writing VBA for User F

Hi,

How about placing this Const NumFunds = 30 in the Declarations area at the top of your UserForm Module .

 

[Joe4]

Re: Globally defining a variable when writing VBA for User F

Thanks. I used Const NumFunds As Integer = 30.

UserForm modules don't like the word Public before the Const NumFunds As Integer = 30, as it gives a Compile Error.

 

[Andrew Poulsom]

Re: Globally defining a variable when writing VBA for User F

Who told you to use Public in your UserForm module?

 

[Joe4]

Re: Globally defining a variable when writing VBA for User F

Maybe I misunderstood your response. My question is regarding VBA for UserForms. Hence, I assumed that I should paste the code at the top of the UserForm module.

If I understand you correctly now, I can paste it at the top of the Standard Module which contains the macro that calls the UserForm. Then will the UserForm module be able to access it?

I am unsure of the relationship/hierarchy between Standard Modules and UserForm modules when trying to declare global variables.

 

[Andrew Poulsom]

Re: Globally defining a variable when writing VBA for User F

If I understand you correctly now, I can paste it at the top of the Standard Module which contains the macro that calls the UserForm. Then will the UserForm module be able to access it?

Yes. Both Mudface and I referred to a General/Standard module.

 

[Joe4]

Re: Globally defining a variable when writing VBA for User F

Sorry, I got it in my mind that I wanted to add it to the UserForm module before I posted the question and didn't get off that train of thought.

I added it to the Standard Module and it works beautifully!