Welcome to MrExcel,
My approach to this would be to create a Module that has the information you wish to pass from one workbook to another. Export the module (after any changes that are needed). For this demo, I named the Module “ArraysToSave” and exported it to a file: "c:\temp\arr.bas". You could do this manually, but where’s the fun in that?
To export with VBA:
Code:
ActiveWorkbook.VBProject.VBComponents("ArraysToSave").export "c:\temp\arr.bas"
To import that file to another workbook,:
Code:
ActiveWorkbook.VBProject.VBComponents.Import "c:\temp\arr.bas"
Application.Run "SetupArr"
Where SetupArr is a sub to do any legwork to get the arrays stuffed with constants and is in the file "c:\temp\arr.bas"
My arr.bas is:
Code:
Public arr
Sub SetupArr()
arr = Array("Dog", "Horse", "Rain")
End Sub
Sub exportArr()
ActiveWorkbook.VBProject.VBComponents("ArraysToSave").export "c:\temp\arr.bas"
End Sub
So, after the import and running SetupArr, the array arr is stuffed with "Dog", "Horse", "Rain" and may be used in the “normal” array statements.