I need your help in deciding the best strategy and technology to choose from for the continuing development of a successful in-house VBA application.
In our company (a web-based distributor of financial information), over the years, we have developed a VBA XLA Add-in application for use only inside our company, which helps in the linking of our database of financial information with Excel worksheets, through the use of several UDF's, supported by ADO/SQL and other business objects logic. This app became a solid, fast, and useful tool for us, somewhat similar to old DDE links, but much more sophisticated and flexible than that.
Recently we replaced the ADO/SQL portion of the system with a SOAP based Web-Service and XMLHTTP MSXML 6.0 technology, literally putting our database into the cloud. The goal was to transform the application into a product which could be used outside of our company. That work is already done, and its behaving quite well, with all functionality, load at startup, user authentication and session control login/logout, seamless integration with EXCEL, user-friendly messages, all done in a single 2.036Kb XLA Add-In file, spanning more than 15.000 lines of good VBA code. However, we feel that it cannot yet be distributed like it is...
We feel that in order to be successfully published as a product to our clients, this application must be transformed into compiled code instead of the interpreted VBA. There are many reasons to justify doing that, including security, speed, robustness, etc.. but we don't need to go into these details right now.
Our fist thought was to use VB6 and Automation Designers to quickly transform our VBA code into a VB6 Automation Add-in. Apart from the fact that VB6 is old technology, it seems that Automation Add-in's are not the ideal solution, for our app requires some interaction with Excel events and the End-User, at least during the "login" and "logout" into the web-service based database (and some other functionality that requires user interaction through forms), BUT is seems that Automation Add-ins are not suitable to anything other than UDF's only. Here we would like to learn about other's experiences with Automation Add-ins and end-user interaction.
So, COM Add-in's are the next option. Again, these allow interaction through menu buttons and commands, but do not allow UDF's in the worksheets. Or so we've read. Also, we have read that COM Add-ins can be made as Automation Add-ins (allowing UDF's after all), but that they will work as two separate entities inside the Excel environment (one COM and one Add-In), one half not communicating with the other. That's not acceptable to us. Again, we'd like to learn more about other's experiences in that regard.
Then there is Managed Code (.NET, Interop, and VSTO) as other viable options. However, while simple to get started with, it is not clear that Interop came to stay, and it is not clear what is the best strategy with managed code. Again, we'd like to learn about other's experiences in this realm.
So, the questions final is: given our requirements (i.e., load at startup, access to data through SOAP based web-service (MSXML 6.0), UDF's functions, login/logout session control, user-friendly error handling, etc.), and the fact that we already have 15.000 lines of good VBA code, which is the best technology for us to continue to develop this Excel component, in order to make it into an easily and safely distributable product? All comments and thoughts in that regard are very welcome.
I would take a look at Excel DNA: https://github.com/Excel-DNA
I had a very similar problem developing a VBA add-in that communicates with an ASP.Net Web API. Couldn't really give all the code out in VBA, so we needed a way of giving out compiled code. Connecting to the web service was freezing up excel making it harder for users working with massive spreadsheets with multiple API calls.
You can develop them in visual studios easily using VB so a lot of the code could just be copied and pasted over then changed slightly to fit the later version of VB you're using.
Add-ins compile to .xll 64-bit and 32-bit versions.
We handle all login and connection to the API in the Ribbon: https://exceldna.codeplex.com/wikipage?title=Ribbon%20Customization&referringTitle=Documentation
And you can use newer VB forms which are more user-friendly.
Connect to other .dll files for sharing logic across multiple add-ins.
The UDF we use which call the API can all run asynchronously freeing up Excels main thread. https://exceldna.codeplex.com/wikipage?title=Asynchronous%20Functions&referringTitle=Documentation
It can run any VBA specific or CAPI code by queuing as a macro.
Pretty much do everything that you could do in VBA plus more.
Govert the Developer https://stackoverflow.com/users/44264/govert is very active on StackOverflow and forums. He's helped us out loads in the development. And Excel DNA is still being updated and worked on.
Let me know if you want some more details.