using vba to copy array constants names and definitions from one workbook to another

[Harvard conservative]

I have two workbooks, one for last year's dataset and another for this years. I use a lot of array constants. I would like to copy the array constant names and definitions from one workbook to the other. How can I do this in VBA?

 

[tlowry]

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:

ActiveWorkbook.VBProject.VBComponents("ArraysToSave").export "c:\temp\arr.bas"

To import that file to another workbook,:

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:

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.