Bookmark and Share

Enter your email address:

Delivered by FeedBurner

Home Page

Subscribe here
Add to My Yahoo!
This page is powered by Blogger. Isn't yours?

Host your Web site with PureHost!

eXTReMe Tracker

  Wednesday, March 03, 2010 – Permalink –

Mail Excel

VBA + sample

Excel Guru Ron de Bruin has put together the VBA code needed to send Excel via email.
He has also included the sample workbooks for those that are not VBA literate.

". . . a new add-in named RDBMail for Excel/Outlook 2007-2010

The add-in create a new tab on the Ribbon named RDBMail with a lot of mail options.
You have the option to send as workbook or PDF for every mail option."

Code to send mail from Excel

See all Topics


<Doug Klippert@ 3:39 AM

Comments: Post a Comment

  Monday, March 01, 2010 – Permalink –

Video Tutorials

What you see

" is The Online Knowledge Magazine that publishes information that everyone should know, wants to know, or forgot. was created in 2001 by Greg Renza with the desire to eradicate the world's stupidity."
A few of the lessons include:
  • Concatenating Cells
  • Conditional Formatting
  • Subtotals
  • Data Sorting
  • Formulas and Named Cells
  • Create Graphs and Charts

See all Topics


<Doug Klippert@ 3:40 AM

Comments: Post a Comment

  Saturday, February 20, 2010 – Permalink –

Selection Address

What's the count

". . . know what range is selected at any given time. You can look at the Name Box, but that only show the active cell."

Display number of Rows and Columns

See all Topics


<Doug Klippert@ 3:58 AM

Comments: Post a Comment

  Thursday, February 18, 2010 – Permalink –

Custom QAT

Access additions

Applications put most of the most-used commands on the Home tab's Ribbon, not everything is there. You may want to add Close, Close All, or Print commands, for example.

In the upper Left corner is the Quick Access Tool bar.

To update the QAT:
Click the down-pointing arrow to the right of the QAT.
Choose any common commands (New, Close, Print, etc.) by checking the option.

See all Topics

Labels: , , , ,

<Doug Klippert@ 3:28 AM

Comments: Post a Comment

  Friday, February 12, 2010 – Permalink –

Custom Toolbars

You’re not restricted

You can create your own toolbars. Here's some code that helps:

Toolbars for Fun and Profit

See all Topics


<Doug Klippert@ 3:07 AM

Comments: Post a Comment

  Thursday, February 04, 2010 – Permalink –

Command Reference


Those of you that are just now making the switch to the Ribbon world, will find this valuable.

See all Topics

Labels: , , ,

<Doug Klippert@ 3:09 AM

Comments: Post a Comment

  Tuesday, February 02, 2010 – Permalink –

Office Training


TechRepublic lists a number of areas that you might explore when training is needed for a new Office version.

Here are a few:

    "Instead of telling your users to go out to and do a search, put hyperlinks to the printer-friendly version of tip sheets and articles on your company's main portal page. Providing links to information you know they need will help you cover the training bases. And presenting the links on an internal web site they already use will show your users that it's okay to go outside of their four firewalls to learn something new. Include your favorite hyperlink in your signature line so it goes out in every e-mail you send."
10 ways to train your users on Office 2007 for free

See all Topics

Labels: , , , ,

<Doug Klippert@ 3:51 AM

Comments: Post a Comment

  Monday, February 01, 2010 – Permalink –

Tips for Word and Excel

Also some Windows hints

This site has useful information about:

  • Word
  • Word VBA
  • Excel
  • Excel VBA

  • Windows

See all Topics

Labels: ,

<Doug Klippert@ 3:29 AM

Comments: Post a Comment

  Wednesday, January 27, 2010 – Permalink –

Color Scales

Conditional colors

Here is some information on Conditional color scales in Office 2010.

Microsoft Excel 2010 Blog 1

Microsoft Excel 2010 Blog 2

Microsoft Excel 2010 Blog 3

See all Topics


<Doug Klippert@ 3:40 AM

Comments: Post a Comment

  Tuesday, January 26, 2010 – Permalink –

Where's the Template

Find and/change storage spots

Describes the different template categories and the locations of templates in 2007 Office programs. Also describes the registry settings that control where to find your custom templates.

See all Topics

Labels: , , ,

<Doug Klippert@ 3:44 AM

Comments: Post a Comment

  Wednesday, January 20, 2010 – Permalink –

Help ID's

VBA code

When you build a macro, you can call up information from the Excel Help file.
Ron DeBruin has the information needed through 2007.

Right clicking the Helpfile and choosing Properties will show the HP####### number in 2010.

Help Context IDs for Excel

See all Topics


<Doug Klippert@ 3:58 AM

Comments: Post a Comment

  Wednesday, January 13, 2010 – Permalink –

Text Files to Excel

A hard way to do an easy job

Text files can be imported or just pasted into Excel. If you want to learn more about SQL statements and non-code coding, look at this blog:

"Excel doesn't have an easy way to append multiple text files into one worksheet through the user interface. From time to time I hear customers asking how to do this in an easy way. Do you have to use Access or VB code to solve this problem? No. There's a way to do it in Excel by using a simple SQL statement in the connection string."

MSDN Excel Blog

See all Topics


<Doug Klippert@ 4:51 AM

Comments: Post a Comment

  Sunday, January 03, 2010 – Permalink –

Keyboard and Key Tips

Finger it out

2007 apps look different because of the ribbon, but the keyboard can still be used to speed up tasks.
Microsoft has an online course that may help

After completing this course you will be able to:
Accomplish tasks by using sequential shortcut keys, known as Key Tips, shown on the Ribbon.
Navigate around the Ribbon using the TAB key and arrow keys.
Accomplish tasks by using key combinations — keys you press at the same time - exactly as you've done in previous versions of Office.

See all Topics

Labels: , , , ,

<Doug Klippert@ 3:52 AM

Comments: Post a Comment

  Saturday, January 02, 2010 – Permalink –

New Calendars

Another year

Here are free Excel calendars for downloading.

Twelve months on one sheet or one month per tab.

See all Topics


<Doug Klippert@ 3:21 AM

Comments: Post a Comment

  Wednesday, December 30, 2009 – Permalink –

Chart Null Data Gaps

Fill in the spaces

When creating a chart, data can sometimes be missing. You have a choice of leaving the cells empty or making them zero.

By default, an empty cell will leave gaps in your graph. Zero entries will send a line graph down to the base line.

One way to force Excel to interpolate the data is to enter the function =NA() in the empty cells.

Another way is to go to Tools>Options and choose the Chart tab.

Pick how you want data plotted - with gaps, as zero, or interpolated.

Microsoft also has this suggestion:

Gaps between the dates
"If the datasheet data for the category axis contains date number formatting, Microsoft Graph automatically uses a special type of axis in your chart called a time-scale axis.
A time-scale axis shows a blank category for dates for which you have no data. If you do not want to see these gaps — for example, if you have data for 1-Jan, 15-Jan, 3-Feb, 12-Feb, and 2-Mar, and you want to plot the days next to each other - you can change the time-scale axis to a standard category axis.
On the Chart menu, click Chart Options, click the Axes tab, and then click Category under Category (X) axis."

Note, For 2007+, on the Design tab, click Select Data in the Data area, and then click Hidden and Empty Cells on the Select Data dialog box.

[Edited entry from 12/30/2006}

See all Topics


<Doug Klippert@ 3:14 AM

Comments: Post a Comment

  Tuesday, December 22, 2009 – Permalink –

Link Workbooks

Tie them together

Excel is a flatfile database, but you can do some Access kinds of relationships.

"A link is a formula that gets data from a cell in another workbook. When you open a workbook that contains links (a linking workbook), Microsoft Excel reads in the latest data from the source workbook or workbooks (updates the links).

Use links when you want to maintain information in a single workbook, but also use the information in other workbooks. For example, if your product prices change frequently, you might keep a master price list.
Other workbooks that use the price data in calculations, such as purchase orders, inventory valuations, or sales estimates, can create links to the price list workbook so calculations always use current prices. When prices change, you only have to enter the new prices in one place."

  1. Open both workbooks.

  2. In the source workbook, select the cells you want to link to and click the Copy button.

  3. Switch to the destination workbook and click the upper left cell of the range where you want the links.

  4. On the Edit menu, click Paste Special, and then click Paste Link.
Create and Manage Links

[Edited entry from 12/22/2006]

See all Topics


<Doug Klippert@ 3:07 AM

Comments: Post a Comment

  Monday, December 14, 2009 – Permalink –

Have a Geeky Chrstmas

And a Functional New Year

It is rather late in the season, but this is a gift that gives all year long:
This hilarious clock is the perfect accessory for any Excel power user. Each numeral has been replaced with a suitable Excel function that will evaluate to that numeral.

We've used one function from as many function categories as possible to really provide a broad range of functions.

For instance:
=FACT(3) - The FACT() function returns the Factorial of a number. The Factorial of 10 is 10x9x8x7x6x5x4x3x2x1. This function is great for statisticians calculating combinations and permutations. In our case, the Factorial of 3 is 3x2x1 or 6

Excel Function Clock

[Edited entry from 12/14/2006]

See all Topics


<Doug Klippert@ 3:18 AM

Comments: Post a Comment

  Saturday, December 12, 2009 – Permalink –

Display Row, Column Headings

User Function

Here's an odd little use of functions.

If you want to display the Row number on a spreadsheet, the formula
works just fine.
You could then hide the Row and Column headings and format the Row numbers any way you want. If a Row is deleted the numbers will automatically update.

Column headings are a little harder. The formula =Column() will show the number of the Column, not the letter, i.e. "2" instead of "B".

The following formula extracts the Column letter:


To break it down:


This finds the address at Row number "1" and current Column number. The abs_num of "4 " says make the result a relative address.

The formula will produce a result such as "AA1".


This function looks at the address, i.e. "AA1".
It replaces the Row number character ("1") with a null or empty value ("").
The formula will produce a result such as "AA".

Also see Daily Dose of Excel by Dick Kusleika.

Dick mused:'

"Sometime before the year 3,000, Microsoft will hopefully increase the number of columns in Excel (Hey, I can dream can't I). The challenge before you is to write a function that converts a column number to its letter equivalent assuming columns go to ZZZZ. That’s about 450,000 columns - maybe more than I need."

Of course Office 2007-10 has taken it up to 16,284 columns.

[Edited entry from 12/10/2006]

See all Topics


<Doug Klippert@ 3:08 AM

Comments: Post a Comment

  Wednesday, December 09, 2009 – Permalink –


Quick graphic reinforcement

A graph or chart can give the reader a visual representation of a great deal of data. Concepts or results can be more easily grasped by a well formatted graphic.

Charts, usually, take up more space in a document than is absolutely required.

Edward Tufte has come up with the concept of Sparklines (Sparklines:Intense, Word-sized Graphics)
These are small graphs about the same height and width as common words. They are not out of place in the text of a document.

Sparklines give the reader a snapshot of the data that quickly supports the material being discussed.

Bisantz Sparklines

The Sparkmaker can create Sparklines for Word, Excel, or PowerPoint. They can also be produced in HTML.

[Edited entry from 12/6/2006]

See all Topics

Labels: , , ,

<Doug Klippert@ 3:25 AM

Comments: Post a Comment

  Sunday, December 06, 2009 – Permalink –


and More

Gabriels Horn from the Curve Bank
Famous curves from Cal State at LA. All your favorites from Astroid to Witch of Agnesi.

Here is a collection of Functions relating to astronomy from

Can't tell who might be interested in the obliquity of the equator given date in days after J2000.0. See: Astro VBA

Other Curve stuff: converting polar coordinates to Cartesian coordinates.
"Students of analytic geometry, (the kind that combines algebra and geometry), often work in one of two coordinate systems: Cartesian or Polar - and frequently must convert from one to the other.

The Cartesian system locates points on a plane by measuring the horizontal and vertical distances from an arbitrary origin to a point. These are usually denoted as a pair of values (X, Y).

The Polar system locates the point by measuring the straight line distance, usually denoted by R, from the origin to the point and the angle of an imaginary line from the origin to the point, θ, (Greek letter Theta), measured counterclockwise from the positive X axis."

[Edited entry from 12/2/2006]

See all Topics


<Doug Klippert@ 3:00 AM

Comments: Post a Comment

  Friday, December 04, 2009 – Permalink –

Password Background


Alan Myrvold has written a background article on how Office handles passwords and what password strength means.

"Word, Excel, and PowerPoint have been able to password protect documents for several versions by setting the 'password to open'. What we felt could be improved was the ability to enforce password strength rules, similar to what may be required when logging into your computer at work."

Enabling password rules for Office 2010

See all Topics

Labels: , ,

<Doug Klippert@ 3:51 AM

Comments: Post a Comment

  Sunday, November 29, 2009 – Permalink –

New Conditional Formatting

Much more capabilities

Pre-2007 Excel was limited to only 3 conditions. The new Office is more generous and versatile.
Here are some of the features:

Format all cells based on their values
Use this to create a data bar, 2-color or 3-color color scale, or icon set rule.
Format only cells that contain:
Use this to create the Excel 2003-style rules and more (format cells greater than, less than, greater than or equal to, less than or equal to, equal to, not equal to, between, not between). This is also the entry point to create rules of type: specific text, date occurring, blanks, non-blanks, errors, non-errors.
Format only top or bottom ranked values:
Use this to create top n, top n%, bottom n, bottom n% rule types.
Format only values that are above or below average:
Use this to create above average, below average, 1 or 2 or 3 standard deviation above, or 1 or 2 or 3 standard deviation below rule types.
Format only unique or duplicate values:
Use this to create rules that format unique or duplicate values.
Use a formula to determine which cells to format:
Use this to create Excel 2003-style rules where you can enter a formula to determine whether a format should be applied.

2007+ Conditional Formatting

[Edited entry from 11/28/2006]

See all Topics


<Doug Klippert@ 3:46 AM

Comments: Post a Comment

  Tuesday, November 24, 2009 – Permalink –

Formatting Overview

Looking good

The judicious use of formatting can make data easier to understand as well as pleasant to see.
Scott Lowe put together a series of articles on how to format data in Excel.

The Articles are on

Anatomy of Excel formatting: Part 1
  • Boldface, italicize and underline cell content
  • Change the size and font of your text
  • Apply a default Excel style (i.e. dollar, percent, etc) to cells
  • Use date and time formatting in your spreadsheet
  • Apply shading
Anatomy of Excel formatting: Part 2
  • Apply borders
  • Resize rows
  • Resize columns
Anatomy of Excel formatting: Part 3
  • Text formatting
  • Justify cell contents
  • Change the direction of the text in your spreadsheet
  • Word wrap text
Anatomy of Excel formatting: Part 4
  • Automatically format cells based on their contents
  • Change the margins for your printed page
  • Add a header and footer to your printer spreadsheet

[Edited entry from 11/20/2006]

See all Topics


<Doug Klippert@ 3:37 AM

Comments: Post a Comment

  Sunday, November 15, 2009 – Permalink –

Color News

A multidiscipline subject

Here is a study about how color effects a reader's choice of concentration.

It was intended for newspaper publishers, but the same knowledge can be used in Web design, PowerPoint, or any other reporting application. Word and Excel will also benefit.

Color, Contrast, and Dimension in News Design


The Poynter Institute is a school for journalists, future journalists, and teachers of journalists.

[Edited entry from 11/8/2006]

See all Topics

Labels: , , , , , , ,

<Doug Klippert@ 3:01 AM

Comments: Post a Comment

  Thursday, November 05, 2009 – Permalink –

Change Code to Comments

Fast solution

When you're testing procedures, you can temporarily convert a block of VBA code to comments that will be ignored during a trial run.

Doing so manually by inserting an apostrophe before each line of code can be a real chore.

To simplify this task,
  1. Open any module in the Visual Basic Editor (VBE)
  2. Choose View >Toolbars>Edit from the menu bar to display the Edit toolbar.
  3. Select the lines of code that you want to turn into comments.
  4. Click the Comment Block button on the Edit toolbar (it's the sixth button in from the right end of the toolbar).
Each line of the selected code is now preceded with an apostrophe. To convert the comments back to executable code, select the appropriate lines and click the Uncomment Block button, which is immediately to the right of the Comment Block button.

[Edited entry from 10/27/2006]

See all Topics

Labels: , , ,

<Doug Klippert@ 3:39 AM

Comments: Post a Comment

  Wednesday, November 04, 2009 – Permalink –

Paste is Special

Versatile functions

If the data you brought into Excel comes through as text rather than numbers, Paste Special can fix it.

  1. Go to an empty cell.
  2. Copy it
  3. Select the "corrupted" data.
  4. Go to Edit>Paste Special and choose Add.
This works better than multiplying by one. Empty cells remain empty

What's So Special About "Paste Special"?

Excel Paste Special function

Pasting Using Paste Special

[Edited entry from 10/27/2006]

See all Topics


<Doug Klippert@ 3:04 AM

Comments: Post a Comment

  Thursday, October 29, 2009 – Permalink –

Hep Me

It's EZ

Thomas Brunt's has a number of web developer tips.

Shirley Wood wrote an article about "Spell checking your web site"

FrontPage uses the same dictionary as Word and Excel.

A spell checker, however, only goes so far:

Eye halve a spelling checker
It came with my pea sea
It plainly marcs four my revue
Miss steaks eye kin knot sea.
Eye strike a key and type a word
And weight four it two say
Weather eye am wrong oar write
It shows me strait a weigh.
As soon as a mist ache is maid
It nose bee fore two long
And eye can put the error rite
It's rare lea ever wrong.
Eye have run this poem threw it
I am shore your pleased two no
Its letter perfect awl the weigh
My checker tolled me sew.

[Edited entry from 10/14/2006]

See all Topics


<Doug Klippert@ 3:11 AM

Comments: Post a Comment

  Sunday, October 25, 2009 – Permalink –

Hep Me

Help topic locations

This from Ron de Bruin:

"Using the Help Context IDs

To make use of this file(s), locate the help topic of interest and make a note of its context ID.

Then, write a VBA subroutine that displays the topic.

You can attach the subroutine to a button, or provide some other way for the user to execute the sub.

For example, if you'd like to display the help topic that show the Date/Time Functions List,
you'll find that the context ID for that particular topic is 5199659.

The following VBA procedure displays that help topic."

Sub ShowHelp()
Application.Help "XLMAIN" & Val(Application.Version) & ".CHM" ,5199659
End Sub

Help Context IDs for Excel

[Edited entry from 10/15/2006]

See all Topics


<Doug Klippert@ 3:06 AM

Comments: Post a Comment

Export Formatted Sheets

Access to Excel

Access provides an easy way to export data to Excel through the Office Links feature.

To use this feature, simply select a relevant database object and choose Tools>Office Links> Analyze It With Excel.

The worksheet Excel creates includes some minor formatting applied to the field headings that appear in row 1. Some formatting in your original Access database affects the worksheet cell formatting as well. For example, if you're exporting from a datasheet, gridline and font attributes are carried over to Excel. If you use the Office Links feature to export data behind a form, text box shading and font properties are applied.

The final result in Excel may not exactly match your Access data; however, you'll probably find that less work is required to get your Excel version of the data into an easily readable state.

In Office 2007-10 it's External Data>Excel

[Edited entry from 10/10/2006]

See all Topics

Labels: ,

<Doug Klippert@ 3:05 AM

Comments: Post a Comment

  Thursday, October 22, 2009 – Permalink –

Move Using Alt

One sheet to another

To move data from one worksheet to another, highlight the data.

Hold down the ALT key and move the mouse until the pointer arrow is on the border of the selection.

Drag the selection down to the destination worksheet tab.

When the arrow touches the tab, Excel switches to the desired worksheet.

Now drag the selection to the correct position. Let go of the mouse and then the ALT key.

To copy data hold down the CTRL+ALT keys and perform the steps above.

BTW the screen will not scroll while you hold the ALT key down.

[Edited entry from 10/6/2006]

See all Topics


<Doug Klippert@ 3:55 AM

Comments: Post a Comment

  Saturday, October 17, 2009 – Permalink –

Shortcut Borders

Keyboard trick

Use Ctrl+1 to bring up Excels Formatting dialog box. Shift+B will take you to the Borders tab.

Now you can use the following key strokes (2002+):

Apply or remove the top border.
Apply or remove the bottom border.
Apply or remove the left border.
Apply or remove the right border.
Apply or remove the downward diagonal border.
Apply or remove the upward diagonal border.
Apply or remove the horizontal interior border.
Apply or remove the vertical interior border.
outlines the cells.
give interior (vertical and horizontal).
removes all borders.
brings up the color palette.

Shortcuts for Applying Borders

Ctrl+Shift+7 will outline a cell without having to display the Format dialog.

[Edited entry from 9/29/2006]

See all Topics


<Doug Klippert@ 3:26 AM

Comments: Post a Comment

  Sunday, October 11, 2009 – Permalink –

Update Excel on the Web

Auto Republish

You can save an Excel file as a Web page and makes it easy to update data in a worksheet that has already been saved to the Web.

Here is how to save an Excel file as a Web page and set it up it for automatic updates:
  1. Click Save As Web page from the File menu. (click the Office logo in 2007)

  2. In the Save As dialog box, click the Publish button. (click Publish, Create Document Workspace in 2007)

  3. Use the drop down arrow beside Choose to select what you want to publish.

  4. In the File name field, enter a file name.

  5. Place a check beside Auto Republish every time this worksheet is saved. This way the data will be updated each time you make changes to the worksheet.

  6. Select Open published page in Web browser to view the data in your Web browser after you click Publish.

  7. Click Publish.
 Save Excel as Web Page
 Four Ways to Use Excel on the Web

Penn State:
 Interactive Excel on the Web

[Edited entry from 9/21/2006]

See all Topics


<Doug Klippert@ 3:46 AM

Comments: Post a Comment

  Thursday, October 08, 2009 – Permalink –

Copy Paste

Excel tables

One way to create a new table in an Access database from information included in an Excel spreadsheet is to select the pertinent data on the spreadsheet, including the field names.

Copy the selection (Edit>Copy, or CTRL+C)

Switch back to Access .

With Tables objects being shown in the database window, choose Edit>Paste, or use the CTRL+V shortcut.

Access will ask if the first row contains the field names and then will paste the information as a new database table.

[Edited entry from 9/16/2006]

See all Topics

Labels: ,

<Doug Klippert@ 3:44 AM

Comments: Post a Comment

  Wednesday, September 30, 2009 – Permalink –

When 28 is 30

How long is a month?

When you use the banker's DAYS360 function to calculate the number of days between two dates, you can get an odd answer.

If you use the DAYS360 function with a start date of February 28 and with an end date of March 28, a value of 28 days is returned.
You expect a value of 30 days to be returned for every full month. (12*30=360)

This behavior may occur if you use the U.S. method, also known as the NASD method, with the DAYS360 function.

To work around this behavior, use the European method with the DAYS360 function. With the European method starting dates and ending dates that occur on the 31st of a month become equal to the 30th of the same month. To use the European method with the DAYS360 function, use the following syntax:

=DAYS360(cell number of start date,cell number of end date,TRUE)

Using FALSE or omitting the third parameter uses the U.S. method
An unexpected value with the DAYS360 function

[Edited entry from 9/6/2006]

See all Topics


<Doug Klippert@ 3:18 AM

Comments: Post a Comment

  Thursday, September 24, 2009 – Permalink –

Shut it All Down

Close the spreadsheet and Excel

As it comes out of the box, at this point, Excel 2007 is set to display spreadsheets on the task bar. No big deal 2003 did too.

If, however, you only have one workbook open and want the application to shut down when you close the book you must make a little change.

Just as before, you must deselect Windows in the Taskbar from the View Options.

In 2007 it is located by clicking on the logo icon, then choose Excel Options. The Windows entry is on the Personalize screen

[Edited entry from 8/29/2006]

See all Topics


<Doug Klippert@ 3:42 AM

Comments: Post a Comment

  Thursday, September 17, 2009 – Permalink –

Lock the Barn

Protect your work

John Walkenbach has put together an FAQ on Workbook/Worksheet/VBA protection.
Spreadsheet Protection FAQ

The Microsoft Knowledge Base article KB 293445 Has a list of references to protection information.

Microsoft Excel provides multiple layers of protection to allow you to control who can access and change your data:
  • Worksheet protection: You can protect elements on a worksheet (for example, cells with formulas) from all user access, or you can grant individual users access to the ranges that you specify.
  • Workbook-level protection: You can apply protection to workbook elements, and you can protect a workbook file from being viewed and edited. If a workbook is shared, you can protect it from being returned to exclusive use and prevent the change history from being deleted.
... articles address some of the more frequently asked questions about workbook and worksheet protection in Excel:
  • How can I grant only a few users access to a range in my worksheet?
  • Why are users not allowed to edit the ranges that I established permissions for?
  • What new features are available in workbook protection?
  • Why don't the permissions that I set on ranges in my worksheets carry over to Windows 98 computers?
Here is more information
Overview of security and protection in Excel

[Edited entry from 8/20/2006]

See all Topics


<Doug Klippert@ 3:41 AM

Comments: Post a Comment

  Saturday, September 12, 2009 – Permalink –

Declaring Multiple Variables

Declare each one

When setting up a macro in VBA, if you want to declare multiple variables in one line of code, be sure to specify the type for each variable, even if the variables are the same type. Avoid code like the following:

Dim strFName, strLName, strMI As String

In such a case, only the last variable, strMI, is actually declared as a String type. The first two variables are designated by default as Variant data types.

To correctly declare the three variables, you would use the statement:

Dim strFName As String, strLName As String, strMI As String

[Edited entry from 8/14/2006]

See all Topics

Labels: , , ,

<Doug Klippert@ 3:52 AM

Comments: Post a Comment

  Friday, September 11, 2009 – Permalink –


Drawing bar objects

Kim Hedrich has put together a series of basic articles on AutoShapes for TechTrax.

AutoShapesPart 1 - How to draw circles, ovals, squares and rectangles; also modifying fill and line colour

AutoShapes Part 2 - Fill Effects

AutoShapes Part 3 - Shadows and 3-D

AutoShapes - Text Inside a Shape

[Edited entry from 8/13/2006]

See all Topics

Labels: , , ,

<Doug Klippert@ 3:09 AM

Comments: Post a Comment

  Thursday, September 10, 2009 – Permalink –

Trouble with Save?

Also great notification service

You may experience issues when you try to save a Microsoft Excel file if one or more of the following conditions are true:
  • You save an Excel file to a network drive where you have restricted permissions.
  • You save an Excel file to a location that does not have sufficient drive space.
  • The connection to the Excel file has been lost.
  • There is a conflict with an antivirus software program.
  • You save an Excel file that is shared.
  • The 218-character path limitation has been exceeded when you save an Excel file.
  • The Transition Formula Evaluation feature is turned on in Excel.
  • The file was created from a template that contains embedded objects.

How to troubleshoot errors when you save Excel files. KB 271513.
Receive Free Email Alerts every time Microsoft Publishes NEW Support or Knowledge Base Articles! is an e-mail notification system that scans the entire Microsoft Knowledge Base every night, and e-mails you when updates or additions are made to the technologies, you subscribe to.
kbAlertz [Edited entry from 8/12/2006] See all Topics


<Doug Klippert@ 3:49 AM

Comments: Post a Comment

  Wednesday, September 02, 2009 – Permalink –

Dynamic Tabs

Change tab names automatically

Changing the names of tabs is easy, just double click the tab or right click and choose rename.

Allen Wyatt has a small piece of code that will automatically update the tab name based on the value of a cell in the spreadsheet.

Sub myTabName()
ActiveSheet.Name = ActiveSheet.Range("A1")
End Sub

Allen also has some error checking code on his site:

Dynamic Worksheet Tabs

Dick Kusleika suggests another way using a change event:

Naming a sheet based on a cell

[Edited entry from 8/3/2006]

See all Topics


<Doug Klippert@ 6:28 AM

Comments: Post a Comment

  Tuesday, August 25, 2009 – Permalink –

Charting Tools

Sample graphs

Ed Ferrero has a number of sample Excel charts and tools.

One he calls a Wind Rose:

How to build a wind rose. Converts compass directions to degrees and radians, and plots a nice-looking chart.

Ferrero Consulting Pty Ltd:

[Edited entry from 7/26/2006]

See all Topics


<Doug Klippert@ 3:03 AM

Comments: Post a Comment

  Saturday, August 22, 2009 – Permalink –

Self Help

Get started in the right direction

The Office of Technology Services of Towson University, located in Towson, Md., provides Self-Help Training Documents for many applications.

They are available for many levels of knowledge. They’re clean, clear, and concise.
  • Access

  • Adobe Acrobat

  • Dreamweaver

  • Excel

  • FrontPage

  • Microsoft Office Tools

  • Outlook

  • Outlook Web Access

  • PowerPoint

  • Publisher

  • Visio

  • Windows

  • Word Art

  • Word
Tech Docs

[Edited entry from 7/21/2006]

See all Topics

Labels: , , , , , , , ,

<Doug Klippert@ 3:03 AM

Comments: Post a Comment

  Thursday, August 20, 2009 – Permalink –

Still More Functions

Never Enough

Laurent Longre Has put together an Excel add-in with 65 more functions.

Here are a few you might find useful:


converts a value from a base into another base.


date of Easter Sunday for a given year


returns the formula of a cell


returns the N highest numbers of a range or an array


converts a positive number into spelled-out text (supports 13 languages)


returns the unique items of a range or an array

MOREFUNC.XLL, 65 add-in worksheet functions

[Edited entry from 7/18/2006]

See all Topics


<Doug Klippert@ 3:12 AM

Comments: Post a Comment

  Friday, August 14, 2009 – Permalink –

Digital Signatures

How do I know it's real?

If you find a need to provide some sort of certification that your document has not been tampered with and is the rel thing, you might consider a digital signature.

This Microsoft Support article discusses the process.
What is a digital certificate?

What is a digital signature?

What occurs when I use a digital signature?

What Word files can I sign?

How can I obtain a digital signature?
  • Method 1: Obtain a digital certificate from a certification authority
  • Method 2: Create your own digital certificate
    Description of digital signatures and code

    Here's information for Excel.

    Digital Signatures for Excel

    [Edited entry from 7/11/2006]

    See all Topics

    Labels: ,

    <Doug Klippert@ 3:55 AM

    Comments: Post a Comment

      Thursday, August 13, 2009 – Permalink –

    Scroll Restrictions

    Without Protection

    You can protect a spreadsheet so that data is shielded from inadvertent entries.
    There is another way to set up a scroll area that does not involve protection.
    Open the Control Toolbox (right-click any existing tool bar) and click on the Properties icon.

    In the Scroll Area text box, type the scroll area range, or type the defined Name for the range.
    To cancel the Scroll Area restricted range, clear the Scroll Area text box.

    If you are using Office 2007, the Property icon is on the Developers tab.

    Multiple areas can be selected using Protection, but only one area is allowed using the Scroll Property.

    [Edited entry from 7/10/2006]

    See all Topics


    <Doug Klippert@ 3:28 AM

    Comments: Post a Comment

      Monday, August 10, 2009 – Permalink –

    Military Clipart

    Thousands of items

    If you find the need for Armed Forces photos and art, here is the place to look.
    Regardless of your opinion about their present mission, the military does present a spectacular visage.

    "06/17/06 - An F/A-18E Super Hornet aircraft sits at the ready as storm clouds pass overhead aboard the Nimitz-class aircraft carrier USS Ronald Reagan (CVN 76) in the Philippine Sea June 17, 2006.
    (U.S. Navy photo by Photographer's Mate 2nd Class Aaron Burden)

    All of these files are in the public domain unless otherwise indicated. However, we request you credit the photographer/videographer as indicated or simply "Department of Defense."

    HqDA.Army.Mil - Clipart

    [Edited entry from 7/7/2006]

    See all Topics

    Labels: , , , , ,

    <Doug Klippert@ 3:59 AM

    Comments: Post a Comment

      Wednesday, August 05, 2009 – Permalink –

    Large Text Files

    Split between worksheets

    While this problem is alleviated in Excel 2007 with its 1,048,576 rows by 16,348 columns, The old XL versions are still here.

    Text files with a large number of records are better handled in a program like Access. Having said that, there can be times that these lists must be imported into Excel. If the file has over 65,536 records, the data will not fit on a single worksheet.

    Here's a Microsoft Knowledge Base article with the macro code needed to bring oversized text data into Excel and split it into multiple worksheets:

    Sub LargeFileImport()
    'Dimension Variables
    Dim ResultStr As String
    Dim FileName As String
    Dim FileNum As Integer
    Dim Counter As Double
    'Ask User for File's Name
    FileName = InputBox("Please enter the Text File's name, e.g. test.txt")
    'Check for no entry
    If FileName = "" Then End
    'Get Next Available File Handle Number
    FileNum = FreeFile()
    'Open Text File For Input
    Open FileName For Input As #FileNum
    'Turn Screen Updating Off
    Application.ScreenUpdating = False
    'Create A New WorkBook With One Worksheet In It
    Workbooks.Add template:=xlWorksheet
    'Set The Counter to 1
    Counter = 1
    'Loop Until the End Of File Is Reached
    Do While Seek(FileNum) <= LOF(FileNum)
    'Display Importing Row Number On Status Bar
    Application.StatusBar = "Importing Row " & _
    Counter & " of text file " & FileName
    'Store One Line Of Text From File To Variable
    Line Input #FileNum, ResultStr
    'Store Variable Data Into Active Cell
    If Left(ResultStr, 1) = "=" Then
    ActiveCell.Value = "'" & ResultStr
    ActiveCell.Value = ResultStr
    End If
    'For Excel versions before Excel 97, change 65536 to 16384
    If ActiveCell.Row = 65536 Then
    'If On The Last Row Then Add A New Sheet
    'If Not The Last Row Then Go One Cell Down
    ActiveCell.Offset(1, 0).Select
    End If
    'Increment the Counter By 1
    Counter = Counter + 1
    'Start Again At Top Of 'Do While' Statement
    'Close The Open Text File
    'Remove Message From Status Bar
    Application.StatusBar = False
    End Sub

    Importing Text Files Larger Than 16,384/65,536 Rows

    Notice the code about 17 lines from the bottom of the macro.

    'For xl97 and later change 16384 to 65536.

    Also, after import, the data must be parsed. Use Data>Text to columns.

    If you have not worked with macros before, Dave McRitchie has a tutorial:

    Getting Started with Macros and User Defined Functions

    [Edited entry from 7/2/2006]

    See all Topics


    <Doug Klippert@ 3:10 AM

    Comments: Post a Comment

      Thursday, July 23, 2009 – Permalink –

    Excel VBA Help File

    Download from Microsoft

    Here is a downloadable compiled Help file covering the vagaries of Excel VBA.

    You can use this file without having to, necessarily opening the application.

    Excel 2003 VBA Language Reference

    [Edited entry from 6/16/2006]

    See all Topics


    <Doug Klippert@ 3:56 AM

    Comments: Post a Comment

      Thursday, July 16, 2009 – Permalink –


    Transfer data

    XML makes data transferable between applications.
    Here is a tutorial with downloadable files.
    Some simple guidance of how to transfer data from Excel or Access into HTML web pages using XML data files. VBA programs can be used to export data tables from Excel or Access into simple XML files. There are several examples of using different methods to display the XML and XSL files on web pages in order to quickly share your data with others.

    An introduction to Excel and XML data files

    Some nice photos and calendar layout:
    Monthly calendar with photos

    [Edited entry from 6/8/2006]

    See all Topics

    Labels: , ,

    <Doug Klippert@ 5:43 AM

    Comments: Post a Comment

      Wednesday, July 15, 2009 – Permalink –

    Restore Defaults

    Office 2003 redo

    To reset the original settings in Office 2003, follow these steps.
    Make sure that you back up your files before you follow these steps.
    1. 1. Start any Office 2003 program.
    2. On Help menu, click Detect and Repair.

    3. Click to select the Discard my customized settings and restore default settings check box, and then click Start.
    4. Quit the application, and then click Ignore.
    5. Click OK when you receive the following message:
      Reset of setting to default succeed.

    Microsoft Office Diagnostics in 2007 replaces Diagnose and Repair:

    [Edited entry from 6/7/2006]

    See all Topics

    Labels: , , , , ,

    <Doug Klippert@ 3:22 AM

    Comments: Post a Comment

      Sunday, July 12, 2009 – Permalink –

    Plain Numbers

    I'd Like to Make It Clear

    Plain Figures is a method of transforming statistical and financial data into figures, tables and graphs that people readily understand.

    Have you ever:
    • squinted your eyes trying to see the numbers in a PowerPoint presentation?

    • scratched your head at a charity leaflet with an indecipherable pie chart titled 'Where your donation goes' ... and set it aside?

    • missed discussion at a meeting because you were busy trying to figure out the figures?

    • put aside a graph or table, thinking "I'm not good with numbers."?

    Then you know how important the clear display of numerical information can be. Common problems People have trouble using numerical information for many reasons. Most commonly, authors don't know:
    • what to include: when unsure what numbers are important, people frequently display them all, overpowering the reader with irrelevance.

    • which format to use: the choice between text and table, table and chart, bar and pie.

    • how to use the technology effectively: computer software generates graphs easily, but the results hide your point behind incomprehensible chartjunk.

    • how to explain the information: selecting the right words for titles, columns and captions.

    Plain Figures is a partnership between Sally Bigwood, located in Wakefield, Yorkshire, UK, and Melissa Spore, who divides her time between Toronto and Saskatoon, Canada. Sally and Melissa are sisters and both have dual citizenship in the United States. [Edited entry from 6/4/2006] See all Topics

    Labels: , , ,

    <Doug Klippert@ 3:59 AM

    Comments: Post a Comment

      Wednesday, July 08, 2009 – Permalink –

    Polyglot Polynomials

    ToolPak Translator

    I was disappointed recently when I tried to look up Eric Desart's ToolPak translator. I found his site "niet beschikbaar."

    I won't use the boy and wet thumb story, but Ron de Bruin did spring up to save the day and make the download available.

    "Ever wanted an oversight of the Analysis-ToolPak Add-In functions, their descriptions, their arguments, their VBA and Procedure names, and all of this in your LOCAL language including translations versus the corresponding English names?

    This utility extracts this data from your LOCAL MS Excel edition.

    As such this table can be generated for ANY LANGUAGE EDITION of MS Excel, even when this language is not yet integrated in the utility."

    Analysis ToolPak Translator

    [Edited entry from 5/31/2006]

    See all Topics

    Labels: ,

    <Doug Klippert@ 3:59 AM

    Comments: Post a Comment

      Tuesday, June 30, 2009 – Permalink –

    Thirtieth Condition Formatting

    Three is not always enough

    Pre-2007 Excel gives the user the ability to specify up to three conditions under Format>Conditional Formatting.

    If that is not enough, Frank Kabel and Bob Phillips of offer a free download that extends the conditions to 30!

    Extended Conditional Formatter

    Also see:
    Conditional Formatting (including 2007)

    [Edited entry from 5/15/2006]

    See all Topics


    <Doug Klippert@ 3:05 AM

    Comments: Post a Comment

      Sunday, June 21, 2009 – Permalink –

    Clip Art at Home

    Install more

    Do you remember all of the clip art that was available locally with Office XP?

    When you have an Internet connection, you have access to the Office Online collection, but if you would like more clip art installed on your machine:

    A small amount of sample clip art images was included The 2007 Office systems and Office 2003 and is part of the "local collection" that is searched when you do not have Internet access to the Microsoft Office Online Clip Art and Media Web site. Office 2003 no longer included a media content CD with additional clip art. However, the Microsoft Office XP Media Content CD can still be installed locally or on a network share.

    The Office XP Media Content CD contains approximately 35,000 clips that are a subset of the clips that are available on the Microsoft Office Online Clip Art and Media Web site. The Office XP Media Content CD was included with Microsoft Office XP Professional, Microsoft Office XP Standard, and Microsoft Publisher 2002 Deluxe Edition.

    To install the contents of the Office XP Media Content CD on a computer, follow these steps:
    1. Exit all programs that are running

    2. Insert the Office XP Media Content CD into the CD drive or into the DVD drive
      (Hold down the SHIFT key to prevent the program from automatically starting. If Microsoft Windows Installer automatically starts, click Cancel)

    3. Click Start, click Run, type the following command, and then click OK:
      msiexec.exe /i CD_drive:\CAG.MSI ADDLOCAL=ALL /qb
    (CD_drive is the letter of the drive that contains the Office XP Media Content CD)
    How to add clip art to Clip Organizer in a 2007 Office system and in Office 2003

    [Edited entry from 5/5/5006]

    See all Topics

    Labels: , , , , , , ,

    <Doug Klippert@ 3:53 AM

    Comments: Post a Comment

      Wednesday, June 17, 2009 – Permalink –

    VBA Variable Problems

    Explicit protection

    It's good practice to always use the Option Explicit statement in the beginning of your code modules to ensure that all variables are unambiguously declared in your procedures.

    With this process in place, you'll receive a "Variable not defined" error if you try to execute code containing undeclared variables. Without this statement, it's possible to mistype variable names, which would be interpreted as new Variant type variables.

    This could severely impact the results of your code, and you might not ever know it. If you do find a problem, tracking down where the error is can be a chore.

    Although you can manually type the statement into your modules, changing a setting in Access can ensure that the statement is always added to new modules.

    1. Open a module (start the VBA Editor)

    2. Choose Tools>Options from the menu bar

    3. On the Editor tab of the Options dialog box, select the Require Variable Declaration check box in the Code Settings panel

    4. Finally, click OK

    [Edited entry from 5/2/2006]

    See all Topics

    Labels: , , ,

    <Doug Klippert@ 3:33 AM

    Comments: Post a Comment

      Sunday, June 14, 2009 – Permalink –

    Julian Dates

    Gregorian to/from Julian

    Julian dates refer to the number of days from the first of the year and the number of days until the end of the year.

    The year -45 has been called the "year of confusion," because in that year Julius Caesar inserted 90 days to bring the months of the Roman calendar back to their traditional place with respect to the seasons. This was Caesar's first step in replacing a calendar that had gone badly awry. Caesar created a solar calendar with twelve months of fixed lengths and a provision for an intercalary day to be added every fourth year. As a result, the average length of the Julian calendar year was 365.25 days.

    The Gregorian (Pope Gregory XIII) calendar is based on a cycle of 400 years, which comprises 146,097 days. Since 146,097 is evenly divisible by 7. Dividing 146,097 by 400 yields an average length of 365.2425 days per calendar year, which is a close approximation to the length of the tropical year. The Gregorian calendar accumulates an error of one day in about 2500 years.

    Calendars by L. E. Doggett

    From Chip Pearson's site

    "Many applications (especially mainframe systems) store dates in the Julian format, which is a 5-digit number, consisting of a 2-digit year and a 3-digit day-of-year number. For example, 24-August-1999 is stored as 99236, since 24-August is the 236th day of the year. Excel does not support Julian dates directly, but you can use them with only a few fairly simple formulas.

    Converting A Standard Date To A Julian Date

    The formula below will convert a standard Excel date in A1 to a Julian Date.


    This formula takes the 2 right-most characters of the YEAR of the date in A1, and then appends the number of days between the date in A1 and the 0th day of that year. The TEXT function formats the day-of-year number as three digits, with leading zeros if necessary.

    Converting A Julian Date To A Standard Date

    The formula below will convert a Julian date to a standard Excel date.


    If the year digits of the Julian date are less than 30 (i.e., 00 to 29), the date is assumed to be a 2000 century year. If the year digits of the Julian date are greater than or equal to 30 (i.e., 30 to 99), the date is assumed to be a 1900 century year. This formula works by taking advantage of the fact that the DATE function can handle days beyond the "normal" days in a month. For example, DATE correctly computes 100-Jan-1999 to be 10-April-1999.

    These Julian dates must have the leading zero or zeros for years between 2000 and 2009. For example the 123rd day of 2000 must be entered as 00123. Format the cell as TEXT before entering the data, or enter an apostrophe before the Julian date -- e.g., '00123. This will prevent Excel from treating the Julian date as a number and suppressing the leading zeros."

    US Naval Observatory has this definition (and a calculator):

    Julian dates (abbreviated JD) are simply a continuous count of days and fractions since noon Universal Time on January 1, 4713 BCE (on the Julian calendar). Almost 2.5 million days have transpired since this date.

    April 29, 2004 at 6:00 AM would be 2453854.75

    [Edited entry from 4/30/2006]

    See all Topics


    <Doug Klippert@ 3:41 AM

    Comments: Post a Comment

      Monday, June 01, 2009 – Permalink –

    Count the Colors

    I bid 3 Red

    What if you would like to know the color name or to count or to sum cells by a fill color? There is no built-in function in Excel.

    In this case you can make a User Defined Function (UDF).

    Here is the sample UDF that you can use to analyze, count and sum the cells depending on their filled color.

    These UDF function can be used in the same way as built-in functions that you can use in the worksheet.

    • AnalyzeColor Returns the color name, the color index or color index in RGB.
      Syntax: AnalyzeColor(color range, optional; "text" or "index" or "rgb". When it is omitted "text" is used.)

    • CountColor Counts the number of cells depending on their filled color.
      Syntax: CountColor(color range, target range)

    • SumColor Adds all the numbers in a range of cells depending on their filled color.
      Syntax: SumColor(color range, target range)
    Sum and Count by fill color

    Chip Pearson:
    Working with Cell Colors

    [Edited entry from 4/11/2006]

    See all Topics


    <Doug Klippert@ 3:13 AM

    Comments: Post a Comment

      Friday, May 29, 2009 – Permalink –

    Report Manager

    Define Print & View Areas

    The Excel 2000 Report Manager add-in is not included with Excel 2002+, as was the case with previous versions of Excel.

    To use it, you must first download the Excel 2002 Report Manager add-in from Downloads on Microsoft Office Online. Follow the instructions on the Downloads page to install the Report Manager.
    (For 2007 see

    from Microsoft Office Online

    "Using the Report Manager add-in program, you can combine worksheets , views (a set of display and print settings that you can name and apply to a workbook. You can create more than one view of the same workbook without saving separate copies of the workbook.), and scenarios (a named set of input values that you can substitute in a worksheet model.) into printable reports. For example, if you have a Best Case and a Worst case scenario, a Summary view, and a Details view, you can create a report that presents the Best Case scenario in the Details view and another report that presents the Best Case Scenario in the Summary view. Reports that you create are automatically saved with your workbook so that you can print them at any time."

    Note If the Report Manager is not available on the View menu after downloading the add-in, click Add-ins on the Tools menu, and then select Report Manager.

    You can use the Report Manager to do the following:

    Create a report for printing

    1. On the View menu, click Report Manager.
    2. Click Add.
    3. In the Report Name box, type a name for the report.
    4. In the Sheet box, click the sheet you want to use for the first section of the report.
    5. Do one of the following:
      • To use a view for the first section of the report, select the View check box, and then click the view in the View box.
      • To use a scenario for the first section of the report, select the Scenario check box, and then click the scenario in the Scenario box.
    6. Click Add to enter the view or scenario as a section in the Sections in this Report box.
    7. Repeat step 5 and 6 until you've created all of the sections you want in the report.
    8. To change the order of the sections, in the Sections in this report box, click the section you want to move, and then click either Move Up or Move Down.
    9. To number the pages of the report consecutively, select the Use Continuous Page Numbers check box.
    Note Microsoft Excel prints sections of a report in the order in which they're listed in the Sections in this report box.

    Edit a report for printing

    1. On the View menu, click Report Manager.
    2. In the Reports box, click the report you want to edit, and then click Edit.
    3. Do one or more of the following:
      • To add a new section, click the sheet, view, and scenario you want under Section to Add, and then click Add.
      • To delete a section, click the section in the Sections in this report box, and then click Delete.
      • To change the order of the sections, in the Sections in this report box, click the section you want to move, and then click either Move Up or Move Down.
      • To number the pages of the report consecutively, select the Use Continuous Page Numbers check box.
      Note Microsoft Excel prints sections of a report in the order in which they're listed in the Sections in this report box.

    Print a report

    1. On the View menu, click Report Manager.
    2. In the Reports box, click the report you want to print.
    3. Click Print.
    4. In the Copies box, type the number of copies you want to print.
    Also see: Template Wizard with Data Tracking

    [Edited entry from 4/9/2006]

    See all Topics


    <Doug Klippert@ 3:30 AM

    Comments: Post a Comment

      Tuesday, May 26, 2009 – Permalink –

    Name that Range

    How to use names in Excel

    Named ranges are one of the more powerful tools in Excel.

    Jan Karel Pieterse of JKP Application Development Services has written a tutorial that will help you understand this technique.

    Here are some of the chapter titles:

    • How To Define Range Names

    • How To Use Range Names

    • Absolute And Relative Addressing

    • The Context Of Names

    • Special Names

    • A Step Further: A Formula In A Defined Name

    • Dynamic Names

    • Passing Arguments To A Defined Name Formula

    • Bugs in Excel's Name Object
    Range Names in Excel

    [Edited entry from 4/5/2006]

    See all Topics


    <Doug Klippert@ 3:52 AM

    Comments: Post a Comment

      Tuesday, May 19, 2009 – Permalink –

    UDF is not a Baby Alien

    Things should to function

    Frank Rice has written a "show how" about creating functions that are not included in the box.

    "Excel allows you to create custom functions, called "User Defined Functions" (UDF's) that can be used the same way you would use SUM(), VLOOKUP, or other built-in Excel functions.
    The Excel user who wishes to use advanced mathematics or perform text manipulation is often seriously disappointed by Excel's limited library of formulas and functions. A UDF is simply a function that you create yourself with VBA.

    The following is a sample that is a good candidate for a UDF:
    Function CtoF(Centigrade)
    CtoF = Centigrade * 9 / 5 + 32
    End Function

    In the Worksheet you would enter something like:

    frice's Weblog

    Here are some other links:
    User Defined Functions
    Functions to Calculate Light Years

    [Edited entry from 3/27/2006]

    See all Topics


    <Doug Klippert@ 3:38 AM

    Comments: Post a Comment

      Friday, May 15, 2009 – Permalink –

    Tabs with the Number of the Week

    Count to 52

    Excel no longer has a theoretical limit on the number of worksheets in a workbook. One common use of this ability is to add a worksheet for each week in the year.

    Here's a macro that does the trick:
    Sub YearWorkbook()
    Dim iWeek As Integer
    Dim sht As Variant
    Application.ScreenUpdating = False
    Worksheets.Add After:=Worksheets(Worksheets.Count), _
    Count:=(52 - Worksheets.Count)
    iWeek = 1
    For Each sht In Worksheets
    sht.Name = "Week " & Format(iWeek, "00")
    iWeek = iWeek + 1
    Next sht
    Application.ScreenUpdating = True
    End Sub
    Naming tabs for weeks

    [Edited entry 3/21/2006]

    See all Topics


    <Doug Klippert@ 3:56 AM

    Comments: Post a Comment

      Friday, May 08, 2009 – Permalink –

    Data from the Other Sheet

    Sometimes we must go next door

    Here is a discussion about how to use data that may be on a different worksheet, or another workbook.

    Getting data from other sheets can be very easy: or it can be very hard, it all depends on what you need to do. This article looks at some of the ways you can get data from other sheets.

    • The basics
    • Using the Indirect function
    • Back to the old school, XLM! (previous and next sheet)
    • VBA user defined functions (previous and next sheet)
    • Links
    Methods In Excel

    Cell References

    [Edited entry from 3/13/206]

    See all Topics


    <Doug Klippert@ 3:12 AM

    Comments: Post a Comment

      Wednesday, May 06, 2009 – Permalink –

    Who was that font I saw you with last night?

    That was no font, that was my typeface

    You can find the Fonts supplied with some Microsoft products
    Select a product name from the list to get a list of fonts supplied with that product.

    Microsoft's Typography is an interesting site to poke around in.

    Here are some books I use for reference material:

    Words into Type

    by Marjorie E. Skillin, Robert Malcolm Gay ISBN 0139642625

    Stop Stealing Sheep & Find Out How Type Works

    by Erik Spiekermann, E.M Ginger ISBN 0201703394

    The Elements of Typographic Style

    by Robert Bringhurst ISBN 0881791326

    A font can be defined as a collection of characters with the same style and size. A typeface is the design of the characters regardless of size or style. The terms are used interchangeably today.

    [Edited entry from 3/11/2006]

    See all Topics

    Labels: , , , , , ,

    <Doug Klippert@ 3:06 AM

    Comments: Post a Comment

      Thursday, April 30, 2009 – Permalink –

    Google from the Help Menu

    Search with Excel, Word, PowerPoint

    Where better to search the Internet for support on an Excel, PowerPoint, or Word problem than through the Help menu?

    Would you like to add Google to that menu?

    Ron de Bruin at has developed free add-ins that does just that.

    "Google Search 6.0/7.0 places a new sub-menu item under the Help menu of whatever program you call it from. When that item is selected, up pops a user-friendly interface. This allows a largely intuitive completion and execution of a Google Search.

    On clicking the Search button a lot goes on behind the scenes.
    • Your default (i.e. your usual) web browser is loaded and, without further instructions,
    • It's off to the Google Advanced Search Page.
    • It then fills in an Advanced Query to your specifications
    • Executes that query.
    • Once results are found (or not found) you are shown those results just as if you had carried out all of the steps of the process."

    [Edited entry from 3/5/2006] See all Topics


    <Doug Klippert@ 3:41 AM

    Comments: Post a Comment

      Thursday, April 23, 2009 – Permalink –

    Lookup, Down, and Sideways

    A very useful Excel feature

    Excel does not have "relational" tables like database applications such as Access.

    You, however, can make use of database functions including the ability to look up values in a table based on a value.

    You could, for instance look up a salesperson's records based on an employee ID.

    All 'Bout Computers has an article describing "Lookups".

    Using VLOOKUP, HLOOKUP, INDEX, and MATCH in Excel to interrogate data tables

    John Walkenbach has a book "Excel 2003 Formulas" with a 24-page chapter on Lookup functions and other database/list tricks.

    Chip Pearson talks about lookups on his site as well.

    Aaron Blood's site offers download files explaining lookup. See numbers 36, 37, and 44.

    One of the zipped Workbooks (number 35), is a study in lookup methodology.

    Daily Dose of Excel:
    VLookup on Two Comumns

    [Edited entry from 2/25/2006]

    See all Topics


    <Doug Klippert@ 3:58 AM

    Comments: Post a Comment

      Thursday, April 16, 2009 – Permalink –


    No Excel needed

    Here's a site that lets you design and post spreadsheets on the web.
    "Keep your records, lists and spreadsheets in one place online. Easily slice, dice and share with others.

    With a free account, you can...
    • Access your spreadsheets from anywhere
    • Share your spreadsheets easily - just email the URL
    • Add spreadsheets to your weblog
    • FREE storage

    [Edited entry from 2/16/2006]

    See all Topics

    Labels: ,

    <Doug Klippert@ 3:14 AM

    Comments: Post a Comment

      Thursday, April 09, 2009 – Permalink –

    Excel-lent E-Mail

    Outlook, Excel, and VBA

    Ron de Bruin, Microsoft MVP - Excel, has put together a collection of VBA routines to make Excel e-mail friendly.

    See if these topics tempt you:

    Example Code for sending mail from Excel
    • Mail Workbook
    • Mail one Sheet
    • Mail more than one Sheet
    • Mail the Selection or range
    • Mail Every Worksheet with Address in cell A1
    • Mail sheet or sheets to one or more people
    • Mail range or sheet in the body of the mail (Send personalized email)
    • Mail a message to each person in a range with Outlook
    • Mail a message to each person in a range with CDO (no security warnings)
    • Sending a different file to each person in a range with Outlook
    • Zip the ActiveWorkbook and mail it with Outlook
    • Security (Prevent displaying the dialog to Send or not Send)

    Also Download Addins for Excel e-mail information

    Also see:

    John Walkenbach:
    Sending Personalized Email from Excel

    [Edited entry from 2/10/2006]

    See all Topics

    Labels: ,

    <Doug Klippert@ 3:23 AM

    Comments: Post a Comment

      Friday, April 03, 2009 – Permalink –

    Where Have All the Bytes Gone?

    Folder size list

    You can create a list in Excel of all the folders on a drive and their sizes.
    (The credit goes to Peter Beach, an Excel MVP.)

    Get Folder Size code

    1. Copy the code and open Excel.
    2. Press Alt+F11 and, if necessary, on the Visual Basic Editor menu, Insert>Module
    3. Paste the code.
    4. You could use Alt+Tab to bring the worksheet forward.
    5. Go to Tools>Macros and run the Macro named "GetFolderListing".

    It may take a little time to complete. BTW, if you feel geeky enough, here is a picture of some of the year 2005 MVPs from John Walkenbach's site. [Edited entry from 2/5/2006] See all Topics


    <Doug Klippert@ 3:54 AM

    Comments: Post a Comment

      Monday, March 30, 2009 – Permalink –

    Linked Table Problems

    Lost functionality

    Have you found that you cannot update linked Excel tables in Access 2003?

    If you have installed Microsoft Office 2003 Service Pack 2 (SP2):

    Because of legal issues, Microsoft has disabled the functionality in Access 2003 and in Access 2002 that let users change the data in linked tables that point to a range in an Excel workbook. However, when you make changes directly in the Excel workbook, the changes appear in the linked table in Access.
    You cannot change data in linked tables that are to an Excel workbook

    You might try reinstalling and then only installing SP1.

    Guacamole dipped - Access to Excel linked table gotcha
    Patent ruling costs Microsoft $8.9 million

    "A jury in U.S. federal court found that Microsoft infringed on a Guatemalan inventor's 1994 patent on technology linking the company's Access and Excel programs, and ordered the world's largest software maker to pay $8.9 million in damages.

    [Edited entry from 2/1/2006]

    See all Topics

    Labels: ,

    <Doug Klippert@ 3:14 AM

    Comments: Post a Comment

      Sunday, March 22, 2009 – Permalink –

    Intro to Excel

    English Excel

    The UCL (University College London) site for the High Energy Physics Group of the Department of Physics & Astronomy, has an introduction to Excel e-book on this page. It's the material used in a 10 week course.

    "This web page contains material for the computing and data analysis elements of the first-year PHYS1B40 Practical Skills course.

    The main elements of the course are:

    Analysis of data and treatment of uncertainties of measurement (Data Analysis) - discussed in lectures and illustrated by exercises using Excel

    Use of spreadsheets (Excel) for

    Analysis and presentation of experimental data
    Mathematical modeling, simulation, analysis and problem solving
    Introduction to computer programming by means of Visual Basic for Applications (VBA)

    Here are some links to the topics covered topics:


    Data Analysis

    Visual Basic

    [Edited entry from 1/27/2006]

    See all Topics


    <Doug Klippert@ 3:34 AM

    Comments: Post a Comment

      Friday, March 13, 2009 – Permalink –

    Web Queries

    Do You Question the Web?

    This feature can make data acquisition a lot easier than Copy-Paste-Reformat-Try again.

    "Generally, though, people tend to overlook the option of using the Web as a data source for Excel, be that source the Internet, an intranet, an extranet, or a Web Service. But they shouldn't. Web queries are an easy, yet remarkably flexible and predictable way of bringing data into Microsoft Excel from anywhere on the Web. You can point a Web query at any HTML document that resides on any Web server - or even on a file server, for that matter - and pull part or all of the contents back into your spreadsheet...When you start using Excel's Web queries, you will realize they are almost as limitless as the Web is.

    Well Kept Secret

    On the menu bar, go to Data>Import External Data. (In 2007, Data>Get Extrnal Data>From Web). Then, select Import Data to use an existing Web query or select New Web Query to build a new one.

    Web Query

    Also see:
    Excel Web Query Secrets Revealed
    Integrate Far-Flung Data into Your Spreadsheets with the Help of Web Services

    Updating Excel From the Web

    Web Queries and Dynamic Chart Data in Excel 2002

    [Edited entry from 1/15/2004]

    See all Topics

    Labels: ,

    <Doug Klippert@ 3:43 AM

    Comments: Post a Comment

      Wednesday, March 04, 2009 – Permalink –

    Animate Window Size

    So cool!

    The following macro has little or no practical computing value, but it can add a "way cool" element when a worksheet is unhidden.
    There are three states that a worksheet can be in; Minimized, Maximized, and Normal.

    This macro will gradually resize a worksheet from small to Maximized. The worksheet appears to be growing:

    Sub SheetGrow()
    Dim x As Integer
    With ActiveWindow
    .WindowState = xlNormal
    .Top = 1
    .Left = 1
    .Height = 50
    .Width = 50

    For x = 50 To Application.UsableHeight
    .Height = x
    Next x

    For x = 50 To Application.UsableWidth
    .Width = x
    Next x

    .WindowState = xlMaximized
    End With
    End Sub

    (By Mark William Wielgus)

    Also fun:

    Sub SheetGrow()

    Dim x As Integer, xmax As Integer

    With ActiveWindow

    .WindowState = xlNormal

    .Top = 1

    .Left = 1

    .Height = 50

    .Width = 50

    If Application.UsableHeight > Application.UsableWidth Then

    xmax = Application.UsableHeight


    xmax = Application.UsableWidth

    End If

    For x = 50 To xmax

    If x <= Application.UsableHeight Then .Height = x

    If x <= Application.UsableWidth Then .Width = x

    Next x

    .WindowState = xlMaximized

    End With

    End Sub

    # posted by Joerd : 12/30/2005

    [Edited entry from 12/30/2005]
    See all Topics


    <Doug Klippert@ 3:24 AM

    Comments: Post a Comment