Is it possible to have modules be external to the actual Excel file and call the functions/subs within them externally?
My thinking is if there are multiple Excel files that use the same module, instead of updating each one of those files separately when I make a change, can I just update the one module stored on a server or something?
Not sure if this would satisfy your needs, but you could create your common module as an "add-in" and so install it so that all files that you open in the same instance of excel would have access to the add-in code. It would not be my recommended way of doing it because I would be worried about suitable testing of all the excel files that use it, when you make a change, plus the added complexity of getting users to install your add-in (this may not be an issue for you). I have a "developersToolkit" module I use across 8 different Workbooks, but I import the module into each workbook so its stand alone and I can also test changes for compatibility with each of the 8 workbooks.
I have doing something like you describe for years. You can move your VBA code to a VB6 ActiveX dll, organize it into classes, and load that dll as a reference from Excel VBA.
This is a good way to reuse non-workbook specific code. For instance, I have code that queries a mainframe. I like to call it from Excel, but the details of the connection and how data is passed are contained in a dll that I can load from Excel, VB6, Word, .NET, wherever. I have a similar dll for reading data from AutoCAD drawings, one for interfacing with a product DB on a MySQL server, etc.
The code that remains in Excel tends to be simple formatting stuff. Say I return a variant array of strings (technically a COM SAFEARRAY) from some library that I wrote. I would then output it into Excel, maybe do a text-to-columns, and have a list of results returned to the user.
You can also pass and return more complex data structures. The beauty of VB6/COM Automation (and I didn't appreciate this until I learned to do it the harder way in VB.NET or C#) is that the data will flow in and out of your components seamlessly and all the necessary interfaces will be created for you.
The main change to your code will be replacing things like ThisWorkbook or ActiveSheet with explicit parameters like (Byval sht as Excel.Worksheet). These will be caught at compile time in VB6 (since it doesn't know what ThisWorkbook is), so you cannot overlook them; you are forced to pass an explicit reference.
I also notice that my code inside the dll becomes more paranoid if it receives a Worksheet or other Excel object as a parameter. In VBA you might have had more assurance that you were passing a good object since it was private to a given workbook. The dll does not know who is calling it, so I view the passed-in object with more suspicion (check if Nothing, sheet name, formatting clues to ensure I am using what I think I am using).
The only downside I see is that you will have to get a copy of Visual Basic 6.0. I bought mine in 1998. It is no longer available from Microsoft, but surely there is someone out there who will sell it to you. The latest service pack is SP6.
You will also have to become familiar with "regsvr32" and "regsvr32 /u" to deal with the "ActiveX can't create component" errors as you open your workbooks on various computers. I just publish my dlls to a mapped network drive and re-register them on the user's computers whenever there is a significant change. Obviously this is a intranet/single company solution. Publishing the updated versions is much more of a pain the farther you are distributed.