VBA advantages
You can automate almost anything you do in Excel. To do so, you write instructions that Excel carries out. Automating a task by using VBA offers several advantages:-
Excel always executes the task in exactly the same way. (In most cases, consistency is a good thing.)
-
Excel performs the task much faster than you can do it manually (unless, of course, you’re Clark Kent).
-
If you’re a good macro programmer, Excel always performs the task without errors (which probably can’t be said about you or me, no matter how careful we are).
-
If you set things up properly, someone who doesn’t know anything about Excel can perform the task by running the macro.
-
You can do things in Excel that are otherwise impossible — which can make you a very popular person around the office.
-
For long, time-consuming tasks, you don’t have to sit in front of your computer and get bored. Excel does the work while you hang out at the water cooler.
VBA disadvantages
It’s only fair that equal time is given to listing the disadvantages (or potential disadvantages) of VBA:-
You have to know how to write programs in VBA. Fortunately, it’s not as difficult as you might expect.
-
Other people who need to use your VBA programs must have their own copies of Excel. It would be nice if you could press a button that transforms your Excel/VBA application into a stand-alone program, but that isn’t possible (and probably never will be).
-
Sometimes, things go wrong. In other words, you can’t blindly assume that your VBA program will always work correctly under all circumstances. Welcome to the world of debugging and, if others are using your macros, technical support.
-
VBA is a moving target. As you know, Microsoft is continually upgrading Excel. Even though Microsoft puts great effort into compatibility between versions, you may discover that the VBA code you’ve written doesn’t work properly with older versions or with a future version of Excel.