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

Ewo

412-413 inserting, 413-414 multiple pivot table fields, 258-260 page fields, 276-279 PivotFields, filtering, error message, 394 File Transfer Protocol (FTP), 347 295-297 CSV files, 297-299 exporting to Word, 302-303 file paths, 77, 462-465 fixed-width files, 388-391 Help file, 33-34 listing, 293-295 looping through, 115-116 Database) files, 401 open files, checking for, importing, 387-398 writing, 398-399 .xla files, 497 XML (Extensible Markup Language). See XML Advanced Filter, 207-208 case...

Looping Through All Files in a Directory

Here are some useful procedures that make extensive use of loops.The first procedure uses VBA's FileSearch object to find all JPG picture files in a certain directory. Each file is listed down a column in Excel. The outer For i loop iterates through each image file found in a particular folder and all its subfolders. Each pass through the outer loop will return a new path and file name in the variable ThisEntry.To separate the path from the file name,the inner For j loop will search the path...

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.

Deleting and Creating the Custom Menu

Excel's main menu bar is known in VBA as Application.CommandBars(l). This menu bar typically has popup controls named File, Edit, View, and so on. Use the Add method to add a new popup menu item to the main menu. When adding the control, you need to specify the name of the menu item and its location. The name of the menu item should be the text that you want to appear on the menu bar. If you specify that the name is XYZ Co, then this appears on the menu bar. However, most of Excel's menu bars...

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

Reserved Names

Excel uses local names of its own to keep track of information. These local names are considered reserved and, if you use them for your own references, may cause problems. Highlight an area on a sheet and select, from the Excel menu, File, Print Area, Set Print Area. As shown in Figure 7.7, a Print_Area listing is in the range name field. Deselect the area and look again in the range name field. The name is still there select it and the print area previously set is now highlighted. If you save,...

About Tushar Mehta

Tushar Mehta, MPPM, Ph.D., an independent consultant based in Rochester, NY, specializes in developing solid business solutions that weave together technology, organizational architecture, marketing, and operations. The financial and economic impact of these solutions is measurable along the dimensions of increased productivity, efficient marketing, revenue growth, lower operating costs, and improved value for the client's customers. Tushar has received the Most Valuable Professional (MVP)...

1

Each action that you perform in the Excel user interface may equate to one or more lines of recorded code. Some actions might generate a dozen lines of code. It is always a good idea to test out your macro. Return to the regular Excel interface, using Alt+F11. Close Invoice.txt without saving any changes. You still have MacroToImportInvoices.xls open. Press Ctrl+i to run the recorded macro. It works beautifully The data is imported, totals are added, bold formatting is applied, and the columns...

Measuring Order Lead Time by Grouping Two Date Fields

Recall in the last section how Excel adds field names when you group by month and year. The less-aggregated measure (month) inherits the name of the original field. Any more-aggregated measures receive the name of the grouping for example, Years. Your manufacturing plant may be concerned with a measure of how far in advance the orders are received. If the plant has a 12-week lead time to procure components, they would love to have all orders placed 13 weeks in advance. When this doesn't happen,...

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

Using Pivot Table Show Detail to Filter a Recordset

Take any pivot table in the Excel user interface. Double-click any number in the table. Excel inserts a new sheet in the workbook and copies all the source records that represent that number. In the Excel user interface, this is a great way to ad-hoc query a data set. The equivalent VBA property is called ShowDetail. By setting this property to True for any cell in the pivot table, you will generate a new worksheet with all the records that make up that cell PT.TableRange2.Offset(2,...

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

Replacing Many A Formulas with a Single RC Formula

After you get used to R1C1-style formulas, they actually seem a lot more intuitive to build. One classic example to illustrate R1C1-style formulas is building a multiplication table. It is easy to build a multiplication table in Excel using a single mixed reference formula. Enter the numbers 1 through 12 going across B1 M1. Copy and transpose these so the same numbers are going down A2 A13. Now the challenge is to build a single formula that will work in all cells of B2 M13 and that will show...

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

Info

Dim WBO As Workbook Dim WBC As Workbook Set WBO ActiveWorkbook ThisFile C inventory.csv Workbooks.OpenText Filename ThisFile Cells.Copy If Cells 65536, 1 .Value gt 1111 Then ' The file was too large. Import again, starting at row 32767 Workbooks.OpenText Filename ThisFile, StartRow 32767 1 There are some rows that have already been copied ' Everything from 32767 to 65,536 is already on the data sheet. 1 Delete the first 32,770 rows from this second pass of the file RowsToSkip Application.Rows 1...

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

Hide the Formula

The Excel formula bar drops down and covers cells when a cell containing more than 50 characters is selected. The following sample prevents this from happening by hiding the formula bar. In Figure 13.14, the formula bar would normally obscure most of this worksheet. Thanks to the following macro, the formula bar is hidden Normally,the formula bar would obscure most of this worksheet.The macro causes the formula bar to be selectively hidden whenever the cell contains more than 50 characters....

Checking for the Existence of a Name

The following function can be used to check for the existence of a user-defined name, even a hidden one, but does not return the existence of Excel's reserved names. It's a handy addition to your arsenal of programmer's useful code Function NameExists FindName As String As Boolean Dim Rng As Range Dim myName As String On Error Resume Next myName ActiveWorkbook.Names FindName .Name If Err.Number 0 Then NameExists True End Function The preceding code is also an example of how to use errors to...

Tb Empposition.value

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

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

How To Draw In Vba Excel

Vba Excel Drawing

XY points were plotted in a scatter chart to draw this shape. XY points were plotted in a scatter chart to draw this shape. The concept of using XY charts for drawing has been perfected by Mala Singh of XLSoft Consulting in India. Mala has been able to use XY charts to draw just about anything. This blueprint is actually an XY chart in Excel see Figure 10.21 . This blueprint is actually an XY Chart in Excel. It takes about 25 seconds to draw the chart. This blueprint is actually an XY Chart in...

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

Separate Delimited String

You need to paste a column of delimited data. You could use Excel's Text to Columns, but you need only an element or two from each cell. Text to Columns parses the entire thing. What you need is a function that lets you specify the number of the element in a string that you need, as shown in Figure 4.13. StringElement str,chr,ind str The string to be parsed. chr The delimiter. ind The position of the element to be returned. Function StringElement str As String, chr As String, ind As Integer...

Using Auto Sort to Control the Sort Order

Pivottable Field Advanced Options

The Excel user interface offers an AutoSort option that enables you to show customers in descending order based on revenue. It is possible to create pivot tables for years without ever finding this setting. In the Excel user interface, double-click the Customer button in the pivot report. This brings up the PivotTable Field dialog, as shown in Figure 12.12. This dialog is the gateway to the AutoSort feature. This dialog is the gateway to the AutoSort feature. From the PivotTable Field dialog,...

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

The Default Chart Type

You will notice that code does not specify the chart type being created, yet a column chart was produced. On my machine, the default chart type for Excel is a column chart. This is an important feature of Excel VBA. Defaults that are saved as Excel settings need not be explicitly specified in VBA code. This can work for you or against you. On the positive side, if you need to create many charts of one particular type, you can change the default chart type on your machine and produce code to...

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

Errors Caused by Different Versions

Microsoft does improve VBA in every version of Excel. Pivot table creation was drastically improved between Excel 97 and Excel 2000. Certain chart features were improved between Excel 97 and Excel 2000. The TrailingMinusNumbers parameter is new in Excel 2002. If you write code in Excel 2002 or Excel 2003 and then send the code to a client with Excel 2000, that user gets a compile error as soon as she tries to run any code in the same module as the offending code. Consider this application with...

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

Publishing Data to a Web Page

This chapter has highlighted many ways to capture data from the Web. It is also very useful for publishing Excel data back to the Web. In Chapter 13, Excel Power, a macro was able to produce reports for each region in a company. Rather than printing and faxing the report, it would be cool to save the Excel file as HTML and post the results on a company intranet so that the regional manager could instantly access the latest version of the report. Consider a report like the one shown in Figure...

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

Caution

For the remainder of your Excel session, Excel will remember the delimiter settings.There is an annoying bug feature in Excel.After Excel remembers that you are using a comma or a tab as a delimiter,any time that you attempt to paste data from the clipboard to Excel, the data is parsed automatically by the delimiters specified in the OpenText method.Thus, if you attempted to paste some text that included the customer ABC, Inc., the text would be parsed automatically into two columns,with text...

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

List Files in a Directory

Oliver of Minneapolis, MN. Nathan is a financial consultant and application developer. This program returns the filename, size, and date modified of all files in the selected directory and its subfolders Dim i As Long, z As Long, ws As Worksheet, y As Variant Dim fLdr As String y Application.InputBox Please Enter File Extension, Info Request If y False And Not TypeName y String Then Exit Sub Application.ScreenUpdating False fLdr BrowseForFolderShell With...

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

Export Data to Word

This program transfers data from Excel to a Word document. It uses early binding, so a reference must be established in the VBE Tools, References to the Microsoft Word Object Library. Sub Export_Data_Word_Table Dim wdApp As Word.Application Dim wdDoc As Word.Document Dim wdCell As Word.Cell Dim i As Long Dim wbBook As Workbook Dim wsSheet As Worksheet Dim rnData As Range Dim vaData As Variant Set wsSheet Set rnData .Range A1 A10 End With 'Add the values in the range to a one-dimensional...

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

The Range Object

The following is the Excel object hierarchy Application Workbook Worksheet Range The Range object is a property of the Worksheet object. This means it requires that either a sheet be active or it must reference a worksheet. Both of the following lines mean the same thing if Worksheets l is the active sheet Range A1 There are several ways of referring to a Range object Range Al is the most identifiable because that is how the macro recorder does it. But each of the following is equivalent Cells...

The SecretIt Is Not That Amazing

Remember that Excel does everything in R1C1-style formulas. Excel shows addresses and formulas in A1 style merely because it needs to adhere to the standard made popular by VisiCalc and Lotus. If you switch the worksheet in Figure 6.5 to use R1C1 notation, you will notice that the different formulas in D4 D9 are all actually identical formulas in R1C1 notation. The same is true of F4 F9 and G4 G9. Use the Options dialog to change the sample worksheet to R1C1-style addresses. If you examine the...

The VBA Loop For Each

This is an excellent loop and the macro recorder never records this type of loop. VBA is an object-oriented language. It's common to have a collection of objects in Excel, such as a collection of worksheets in a workbook, cells in a range, PivotTables on a worksheet, or data series on a chart. This special type of loop is great for looping through all the items in the collection. Before discussing this loop in detail, you need to understand a special kind of variable called object variables.

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

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

Creating a Collection in a Class Module

Collections can be created in a class module but, in this case, the innate methods of the collection Add, Remove, Count, Item are not available they have to be created in the class module. The advantages of creating a collection in a class module are that the entire code is in one module, you have more control over what is done with the collection, and you can prevent access to the collection. Insert a new module for the collection and rename it clsEmployees. Declare a private collection to be...

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

Referencing Ranges in Other Sheets

Switching between sheets by activating the needed sheet can drastically slow down your code. Instead, you can refer to a sheet that is not active by referencing the Worksheet object first This line of code references Sheet1 of the active workbook even if Sheet2 is the active sheet. If you need to reference a range in another workbook, then include the Workbook object, the Worksheet object, and then the Range object Be careful if you use the Range property as an argument within another Range...

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

Advanced Filter Is Easier in VBA Than in Excel

The arcane Advanced Filter command is so hard in the Excel user interface that it is pretty rare to find someone who enjoys using it regularly. I predict that Microsoft will totally rework this command for Excel 2005 to make it easier to use. However, in VBA, advanced filters are a joy to use. With a single line of code, you can rapidly extract a subset of records from a database or quickly get a unique list of values in any column. This is critical when you want to run reports for a specific...

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 the Top Left and Bottom Right Corners of a Selection to Specify a Range

There are two acceptable syntaxes for the Range command. To specify a rectangular range in the first syntax, you specify the complete range reference just as you would in a formula in Excel In the alternative syntax, you specify the top-left corner and bottom-right corner of the desired rectangular range. In this syntax, the equivalent statement might be Range A1, B5 .Select For either corner, you can substitute a named range, the Cells function, or the ActiveCell property. This line of code...

The Chart AreaVBA Name Chart Area

ChartArea is the container for all the other elements of a chart, including the plot area, axes, legend, series, data labels, and so on. The most common action performed with the chart area is modifying its format border, fill color, and pattern and the font settings for the entire chart, including chart and axis titles, legend, and data labels. Take any existing chart that is loaded on a chart sheet. Turn on the macro recorder and use the Excel user interface to format the chart by...

Read Entire CSV to Memory and Parse

Submitted by Suat Mehmet Ozgur of Istanbul, Turkey. Suat develops applications in Excel, Access, and Visual Basic for MrExcel.com and TheOfficeExperts.com. This sample takes a different approach to reading a text file. Rather than read one record at a time, the macro loads the entire text file into memory in a single string variable. The macro then parses the string into individual records. The advantage of this method is that you access the file on disk only once. All subsequent processing...

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