|
|
Department of Technology |
TECH 394 - Tech Computer Applications |
|
Summer 2004 |
|
|
Visual Basic is an object-oriented programming language. A limited (but still very powerful) version is incorporated into Word, Excel, and Access in Visual Basic for Applications (VBA). It’s the engine needed to create macros. In some cases, the application program does an excellent job of writing the VBA code for you. When you create an Access form that searches for data, an underlying piece of VBA code is written that makes the form operate. Most people never even see the code - they don’t need to. In other situations, however, you will need to read and understand the code in order to make it do exactly what you want. I almost always write my Excel macros directly in VBA. This discussion is covers several features and keywords used in VBA. You can jump to the specific section you want by clicking on the keyword in the list below:
When I say that VBA is an "object-oriented language", I mean that it identifies items on the screen as objects, and then takes action to modify those objects as instructed. The objects can be an entire form, a box or line on the form, a worksheet, or a cell in a worksheet. Each object can have related properties. The kinds of properties associated depend on the object identified. A text box on a form will have a property called "text," which represents the text to be printed in the box. An input box also allows the user to show text on the screen, but the text will appear in a property called "contents". A typical line of VBA code might read: ActiveCell.Offset(2,3).Value = "Hello" The object in this line is "ActiveCell". As you might guess, this identifies the cell where the cellpointer rests. If it were in cell B2, you could also identify it as Range("B2"). Then you could get the same results with: Range("B2").Offset(2,3).Value = "Hello" Note, however, if the cellpointer moves to a new location, the results would no longer be the same. In many situations, you want to be able to refer to a string of text or a number by a name that can be called again and again. VBA allows you to store the information in memory in a variable. VBA allows you to identify these variable by any name it doesn't use (naming a variable "Exit" would be a problem, because VBA understands that word to be an instruction to follow). For clarity, I usually start variable names with a lower-case "m", to remind me that these are memory variables - the values in them will go away when the program terminates. If you want to save the value for future reference, you must tell VBA to print it, store it in a cell on the spreadsheet, etc. If you don't, when the macro is complete, the data is lost. A typical piece of VBA code using variables might be:
These instructions would tell VBA to look up the value in cell B6, multiply it times the value stored in a variable named mNumber, and write the result in cell D6. You could have written the number directly in the second line, but if that same number needs to be referred to in several places, you could use the same variable name in any other line of code, and VBA will always substitute the same value. VBA also allows you to run pre-defined processes that modify the data shown. Since the word "procedure" is used in another way, VBA identifies these as "methods". A typical method in spreadsheet use is "Offset", which allows the user to identify a cell at some location relative to the object cell. The period separates parts of the statements. Following the object is the Offset method. .Offset(2,3) It says "from the referenced cell, move down 2 rows, and over 3 columns". The method will only work with spreadsheet cell objects, since it needs to be able to move through columns and rows. You can get fancy with this by replacing the number 2 or 3 with statements that yield a number. For instance, if the number 4 was stored in cell B6, the method statement .Offset(2, Range("B6").Value) would offset from the referenced cell by 2 rows and 4 columns, since it would find the number 4 in cell B6. Following the object or method is the property. In this case, the property identified is "Value". It means the value of whatever is stored in the cell. A similar property is "Contents". You must be careful how you store things in cells, and how you identify them. If you somehow manage to store the number 1 as a text string, VBA will identify it as a character, just like any other letter. Its numeric value will be "0", since you can’t add a letter to a number. We’ll spend some time looking at functions that help us keep these straight. The last part of the statement is the action to be taken. Many statements list a property followed by an equals sign, and then a number or text, in this case, ‘= "Hello"’. These statements tell VBA to replace whatever is currently in that property for that object with whatever follows the equal sign. Therefore ActiveCell.Offset(2,3).Value = "Hello" says to replace whatever value may currently be stored in the cell 2 rows down and 3 columns to the right of the active cell with the text string "Hello". Likewise, ActiveCell.FontName = "MS San Serif" would change the font used for text in the active cell, from whatever was currently used to MS San Serif. Functions are built-in processors that let us modify data in a specific way. The function Ucase( ) transforms whatever is in the parentheses in to upper case. Functions can appear in the middle of a VBA code line. For instance Range("D3").Value = Ucase(Range("B23").Value) determines the value in the active cell, converts it to upper case, and then places it in cell D3. Some users run into difficulties here - they know that Excel has built-in functions as well. If I were to place the function Upper(+B23) in cell D3, it would do the same thing. And, if the value in cell B23 were to change, Excel would update cell D3 automatically. The VBA statement would only replace the value in cell D3 once. When planning a problem solution, you often need to consider whether you will let the application (in this case, Excel) do the work, or whether you will do the work in VBA. Keep in mind, there are some things you can only do in VBA. What spreadsheet function could you use to replace the cell contents with the contents of the active cell at a given point in the process? VBA processes a set of statements that are grouped together in what it calls "subroutines." Each subroutine starts with the word "SUB," followed by a name and then the open and closed parentheses. It ends with the statement "End Sub". VBA will try to process the subroutine from top to bottom, reading each line as a statement to be processed. Any line that starts with a singe quote (‘) will be highlighted in green as a comment - VBA will not attempt to process anything on that line. Also, if any statement is too long to fit conveniently on one line, it can be broken into tow or more lines, with each line ending in an underscore ( _ ). The underscore cannot occur within a text string, because VBA will try to make it a part of the string. A typical VBA program (or subroutine) might look like this:
This subroutine moves the cellpointer to cell B3, select the range from B3 to B18, copies that selection to the clipboard, and pastes it into a range starting at cell E3. This is the same effect as if you had used the mouse to highlight the range, used Ctrl-C to copy, used the mouse to select E3, and pressed Ctrl-V to paste. You can call one macro from inside another, simply by inserting the macro name. For example, if you wrote:
The macro would move the cell pointer to cell B6 and enter the date ("Now" is a VBA function that looks at the computers internal calendar, and return’s the current date). It would then run the macro named "MyMacro", as recorded above. When MyMacro was done running, VBA would return to NewMacro, read the next line - End Sub - and quit. I often start the process I have in mind by recording a macro of the key stokes used to accomplish the task. To do this, look under the Tools menu, select Macro, Record New Macro, choose a name for the new subroutine, and OK to begin recording. When the steps you want are completed, stop the recording. You can view the VBA code by selecting Tools, Macro, Macros, highlight the macro you recorded, and press Edit. You can go directly to the VBA editor by pressing Alt-F11. I then try to determine how to make the code do what I want, rather than what it recorded. For example, if I wanted to know how to get VBA to input data to a cell, I might turn on the recorded, enter a cell, type in a message, press return, and turn off the recorder. The code would look like:
If you were unclear as to what the code meant, you could use VBA’s context-sensitive help. If you recognized that FormulaR1C1 was a property, but weren’t sure how it worked, you’d put the cursor anywhere on that word, and press [F1]. It would bring up the help file for that key word, including examples. If your purpose was to offer the user a chance to enter some data into a cell, rather than plug in a set message each time, you’d replace the text string "My message" with an Index Box function. This function pops up a Windows dialog box with a sentence prompting the user to type in some information. When the user presses [Enter], the function returns whatever the user typed as a string to be used by the program. The standard form is:
The Prompt and Title must be text strings. If you leave off the quotes, VBA will look in memory to find a variable with the name you typed, and failing to find it, will return an error. The Prompt is the message that appears in the box, asking for information. The Title appears in the upper left-hand corner of the box. The Default value will appear highlighted in the input window of the box. If the user begins typing, their input replaces the default value. If the user presses return without typing anything, InputBox will return the default value. You might use this in a box asking for the user’s home state. If 90% of the users were from Minnesota, you would use "Minnesota" as the default value. If the default value is text, it must also be enclosed in quotes, but numbers are entered without them. Both the title and the default values are optional. You may also pop up a dialog box that simply makes a statement. The MsgBox function allows you to stop the macro, deliver a message, and let the user respond by clicking on a button. In its simplest form, it appears as a single line:
This will simply stop operation and pop up a standard dialog box containing 2 things - the message, and a button to click that says "OK". I use this to tell the user when an error has occurred. When they click OK, the program branches to a point where the error can be fixed. In its more complex form, MsgBox is a function that returns a value. The value returned will correspond to whichever button the user clicked. While the actual value is a number, VBA has assigned several of those numbers to variables with predictable names. For example, if the user clicks a button marked Yes, MsgBox will return the number 6. You don’t need to remember that if you recall that VBA assigns that number to a variable called vbYes. Likewise, there are numbers associated with vbNo, vbOK, vbCancel, etc. There are variable corresponding to whichever button the user might click. The standard form for this use of MsgBox is:
As with InputBox, the prompt and title are text strings that tell the user what is required, and identify the box, and again, only the prompt is required. The third item, "buttons", is a number corresponding to which buttons (yes, no, cancel, OK) and what icons you want to appear in the box. The best way to decide what value to enter is to look for help on the MsgBox key word. The help file contains several good examples. The output from this form of MsgBox can be used to control the branching of code. Note that when this form of MsgBox is used, VB interprets it as a function which returns a value. You can't simply set it in a line of code by itself - VB wants to know what you want to do with the value it returns. A typical use is to ask the user a yes/no question, and have the macro respond based on their answer. This requires the use of an If statement:
In the statements above, the MsgBox function pops up a window with the text "Do you want to quit now?", and two buttons, Yes and No. VBA recognizes vbYesNo as a variable corresponding to the correct number to make those buttons appear. If the user presses Yes, the function returns the number 6, while pressing the No button returns the number 7. VBA also stores the number 6 in a variable named vbYes. So the first line in effect says, "If the user presses a button in this box, and the value returned is equal to the value stored in vbYes, the condition is true - process the next line". If they click No, or cancel out of the box, the value will not equal 6, so nothing else happens. VBA code branches when a decision is made. If the user makes one decision, on set of code statements must be read and interpreted. If another choice is made, different instructions must be followed. VBA allows several methods for code branching. When the choice of two (or more) alternatives must be selected, and the results will follow directly from the choice made, the If ..Then structure is typically used. It’s form is:
The non-italicized words are key words to the process, and must appear. The word Else and the statements to follow if the condition is false are optional. Many conditional branches don’t require alternative action. The If ..Then structure is often used where the flowchart branches out and downward. For instance, if the flowchart showed the following:
The condition to be considered is whether the input value is greater than zero. If that condition is true, then the input value is to be increased by 10. If not, the input value is to be increased by 20. If the input value is stored in cell B3, the code to accomplish this would read:
Note that the replacement of the value doesn’t happen until after the entire line is read, so you can add to a value by replacing it with the existing value plus some amount. You can use this type of structure to respond to the user’s selection from a message box. If, for instance, you had run through a set of calculations, and wanted the user to decide whether to print the results, you might include the function:
This would pop up a box that asked "Do you want to print these results?", and offered the user two buttons - one marked Yes, the other No. If the user clicked Yes, the function would return the number 6. If they clicked No, it would return the number 7. But the message box alone is not enough. VBA would be confused by a line of code that simply caused it to tell itself a number. How was VBA supposed to respond? An If ..Then statement tells it what to do:
Note that everything between If and Then is a statement which compares two values. The user supplied the value to the left of the equals sign by clicking on Yes (6) or No (7). The value to the right of the equals sign is a variable that VBA interprets to be the number 6. So, if the user clicks on Yes, VBA interprets this line to read:
The conditional statement here is true - 6 is equal to 6, and so any statements up to the next key word will be processed, and the active sheet will be printed. If the user had clicked No, the statement would be interpreted:
Here the conditional statement is false - 7 is not equal to 6, so VBA would skip to the next key word - End If - without processing any intervening statements. In this case, no alternative action is needed, so the key word Else is left off. Either the user wants the current sheet to be printed, which they indicate by clicking yes, or they don’t. An If ..Then statement can be used to select from more than two choices by adding another key word - ElseIf. The form is:
You can include any number of alternate conditions in consecutive "ElseIf" statements. In many situations, you need the program to repeat the same set of steps until a specific condition is satisfied. The Do command can be stated in four ways, each offering a slightly different meaning.
The first two forms determine if the condition is true or false before processing any statements. In a "Do While" statement, if the condition is not true initially, VB will say, "The condition was never true, therefore the statements should never be processed." Likewise, a "Do Until" statement where the condition is initially true will be passed through without processing any internal statements - the condition must be false before VB will process the statements. The last two forms call for the statements to be processed at least once. They will be processed immediately after VB reads "Do". When VB is done processing the statements, it evaluates the condition and determines if it should loop back and start over. The "Do...Loop While" form will loop back if the condition is true, while the "Do...Loop Until" form stops looping and moves on when the condition becomes true. In flowcharting, the Do loop appears when a conditional response takes you backwards into the chart:
The VB statement that make this process occur are:
Note that this can be a dangerous process. If you've defined a condition that can never be satisfied, the program will loop forever (or until you get fed up and reboot!) In this case, if the conditional said "Is nVal = 0?", you might have a problem. What if nVal = -0.5? The steps that follow only add 1 to the existing values, and nVal will never reach 0 exactly. In some situations, you need to have the program repeat a step for a known number of cycles. The For-Next loop allows you to name a counter variable, define the start and stop values for the counter, and repeat the steps for each counter value in that range. If you had monthly totals listed in twelve adjacent columns, and wanted to total those values into a variable named mTotal, the flow chart might look like this:
Again, you could do this with the If-Then statement and some creativity, but the For-Next loop makes it easy. If the monthly total for January can be found in cell B16, and the totals for the other months are found in the next 11 columns of row 16, the code might look like this:
These instructions tell VBA to make cell B16 the active cell. Then it repeats two instructions 12 times: Add the value in the active cell to the variable mTotal, and move one cell to the right. If it starts in the January column, after 11 moves it ends up in the December column. The 12th step adds the value for December to the total, and moves to the right. The last instruction writes the total in the cell to the right of the monthly totals. The counter value itself can actually be used in the steps to be performed. If the monthly totals are located in B16 - M16, and the yearly total is to be located in N16, the same process could be performed with these instructions:
This set of instructions makes the grand total cell active, and then stepping through the loop, adds the value in a cell "i" columns to the left to the existing value. Either of the above methods will work, but your job as a programmer is to make them work as efficiently as possible. You decide which makes most sense to you, and which you can explain or modify most easily to meet the needs of the people who use your macros.
|
