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 Overview

Rating:

4.8 stars out of 17 votes

Contents: Excel Templates
Official Website: www.vertex42.com
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.

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.

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.

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.

End user interface styles VBA Excel applications

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...

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.

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.

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.

The Chart Sheet Interface

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...

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.

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...

Linking an Excel Spreadsheet to a Microsoft Access Database

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...

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.

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

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.

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.

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...

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...

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.

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.

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.

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.

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...

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...

The Chart object model

To get a feel for the number of objects involved when working with charts, turn on the macro recorder, create a chart, and perform some routine chart-editing tasks. You might be surprised by the amount of code that Excel generates. When you first start exploring the object model for a Chart object, you'll probably be very confused . . . which is not surprising because 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 which 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...

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.

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

Excel Overtime Formula

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.

A simple example using the OWC Spreadsheet control

Cells Userform Vba Excel

This example uses a Spreadsheet control to create a simple loan payment calculator in a UserForm. The finished product is shown in Figure 15-16. The user can enter loan information into column B, and the monthly payment is calculated (by using a formula) and then displayed in the bottom-right cell. Figure 15-16 This UserForm uses a Spreadsheet control for a simple loan payment calculator. Figure 15-16 This UserForm uses a Spreadsheet control for a simple loan payment calculator. This example is primarily for illustration only.Using a Spreadsheet control is overkill. It is much more efficient to use EditBox controls to get the information and calculate the loan payment by using VBA code. To create this UserForm, start with a new workbook and follow the steps presented next. Remember Make sure to first add the Spreadsheet control to your Toolbox. (See the earlier section, Adding the Spreadsheet control to a UserForm.) 1. Insert a new UserForm and add a Spreadsheet control. Don't change...

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...

Example Using Chart events with an embedded chart

The example in this section provides a practical demonstration of the information presented in the previous section. The example shown in Figure 18-13 consists of an embedded chart that functions as a clickable image map. Clicking one of the chart columns activates a worksheet that shows detailed data for the region. The workbook is set up with four worksheets. The sheet named Main contains the embedded chart. The other sheets are named North, South, and West. Formulas in B1 B4 sum the data in the respective sheets, and this summary data is plotted in the chart. Clicking a column in the chart triggers an event, and the event handler procedure activates the appropriate sheet so that the user can view the details for the desired region. user clicks the Enable Chart Events button, the embedded chart is assigned to the SummaryChart object, which, in effect, enables the events for the chart. Listing 18-4 shows the class module for EmbChartClass.

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

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

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...

Displaying a Spreadsheet in a User Form

Not content to simply display a chart in a UserForm How about an entire spreadsheet Figure 15-15 shows an example of a UserForm that contains a Microsoft Office Spreadsheet 11.0 control. This control can contain an entire interactive spreadsheet, complete with formulas and formatting. In fact, this Spreadsheet control has a significant advantage over a standard Excel sheet Its dimensions are 18,278 columns x 262,144 rows. This is nearly 300 times as many cells as a standard Excel worksheet Figure 15-15 This UserForm contains a Spreadsheet control. Figure 15-15 This UserForm contains a Spreadsheet control.

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'...

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

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 & _ 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

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 & _ 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 full path and filename 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.

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 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.

Chart sheets

A chart sheet normally holds a single chart. Many users ignore chart sheets, preferring to store charts on the worksheet's drawing layer. Using chart sheets is optional, but they make it a bit easier to print a chart on a page by itself, and they are especially useful for presentations. Figure 2-1 shows a pie chart on a chart sheet. Figure 2-1 A pie chart on a chart sheet.

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.

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

Spreadsheets

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

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...

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 Ibm Pc the catalyst for standards

By the time that the fourth-generation computers were being built, IBM was well established in manufacturing mainframe and minicomputers. IBM's entry into the microcomputer market was relatively late, but turned out to be a defining moment because the IBM PC became a standard. It did so in the sense that other computer manufacturers were keen to ensure that software written for their systems would work on the IBM PC they wanted to ensure that their software was 'IBM compatible'. This created a bandwagon effect with manufacturers everywhere building IBM-compatible machines. It also gave software developers the incentive they needed and spawned the development of high quality business software such as the spreadsheet Lotus 123 (see later in this chapter). When IBM released the PC, they enlisted the Microsoft Corporation to write the operating system called DOS (Disk Operating System) for the PC and this also became a standard. They also enlisted the microprocessor company Intel to...

Software and its evolution

Computer hardware is of little use without computer software hardware without software is like a CD player without compact disks. However, the hardware of a computer system requires different types of software - called programs - to operate. First, it needs programs that will control all the hardware units to link together so that they act as a whole, and enable the user to interact with the computer. The software to operate and control the hardware is called the operating system or operating system software. Second, it needs programs to perform a specific task, such as enable the user to send an e-mail message, or create a balance account using a spreadsheet. These programs are called application programs, or application software. Application and system programs are collectively known as software.

Declaring and assigning variables

One can also think of a variable as a spreadsheet cell that is not visible to the user. After all, a cell can contain a data item that can be varied. However, using variables, rather than cells, allows VBA programs to run faster, for VBA can manipulate variables more efficiently. Moreover, variables can be used to hold (or save) temporary data when a macro is executing.

VBA program using variables

Listing 5.1 is a VBA program that enables a user to input two numbers, and output the result along with the numbers on a blank spreadsheet (see Figure 5.3). The program assigns two integer variables called first_number and second_number using input boxes. The sum of these variables after input is then assigned to another integer variable called sum. Each of these variables is then assigned to worksheet cells in the range C3 to C5, along with appropriate labels in the previous column cell range (i.e. B3 to B5) and a heading which begins in cell B1.

Loops controlled by a number

The previous example used a For .Next loop to go through all the cells in a range - this is a very powerful way to process spreadsheets. You can also use a number to control how many times a For .Next loop executed. This kind of loop is very common in traditional programming languages when general variables are used. For example, the weekly sales data in the SALESMAN workbook might require calculations to find the total weekly mileage from seven daily totals. Rather than do this manually, a macro could be written whose purpose is to provide an input box for the user to enter the number of sales recorded for a salesman for each day for seven days of the week, add them up and display the total in a message box (see Figure 7.2). In structured English, the program might look like this

Using active cells and jumping around in a For Next loop

This example uses the weeklysales worksheet of the SALESMAN.XLS workbook, to check to see if a salesperson is worthy of promotion. The criteria for promotion in the organisation are currently that the sales person's monthly sales should exceed 1000 units. The macro is given in Listing 7.3.

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 is one you develop yourself using (what else ) VBA. To use a custom function, you must define it in the workbook in which you use it.

Okay So Whats an AddIn

Any knowledgeable user can create add-ins (but VBA programming skills are required). An Excel add-in is basically a different form of an XLS workbook file. More specifically, an add-in is a normal XLS workbook with the following differences You can convert any XLS file into an add-in. Because add-ins are always hidden, you can't display worksheets or chart sheets contained in an add-in. However, you can access an add-in's VBA Sub and Function procedures and display dialog boxes contained on UserForms.

Almost Ten Excel Resources

7his book is only an introduction to Excel VBA programming. If you hunger for more information, you can feed on the list of additional resources I've compiled here. You can discover new techniques, communicate with other Excel users, download useful files, ask questions, access the extensive Microsoft Knowledge Base, and lots more.

Developing Applications Step by Step

No simple recipe exists for developing a spreadsheet application. Besides, this isn't a cookbook. Fact is, everyone develops his or her own style for creating spreadsheet applications. In this section I provide you with some general guidelines that I find useful. At the very least, this information can help you improve your own development style. Spreadsheet developers typically perform some of the following activities. You won't necessarily perform all these steps for every application, and the order in which you perform them may vary from project to project. I Determine the user's needs. I Plan an application that meets those needs. I Determine the most appropriate user interface. I Create the spreadsheet, formulas, macros, and user interface. I Test and debug the application.

Documenting your efforts

You can easily assemble a spreadsheet application. The hard part is making it understandable to other people. You must thoroughly document your work. Doing so helps you if you need to modify the application (and you will), and it helps anyone else who needs to work on the application (after you get that big promotion).

Who ShouLd Read This Book

The goal of this book is to help you learn VBA programming with Excel. No prior programming experience is required or expected. Although you do not have to be an Excel user, you must have a good understanding of the basic tools involved in using any spreadsheet application. This includes a basic understanding of ranges and cell references, formulas, built-in functions, and charts. I ask my students at the start of every semester if they know how to use Excel. At least 90 percent of them say they are very comfortable with the application. Within two weeks of the start of the semester it is clear that no more than 10 percent of the class can write a proper formula one that takes advantage of absolute and relative references, and built-in functions. Furthermore, fewer than 5 percent know anything about chart types and the kind of analyses they should be used in. If you're not comfortable with spreadsheet applications or it's been a while since you have used a spreadsheet, then I...

VisuAL Basic for Applications with Excel

In this first chapter, I introduce you to the programming tools available in Excel. These tools include the VBA IDE (Integrated Development Environment), controls and functions available through the main Excel application, and VBA on-line help. After your introduction to the VBA programming environment, I take you through a very short and simple program that calculates some basic statistics from a sample data set. The program displays the statistics in a worksheet formatted with a large font, bright colors, and a border to complete the Colorful Stats project. The project in this chapter is short and simple, but will serve as your first introduction to the VBA programming environment, ActiveX controls, event-driven programming, and using VBA to interact with your spreadsheet. A view of the Colorful Stats spreadsheet is shown in Figure 1.1.

Programming Components within Excel

Not everything of interest to the VBA programmer can be found in the VBA IDE. There are a few programming-related components that you can access from the Excel application. The components I am referring to are the Macro items found under the Tools menu, and three of the available toolbars Visual Basic, Control Toolbox, and Forms found in the View menu in Excel. The title bar tells us the object to which this code window belongs. In this case, the code window belongs to the worksheet named Sheet1 in the workbook named Book1. This is because I placed the Command Button control on Sheet1 of Book1 in the Excel application. You may recall that I changed the name of the worksheet in Excel to MySheet, but the name of the worksheet as it will have to be referenced in code is still Sheet1. In the upper left corner of the code window is a dropdown list box containing the names of all objects contained within the selected worksheet. The name of the Command Button control is displayed because the...

Getting Help with VBA

I can't emphasize enough how important it is that you become comfortable with the on-line help in the VBA IDE (not to mention in the Excel application). The on-line help provides fast access to solutions for any programming problems you have with your project. Books make good resources and are much better at teaching you how to program, but they can't cover everything. Often, all you need to see is a simple example of how to use a particular function or other keyword the on-line help does contain documentation on every keyword, programming construct, and object you might use in your project. The bottom line is this there is always something helpful on-line, it's just a matter of finding the right document.

Constructing THe Colorful Stats Program

The purpose for the Colorful Stats program (as it relates to this book) is to give you a demonstration of ActiveX controls, event procedures, and using VBA to interact with an Excel worksheet. The practical purpose of the Colorful Stats program is to allow a user to immediately calculate basic statistics for a selected set of data. I've defined a few specific requirements for the Colorful Stats program and they are listed as follows 2. The program shall use Excel worksheet formulas to calculate the statistical parameters listed in Requirement 1.

One Dimensional Arrays

However, arrays are typically initialized inside a loop. To insert the spreadsheet's values of the first 10 cells of column A into an array, do the following Then use another loop to output the values of the array. The following loop squares the values stored in the array myArray before copying them to column B of the spreadsheet.

Initializing Battlecell and Starting the Game

The InitalizeGame() sub procedure initializes the module level and global variables used by the program. The only two variables I have not already discussed are pRange and cRange which are both global variables (type range) used to represent the 10 by 10 grids for the player and computer, respectively. Note that the range B2 K11 was defined in the Excel application with the name Player and the range O2 X11 was defined with the name Computer. Figure 5.17 shows the Battlecell worksheet with the Player range selected. The code in the sub procedure ClearBoard() effectively clears the player's and computer's grids of colors and values, and also clears the merged cells (J14 P15 defined with the name Output in the Excel application) of any help message that might be displayed. This procedure must have public scope so that it may be called from the BeforeClose() event of the Workbook object.

Using the On Error Statement

In the MultiPage.xls project from Chapter 6, the Click() event of the cmdCalcStats Command Button control contained the statement In the case of the Click() event procedure in Chapter 6, a runtime error was anticipated for the AVERAGE(), MEDIAN(), and STDEVP() worksheet functions when the user failed to select data, but clicked the Calculate button. Because the runtime error will only occur under special circumstances, it was handled by using the Resume Next clause. The Resume Next clause sends program execution to the next line of code following the line that generated the error. When the user notices that no statistics were calculated after clicking the Calculate button, then he or she should conclude that they need to select a range of cells on the worksheet. The Resume Next clause is the simplest solution for handling runtime errors and works well in the MultiPage.xls project however, it may not always be the best solution. The message box displayed by the error handler in the...

Chapter Project Word Find

The Word Find project is an Excel VBA program that creates word search puzzles. Words for a puzzle are associated with a topic that the program uses to sort the data. The topics and words used in a puzzle are stored in a random access file. The file containing the words and topics is accessed and displayed by the program. New words and topics for puzzles can be added to the file by the user. A puzzle is created when the user selects individual words and places them within a fifteen by fifteen grid running in any direction. After placing the words, the empty spaces in the puzzle are randomly filled with letters before printing. The Word Find program is stored on the accompanying CD-ROM as Wordfind.xls.

Requirements for Word Find

The objectives for the Word Find project are to demonstrate some basic techniques for file I O and error handling in a VBA program. To accomplish the task, I use an Excel worksheet as the grid for a word search puzzle and a VBA form for updating the data required by the program. The requirements for the program follow An Excel worksheet shall be used to create the word search puzzle.

Saving Worksheets to XML Files

Save as an XML> spreadsheet Save as an XML> spreadsheet Saving Data as an XML Spreadsheet If you choose to save the data as an XML spreadsheet, Excel will use its own schema to define the document. As you might expect, the XML required to define a spreadsheet is quite long, but you don't have to worry about that because Excel creates it for you. The root tag is < Workbook> and it will contain nested < Worksheet> tags for every worksheet in a workbook. In addition to the < Worksheet> tags, there are several other tags that describe the formatting and objects in the workbook. The following code shows the basic format of a document saved using the Excel-XML structure (data and attributes have been deleted for brevity and clarity). The document resulting from saving a workbook with the Excel-XML structure is just a text file however, it is also an XML file defined using the Excel-XML schema. As a well-formed and schema-defined XML document, it can be opened by other...

Coding the Math Game Program

Much of the code for the Math Game involves objects and methods discussed in previous chapters. At this point you are familiar with many of the structures and common objects used in Excel-VBA programs. New topics will usually come in the form of a new object and its associated methods and subordinate objects. Now, the greatest challenge for you is designing programs and developing algorithms.

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