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

Functions

Any Excel user will b e aware of functions such as Sum, Average, Count, and so on. These functions can be used from Excel, just like defining a formula in a cell, i.e., begin with the ' ' sign, then select the function required from the Function list box. These are the built-in functions. The idea behind a function is that you send data into it and the function sends back, or returns, an answer. These examples show how built-in functions are used in Excel The trigonometric Tan function returns...

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

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

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

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

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

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

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

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

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

Referencing named ranges

There are a number of ways to reference a range in VBA. Some of these are Using a named range - follow the Range object with the named range in double quotation marks. For example, Range week_sales Use a cell address range - the same as above, but use a cell address reference range. For example, Range D1 D5 Use a Range object variable see Chapter 5 . To create a Range object variable called myRange, you can use statements of the following form Dim RandomRange As Range Set RandomRange Range b1...

Fifthgeneration languages artificial intelligence languages

Fifth-generation languages were characterised by artificial intelligence AI . The term 'fifth-generation' became established during the 1980s. These languages are not, contrary to popular belief, the sequel to 4GLs. AI languages use a different paradigm to previous generations. Previous generations used the procedural programming paradigm to solve a problem this paradigm works by knowing 'how to solve the problem'. On the other hand, AI programs use the declarative paradigm this solves a...