Alternatives for VBA development in Office

Asked

Viewed 1,419 times

6

At work I have a large code base written in VBA (approximately 13 active projects), both for Access or Excel applications.

I’ve been trying to refactor some things, but several factors are making my job difficult, like:

  • The IDE that accompanies the VBA is very old (based on Visual Studio 6, 1998)
  • VBA itself has many limitations compared to languages like C# and Java
  • VBA projects are aggregated to the file .xls or .mdb, which makes it difficult to use within version control software (Git)
  • We have more than one developer, which makes working on projects simultaneously unfeasible, because we can’t get

I try in view of these points, I am thinking of alternatives to circumvent these problems causing the least impact.

It is not feasible to migrate all projects at once to a new architecture due to the complexity of some.

I have tried to compile Dlls in C# and link them to VBA from COM, but it brought more trouble than it helped.

Question

There is some way to gradually migrate these solutions in VBA, mixing what already exists today with a new architecture ?

Or in the worst case, where I will have to stay with the VBA, how to change the VBA’s default IDE to some more modern that maintains the integration with Office?

c# .net vba office
  • 2

    For this purpose I have used the Microsoft.Office.Interop.Excel, migrating code gradually from VBA for C#. You are aware of this namespace?

  • 1

    @Caffé to perform some Access routines using the Interop. If it were not he would have freaked out to include new features.

  • 1

    Use the Rubberduck to improve the VBA IDE.

2 answers

7


The official solution to do this on . NET is the Visual Studio Tools for Office. I can’t guarantee that it meets all your needs but it seems to solve what you posted.

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

Unfortunately I do not know the difficulty of gradual migration from one technology to another, because I do not know their specific needs. It may be more a matter of planning but there may be situations that make it difficult. Then you probably won’t 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: Netoffice - solution open source with facilities to perform some tasks with the Office API for . NET.

  • 1

    VSTO is an interesting alternative. The only problems I would have initially is that it requires VS Professional at least and until mid-next year at least I’m stuck in Office XP.

4

After a while using these technologies, I ended up facing the situation of having to continue developing with VBA.

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

MZ-Tools: a toolkit for the VBA IDE that includes enhancements such as Find and Replace, Code Templates, documentation and other resources. We can find the list of these resources here. Your 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 number of comings and goings in the language documentation.

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 classes of Zend Framework 1. It is necessary to apply some tricks as you can see in this OS response.

Recommended reading: Organizing Code With Namespaces in VBA

Versioning: VBA codes are aggregated inside the file .xls where it is created, however there is the possibility to export them to text files.

I found a script that exports these files in that gist, but while researching a little more I found this response in the OS, pointing to a project that automatically exports these files while saving the spreadsheet and importing again in a very simple way. This project can be found here.

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

Completion

Even being stuck to an old platform and with a lot of legacy code, it is possible to work and have productivity with VBA. As programmers we sometimes have to give up the best tools and work with what is "offered", whether for political issues or scope restrictions.

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

Browser other questions tagged c# .net vba office

You are not signed in. Login or sign up in order to post.