Guide to Using Visual Basic for Applications

Guide to Using Visual Basic for Applications One of the most outstanding qualities of Visual Basic is that its a complete development environment. Whatever you want to do, theres a flavor of Visual Basic to help you do the job! You can use Visual Basic for desktop and mobile and remote development (VB.NET), scripting (VBScript) and Office development (VBA !) If you have tried VBA and you want to know more about how to use it, this is the tutorial for you. (This course is based on the version of VBA found in Microsoft Office 2010.) If youre searching a course in Microsoft Visual Basic .NET, you have also found the right place. Check out: Visual Basic .NET 2010 Express - A From the Ground Up Tutorial VBA as a general concept will be covered in this article. Theres more to VBA than you might think! You can also find articles about the Office VBA sisters: There are basically two way to develop programs that can work with Office applications: VBA and VSTO. In October 2003, Microsoft introduced an enhancement to the professional programming environment Visual Studio .NET called Visual Studio Tools for Office - VSTO. But even though VSTO leverages the considerable advantages of .NET in Office, VBA remains more popular than VSTO. VSTO requires the use of the Professional or higher version of Visual Studio - which will probably cost you more than the Office application youre using - in addition to the Office application. But since VBA is integrated with the host Office application, you dont need anything else. VBA is used primarily by Office experts who want to make their work faster and easier. You seldom see large systems written in VBA. VSTO, on the other hand, is used by professional programmers in larger organizations to create Add-Ins that can be quite sophisticated. An application from a third party, like a paper company for Word or an accounting firm for Excel, is more likely to be written using VSTO. In their documentation, Microsoft notes that there are basically three reasons to use VBA: - Automation Repetition - Computers can do the same thing over and over much better and faster than people can. - Extensions to User Interaction - Do you want to suggest exactly how someone should format a document or save a file? VBA can do that. Do you want to validate what someone enters? VBA can do that too. - Interaction between Office 2010 Applications - A later article in this series is called Word and Excel Working Together. But if this is what you need, you might want to consider Office automation, that is, writing the system using VB.NET and then using the functions from an Office application like Word or Excel as needed. Microsoft has stated that they will continue to support VBA and its featured prominently in the Official Microsoft Office 2010 Development Roadmap. So you have as much assurance as Microsoft ever provides that your investment in VBA development wont be obsolete in the near future. On the other hand, VBA is the last remaining Microsoft product that depends on VB6 COM technology. Its over twenty years old now! In human years, that would make it older than Lestat the Vampire. You might see that as tried, tested and true or you might think of it as ancient, worn-out, and obsolete. I tend to favor the first description but you should be aware of the facts. The first thing to understand is the relationship between VBA and Office applications like Word and Excel. The Office application is a host for VBA. A VBA program can never be executed by itself. VBA is developed in the host environment (using the Developer tab in the Office application ribbon) and it must be executed as part of a Word document, an Excel workbook, an Access database or some other Office host. The way VBA is actually used is different too. In an application like Word, VBA is used primarily as a way to access the objects of the host environment such as accessing the paragraphs in a document with the Words Word.Document.Paragraphs object. Each host environment contributes unique objects that are not available in the other host environments. (For example, there is no workbook in a Word document. A workbook is unique to Excel.) The Visual Basic code is mainly there to make it possible to use objects customized for each Office host application. The fusion between VBA and host specific code can be seen in this code sample (taken from the Microsoft Northwind sample database) where purely VBA code is shown in red and Access specific code is shown in blue. The red code would be the same in Excel or Word but the blue code is unique to this Access application. VBA itself is almost the same as it has been for years. The way it integrates with the host Office application and the Help system has been improved more. The 2010 version of Office doesnt display the Developer tab by default. The Developer tab takes you into the part of the application where you can create VBA programs so the first thing you need to do is change that option. Simply go to the File tab, Options, Customize Ribbon and click the Developer box in Main Tabs. The Help system works much more smoothly than it has in previous versions. You can get help for your VBA questions either offline, from a system that is installed with your Office application, or online from Microsoft over the Internet. The two interfaces are designed to look a lot alike: Click Here to display the illustration If your Internet connection is fast, the online help will give you more and better information. But the locally installed version will probably be faster and in most cases its just as good. You might want to make the local help the default and then use the online help if the local version doesnt give you what you want. The fastest way to go online is to simply select All Word (or All Excel or other app) from the Search dropdown in the help. This will immediately go online and perform the same search, but it wont reset your default selection. Click Here to display the illustration On the next page, we get started with how to actually create a VBA program. When VBA is hosted by an application like Word or Excel, the program lives in the document file thats used by the host. For example, in Word you can save your Word macro (its not a macro, but we wont quibble about terminology right now) either in a Word document or a Word template. Now suppose this VBA program is created in Word (this simple program just changes the font to bold for a selected line) and is saved in a Word document: Sub AboutMacro() AboutMacro Macro Macro recorded 9/9/9999 by Dan Mabbutt Selection.HomeKey Unit:wdStory Selection.EndKey Unit:wdLine, Extend:wdExtend Selection.Font.Bold wdToggle Selection.EndKey Unit:wdStory End Sub In earlier versions of Office, you could clearly see the VBA code stored as part of the document file in the saved Word document by viewing it in Notepad where everything in the Word document can seen. This illustration was produced with a previous version of Word because Microsoft changed the document format in the current version and VBA program code doesnt show up clearly as plain text anymore. But the principal is the same. Similarly, if you create an Excel spreadsheet with an Excel macro it will be saved as part of an .xlsm file. Click Here to display the illustration VBA and Security One of the most effective computer virus tricks in the past was to insert malicious VBA code into an Office document. With previous versions of Office, when a document was opened, the virus could run automatically and create havoc on your machine. This open security hole in Office was starting to impact Office sales and that really got Microsofts attention. With the current 2010 generation of Office, Microsoft has thoroughly plugged the hole. In addition to the improvements mentioned here, Microsoft has enhanced Office security in ways that you might not even notice right down to the hardware level. If youre hesitant to use VBA because you heard that it wasnt safe, be assured that Microsoft has gone the extra mile to change that now. The most important change was to create a special document type just for Office documents that include VBA programs. In Word, for example, MyWordDoc.docx cannot contain a VBA program because Word will not allow programs in a file saved with a docx file extension. The file must be saved as a MyWordDoc.docm for the VBA programming to be allowed as part of the file. In Excel, the file extension is .xlsm. To go along with this enhanced document type, Microsoft created a new security subsystem in Office called the Trust Center. Essentially, you can customize how your Office application treats documents containing VBA code in fine detail. You open the Trust Center from the Developer tab in your Office application by clicking Macro Security in the Code section of the ribbon. Click Here to display the illustration Some of the options are designed to harden your Office applications so malicious code doesnt run and others are designed to make it easier for developers and users to use VBA without having security unnecessarily slowing things down. As you can see, there are a lot of ways that you can customize security and going through all of them is far beyond the scope of this article. Fortunately, Microsofts site has extensive documentation on this topic. And its also fortunate that the default security settings are good for most requirements. Since VBA is tied to the host Office application, you have to run it there. That topic is covered starting on the next page. How Do I Run a VBA Application Thats actually a very good question because its the first one that users of your application will ask. There are basically two ways: - If you decide not to use a control, like a Button, to start the program, then you must use the Macros command on the ribbon (Developer tab, Code group). Select the VBA program and click Run. But this might seem a little too much to some of your users. For example, you might not want the Developer tab to even be available to them. In that case ... - You need to add something that the user can click or type to start the application. In this article, we will look at the Button control. But it could be clicking a shortcut, an icon on a toolbar or even the act of entering data. These are called events and what we will write in this and later articles is event code - program code that is automatically run when some specific event - like clicking a Button control - happens. UserForms, Form Controls and ActiveX Controls If youre not just selecting a macro, the most common way to run a VBA program is to click a button. That button can either be a form control or an ActiveX control. To a degree, your choices depend on the Office application that youre using. Excel provides slightly different choices than Word, for example. But these fundamental types of controls are the same. Because it offers the most flexibility, lets look at what you can do with Excel 2010. A simple text message will be inserted into a cell when several different buttons are clicked just to make the differences more clear. To get started, create a new Excel workbook and select the Developer tab. (If you have another Office application, a variation of these instructions should work.) Click the Insert icon. Well work with the Form Controls button first. Form controls are the older technology. In Excel, they were first introduced in version 5.0 in 1993. Well work with VBA UserForms next but form controls cant be used with them. Theyre also not compatible with the web. Form controls are placed directly on the worksheet surface. On the other hand, some ActiveX controls - which we consider next - cant be used directly on worksheets. Form controls are used with a click and draw technique. Click the Button form control. The mouse pointer will change into a plus sign. Draw the control by dragging over the surface. When you release the mouse button, a dialog pops up asking for a macro command to connect with the button. Click Here to display the illustration Especially when youre creating a control for the first time, you wont have a VBA macro waiting to be connected with the button, so click New and the VBA Editor will open with the suggested name already filled into the shell of an event subroutine. Click Here to display the illustration To complete this very simple application, just type this VBA code statement inside the Sub: Cells(2, 2).Value Form Button Clicked An ActiveX button is almost exactly the same. One difference is that VBA places this code in the worksheet, not in a separate module. Heres the complete event code. Private Sub CommandButton1_Click() Cells(4, 2).Value ActiveX Button Clicked End Sub In addition to placing these controls directly on the worksheet, you can also add a UserForm to the project and place controls on that instead. UserForms - about the same thing as Windows forms - have a lot of advantages in being able to manage your controls more like a normal Visual Basic application. Add a UserForm to the project in the Visual Basic editor. Use the View menu or right-click in Project Explorer. Click Here to display the illustration The default for a UserForm is to not display the form. So to make it visible (and make the controls on it available to the user), execute the Show method of the form. I added another form button just for this. Sub Button2_Click() UserForm1.Show End Sub Youll notice that the UserForm is modal by default. That means that when the form is active, everything else in the application is inactive. (Clicking the other buttons does nothing, for example.) You can change this by changing the ShowModal property of the UserForm to False. But this is getting us deeper into programming. The next articles in this series will explain more about this. The code for the UserForm is placed in the UserForm object. If you select View Code for all of the objects in Project Explorer, you will see that there are three separate Click event subroutines that are contained in three different objects. But theyre all available to the same workbook. Click Here to display the illustration In addition to forcing an event by clicking a button, VBA is also used to react to events in the objects in the hosting application. For example, you can detect when a spreadsheet changes in Excel. Or you can detect when a row is added to a database in Access and write a program to handle that event. In addition to the familiar command buttons, text boxes, and other components that you see in programs all the time, you can add components that are actually part of your Excel spreadsheet in your Word document. Or do the reverse. This goes way beyond copy and paste. For example, you can show an Excel spreadsheet in a Word document. VBA allows you to use the whole power of one Office application in another. For example, Word has relatively simple calculation ability built in. But Excel - well - excels at calculation. Suppose you wanted to use the natural log of the Gamma function (a relatively sophisticated math calculation) in your Word document? With VBA, you can pass values to that function in Excel and get the answer back in your Word document. And you can use much more than the Office applications! If you click the More Controls icon, you can see a considerable list of things that are installed on your computer. Not all of these work out of the box and you should have the documentation for each of them available, but it gives you an idea about how broad the support is for VBA. Of all the features in VBA, there is one that is clearly more useful than any other. Find out what it is on the next page. Ive saved the best for last! Heres a technique that applies across the board to all of the Office applications. Youll find yourself using it a lot so were covering it here in the Introduction. As you start to code more sophisticated VBA programs, one of the first problems you will run into is how to find out about methods and properties of Office objects. If youre writing a VB.NET program, youll often look for code samples and examples to solve this problem. But when you consider all the different hosting applications and the fact that each of them have hundreds of new objects, you usually cant find something that exactly matches what you need to do. The answer is the Record Macro ... The basic idea is to turn on Record Macro, go through the steps of a process that is similar to what you want your program to accomplish, and then check the resulting VBA program for code and ideas. Many people make the mistake of thinking that you have to be able to record exactly the program you need. But its not at all necessary to be that exact. Its usually good enough to record a VBA program that is just close to what you want and then add the code modifications to make it do the job precisely. Its so easy and useful that I will sometimes record a dozen programs with slight differences just to see what the code differences are in the result. Remember to delete all the experiments when youre finished looking at them! As an example, I clicked Record Macro in the Word Visual Basic Editor and typed several lines of text. Heres the result. (Line continuations have been added to make them shorter.) Sub Macro1() Macro1 Macro Selection.TypeText Text: _ These are the times that Selection.TypeText Text: _ try mens souls. The Selection.TypeText Text: _ summer soldier Selection.TypeText Text: _ and the sunshine patriot Selection.TypeText Text: _ will, in these times, shrink from Selection.TypeText Text: _ the service of their country. Selection.MoveUp Unit:wdLine, Count:1 Selection.HomeKey Unit:wdLine Selection.MoveRight Unit:wdCharacter, _ Count:5, Extend:wdExtend Selection.Font.Bold wdToggle End Sub Nobody studies VBA just for itself. You always use it along with a specific Office application. So, to continue learning, there are articles here that demonstrate VBA used with both Word and Excel: - Getting Started Using VBA: The Word Working Partner - Getting Started Using VBA: The Excel Working Partner