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?

 

[ret44]

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).

I'm not particularly familiar with dynamic ranges. On a quick review, it doesn't look like it's actually indexing the columns. Edit: Ok, now I just realized what I think the idea of the comment was - if I set each column to a range, I could refer to that range. How well does this work with multiple reports? (I have 10-15 things that use this table). I suppose I could also delete the named ranges at the end of the sub.

VBA is required regardless - most of processes involved are not realistic for manual input.

As far as live code - unnecessary details removed

sub report()

application.run "utiladdin.xlam!importWB", "products" 'Imports table of products
application.run "utiladdin.xlam!setVlookupReference" 'Sets variables of all column heads

'Code that creates a report
'Now I need a formula that vlookups SRP (A field in the product table)

ws.range("c2:c" & rc).FormulaR1C1 = "=vlookup(rc[-2],products!r1c1:r12000c60," & srp & ",false)
'SRP is equal to 7 normally, as SRP is contained in column 7 of the product table - variables was set by the setVlookupReference sub

end sub

Hopefully that helps

 

[xenou]

Your example above doesn't use the syntax that Rory gave you (?). You need to reference the variable in the addin as a workbook property:

sub report()
application.run "utiladdin.xlam!importWB", "products" 'Imports table of products
application.run "utiladdin.xlam!setVlookupReference" 'Sets variables of all column heads

'Code that creates a report
'Now I need a formula that vlookups SRP (A field in the product table)

[COLOR="Red"]srp = workbooks("utiladdin.xlam").SomeVariable
[/COLOR]ws.range("c2:c" & rc).FormulaR1C1 = "=vlookup(rc[-2],products!r1c1:r12000c60," & srp & ",false)
'SRP is equal to 7 normally, as SRP is contained in column 7 of the product table - variables was set by the setVlookupReference sub

end sub

 

[xenou]

For what it's worth:
<a href="http://northernocean.net/etc/mrexcel/20120604_DynamicRanges.zip">dynamic range workbook example</a>
sha256 checksum (zip file): eeea406a3fd32158aba1030d20ec968156e648065a5b57b048828aa7c00d2186

This locates the column without vba or addin. It's sort of begs the question if you are importing data why the columns are moving around (usually a query is more stable than that). I would also prefer index(Match()) over VLookup in cases where I don't know where columns are -- you don't need to worry about the column returning a value being left or right of the column where the lookup takes place. I don't know if this is a matter of relevance or not, not knowing the details of your project.

 

[ret44]

For what it's worth:
<a href="http://northernocean.net/etc/mrexcel/20120604_DynamicRanges.zip">dynamic range workbook example</a>
sha256 checksum (zip file): eeea406a3fd32158aba1030d20ec968156e648065a5b57b048828aa7c00d2186

This locates the column without vba or addin. It's sort of begs the question if you are importing data why the columns are moving around (usually a query is more stable than that). I would also prefer index(Match()) over VLookup in cases where I don't know where columns are -- you don't need to worry about the column returning a value being left or right of the column where the lookup takes place. I don't know if this is a matter of relevance or not, not knowing the details of your project.

Agreed on columns moving around. Unfortunately - as we gain more capabilities with VBA, we're starting to analyze more and more types of data in more rigorous ways. I could always have the new data put on the the end of the report, but the order of the columns becomes less logical when that happens - hence this project.

As far index and match - I use it when I need to for left looking stuff - for looking up to this table I'm going left to right 99% of the time. To me, for code review, vlookup is a little easier to read, though index/match obviously has its advantages for going both directions.

I'll check the out the workbook. Thank you very much for your persistence on these issues.

 

[xenou]

Okay, so for what's it worth another variation on the theme (also no need for public variables, persistent or otherwise):


Function in Addin:

[COLOR="Navy"]Public[/COLOR] [COLOR="Navy"]Function[/COLOR] GetColumnNumber(ByRef oWs [COLOR="Navy"]As[/COLOR] Worksheet, [COLOR="Navy"]ByRef[/COLOR] sVal [COLOR="Navy"]As[/COLOR] String) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR] [COLOR="SeaGreen"]'//Avoid errors if cell not found[/COLOR]
    GetColumnNumber = oWs.Range("a1:bz1").Find(What:=sVal, LookIn:=xlValues, lookat:=xlWhole).Column
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Function[/COLOR]

Calling Code:

[COLOR="Navy"]Sub[/COLOR] foo()
[COLOR="Navy"]    Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] Workbooks("Book1.xlam").getcolumnNumber(ActiveSheet, "item_id")
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Used like this:

    ws.range("c2:c" & rc).FormulaR1C1 = _ 
    "=vlookup(rc[-2],products!r1c1:r12000c60," & Workbooks("utiladdin.xlam").getcolumnNumber(ActiveSheet, "item_id")
 & ",false)

Or:

    srp = workbooks("utiladdin.xlam").getcolumnNumber(ActiveSheet, "item_id")
    ws.range("c2:c" & rc).FormulaR1C1 = _ 
        "=vlookup(rc[-2],products!r1c1:r12000c60," & srp & ",false)

OR using a range value in the function instead of a worksheet (getting down to brass tacks here):

[COLOR="Navy"]Public[/COLOR] [COLOR="Navy"]Function[/COLOR] GetColumnNumber(ByRef rng [COLOR="Navy"]As[/COLOR] Range, [COLOR="Navy"]ByRef[/COLOR] sVal [COLOR="Navy"]As[/COLOR] String) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR] [COLOR="SeaGreen"]'//Avoid errors if cell not found[/COLOR]
    GetColumnNumber = rng.Find(What:=sVal, LookIn:=xlValues, lookat:=xlWhole).Column
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Function[/COLOR]

Called with the range reference (just beware that in this case the columns are relative to the start of the range, which could now be other than column A):

[COLOR="Navy"]Sub[/COLOR] foo()
    [COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] Workbooks("Book1.xlam").getcolumnNumber(ActiveSheet.Range("a1:bz1"), "item_id")
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

 

[ret44]

I really appreciate your help here.

I think I'm going to go with the first method, as that involves the less code per sub.

Important to note that a reference needs to be set up to the addin for workbook that the function will be used in for VBA purposes.

Thanks much!

 

[xenou]

Okay - post back if more troubles. +thanks to rorya for getting this on the right track with the right place to put the code in the addin.