Declaring and assigning variables

Each variable has a name - something that is decided by the programmer. The programmer also decide what type of data it will hold. For example, if a variable is to store a person's age, a whole number - or integer - might be appropriate, as people normally give their age as an integer, such as 45 or 38. Each variable is of a particular type. The type is designated by the programmer in a process known as declaration. A variable can be given a value - this is called assignment - and that value...

The Add ButtonClick event

The event handler (Listing 10.5) works by checking to see if the value in the textbox called RepBox is empty. If it is, then the message Enter a name for the rep to b e added is delivered, followed by setting the focus to the RepBox object by using the statement .SetFocus. This will ensure that the focus will continue to be on the RepBox object, forcing the user to enter a value for this. When a non-empty value is input the focus then switches to getting a value from the user for the starting...

The Ibm Pc the catalyst for standards

By the time that the fourth-generation computers were being built, IBM was well established in manufacturing mainframe and minicomputers. IBM's entry into the microcomputer market was relatively late, but turned out to be a defining moment because the IBM PC became a standard. It did so in the sense that other computer manufacturers were keen to ensure that software written for their systems would work on the IBM PC they wanted to ensure that their software was 'IBM compatible'. This created a...

Loops controlled by a number

The previous example used a For .Next loop to go through all the cells in a range - this is a very powerful way to process spreadsheets. You can also use a number to control how many times a For .Next loop executed. This kind of loop is very common in traditional programming languages when general variables are used. For example, the weekly sales data in the SALESMAN workbook might require calculations to find the total weekly mileage from seven daily totals. Rather than do this manually, a...

Software and its evolution

Computer hardware is of little use without computer software hardware without software is like a CD player without compact disks. However, the hardware of a computer system requires different types of software - called programs - to operate. First, it needs programs that will control all the hardware units to link together so that they act as a whole, and enable the user to interact with the computer. The software to operate and control the hardware is called the operating system or operating...

Using active cells and jumping around in a For Next loop

This example uses the weeklysales worksheet of the SALESMAN.XLS workbook, to check to see if a salesperson is worthy of promotion. The criteria for promotion in the organisation are currently that the sales person's monthly sales should exceed 1000 units. The macro is given in Listing 7.3. Active cells are used to implement this program. It is a slightly different approach to what we have seen so far because instead of using a For. Next loop on the cell range, we will use a loop on the range of...

VBA program using variables

VBA Listing 5.1 includes line numbers for ease of explanation. Do not include these in any VBA for Excel code. Listing 5.1 VBA program using variables 1 Option Explicit 'forces the programmer to declare all variables 1 Option Explicit 'forces the programmer to declare all variables Dim first_number As Integer 'declare 3 variables as integers first_number InputBox(prompt enter first number) second_number InputBox(prompt enter second number) Columns(B B).ColumnWidth 18 Range(B1).Font.Bold True...

End user interface styles VBA Excel applications

Application Vba Excel Interface

In essence, the designer's decision regarding the interface styles will could be one of those described below. (Note A hybrid of two or more of these styles may be used in practice.) The standard Excel worksheet interface may be suitable for those who are familiar with Excel, supported perhaps with comments to guide the user. For example, Figure 10.15 illustrates how comments can add explanation to the meaning of a column of data in a spreadsheet. In the fragment shown of the weeklysales...

Events

The following tables list some of the more useful events for the event procedures for the objects listed in Chapter 10. Occurs when a worksheet is activated Occurs when the user double-clicks on a cell * Occurs when the user right-clicks on a cell * Occurs when a cell on the worksheet is calculated. Occurs when the value of a cell is changed, not following a change as the result of a calculation. Occurs when a worksheet is deactivated, or another worksheet is displayed. Occurs when the...

Excel Vba Reference

Visual Basic Applications for Excel is a programming language well suited to beginners. It provides many of the Visual Basic programming facilities through the Excel application. Thus, students who have access to Excel can gain familiarity with Visual Basic without having to step up to the full blown program. With VBA, programmers have the power to customise Excel applications that would be impossible to achieve with Excel alone. VBA can often provide a faster, and sometimes easier-to-implement...

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,...

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...

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...

Worked example

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...

Creating event procedure code

Event Procedure

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 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,...

The form design

Form Design Vba Autocad

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...

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....

The Excel object model

Vba Excel Objektmodell

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...

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...

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...

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...

The fourth generation

The fourth generation of computers became known as microcomputers, and were being built in the late 1970s and were based upon silicon chip technology. The level of miniaturisation was such that these silicon chip based machines became small enough to fit on a home desktop and, thus, the birth of the home computer began. The term microcomputer has now become synonymous with what we now call the Personal Computer PC . Today's machines are still using silicon chip technology, but, enormously...

Comparing different data types

The expressions on both sides of a comparison operator must conform to the same data type or at least compatible data types. You cannot compare a string to a numeric data type, e.g. one with 1. They are stored differently in the computer, and if you try, you will get a type mismatch error. You can compare any numeric data type against any other numeric data type most of the time. In other words, you can test whether a single-precision value is less than or greater than an integer value, such as...

Toggle Breakpoint the hand icon

There is little point in opening a Watch window, unless you can slow the program operation down enough to get a chance to see what is happening during program execution. A breakpoint does exactly that it sets a program execution break at a line of code as a point at which the program will pause. It is a forced execution break in the program, and you can set breakpoints wherever you want. Select Toggle Breakpoint on the Debug menu and choose breakpoints carefully, because if, for example, you...