VBA Excel version compatibility
When Microsoft introduced Excel 97, some radical changes to both the language and the developers interface were made. Excel 97 was the first time that Active X components could be embedded with worksheets and user forms. Compatibility with previous versions of VBA is far less likely than with versions released after Excel 97. At the time of writing this book, these include Excel 2000 and Excel XP. The VBA macros written in this book should work with versions of Excel 97 onwards. However,...
Other Debug facilities
The Debug.Print statement displays the values of variables, or cells, as they change during execution. The Immediate window is used to display output from Debug.Print. To see how this w orks, suppose w e w anted to display the contents of each cell during the running of the validateWeekSales macro. We could do this by adding these lines during the loop execution Debug.Print The Cell Value is , cell.Value Figure 8.9 shows how this statement has b een added to the validate WeekSales procedure....
Passing a parameter by value
When a parameter is passed by value using the ByVal keyword, the called parameter obtains its own separate copy of the variable. The value of the variable can therefore be changed in the called procedure without affecting its original value in the calling procedure. This means that as in the function GetMaxSaleslnfo, when it changes the values of the parameters on lines 8 and 9, these changes also take place in the calling procedure. So in line 5 of TestMaxSaleslnfo, the two actual parameters...
Exercises
1 State the elements of hardware that make up a microcomputer system. Give examples of each element. 2 Briefly describe the four generations of computer hardware, with an example from each generation. 3 Give three examples of 8 bit microcomputers. 4 What are the main factors attributed to the success of the IBM PC 5 What is the main difference between hardware and software Give an example of microcomputer hardware and software. 6 Categorise the following items as either operating system...
Select case
Consider the If structure shown in Listing 6.5. Although the logic is simple, the coding is a little difficult to follow. MsgBox Standing at rear Else MsgBox Front wing seats Else MsgBox Lower front wing seats Else MsgBox Upper Balcony Else MsgBox Royal Box End If End If End If End If End If VBA supports the Select Case statement that can make it easier to understand, multiple-choice conditions than If ElseIf Else statements. The syntax of the Select Case statement is as follows one or more VBA...
Validating data
Consider the workbook SALESMAN. The following program is concerned with ensuring that data entered into the weekly sales range are strictly numeric values within a set range. This has been arbitrarily selected as between 0 and 100. A VBA program will need to scrutinise each cell in the weekly sales range and check to see that its value is numeric and within the range 0 to 100. Should a value be entered that is outside this valid range, then the program should respond with a message requesting...
Take note Bbn
The built-in macros Auto_Open, Auto_Close, etc., have been replaced by event procedures see Chapter 10 in Excel 97 and above, but they are still supported for compatibility with workbooks created in previous versions. It is recommended that you do not to use the Auto_Open and Auto_Close macros in new workbooks. For even though they do what is required, implementing event procedures is more appropriate now see Chapter 10 . They have been included here for two reasons. If you are developing a...
Objectoriented programming languages
Object-oriented programming OOP languages have had a major impact on software development in the last decade. The OOP idea began to ferment in the 1970s, but it was not until the emergence of the Windows operating system that OOP languages became an established program paradigm. The main benefit of OOP is that programs become more re-usable. Software objects can limit and control a computer program and the data on which it Figure 1.4 Illustration of the Delphi Interface Figure 1.4 Illustration...
Trapping errors
Many programmers make the mistake of assuming that the user will always do what they are expected to do. However, this is a false assumption, for no matter what effort programmers make to provide explanations, users are still liable to carry out erroneous actions. The programmer therefore needs to anticipate user errors and handle them appropriately. The On Error GoTo Label statement provides one such way of doing this. It tells VBA what to do when a run-time error occurs. When you use an On...
Logical operators
VBA supports three logical operators And, Or, and Not. They let you combine two or more comparison tests into a single compound comparison. For example, suppose we wanted to implement the following code If myCell.Value 6 Then If Activecell.Value gt 0 Then MsgBox Done End If End If The two comparison tests can be replaced with one test as If myCell.Value 6 And ActiveCell.value gt 0 Then. Succeeds, or produces True, if both sides of the And are true. In this example myCell.Value must be 6 And z...
The InputBox function
The InputBox function enables a user to input data through a dialog box see Figure 3.5 . Notice that the generic dialog box has two buttons OK the default selection and Cancel. These buttons can be customised see Chapter 10 . The dialog box also contains a title in this case Ask Age , a prompt How old are you and a text box for the user's input. The InputBox function takes at least one parameter a prompt to the user. The format for using this function in a VBA program is illustrated in the...
Using AutoOpen
The following example illustrates how the Auto_Open procedure can be used to provide a start up message. The example shown in Listing 3.1 will give a different message for each day of the week. On Monday the message given is Have a nice week, on Tuesday the message is One down, four to go , and so on. The procedure works by creating a variable that will contain the digits from 1 to 7 representing the days of the week. It then finds out what day it is, using a built-in function that returns the...
Step Over
Step Into w orks by stepping through a program on line at a time in sequence. However, if one sub calls another, then you might w ant the debugger to step over the called sub. This can be done by using the Step Over option. This means that it will execute the called sub all at once, without stepping through it. As an example, consider the two procedures that are named GetRange and CountRange as shown in Listings 8.1 and 8.2. The purpose of the GetRange procedure is to enable the user to enter a...
Structured English pseudocode
Structured English is an informal language that helps the programmer to 'think out' the actual program steps before actually writing in a formal programming language such as VBA. Structured English is similar to everyday English and therefore is a useful step before translation to VBA code. It is not a programming language and its purpose is to aid code writing. This means that you don't need to be precise with structured English because it is not proper computer code. Also, include executable...
Programming style
Many students who are new to computer programming assume that getting the program to work correctly is all that matters However, even when a program is working correctly, changes to the program might be necessary in the future. Research has shown that program maintenance accounts for over 67per cent of software development time. There are many reasons why maintenance becomes necessary. For example, it may be necessary to make changes to the functionality of a program, or perhaps the...
Worked example Bfa
This example creates context-sensitive balloon Help for supporting the use of the SALESMAN workbook. In this example, the user will be presented with a balloon whose title is 'Help Choices for using the Salesman system'. The first thing to do is to define a v ariable that can store a Balloon obj ect. We can use a statement of the form You can create a Balloon object by using a property called NewBalloon. and this is assigned to the balloon variable. When assigning any object variable, we can...
The Immediate window
The Immediate window provides an opportunity to enter a line of code that will execute immediately. It is displayed by default the first time you open the Debug window, otherwise to display it, select View gt Immediate Window or press Crtl G . The Immediate window provides an ideal tool for experimentation for the result of any command entered is immediately interpreted. In the example shown in Figure 8.8, the effect of the command ActiveCell.Value 4 can be seen to be entered immediately in the...
Creating event procedure code
You cannot write event procedure code using a standard code module, because the code resides behind the objects that contain them. To create an event procedure you will need to follow the steps shown below 1 From the VBE, choose View gt Project Explorer. 2 In the P roject Explorer, you will see a list of all the open workbooks Figure 10.10 . Locate your workbook in the list, and expand that branch. Y ou'll see a folder called Microsoft Excel Objects. Expand this branch. 3 There will be an icon...
The Set keyword
As the last example in Table 5.2 shows, the difference between assigning a value to an object variable and a non-object variable is that the object assignment must begin with the keyword Set. The remainder of the syntax is identical to that when declaring a non-object variable. For example, the following two lines of VBA code declare a variable called this_month_sales as a Range object, and define it with the keyword Set. Set this_month_sales Range month_sales The above two lines will assign...
Adding controls to a user form
To add a control to a user form, point and click on the control in the Toolbox then drag to the point on the form where it is required and click. In this example, we will develop a Form that inputs two numbers and provides three buttons on the form one to add them, one to multiply them and one to quit. The steps are Open a new workbook and press Alt F11 to go to the VBE. Select Insert gt UserForm. A blank user form and the toolbox palette will be displayed. Change the Caption on the user form...
Using MsgBox with Yes or No Buttons
MsgBox has other uses apart from output display. For instance, a MsgBox could be used to get the user to make a simple choice of two or more options, and then take appropriate program action depending on the choice made. If the programmer were to customise the layout of the MsgBox to include two buttons taking values Yes, or No, then the statement Outcome MsgBox Do you like Bananas , vbYesNo, Tastes in fruit question would have the effect of producing the output as shown in Figure 3.8. Note how...
The problem scenario
Throughout much of this book, we will be referring to an Excel workbook called SALESMAN.XLS. This contains a number of worksheets including that displayed in Figure 2.1. The purpose of this worksheet is to maintain weekly sales data for each representative employed by a sales company. The top section contains administrative data such as the date the worksheet was written, and so on. The lower section contains data showing a column of representative names, along with the total sales to date,...
Macro security issues
When you try to open a workbook in Excel that contains one or more macros, you will probably see the dialog box appear as shown in Figure 2.19. Its purpose is to alert the user that macros may contain viruses. This could be true if the macros are from an unknown, or external source. If you are confident the macros are safe, then you can click the Enable Macros button. C Documents and 5ettings Keith Darlington Desktop 5ALE5MAN.xls contains macros. Macros may contain viruses. It is always sate to...
Explicit and implicit declarations
The VBA default data type is the Variant data type. As Table 5.1 shows, a variable of the variant data type consumes 16 bytes plus 1 for each character, and is used to store a data item when the data type is not known. Using a Variant data type can be costly in memory and program execution time. Lomax see Further reading, page 218 estimates that expressions using only Variant data execute about 30 slower than the same expressions using the correct intrinsic data types. In VBA, the programmer is...
MsgBox customisation
A MsgBox function can be customised in many ways, as the example in Figure 3.7 shows. A number of examples follow that illustrate customisation. The first example provides a meaningful title To whom it may concern in the Title bar by replacing the default Microsoft Excel title. This example also uses an indicator inside the dialog box of the type of data being displayed - in this case i for information. This variation is produced by including two optional arguments in the MsgBox statment as...
Adding macros to a toolbar
You can also add a macro to a toolbar, or assign one to a customised button. 1 Select View gt Toolbars gt Customise to open the Customise dialog box. 2 Click the Commands tab to open the Commands page, and select Macros from the Categories list see Figure 2.9 . 3 The Commands list for macros has two options Custom Menu Item and Custom Button. To add the macro to a toolbar, choose Custom Button, and drag it into place, e.g. on the Standard toolbar see,, Figure 2.10 . J S tl i Times New Roman j...
Setting explicit declarations for variables
You can set VBA for Excel to so that you are forced to declare all variables explicitly without having to write an Option Explicit line in every program. This can be done either globally for all modules or on a module by module basis. To do this, choose Tools gt Options from the VBE. You will see the Options dialog box. Click the Editor tab, and tick the Require Variable Declaration box see Figure 5.1 . Notice, that it is unchecked by default. Now, if you do this, you will find an Option...
The Object Browser
The Object Browser provides the primary means to find objects and their associated properties and methods in Excel VBA. It gives programmers a window to the object hierarchy in VBA and Excel, allowing them to inspect the objects available in VBA and Excel and their methods and properties. It can be accessed from the VBE either by clicking the Object Browser button on the Standard toolbar, or selecting Object Browser from the View menu. 'S Microsoft Visual Basic - Bookl version l .xls - Object...
The Debug tools
The VBE contains some powerful tools for debugging your program. One of the most important of these is the provision of a Watch window combined with the setting of Breakpoints. Figure 8.4 shows the facilities available on the Debug toolbar.
The form design
We need to create a form to provide the user with a means of entering the rep name and the rep sales to date. The form design is shown in Figure 10.7. The form Caption property has been called 'Add a Rep'. Two text boxes have been created one for the new rep name, the other for the sales. Labels for the two buttons and other controls are also included. A Frameset has b een added to surround the related items rep name and sales, and its caption is 'Enter Details'. There is also a list box, which...
Operator precedence
Arithmetical operators in VBA, as in everyday arithmetic, are not evaluated from left to right, but follow rules of precedence. These prevent ambiguity regarding the order in which arithmetical operations are to be followed. These rules of precedence can be memorised from the BODMAS mnemonic Brackets, power Of, Division, Multiplication, Addition, Subtraction . For instance, the expression 9 4 - 3 would give the result 33, since multiplication has a higher precedence than subtraction - . Thus, 9...
The Visual Basic toolbar
Excel contains a Visual Basic toolbar that groups actions for manipulating macros. To view this toolbar choose View gt Toolbars and select Visual Basic. The toolbar see Figure 2.18 include buttons for the following Run Macro displays the Macros dialog box, in which any currently available macro can be selected to run. Record Macro displays the Record Macro dialog box. Note that when the Visual Basic toolbar is on screen, the Record Macro button appears depressed clicking can stop the macro....
Which loop structure is best
As a general rule, it is best to use the loop that makes for the cleanest and clearest comparison test. Sometimes, the logic makes the Do While clearer, whereas other loops seem to work better when you set them up with Do Until. Remember, Do Until continues executing a block of VBA statements as long as the comparison test is false. As soon as the test becomes true, the loop terminates and the program continues on the line that follows the closing Loop statement. Remember also, that when the...
The test plan
A range of input data, both valid and invalid, needs to be carefully selected to give confidence in the working of the program. This usually involves creating a table of data to carry out a series of tests to develop enough confidence in the program the test plan. The first column in the table should refer to the test number. Following columns would list combinations of input data so that all possible paths in the program are going to be tested. The final right-hand column should show the...
The Excel object model
Many real-world objects can contain objects that are themselves objects. For example, the hardware of a computer system contains parts such as a monitor, speakers, keyboard, mouse, and so on. These are themselves objects - sometimes collections and individual - that have properties and methods associated with them. The same analogy can be made for example, when VBA for Excel interfaces with objects such as workbooks, cell ranges, cells, charts, and so on. An object model is a description of the...
Option explicit
A programmer can force all variable declarations in a program by inserting an Option Explicit command before the procedure definition begins. By inserting this optional command, the VBA program will expect every variable that is used in the program to be declared before it can be used. This is good VBA program practice. An example of Option Explicit follows 'Notice how the option explicit command precedes the procedure definition all variables must be declared 'Specific object Chart declaration...
Fourthgeneration languages visual languages
Fourth-generation languages 4GL provide a higher level of abstraction than third-generation languages in that they support visual program development. With visual programming, the programmer has the ability to create graphical user interfaces by pointing and clicking with a mouse, similar to using the Windows operating system. This contrasts with third-generation programming where the programmer has to write code with the keyboard. 4GLs are a product of the visual programming age that began...
Creating contextsensitive Help
In Chapter 3, we looked at VBA b uilt-in Help facilities. However, most users of Excel - or of any other Office application - will have had some experience with the Office Assistant. When clicked, this will deliver Help known as Balloon Help - so-called because of the way in which it is displayed Figure 10.13 . It is possible to customise the Help delivered by the balloon object, so that we can create context-sensitive Help, which could be of significant benefit to a user who needs support with...
Difficulties with maintenance
There are many difficulties that can arise from maintaining code as follows It can sometimes be very difficult to maintain code that was written by another programmer. It might be difficult to discuss these problems with the original programmers, because they may have left the organisation, retired, or whatever. The code produced might be unstructured - or following no method as the person who produced it was a novice at the time Code was produced in a previous standard of the implementation...
The Watch window the glasses icon
The Watch window lets you monitor one or more of the variables or cells or whatever as the program runs. Clicking this icon or choosing Add Watch. from the Debug menu will prompt the user with a dialog box as shown in Figure 8.5. In the case of the validateWeekSales program we are interested in watching the range variable cell, since this is going to contain the values of the input used in the tests. As you can see in Figure 8.5, this has been entered in the Expression box all other list boxes...
Exercises 1
Open the file SALESMAN.XLS. Write a recorded macro to make all data in the week_sales range of the weeklysales worksheet bold face and place a border around the week_sales range. Link the macro to a button named border to be placed on the right-hand side of the worksheet. Open the Excel workbook SALESMAN.XLS. Create a recorded macro calledprint_ main_data, which will select the range from A31 to I41. The macro should then print this area by using File gt Print Area gt Set Print Area. Test the...
The Properties pane
Everything that you see in Excel VBA, such as cell ranges, worksheets, charts, and so on, has a set of characteristics or properties associated with it. For example, a range of cells will have properties like the width of cells or the value stored in a cell. We can view and change these properties in the Properties pane - displayed in the lower left of the editor. Its purpose is to list those properties that are associated with the object currently selected in the Project Explorer. In the...
Worked example 1
To see how passing parameters by value and by reference work in practice, let's look at an example of what happens when we pass parameters by reference and by value. Consider the function, sqNum, which squares an integer, and then the sub, testSqNum, which makes a call to sqNum. Listing 9.9 displays the sub testSqNum code. Notice that an Integer variable calledpassVal is declared and assigned the value 10 in the sub. The call to the pass by value function is sandwiched between the two message...
Worked example
This example function is intended to work with the weeklysales worksheet of the SALESMAN.XLS w orkbook. Its purpose is to find the highest w eekly sales in the week_sales named range. We have used a function for this purpose b ecause w e require a value to b e returned i.e., the highest v alue. The function has been named getMaxSales, and searches through all the cells in the week_sales range to find the largest value, which will be an integer. The function therefore, will return an integer...
Input and output in VBA
Most programming languages will contain commands that enable the user to input data from the keyboard and output results on the monitor display. Of course, data could be input and displayed by using worksheet cells in Excel. However, this may not be appropriate for it might be necessary to input or output without using worksheet cells. A function is defined as something in VBA Excel that will return a value and will be familiar to any Excel user. Functions will be studied in more detail in...
Exiting a For loop
Sometimes, it is necessary to exit a For loop prematurely. For example, if the purpose of the code is to check for a particular value in a For range, and that value has been found, then the code should not have to check all other values in that range. However, this will happen because a For loop will by default continue through the range. Fortunately, there is a way to circumvent it. You can jump out of the For. Each loop when the item has been found by using the Exit For statement. The next...
Using constants in VBA
Sometimes it is convenient to name a data item that is used in a macro as a fixed or constant value. For example, the speed of light has a constant numerical value of 186,000 miles per second. If we were writing a macro that referenced this constant, we could name it in the same way that we name variables. However, it would clearly differ from a variable in that its value remains constant. You can do this by using the syntax shown below. Examples of the way in which constants are declared are...
The multiway If statement
The two-way If statement is applied when you want the program code to execute one set of instructions if some condition is true, else execute another set of instructions. However, there are times when you may want to execute one set of instructions if some condition is true, or else if some other condition is true, you may want to execute another set of instructions, and so on, until all alternatives have been completed. The general syntax is Consider the previous example. This was written as a...
Testing a validation program
This example will take the validation program Listing 4.2 and apply the black box method of testing. Recall that the purpose of this program was to validate the week sales range of data for each sales representative on the weeklysales worksheet of the SALESMAN w orkbook. In this example, data input into each cell had to be numeric, and in the range 0 to 100. To use the b lack b ox method of testing, we first need to decide on ranges for selecting appropriate data. As a starting point, we could...