The disadvantages of a larger project in VBA

Question

I am new to a project for a company that has a larger enterprise accounting system based on Visual Basic for Applications (VBA) in Ms Access 97. This application is still lively, they make updates, and everything works relatively well. But they want to transfer this application to the highest level, speed up development, make this application more “attractive” for end users, etc. But I'm not sure if it would be a good idea to continue development using this technology (VBA) and therefore I have a few questions. I would be very happy if you could help me.

• Is VBA even for large projects? I think it is more likely for macros and just functions that extend access functionality.
• It will be better to convert the application to .NET winforms / wpf
• Can more VBA developers work?
• What are the worst flaws of VBA code running in a hosting program from a standalone application?
• Is it possible to run unit tests or any similar technology?

Thanks so much for any answer.

EDIT: The access interface now uses SQL Server almost again, but this does not change the main problems.

Answer 1

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

1 Is VBA even for larger projects?

Define "big projects." In general, I would say no, but I mean applications for the entire enterprise. But the question here is not whether VBA can be used, but that Access is an interface suitable for applications of this degree. Access's biggest drawback (besides problems with form deployment) is project management, because source management is not as simple as with other development platforms.

But you really can’t imagine how big the application is. Are they used by hundreds of people? Or does he just have hundreds of subcomponents? That is, is it big in terms of complexity or big in terms of number of users?

I think its more likely for macros and just functions which extends access functionality.

As I said, you are asking the wrong question. The problem is that Access is a suitable interface. If so, then VBA is the language in which you must write it.

Regarding “macros and simple functions,” VBA generates a terminological problem in the “Word” and “Excel” macros, which makes many people think that VBA is a simple language. VBA is actually a SUPERSET VB6 (and not a subset) - VBA has MORE functionality than VB6 itself. When using Access, most of these functions are specific for interacting with databases, and because of this it is very useful (for example, compare a form with Access binding to a VB form).

In general, an access philosophy is one in which you start by creating objects interactively with a dot and click, and then adding code only when the default behavior is not enough. VBA provides great flexibility in that it can interact with any tools that have a COM interface. In principle, it is unlimited in what it can do (although, of course, there is a limit to what is practical).

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

"Better" for what purpose? If you have a working application, you really need to consider the Spolsky / Netscape effect (cited above). Of course, you can improve code management, but at the cost of having to maintain it much more. On the other hand, if you have experience working on this development platform, and no one has VBA, then there will be bias for switching to a development platform that is more suitable for your specific developers.

But this is not a “better” problem that cannot be determined without knowing all the problems and personnel.

3 Can more developers work on a VBA project?

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

But in general, I would say that Access is much better for a single-developer model.

4 What are the worst flaws of VBA code running in a hosting program against a standalone application?

I have no idea what this question means. Many people have the crazy idea that the desktop for the front end of Access is larger than the working set for a VB6 application that does the same thing, but it really isn’t. I would expect that .NET would be roughly the same (if not a bigger workplace, as things always get bigger and bigger as the “progress” goes forward).

The biggest drawback of Access, in my version, is the deployment problem, when any users who require multiple versions of Access can have a lot of problems. SageKey installation scripts are intended to be fixed, but they are a kind of ad hoc - they fix problems when they appear, so if you have a deployment environment with problems that they have not yet encountered and have not been processed, you are not lucky. But they cover most of these circumstances.

5 Can you run unit tests or any similar technology?

I don’t believe that, but then, as an Access developer, I probably don’t have a really good idea what unit testing is. I think most of the Access application code is the user interface, not algorithmic programming, so it will NOT be easy to test. Whether this is a problem or not depends on the nature of the project and the type of testing considered necessary.

For an accounting application, you really need a high level of reliability, and, as I said in a comment elsewhere, if someone gave me an interview for a potential project to create an accounting application, I would recommend that they not build it from scratch, regardless of which one platforms.

Answer 2

This is very subjective. VBA has a bad reputation, but only because it was designed to be a very accessible scripting language for programmers who did not have many skills. The ultimate irony of using such a language for a large project is that it requires a very experienced programmer so that he does not get out of hand. Like someone who understands what you need to do from the very beginning, when the language does not support namespaces, you'd better come up with a very good naming convention from the very beginning.

I can guess why you are asking this question. And the answer is that it is very difficult to throw away years of work just because the language is not attractive. The lodost for this is the Netscape 6.0 version, well covered by Spolsky "T hings, which you should never do .

The best way to find that you are working for an employer who has products from a brown field is to look for another one.

Answer 3

• In theory, no - it was developed mainly for applications with a small number of users. Your example shows that it can be used in larger projects.
• The resulting system (if it is converted correctly) will have the best quality. But, as always in business, you must consider the cost of conversion.
• Yes, they can, but it’s more difficult, since there aren’t many version control systems compatible with access modules (Beyond Compare should work, afair).
• Performance, scalability, etc. I don’t think about the benefits, just VBA is not very good for larger systems (imagine that you would like to have some web reporting or other functions that are very difficult with Access)
• Perhaps, but completely by hand

As with all technologies, almost anything is possible with Access - but you must consider the costs of maintaining such systems. It is not a question if you will convert, but WHEN you will have to.

Answer 4

You probably need to ask the company a few questions first:

• Why was the project not migrated to newer versions? Staying with one version means wanting to kill the project in the long run. If you should not keep up with current versions, there are probably also not enough resources to properly maintain the application. Most likely, this will delete the migration object. Accounted for 10 years of inadequate maintenance is expensive.
• Building a large system in VBA was a bad decision 10 years ago, just as it is now. Why should you believe that management is going to make better decisions now?
• The right way to handle this legacy is to make sure that the accompanying persons have reached retirement age just a few months after they stop using the software. When does management expect this to happen? Getting qualified people will become increasingly difficult, and technology will become obsolete.
• Migrating such systems can be terribly expensive. How prepared are management to invest now that they did not want before? You need to develop a strategy in which you can take very small steps and use already written software. Do not use simple .net / winforms / wpf. Make sure you spend some time looking for at least application level frameworks. A completely different approach is to use tools like MOOSE to reengineer a solution.

Answer 5

Well, it’s normal to find applications in VBA according to their popularity in the late 90s. Thanks to the new MS, Frameworks.Net allows you to convert your code from VBA to VB.Net if you want.

You are right when you say that VB can be used today in macros for Excel, but for a large project, VBA can be limited if you want to interact with web services and all the new features that you can use in .Net.

The big problem is to fix some errors that may appear for the conversion. If your VBA project was designed in modules or in a set of forms, you must do the conversion for each element and make sure that the conversion is done.

With a converted project, many developers can work on the project, not only on VB.net, but also in other languages ​​.Net framework. Currently, web services can be added to the updated project, NHiberante as an API Persistence, NUnit as unit tests (like many other functions).

Here, a link to MSDN information cancels the conversion. http://msdn.microsoft.com/en-us/library/aa192490 (v = office.11) .aspx

Answer 6

• Almost everyone got a VBA horror story where the access application or Excel worked in large-scale environments, but, as ufoq says, at some point you will have to make this switch. Is it VBA that causes the problem, or is the Jet engine working with Access? The access GUI can be used in large applications with an SQL server backend.

2.Most Probably, although it obviously depends on the skill of the developer.

3.This will be a problem since everyone will work on the same file. So yes,> NET development supported by version control will be better for other developers

4 and 5. See ufoq answers

Answer 7

• No. This is Visual Basic for Applications , and it does just that, provides an interface for COM objects and macros.

• I would start moving towards WPF, as that would be more reliable proof of two options, but if your stuck supporting machines are not running XP Sp2 yet, you will need to use WinForms (although I really doubt this scenario). VB.Net can be a good starting point for all the useful dodads in the Microsoft.VisualBasic namespace.

• Yes, start deploying the solution in separate .vba and .mod files and enter the initial control. However, at this point, you should consider porting your developers to VB.Net or C #.

• VBA is pretty much stuck in single-threaded execution. Visual Studio Tools for Office when used in conjunction with VB.Net and C # does not contain functions that are present directly using VBA.

• Sorting. See VBAUnit .

Something to keep in mind is that VB6 hit the end of life in 2008, and VBA is still included for Office 2010, but developers are encouraged to use VSTA. Administrators used batch files and VBA scripts, but Powershell is slowly but surely replacing this type of implementation. Basically, VBA will still work, but there are new (and probably better) ways to do things (which will not soon hit the end of life).