Pagebanner

ThreeWave Welcome

ShortFadeBar

This web site is brought to you as a public service by Chip Pearson and Pearson Software Consulting, LLC. We provide complete consulting and custom application design and development for Excel, Office, Visual Basic, and the NET Framework in VB.NET and C#. For more information about how to turn Excel into a powerful application platform, contact Chip Pearson at (913) 549-4658.

This web site contains about 300 individual topic pages that together cover all aspects of Excel, from simple formulas through Automation and COM Add Ins and into the NET Framework. The content is aimed at the intermediate level user although there is plenty of content for both beginners and advanced level users. Much of the content is VB/VBA code, and those pages assume that you are conversationally familiar with VB/VBA programming. I have been a professional computer programmer for almost twenty years, ten years of that in the Windows/VB/VBA/Excel world. The topics presented on the site are drawn from real world experience. There are approximately 200 downloadable files, which include sample workbooks, VB/VBA modules, VB Project source code and compiled DLL files.

The content of the site provides you with the tools you need to create workbooks and VBA projects. Since it is impossible to be all things to all people, the workbooks, formulas, and code examples are rather generic, allowing you to readily customize them for your own particular needs. The formulas and appearing on this site should work in any version of Excel, version 97 or later, unless noted otherwise. Most of the VBA code should work in Excel 97 and later, but I do make use of the enhanced features of VBA version 6, which was introduced with Excel 2000.

Help Logo


MVP New Logo

Microsoft
Most Valuable Professional
Excel
1999 - 2008


Page Index

Topic Index

Search The Site

What's New


Contact Chip Pearson
chip@cpearson.com
Before you send me an email,
read this page.

Phone:
(913) 549-4658
Note: This phone is for existing and new clients only. If you call, please be prepared to pay for 1/2 hour of consulting time.
Please call after 9:00 AM USA Central Time Zone.

What's New On The Site
The list below shows only the ten or so most recent additions to the web site out of a total of about 500 individual topic pages. For a complete chronological list of updates, see the What's New page. You can also locate pages from the Page Index or the Topic Index.  (Last Updated 14-June-2009)

String Concatenation (14-June-2009)
Excel's built-in CONCATENATE function is utterly worthless. It doesn't do anything that the simpler & operator doesn't do, and it is longer to type. Using either the CONCATENATE function or the & operator, you cannot use array formulas to build up strings on the fly, nor can you string together the contents of all the cells in a range. This page describes a new and improved version of the previous StringConcat function that provides great flexibility in its input parameters and can be used in array formulas to build strings on the fly.

Defined Names In Excel (6-June-2009)
This page introduces Defined Names and illustrates how to use them to your advantage in your workbooks.

Downloading A File From The Internet (23-April-2009)
This page describes VBA code you can use to download a file from the internet to your local computer.

Flexible Lookups - Alternative To VLOOKUP (3-April-2009)
Excel's VLOOKUP and HLOOKUP functions are great for simple table lookups. However, they suffer the shortcomings that you can only search the left column, return a value only from a column to the right, and return only a single value. The formulas on this page describe how to use any column as the search column, any column for the returned values, and return multiple value from the table.

Unique Identifiers With GUIDs (20-March-2009)
Your application may need to create unique indentifiers to track objects or data values. If you need a unique identifier but you don't need that identifier to contain any meaningful information beyond simply being unique, you can use a Globally Unique Identifier or GUID. This value will be unique across all users, all computers, and all networks. This page describes the code to create a GUID.

Fractional Months (19-March-2009)
Some Excel applications need to calculate the fractional months between two dates. This is somewhat ambiguous because a month may have between 28 and 31 days. This page describes methods and formulas for calculating fractional months.

Merging Lists (17-March-2009)
A common task in Excel is to merge two lists into a single list, usually preventing duplicate entries in the merged list. The code on this page describes how to do this.

Playing A Sound In VBA (23-February-2009)
This page describes how to play sounds from VBA. You can use your own sound files or use one of the files provided by Windows in the C:\Windows\Media directory.

Pictures On Command Bar Buttons (23-February-2009)
This page describes how to put custom images on command bar buttons and menu items. You can use either external image files or images embedded on a worksheet.

EveryNth Row (20-January-2009)
This page describes how to retrieve every Nth row from a column of numbers, such as every other or every third row. It also describes how to use SUM and AVERAGE on these data sets.

AnyXML - Creating optional free-form XML content in a schema. (20-January-2009)
This page describes how to write XSD Schemas for XML to allow optional, arbitrary XML to be included in a ComplexType. There is no restriction on this XML other than it be syntactically well-formed.

Working With Latitude, Longitude, And Great Circles (18-January-2009)
This page describes formulas and code for working with latitude and longitude values in either time-format value or decimal degree values. It also contains formulas and code for calculating Great Circle Distances between two points on Earth.

Creating A Table From A Column With Variable Block Sizes (16-December-2008)
This page describes VBA code that you can use to create a two-dimensional table from a column of blocked data where the data blocks are variable lenght.

Using Attributes To Describe Code In The Object Browser (16-December-2008)
You can add description attributes to modules and procedure to provide documentation in the Object Browser.

Working With The Windows Clipboard (16-December-2008)
This page describes VBA procedures for working with the Windows clipboard. It includes a downloadable module with several useful procedures for putting text in the clipboard and getting text out of the clipboard.

FindAll XLA Add-In (13-December-2008)
This page describes the FindAll.xla add-in that allows you to search any number of worksheets in a workbook and display all the results in a simple to use user interface.

Convert A Table To A Column Or Row (11-November-2008)
This page describes formulas to transform a two dimensional table of rows and columns into a single row or single column of data, in either row-by-row or column-by-column order.

Convert A Column To A Table (11-November-2008)
It is not an uncommon task to convert a column of data, grouped into logical blocks, to a two dimensional table of rows and columns. This page describes how to do this with formulas and with VBA code.

Testing If An Array Is Sorted (9-November-2008)
Sorting an array is an expensive operation in terms of memory access and processing overhead, and this problem only gets worse as the array gets larger. This page describes how to test is an array is already in sorted order and therefore does not need to be sorted again. In conjunction with this page, the Sorting Arrays page has been rewritten.

Get What's New via RSS Feed (20-Oct-2008)
You can now get What's New On The Web Site information via an RSS feed. Point your RSS aggregator or your browser to http://www.cpearson.com/RSS.xml.

How To Use Support Newsgroups Properly (19-Sept-2008)
One of the best avenues to get support and answers for Excel is through the NNTP USENET system of the Internet. This page desribes how to connect to the Excel newsgroups and provides a list of guidelines to follow when posting messages to the nwesgroups.

   

This page last update: 5-January-2008.

Created by Chip Pearson at Pearson Software Consulting, LLC
Email: chip@cpearson.com Before emailing me, please read this page
http://www.cpearson.com/excel/MainPage.aspx
Copyright © 1997 - 2009, Charles H. Pearson

Submit bug information or errors on the Bug And Error Report Page.



 


sectionbreak
Essential Tools For Developers

  

Ready

Advertise Your Product On This Site