Microsoft Visual Basic for Applications (VBA) Course

In this lesson I will discuss what is VBA. VBA stands for Visual Basic for Applications. It is a tool to create executable modules that would run in the background behind the various Microsoft Office products like Word, Excel, PowerPoint, Access, Outlook, et cetera. Now the average user does not know these are happening. They do not get a little message that pops up on the screen that says you are now running a VBA Module. They don't see the Module Window itself come up. All the user would see is the result of that module running, whatever it causes to happen in the product. Most Microsoft Office products have VBA Modules running though they are most commonly used with Excel and with Access. Because those are the two products where people who have been using the product long enough and getting very advanced and experience have a tendency to want to create much more complicated items. And that's really where the power of VBA comes in. Now VBA is a very simple programming language. Now the minute I'd used that term, some people may cringe. They hear programming language and they think oh my god, I need to know a lot about how the computer works and how to talk to it and all the internal mechanisms of the computer. Well you might have to know that for some programming languages but not for VBA. VBA is a very, very simple programming language. It does provide a series of structures which is the way things are organized and Keywords. Certain reserved words in the language that the computer needs to recognize and that's similar to other programming languages. But VBA gives you a lot of latitude and it's really very simple. You do not need to know the inner workings of the computer or how the computer will use the information you're providing it in order to be able to code in VBA. However VBA has a disadvantage versus some other programming languages in that the window, the environment in which you are writing VBA is very simple. For instance if anybody has heard of HTML or done any programming in HTML. HTML by the way is a language that is most frequently used in the world to create web pages. You know that in most programs that allow you to program in HTML and they're multiple applications out there that allow you to do that. Most of the applications have some nice little icons where you can click and have some base structures created on the web page. Then you would customize and kind of tweak to look the way you want. VBA doesn't have anything like that. You are basically creating from scratch. You got a blank window and you're typing sentences. Well not exactly English sentences, you're writing statements in the language that will then be translated by the computer into computer speak, so the computer knows what it is that needs to be done. So you don't have a lot of the shortcuts that you might have in other development environments. So why would you want to use it? Well as I said what VBA allows you to do is to customize your Microsoft Office product to do things that didn't automatically come with the product. See when Microsoft creates these they look at well what would be the needs of the general or average user? And they'll create some procedures that can be activated by the click of an Icon Button up on the top of the screen. And they will provide the means for some more experienced users to be able to do things. But it's all within what they figured would be the steps or the procedures that people would want. But say you want to do something a little bit different? Well it's not there. So you'd have to figure out a way to do it inside the different items that Microsoft has provided you. The advantage with VBA is you can write a module that does that and you are just telling the computer specifically what to do. It doesn't need an icon, it doesn't need a pre-saved set of instructions. You're telling the computer exactly what to do. It also allows you to save repetitive things that you do on a regular basis or repeatedly. Where you're doing the same steps all the time. Now some of you may recognize that what I'm describing is a Macro. Many of the Microsoft Office products allow you to record a Macro which is the same steps, the same series of things you're doing by clicking on different parts of the screen or typing in certain items. And you can record that, save that Macro and then just run that Macro all the time. A Macro is a form of VBA. It uses a VBA Module much like the VBA code that you would write. But VBA even allows you to take those Macros and update them. For instance say the Macro you created is taking one month's worth of tasks that are performed by the people in your company. So you want to compile everything that they've accomplished, summarize it somehow and then create a series of tables or graphs that makes it easy for the high level executives to visualize the work that people have done. And in general that might work great but what about the months where maybe a lot got accomplished and the list is longer than usual? When you record the Macro you maybe selecting only a certain number of lines and now there are more lines. Now your Macro won't work. But you could inside VBA open that Macro and change the collection process to be something that would be based on an item it gets from the user. It's called Parameterization. So you could actually say okay this month there are 326 lines to read and when you recorded it, it was set at say 275. So then you could just say this 326 lines to get, it will get those 326 lines and do all the other steps afterwards that are in the Macro. So you can create Macros in VBA or take pre-recorded Macros and modify them such that they can be used under any circumstances, no matter how big or small something this. And that's one great advantage to VBA even for people who use Macros.  

Download WorkFiles