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?

 

[xenou]

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!

Okay, good. But this is not the only way to pass variables, FYI.

 

[ret44]

Ok - running into more problems.

I'll post the exact thing I'm trying to do as it seems there are some very specific details I'm missing.

Currently, I have this code in the "ThisWorkbook" module of an addin.

Public item_id as integer
Public company_item_id As Integer
Public short_desc   As Integer

Public Sub setVlookupColumns()
'
'Sets vlookup columns for all variables
'


Dim ws As Worksheet
    Set ws = ActiveWorkbook.Sheets("products")

item_id = ws.Range("a1:bz1").Find("item_id", LookIn:=xlValues, lookat:=xlWhole).Column
company_item_id = ws.Range("a1:bz1").Find("company_item_id", LookIn:=xlValues, lookat:=xlWhole).Column
short_desc = ws.Range("a1:bz1").Find("short_desc", LookIn:=xlValues, lookat:=xlWhole).Column

End Sub

When I call this from an activeworkbook, I use:

Application.Run "UtilAddin.xlam!thisworkbook.setVlookupColumns" 'set dynamic vlookup references

Basically - I needed to add the thisworkbook reference to get it to call at all, but then the variable values didn't persist.

Any ideas, or different methods?

 

[xenou]

It looks like your code "dynamically" sets up the columns based on values it finds in the column headers, etc. Why can't you just repeat the same process every time you want to set up the columns (find the key values in the column headers, etc.)? I'm not sure I see why you need to have these values persist. Can you give an example?

By the way, A value saved in a vba variable does not persist between sessions unless it is saved to disk. So you'd have to save the variable value when the workbook closes, and "reload" the variables values when the workbook is opened (e.g., in a file as advised above, or in the workbook itself, such as in a cell or in a named constant).

 

[RoryA]

How are you actually retrieving the variables? They should persist so I'm curious as to what you are actually doing when you use them.

 

[ret44]

Rorya- Are you saying that public variables set in an addin should persist?
I have tried with quite a bit, testing with debug.print, and it doesn't seem to be the case when called from a sub in a different workbook.

In terms of code - I'm trying to be able to call vlookup functions with the variables as the column reference:

ws.range("h2:h" & rc).formular1c1 = "=vlookup(rc[-7], products!r1c1:r12000c60," & company_item_id & ",false)

Xenou - I'm fine with the variable not persisting once the workbook is closed - I'm really just trying to start a sub in a workbook, have the references set by a sub contained in an addin, and then pass those back to original sub, for use in subsequent vlookups.

 

[RoryA]

The variables are now members of the add-in workbook and you have to refer to them accordingly using

Workbooks("addin name.xlam").company_item_id

rather than just

company_item_id

for example.

 

[xenou]

We must have different definitions of "persist". I mean between Excel sessions (open/close Excel). In that case, a public variable is reset to its default value.

 

[xenou]

Personally, I'd still like to see a real-live example of what you are doing. Your addin code seems to just reference key values in the column headers, etc. This could be done with a dynamic range and require no vba at all, neither in the addin nor in the workbook with the formula ... (or, maybe it's possible, anyway).

 

[ret44]

The variables are now members of the add-in workbook and you have to refer to them accordingly using

Workbooks("addin name.xlam").company_item_id

rather than just

company_item_id

for example.

Hi Rorya,

I just tried this in the immediate window, and got an error.

Typed:

debug.Print workbooks("utiladdin.xlam").company_item_id

Error: "Object doesn't support this property or method"

The variable doesn't come up as an autocomplete, so I'm not sure this syntax would work?

 

[xenou]

Is the addin installed? It works for me okay like that.