Disadvantages of bigger project in VBA

Question

i am new in project for company that have bigger enterprise/accounting system based on Visual Basic for Applications (VBA) in Ms Access 97. This application is still alive, they make updates and everything is working relatively fine. But they want to move this app to highest level, speed up development, make this app more "atractive" for end-users and so on.. But i'm not shure if it's good idea to continue developing using this technology (VBA) and therefore i have a few questions. I would be very glad if you could help me.

1. Is VBA even designed for bigger projects? I think, that its more likely for macros and simply functions which extends Access funcionability.
2. Will be better to convert the application to .NET winforms/wpf
3. Can work more developers on VBA project?
4. What are the worst disadvantages of VBA code running in hosting program against stand-alone application?
5. Is possible to run unit tests or any similar technology?

Thanks a lot for any response

EDIT: Access interface is now using almost agains SQL Server, but that doesn't change main problems.

Answer 1

This is very subjective. VBA has a bad reputation but that's only because it was designed to be a very accessible scripting language for programmers that didn't have a lot of skill. The ultimate irony of using a language like that for a big project is that it takes a very skilled programmer to not let it spiral out of control. Like somebody that understands what you have to do from the get-go when the language doesn't support namespaces, you'd better come up with a very good naming convention from the start.

I can guess why you are asking this question. And the answer is that it is extremely difficult to throw away years of work just because the language isn't appealing. The lodestone for this is Netscape version 6.0, well covered by Spolsky's "Things You Should Never Do".

The best cure for discovering that you work for an employer that has brown-field products is to find another one.

Answer 2

There are no real answers here from anybody who demonstrates extensive experience with Access, so I'll give my answer:

1 Is VBA even designed for bigger projects?

Define "bigger projects." In general, I'd say no, but I'm thinking in terms of Enterprise-wide apps. But the question here is not whether VBA can be used, but whether Access is a front end appropriate for apps of this extent. The biggest drawback with Access (aside form deployment issues) is managing the project because source control is not as easy as with other development platforms.

But you don't really give an idea of how big the app is. Is it used by hundreds of people? Or does it just have hundreds of sub-components? That is, is it big in terms of complexity or big in terms of number of users?

I think, that its more likely for macros and simply functions which extends Access funcionability.

As I said, you're asking the wrong question. The issue is whether Access is an appropriate front end. If it is, then VBA is the language you have to write it in.

As to "macros and simple functions" there's a terminological problem in that Word and Excel "macros" are generated in VBA, which makes many people think that VBA is a simple language. VBA is, in fact a SUPERSET of VB6 (not a subset) -- VBA has MORE functionality built in than VB6 itself. With Access, most of that functionality is specific to interacting with databases, and because of that is very useful (compare an Access bound form to a VB form, for instance).

In general, though, the Access philosophy is one in which you begin with building objects interactively, point-and-click, and then add code only when the default behaviors are not sufficient. VBA allows a great deal of flexibility in that it can interact with any tools that have a COM interface. It's basically unlimited in what it can do (though certainly there is a limit to what is practical).

2 Will be better to convert the application to .NET winforms/wpf

"Better" for what purposes? If you have a working application, you really have to consider the Spolsky/Netscape effect (cited above). Certainly you might get better code management, but at the cost of having to maintain a lot more of it. On the other hand, if you have inhouse expertise on that development platform and none with VBA, then there's going to be a bias towards migrating to the development platform that's more suited to your particular developers.

But it's not really an issue of "better," which can't really be determined without knowing all the issues and personnel involved.

3 Can work more developers on VBA project?

With Visual SourceSafe you can have multiple developers working on the project. Or, you could automate your build using Application.SaveAsText/.LoadFromText and use the CVS of your choice.

But in general, I'd say that Access is much better suited to a single-developer model.

4 What are the worst disadvantages of VBA code running in hosting program against stand-alone application?

I have no idea of what this question means. Many people have the crazy idea that the workset for an Access front end is bigger than the workset for a VB6 app doing the same thing, but it's actually not true. I would expect .NET is about the same (if not an even bigger workset, since things always get bigger and bigger as "progress" marches forward).

The biggest disadvantage of Access, in my opionion, is the problem of deployment, where any users who need multiple versions of Access can have lots of problems. The SageKey install scripts are designed to fix that, but they are kind of ad hoc -- they fix problems when they come up, so if you have a deployment environment with problems they haven't yet encountered and handled, you're out of luck. But they do cover most such circumstnaces.

5 Is possible to run unit tests or any similar technology?

I don't believe so, but then, as an Access developer, I probably don't have any really good idea what unit testing really is. Most of an Access application's code is user interface not algorithmic programming, so it's NOT going to be easily testable, I'd think. Whether this is an issue or not depends on the nature of the project and the type of testing that is believed to be necessary.

For an accounting app, you really do require a high level of reliability, and as I said in a comment elsewhere, if somebody interviewed me for a potential project to build an accounting app, I'd recommend they not build it from scratch, regardless of the chosen platform.