Microsoft Excel: Object-Oriented Programming

The first generation of popular programming languages were procedural languages, which meant that programmers designed an algorithm, or procedure, for the program to follow and defined variables (placeholders for values) as the program developed. As programs became increasingly complex, the need for descriptive variable names increased in importance. While it’s easy to create a variable named price to store the price of a product you offer for sale in a store, it’s difficult to write an expandable program that can keep track of all of the prices in an ever-changing product inventory. Every time you wanted to add a product to your store, you would need to create a new variable. Doing that once or twice is no big deal, but if you write a lot of programs and want to save time and effort by reusing your code, you need some way of organizing your program around the things in your environment.

Procedural programmers attempted to solve the problem by representing the things in their environment using an abstract data type, which is a collection of characteristics and operations that reflect the values and actions associated with something you need to represent in a program (such as a product). For example, a garden supply store could have both an indoor sales area and a greenhouse, with products associated with each location. The abstract data type product might have values reflecting the name of the product, the category to which the product belongs, the product’s price, the product’s supplier, a description, and so on. It’s important to note, however, that defining an abstract data type for a product doesn’t create a place to hold the values and actions associated with that product. Instead, you would need to create an instance of the product abstract data type to store the data and actions associated with the new brand of potting soil you just started offering for sale in your garden supply store. The instance would have a unique identifier within the system, such as product001, and the program would know that the product name, category, price, description, and supplier would all refer to that particular product.

While abstract data types are a handy way to define sets of variables in a program, the underlying structure of the programming languages that use abstract data types is still procedural because there can be routines that exist outside of the abstract data types. In an object-oriented programming language, every aspect of your computer code is based around the things in your environment. Those “things,” not surprisingly, are represented as objects, and all actions and data are encapsulated within those objects. In Excel, those objects could be workbooks, worksheets, ranges of cells, or external files. In general, there are four aspects of objects you can use to flesh out a program:

? Properties
? Methods
? Events
? Collections

Properties
In brief (actually, in total as well), properties are variables that describe some aspect of the object in which they are included. A common property for objects in Excel is Name, which holds the identifying value you or Excel assigned to the workbook, worksheet, cell range, or other object to which you’re referring. If you change the worksheet’s name, whether by using VBA code or by right-clicking the worksheet’s tab on the tab bar, clicking Rename, and editing the value, you change the value that is stored in the Name property. You can set new values for some worksheet properties (such as Name) directly, but to change other workbook properties you need to either take action using the Excel interface (such as by protecting a cellrange) or a method (described in the next subsection).

In VBA, properties are referred to in a program using dot notation, where the object name is written first, the property name is written second, and the two elements are separated by a period. For example, to change the name of a worksheet, you would use the Worksheet. Name property. Changing the name of a worksheet when you’ve edited or updated the values in one
or more worksheet cells lets you and your colleagues know that the data on that sheet is new and should be checked before the worksheet is included in any final written products.

Methods
A method is an action that an object “knows” how to perform. For example, you probably know that the worksheet displayed in the Excel window is referred to as the active worksheet. In Excel VBA, you can change the worksheet you’re affecting with your VBA code by calling the target worksheet’s Activate method. After the Activate method runs, the worksheet to which it is attached moves to the front of the worksheets in the Excel window and becomes available for editing. As with properties, methods are called using dot notation. To recalculate all of the formulas in a worksheet, for example, you would call the Worksheet.Calculate method.

Events
Just as a property is a quantifiable attribute of an object and a method is an action an object knows how to take, an event is an action an object recognizes as having happened. For example, Excel 2003 knows about the following events (among many others):
? A workbook is opened or closed.
? A worksheet is activated or deactivated.
? A workbook is saved.
? A chart is clicked.
? A key (or combination of keys) is pressed.
? Data is typed into a cell.
? The formulas in a worksheet are recalculated.
? A hyperlink is followed.
Excel comes with a number of event handlers, or code routines that watch for particular actions to occur. When one of those actions does occur, and you’ve told Excel what you want it to do when the event happens, Excel will run the code in your event handler. For example, if after creating a new workbook you want Excel to display all open workbooks as a cascaded set of windows, you could create the following event handler:

Private Sub App_NewWorkbook(ByVal Wb As Workbook)
Application.Windows.Arrange xlArrangeStyleCascade
End Sub

Don’t worry if you’re not sure what each and every element of the event handler routine does; for now you can concentrate on the middle line of code, which tells the Excel application to arrange its windows using the cascade style. It’s the same result that would occur if you clicked Window, Arrange, Cascade in the Excel menu system, but if it’s an action you want to happen every time a particular event occurs, you can use VBA to make it happen and save you the trouble.

Collections
The final element of object-oriented programming with which you should be familiar is the collection. As the name implies, a collection is a group of objects of the same type that are contained within another object. For example, a workbook contains a collection of one or more worksheets. If you wanted to make a change to every worksheet in a workbook, you could step through every worksheet in the collection and make the change programmatically.

Article Source: http://www.rightbiz.com

World Web Directory
We are the fastest growing human edited web directory in the world. Listing of new website in 48 hours on static html pages with static text links.We are the only directory providing life time directory listing on static html pages.

Excel VBA Tutorial
We Provide Professional Training For Microsoft Excel and VBA With Video Example.

Please Rate this Article

Not yet Rated