Professional Excel Templates

Vertex42 The Excel Nexus

Vertex42 is a system that gives you a way to get all of the professional spreadsheet templates that you need for your home business. They not simply limited to Microsoft Excel spreadsheets either; they can provide high-quality spreadsheets for Google Sheets and OpenOffice as well. No matter what you need your spreadsheets for, they can deliver what you need in a great timely manner. You can get amazing tools for Debt Reduction and Money Management; there is no need to create those spreadsheets yourself when you can get them done by Vertex42 professionally! You can also get useful resume tools, letters, or bills of sale. Creating a useful and professional-looking spreadsheet is often a very difficult task as it can be time-consuming to get them done well. Let us help you can get quality spreadsheets today! Read more here...

Vertex42 The Excel Nexus Summary


4.8 stars out of 16 votes

Contents: Excel Templates
Official Website:
Price: $30.00

Access Now

My Vertex42 The Excel Nexus Review

Highly Recommended

Some users might complain that the default interface is more complicated than it needs to be. If you just panicked grab a quick drink and relax because this baby has a full customizable interface.

However, Vertex42 The Excel Nexus is a fairly good program considering the standard and depth of the material it provides. In addition to being effective and its great ease of use, this software makes worth every penny of its price.

Using Transfer Spreadsheet to Create a New Worksheet

If you prefer not to use the CopyFromRecordset method, you can also use the TransferSpreadsheet method from the DoCmd object. There are a few distinct advantages to the TransferSpreadsheet method. One advantage is that you can export an entire table to a spreadsheet with one simple command. For example 'Use Transfer Spreadsheet to create an Excel Spreadsheet This code is all you need to export the Skids table to a spreadsheet called skids.xls to the C drive. This method enables you to export both tables and queries stored in your database. Another advantage to the method is that you don't actually invoke the Excel object model, which requires more code and more overhead, as Excel is loaded into memory. A noticeable side effect of using the TransferSpreadsheet method is that if you already have a file called skids.xls in the specified location, with the same sheet name and named data range, the preceding code will fail silently. The code runs, but the existing spreadsheet is not...

Embedded Chart Events

In the Code window of the class module, declare an object variable that will represent the events generated by the Chart object ' connect the class module and its objects with the Chart object Set myChart.xlChart _ 12. Activate the Microsoft Excel application window and click the embedded chart. At this time, the xlChart_Activate event procedure that you entered in step 7 should be triggered. 13. You can now enter in the class module additional event procedures for the embedded chart.

Step Creating a Timesheet Spreadsheet

Input Choice And Create Vba

Now is the time to set up the user interface for your web application (Figure 17-34). Because most users are familiar with the spreadsheet format, they will welcome the idea of keeping track of their time in a worksheet, especially if you make it quick and easy for them to use. Place the formulas that calculate week-ending dates, as shown in Figure 17-35. Notice that these formulas are out of view when the spreadsheet is first presented to the user. The formulas assume that Sunday is the last day of the week. 3. Use the Define Name dialog box (Figure 17-38) to assign the following names to spreadsheet cells Use this dialog box to define names in the Timesheet spreadsheet. Use this dialog box to define names in the Timesheet spreadsheet.

Retrieving Data with the Transfer Spreadsheet Method

It is possible to use the TransferSpreadsheet action to import or export data between the current Microsoft Access database (.mdb) or Access project (.adp) and a spreadsheet file. You can also link the data in a Microsoft Excel spreadsheet to the current Microsoft Access database. With a linked spreadsheet, you can view and edit the spreadsheet data with Microsoft Access while still allowing complete access to the data from your Microsoft Excel spreadsheet program. DoCmd.TransferSpreadsheet transfertype , spreadsheettype , _ tablename, filename , hasfieldnames , range The spreadsheettype argument can be one of the following constants It is not difficult to guess that the spreadsheettype argument specifies the spreadsheet name and the version number. The tablename argument is a string expression that specifies the name of the Microsoft Access table you want to import spreadsheet data into, export spreadsheet data from, or link spreadsheet data to. Instead of the table name, you may...

What Is an Excel Application

First of all, I should define what I mean by an Excel application. By Excel application, I mean a spreadsheet that you have developed using Excel and VBA for use by yourself or other users. VBA stands for Visual Basic for Applications, which is essentially Visual Basic that has been modified to run within other host applications such as Excel or Microsoft Word. Excel applications contain VBA code that may perform one or more of the following tasks Automate tedious processes. Many times, once you create a useful spreadsheet, in order to maintain it, you need to perform a set of tasks in a specific order on a frequent basis. Often, you can automate such simple tasks using Excel's macro recorder. For more complex tasks you need to write VBA code. Enhance the user experience. The experience and comfort level of people using Excel varies widely. One way to spread the wealth of a useful spreadsheet is to incorporate a user interface into the workbook that can serve to guide the less...

Creating Excel Applications for Others

Developing spreadsheets for yourself and for other people Knowing what makes a good spreadsheet application Using guidelines for developing applications for others xcel programmers develop two basic types of spreadsheets spreadsheets that only they use and spreadsheets that other people use. This distinction often determines how much effort you need to put into creating a spreadsheet. Usually, developing spreadsheets for your use is much easier than developing spreadsheets that others will use. In this chapter, I provide general guidelines for developing spreadsheets for someone other than yourself. But even if you're the only person who uses your spreadsheet creations, you might discover some helpful hints.

Creating an Embedded Chart

Refedit Excel

To add an embedded chart to a worksheet, use the Add() method of the ChartObects collection object. The AddEmbeddedChart() sub procedure creates the same column chart as the AddChartSheet() sub procedure listed in the previous section however, it embeds the chart on an existing worksheet named Embedded Charts. When adding an embedded chart, the Add() method of the ChartObjects collection object accepts four parameters that define the position of the upper-left corner of the chart on the worksheet, as well as the chart width and height. The position properties of the Add() method (Left and Top) are relative to the upper-left corner of cell A1 and are in units of points. The Activate method of the ChartObject object is equivalent to selecting the chart because only one Chart object is contained in a ChartObject object. Before setting the properties of the Chart object, the chart must contain at least one Series object. Thus, the NewSeries method is used to add an empty Series object to...

Creating a Chart Sheet

Chart Haslegend

The sub procedure AddChartSheet() creates a new chart sheet and a column chart of sample data selected from a worksheet by the user. The worksheet range that contains the data is selected via a custom dialog box using methods discussed in Chapter 6. The Add() method of the Charts collection object is used to create a column chart on a new chart sheet. Remember, the Charts collection object represents a collection of chart sheets in a workbook (refer to Table 9.1). After the chart sheet is added, the chart it contains is automatically active because it is the only component of the sheet. Next, a With End With structure is used to modify the properties of the Chart object. Many of these subordinate objects and properties have common sense names, so their function is intuitive. Use Excel worksheet function to set the maximum scale on the value axis. The rest of the objects and properties set via the Axis object are fairly straightforward and include setting tick marks and chart labels....

Whats a Spreadsheet Application

Excel programming is essentially the process of building applications that use a spreadsheet rather than a traditional programming language. In many cases, people other than the application developer use these applications. My working definition of a spreadsheet application is this A spreadsheet file (or group of related files) designed so that someone other than the developer can perform useful work without extensive training. Based on this definition, most of the spreadsheet files you've developed probably don't qualify as spreadsheet applications. You may have hundreds of spreadsheet files on your hard drive, but you probably didn't design most of them so that others can use them. Like witches, there are good spreadsheet applications and bad spreadsheet applications. How can you tell them apart A good spreadsheet Provides an appropriate solution to a problem. The optimal approach for solving a problem doesn't always involve designing an application that works in a spreadsheet...

Adding an Embedded Chart Using VBA Code

'Create new embedded chart AddChart aligns the Shape with F3 M19 by assigning the Top, Left, Width, and Height property values of the range to the same properties of the Shape, and then applies the name MangoesChart to the Shape. The Chart property of the Shape object is then used to return a reference to the embedded chart and the properties set, as you have seen previously.

Example Printing Embedded Charts

We can now implement the PrintCharts feature of our SRXUtils application. This is designed to provide a list of the embedded charts in the active workbook, so the user can select from this list and print the selected charts. (To print a chart sheet, use the PrintSheets utility.) The list box contains a list of all embedded charts in the active workbook. The user can select one or more charts and hit the Print button. The following are the steps to create the print utility. All the action takes place in the Print.xls workbook, so open this workbook. When the changes are finished, you will need to save Print.xls as Print.utl as well. If Print.utl is loaded, the only way to unload it is to unload the add-in SRXUlils.xla (if it is loaded) and close the workbook SRXUtils.xls (if it is open).

The Spreadsheet Web Component

Because you already know how to use a Microsoft Excel Workbook object, you will find it extremely easy to work with the Spreadsheet component. This component is very useful for displaying formatted data, performing calculations, or simply providing your users with a quick way to enter, calculate, and store data. The current XP version of the Spreadsheet component supports multiple worksheets containing 262,144 rows and 18,278 columns (ZZZ) each. Creating business solutions that utilize the Spreadsheet component is not complicated because the component uses Excel properties, methods, and events that you are already familiar with. The best way to learn about the Spreadsheet and the other three Office web components is, of course, by example. Our example for the Spreadsheet component is very simple. You will create an HTML form with the embedded Spreadsheet component. This form will have three buttons (see Figure D-2) for performing different operations, such as renaming the active...

Linking a Microsoft Excel Spreadsheet

You can link an Excel spreadsheet to a Microsoft Access database by using the TransferSpreadsheet method of the DoCmd object, as shown in Hands-On 11-9. Note, however, that neither the DoCmd object nor its Transfer-Spreadsheet method are members of the ADO Object Model. The DoCmd object is built into the Microsoft Access library. Hands-On 11-9 Linking an Excel Spreadsheet This hands-on uses the Regions.xls spreadsheet file provided in the book's downloadable files. You can revise the procedure to use any spreadsheet file that you have available. The Link_ExcelSheet procedure begins by creating a linked table named mySheet from the specified range of cells (A1 B15), located in the Regions worksheet in the Regions.xls file. The -1 argument in the DoCmd statement indicates that the first row of the spreadsheet contains column headings. Next, the procedure uses the ADO Recordset object to retrieve the data from the mySheet table into the Immediate window. Notice that prior to opening the...

Initializing the Chart Sheet

The public sub procedure Main() is triggered from the form button on the chart sheet and contains calls to the initialization procedures for the chart sheet, then scans the chart for score sequences. Screen updating is initially turned off otherwise Excel will update the screen as images are added or removed from the chart. Screen updating is turned back on so that the user can see the chart before it is scanned for score sequences. Note that the ChartTitle object is used to display help messages to the user telling them how to play the game. The ChartTitle object is accessed via the ChartTitle property of the Chart object, which in turn is returned from the Sheets property of the Application object. I added the title to the bottom of the chart when initially formatting it. 'Initialize Alienated chart sheet. The AddSeries() sub procedure is also called from Main() and its purpose is to add the data to the chart. Since the data remains static, I can add it programmatically using...

Enabling events for an embedded chart

As I note in the preceding section, Chart events are automatically enabled for chart sheets but not for charts embedded in a worksheet. To use events with an embedded chart, you need to perform the following steps. DECLARE A PUBLIC CHART OBJECT Before your event handler procedures will run, you must connect the declared object in the class module with your embedded chart. You do this by declaring an object of type clsChart (or whatever your class module is named). This should be a module-level object variable, declared in a regular VBA module (not in the class module). Here's an example After the preceding statement is executed, the clsChart object in the class module points to the first embedded chart on the active sheet. Consequently, the event handler procedures in the class module will execute when the events occur. The following example is a simple event handler procedure that is executed when the embedded chart is activated. This procedure simply pops up a message box that...

Creating Chart Sheets

The Workbook object has a Charts property that returns the Charts collection of all chart sheets in the workbook. We can use the Add method of the Charts collection to create and add a new chartsheet to the workbook. As usual, this method returns a Chart object. The Before parameter specifies the sheet before which the new sheet is added, and the After parameter specifies the sheet after which the new sheet is added. Only one of these parameters can be specified at one time or an error will result. If neither is set, the new chart is inserted before the active sheet. For example, the following code creates a new chart sheet named Sales The Add method cannot be used to format the chart. As mentioned earlier, this must be done using the various properties of the Chart object or the ChartWizard method, discussed later in the chapter.

Creating workbook templates

The book.xltx and sheet.xltx templates discussed in the preceding section are two special types of templates that determine default settings for new workbooks and new worksheets. This section discusses other types of templates, referred to as workbook templates, which are simply workbooks that you set up as the basis for new workbooks or worksheets. Why use a workbook template The simple answer is that it saves you from repeating work. Assume that you create a monthly sales report that consists of your company's sales by region, plus several summary calculations and charts. You can create a template file that consists of everything except the input values. Then, when it's time to create your report, you can open a workbook based on the template, fill in the blanks, and be finished. When you create a workbook that is based on a template, the default workbook name is the template name with a number appended. For example, if you create a new workbook based on a template named Sales...

The Chart object model

When you first start exploring the object model for a Chart object, you'll probably be very confused -which is not surprising the object model is very confusing. It's also very deep. For example, assume that you want to change the title displayed in an embedded chart. The top-level object, of course, is the Application object (Excel). The Application object contains a Workbook object, and the Workbook object contains a Worksheet object. The Worksheet object contains a ChartObject object, which contains a Chart object. The Chart object has a ChartTitle object, and the ChartTitle object has a Text property that stores the text that's displayed as the chart's title. Here's another way to look at this hierarchy for an embedded chart This statement assumes the active workbook as the Workbook object. The statement works with the first item in the ChartObjects collection on the worksheet named Sheet1. The Chart property returns the actual Chart object, and the ChartTitle property returns the...

Creating an Embedded Chart from Microsoft Access Data

Using VBA, you can easily create a chart based on the data retrieved from a Microsoft Access database. The ChartData procedure shown below uses the data fetched from the Microsoft Access Northwind database to create an embedded chart. The chart is created by using the Add method of the Charts collection. The source of the chart data is provided by the Range object. The CurrentRegion method returns all the non-blank cells surrounding cell A1. The remaining part of the procedure formats the chart by setting various properties. The chart code fragment has been recorded in a separate macro and then pasted into the VBA procedure with modifications made to the settings of some of the properties. You can create an embedded chart

Using Excel Application Functions in VBA

Now that you know how to write functions in VBA and make them available to your spreadsheets, you are also aware that you can re-create any function already available in the Excel application. Although recreating Excel's functions would be a good way to improve your VBA programming skills, it's certainly not a practical use of your time. Why reinvent what's already been created for you It would be nice if you could use Excel's application functions in your VBA code, as they are mostly complimentary, not repetitive, to VBA's set of functions. That way, if you need a specific function performed in your program that is not already included with VBA, you don't have to write it yourself. Well, there is a method to use the Excel application functions, of course, and it is really quite simple. Replacing the call to the PowerDB() function in the TestPower() sub procedure shown earlier with the line of code above will give the exact same result. The difference is that this code uses Excel's...

Using Excel Templates to Create Formatted Worksheets Filled with Access Data

If you want to produce a more formatted worksheet, you can prepare an Excel template and format it as needed for example, adding a large, centered title and column headings with appropriate text, perhaps in a larger or bolder font than the data area. Then, instead of using the Excel command on the Ribbon, use VBA code to export the Access data row by row to the data area of a new worksheet created from the template. I created a set of queries for archiving data, again using the sample Northwind data, and a dialog form (fdlgArchiveOrders) that allows the user to select a date range for archiving Orders data, as shown in Figure 3.5. Once the start date and end date have been entered or selected, clicking the Archive button runs a procedure that creates a new Excel worksheet from a template (Orders Archive.xltx) in the same folder as the database, fills it with data from tblOrders in the selected date range, and deletes the archived records. strPrompt Excel template 'Orders Archive.xlt'...

Magic Squares Spreadsheet Selectionchange Procedure

Picture property, 13, 79, 241, 317 Pie chart, 384 PieClock.xls workbook, 388 PlaceComputerShips() sub procedure, 193, 196 PlaceWord sub procedure, 317, 318, 322 PlayerFire() sub procedure, 191, 192, 197 PlayWav() sub procedure, 204, 246 Point object, 394, 395, 406, 420, 422 Points, 228 Rows, in spreadsheets, 109 Rows property, 169, 248 of Range object, 195 Run Macro, 11 Run time, 9 Select() event procedure, of Chart object, 394, 395, 396, 399, 402, 420 SelectionChange() event procedure, 56, 169, 181 of Magic Squares spreadsheet, 34-35 of Worksheet object, 188, 189, 190, 191 Selection property, of Window object, 174 SelectionValid() function, 318, 319, 321 SelectLines() procedure, 434, 435 SetTargetCell() function procedure, 197 ShapeDemos.xls workbook, 432, 433 Shape objects, 428-432, 442, 471 activating, 435 shuffling deck for Blackjack program and, 249 Spaces, removing in strings, 108 Spaghetti code, 271 SpecialEffect property, 211, 299 Splitting, in Blackjack game, 267 Spreadsheet...

Properties and Methods of the Chart Object

The 91 properties and methods of the Chart object are shown in Table 21-7. Table 21-7. Members of the Chart Object Table 21-7. Members of the Chart Object Table 21-8 shows the members of the Chart object that return children of the Chart object, along with the objects that they return. Note that several members can return a single object or a collection of objects. Table 21-8 shows the members of the Chart object that return children of the Chart object, along with the objects that they return. Note that several members can return a single object or a collection of objects. Let us discuss a few of the members of the Chart object. (We have encountered many of these members in connection with other chart-related objects.)

Chart Objects and Chart Object Objects

As you probably know, Excel charts can reside in a special type of sheet called a chart sheet or they can be embedded in an ordinary worksheet. Accordingly, a Chart object can represent a chart sheet (standalone chart) or an embedded chart. In the latter case, the Chart object is not contained directly in a worksheet. Rather, the worksheet contains a ChartObject object that acts as a container for the Chart object. Note the space between the word Chart and the number 1 in the name of the ChartObject object, but not in the name of the Chart object. We emphasize that there is no ChartSheet object. The Charts property of the Application object returns a so-called Sheets collection containing one Chart object for each chart sheet. It does not contain Chart objects for the embedded charts.

Storing multiple charts on a chart sheet

Word Graph Example

Most Excel users who take the time to think about it would agree that a chart sheet holds a single chart. Most of the time, that's a true statement. However, it's certainly possible to store multiple charts on a single chart sheet. In fact, Excel lets you do this directly. If you activate an embedded chart and then choose Chart Location, Excel displays its Chart Location dialog box. If you select the As New Sheet option and specify an existing chart sheet as the location, the chart will appear on top of the chart in the chart sheet. Most of the time, you want to add embedded charts to an empty chart sheet. To create an empty chart sheet, select a single blank cell and press F11. Figure 18-24 shows an example of a chart sheet that contains six embedded charts. Figure 18-24 This chart sheet contains six embedded charts. Figure 18-24 This chart sheet contains six embedded charts.

Creating Embedded Charts

The Worksheet object also has a ChartObjects property that returns a ChartObjects collection, which is the collection of all ChartObjects in the worksheet. As we have mentioned, a ChartObject object is a container for a Chart object that is, an embedded chart. The ChartObjects collection has an Add method that is used to create a new embedded chart. The syntax is Note that the Add method returns a ChartObject object, rather than a Chart object. This is a bit confusing, since the method creates both a ChartObject object and the contained Chart object. The code in Example 21-1 creates a new ChartObject object called ExampleChart along with its contained Chart object. It positions the chart so that its upper-left corner is three columns from the Example 21-1. Creating an Embedded Chart ' Create an embedded chart Figure 21-4. The results of creating an embedded chart object You may have noticed that the chart in Figure 21-4 is a trifle uninteresting. We will need to use the various...

Spreadsheet File Formats Supported

Although Excel's default file format is an XLS workbook file, it can also open and save a wide variety of files generated by several other applications. An important consideration is whether a particular file type can survive a round trip. In other words, do you lose any information if you save a file in a particular format and then reopen it in the same application As you might expect, using Excel's native file format (XLS files) ensures that you'll lose absolutely nothing as long as you use the latest version of XLS.

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 'Create new chart sheet Set cht Charts.Add

Creating an Interop Excel Application

To create the sample Excel application, which calculates the distance between two points in two-dimensional space, you will use a user defined function written in VBA. 2. Create the spreadsheet shown in Figure 12.8. You now need to create a VSTO project that uses the CalculateDistance.xls file that you built earlier. Create a new Excel Workbook project. In the Create New Project Wizard, select Copy An Existing Document. Browse to and select CalculateDistance.xls.

Short Survey of Excel Applications

I have seen or developed Excel applications that are used for a diverse range of activities. Some of the uses that I have seen or developed include these Sales commission models A financial services company developed an Excel application that used detailed sales data to determine the commission earned by each sales representative.

Opening a Microsoft Excel Spreadsheet

You can open external data sources supported by the Microsoft Jet database engine by using ADO and the Microsoft Jet 4.0 OLE DB provider. Use the Extended Properties of the Connection object to pass the connection string. Hands-On 10-6 demonstrates how to open a Microsoft Excel spreadsheet named C Report.xls by using ADO. Hands-On 10-6 Opening an Excel Spreadsheet with ADO This hands-on uses the Report.xls spreadsheet file included in the book's downloadable files. You can modify the procedure to use any other spreadsheet file. Dim conn As ADODB.Connection Set conn New ADODB.Connection conn.Open Provider Microsoft.Jet.OLEDB.4.0 & _ Data Source & CurrentProject.Path & _ Report.xls & _ Extended Properties Excel 8.0 MsgBox Excel spreadsheet was opened. conn.Close Set conn Nothing MsgBox Excel spreadsheet was closed. End Sub

Using Excel Worksheet Functions

The Change The Function Argument

You can add almost all of the Excel worksheet functions to your VBA code. VBA provides a very limited number of built-in functions. By using the various functions available within Excel, you can add functionality that is not available with the existing VBA functions. For example, Excel provides several different financial functions that you can use within your macros. To place an Excel worksheet function in your VBA subroutine or function, you use the WorksheetFunction property along with the name of the function. One of the properties available for the Application object, the WorksheetFunction property is part of the Excel Object Model that VBA uses to access features of Excel. The Application object refers to the actual Excel program. The WorksheetFunction object stores all of the Excel Worksheet functions. To access one of the functions in the WorksheetFunction object, you use the WorksheetFunction property and precede the name of the function with the statement Application....

Querying Microsoft Excel Workbooks

When using ADO to access data from Excel 2007 workbooks, you use the same OLE DB provider that you used earlier in this chapter to access data from Microsoft Access 2007. In addition to Access, this provider also supports most ISAM data sources (data sources that are laid out in a tabular, row and column format). You will use the Sales.xlsx workbook, shown in Figure 20-7, as the data source for the Excel examples. 15 Sales.xlsx When using ADO to work with Excel, the workbook file takes the place of the database, while worksheets within the workbook, as well as named ranges, serve as tables. Compare a connection string used to connect to an Access database with a connection string used to connect to an Excel workbook. Connection string to an Excel workbook sConnect Provider Microsoft.ACE.OLEDB.12.0 & _ Data Source C Files Sales.xlsx & _ Extended Properties Excel 12.0 Note that the same provider is used, and that the full path and filename of the Excel workbook takes the place of the...

Embedded Charts Versus Chart Sheets

This separate evolution of charts has made it necessary for us to deal with two separate object models for charts. When a chart is on its own stand-alone ChartSheet, then we are dealing with a Chart object. When a chart is embedded in a worksheet, then we have to deal with a ChartObject object.

Formatting Excel Worksheets in VBA Code

If you need to sort, group, indent, or otherwise format exported data in an Excel worksheet, or create a total under the last row of data, you can write VBA code to use Excel commands to do the work in code. You can apply formatting to a worksheet created by the TransferSpreadsheet method, or one created from the Ribbon command, or a worksheet created programmatically from a template. strPrompt Excel template 'Northwind Orders.xlt' _ & not found in & strDBPath & & vbCrLf _ & please put template in this folder and try again MsgBox strPrompt, vbCritical + vbOKOnly, strTitle GoTo ErrorHandlerExit Else Debug.Print Excel template used & strTemplateFile End If wks.Range( A1M).Value strSheetName strSaveName strDBPath & strSheetName & .xlsx Debug.Print Time sheet save name & strSaveName

Embedded Charts in a Chart Object Container

The ChartObject object represents the container for an embedded chart. The purpose of having this container is to enable you to specify its size (height, width) and location (top, left) on the worksheet. These properties apply to all objects (such as pictures or autoshapes) that can be embedded on a chart. To illustrate, go to any worksheet with an embedded chart. With the Ctrl or Shift key depressed, click the chart. The eight resize handles appear white as shown in Figure 10.1. You have now selected the chart object. The Name box to the left of the formula bar shows Chart 1 (or whatever the name of the chart is). You can rename the embedded chart object by entering another name, say MyChart, in the Name box. Subsequently, you can use this name in your VBA code to refer to the embedded chart object. For example

Validation with a Spreadsheet Cell

In older versions of Excel, validation of spreadsheet content meant writing a lot of code to ensure the data was of proper type and or format. With the latest versions of Excel, this is no longer the case. Data validation is now included in the Excel application, so you don't necessarily have to write any code. Figure 4.2 to shows the data validation dialog box (select Data, Validation from the Excel application menu). Use this tool in your spreadsheets to force validation of data entered by the user. If your project creates new worksheets that require data validation, you can use the record macro tool discussed later in this chapter to learn how to add it to your program.

Chart Object and the Charts Collection

The Charts collection holds the collection of chart sheets in a workbook. The Workbook object is always the parent of the Charts collection. The Charts collection only holds the chart sheets. Individual charts can also be embedded in worksheets and dialog sheets. The Chart objects in the Charts collection can be accessed using the Item property. The name of the chart can be specified either as a parameter to the Item property's parameter or an index number describing the position of the chart in the workbook (from left to right). The Chart object allows access to all of the attributes of a specific chart in Excel. This includes chart formatting, chart types, and other charting properties. The Chart object also exposes events that can be used programmatically.

Importing Spreadsheet Files

If you have old Lotus 1-2-3 spreadsheet files, you can import data from them into Access tables using the TransferSpreadsheet method, which works much like the TransferText method, importing all the data from a worksheet. Unlike database files, you can use named argument values from the AcSpreadSheetType enum for spreadsheets of various versions these values are listed in Table 10.2. SpreadsheetType Named Constants for Lotus 1-2-3 SpreadsheetType Named Constants When you inspect new job data imported from a Lotus spreadsheet (as shown in Figure 10.22), the Job Date Time field looks strange, because the field is created as a Number field. However, when you click the Save Jobs to Table button, the CDate() function in the qappNewJobs append query converts the numeric date value into the correct format before appending the data to tblJobs. This type of tweaking of imported data is often required, to ensure that the data arriving in the target Access table is the correct data type. Numeric...

Writing Code in an Excel Workbook

In this section, you will create a new Excel workbook and add code that contains errors. (The user interface might differ depending on your Visual Studio development settings. The examples in this book assume that you have chosen the Visual Basic development setting.) 5. Select an Excel workbook in the Templates pane, leaving the default name (ExcelWorkbookl if this is your first Excel project), and then click OK to create a new workbook project.

The audience for spreadsheet applications

The remaining segments in the preceding table comprise spreadsheet end users, whom you can think of as the consumers of spreadsheet applications. When you develop a spreadsheet application for others to use, you need to know which of these groups of people will actually be using your application. Users with little experience and no interest in learning more about spreadsheets make up a large percentage of all spreadsheet users, probably the largest group of all. These are the people who need to use a spreadsheet for their jobs but who view the spreadsheet simply as a means to an end. Typically, they know very little about computers and software, and they usually have no interest in learning anything more than what's required to get their work done. They might even feel a bit intimidated by computers. Often, these users don't even know which version of Excel they use, and they are largely unfamiliar with what it can do. Obviously, applications developed for this group must be...

The Chart Sheet Interface

Refedit Excel

This chapter teaches you how to program Excel's Chart object so the interface for the project is built from a chart. Specifically, a chart sheet consisting of a bubble chart will serve to display the images. The requirements state that the game must involve a 10 by 10 grid of 100 images. To satisfy this requirement I will create the chart from ten data sets consisting of ten x,y-value pairs. The data is charted as ten different series in the chart. Each data set must use the same set of values for the x-axis variable to ensure vertical alignment of the images (for example, if x 2 for one element in each series, then their corresponding data markers are vertically aligned across the y-axis). In addition, the values for the x-axis variable must have a uniform increment for homogeneous spacing of the images. To ensure the images are aligned horizontally the y-values must be equivalent within a data series (for example, if y 2 for every element in a series, then the corresponding data...

Spreadsheet Applications

For the purposes of this book, a spreadsheet application is a spreadsheet file (or group of related files) that is designed so that someone other than the developer can perform useful work without extensive training. According to this definition, most of the spreadsheet files that you've developed probably don't qualify as spreadsheet applications. You may have dozens or hundreds of spreadsheet files on your hard drive, but it's a safe bet that most of them aren't really designed for others to use. A good spreadsheet application has the following characteristics It provides the appropriate solution to the problem. (A spreadsheet environment isn't always the optimal approach.) It should come as no surprise that it is possible to create spreadsheet applications for many different usage levels, ranging from simple fill-in-the-blank templates to extremely complex applications that use a custom interface and that may not even look like spreadsheets.

Checking Smart Tag Options in an Excel Workbook

Notice that both built-in and custom smart tags are listed as a group in the Recognizers section. Microsoft Excel 2002 comes with the MSN MoneyCentral smart tag that allows you to check the latest stock prices. So if you enter a financial symbol, such as MSFT, in a spreadsheet cell and

End user interface styles VBA Excel applications

Application Vba Excel Interface

The standard Excel worksheet interface may be suitable for those who are familiar with Excel, supported perhaps with comments to guide the user. For example, Figure 10.15 illustrates how comments can add explanation to the meaning of a column of data in a spreadsheet. In the fragment shown of the weeklysales worksheet of the SALESMAN workbook, the comment elaborates on the meaning of the sales_to_date range. Clearly, the designer of this type of interface will not require much knowledge of VBA. A complete Excel novice user might b e b est served by designing customised dialog boxes for data entry, or forms with other window controls as a front end, possibly rendering the worksheet invisible to the user. For example, in Figure 10.16, a dialog box is used to input data for adding a new representative into worksheet cells. The design of this system was looked at earlier in this chapter. For a novice user with no knowledge of spreadsheets this interface style would be better than using a...

Importing an Excel Spreadsheet to a Microsoft Access Database

In the previous section, you learned how to link your Excel spreadsheet to an Access database. Importing your spreadsheet data is just as easy. You can even use the same VBA procedure you used for linking with one minor change simply replace the acLink constant with aclmport, and you are done. The following procedure imports the spreadsheet presented in Figure 15-19 (see the previous section) into the Northwind database.

Using the Excel Macro Recorder

Record it using the Excel macro recorder Write it with VBA This chapter deals specifically with the ins and outs of using the Excel macro recorder. Recording a macro isn't always the best approach, and some macros simply can't be recorded, no matter how hard you try. You see, however, that the Excel macro recorder is very useful. Even if your recorded macro isn't quite what you want, the recorder is an excellent learning tool.

Linking an Excel Spreadsheet to a Microsoft Access Database

Example Micro Soft Access Work Sheet

You can link an Excel spreadsheet to a Microsoft Access database by using the TransferSpreadsheet method (refer to the Retrieving Data with the TransferSpreadsheet Method section in this chapter for the details on working with this method). The following example procedure links the spreadsheet shown in Figure 15-19 to the Northwind database. After opening the Access database with the OpenCurrentDatabase method, the procedure uses the TransferSpreadsheet method of the Microsoft Access DoCmd object to create a linked table named ExcelSheet from the specified range of cells (A1 D7) located in the mySheet worksheet in the Chap15.xls spreadsheet file. Notice that the -1 argument in the DoCmd statement indicates that the first row of the spreadsheet contains column headings. Next, the procedure opens the linked table in Edit mode, so the user can add or modify data. If you change back to Excel after adding one or more records, you'll notice that the changes made in the linked Access table...

Solving Problems with a Spreadsheet

I've covered the basic concept of a spreadsheet application, discussed the end users and developers of such applications, and even attempted to figure out why people use spreadsheets at all. Now it's time to take a look at the types of tasks that are appropriate for spreadsheet applications. You might already have a pretty good idea of the types of tasks for which you can use a spreadsheet. Traditionally, spreadsheet software has been used for numerical applications that are largely interactive in nature. Corporate budgets are an excellent example of this. After the model has been set up (that is, after formulas have been developed), working with a budget is simply a matter of plugging in amounts and observing the bottom-line totals. Often, budgeters simply need to allocate fixed resources among various activities and present the results in a reasonably attractive (or at least legible) format. A spreadsheet, of course, is ideal for this. Budget-type problems, however, probably account...

HandsOn From Access to Excel Loading an XML File into an Excel Workbook

' create a new Excel workbook Set wkb objExcel.Workbooks.Add ' make Excel application window visible objExcel.Visible True When the procedure is complete the Excel application window should be visible with the ExcelReport.xls workbook file displaying products retrieved from the XML file (see Figure 29-26). 4. Close the Excel workbook and exit Excel.

HandsOn Copying Records to an Excel Spreadsheet

' create a new Excel workbook Set wbk myExcel.Workbooks.Add ' make the Excel application window visible myExcel.Visible True ' quit the Excel application Figure 14-1 This Excel spreadsheet is created from Access data by running the procedure in Hands-On 14-5. Figure 14-1 This Excel spreadsheet is created from Access data by running the procedure in Hands-On 14-5.

Moving from Embedded to Chart Sheet and Vice Versa

The Location method is the equivalent of right-clicking a chart (embedded or in its own sheet) and selecting Location from the shortcut menu. The method is put to use when you want to change an embedded chart to a chart sheet or vice versa. When this code is run, it changes the embedded chart, Chart 1 on Sheet1, to a chart sheet named MyChart.

Copying Records to an Excel Spreadsheet

The procedure in Hands-On 14-5 uses automation to copy records from the Employees table to an Excel spreadsheet. Once the recordset is opened, the Excel part is handled by object variables that point to the Excel Application object (myExcel), Excel Workbook object (wkb), Excel Worksheet object (wks), and Excel Range object (StartRange). Before you can use any of these objects you must set a reference to the Microsoft Excel Object Library. The result of copying a recordset to a worksheet is shown following the procedure code.

Chapter Excel Applications

Simply put, we can define an Office application to be an Office document (for instance, an Access database, Excel workbook, Word document, Word template, or PowerPoint presentation) that contains some special customization. This customization usually takes the form of a combination of VBA procedures and menu and or toolbar customizations and is generally designed to simplify or automate certain tasks. It may provide utilities, which are programs for performing a specific task, such as printing or sorting. On the other hand, Office applications do not revolve around standalone executable files. Rather, they are created within an Office document. In particular, an Access application is created within an Access database, an Excel application is created within an Excel workbook, a Word application is created within a Word document, and a PowerPoint application is created within a PowerPoint presentation. Office applications can be created within Office templates or add-ins as well.

Transferring the Excel Spreadsheet to an Access Database

Many of the world's biggest databases began as spreadsheets. When the time comes to build a database application from your spreadsheet, you can resort to a tedious manual method to transfer the data, or you can use your new VBA programming skills to automatically turn your spreadsheets into database tables. Once in a database format, your Excel data can be used in advanced company-wide reports or as a stand-alone application (needless to say, the latter requires that you possess database application design skills). The remaining sections of this chapter demonstrate how to link and import Excel spreadsheets to an Access database. Prior to moving your Excel data to Access, you should clean up the data as much as possible so the transfer operation goes smoothly. Keep in mind that each spreadsheet row you'll be transferring will become a record in a table, and each column will function as a table field. For this reason, the first row of the spreadsheet range that you are planning to...

Classifying spreadsheet users

Over the years, I've found that it's often useful to classify people who use spreadsheets (including both developers and end users) along two dimensions their degree of experience with spreadsheets and their interest in learning about spreadsheets. To keep things simple, each of these two dimensions has three levels. These levels can be combined in nine combinations, which are shown in Table 5-1. In reality, only seven segments are worth thinking about because both moderately experienced and very experienced spreadsheet users generally have at least some interest in spreadsheets. (After all, that's what motivated them to get their experience.) Users who have a lot of spreadsheet experience and a low level of interest would make very bad developers. Table 5-1 CLASSIFICATION OF SPREADSHEET USERS BY EXPERIENCE AND INTEREST Open table as spreadsheet Table 5-1 CLASSIFICATION OF SPREADSHEET USERS BY EXPERIENCE AND INTEREST Open table as spreadsheet It should be clear that spreadsheet...

What Is a Spreadsheet Application

In this chapter, I attempt to clarify how people use spreadsheets in the real world. This is a topic that's germane to this entire book because it can help you determine how much effort you should devote to a particular development project. By the time you finish this chapter, you should have a pretty good idea of what I mean by a spreadsheet application. And after you've made it through the rest of the book, you'll be well on your way to developing your own spreadsheet applications with Excel. But first, let's get down to the basics. A working definition of a spreadsheet application The difference between a spreadsheet user and a spreadsheet developer A system for classifying spreadsheet users to help you conceptualize who the audience is for your applications A discussion of why people use spreadsheets A taxonomy of the basic types of spreadsheets You've probably been working with spreadsheets for several years, but chances are good that your primary focus has been on simply...

Spreadsheet newsgroups

The primary Usenet newsgroup for general spreadsheet users is comp.apps.spreadsheets This newsgroup is intended for users of any spreadsheet brand, but about 90 percent of the postings deal with Excel. My advice Skip this one and head directly for the Microsoft newsgroups.

Doing Things with Spreadsheet Ceils

When you are ready to write your own VBA procedure to automate a particular spreadsheet task, you will most likely begin searching for instructions that allow you to manipulate spreadsheet cells. You will need to know how to select cells, how to enter data in cells, how to assign range names, how to format cells, and how to move, copy, and delete cells. Although these tasks can be easily performed with the mouse or keyboard, mastering these techniques in Visual Basic for Applications requires a little practice.

Basic Spreadsheet Types

In this section, I classify spreadsheets into several basic types to provide a better perspective on how spreadsheet applications fit into the overall scheme of things. This is all quite arbitrary, of course, and is based solely on my own experience. Moreover, there is quite a bit of overlap between the categories, but they cover most of the spreadsheets that I've seen and developed. My names for these categories are as follows

Data storage and access spreadsheets

A large percentage of Excel workbooks consist of one or more database tables (sometimes known as lists). These are used to track just about anything you can think of. Most people find that it's much easier to view and manipulate data in a spreadsheet than it is using normal database software. If the tables are set up properly, they can be summarized with a pivot table.

And Spreadsheet Files

Since the earliest days of Access (when dBASE and Paradox were major forces in the database world, and Lotus 1-2-3 was the leading spreadsheet application) Access could import from or export to these formats. Some people are still using these programs, or at least have old files created by them in past years, so you still might need to import data from a dBASE, Paradox, or Lotus file or (though it's much less likely) export to one of those formats. Access still supports importing from these legacy formats, and you can also export to them, both in the interface and in VBA code.

Foryoureyesonly spreadsheets

As the name implies, no one except you - the creator - will ever see or use the spreadsheets that fall into this category. An example of this type might be a file in which you keep information relevant to your income taxes. You open the file whenever a check comes in the mail, you incur an expense that can be justified as business, you buy tax-deductible Girl Scout cookies, and so on. Another example is a spreadsheet that you use to keep track of your employees' time records (sick leave, vacation, and so on). Spreadsheets in this category differ from quick-and-dirty spreadsheets in that you use them more than once, so you save these spreadsheets to files. But, again, they're not worth spending a great deal of time on. You might apply some simple formatting, but that's about it. This type of spreadsheet also lacks any Spreadsheets in this category don't qualify as applications, although they sometimes increase in sophistication over time.

Quickanddirty spreadsheets

This is probably the most common type of spreadsheet. Most of the spreadsheets in this category are fairly small and are developed to quickly solve a problem or answer a question. Here's an example You're about to buy a new car, and you want to figure out your monthly payment for various loan amounts. Or perhaps you need to generate a chart that shows your company's sales by month, so you quickly enter 12 values and whip out a chart, which you paste into your word processor. In both of the preceding cases, you can probably input the entire model in a few minutes, and you certainly won't take the time to document your work. You probably won't even think of developing any macros or custom dialog boxes. In fact, you might not even deem these simple spreadsheets worthy of saving to disk. Obviously, spreadsheets in this category are not applications.


The TransferSpreadsheet method is very similar to the TransferDatabase method in that it enables you to import, link, and export, only in this case it deals with spreadsheets. The syntax is shown in the following code. DoCmd.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA) SpreadsheetType Type of spreadsheet. The default is acSpreadsheet TypeExcel8. See the help documentation for a complete list and explanation. Filename and path of your spreadsheet. Use True to use the first row of the spreadsheet as field names and False to treat the first row as data. False is the default. Valid range of cells or named range in the spreadsheet that you want to import from. Leave blank to import an entire spreadsheet. Using with Export will cause an error. Now we'll walk through an example of how you might use the TransferSpreadsheet method to export data to a spreadsheet. Suppose you want to export the contents of the Employees table you just...

Pivot Charts

Vba Chart Axis

The finished Pivot Chart is shown in Figure 1.31. Access 2007 reports have some interactivity, and PivotTables and PivotCharts have almost unlimited interactivity, but both have a serious limitation the interactivity is available only when you are working in the Access database when you send an Access report, PivotTable, or PivotChart to someone else who doesn't have Access, say as a PDF file, the recipient gets a read-only image of the report, PivotTable, or PivotChart, with no interactivity. This may be what you want in some cases but if you need to deliver data in a worksheet or chart format that users can interact with, you need to create an Excel worksheet or chart from your Access data, rather than a report, PivotTable, or PivotChart.

Embedded Charts

Because embedded charts do not create chart sheets, the chart events are not as readily available. You can make them available by adding a class module. The chart events are now available to the chart, as shown in Figure 8.6. They are accessed in the class module rather than on a chart sheet. These lines initialize the embedded chart to be recognized as a chart object. The procedure must be run once per session (use Workbook_Open to automate this). Embedded chart events are now available in the class module.

Primer in Excel VBA

This chapter is intended for those who are not familiar with Excel and the Excel macro recorder, or who are inexperienced with programming using the Visual Basic language. If you are already comfortable with navigating around the features provided by Excel, have used the macro recorder, and have a working knowledge of Visual Basic and the Visual Basic Editor, you might want to skip straight to Chapter 2. The Excel macro recorder Excel VBA is a programming application that allows you to use Visual Basic code to run the many features of the Excel package, thereby allowing you to customize your Excel applications. Units of VBA code are often referred to as macros. More formal terminology is covered in this chapter, but you will continue to see the term macro as a general way to refer to any VBA code. Don't get the impression that we are dismissing the macro recorder. The macro recorder is one of the most valuable tools available to VBA programmers. It is the fastest way to generate...

Chart Sheets

Chart events are automatically enabled with chart sheets. To catch events triggered by the user in a chart sheet, add code to an event procedure contained in the module associated with the chart sheet. The code window can be opened in the same manner as with a worksheet. Figure 9.10 shows the code window of a chart sheet selected from the project explorer. The active project displayed in Figure 9.10 is an Excel workbook containing several chart sheets. Chart sheet component module Chart sheets* Adding code to an event procedure of a chart sheet. Chart sheet component module Chart sheets* Adding code to an event procedure of a chart sheet. Unfortunately, some of the events unique to the Chart object cannot be used with a chart sheet because there is no manner in which the user can trigger them. For example, the user cannot drag and drop a range of cells over the chart when the data is in another worksheet however, the other chart events work as expected, and an example using the...

Excel Templates

The purpose of an Excel template is to provide a starting place for a new workbook, worksheet, chart, or code module. Creating a template is easy. We simply create a new workbook and save it as a template using the Save As command. It is very important to note that the data (and other things such as formatting) as well as macros are actually copied to the workbook, after which all connection between the template and the new workbook is severed. This is quite different from the way that Microsoft Word uses templates. A Word template remains attached to the document. Certain changes, such as the addition of styles or macros, can be saved either in the template or in the document itself, but Word never copies macros from a template into a document. Also, several templates can be opened at one time (the so-called global templates), each of which may affect the document. Word templates are dynamic Excel templates are static. This reduces the usefulness of Excel templates considerably, for...

Excel Vba Reference

Visual Basic Applications for Excel is a programming language well suited to beginners. It provides many of the Visual Basic programming facilities through the Excel application. Thus, students who have access to Excel can gain familiarity with Visual Basic without having to step up to the full blown program. With VBA, programmers have the power to customise Excel applications that would be impossible to achieve with Excel alone. VBA can often provide a faster, and sometimes easier-to-implement solution, than could be achieved with Excel alone. In particular, VBA gives you the power to automate all sorts of Excel tasks. For example, you could create an Excel workbook, add data to it and format it automatically using VBA. Excel is almost ubiquitous in the business world, and is amongst the most popular software applications ever used. These reasons make VBA for Excel an excellent choice as an introductory programming language. Chapter 10 is concerned with using forms in VBA programs,...

The Chart Object

A graphical representation of the Charts collection object and Chart object are shown in Figure 9.2. The figure shows the objects and collections that are subordinate to the Chart object. Many of these components also have numerous subordinate objects, so Figure 9.2 does not illustrate the breadth of the Chart object. You should not be intimidated, though, because programming the Chart object involves many of the same techniques that have been discussed throughout this book. The goal of this chapter is to point out major components and some of the unique properties involved with programming the Chart object.

XLS files

The XLS workbook files produced by Excel 2003 use the same file format as Excel 97, Excel 2000, Excel 2002, and Excel 2003. These files cannot be opened by any version of Excel prior to Excel 97. You can, however, save a workbook using any of the older Excel file formats. You might lose some information that is specific to the later file format. An Excel workbook or add-in file can have any extension that you like. In other words, these files don't need to be stored with an XLS (for workbooks) or XLA (for add-ins) extension.

ExceL Charts

Charts are valuable tools for data analysis and presentation in Excel or any other spreadsheet application. Unfortunately, the learning curve for creating charts is typically a bit longer and steeper than for other spreadsheet components. This is also true with regard to programming charts in Excel because the Chart object is a rather substantial component of the Excel object model. Before attempting to program with Excel's Chart object, a good understanding of the common chart types and their components is required. The Chart object Chart sheets and embedded charts

The Spreadsheet Page

All humility aside, this is one of best sites on the Web for developer information. It contains files to download, developer tips, instructions for accessing Excel Easter Eggs, an extensive list of links to other spreadsheet sites, information about my books, and even spreadsheet jokes. The URL is

Designing the Math Game

The program interface is built from a single Excel worksheet. The worksheet is formatted with colors and a large font to make it easy for the user to see the questions. The macro recording The only input required by the Math Game program is the user's answers to the questions as they are entered from the keyboard. The program must make it convenient for the user to quickly enter his or her answers in the required worksheet cell, so the program must keep the answer cell selected through the duration of the game. This can be accomplished pro-grammatically by selecting the cell when the user starts the game and setting the direction in which the selection moves after Enter is pressed on the keyboard (see Tools, Options, Edit, and the Move selection after Enter Check Box from the Excel application).

The Multi Page Control

Excel Vba Multiseiten

The MultiPage control is another example of a container control that groups or organizes the user interface on a form into multiple categories. An example of the MultiPage control in the Excel application is the Options dialog box shown in Figure 6.10. The Options dialog can be selected in the Excel application from the Tools menu. You can see from this example that the MultiPage control allows you to cram a lot of options onto a single form.

Pivot Select and Pivot Selection

The Name parameter specifies the selection in what Microsoft refers to as standard PivotTable selection format. Unfortunately, the documentation does not tell us what this means, saying instead, A string expression used to specify part of a PivotTable. The easiest way to understand the required syntax is to turn on the macro recorder, select cells in the PivotTable, and then study the resulting code. There is more on this, and we refer the reader to the Excel VBA help documentation (start by looking up the PivotSelect topic).

Write A Vba Procedure That Outputs A Range After Being Selected By The User

Write a VBA procedure that deletes all but one worksheet in all workbooks currently open in the Excel application. Again use nested For Each loops. To turn off prompts to the user, use the DisplayAlerts property of the Application object. 7. Create a spreadsheet that contains several names in multiple rows and columns. Write a VBA procedure that finds a specific name within a highlighted range on the spreadsheet. Use the Find() method of the Range object and the Worksheet_ SelectionChange() event procedure of the Worksheet object. Refer to the Object Browser or on-line help for syntactic requirements. Then record a macro with a similar function and compare the recorded procedure to your own.

Run A Procedure When Rightclicking A Chart

Position Chart Cell Vba

You can create a procedure that runs automatically each time a user right-clicks on a particular chart with the mouse. To create this type of procedure, you need to capture the BeforeRightClick event associated with the appropriate Chart object. To create a procedure that executes when a user right-clicks a chart, you create a new procedure and add it to the object code module for the particular chart. In fact, all event-handling procedures that you create for monitoring chart events must reside within the appropriate chart object code module to have Excel execute them automatically. To create a procedure that executes when right-clicking a chart, you name the procedure Chart_BeforeRightClick. Although the procedure resides in a chart object code module, it can access other procedures within the same workbook. Therefore, you can create a Q In the Projects window, double-click the chart object node for the chart where you want to place the Chart_BeforeRightClick subroutine. The code...

Looping through the Chart Objects collection

This example changes the chart type of every embedded chart on the active sheet. The procedure uses a For-Next loop to cycle through each object in the ChartObjects collection, access the Chart object in each, and change its Type property. The following macro performs the same function but works on all the chart sheets in the active workbook

The Legend Entry Object

Figure 21-1 shows a chart legend with two legend entries. Legend entries are represented by LegendEntry objects. The LegendEntry objects for a legend are kept in the LegendEntries collection object for the Legend object. This collection is accessed using the LegendEntries property of the Chart object.

Requirements for Poker Dice

I want to create a program that simulates five card draw using dice instead of cards. The spreadsheet is preformatted in the Excel application for color, font, and borders. The requirements of the program are as follows 1. The user interface shall consist of a single spreadsheet formatted to simulate a game board with five Image controls, five Check Box controls, two Command Button controls, and a merged area of cells for outputting messages to the user. 2. A new game shall be initiated by clicking a button and clearing the game board (spreadsheet) of images, check marks, and text. 6. After the initial roll of the dice, the program shall report the result of the hand as text in a spreadsheet cell.

Apply Chart Wizard Settings To A Chart

You use the ChartWizard method with a specific Chart object. This method includes eleven different optional parameters, which you can only use with this method and which enable you to set properties for the chart Source, Gallery, Format, PlotBy, CategoryLabels, SeriesLabels, HasLegend, Title, CategoryTitle, ValueTitle, and ExtraTitle. You must set any additional properties individually. You use the Source parameter to specify or modify any valid range of data that creates the chart. Keep in mind that when you work with a chart sheet, you must specify the

Creating contextsensitive Help

In Chapter 3, we looked at VBA b uilt-in Help facilities. However, most users of Excel - or of any other Office application - will have had some experience with the Office Assistant. When clicked, this will deliver Help known as Balloon Help - so-called because of the way in which it is displayed (Figure 10.13). It is possible to customise the Help delivered by the balloon object, so that we can create context-sensitive Help, which could be of significant benefit to a user who needs support with some Excel application. This is one of several objects that Microsoft makes available to each of its Office applications. The Assistant displays inside the balloon a heading followed by a list of menu options and some buttons at the bottom.

An Example of Chart Creation

As we have said, creating a useful chart from scratch requires using the properties and methods of the Chart object. As we will see, this object is quite complex, with a great many properties, methods, and children. Before plunging into a discussion of these items, we want to give an example of chart creation. This will put our future discussion into some perspective. In fact, we will have several occasions to make reference to this code in the sequel. ' Create an embedded chart Before looking at the main properties, methods, and children of the Chart object, we can get one simple, but important, item out of the way. Namely, it is possible for two or more embedded charts to overlap, which raises the question of how to control which chart object appears on the top. Every ChartObject object has an order, called its z-order, that indicates the object's relative position with respect to an imaginary z-axis that comes directly out of the monitor at right angles, towards the user, as...

Figure Axesrelated objects

The Chart object has an Axes collection that contains an Axis object for each axis in the chart. The Axes method returns either a single axis or the Axes collection for a chart. To return the Axes collection for a chart, use the syntax The Chart object has a read-write property named HasAxis that determines whether or not the chart displays various types of axes. However, it is important to note that this method will fail if the chart does not yet have the corresponding data series. For instance, if you are creating a chart from scratch, it might be natural to add the code to create a category axis before adding the category data that will be plotted against that axis. This not to do (to quote Hamlet)

File IO Using Workbook and Worksheet Objects

The Workbook and Worksheet objects contain methods for opening, closing, and saving workbooks in the same manner a user might perform these operations from the Excel application. You can open and save workbook files using a variety of formats with VBA code. The same file formats may also be used to save individual worksheets within an existing workbook. Figure 7.10 shows an Excel worksheet with random numerical data that has been saved as a comma-delimited text file. Figure 7.11 shows the resultant file opened in WordPad.

Adding ActiveX Controls to a Form

To test the application, select the form and click on Run Sub UserForm from the IDE standard toolbar (see Figure 6.5) or menu bar, or press F5 on the keyboard. The form appears as a window above the Excel application. Click the Command Button control to output the simple message to the Label control. To close the form, click on the X in the upper-right corner of the window.

Constructing the Biorh Ythms and the Time of Your Life PRogRAm

The Time Your Life

The majority of the work for this project will be handled by the Excel application via formulas and a chart. The requirements handled by the VBA program will be limited to collecting the user's name and birth date, and outputting the result of some date calculations. As was the case for the Colorful Stats project in Chapter 1, there is nothing in this project that could not be accomplished in the Excel application without the aid of a VBA program. As mentioned earlier, I've left most of the work to the Excel application by using formulas to calculate the sinusoidal curves for the three cycles, and a chart to display the curves. The specific requirements of the project follow 1. The biorhythm spreadsheet shall use formulas to calculate a sinusoidal curve for each of the three cycle types. Note that these three curves are static. 2. The spreadsheet shall contain an embedded chart that displays the static curves described in requirement 1. 3. The VBA program shall be initiated from a...

Project Bior HytHMs and tHe TiMe of Your Life

The Biorhythms and the Time of Your Life program (see Figure 2.1) begins by asking for the user's name and birth date. The program then calculates the length of the user's life in years, months, days, hours, minutes, and seconds. Following the user input, the user's name, birth date, and age (in the aforementioned units) are displayed in the worksheet. The worksheet also contains an embedded chart that displays the current state of the user's three biorhythm cycles (physical, emotional, and intellectual). The Biorhythms and the Time of Your Life spreadsheet.

Planning an application that meets user needs

After you determine the end users' needs, you might be tempted to jump right in and start fiddling around in Excel. Take it from someone who suffers from this problem Try to restrain yourself. Builders don't construct a house without a set of blueprints, and you shouldn't develop a spreadsheet application without a plan. i Add-in or XLS file In most cases, you probably want your final product to be an XLA add-in. Sometimes an XLS file is preferable.

The Visual Basic toolbar

Security Button displays the Security dialog box where you can select the security level. There are three levels from high - where macros are only accepted by the system from trusted sources, to protect against macro viruses, to low - with no protection. The default level is medium. Visual Basic Editor switches to the Visual Basic Editor - see Chapter 3. Control Toolbox toggles the display of the Control Toolbox toolbar. This lets you add controls such as buttons, edit boxes, and so on, to Excel worksheets.

The Excel Startup Folder

When Excel loads, it automatically loads any spreadsheets stored in its startup and alternate startup folders. The default location of the startup folder is usually a subfolder of the main Excel folder named XlStart. By default, there is no alternate startup folder, although one can be defined using the General tab of the Options dialog to open it, select Options from the Tools menu. Because the contents of these folders are opened at startup as ordinary workbooks, their macros are easily accessible to all other Excel workbooks. This makes them ideal as a storage location for macros. The only drawback is that Excel actually opens the spreadsheets stored in these directories to prevent this, they should be hidden by selecting the Hide option from Excel's Window menu (not the Format menu) when the spreadsheet to be hidden is active. Macros that are stored in the startup and alternate startup folders are available from the Macro dialog, and we can assign them to toolbars and menus...

Add A Data Table To The Chart

Excel stores the data table associated with a chart in the DataTable object. The HasDataTable property, associated with the Chart object, specifies whether a data table actually displays for the selected chart. This property only accepts Boolean values of either True or False. To display a data table for a chart, you need to set this property to True. with the Chart object, the DataTable object has its own list of associated properties and methods. For example, you can customize the font settings and border settings for the data table. You can specify the font for the data table using the Font property. With the Font property, you use the properties associated with the Font object to specify the actual font properties of the text. For example, DataTable.Font.Name Arial specifies that the data table uses the Arial font. See the section Format Chart Text for more information on working with the Font object on a chart. If you display the legend as part of the data table, you typically do...

Opening and Closing the Battlecell Workbook

The Open() event of the Workbook object is triggered when the Excel file is opened by the user. It's an excellent location for code that initializes the appearance of the workbook and or specific worksheets. I have used it here to maximize the Excel application window and the workbook window by setting the WindowState property of the Application and Window objects to the VBA-defined constant xlMaximized. The ZoomGameBoard() sub procedure (listed later) is called in order to zoom in or out on the workbook window such that the game board fits within the user's visible range. The last task before closing the workbook is to save it using the Save() method of the Workbook object. If the Saved property of the Workbook object returns false, then the Save() method is used to resave the Battlecell.xls workbook.

Saving and Opening XML Documents

If the structure of the XML document is Excel-XML, then the opened file will conform to that of a normal Excel spreadsheet however, if the file is just a well-formed XML document (not structured as Excel-XML), then Excel will open it as tabular data. Figure 8.9 shows the result of opening the words.xml file with the Open() method of the Workbooks collection object. The words.xml file had not been previously saved using the Excel-XML structure.

The Chart Group Object

As you no doubt know, an Excel chart can contain more than one data series. We have remarked that each series (that is, the Series object) has a ChartType property that can be used to set the chart type of the series. Thus, a single chart may have one or more series with a column-type format and one or more series with a line-type format. The ChartGroup objects for a single chart are stored in the ChartGroups collection for the Chart object. This collection is accessed using the ChartGroups property. The Chart object has the following methods that return corresponding collections of ChartGroup objects There are also some members of the Chart object that return chart groups for 3-D charts. They are Area3DGroup, Bar3DGroup, Column3DGroup, Line3DGroup, Pie3DGroup, and SurfaceGroup. These members are singular because they return a single ChartGroup object.

The Workbook and Window Objects

Thisworkbook Vba

However, the Window object may be unfamiliar and or a bit confusing. Window objects refer to instances of windows within either the same workbook, or the application. Within the Excel application, the Windows collection object contains all Window objects currently opened this includes all Workbook objects and copies of any Workbook objects. The Window objects are indexed according to their layering. For example, in Figure 5.2, you could retrieve Book2 with the following code You may be thinking that the Windows collection object within the Application object is essentially the same as the Workbooks collection object. This may or may not be true depending whether or not the user creates a new window by selecting New Window from the Window menu in the Excel application. This effectively makes a copy of the currently selected workbook. You may also use the NewWindow() method of either the Window or Workbook object in your code to accomplish the same task. Next, the Open() event procedure...

Table Dialog Types Used with the File Dialog Object

The Execute() method allows the user to carry out the specified action of the dialog box for files that are compatible with the Excel application (for example, files of type .xls, .xlt, .csv, and so on). For example, the Open dialog box allows the user to select one or more files to open when the Execute() method of the FileDialog object is invoked. When the following statement follows the Show() method for the Open dialog, the item(s) selected by the user are opened in Excel.

Where Can I Download Vertex42 The Excel Nexus

There is no place where you can download Vertex42 The Excel Nexus for free and also you should not channel your time and effort into something illegal.

Download Now