Accessing Variables Between Excel Spreadsheets

[LuiCiphier]

Hello all,
I've written a spreadsheet (WBk1) that runs macros within another spreadsheet (WBk2) to create an array of data. How do I access this array from WBk1? I think I'm overlooking something obvious but I always get "Object doesn't support this property or method" errors. The array is declared as public.

 

[xlbo]

Not 100% sure but have you tried defining it as a GLobal variable rather than Public..
HTH
Geoff

 

[LuiCiphier]

I tried declaring the array as global and this had the same effect.

Basically, I want to something like this:
CalcArray() contains the data within the called (hidden WBk1) spreadsheet
NewTotal() Is the array within the current Workbook(WBk2) - the Workbook that is calling the macros within WBk1.

I want to work with this data array - to simplify:

NewTotal(1,X)=Wbk2.CalcArray(1,X)+ (blah blah blah....)
But I don't know how to reference CalcArray properly.

 

[xlbo]

If it's declared as Public then, as far as I can make out from excel help, it should be available to you as a standard variable - ie you shouldn't need to reference it in any special way
HTH
Geoff

 

[LuiCiphier]

Got it!
The other workbook has to be added as a reference (like plugins). And there is no mention of this in Excel help (that I could see!)

Cheers anyway.