Multi-user access to a VBA-application

The short story: What I have is VBA-application, imbedded in an otherwise empty MS Excel (2007) file . What I want is for multiple users to be able to open and use this VBA-application simultaneously from a shared network location.

The longer story: The application is used for financial simulations. Basically, the user selects a whole string of settings and parameters on a input form, the application does some calculations, and then it returns the corresponding financial data on a second user form. It’s made my work quite a bit easier in the past. So much so, in fact, that we’ve decided to make it available to all our employees. But as it turns out, transforming what was originally intended as a simple tool for personal use into a full-fledged, multi-user application, isn’t that simple after all.

Some background information: The project started out as a spreadsheet with some built-in automation through VBA-code. As I continued to expand on the latter, the former became largely irrelevant, until I ended up with a virtually independent VBA-application, which happens to be stuck in an otherwise empty Excel-file.

If I would have the time, I would of course start over, and recreate the tool as a stand-alone executable in VB or Java. But rewriting several thousand lines of source code is simple not an option at this time. (Note that this exorbitant length is mostly due to my lack of programming skill. It could easily be reduced in size by at least 80% by extracting the financial parameters – which are currently hard-coded – and retrieving this information as needed from a separate database or ini-file. I intend to do this, as soon as I figure out how.)

The fact that I’m stuck with MS Excel poses quite a few problems, but the main issue is that Excel files are unsuited for simultaneous access by multiple users, or so I've been told. The simple solution would of course be to send everyone a copy of the file. This is not an option, however, because the tool requires frequent updates in order to ensure the continued accuracy of the calculations, and we can’t afford the risk of people accidently using an outdated version, As such, airtight and fool-proof versioning is absolutely essential.

The current solution We're thinking about creating a script or batch file which would copy the most recent version (and, optionally, the supporting database or ini-file) from the server to the user’s temporary folder, overwriting any previous versions, and then open that copy. A link to this script would be placed in the software section of our intranet. I would work, I guess, but it’s far from elegant, and doesn’t completely eliminate the risk of people using the wrong version (e.g. by accessing an outdated copy though “recently used documents”).

The question: Should any of you have a better idea on how to achieve this, please feel free to enlighten me!

It may be possible to open the Excel file directly on the web page without users downloading and viewing in Excel natively...

Hi,

Multiple users can simultaneously use the same file and will not face any issue untill and unless you are saving any information in the file. You can protect the file and allow everyone to open it in a read only mode. Ensure that the file is not in a shared mode. Hope this helps.

Regards
Roshan

What you need is to save the existing workbook as an addin, this can then be deployed on the server and each user could install & use the addin. You would need to either have a customised ribbon tab or a menu for access to the tools

here's a comprehensive tutorial on creating an addin which can then be deployed on a network or installed on individual machines.

Your last remarks are the best arguments in favour of RoyUk's suggestion.
An AddIn is the obvious method in this case (and intentionally designed for this purpose).