Info

The example near the end of Chapter 18, Text File Processing, proposed a method for storing 650,000 records in an Excel worksheet. At some point, you need to admit that even though Excel is the greatest product in the world, there is a time to move to Access and take advantage of the Access Multidimensional Database (MDB) files. Even before you have more than 65,000 rows, another compelling reason to use MDB data files is to allow multi-user access to data without the headaches associated with...

Scheduled Procedures Require Ready Mode

The .OnTime method will run provided only that Excel is in Ready, Copy, Cut, or Find mode at the prescribed time. If you start to edit a cell at 7 59 55 a.m. and keep that cell in Edit mode, Excel cannot run the CaptureData macro at 8 00 a.m. as directed. In the previous code example, I've specified only the start time for the procedure to run. Excel waits anxiously until the spreadsheet is returned to Ready mode and then runs the scheduled program as soon as it can. The classic example is that...

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. 1).Activate ActiveChart.Location Where xlLocationAsNewSheet, Name MyChart When this code is run, it changes the embedded chart, Chart 1 on Sheet1, to a chart sheet named MyChart.

Suppressing Subtotals for Multiple Row Fields

As soon as you have more than one row field, Excel automatically adds subtotals for all but the innermost row field. Because I am unhappy with the pivot table's lack of capability to add a page break at each change in the product field, I have resigned myself that the subtotals are better added if I use the Subtotals method after converting the pivot table to a table. Thus, while creating the pivot table in code, you will want to turn off the subtotal rows for the product field. To do this in...

BonusFTP from Excel

After you are able to update Web pages from Excel, you still have the hassle of using an FTP program to upload the pages from your hard drive to the Internet. Again, we have lots of people proficient in Excel, but not so many comfortable with using an FTP client. Ken Anderson has written a cool command-line FTP freeware utility. Download WCL_FTP from http www.pacific.net -ken software . Save WCL_FTP.exe to the root directory of your hard drive, and then use this code to automatically upload...

Ydn

(Debug menu), 50 Edit Watch dialog box, 50 editor. See VBE electrical control system case study 512-515 addMSlash() function, 514 addWiringWSTag() function, 512 cloneDataBlocks() function, 513 function, 513 createWiringData() function, 508-511 deleteAConnection() function, 512, 515 ExcelCoords type, 509 global constants, 510 goals, 506 resetValue() function, 515 solution, 506-507 summary, 516 WiringToExcelCoords() function, 509 Workbook_SheetChange() function, 508, 511-512...

Case Study Using XML Data from Amazoncom

The promise is that all sorts of XML data will soon be available. One high-profile example available now is Amazon.com. They now offer a wide variety of queryable data that is returned in XML format. It is possible to use VBA to query data from Amazon and have the results returned to Excel. Amazon.com offers a software development kit for download from http amazon.com webservices.To get the kit,follow these steps. 1. Follow the link to download the free developer's kit. 2. Click the Download...

XML as the New Universal File Format

If you use Excel to open the simple XML file shown in Figure 15.1, Excel can offer headings and can even infer something called the schema of the data. A schema is a separate file that describes the columns and relationships inherent in the data. If the source system would happen to be missing a zip code for a record, we do not have to worry about the SKU moving over from Column F. Compare Figure 15.2 with the CSV example shown in Figure 15.1. Excel can present a much...

Info Rbl

Charts to, 181 compared to embedded charts, 176 colors, 186 Column charts, 195 cone charts, 197 creating, 178-181 Add function, 179 Location function, 180-181 SetSourceData function, 179 custom charts, 204-205 cylinder charts, 197 data labels, 191-192 data series, 188-189 data tables, 192-193 default chart type, 181-182 doughnut charts, 196 embedded charts, 153 changing to ChartSheets, 181 ChartObject container, ChartSheets, 176 events, 418-419 error bars, 193-195 events, 201-202 exporting as...

Administrator To

Declare an Array Multidimensional Arrays Fill an Array Empty an Array Arrays Can Make It Easier to Manipulate Data, But Is That All 383 Dynamic Arrays Passing an Array Next Steps 18 Text File Importing from Text Files Importing Text Files with Less Than 65,536 Rows Reading Text Files with More Than 65,536 Rows Writing Text Files Next Steps 19 Using Access as a Back End to Enhance Multi-User Access to Data 401 ADO Versus DAO The Tools of ADO Adding a Record to the Database Retrieving Records...

Automating Excel Power In

Overview Embedded Charts Versus Embedded Charts in a ChartObject Container Charts on a Chart Sheet Have No Container Creating a Chart with VBA Moving from Embedded to Chart Sheet and Vice-Versa 181 The Default Chart Using Object Variables to Streamline Code The Anatomy of a Chart The Chart Area VBA Name ChartArea The Plot Area VBA Name PlotArea The Data Series VBA Name Series The Chart Axes VBA Name Axis Grid Lines VBA Name HasMajorGridlines and HasMinorGridlines 191 Data Labels VBA Name...

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

Universal File Format

For many years, CSV was considered the universal file format. Just about any application could produce data in comma-separated values and just about any spreadsheet could read in CSV data. XML promises to become the universal file format of the future and it is far more powerful. You may deal with CSV data every day. As developers, we need to talk to the other developer who is generating the CSV data, and we have to understand that the fifth column contains a zip code and the sixth column...

Embedded Charts Versus Chart Sheets

If you go back far enough, you find that all charts used to be created as their own ChartSheets. Then, in the mid-1990s, Excel added the amazing capability to embed a chart right onto an existing worksheet. This allowed a report to be created with tables of numbers and charts all on the same page, something we all take for granted today. 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...

Auto Filters

The AutoFilter feature was added to Excel because people found Advanced Filters too hard. They are cool when used in the Excel user interface. I rarely have an occasion to use them in Excel VBA. However, one cool feature is available only in Excel VBA. When you AutoFilter a list in the Excel user interface, every column in the dataset gets a field drop-down in the heading row. Sometimes, you have a field that doesn't make a lot of sense to AutoFilter. For example, in our current dataset, you...

The Chart AxesVBA Name Axis

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

Using a Scrollbar as a Slider to Select Values

Access Userform

Chapter 9 discussed using a spin button control to allow someone to choose a date. The spin button is good, but it allows clients to adjust up or down by only one unit at a time. An alternative method is to draw a horizontal scrollbar in the middle of the userform and use it as a slider. Clients can use arrows on the ends of the scrollbar like the spin button arrows, but they can also grab the scrollbar and instantly drag it to a certain value. The userform shown in Figure 21.12 includes a...

Adding Graphics to a User Form

Animation Excell Dans Userform

ggj A listing on a form can be even more helpful if a corresponding graphic is added to the form, as shown in Figure 9.12. The following code displays the photograph corresponding to the selected employee from the ListBox Private Sub lb_EmpName_Change Dim EmpFound As Range With Range EmpList Set EmpFound .Find lb_EmpName.Value If EmpFound Is Nothing Then MsgBox Employee not found lb_EmpName.Value tb_EmpPosition .Offset 0, 1 tb_HireDate .Offset 0, 2 On Error Resume Next Img_Employee.Picture...

Convert Week Number into Date

Ever receive a spreadsheet report and all the headers showed the week number I don't know about you, but I don't know what Week 15 actually is. I'd have to get out my calendar and count the weeks. And what if you need to look at a past year What we need is a nice little function that will convert Week Year into the date of the Monday for that week, as shown in Figure 4.12. Str The Week information to be converted. The result must be formatted as a date. Function ConvertWeekDay Str As String As...

Creating a Vanilla Pivot Table in Excel Interface

Although they are the most powerful feature in Excel, Microsoft estimates that pivot tables are used by only 7 of Excel users overall. Based on surveys at MrExcel.com, about 42 of advanced Excel users have used pivot tables. Because a significant portion of you have never used pivot tables, I will walk through the steps of building a pivot table in the user interface. If you are already a pivot table pro, jump ahead to the next section. Let's say you have 50,000 rows of data, as shown in Figure...

Summarizing Records via ADO

One of Access's strengths is running summary queries that group by a particular field. If you build a summary query in Access and examine the SQL view, you will see that complex queries can be written. Similar SQL can be built in Excel VBA and passed to Access via ADO. The following code uses a fairly complex query to get a net total by store ' This builds a table of net open 1 on Styles AI1 Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Basic Logic Get all open Incoming Transfers by...

Adding Controls at Runtime

It is possible to add controls to a userform at runtime. This is very convenient if you are not sure how many items you will be adding to the form. Figure 21.8 shows a very plain form. It has only one button. This plain form is used to display any number of pictures from a product catalog. The pictures and accompanying labels appear at runtime, as the form is being displayed. Flexible forms can be created if you add most controls at runtime. Figure 21.8 shows a very plain form. It has only one...

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. 2. Rename the module to cl_ChartEvents. 3. Enter the following line of code in the class module Public WithEvents myChartClass As Chart 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. 5. Enter the following lines of code in a standard module Dim...

Coloring the Active Control

Another method for helping a user fill out a form is to color the active field. The following example changes the color of a text box or combobox when it is active, as shown in Figure 21.16. Help users keep track of which field they're filling in by coloring the field when it is active. Help users keep track of which field they're filling in by coloring the field when it is active. Place the following in a class module called clsCtlColor Public Event GetFocus Public Event LostFocus ByVal...

Return Hyperlink Address

You've received a spreadsheet with a list of hyperlinked information. You would like to see the actual links, not the descriptive text. You could just right-click and Edit Hyperlink, but you want something more permanent. This function extracts the hyperlink address, as shown in Figure 4.18. GetAddress Hyperlink The argument is Hyperlink The hyperlinked cell from which you want the address extracted. Function example Function GetAddress HyperlinkCell As Range GetAddress mailto , End Function

Switching Excel to Display RC Style References

To switch to R1C1-style addressing, select Tools, Options from the main menu. On the General tab, check the box for R1C1 reference style see Figure 6.1 . Checking R1C1 reference style on the General tab of the Options box causes Excel to revert to R1C1 style in the Excel user interface. Checking R1C1 reference style on the General tab of the Options box causes Excel to revert to R1C1 style in the Excel user interface. After you switch to R1C1 style, the column letters A, B, C across the top of...

Microsoft Excel

Introduction I First Steps up the VBA Learning Curve 1 Unleash the Power of Excel with VBA 11 2 This Sounds Like BASIC,So Why Doesn't It Look Familiar 29 3 Referring to 4 User-Defined 6 R1C1 Style 8 Event 9 UserForms An II Automating Excel Power in VBA 11 Data Mining with Advanced 12 Pivot 13 Excel 14 Reading from and Writing to the Web 331 15 XML in Excel 2003 16 Automating III Techie Stuff You Will Need to Produce Applications for the Administrator to Run 18 Text File 19 Using Access as a...

The Plot AreaVBA Name Plot Area

This is the area where the data series are plotted. The plot area also contains the axes and axis labels. The plot area can be formatted just like the chart area. In addition, you can also resize and reposition the plot area within the chart. To do this, specify the Top, Left, Height, and Width properties of the plot area. Every drawing or chart object has a container. For example, the container for an autoshape say, rectangle is the worksheet on which it is drawn. Similarly, the container for...

Manually Creating a Web Query and Refreshing with VBA

The easiest way to get started with Web queries is to create your first one manually. Using any Web browser, navigate to a Web site and enter the settings needed to display the information of interest to you. In the case of Figure 14.1, the URL to display that portfolio is Open Excel. Find a blank area of the worksheet. From the Excel menu, choose Data, Import External Data, New Web Query. Excel shows the New Web Query dialog with your Internet Explorer home page displayed. Copy the preceding...

Hierarchical Donut Chart

Doughnut Chart Excel Template

Conceptually, a hierarchical donut chart is like a pie chart with each slice exploded twice. The unique thing is that each layer holds the proportion of the one inside. Each data label optionally shows the data value, as well as what percent it is of the preceding layer s it encloses see Figure 10.25 . In a macro-economic supply curve chart, both the height and width of the bars represent data. In a macro-economic supply curve chart, both the height and width of the bars represent data.

Case Study Using Formula Based Conditions in the Excel User Interface

You can use formula-based conditions to easily solve the report introduced in the prior case study. To illustrate,o to the right of the criteria range,set up a column of cells with the list of selected customers. Assign a name to the range such as MyCust. In cell J2 of the criteria range enter a formula such as Not ISNA Match D2,MyCust,False . To the right of the MyCust range,set up a range with a list of selected products. Assign this range the name of MyProd. In the K2 of the criteria range,...

Grouping by Week

You probably noticed that Excel allows you to group by day, month, quarter, year. There is no standard grouping for week. You can define a group that bunches up groups of 7 days. By default Excel starts the week based on the first date found in the data. This means that the default week would run from Thursday January 1, 2004 through Wednesday January 7, 2004. You can override this by changing the Start parameter from True to an actual date. Use the WeekDay function to determine how many days...

Cannot Move or Change Part of a Pivot Report

Although pivot tables are incredible, they have annoying limitations. You cannot move or change just a part of the pivot table. For example, try to run a macro that would delete Column 0. This column contains the Grand Total column of the pivot table. The macro comes to a screeching halt with an error 1004, as shown in Figure 12.7. You can not delete just a part of a pivot table.To get around this limita-tion,you can change the summary from a pivot table to just values. You cannot change, move...

Charts

My guess is that you are probably a numbers person. Excel power users tend to be analytical types. We can look at a table of data and spot the spikes and trends. Unfortunately, our managers need some help. They say a picture is worth a thousand words. And for a picture of numbers, nothing works better than a well-conceived chart that draws data from an Excel table. Charts linked with tables of spreadsheet data have been in use from the early spreadsheet days and, over this period, spreadsheet...

Using XML to Round Trip a Workbook from Excel to HTML and Back

Diagnosis Worksheet Template

Microsoft is thrilled that you can now save Excel 2003 and Word 2003 documents as XML files. They consider XML files to be native Office documents now. This is cool, because it means that any program that can write XML can, in theory, produce an Excel file on the fly. Round-tripping is the process of saving an Excel file as XML, and then opening the XML file back into Excel. Figure 15.5 shows the results of round-tripping an Excel file. The file on the left contains greenbar formatting, strange...

Import CSV

Submitted by Masaru Kaji of Kobe-City, Japan. Masaru provides Excel Consultation through Colo's Junkroom If you find yourself importing a lot of CSV files and then having to go back and delete them, this program is for you. It quickly opens up a CSV in Excel and deletes the original file Option Base 1 Dim buf 1 To 256 As Variant Dim i As Long Const strFilePath As String C temp Test.CSV 'Change here Dim strRenamedPath As String strRenamedPath Split strFilePath, . 0 amp txt .ScreenUpdating False...

Trendlines and Error BarsVBA Name Trendlines and Error Bar

Equation Straight Line Worksheet

If you have observed values, you might want to chart both the values and then add a trend line to help illustrate the trend. Excel offers several types of trend lines linear, logarithmic, polynomial, power, exponential, and moving average. After you've added the trend line, you might want to add error bars to show how the actual values differ from the trend. Figure 10.14 shows an XY chart plotting sales by year for a product line. Figure 10.14 shows an XY chart plotting sales by year for a...

Speedometer Chart

Figure 10.23 shows data plotted on a speedometer chart. It is perfect for dashboard reports for your boss. This is a customized XY chart with two autoshapes circles to represent the outer periphery and dial hub . The circles are sized and placed each time the chart is updated. The rest of the chart is made up of several series and data labels that derive data from the spreadsheet. The actual dial reading, as well as the range of each color zone, is completely customizable. Several dials can be...

The Ultimate Reference to All Objects Methods Properties

In the Visual Basic Editor, press F2 to open the Object Browser see Figure 2.40 . The Object Browser lets you browse and search the entire Excel object library. I own a thick book that is a reprint of this entire object model from the object browser. It took up 409 pages of text. I've never used those 409 pages, because the built-in Object Browser is far more powerful and always available at the touch of F2. I'll take a few pages to teach you how to use the Object Browser. Press F2 to display...

Disable Cut Copy and Paste

There are times when you do not want your clients to interfere with the structure of a worksheet. This program disables enables all methods of cutting, copying, and pasting data in Excel EnableControl 295, True ' cells EnableControl 296, True ' Rows EnableControl 297, True ' Cols ' amp Delete

Case Study Password Cracking

The password hacking schemes were very easy in Excel 97 and Excel 2000.The password cracking software could immediately locate the actual password in the VBA project and report it to the software user. Then, in Excel 2002, Microsoft offered a brilliant protection scheme that temporarily appeared to foil the password cracking utilities.The password was tightly encrypted. For several months after the release of Excel 2002,password cracking programs would have to try brute force combinations.The...

Unleash the Power of Excel with VBA

Visual Basic for Applications combined with Microsoft Excel is probably the most powerful tool available to you. This tool is sitting on the desktops of 400 million users of Microsoft Office and most have never figured out how to harness the power of VBA in Excel. Using VBA, you can speed the production of any task in Excel. If you regularly use Excel to produce a series of monthly charts, you can have VBA do the same task for you in a matter of seconds. There are two barriers to learning...

Using RC with Mixed References

A mixed reference is one where the row is fixed and the column is allowed to be relative, or where the column is fixed and the row is allowed to be relative. There are many situations where this will be useful. Imagine you've written a macro to import Invoice.txt into Excel. Using .End xlUp , you find where the total row should go. As you are entering totals, you know that you want to sum from the row above the formula up to Row 2. The following code would handle that TotalRow Cells 65536, 1...

Using RC with Relative References

Imagine you are entering a formula in a cell. To point to a cell in a formula, you use the letters R and C. After each letter, enter the number of rows or columns in square brackets. For columns, a positive number means to move to the right a certain number of columns, and a negative number means to move to the left a certain number of columns. From cell E5, use RC 1 to refer to F5 and RC -1 to refer to D5. For rows, a positive number means to move down the spreadsheet a certain number of rows....

Using Streaming Data

A number of services offer live streaming data into your spreadsheet. These services use a technology such as DDE to automatically pipe information directly into cells in your worksheet. Although these services require a monthly subscription, it is pretty amazing to watch your spreadsheet automatically change every second with real-time stock prices. These real-time DDE services typically use a formula that identifies the external .exe program, then a pipe character , and data for the external...