Alternatives to developing VBA in Office

The official solution for doing this in .NET is Visual Studio Tools for Office . I can not guarantee that it meets all your needs but it seems to sort out what you posted.

Since development is done in Visual Studio as an application it is clear that you will have access to the most modern tools and languages, separation of the document and code, and will avoid the problem of using direct COM (VSTO accesses the COM internally but is transparent).

Unfortunately, I do not know the difficulty of migrating from one technology to another, even though I do not know the specific needs. It may be more of a planning matter, but you may have situations that make it difficult. You probably will not have a better way out.

VBA is to forget even. In addition to this solution I can cite an alternative that I do not know about: NetOffice - open source solution with ease to execute some tasks with the Office API for .NET.

After some time using these technologies, I came across the situation of having to continue developing with VBA.

But I found some alternatives that improved my workflow with this kind of environment:

MZ-Tools : A set of tools for the VBA IDE that include enhancements such as Find and Replace , Code Templates, resources. We can find the list of these resources here . Its download is free and can be downloaded here .

Object Orientation : Yes, VBA supports object orientation, but in a very limited way. There is no namespaces and the language API is not very intuitive, which leads to a series of comings and goings in the documentation language.

The solution in this case was to create a layer between some native functions and the code, creating a sort of separate library in "pseudo-namespaces", similar to the Zend Framework 1 . You must apply some tricks as you can see from this OS response.

Recommended reading: Organizing Code With Namespaces in VBA

Versioning : VBA codes are aggregated within the .xls file it is created in, but it is possible to export them to text files.

I found a script that exports these files to gist , but when searching for a I barely found this answer in SO , pointing to a project that automatically exports these files when saving the worksheet and reimporting very simple. This project can be found here .

From the code files in text, I can now use git normally and the code is versioned in the company repository.

Conclusion

Even though you're stuck on an old platform with lots of legacy code, you can work and have productivity with VBA. As programmers we sometimes have to give up the best tools and work with what is "offered," either for political reasons or for scope constraints.

I recommend that, if possible, study the migration to other technologies and only in extreme cases maintain projects in VBA.