Problem

You know VBA is an object-oriented language and you'd like to create your own objects for use in other subroutines and functions.

Solution

Create a new *Class Module* in VBA and add your object properties and methods.

Discussion

Let me show you how to do this by way of example. First, for those of you not familiar with object-oriented lingo, an *object* is a code construct comprised of *properties* and *methods*. Properties are variables that describe the object in some way; they store attributes of the object. Methods are functions or subroutines that actually do something. They can manipulate properties, perform some action on given parameters, or do whatever other programming task you can devise. Objects provide a convenient way of packaging code that allows easy reuse.

In Recipe 7.11, I show you how you might add support for manipulating vectors using VBA. You might also approach the same problem by writing a vector object to encapsulate all the properties and functionality of a vector. This way you can define vector type variables and perform computations with these variables. I'll show you step by step how to get started.

Open Excel and the VBA editor. In the VBA editor, select Insert images/U2192.jpg border=0> Class Module from the main menu bar. In VBA (as in some other languages) an object is called a *class*. Under the VBA project browser panel, you should see a folder called Class Modules and under that a new module called something like `class1`. Now press F4 (or select View images/U2192.jpg border=0> Properties Window from the main menu bar) to open the properties panel. Click on your new class and you should see its properties displayed in the properties panel.

For the moment you won't see much. You should, however, see a Name property. Click the field to the right of Name and type in `MyVector`. This is the new name for your vector object, which should also be reflected in the VBA project browser window.

Find the module now named `MyVector` in the project browser and double-click it to open a code window. It will be blank at first, so you need to add the properties and methods for this new vector class.

First, let's add some properties. At the very least, for a 2D vector, you know you need an `x` and a `y` component. So add the code shown in Example 2-22.

Example 2-22. MyVector properties

'MyVector Object Properties: Public x As Double Public y As Double |

These look similar to local variable declarations. Notice the `Public` keyword, however. You need to use this keyword to make your properties available to you outside of this module. You can add any number of properties in this manner, but for now these two will suffice.

When you use a custom object in other code, you have to create the object by allocating memory for it. I'll show you how to do that later, but you should be aware that when an object is created, VBA will automatically call an initializing subroutine to initialize the object for you, setting its properties to default values. If you want your object's properties set to some specific default values, then you should create a custom initialize method.

Look at the top of the code window in your class module. You should see a drop-down listbox with the word General displayed in it. Click this listbox and select Class from the drop-down list. Over to the right, you'll see another listbox. Click it and select "initialize" from the list. Now you should see that a new subroutine has been created in your code module. It should look like that shown in Example 2-23.

Example 2-23. Empty initialize method

Private Sub Class_Initialize( ) End Sub |

You can add your custom initialization code inside this subroutine. For example, in Example 2-24 I added two lines to initialize this object's properties to 0.

Example 2-24. Empty initialize method

Private Sub Class_Initialize( ) x = 0 y = 0 End Sub |

It isn't quite necessary in this simple case, but does illustrate how to initialize objects.

So far, this vector object only stores two values. To make it actually do something, you have to add methods. As I said earlier, methods are just subroutines and functions. Example 2-25 shows one I added to compute and return the magnitude of the vector.

Example 2-25. Magnitude method

Public Function Magnitude( ) As Double Magnitude = sqrt(x * x + y * y) End Function |

This method is simply a function. You add it just as you would a regular function, as shown earlier in this chapter. The `Public` keyword makes this function available outside of this module. As you can see, the vector magnitude is computed using the object's `x` and `y` properties. Since these properties have module-level scope, you can access them from within any method you write.

You can add any number of methods in a similar manner. For example, you could add a subroutine that normalizes the vector, or you could write a subroutine that adds another vector given as a parameter to the vector, and so on.

Now we have a real object that can do something. To use this object in other code, you must first declare a variable with your object name as the type. Then you have to call VBA's `Set` and `New` statements to actually create the memory for the object, which also initializes the object by calling its initialize method if one exists. Once the object is created, you can use it in code using the `.` syntax to access its methods and properties. Example 2-26 illustrates all these tasks.

Example 2-26. Using the MyVector class

Public Sub MySubroutine( ) Dim v As MyVector Dim mag As Double Set v = New MyVector v.x = 2 v.y = 4 mag = v.Magnitude End Sub |

The line `Dim v As MyVector` declares a local variable of type `MyVector`. You can't use it just yet (unlike `mag`, which is a regular variable of type `Double`). The line `Set v = New MyVector` is required to actually create the `MyVector` object, assigning it to the variable `v`.

The lines `v.x = 2` and `v.y = 4` set the `x` and `y` properties of the vector, `v`, to 2 and 4, respectively. They'll stay that way until you change them. A method is called in a similar manner. The line `mag = v.Magnitude` calls the vector's `Magnitude` method, returning the vector magnitude and storing it in the local variable `mag`.

See Also

You can create fairly complex objects encapsulating a wide variety of functionality using these simple techniques. In Recipe 13.7, I use these same techniques to create a `Chromosome` object for a genetic algorithm.

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