Problem

You've heard of Excel objects (in fact I've already mentioned them several times) and want to know what they are and how they'll benefit you.

Solution

Excel exposes its features and functionality through objects. Excel's object model, as it's called, includes nearly 200 objects that represent everything from cell ranges to charts to worksheets, workbooks, and the Excel application. Each object has properties that allow access and control over its attributes, as well as methods that expose the object's functionality. When working with VBA, you access Excel and the spreadsheets included in your project through Excel objects. There are far too many objects to discuss here. Further, not all of them are directly relevant to the sort of calculations in which we're interested in this book. The purpose of this recipe is to introduce key objects to you now, so we can use them in later recipes. The discussion to follow gives an overview of these key objects.

Discussion

Before looking at specific objects, I want to discuss the Object Browser available in the VBA development environment. The Object Browser is a handy tool that allows you to browse through available objects, inspecting their properties and methods.

The Object Browser

As I mentioned earlier, there are nearly 200 Excel objects that you can access using VBA. Each object also contains numerous properties and methods. Thus, it's very difficult to keep track of all the objects, their properties, and methods without the aid of a good reference book, the VBA help guide, or the Object Browser. The Object Browser is a window that you can open within the VBA IDE by pressing the F2 key or selecting View images/U2192.jpg border=0> Object Browser from the main menu bar. Figure 2-6 shows the Object Browser opened in the VBA IDE.

Figure 2-6. Object Browser window

The Object Browser displays a list of objects. The upper-left drop-down listbox allows you to select the scope of the objects to view. For example, in Figure 2-6 I've selected Excel; therefore, the Object Browser is displaying only those objects belonging to Excel. Other options include selecting only those objects in your project or only objects in the Microsoft Office Suite object model; there are also many others. We're going to focus on objects in Excel.

The list labeled Classes is the list of objects. You can select any object in this list to display more information on the object. For example, the very bottom panel of the Object Browser window is telling us that the selected Application object is a member of the Excel object model. Also, the list to the right of the Classes list displays all *members* of the selected object. Members include both properties and methods. You can select a member in this list and the bottom panel will display relevant information, such as whether it is a property or a method. If you press the button at the top of the Object Browser window with the question mark icon, a new window will open that contains context-sensitive help specific to the selected object or member. Thus the Object Browser is a convenient way to navigate the object reference guide, too.

The second drop-down listbox at the top of the browser window allows you to enter a term (e.g., a property name), and search for it in the object list. The search results panel will display a list of findings for your search term.

I encourage you to play around with the Object Browser and explore the myriad objects, properties, and methods included in the Excel object model. That said, for the most part we'll use only a small subset of these for our purposes.

Application object

The `Application` object represents the Excel program. It's a top-level object that gives access to top-level features in Excel such as setting options, among others. Some of the most common uses I have for the Application object involve accessing an object that represents the currently active worksheet, accessing an object that represents the currently active cell, and controlling Excel's window updating while VBA procedures are running.

Example 2-17 shows how to access the currently active worksheet in Excel by using the `ActiveSheet` property of the `Application` object. You can type the statement shown in Example 2-17 directly in a VBA procedure.

Example 2-17. ActiveSheet property

Application.ActiveSheet.Cells(1, 2) = time |

You access members of objects by placing a period after the object name, followed by the property or method you want to access. In Example 2-17 I'm accessing the `Cells` property of the `ActiveSheet`, which itself is a property of the `Application` object. Note that properties can be objects with members of their own. This sort of cell access is useful when writing calculation output directly into cells in a worksheet, in this case the currently active worksheet. Note the cell reference style used here. `Cells(1, 2)` represents the cell in row 1 column 2.

Example 2-18 shows how to access the `ScreenUpdating` property of the `Application` object.

Example 2-18. ScreenUpdating property

Application.ScreenUpdating = false |

The `ScreenUpdating` property stores a `Boolean` value, indicating whether the Excel window should be updated while VBA code is running. In most cases I leave `ScreenUpdating` set to `TRue` so I can follow my VBA code as it runs. However, for some intensive calculations, especially with a lot of looping, keeping `ScreenUpdating` set to `true` can really slow things down. In those cases, it may be desirable to turn `ScreenUpdating` off (i.e., set it to `false`). You can set it back to `true` after the intensive block of code.

Workbook object

The `Workbook` object represents an Excel workbook. You can control many aspects of workbooks, such as gaining access to the active sheet in a workbook through its `ActiveSheet` property, obtaining the name and path to the workbook, and saving the workbook.

I often use the `Workbook` object's `Save` method to save the workbook. For example, if I write a particular VBA procedure that makes changes to a workbook by writing calculation output to cells in a worksheet, then I'll have the VBA procedure also save the workbook. Example 2-19 shows how to call the `Save` method.

Example 2-19. Save method

Application.ActiveWorkbook.Save |

In this example, I'm gaining access to the currently active workbook through the `Application` object's `ActiveWorkbook` property.

Worksheet object

The `Worksheet` object represents an Excel worksheet. It provides access to sheet-level features and functionality such as copying and pasting, printing the sheet, saving the sheet, and showing or hiding the sheet. I use it primarily to access cells, as shown earlier in Example 2-17 (in that example, `ActiveSheet` is a `Worksheet` object that refers to the currently active worksheet).

WorksheetFunction object

As discussed in Recipes 2.5 and 2.12, the `WorksheetFunction` object allows you to access Excel functions from within VBA. Figure 2-7 shows a screenshot of the Object Browser with the `WorksheetFunction` object selected.

Figure 2-7. WorksheetFunction object in the Object Browser

In the figure, you can see a partial list of functions to which you have access through the `WorksheetFunction` object. Example 2-20 shows a few ways to call Excel functions using the `WorksheetFunction` object.

Example 2-20. Calling functions through the WorksheetFunction object

area = WorksheetFunction.Pi * r ^ 2 a = WorksheetFunction.Acos(b) Set TestRange = Worksheets("Sheet1").Range("A1:A5") TestSum = WorksheetFunction.Sum(TestRange) |

The first line uses the worksheet function `Pi` in an expression for calculating the area of a circle given its radius (stored in the variable `r`).

The second line calculates the arccosine of the variable `b` and returns the result in the variable `a`.

The last pair of lines sets a range consisting of the first five cells in column A of `Sheet1` in the open workbook and then uses Excel's `Sum` function to get the sum of the values stored in the range.

This last pair of lines uses the `Worksheets` collection, which is simply a collection of all the open `Worksheet` objects in a workbook. In this case, we access a specific sheet in the collection using the sheet's name. We then access the `Range` property of the selected sheet and set it to include cells A1 through A5.

I use the `Set` statement to set the `Range` object to a temporary variable called `TestRange`. This saves a bit of typing when using the range in multiple statements. Finally, `TestRange` is passed in as an argument to the `Sum` function, and the result is returned in the `TestSum` variable.

Range objects

`Range` objects represent cell ranges. Earlier I showed you how to access cells using the `Cells` property of the `ActiveSheet` object. You can use `Range` objects as another means of accessing cells, as shown previously in Example 2-20.

Example 2-21 shows a few more ways to use the `Range` object to access cells.

Example 2-21. Using Ranges

Set TestRange = Worksheets("Sheet1").Range("A1:A5") TestSum = WorksheetFunction.Sum(TestRange) Worksheets("Sheet1").Range("A7").Value = TestSum Worksheets("Sheet1").Range("B7").Formula = "=Sum(A1:A5)" |

This example includes the last two statements from the earlier example, with the addition of two new statements. The second to last statement takes the sum calculated in the previous statement and puts it in cell A7. As a comparison, the final statement adds a cell formula to cell B7, which also calculates the sum of values in cells A1 to A5. When these statements are executed, both cells A7 and B7 should show the same value.

These are just a few common tasks that involve working with `Range` objects. Another very useful member of the `Range` object is the `GoalSeek` function, which we can use for iterative calculations (see Chapter 9 for some examples).

See Also

The summary presented in this recipe only scratches the surface of Excel's object model. Granted, we don't really need much in the way of Excel objects for our purposes; however, it's good to be familiar with the other objects in case you decide to expand your use of VBA and Excel; for example, if you decide you want to write and distribute your own add-ins.

For more information on Excel objects, you can always consult the VBA help guide. Look for the "Objects" topic under Microsoft Excel Visual Basic Reference in the table of contents. There you'll find an alphabetically organized object reference.

Using Excel

- Introduction
- Navigating the Interface
- Entering Data
- Setting Cell Data Types
- Selecting More Than a Single Cell
- Entering Formulas
- Exploring the R1C1 Cell Reference Style
- Referring to More Than a Single Cell
- Understanding Operator Precedence
- Using Exponents in Formulas
- Exploring Functions
- Formatting Your Spreadsheets
- Defining Custom Format Styles
- Leveraging Copy, Cut, Paste, and Paste Special
- Using Cell Names (Like Programming Variables)
- Validating Data
- Taking Advantage of Macros
- Adding Comments and Equation Notes
- Getting Help

Getting Acquainted with Visual Basic for Applications

- Introduction
- Navigating the VBA Editor
- Writing Functions and Subroutines
- Working with Data Types
- Defining Variables
- Defining Constants
- Using Arrays
- Commenting Code
- Spanning Long Statements over Multiple Lines
- Using Conditional Statements
- Using Loops
- Debugging VBA Code
- Exploring VBAs Built-in Functions
- Exploring Excel Objects
- Creating Your Own Objects in VBA
- VBA Help

Collecting and Cleaning Up Data

- Introduction
- Importing Data from Text Files
- Importing Data from Delimited Text Files
- Importing Data Using Drag-and-Drop
- Importing Data from Access Databases
- Importing Data from Web Pages
- Parsing Data
- Removing Weird Characters from Imported Text
- Converting Units
- Sorting Data
- Filtering Data
- Looking Up Values in Tables
- Retrieving Data from XML Files

Charting

- Introduction
- Creating Simple Charts
- Exploring Chart Styles
- Formatting Charts
- Customizing Chart Axes
- Setting Log or Semilog Scales
- Using Multiple Axes
- Changing the Type of an Existing Chart
- Combining Chart Types
- Building 3D Surface Plots
- Preparing Contour Plots
- Annotating Charts
- Saving Custom Chart Types
- Copying Charts to Word
- Recipe 4-14. Displaying Error Bars

Statistical Analysis

- Introduction
- Computing Summary Statistics
- Plotting Frequency Distributions
- Calculating Confidence Intervals
- Correlating Data
- Ranking and Percentiles
- Performing Statistical Tests
- Conducting ANOVA
- Generating Random Numbers
- Sampling Data

Time Series Analysis

- Introduction
- Plotting Time Series Data
- Adding Trendlines
- Computing Moving Averages
- Smoothing Data Using Weighted Averages
- Centering Data
- Detrending a Time Series
- Estimating Seasonal Indices
- Deseasonalization of a Time Series
- Forecasting
- Applying Discrete Fourier Transforms

Mathematical Functions

- Introduction
- Using Summation Functions
- Delving into Division
- Mastering Multiplication
- Exploring Exponential and Logarithmic Functions
- Using Trigonometry Functions
- Seeing Signs
- Getting to the Root of Things
- Rounding and Truncating Numbers
- Converting Between Number Systems
- Manipulating Matrices
- Building Support for Vectors
- Using Spreadsheet Functions in VBA Code
- Dealing with Complex Numbers

Curve Fitting and Regression

- Introduction
- Performing Linear Curve Fitting Using Excel Charts
- Constructing Your Own Linear Fit Using Spreadsheet Functions
- Using a Single Spreadsheet Function for Linear Curve Fitting
- Performing Multiple Linear Regression
- Generating Nonlinear Curve Fits Using Excel Charts
- Fitting Nonlinear Curves Using Solver
- Assessing Goodness of Fit
- Computing Confidence Intervals

Solving Equations

- Introduction
- Finding Roots Graphically
- Solving Nonlinear Equations Iteratively
- Automating Tedious Problems with VBA
- Solving Linear Systems
- Tackling Nonlinear Systems of Equations
- Using Classical Methods for Solving Equations

Numerical Integration and Differentiation

- Introduction
- Integrating a Definite Integral
- Implementing the Trapezoidal Rule in VBA
- Computing the Center of an Area Using Numerical Integration
- Calculating the Second Moment of an Area
- Dealing with Double Integrals
- Numerical Differentiation

Solving Ordinary Differential Equations

- Introduction
- Solving First-Order Initial Value Problems
- Applying the Runge-Kutta Method to Second-Order Initial Value Problems
- Tackling Coupled Equations
- Shooting Boundary Value Problems

Solving Partial Differential Equations

- Introduction
- Leveraging Excel to Directly Solve Finite Difference Equations
- Recruiting Solver to Iteratively Solve Finite Difference Equations
- Solving Initial Value Problems
- Using Excel to Help Solve Problems Formulated Using the Finite Element Method

Performing Optimization Analyses in Excel

- Introduction
- Using Excel for Traditional Linear Programming
- Exploring Resource Allocation Optimization Problems
- Getting More Realistic Results with Integer Constraints
- Tackling Troublesome Problems
- Optimizing Engineering Design Problems
- Understanding Solver Reports
- Programming a Genetic Algorithm for Optimization

Introduction to Financial Calculations

- Introduction
- Computing Present Value
- Calculating Future Value
- Figuring Out Required Rate of Return
- Doubling Your Money
- Determining Monthly Payments
- Considering Cash Flow Alternatives
- Achieving a Certain Future Value
- Assessing Net Present Worth
- Estimating Rate of Return
- Solving Inverse Problems
- Figuring a Break-Even Point

Index

Excel Scientific and Engineering Cookbook (Cookbooks (OReilly))

ISBN: 0596008791

EAN: 2147483647

EAN: 2147483647

Year: N/A

Pages: 206

Pages: 206

Authors: David M Bourg

Similar book on Amazon

Flylib.com © 2008-2020.

If you may any questions please contact us: flylib@qtcs.net

If you may any questions please contact us: flylib@qtcs.net