Read an Excerpt
CHAPTER 1
Visual Basic for Applications
1.1 THE VISUAL BASIC OBJECT MODEL
1.1.1 Summary
This first Visual Basic project looks at the organisation of objects (the object model, Figure 1.1.1), the toolbars or menus commonly used for designing VB projects (the environment), and the automatic writing of small programmes (recording macros).
The version of Visual Basic used herein (VB6 in VBA) is not wholly object oriented, although its successor (VB.NET) is. The latter is a complete redesign of the whole code, and conversion programmes are available. However, the object library is similar to all applications, and is structured as above.
All the codes for this book were originally designed for a PC environment that used a 1024
A lower display setting than 1024
1.1.2 The object model
Object oriented programs
Object oriented programming (OOP) has been in existence for several years. It is superceding the use of older procedural codes such as Fortran and Basic for the following reasons:
• It runs faster since there is efficient reuse of large portions of code (the objects).
• It enables users to interact with code by means of controls.
• It works with many applications such as Excel, Word, Access, and others. Note that other OOP such as Delphi may also communicate with MS applications.
• It enables the transfer of data between applications, platforms and systems.
• It is easier to write since it is developed in pieces (modules).
• It has an expandable object library for applications of increased size.
• It enables the immediate visualisation of pre and post processing of code.
• It enables small programmes to be recorded automatically.
The main reason why OOP has these advantages is because a major part of the code, the "Object Library" is already specifically prepared for each application. For example, the main 5 object collections used by Excel are drawings, charts, forms, ranges and controls. These collections are used for all the code examples used in this book, and no new object collection has been found necessary.
Perhaps the simplest explanation for OOP is that is similar to English grammar. The sentence:
I select a scatter chart
has a subject, verb, adjective and object. In OOP, the sentence becomes:
chart.scatter.select
and is controlled by the programme, a programme control event, or the User. Thus a grammatical object noun becomes an OOP "object", a verb becomes a "method" of the object, an adjective becomes a "property" of the object, and a subject noun becomes a "control" for the object. It is this simulation of natural language that eases the coding, and enables the object library to represent the whole application.
The VB object model shown in Figure 1.1.1 consists of several layers, beginning at the Application level. Although each of the levels may be considered an object, it is more appropriately described by its collective noun. A familiarity with applications is assumed, and the following discussion looks at the lower levels in succession. Note that there are several ways for issuing a command in Excel, and those detailed herein are based on the author's experience.
Tasks level
A Taskbar is the lowest line of any Windows display (although it may be moved elsewhere), as shown in Figure 1.1.2 below. It contains the [Start] icon at the left, and then the Task icons. The [Microsoft Excel – Project.xls] task refers to the Excel workbooks contained by the task. This however represents only part of the application, as the other major Task is the [Microsoft Visual Basic – Project.xls] or VB Editor (VBE). The latter is invoked by enabling the VB Toolbar and activating the [Visual Basic Editor] icon.
Using the workbooks alone will enable the drawing of objects, and the worksheet programming of worksheet cells. However, it is soon learned that worksheet programming is limited by reason of space in the formula bar, and the VBE provides far superior access for larger codes. The word "Project" is reserved for the description of a single project file and its associated code. Thus each of the code examples described in this book are called projects.
Note that multiple projects may be added to the application by use of so-called "Add- ins". They are accessed using the Excel commands:
[Tools, Add-ins] then checking the required programme
A particularly useful Excel "Add-in" is the data analysis programme, which calculates a variety of statistics. It is not however used in any of the present codes.
Collections level
Each workbook task contains a number of sheet objects. By default, the number of sheets is set to 3, but it may be altered using the Excel commands:
[Tools, Options, General, Sheets in new workbook]
(When using this command, it is always convenient to set the [Default file location] command towards future use.) Note that each worksheet has a name tab at the bottom left corner, and an appropriate worksheet name can be set.
The VBE has similar collections of windows. These are named as Sheet objects used for storing control event codes, Modules used for storing method and function codes, and Class modules used for storing user-defined object codes. As stated above, the latter are not used herein, but the former 2 collections are used throughout.
Objects level
All of the following projects in this book use the 5 object types of Drawings, Charts, Forms, Ranges and Controls in the worksheets. Although each object is associated with a specific toolbar ([View, Toolbars]), the [Insert, Chart] commands are used preferentially to the [Chart] toolbar.
The [Drawing] toolbar provides access to both 2 dimensional and 3 dimensional Drawing objects on the worksheet. The toolbar may be exercised by drawing the 2D and 3D examples given for this current project. The Chart commands provide access to 14 different Chart types, and up to 9 sub-types for each of these types. Only 3 different Chart types are used for these projects, corresponding to 1D, 2D and 3D data. They are exercised in the "Objects.xls" project. The Forms object type is not used extensively in these projects; forms are useful for controlled data input in commercial programming. There are however 2 commonly used "built-in" (intrinsic) forms in Excel, namely a Message Box and an Input Box. The former is demonstrated in this project, and the latter in the "Objects.xls" project.
The Ranges object describes the worksheet cells used for data. The object is edited using the [Formatting] toolbar, and its use is common throughout these projects. Finally, the Controls object types are invoked from the [Control] toolbox. Only 5 controls are commonly used in these codes, namely the Check box, the Command button, the Option button, the Spin button and the Scroll bar. Each control is associated with [Properties] and an [Event] code. They are readily accessed by entering VB Design mode (activating the [Design mode] icon) and right clicking the [Control] icon.
Procedures level
The procedures are the actual VB code used in the projects. For organisational purposes, the code in the control event procedures under the Sheet Objects is kept to a minimum. The code is used mainly to call method and function procedures developed in the Modules. It is also used to provide Message box warnings when irrelevant controls are operated.
Since Hydrology involves the intermediate calculation of many separate equations, it is useful to branch to a Functions procedure where all calculations are stored. The clarity of the Methods procedure is then maintained. The Functions procedure is labelled by the VB command Function Name(Variables) and the methods procedure by Sub Name(Variables). An example for separating subroutines and functions is given for the "Evap.xls" project. Note that a function procedure is not intended to interact with a worksheet, but only with the variables (the data) in the subroutine from which it is called.
1.1.3 excel environment
The default Excel environment is sufficient for developing these projects. However, the addition of relevant toolbars facilitate the exercise. A knowledge of the coordinates of drawing objects on a worksheet is also required. These factors are now considered.
The 5 supporting toolbars for VB development in Excel are shown in Figure 1.1.2. The toolbars are loaded using the Excel commands:
[View, Toolbars] then clicking the required toolbar
The chosen toolbars fit comfortably on the 3rd and bottom row of the Excel window. The formula bar is also included, as worksheet calculations are often needed to confirm parts of a program.
Display settings
The display settings are controlled by the operating system, and are accessed using the following [Windows 2000] commands:
[Start, Settings, Control Panel, Display, Settings, Advanced]
As noted above, a Screen Area of 1024
Excel sheet coordinates are referenced from the top left corner of the first worksheet cell (0,0). This first cell has a graphical coordinate width (abcissa) of 64/17
Another important consideration for display settings is the number of rows and columns that are displayed on a worksheet. For a 1024
In summary, the adaptation of code from a normal to a large font requires changes in the positions of drawing objects. And the reduction of display settings requires the reduction of image size (by zooming) to show the same objects.
1.1.4 Recording a Macro
A simple way to begin coding in VB is to enable the automatic recording of key strokes and mouse clicks which are used in object creation and manipulation. The resulting code is called a [Macro] (an abbreviation for macroscopic, meaning a large scale as opposed to microscopic) and is written to the modules collection. The following example details the steps used in creating the 3D worksheet for this code. (The Excel commands are inserted in square brackets.)
1. Starting at the [Drawing] toolbar:
[Autoshapes, Lines, Freeform]
The cursor should now be a cross, and the above shape (Figure 1.1.3) may be copied by clicking several similar points, and closing at the starting position with a double click.
2. Moving to the 3D icon on the [Drawings] toolbar, activate (click) the freeform then:
[3-D, 3-D settings, 3-D On, 3-D Color (Light Orange)]
3. One may now experiment by activating the [Tilt] icons to rotate the freeform. However, we wish to record these actions in a macro, so activate the [Record Macro] icon (the small, filled blue circle on the VB toolbar). A Message Box will appear to inform that [Macro 1] is being recorded in [This Workbook]. The macro will be written to [Module 1] in the VBE. Note that the small, filled blue circle changes to a small, filled blue square, and remains as such until the macro is terminated by clicking the square.
4. Activate the [Freeform], then tilt it several times to the left and right. Close and observe the macro by activating the [Visual Basic Editor] icon on the VB toolbar:
[Visual Basic Editor, Modules] then double click Module 1
The following VB code should be available:
ActiveSheet.Shapes("Freeform 1").Select Selection.ShapeRange.ThreeD.IncrementRotationY -5#
5. If we tilted the [Freeform 1] object to a maximum (180o of arc), we must repeat the IncrementRotation Y 5# command 36 times. Instead, we can write a small code to do this, which "loops" (repeats) the command 36 times. The macro may be copied and pasted, and the new code can be written in a new module called "Tilt" say, using the following commands in VBE:
[Insert, Module, View, Properties Window] then change the Name property to "Tilt"
The Do Loop is one of 2 very common procedures used in VB (the other being the If condition), and the new code becomes:
Sub HillTiltLeft()
6. This code may be Run by reducing the size of the VBE window, placing the cursor anywhere in the code and using the VB commands:
Run, Run Sub/User form
It is soon found that a "HillTiltRight" subroutine is required, and the rotation is too fast.
The rotation may be slowed by inserting code to wait for 1 second after each loop:
Application.Wait (Now + TimeValue("0:00:1"))
7. It would be more convenient to run this code from the worksheet itself. A control event is therefore required. A control object can be drawn on the worksheet by accessing the [Control] toolbox, activating the [Command Button] icon, and inserting the button to a relevant position. By right clicking the new control, the object [Properties] may be accessed, and the control [Caption] may be changed to say "TiltLeft". The [View code] command may also be accessed by right clicking, and it will open the VBE at the Sheet2 (3D) object. An event subroutine is already prepared and one simply inserts the macro name that is to be run, as follows:
Private Sub CommandButton1_Click()
8. There finally remains a form object to introduce on the worksheet as "Reset":
Private Sub CommandButton5_Click()
1.2 OBJECTS AND PROCEDURES
1.2.1 Summary
This project demonstrates objects and procedures in more detail, since they are the main part of a programme. We begin with a more detailed description of the object library (Figure 1.2.1), then demonstrate sequentially 3 major chart object types used in engineering. The types are associated with the graphical display of 1, 2 and 3 dimensional data.
The data used are generated from random number selection using the VB intrinsic (built- in) Random function in the procedures. Since this method of data generation is used throughout the book, it is introduced at this early stage. A series of 1D data is generated in a procedure, sorted in ascending order, and plotted as a Column chart. The logical If condition is then used to sort the data into classes, and a histogram is plotted.
A series of data for 2D representation is generated, sorted, and plotted against a fixed, ascending abcissa on a Scatter chart. The slope and intercept of the sorted random data may be varied to simulate any particular distribution. This technique is used later in the Monte Carlo simulation of frequency distributions (Chapter 2, "EVA.xls").
Finally, a random array of data is generated and plotted as a 3D Surface chart and Surface plan chart. This illustrates the very useful ability of Excel to display surface data for say Digital Elevation Models.
(Continues…)
Excerpted from "Visual Hydrology"
by .
Copyright © 2004 IWA Publishing (text) and Peter A Mantz.
Excerpted by permission of IWA Publishing.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.
