Variable Scope - Passing values from an addin possible?

[ret44]

Hi Guys,

I have a question about variable scope.

Basically, I have a number of variables that need to be set dynamically. Many reports will use these variables. Variables may need to be added/updated in the future, so I'd like to do this in such a way that I can update 1 central file (an already existing addin)

All the variables are currently public, but do not persist after the macro is run. Is there a way for the variables to persist, but still being set inside a macro in an addin?

 

[Leith Ross]

Hello ret44,

This is tough question to answer without knowing more details about code. Two methods come to mind. You could create one or more Class objects to handle these "dynamic" variables or store them outside of the macro. The variables could reside on a worksheet (hidden or not) or be saved to a file.

 

[ret44]

Hi Keith,

Thanks for the response. I could definitely store the variables in a worksheet - however, I'm trying to only have 1 place to update things when changes are made. Basically, this is a vlookup reference - the report table that I use could have values added (that I need) at any point, and if 20 reports need that new value, I'd have to update that variable in 20 places - so I'm trying to set the variables without any involvement from the output reports.

I'll look into the class objects - not familiar with them, but hopefully that's the the way to go.

Thanks!

 

[ret44]

My apologies - totally misread your name.

 

[xenou]

Typically you would use the addin itself to store the data that needs to be saved persistently between sessions (i.e., in a worksheet in the addin).

 

[RoryA]

Typically I would store data in a separate file, not in the addin. I don't generally like addins needing to be saved (especially as if you have it on a network it's much better to have it read only.)

 

[ret44]

So I gather there's no way to pass a variable set in an addin to the sub that called the variable setting sub in the first place?

Thinking about it - I am technically ok with storing the values in a sheet, because I can control the order, and it doesn't have to be the same as the actual report.

It would be nice however, if I didn't have to go with that solution, as being able to pass the variables is more efficient, and less constrained.

 

[xenou]

I'm not sure what you mean. Short answer is yes, variables can be passed from one sub to another. What exactly is the problem?

 

[RoryA]

If you want to store a variable in an addin you can add a property (or just a public variable) to its ThisWorkbook module and then reference it from any other project.

 

[ret44]

Thank you very much for the help everyone - I didn't realize that setting the variable had to be done in the Thisworkbook module to be accessible by other subs.

Thanks much!