VBA Tips and Why Dalton Should be a Beta Tester

Now that I’m sure you’re all fired up about learning VBA the next question has to be….

How do I learn VBA?

I’m glad you asked. Have you taken class “M2M Foundations and VBAe” or watched the pre-recorded version? If not, stop reading now and go do that first. After you’ve done that the best way to learn to code is to code, so start a simple customization project. As your skills increase, take on projects that get progressively more complex.

The Process

If your project is something that will actually be used in production, the first thing you need to do is thoroughly interview the people requesting the customization. Ask probing questions about the project and be as specific as possible. The more thorough you are during this phase, the fewer revisions you’ll have to do later.

Next, break your customization up into small, basic steps and build on little successes. Do not begin a VBA project for Made2Manage by writing 20-50 lines of code. Write just enough code to prove the basic concept. Test it to make sure it works. After that piece works, add the next layer of complexity on top of it. If you code this way, you will only need to debug one concept or procedure at a time. Continue this basic process until your code does what you think it should.

At this point, I will often show the prototype to one of the users to make sure I understood their needs and that this basic design will fulfill them. Make any necessary changes to the basic product before continuing.


Road House

“Never underestimate your opponent. Expect the unexpected.” – Road House

I cannot emphasize this enough, test everything you write. Test it thoroughly yourself, have others test it in a practice environment, and monitor it for quite some time in production. I subscribe to the philosophy that programmers are typically the worst software testers. Programmers do not think like regular users and are likely to miss a whole slew of “creative” ways users will respond to our programs. Therefore, when you are testing your own work, you have to be paranoid and envision your user as someone who deliberately tries to break your program. For example, lets say you create a text box which the user will use to enter only numeric data. You must address the possibility that the user will leave it blank or accidentally enter letters and incorrect characters in it.

That’s my basic process for creating VBA customizations. In a previous article, I listed some of the advantages of using VBA, so lets discuss some of the disadvantages.

Disadvantages of using VBA

First, you can’t accomplish everything in the VBA environment. Made2Manage’s own development team has indicated this to me several times over the years. Some of the customizations they do require modifications to the source code, which you and I can’t access.

Second, implementation of the VBA can be very inconsistent. Those who are already accomplished programmers with Visual Basic with other programs will realize that some Visual Basic commands simply will not work in the M2M VBA environment. Also, some of the events like BEFORESAVE behave inconsistently on different Made2Manage forms. I’ll point out specific examples in a future post.

Third, VBA must be installed on every workstation that needs to run the customizations. While the new VBA code can be retrieved from the server each time a user logs in, to install the product you must literally touch each machine. In addition, you may need to update the VBA program file each time you migrate your M2M Version. This can be very time consuming if you support 50 or more users.

Fourth, VBA for Made2Manage tends to hide mistakes in your code. The program will attempt to go past code with syntax and other errors in an attempt to hide your mistakes from the user. This may sound like a great feature, but let me assure you that it is in fact one of VBA’s biggest problems. Since VBA masks many errors, they can be exceedingly difficult to find and correct.

Finally, you have to be very careful when performing customizations as you could possibly damage your data. Obviously, Made2Manage does not support customizations that they haven’t written, and it will cost you dearly if you need to hire them to fix your database. Until you are very confident of your skills in VBA and SQL, do not write directly to your database. Restrict yourself to screen and pre-save data validation and leave database access to Made2Manage itself.

In my next article I will demonstrate a relatively simple VBA customization and how I step through it.

1 comment to VBA Tips and Why Dalton Should be a Beta Tester

Leave a Reply




You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>