## Solar Power Design Manual

#### Solar Power Design Manual Summary

Rating: 4.6 stars out of 11 votes

Contents: Ebook
Author: Richard Stubbs
Price: \$19.97

Access Now

#### My Solar Power Design Manual Review

This ebook comes with the great features it has and offers you a totally simple steps explaining everything in detail with a very understandable language for all those who are interested.

This ebook served its purpose to the maximum level. I am glad that I purchased it. If you are interested in this field, this is a must have.

## Fourth Order Runge Kutta Method Applied to a Differential Equation Involving Both x and y

In the preceding examples, the differential equation involved only the dependent variable y. In the general case, the differential equation can be a function of both x and y. The following example illustrates the use of the Runge-Kutta method for dy dx F x, y . A function is described by the differential equation and the function has the value y 0.5 at x 0. We want to find the value of the function over the range x 0 to x 1. Figure 10-4 illustrates the use of the RK method to model the...

## The Basics of Using XML Data in Excel

As intimidating as an XML document may seem, it's really nothing more than a text file that contains data wrapped in markup tags that denote structure and meaning . These tags essentially make the text file machine-readable. The term machine-readable essentially means that any application or web-based solution designed to read XML files will be able to discern the structure and content of your file. Because XML is text-based, it is not platform-dependent. That is to say, XML is not dependent on...

## Picture Format Properties

Set Get the brightness of the parent shape 0 to 1, where 1 is the brightest Set Get the type of color setting of the parent shape Set Get the contrast of the parent shape 0 to 1, where 1 is the greatest contrast Set Get how much is cropped off the bottom Set Get how much is cropped off the left Set Get how much is cropped off the right Set Get how much is cropped off the top Set Get the color used for transparency Set Get whether transparent colors appear transparent

## Characters Object

The Characters object allows access to individual characters in a string of text. Characters can have some of the visual properties modified with this object. Possible parents of the Characters object are the AxisTitle, ChartTitle, DataLabel, and Range objects. Each of the parent objects can use the Characters Start , Length property to access a part of their respective texts. The Start parameter can specify which character to start at, and the Length parameter can specify how many to take from...

## Using Custom Functions

I've covered VBA functions and Excel worksheet functions. The third category of functions you can use in your VBA procedures is custom functions. A custom function also known as User Defined Function, UDF is one you develop yourself by using what else VBA. To use a custom function, you must define it in the workbook in which you use it. Here's an example of defining a simple Function procedure and then using it in a VBA Sub procedure MultiplyTwo num1 num2 End Function Dim n1 As Double, n2 As...

## Making a Choice by Using the Select Case Statement

You can use the If Then Else or If Then ElseIf statement to meet all decision-making needs. However, using these statements can quickly make your code hard to read when you need to make a lot of decisions in rapid succession. Using these statements is required when you want to perform complex expression checking. VBA provides the Select Case statement as an easier-to-read choice when making a single selection from a list of choices. If you know that a variable contains one of several choices...

## Using the Select Case statement

The Select Case structure begins with the Select Case statement and ends with an End Case statement. You provide a variable that the Select Case statement can use for selection. Within the Select Case structure are Case clauses, or values that the Select Case structure uses for comparison. When the value of a clause matches the value of the input variable, the Select Case structure performs all tasks required by that clause. In this section, I use examples of the Select Case statement to make a...

## Msg Box

The MsgBox dialog box allows you to display a message, such as Please wait or Access denied. The box can display one of four message icons, and there are many possibilities in the number and function of buttons that can be displayed. The syntax of the MsgBox function is MsgBox prompt_text, buttons, title_text, helpfile, context where prompt_text is the message displayed within the box, buttons specifies the buttons to be displayed, and title_text is the title to be displayed in the Title Bar of...

## Using BuiltIn Constants

Many properties and methods have their own predefined constants. For Excel objects, these constants begin with the letters xl. For Word objects, the constants begin with wd. For VBA objects, the constants begin with vb. For example, Excel's Window object has a WindowState property that recognizes three built-in constants xlNormal to set a window in its normal state , xlMaximized to maximize a window , and xlMinimized to minimize a window . To maximize the active window, for example, you would...

## Comparison of ActiveX Controls to Windows Forms Controls

ActiveX controls differ from the corresponding Windows Forms controls in many ways. For example, the names of some of the controls and their properties, methods, and events are different. The left column in Table 4.5 shows the names of the ActiveX controls, and the right column shows the names of the corresponding Windows Forms controls. Table 4.6 shows the changes in the names of the properties, methods, and events that are common to many of the controls listed in Table 4.5. Table 4.5. ActiveX...

## The Chart AxesVBA Name Axis

Each axis in a chart is a member of the Axes collection. The sample chart has two axes X and Y , known respectively as the category axis and the value axis. The simplest VBA syntax for specifying a given axis is where Type is an Excel VBA constant specifying the axis. For our chart, the value of Type can be xlCategory for the X axis or xlValue for the Y axis . Using Cht.Axes without the Type argument returns the entire Axes collection for the chart. The following code demonstrates how you can...

## Floor Properties

Returns the ChartFormat object, which controls the line, fill, and effect formatting for the chart area Name String Returns a string representing the name of the object PictureType Variant Set Get how an associated picture is displayed on the floor of the 3D chart for example, stretched or tiled . Use the xlChartPictureType constants Thickness Long Set Get the thickness of the floor. Default is 0

## Nonlinear Regression Using the Solver An Example

To perform nonlinear least-squares curve fitting using the Solver, your spreadsheet model must contain a column of known y values and a column of calculated y values, so that the sum of squares of residuals can be calculated. The calculated y values must be spreadsheet formulas that depend on the curve fitting coefficients that will be varied by the Solver. To illustrate the use of the Solver for nonlinear least-squares curve fitting, we'll use as an example the system of two consecutive...

## What Is the Visual Basic Editor

The Visual Basic Editor is a separate application where you write and edit your VBA macros. It works seamlessly with Excel. By seamlessly, I mean that Excel takes care of opening the VBE when you need it. You can't run the VBE separately Excel must be running in order for the VBE to run. The quickest way to activate the VBE is to press Alt F11 when Excel is active. To return to Excel, press Alt F11 again. You can also activate the VBE by using the DeveloperOCodeOVisual Basic command. If you...

## Naming objects

In addition to providing names for cells and ranges, you can give more meaningful names to objects such as pivot tables and shapes. This can make it easier to refer to such objects, especially when you refer to them in your VBA code. To change the name of a nonrange object, use the Name box, which is located to the left of the formula bar. Just select the object, type the new name in the Name box, and then press Enter. Note If you simply click elsewhere in your workbook after typing the name in...

## Code Module Properties

Returns the active CodePane for the module. If there is no visible CodePane, one is created and displayed. Note that a CodeModule can have up to two code panes, but there is no CodePanes collection for them Read-only. Returns the number of lines at the top of the module used for Dim, Type, and Option statements. If there are any such items at the top of the module, any comments following them are considered to be part of the following procedure, not the declarations. The following...

## Run Time Error Handling

When you are designing an application, you should try to anticipate any problems that could occur when the application is used in the real world. You can remove all the bugs in your code and have flawless logic that works with all permutations of conditions, but a simple operational problem could still bring your code crashing down with a less than helpful message displayed to the user. For example, if you try to save a workbook file to the floppy disk in the A drive, and there is no disk in...

## Importing XML data to a list

The example in the preceding section used XML files that contained only a single record. XML files often contain multiple records, called repeating elements. Examples include a customer list or data for all employees in an organization. You can use the Excel File Open command to open an XML file that contains repeating elements. After you specify the filename, Excel presents the Open XML dialog box, as shown in Figure 4-6. This dialog box has three options As an XML List The file opens, and...

## Using the Editor Format tab

Figure 7-9 shows the Editor Format tab of the Options dialog box. The options on this tab control the appearance of the VBE itself. Breeipwtt Tem Comment Text KeywddTert Figure 7-9 The Editor Format tab of the Options dialog box. The Code Colors option lets you set the text color foreground and background and the indicator color displayed for various elements of VBA code. This is largely a matter of individual preference. Personally, I find the default colors to be just fine. But for a change...

## Adding a Chart Sheet Using VBA Code

There is no need to create an embedded chart. You can simply add a chart sheet and set its properties directly. You can also create an object variable, so that you have a simple and efficient way of referring to the chart in subsequent code. Rather than limit yourself to the preset layouts, you can select the chart features you want, such as a title. There is no need to plot by columns and then plot by rows. The following code incorporates these changes Sub...

## File SystemObject GetFile Name Method VB

OFileSysObj.GetFileName Path oFileSysObj Any object variable returning a FileSystemObject object. Return Value A String value. Description Returns the filename element of a given path. Rules at a Glance If the filename can't be determined from the given Path, a zero-length string is returned. FileSystemObject.GetFileName Method VB6 299 Path can be a relative or an absolute reference. Programming Tips amp Gotchas GetFileName doesn't verify that a given file exists in Path. Path can be a network...

## Dialog Box Controls The Details

In the following sections, I introduce you to each type of control you can use in custom dialog boxes and discuss some of the more useful properties. I don't discuss every property for every control because that would require a book that's about four times as thick and it would be a very boring book . The Help system for controls and properties is thorough. To find complete details for a particular property, select the property in the Properties window and press F1. Figure 17-4 shows the online...

## Referencing User Form Controls

When working with controls on a UserForm, the VBA code is usually contained in the code window for the UserForm. You can also refer to UserForm controls from a general VBA module. To do so, you need to qualify the reference to the control by specifying the UserForm name. For example, consider the following procedure, which is located in a VBA module. It simply displays the UserForm named userForml. Assume that UserForml contains a text box named TextBoxi , and you want to provide a default...

## On Close Save a Setting to the Registry

In case you are unfamiliar with Windows, there is a very important database called the registry. In Windows 3.1, the registration database stored OLE registration information and file associations, while program settings were stored in initialization .ini files. With the introduction of Windows 95 and later versions, the registry became the central repository for all this information. As you have read, the registry database is vital to the operation of all applications installed on your...

## Processing a series of files

One common use for macros, of course, is to repeat an operation a number of times. The example in this section demonstrates how to execute a macro on several different files stored on disk. This example -which may help you set up your own routine for this type of task - prompts the user for a file specification and then processes all matching files. In this case, processing consists of importing the file and entering a series of summary formulas that describe the data in the file. In previous...

## Object Oriented Programming An Overview

I decided early on in the process of writing this book that I would concentrate my efforts on providing guidance in the creation of class-based solutions to Excel VBA coding problems where possible. I have found through programming in Access, VB 6, and then VB.NET that using OOP techniques has helped me visualize my applications more clearly, helped me to better organize my code, and has been invaluable in making my code easier to maintain. Standard code modules and procedural programming allow...

## Worksheets Collection Properties and Methods

Returns a collection holding all the horizontal page breaks associated with the Worksheets collection Set Get whether the worksheets in the collection are visible. Also can set this to xlVeryHidden to prevent a user from making the worksheets in the collection visible Read-only. Returns a collection holding all the vertical page breaks associated with the Worksheets collection Method. Parameters Before , After , Count , Type . Adds a worksheet to the collection. You can specify where...

## Stepping Through Code

There are two ways to begin the process of stepping through the code of a Sub procedure Select the name of the procedure in the Macro Name list box and press the Step Into button. This will display the code module containing the procedure the first line of the procedure will be highlighted in yellow, as in Figure 2-22 . Add a breakpoint as described in the following section, then run the Sub procedure in the usual way. When the code window is displayed, with a line of code highlighted, you can...

## Two Ways to Specify Arguments of Methods

VBA methods usually take one or more arguments. The Sort method, for example, takes 10 arguments. The syntax of the Sort method is object.Sort key1, orderl, key2, order2, key3, order3, header, orderCustom, The object argument is required all other arguments are optional. You can specify the arguments of a method in two ways. One way is to list the arguments in order as they are specified in the preceding syntax, i.e., Range A1 E150 .Sort Last Name, xIAscending which sorts the data contained in...

## Linear Regression

Linear regression is not limited to the case of finding the least-squares slope and intercept of a straight line. Linear regression methods can be applied to any function that is linear in the coefficients . Many functions that produce curved x-y plots are linear in the coefficients, including power series, for example, and some functions containing exponentials, such as Mathematically, a function that is linear in the coefficients is one for which the partial derivatives of the function with...

## Late Binding

Another timing issue that can have significant impact on performance is whether the form is using early or late binding. Basically, this refers to when the record or recordset is created for a form or other object. Late binding typically involves filtering, which reduces the size of the recordset and also allows the developer greater control of the record source. When you add fields to your form, the text boxes that are created become bound text boxes. That is, the table or query fields are...

## Derivatives of a Worksheet Formula

Instead of calculating the first or second derivative of a curve represented by data points, we may wish to find the derivative of a function a worksheet formula . In the following, two different methods are illustrated to calculate the first or second derivative of a worksheet formula by using a user-defined function. The calculation of the first derivative of the function y 3x3 5x2 - 5x 11 is used as the example for each method Derivatives of a Worksheet Formula Calculated by Using a VBA...

## Displaying Excels Builtin Dialog Boxes

You can write VBA code that performs the equivalent of selecting an Excel menu command and making choices in the resulting dialog box although Excel doesn't actually display the dialog box. For example, the following statement has the same effect as choosing the EditOGo To command, specifying a range named InputRange, and clicking OK Application.Goto Reference InputRange When you execute this statement, the Go To dialog box does not appear. This is almost always what you want to happen you...

## Nonlinear Regression Using the Solver

If you have read the preceding chapter on linear regression and are familiar with the use of LIN EST, you should have no trouble recognizing a function that is linear in the coefficients. Some examples of functions that are linear in the coefficients arsy a bx cx2 dx ory aex. However, if the function is one such as it is not linear in the coefficients. It should be obvious that it's not possible to apply LI NEST to this equation given a column of x values, you can't create a column of ea bx...

## Handling Errors Another

How can you identify and handle every possible error The answer is that often you can't. Fortunately, VBA provides another way to deal with errors. Revisiting the EnterSquareRoot procedure Examine the following code. I modified the routine from the previous section by adding an On Error statement to trap all errors and then checking to see whether the InputBox was cancelled. Sub EnterSquareRoot5 Dim Num As Variant Dim Msg As String ' Set up error handling On Error GoTo BadEntry Num InputBox...

## Duplicating rows a variable number of times

The example in this section demonstrates how to use VBA to create duplicates of a row. Figure 11-9 shows a worksheet for an office raffle. Column A contains the name, and column B contains the number of tickets purchased by each person. Column C contains a random number generated by the RAND function . The winner will be determined by sorting the data based on column 3 the highest random number wins . Figure 11-9 The goal is to duplicate rows based on the value in column B. Figure 11-9 The goal...

## Embedded Charts

When you create a chart embedded as a ChartObject, it is a good idea to name the ChartObject so that it can be easily referenced in later code. When you select the chart, you will see its name to the left of the Formula bar at the top of the screen in the name box. You can select and change the name of the ChartObject in the name box and press Enter to update it. The embedded chart in Figure 8-4 was created, dragged to its new location, and had its name changed to MangoesChart. The name can...

## Smart Workbook Design

Although this book focuses on the VBA aspect of developing Excel applications, this chapter wouldn't be complete without mentioning some practical advice regarding the general layout of an Excel application. In Chapter 22, I describe various application-distribution strategies and techniques such as templates, add-ins, and standard Excel workbooks. For the purposes of this section, I'm assuming that you're developing a standard Excel workbook. By standard Excel workbook, I mean an application...

## Determining default printer information

The example in this section uses a Windows API function to return information about the active printer. The information is contained in a single text string. The example parses the string and displays the information in a more readable format. Private Declare Function GetProfileStringA Lib kernel32 ByVal lpAppName As String, ByVal lpKeyName As String, ByVal lpDefault As String, ByVal lpReturnedString As _ String, ByVal nSize As Long As Long Dim strLPT As String 255 Dim Result As String Call...

## Circular Objects

This section demonstrates how to create the various circular objects. You create an Arc object by using the AddArc method. This method takes four arguments that determine the position and size of the arc Set ArcObject Object.AddArc CenterPoint, Radius, StartAngle, EndAngle Table 8-2 provides a brief description of each argument. A three-element array of doubles specifying the center of the arc in the WCS The start angle of the arc given in radians with respect to the X-axis of the WCS The end...

## Writing a Macro in the VBE

In this example, you'll create a macro by typing code directly in the VBE. Open the file 1-MacroExample01.xlsx shown in Figure 1-21 , and open the VBE. Note You will find all the example files and source code for this book at www.apress.com in the Downloads section of this book's home page. Figure 1-21. Sales data for the first quarter of the year Figure 1-21. Sales data for the first quarter of the year We see tour sales for the fictitious band VBA, which are received quarterly by their...

Data Type A Form or Control object An expression that evaluates to a form or control. Description Loads a form or control into memory. Rules at a Glance When a control or form is first loaded using the Load statement, it's resident in memory, but it isn't visible on the screen. To make a form visible, use the form's Show method. To make a control visible, set its Visible property to True. You can explicitly create a control only with the Load statement as part of a control array. See following...

## Watch Object Example

The following routine prompts the user for a range, then loops through each cell in the range and adds it to the Watch window. It then displays the Watch window Set rngWatches Application.InputBox _ Please select a cell or cell range to watch, 'View the watch window based on their answer Application.CommandBars Watch Window .Visible

## How Excel Handles Custom Dialog Boxes

A custom dialog box is created on a UserForm, and you access UserForms in the Visual Basic Editor VBE . Following is the typical sequence that you will follow when you create a UserForm 1. Insert a new UserForm into your workbook's VB Project. 2. Write a procedure that will display the UserForm. This procedure will be located in a VBA module not in the code module for the UserForm . 3. Add controls to the UserForm. 4. Adjust some of the properties of the controls that you added. 5. Write event...

## The Offset property

The Offset property, like the Range and Cells properties, also returns a Range object. But unlike the other two methods that I discussed, the Offset property applies only to a Range object and no other class. Its syntax is as follows obj ect.Offset rowOffset, columnOffset The Offset property takes two arguments that correspond to the relative position from the upper-left cell of the specified Range object. The arguments can be positive down or to the right , negative up or to the left , or...

## Thinking in Terms of Objects

When you are developing applications with Excel especially when you are dabbling with Visual Basic for Applications - VBA , it's helpful to think in terms of objects, or Excel elements that you can manipulate manually or via a macro. Here are some examples of Excel objects A range or a table in a worksheet A ListBox control on a UserForm a custom dialog box A chart embedded in a worksheet A particular data point in a chart You may notice that an object hierarchy exists here The Excel object...

## Working With Commandbars

By using toolbars and shortcut menus, you can provide quick access to commonly used commands. Excel has more than 50 different built-in toolbars and about 60 different shortcut menus, all of which are part of the CommandBars collection. This collection also contains any new custom toolbars and menus you create. Due to their central location, you can easily make modifications to existing menus and toolbars, or add new ones as needed. UNDERSTANDING THE COMMANDBARS COLLECTION The CommandBars...

## Randomizing a range

The RangeRandomize function, which follows, accepts a range argument and returns an array that consists of the input range - in random order Dim V As Variant, ValArray As Variant Dim Temp1 As Variant, Temp2 As Variant Dim RCount As Integer, CCount As Integer ' Return an error if rng is too large CellCount rng.Count If CellCount gt 1000 Then RangeRandomize CVErr xlErrNA Exit Function ReDim ValArray 1 To 2, 1 To CellCount Fill ValArray with random numbers and values from rng For i 1 To CellCount...

## Writing the Sort procedure

It was time to sort the SheetNames array. One option was to insert the sorting code in the SortSheets procedure, but I thought a better approach was to write a general-purpose sorting procedure that I could reuse with other projects sorting arrays is a common operation . You might be a bit daunted by the thought of writing a sorting procedure. The good news is that it's relatively easy to find commonly used routines that you can use or adapt. The Internet, of course, is a great source for such...

## Open Method

This method opens an existing workbook. The rather complex syntax is WorkbooksObject.Open FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMRU Most of these parameters are rarely used several of them relate to opening text files, for instance . We discuss the most commonly used parameters and refer the reader to the help files for more information. Note that all of the parameters are optional...

## The Open Method

This method opens a connection to the data source, and has the following syntax connection.Open ConnectionString, UserID, Password, Options The ConnectionString argument serves the same purpose as the ConnectionString property discussed in the previous section. ADO allows you to set this property in advance or pass it in at the time you open the connection. The UserID and Password arguments can be passed separately from the connection string if you wish. The Options argument is particularly...

## The Properties Window

The Properties window shows you the properties that can be changed at design time for the currently active object in the Project Explorer window. For example, if you click Sheet1 in the Project Explorer, the Sheet1 properties are displayed in the Properties window, as shown in Figure 1-7. The ScrollArea property has been set to A1 D10, to restrict users to that area of the worksheet. You can get to the help screen associated with any property very easily. Just select the property, such as the...

## Listing Using the Find Method

Sub SearchRecordsWithFind Dim rs As ADODB.Recordset Dim strCriteria As String Set rs .Source Employees .ActiveConnection Northwind .CursorType adOpenKeyset .Open End With strCriteria City 'Seattle' rs.Find strCriteria ' Loop to find other records that meet the criteria Do While Not rs.EOF MsgBox rs First Name amp amp rs Last Name 1 Search again, but skip a row rs.Find strCriteria, 1 After opening the Employees table as the recordset, this code uses the strCriteria variable to hold the criteria...

## Creating a UDF

Unlike manual operations, UDFs cannot be recorded you have to write them from scratch using a standard module in the VBE. If necessary, you can insert a standard module by right-clicking in the Project Explorer window and choosing Insert O Module. A simple example of a UDF is shown here Fahrenheit Centigrade 9 5 32 End Function Here, a function called Fahrenheit is created that converts degrees Centigrade to degrees Fahrenheit. In the worksheet, you could have column A containing degrees...

## Removing a VBA module

If you need to remove a VBA module or a class module from a project, select the module's name in the Project Explorer window and choose File Remove xxx where xxx is the name of the module . Or you can right-click the module's name and choose Remove xxx from the shortcut menu. You are asked whether you want to export the module before removing it. See the next section for details. You cannot remove code modules associated with the workbook the ThisWorkbook code module or with a sheet for...

## Visible Property

You can hide items by setting their Visible property to False. Say you are working with the grouped dates from the last exercise, and you want to see only Jan 2006 and Jan 2007, as shown in Figure 7-12. Sub CompareMonths Dim pvt As PivotTable Dim pvi As Pivotltem Dim sMonth As String 'Specify month to be visible sMonth Jan Set pvt ActiveSheet.PivotTables l 'Hide all years except 2006 amp 2007 For Each pvi In If pvi.Name lt gt 2006 And pvi.Name lt gt 2007 Then 'Make sure specified month is...

## User Defined Data Types

VBA lets you create custom, or user-defined, data types a concept much like Pascal records or C structures . A user-defined data type can ease your work with some types of data. For example, if your application deals with customer information, you might want to create a user-defined data type named Company As String Contact As String RegionCode As Long Sales As Double End Type Note You define custom data types at the top of your module before any procedures. After you create a user-defined data...

## Figure The Quick Watch window

1 en lp lattlJnQ BCt amp IbMOC 1 jferhYOt ih irpA Another way to quickly get values for expressions or variables is to enable Auto Data Tips on the Editor tab of Excel VBA's Options dialog box. With this feature enabled, when we place the mouse pointer over a variable or select an expression and place the mouse pointer over it, after a slight delay, a small yellow window will appear containing the value of the variable or expression. This is very useful

## DoUntil loop

The Do-Until loop structure is similar to the Do-While structure. The two structures differ in their handling of the tested condition. A program continues to execute a Do-While loop while the condition remains true. In a Do-Until loop, the program executes the loop until the condition is true. Do Until condition statements Exit Do statements The following example is the same one presented for the Do-While loop but recoded to use a Do-Until loop ActiveCell.Value ActiveCell.Value 2...

## File SystemObject CopyFile Method VB

OFileSysObj.CopyFile Source, Destination , OverwriteFiles oFileSysObj Any object variable returning a FileSystemObject object. FileSystemObject.CopyFile Method VB6 283 The path and name of the file to be copied. The path and optionally the filename of the copy to make. Flag indicating whether an existing file is to be overwritten True or not False . Copies a file or files from one folder to another. Rules at a Glance The default value for OverwriteFiles is True. The source path can be relative...

## Worksheet function examples

In this section, I demonstrate how to use worksheet functions in your VBA expressions. Finding the maximum value in a range Here's an example showing how to use the MAX worksheet function in a VBA procedure. This procedure displays the maximum value in the range named NumberList on the active worksheet You can use the MIN function to get the smallest value in a range. And, as you might expect, you can use other worksheet functions in a similar manner. For example, you can use the LARGE function...

## Chart Object Methods

Name Returns Parameters Description Activate Variant Makes the embedded chart the active chart Activate Variant Makes the embedded chart the active chart Brings the embedded chart to the front of all the other objects on the sheet. Changes Copies the Chart object into the clipboard as a picture. The Appearance parameter can be used to specify whether the picture is copied as it looks on the screen or when printed. The Format parameter can specify the type of picture that will be put into the...

## Some Useful Application Properties

When you're working with Excel, only one workbook at a time can be active. And if the sheet is a worksheet, one cell is the active cell even if a multicell range is selected . VBA knows about active worksbooks, worksheets, and cells, and lets you refer to these active objects in a simplified manner. This is often useful because you won't always know the exact workbook, worksheet, or range that you want to operate on. VBA handles this by providing properties of the Application object. For...

## Displaying the date and time

If you understand the serial number system that Excel uses to store dates and times, you won't have any problems using dates and times in your VBA procedures. The DateAndTime procedure displays a message box with the current date and time, as depicted in Figure 11-14 . This example also displays a personalized message in the message box title bar. Figure 11-14 A message box displaying the date and time. Figure 11-14 A message box displaying the date and time. The procedure uses the Date...

## Tb Empposition.value

A simple form to collect information from the user. Private Sub btn_EmpOK_Click Dim LastRow As Long LastRow 1 Cells LastRow, 1 .Value tb_EmpName.Value Cells LastRow, 2 .Value tb_EmpPosition.Value Cells LastRow, 3 .Value tb_EmpHireDate.Value End Sub Private Sub btn_EmpOK_Click Dim LastRow As Long LastRow 1 Cells LastRow, 1 .Value tb_EmpName.Value Cells LastRow, 2 .Value tb_EmpPosition.Value Cells LastRow, 3 .Value tb_EmpHireDate.Value End Sub The information gets added to the sheet.

## RibbonX and VBA

As well as having the ability to create custom tabs and groups containing built-in controls, Microsoft has provided the ability to add many types of custom controls to the Ribbon, and to hook their actions and most of their attributes to VBA procedures and functions. This is done using a mechanism known as a callback. A callback means simply that, as part of a RibbonX definition, you provide the name of a procedure to run when the control is clicked, changed, and so on. It's exactly the same as...

## Integrating a Function

Instead of finding the area under a curve defined by a set of data points, you may wish to integrate a function F x . You could simply create a table of function values and use one of the methods described in earlier sections to calculate the area. But a more convenient solution would be to create a custom function that uses the Formula property of the cell to get the worksheet formula to be integrated, in the same way that was used in the preceding chapter, and uses the formula to find the...

## Simple Input and Output with VBA

You have already seen how to get input from the user through the use of the Value property of a spreadsheet cell. Conversely, you can generate output for the user through the spreadsheet. Yet there may be times when you want something more dynamic and dramatic than a spreadsheet cell. The easiest method for gathering input from the user and sending output back is the InputBox and MsgBox functions. Just as Excel comes with a Large number of functions for the user to use in spreadsheet formulas...

## The VBA Integrated Development Environment IDE

Before learning how to program in VBA, you have to learn how to use the software required for creating your projects. The VBA development software is included with each component of the Microsoft Office suite of programs, including Excel. Starting the VBA development software places you in the VBA programming environment IDE, which provides you with a number of tools for use in the development of your project. An IDE is software used by programmers for rapid application development RAD . IDE's...

## The Copy and Paste methods

You can perform copy and paste operations in VBA by using the Copy and Paste methods. The Copy method is applicable to the Range object, but the Paste method is applicable to the Worksheet object. This short macro copies range A1 A12 and pastes it to the range beginning at cell C1 Range A1 A12 .Select Selection.Copy Range C1 .Select ActiveSheet.Paste End Sub Notice that in the preceding example, which the macro recorder generated, the ActiveSheet object is used with the Paste method. This is a...

## Charts Collection Properties and Methods

The Charts collection has a few properties and methods besides the typical collection attributes. These are listed in the following table. Read-only. Returns a collection holding all the horizontal page breaks associated with the Charts collection Set Get whether the charts in the collection are visible. Also, you can set this to xlVeryHidden to prevent a user from making the charts in the collection visible Read-only. Returns a collection holding all the vertical page breaks associated with...

## Erase Statement

Resets the elements of an array to their initial unassigned values. In short, Erase clears out or empties an array. Specify more than one array to be erased by using commas to delimit array-list. Fixed array variables remain dimensioned on the other hand, all memory allocated to dynamic arrays is released. The following table describes how Erase reinitializes the elements of a fixed array. Each member of the user-defined type is treated as Once you use Erase to clear dynamic arrays, they must...

## Trapping errors

You can use the On Error statement to specify what happens when an error occurs. Basically, you have two choices Ignore the error and let VBA continue. Your code can later examine the Err object to determine what the error was and then take action if necessary. Jump to a special error-handling section of your code to take action. This section is placed at the end of the procedure and is also marked by a label. To cause your VBA code to continue when an error occurs, insert the following...

## Pivot Caches

A PivotCache is a buffer, or holding area, where data is stored and accessed as required from a data source. It acts as a communication channel between the data source and the PivotTable. In Excel 2007, you can create a PivotCache using the Create method of the PivotCaches collection, as seen in the recorded code. You have extensive control over what data you draw from the source when you create a PivotCache. Particularly in conjunction with ADO ActiveX Data Objects , which is demonstrated at...

## Area under a Curve

By area under a curve we mean the area bounded by a curve and the x-axis the line y 0 , between specified limits. The area can be positive if the curve lies above the x-axis or negative if it is below. Calculation of the area under a curve is sometimes referred to as quadrature, since it involves subdividing the area under the curve into a number of panels whose areas can be calculated. The sum of the areas of the panels will be an approximation to the area under the curve. The three most...

## Pivot Items

Each PivotField object has a Pivotltems collection associated with it. You can access the Pivotltems using the Pivotltems method of the PivotField object. It is a bit peculiar that this is a method and not a property, and is in contrast to the Hiddenltems property and Visibleltems property of the PivotField object that return subsets of the Pivotltems collection. The Pivotltems collection contains the unique values in a field. For example, the Product field in the source data has four unique...

## Linear Interpolation in a Two Way Table by Means of Worksheet Formulas

To perform linear interpolation in a two-way table a table with two ranges of independent variables, x and y and a two-dimensional array of z values forming the body of the table , we can use the same linear interpolation formula that was employed earlier. Consider the example shown in Figure 5-15 we want to find the viscosity value in the table for x 76 F, y 56.3 wt ethylene glycol. The shaded cells are the values that bracket the desired x and y values. Viscosity of Heat Transfer Fluid cps...

## Cubic Interpolation in a Two Way Table by Means of Worksheet Formulas

To perform cubic interpolation between data points in a two-way table, we use a procedure similar to the one for linear interpolation. Figure 5-18 shows the table of viscosities that was used earlier. In this example we want to obtain the viscosity of a 63 solution at 55 F. The shaded cells are the values that bracket the desired x and y values. Viscosity of Heat Transfer Fluid cps Figure 5-18. Cubic interpolation in a two-way table. The shaded cells are the ones used in the interpolation,...

## Customizing the BuiltIn Ribbon

Rather than create your own tab, you might instead want to customize an existing built-in tab. Remember that just because you can do something, it doesn't mean that you should There are some things to keep in mind as you customize the Ribbon. Although you can hide tabs and groups, you should avoid doing so. Other installed add-ins might rely on these built-in items that you've hidden. You cannot add buttons to a built-in group. This is unfortunate because in some situations it would make sense...

## GoTo statements

The most straightforward way to change the flow of a program is to use a GoTo statement. This statement simply transfers program execution to a new instruction, which must be preceded by a label a text string followed by a colon, or a number with no colon . VBA procedures can contain any number of labels, but a GoTo statement cannot branch outside of a procedure. The following procedure uses the VBA InputBox function to get the user's name. If the name is not Howard, the procedure branches to...

## Assignment Statements

An assignment statement is a VBA instruction that makes a mathematical evaluation and assigns the result to a variable or an object. Excel's Help system defines expression as a combination of keywords, operators, variables, and constants that yields a string, number, or object. An expression can perform a calculation, manipulate characters, or test data. I couldn't have said it better myself. Much of the work done in VBA involves developing and debugging expressions. If you know how to create...

## Types of References

You can add references to many types of libraries from your Access projects. Library types include the following type or object libraries OLB, TLB, and DLL , ActiveX controls OCX and references to other Access databases ACCDB, ACCDE, MDB, and MDE , Access add-ins ACCDA and MDA , and Access projects ADP and ADE . An object library or type library generally provides functionality for access to other applications or adds functionality to use in your Visual Basic code. For example, Microsoft Office...

## Add A New Data Series To A Chart

After you create a chart, you can redefine the range of data Excel uses to display values on the chart by adding a new data series. A data series consists of a group of data values, which Excel displays on the chart. For example, if you have a bar chart showing the monthly sales in Dallas for each month the year, you can add another data series which contained the sales in Miami for the year. To define a new data series to add to the existing range of data, you create a new Series object and...

## Pivot Tables Collection

You can use another method to create a PivotTable from a PivotCache, using the Add method of the PivotTables collection. If you have already created a PivotCache in your workbook and you want to create a second PivotTable, you can use the following code Dim pvc As PivotCache Dim pvt As PivotTable Set pvc ActiveWorkbook.PivotCaches l 'Add new PivotTable to PivotTables collection There is no particular advantage to using this method compared with the CreatePivotTable method. It's just another...

## Constants Structures Handles and Classes

Most of the API functions include arguments that accept a limited set of predefined constants. For example, to get information about the operating system's capabilities, you can use the GetSystemMetrics function Declare Function GetSystemMetrics Lib user32 _ ByVal nIndex As Long As Long The value that you pass in the nIndex argument tells the function which metric you want to be given, and must be one of a specific set of constants that the function knows about. The applicable constants are...

## Text Tools The Anatomy of a Utility

In this section, I describe an Excel utility that I developed and that is part of my Power Utility Pak add-in . The Text Tools utility enables the user to manipulate text in a selected range of cells. Specifically, this utility enables the user to do the following Change the case of the text uppercase, lowercase, proper case, sentence case, or toggle case . Add characters to the text at the beginning, to the end, or at a specific character position . Remove characters from the text from the...

## Declaring a Sub procedure

A procedure declared with the Sub keyword must adhere to the following syntax Private Public Static Sub name arglist instructions Exit Sub instructions End Sub Private Optional Indicates that the procedure is accessible only to other procedures in the same module. Public Optional Indicates that the procedure is accessible to all other procedures in all other modules in the workbook. If used in a module that contains an Option Private Module statement, the procedure is not available outside the...

## Counting visible cells in a range

The CountVisible function that follows accepts a range argument and returns the number of nonempty visible cells in the range. A cell is not visible if it's in a hidden row or a hidden column. Function CountVisible rng ' Counts visible cells Dim CellCount As Long Dim cell As Range Application.Volatile CellCount 0 Set rng Intersect rng.Parent.UsedRange, rng For Each cell In rng Not cell.EntireColumn.Hidden Then _ CellCount CellCount 1 End If Next cell CountVisible CellCount End Function This...

## Selecting cells by value

The example in this section demonstrates how to select cells based on their value. Oddly enough, Excel does not provide a direct way to perform this operation. My selectByValue procedure follows. In this example, the code selects cells that contain a negative value, but this can be changed easily. Dim Cell As Object Dim FoundCells As Range Dim WorkRange As Range If TypeName Selection lt gt Range Then Exit Sub Set WorkRange ActiveSheet.UsedRange Set WorkRange Application.Intersect Selection,...

## Modeless Dialog

Most dialog boxes that you encounter are modal dialog boxes, which must be dismissed from the screen before the user can do anything with the underlying application. Some dialogs, however, are modeless, which means the user can continue to work in the application while the dialog box is displayed. To display a modeless UserForm, use a statement such as The word vbModeless is a built-in constant that has a value of 0. Therefore, the following statement works identically Figure 15-1 shows a...

## Presenting data in a pleasing format

Most programs can use the default formatting that VBA provides for output. However, you might need some way to present the data in another way, such as a short or long date 04 24 06 or 24 April 2006 . It's important to know how to format data so that you can create astounding reports and concise analysis. The Format function is the most common way to change the appearance of data. This function accepts any valid expression as input. You can also supply an expression that defines how to format...

## VariabLEs Data Types and Constants

Since this book focuses on a spreadsheet application, it's only natural that I introduce variables by asking you to think about the following what types of values can be entered into a spreadsheet cell and how you might use them You know that you can enter numbers and text in any spreadsheet cell in Excel. Also, you may or may not know that the format of a spreadsheet cell can be changed to one of several possibilities. For example, a number can be formatted such that the value is displayed...

## Determining the last nonempty cell in a column or row

In this section, I present two useful functions LastInColumn returns the contents of the last non-empty cell in a column LastInRow returns the contents of the last non-empty cell in a row. Each function accepts a range as its single argument. The range argument can be a complete column for LastInColumn or a complete row for LastInRow . If the supplied argument is not a complete column or row, the function uses the column or row of the upper-left cell in the range. For example, the following...

## Dynamic arrays

A dynamic array doesn't have a preset number of elements. Declare a dynamic array with a blank set of parentheses Before you can use this array, you must use the ReDim statement to tell VBA how many elements the array has. Usually, the number of elements in the array is determined while your code is running. You can use the ReDim statement any number of times, changing the array's size as often as you need. The following example demonstrates how to change the...

## Relative or absolute

When recording your actions, Excel normally records absolute references to cells. In other words, when you select a cell, it will remember that exact cell not the cell relative to the current active cell . To demonstrate how this works, perform these steps and examine the code 1. Activate a worksheet and start the macro recorder. 4. Move to cell C1 and enter Feb. 5. Continue this process until you've entered the first six months of the year in B1 G1. 6. Click cell B1 to activate it again. 7....

## Drive Object

DriveLetter Property DriveType Property FileSystem Property IsReady Property Path Property RootFolder Property ShareName Property TotalSize Property Returns a variant representing the available space on the drive in bytes Returns a string containing the drive letter Returns a DriveTypeConst specifying the type of drive Returns a string containing an abbreviation for the filesystem type i.e., FAT Returns a variant containing the free space on the drive in bytes Returns True if the specified...

## Worksheet Events

The following worksheet event procedures are available in the code module behind each worksheet Private Sub Worksheet_Activate Private Sub Worksheet_BeforeDoubleClick ByVal Target As Range, Cancel As Boolean Private Sub Worksheet_BeforeRightClick ByVal Target As Range, Cancel As Private Sub Worksheet_BeforeRightClick ByVal Target As Range, Cancel As _FollowHyperlink ByVal Target As Hyperlink _PivotTableUpdate ByVal Target As PivotTable _SelectionChange ByVal Target As Range You should use the...

## Comment Properties

Returns the name of the person who created the comment Read-only. Returns the comment box as a Shape object, allowing manipulation of the comment box Set Get whether the comment is visible all the time True or only when the user hovers over the cell containing the comment

## Understanding object parents

As you know, Excel's object model is a hierarchy Objects are contained in other objects. At the top of the hierarchy is the Application object. Excel contains other objects, and these objects contain other objects, and so on. The following hierarchy depicts how a Range object fits into this scheme Application object Workbook object Worksheet object Range object In the lingo of object-oriented programming, a Range object's parent is the Worksheet object that contains it. A Worksheet object's...