Excel VBA - Use Module in Workbook B to Update Data in Workbook A

Question

I have 10 XLS's, each of which contain a a few hundred lines of VBA that do the same thing. I want to move this common code into an 11th XLS, and have the other 10 call the code in the 11th XLS. The common code must have access to all of the data and worksheets in the calling XLS. This last requirement does not seem to be addressed by other answers to this question on SO. Can I pass the calling XLS's worksheets in as a parameter, or something similar?

Answer 1

Instead of putting this into a secondary XLS file, I'd recommend creating an XLA file (an Excel Add In).

This is the exact scenario for which XLA was intended. XLA will work the way you intend in this case.

For details on creating an XLA, see this page.

Answer 2

Yes, you can pass references to workbooks, worksheets, ranges, etc. as parameters to any function:

Public Sub CallMe(ByVal oWorkbook as Workbook)

    Dim oWorksheet as Worksheet
    Set oWorksheet = oWorkbook.Worksheets(1)

    ' Do stuff...

End Sub

Note that you'll probably have to re-write a lot of the code you copy from the 10 workbooks since they'll be full of implicit references to "this" workbook, such as Worksheets(1) etc. As in the example above, you now need to say oWorkbook.Workbooks(1) instead.