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

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

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

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