Free Downloads for Microsoft Office


Check our own downloads folder; we are constantly adding new things, some of which relate to articles found here on our site.  Our free downloadable files and templates include:



Name of Download





Climate Data Workbook

An interactive charting workbook that lets you compare the climate of two U.S. cities. It uses no macros.



Gradient Contour Chart Add-In

An add-in that generates an alternative to Excel's contour chart. Charts produced by this add-in are not dynamic.



J-Walk Chart Tools

A collection of chart-related utilities.



J-Walk Enhanced Data Form Add-In

Version 2.0. An alternative to Excel's Data * Form command. Lots of enhancements, and the VBA source code is available (for a small fee) so you can customize it.



Animated Hypocycloid Charts

An Excel workbook that generates an infinite number of dazzling animated charts. You will be impressed.



Power Utility Pak v6

A collection of Excel utilities, functions, and enhanced shortcut menus.



Dice Roller

An Excel workbook that simulates rolling two dice.



Toolbar Calculator

An Excel add-in that creates a toolbar that functions as a simple calculator.



Clock Chart

An Excel workbook that contains an XY chart that displays a working analog clock. It uses a VBA macro to update the chart every second.



Employee Time Sheet

Easy-to-use time sheet for tracking daily hours worked.



ActiveX Control Demo

Demonstrates the use of ActiveX controls on a worksheet



J-Walk Cell Math Add-In

An add-in that enables you to perform any mathematical operation on cells without using formulas. An enhanced version of this utility is available in PUP v6.



Children's Multiplication Exercises

Assists in learning multiplication tables. Contributed by Jim Hubbard. Instructive for children and adults who are learning VBA.



AddPath Add-in

An add-in that enables you to print a workbook's full path in a header or footer.



Extended Date Functions

An add-in that provides a set of worksheet functions that enable you to work with dates prior to the year 1900 -- something that's not normally possible. An enhanced version of this utility is available in PUP v6.



J-Walk Date Report Add-In

An add-in that generates a useful report that describes all date cells in a worksheet. This may help you identify potential Year-2000 problems. An enhanced version of this utility is available in PUP v6.



J-Walk Select by Format Add-In

An add-in that enables you to select cells based on formatting. For example, you can easily select all cells that contain a specific number format and are red. An enhanced version of this utility is available in PUP v6.



Keno for Excel 97

A worksheet that simulates a Keno game. Just like the casinos - except you won't lose any money with this version.



J-Walk Conditional Row Delete

An add-in that makes it easy to delete rows based on the values in a specified column.



Timeline Chart

Demonstrates how to create an attractive "time line" (Gantt) chart in Excel.



Name Lister Add-In

An add-in that displays a list of names in a workbook by category (all names, workbook level names, sheet level names, hidden names, linked names, and "bad" names). An enhanced version of this utility is available in PUP v6.



Superscript / Subscript Formatting Add-In

An add-in that enables you to add superscript or subscript formatting to characters within a cell. An enhanced version of this utility is available in PUP v6.



Tony Gwynn's Hit Database

An Excel database with information about Tony Gwynn's first 3,000 hits. Use the pivot table to summarize the data in various ways. (Tony Gwynn is a baseball player on the San Diego Padres)



Menu Shenanigans

Makes a great April Fool's prank for an office-mate (preferably one with a sense of humor). VBA macros reverse the text in all of Excel's worksheet menus. The hot keys remain intact, so the menus can still be accessed using familiar keystrokes.



Appointment Calendar Maker

A workbook that lets you generate and print daily appointment calendar pages.



Guitar Scales and Modes

This workbook features a graphic depiction of a guitar fretboard, and displays the fret locations for the notes in any scale or mode, in any key you choose.



Excel Doodle Pad

Etch-a-Sketch with a new twist.This workbook displays a doodle pad that lets you create attractive symmetrical drawings using the arrow keys on your keyboard.



Array Calendar

Demonstrates how to create a calendar for any month using a single (complex) array formula.



Another Gantt Chart

This one was featured in John Walkenbach's PC World column.



Hangman Game

The classic word-guessing game, programmed in VBA - complete with cheesy hand-drawn graphics. Includes many words, and you can even add your own. An enhanced version of this utility is available in PUP v6.



Custom Number Format Examples

This workbook contains several examples of useful custom number formats.



MP3 File Lister

A macro-driven application that creates an attractive listing of MP3 music files.



A Class Module to Manipulate a Chart Series

A useful class module that makes it easy to work with the data used by a chart.



Soundex Demo

Demonstrates a technique that lets you search for a text based on its sound.



GetOption Function

Demonstrates a useful function the displays a list of OptionButtons derived from an array. The UserFrom and VBA code are created on the fly.



Control the LED Display in the StatusBar

Originally developed by David Wiseman. It consists of VBA code that lets you control the "LED" display in Excel's status bar. I've always thought that this display was off limits, but David proved otherwise with some incredible programming. Very impressive! Thanks to David for letting me share this file with the world, and thanks to Jim Rech for modifying it so it works with Excel 2000.

This updated version is in the form of a class module (expertly programmed by Ole P. Erlandsen).



UserForm Hyperlink Demo

Demonstrates how to add hyperlinks to a UserForm Click



J-Walk FaceID Identifier Add-In

Makes it very easy to determine the FaceID value for a CommandBar image.



Charts in a UserForm

Demonstrates how to display one or more charts in an Excel 97 UserForm.



Menu Maker

A technique that makes it very easy to create a custom menu for an Excel 97 (or later) workbook or add-in. VBA programming not required!



Displaying Online Help

A technique that makes it easy to display help topics in a UserForm.



A Color Picker Dialog Box

Demonstrates a custom VBA function that displays a UserForm that lets the user select a color. Uses a class module.



Filling a ListBox With Unique Items

A clever technique to fill a ListBox with non-duplicated items in a list.



Handle Multiple UserForm Buttons With One Procedure

With an Excel 97/2000 UserForm, each control on the form has its own unique Click event, so you can't have several buttons that call the same Sub procedure. This workbook demonstrates a way around this limitation by using a class module.



ImageMap Demo

Demonstrates how to create a clickable image map for navigation within a workbook.



Animated Shapes

Demonstrates how to use VBA to generate some interesting animations using shapes and charts.



Progress Indicator

Demonstrates how to create a graphic progress indicator in a UserForm.



Dialog-To-UserForm Wizard

A handy utility that converts Excel 5/95 dialog sheets to Excel 97/2000 UserForms.



Generate Permutations

Uses a recursive procedure to generate all possible permutations of a string.



Copy Multiple Selection

A VBA macro that lets you copy and paste a multiple range selection -- something that's normally impossible.



Button Faces

Contains nearly 200 custom toolbar button faces that you can copy. These are not Excel 97/2000 CommandBar images. For Excel 5/95.



Range Selection Demonstration

Contains 15 VBA routines that demonstrate how to make various types of range selections. When you open the workbook, a new menu appears that lets you try out the various routines.



Multiple: Excel Utilities

Alert The best way to display diagnostic messages to users and developers. Overcomes the limitations of MsgBox, Debug.Print, and Application.Statusbar. Recommended for intermediate and advanced developers.

AltKeyList Creates a list of the ALT key menu accelerator keys (8 KB).

AppEvent Illustrates the use of Application level events. (10 KB)

Arr2Col Converts an NxM array to a single column or row. (19 KB)

Calendar A Calendar workbook. Allows you to have special days (birthdays, holidays, etc) automatically highlighted. (36 KB) NOTE: This file requires that both the Analysis Tool Pack and the Analysis Tool Pack VBA add-in libraries be loaded. Otherwise, you'll get run time errors.

Case Convert This add-in will allow you to perform a variety of text conversions (UPPER CASE, lower case, Title Case, Sentence case, etc) on a range of cells. Install the Add-In, and then choose Text Convert from the Excel Tools menu.

Cell View This add-in allows you to see the characters and their codes in a cell. This makes it easy to find unprintable characters such as tabs that don't display but may affect formulas.

Clock This workbook shows how to create and manipulate Shape objects on a worksheet to create an analog clock that ticks every second. (17 KB)

Compare This Add-In utility allows you to compare the contents of two workbooks. This was written by Myrna Larson and Bill Manville.

DistributedDates A file illustrating the formulas on the Distributed Dates page. (10KB) NOTE: This file requires the Analysis Tool Pack

DynaRange Using defined names to refer to dynamic ranges. (6 KB)

EventSeq Demonstrates the sequence of all Excel97 events. (14 KB)

FirstLast Extracting first and last names from full names. (6 KB)

FormPosition Excel typically displays forms in the center of the screen. This is usually fine for data entry and dialog forms. However, in many cases it is desirable to display a form in relation to a specific cell. This is not a simple a task as it seems because the top and left coordinates of a UserForm based on the same coordinate system as the Top and Left coordinates of a cell. To properly calculate the Top and Left coordinates of a UserForm, you have to take in to account the window state (normal or maximized) of the Excel application window, and the Workbook window, and their relative positions, in addition to whether the formula bar is visible, what command bars are displayed, and how they are positioned. Needless to say, these calculations can get rather complicated. Fortunately, I've done the work for you. (34KB)

GetInfo The GetInfo function, written in VBA, allows you to retrieve nearly any property of any object in the Excel object model directly from a worksheet cell. Using this one function, you can eliminate dozens of custom UDFs to get application and object properties.

HiLite Highlights the background color of the current selection. (8 KB) NOTE: The functionality of this workbook is replaced and enhanced by the RowLiner add-in.

Latitude Latitude and Longitude data for 1200 US Cities. (62 KB)

Lists Working with 2 lists with duplicate entries. (11K)

MinMax Various Minimum and Maximum formulas. (6 KB)

Names Returning File, Workbook, and Worksheet names to a cell. (6 KB)

NoBlanks Eliminating blanks from a list of data. (7 KB)

Rank A totally new Ranking workbook, which has all the formulas described on the “Ranking Data In Lists” page. (16 KB)

OpenSafe This add-in allows you to safely open workbooks that contain VBA code (macros) without risk. It adds an item to your File menu called "Open Safe". NOTE: The level of protection provided by this add-in is higher in Excel 2002 than Excel 2000. NOTE: Opening workbooks that contain VBA macros always entails risk, so I do not guarantee that this add-in will eliminate all possible risks.

Overtime A file illustrating the formulas on the “Working With Overtime Hours page.” (8 KB)

ProgressReporter A DLL file that allows you to display a simple progress indicator. Click here for more details.

Reader Reads cells back to you through your sound card. Adds "Reader" to Tools menu. You should make this into an Add-In. (272 KB, including sound files.) All files must reside in same directory. Yes, that's Chip Pearson's voice reading the numbers.

RowLiner This add-in allows you to have Excel automatically display row and column view lines. See the RowLiner page for more details.

StockWeb Illustrates using Excel to download stock price information from the web. (22 KB)

Symbolize This workbook will display a form that allows you to view and insert special symbols such as currency symbols into your workbook. It works much like the "Insert Symbol" tool in Microsoft Word. (21KB). This works only in Excel97 and Excel2000. It will not work with Excel95 or earlier versions. You can download a better version of this, written as a COM Add-In here. Note that COM Add-Ins are supported only by Excel 2000 or XP. They will not work in earlier versions of Excel.

SumEvery Summing Every Nth Value in a list. (6 KB)

TLIUtils Provides VBA procedures for working with type libraries, via the TypeLib Information object. Documentation of the available procedures is available in the VBA code modules.

Unique Counting Unique items in a list. (7 KB)

VBA Shortcuts This zip file (6 KB) contains a workbook that lists all of the keyboard shortcuts available in the VBA Editor. For a list of keyboard shortcuts within Excel, download Excel Keyboard Shortcuts.

VBE Menus Adding menu items to the Visual Basic Editor (15 KB)

X-Ray Chip Pearsonwrote this game several months ago. It is similar to a game called "Black Box" that he used to play in Junior High. You try to find targets by shooting paths into a grid, and watching whether those paths hit the targets, exit out of the grid, or are reflected back to their original cell. Complete details are in the workbook file. (100 KB) This works only in Excel97 and Excel2000. It will not work with Excel95 or earlier versions.

XLKeys This workbook lists all of the shortcut key combinations in Excel (8KB). Updated 23-April-2001.

XLCAI This download has a sample COM Add-In written for Excel in Office 2000 Developer Edition. It illustrates the basic programming for a COM Add-In, including how to write menu controls, how to handle modeless forms and how to make functions in the COM Add-In available to worksheet cells (via VBA code). The actual download doesn't really do anything, so it will be of interested only to those who want to write COM Add-Ins. You must have the Developer Edition of Office 2000 to view the code. (35KB).

XLConst This workbook lists all the symbolic constants in Excel and their numeric equivalents. The list can be built on the fly using the TypeLib Information DLL.



Apply Formula to Range Utility

This nifty utility allows you to apply a certain formula to every cell in a selected range. You first select the cells you wish to change, and then run the macro. Winner of the April 2003 challenge to submit the most useful general-purpose utility for Excel.



Multiple: Excel Graphics Utilities

Submissions to the Dec 2002 challenge by this website to find the most innovative uses of Excel related to charts or graphics. Check out the winner - Animated Warehouse Locator!

Entry 1:
BCG Chart by Ed Ferrero

Ed says, "Demonstrates how to create a bubble chart where the bubbles are themselves pie charts. The workbook is useful in its own right for BCG portfolio analysis (Boston Consulting Group). The technique shown here can be used with other types of charts, turning each point in a chart series to an individual pie or other chart. This is one of those 'they said it couldn't be done' charts."
Entry 2:
Animated Cannon Chart by Earl Takasaki

Earl writes: While browsing the Microsoft newslists, I ran across a plea from a teacher who wanted to demonstrate simple cannon-ball ballistics. I created an Excel program that uses an “animated chart” to simulate the flight of a cannon ball from a top view on a map. The chart is a simple X-Y chart with a JPEG map of Charlestown bay as the background with points 0,0 centered on the city of Charlestown. I manually scale the X-Y axises so that the scale would equal that of the map in Km. The student enters an amount of powder which linearly translates in muzzle velocity and sets the elevation angle of one of three cannon. (Try Ft Sumter, 7 kg or powder at 35 degrees). After calculating the x-y positions at each point in time, and using translation and rotation to correspond to the cannon and targets on the map, I animate the flight of the cannon towards the target in both a top view and a side view.
The “animation” is done by hiding every line of the data source of an X-Y graph, then unhiding each line one by one using VBA. The “explosion” is done by showing, then hiding increasing sizes of orange dots. Hope you like it!
Entry 3:
Demographic Indices by Ken Kranz

Ken writes, This is an example of a spreadsheet I designed to bring together (and compare) an Account and a Brand across a series of Demographic Measures
Entry 4:
Interactive Graph without VBA by Vinh Nguyen

Vinh writes, I created this interactive graph without using any VBA coding whatsoever.
Entry 5:
Animated Warehouse Locator by Phil Johnson

Phil says, "Just a simple diddy, nothing fancy but rather useful". This is very cool. On screen 1, you select a product. Click the button and an animated path draws how to get to that location in the warehouse.
Entry 6:
CD Label Maker by Roy Cox

Roy says, "My entry for the competition uses Excel to create Labels for CDs and Jewel cases. It uses Autoshapes and formating to insert pictures. Text boxes, WordArt etc. can be used to personalise Labels for projects. It prints to Neato and Pressit Labels.
Entry 7:
Pricing Sensivity by Ken Kranz

Ken writes, Another (simple/interactive) way I've used charting in Excel to enable a user to "see" potential pricing implications… "nothin' fancy, just Kranzy.”

Entry 8:
AutoCAD for Carton Industry by Ron Carroll

Ron writes, This is a program I have been working on for a while now for the corrugated carton industry. It is an inexpensive autocad program designed for that industry to create production specifications as well as estimates, quotes, Package load tags. As far as corrugated cartons go there are about 30 different carton styles and about 20 different thicknesses of corrugated board that can be used to make each. Depending on what combination is used it changes the allowances used in designing the carton. Currently the average small business owner buying cartons has no way to design the cartons they need. So this program is an inexpensive way to do it, it is a combination of excel and vba and as I said it has a long way to go yet, This copy is just a sample I did for one style of carton and it shows how you can import graphics and and printing to the specification. The actual program has all of the carton styles listed in a vb form window so the user can create any style he may need.

Entry 9:
Sorted Pareto without VBA by Henrick Wendel

You have to download this one to see how cool it is. All of the formulas in B are =RAND() functions. Click the checkbox to recalc, and a whole series of dynamic range names cause the chart to redisplay sorted values.

Entry 10:
Western Canada Grid System Maps by (contributor name removed at request of contributor)

This is a series of maps that were created created using Excel. The maps calculate the theoretical grid systems in Western Canada. The Dominion Land Survey (DLS) in Alberta, Saskatchewan, Manitoba, and the Piece River Block of British Columbia, and the National Topographic System (NTS) in the remainder of British Columbia. I created these routines as a prototype for a WEB GIS application that is currently in use by Enermarket Solutions. I used Excel to test out the calculations by using Excel to plot out the results. If you are interested, you can check out the production version at Enermarket's web site by downloading their EnerMap application. Normally I would fill in the coordinates of the area to plot and then hit the "map" button to generate the mat then I had some navigation buttons to zoom in/out and north/south and east/west. The sheets that I have shown are the output of the routines that remain proprietary.
Entry 11:
Alberta Pricing Map by (contributor name removed at request of contributor)

This second file shows a map of Alberta along with the Nova Gas Transmission pipeline system. In this example I used Excel to plot circles at the location of receipt meter stations and I varied the color of the circles to represent different price levels. This gave a quick visual representation of the results of a rate calculation. This work was done in relation to rate design work for the pipeline system. As you can imagine, we went through a lot of rate calculations and this map was very useful in showing the results of those calculations, certainly quicker than the traditional rate schedules that are essentially tables. This particular example just plots the meter stations on the map. But in actuality the outline of the province and the plot of the pipeline system were also created with excel. I originally used the map to display properties of the pipeline system rather than the meter stations in relation to hydraulic simulation results. As you can see in this map as compared with the above map, I included an orthographic projection that makes it more ascetically pleasing. Examples of these maps can be found on TransCanada Pipelines's web site in the regulatory and tolling pages and are referred to as "dot maps"

Entry 12:
Schematics Example by (contributor name removed at request of contributor)

This last file overlays information from an excel spreadsheet on top of an existing drawing. The attached file shows one use of this type of mapping, the status of individual pipes in the pipeline system relating to an allocation methodology being developed for cost modeling. The different colors relate to being in-service, retired, out-of-path, etc. In addition to the different line styles to represent different data, I'm also able to include labels directed either at the pipe units or the nodes. This involved a bit of work because of the vast amount of information being represented. I started out by copying in the scanned pages from a pipeline line schematics book into Excel. The attached file only contains one page of the pages. I then drew lines on top of the schematics and recorded the x and y position of all the points in the lines into a separate spreadsheet. This separate spreadsheet then was the cross reference between the unit/pipe name and its location in the excel file i.e. the workbook, sheet, and location. Having this information I could then just take the unit name and attach some information to it such as age. I could then use a routine to go through my list get the unit name and draw it over the correct drawing in the correct sheet and format it according to the Information I provided for it or add a label to the unit with the information I want displayed. As it turned out, this particular mapping has turned out to be very useful. Because in addition to the straight plotting of information on a familiar diagram, this also provided an excellent cross reference of where each of the pipe and node units were located in the schematics. For confidentiality reasons I have had to smudge some of the details on the original drawing.

Entry 13:
Interactive Chart by Prashant Nans

This is a nice management reporting tool. Select the product line from the dropdown, and click the blue button to have a small macro re-filter the data and present the chart.



Multiple: Excel Games

Submissions to the Oct 2002 challenge by this website to create a game or diversion in Excel. The Challenge specifications should give you an idea of what to expect - "Create something that will be interesting enough to cause other people to waste their valuable time playing the game!"

Entry 1:
Drive by Sean O'Sullivan

Sean says: "This one is similar to those old fashioned ZX81 games (an old UK Computer that was about 5 years before the PC was invented!!!) My game is still a little un-refined as its basically 2 lunch times and tonight finishing off..."
Entry 2:
Memory by Chris Leonard

Nice version of the children's memory game. Double click 2 squares to reveal the colored tiles underneath. The goal is to identify all matching pairs.
Entry 3:
3D Tic Tac Toe by Tim aka Bolo

Cool version of 3D tic tac toe.
Entry 4:
Screen Soother by Matt Naumann

A screen soother. Best viewed in 1028 x 768, the screen soother features colors that rapidly change in stunning patterns.
Entry 5:
Life by Jerry Hunter

A nice Excel version of the classic Life simulation. Comes with a number of pre-programmed start points or draw your own.
Entry 6:
FootyLeague by Russel Piper through Ivan Moala
FootyLeague by Jonathan Davies

Fantasy Soccer League (for our U.S. readers) or Football League (for everywhere else). Select the team you wish to own and run a simulation of the season. Jonathan Davies wrote the original simulation a few years ago. He shared it with a few mates and it was passed on to many people. There were bugs in the original version that became widespread. We offer two versions here, one last enhanced by Russel Piper, and the latest directly from Jonathan Davies with all of his latest bug fixes.
Entry 7:
Magic 8 Ball by Rich Needham

Rich says: "Here's my MagicEightBall program, more diversion than game. It's a fairly simple program using a random function to select from 60 different answers. Just ask a question which can be answered by "yes" or "no" and click around the MagicEightBall in the window to get your answer. Click again to reset the window. Click the small "X" button in the MagicEightBall window to exit the program. Just for fun, try grabbing the MagicEightBall window by the titlebar and dragging it around the screen to see a cool effect.
Entry 8:
Hangman by Joseph Cognard

Hangman. See, I am about to get hung in this initial game....
Entry 9:
Snakes & Ladders by Bob LaLonde

Nice game - visually appealing. Great for the kids. Bob says: "Snakes & Ladders. You can play with 2 players or Autoplay. I made this to amuse my son one rainy day, when the internet was slow.
Entry 10:
Tic Tac Toe 4x4 by Barry Tocher

Barry says: "Click on box that you want to put your x or o, Then click on the x player or o player button. To restart the game click on the start button" Bill says: I can't get this to work in XP - the color schemes must be different.
Entry 11:
Snake by Jochen Wriske

Wow! I couldn't put this one down. The classic snake game. You need to maneuver an ever-growing snake around the grid, trying to each the black squares and not running into yourself. As the snake lengthens and the speed increases, this becomes increasingly challenging.
Entry 12:
Master Mind by David Tate

David notes that this game requires RANDBETWEEN, so you have to have the analysis tool pack turned on. Nice implementation of the Master Mind peg game. The computer selects a random sequence of numbered pegs. You try to guess the number and sequence of the pegs and in response the computer tells you how many you have in the right sequence and how many you've selected the correct peg number but it is in the wrong sequence.
Entry 13:

Ioannis writes: This is like the Rubic cube but not 3D, just 2D, the game is like the bonus stage in an old coin op game 15 years ago but I cant remember its name.
The goal is to make the colors match the target. First, left-click a square outside of the color tiles to get the right arrow to appear. Then right-click the arrow to rotate the squares in that row, column, or diagonal. The first color becomes the last; the second become the first and the last become second (to the direction you right click). Beat the clock - you have 1.30 minutes to solve every Board. If you take longer than that, you get no points.
Entry 14:

Ioannis writes: The second one it looks like Tetris but it is not Tetris. It uses the Tiles like Tetris. The idea is to put Tiles together to make "floor" with specific dimensions such as 3x5, 3x20, 4x15. Every "floor" is a Level in the game, if you solve one Level (level 1 is 3x5) then you go to the next Level (unless you want to find another solution to win extra points) .. and so on .. until you go to the last Level, which is 6x15. The points-score for each level is equal to its dimensions, 3x5=15 points, 4x10=40 points and so on. The Tiles are 12, Every tile has 5 square cells. The levels where the Area of Floor is equal to 60 are the most difficult, because you must use all the tiles in a specific order and "shape" (Every tile have mirrors or rotated shapes relative to its self).
Entry 15:
Knights Game by Andy Pope

This is a solitaire game where the object is to move all of the BLUE marbles from ABOVE the line to BELOW the line and all of the RED marbles from BELOW the line to ABOVE, using a single space to move. Double click any marble to move it to the empty space. Each marble moves the same way as a knight in a game of chess - 2 spaces either horizontally or vertically, and then 1 space vertically or horizontally - forming an L.
Entry 16:
ExcellonII by Koichi Tani, translated by Colo

This is an impressive shooting game. Move the rocket with your arrow keys and fire at incoming targets. Hit the powerups for more power. This definitely gets the award for looking the least like Excel!
Entry 17:
3D Tic Tac Toe by Steve W (White6174)

Play against the computer in a 3x3x3 Tic Tac Toe board.
Entry 18:
Snake by Colo

Colo's Snake game. This looks impressive, but something is causing it to GPF on my XL2002 machine.



Multiple: Excel VBA downloads

Multiple Excel VBA downloads provided by Martin Green of

Pop-up Calendar Add-In for Excel
A fully-working Add-In (.xla) for Excel which opens a calendar when the user right clicks on a cell and chooses Insert Date or uses the shortcut CTRL+SHIFT+C. The file accompanies the Excel VBA tutorial A Pop-up Calendar for Excel. It is also available as an Excel workbook [Excel 97, 2000, 2002]

Introduction to Excel VBA: 1. Macro Basics
This Excel file and its accompanying notes guide you through the principles of recording macros and show you how to edit a recorded macro. [Excel 97, 2000, 2002]

Introduction to Excel VBA: 2. Using Loops
Loops are the one of the core tools used by the VBA programmer. This file contains several different examples and demonstrates how and why they are used. [Excel 97, 2000, 2002]

Introduction to Excel VBA: 3. Messages
Communicate with your users! Message boxes and Input boxes provide an easy way to interact with the user when your code runs. This file has several examples of both. There are no additional notes - check out the code and you'll find all you need. [Excel 97, 2000, 2002]

Introduction to Excel VBA: 4. User Forms
Master UserForms and you are on your way to creating full-scale applications in VBA. This file contains an example form and the accompanying notes explain how the code works. [Excel 97, 2000, 2002]



Multiple: Access VBA downloads

Multiple Access VBA downloads provided by Martin Green of Includes Pop-up Calendar Add-in for Access, Cascading Lists, and more!

Fontstuff Database
This sample database contains two tables - one containing the personal details of the staff of a fictitious company and the other containing the addresses of that company's offices. The staff table contains over 1000 records. I have used this database as the example in many of the Access and Access VBA tutorials. It is available in both Access 97 and Access 2000 (also suitable for Access 2002) formats and as a Zip file for faster download. [Access 97, 2000, 2002]

Access Pop-up Calendar Demo
A sample database to accompany the tutorial A Pop-up Calendar for your Access Forms. It is available in both Access 97 and Access 2000 (also suitable for Access 2002) formats and as a Zip file for faster download. [Access 97, 2000, 2002]

Cascading Lists Demo
A sample database to accompany the tutorial Cascading Lists. It is available in both Access 97 and Access 2000 (also suitable for Access 2002) formats and as a Zip file for faster download. [Access 97, 2000, 2002]

Multi Select Demo
A sample database to accompany the tutorial Making Sense of List Boxes. It is available in both Access 97 and Access 2000 (also suitable for Access 2002) formats and as a Zip file for faster download. [Access 97, 2000, 2002]

Coloured Tabs Demo
A sample database to accompany the tutorial Coloured Tabs for Your Access Forms. It is available in both Access 97 and Access 2000 (also suitable for Access 2002) formats and as a Zip file for faster download. [Access 97, 2000, 2002]

Combo Box Date Chooser Demo
A sample database to accompany the tutorial Build a Combo Box Date Chooser. It is available in both Access 97 and Access 2000 (also suitable for Access 2002) formats and as a Zip file for faster download. [Access 97, 2000, 2002]

Access and SQL Part 4: Building Queries "On the Fly"
A sample database to accompany the tutorial Building Queries On the Fly. It contains a table of sample data and the two forms containing the features described in the tutorial.

Access and SQL Part 5: More Instant Queries
A sample database to accompany the tutorial More Instant Queries. It contains a table of sample data and the two forms containing the features described in the tutorial.

Access and SQL Part 6: Dynamic Reports
A sample database to accompany the tutorial Dynamic Reports. It contains tables of sample data, a report and the five dialog box forms featured in the tutorial.



Multiple: Word VBA downloads

Multiple Word VBA downloads provided by Martin Green of

Automatic Document Template
A fully-working template for Word which employs a VBA UserForm to help the user fill in a standard letter. This file accompanies the Word VBA tutorial Build an Automatic Document Template for Word. [Word 97, 2000, 2002]

Simple Pop-up Calendar
A fully-working template for Word containing a Pop-up Calendar UserForm. Install this file in your Word Startup folder. This file accompanies the Word VBA tutorial A Pop-up Calendar for Word - Part 1: A Simple Calendar. [Word 97, 2000, 2002]



VBA Course Handbook

This handbook is published as a set of self-contained modules, each on a different aspect of VBA. Each module contains comprehensive illustrated notes and is accompanied by one or more practical exercises. Each one is supplied in both in both Microsoft Word (*.doc) and Adobe Acrobat (*.pdf) format.



Multiple: Access Examples

A compendium of Access 97 and 2000 routines, each separated into its own database. Each database illustrates one or more possible solutions to a single problem. They are not intended to be fully functioning utilities, but rather an illustration of how the problem might be solved and are therefore intended for educational use. These routines are entirely free. You may use them anywhere and in any way you choose, with or without citation.


Animation.mdb (intermediate)
This sample shows how to implement a variety of simple animations in an Access Form.

AreaCodes.mdb (expert)
This application is a utility to programmatically modify Area Codes. It illustrates a number of techniques. It reads files from a directory. It parses CSV files. It reads tables in the database and fields within the tables. It steps through a list (C

AuditTrail.mdb (intermediate)
This sample illustrates one way to produce a simple audit trail table containing: machine name, login name, user name (access security), record number, field name, original value, new value, and a date/time stamp.

AuditTrail2.mdb (expert)
This sample is a more advanced version of AuditTrail.mdb

AutonumberProblem.mdb (beginner)
This sample illustrates a number of ways to simulate your own Autonumber primary key.

BackUpWithCompact.mdb (intermediate)
This sample illustrates how to back up a database using the Compact Method. It creates a backup file called "BackUpWithCompact2kXX_XX_XXXX.mdb" with the X's representing the data. It puts this file in the same directory as the database. It also looks f

BoilerPlate.mdb (intermediate)
This sample is a useful template for an application.

BoilerPlate2.mdb (intermediate)
This sample is a useful template for an application. It is mostly the same as Boilerplate.mdb but with a different MainDataEntry Form.

Books.mdb (intermediate)
"Books.mdb" and it's companion database "Books_be.mdb", make up a simple personal book inventory database. It uses the "BoilerPlate.mdb" sample to create a complete application.

CalculateSpec.mdb (beginner)
This sample automatically calculates the Start Bit of an ImportSpec by adding the Start Bit and Length of the previous record.

CascadingComboBoxes.mdb (beginner)
This sample illustrates how to restrict the value of one combo box based on the value of another.

CascadingComboInSubform.mdb (intermediate)
Illustrates how to use Cascading Comboboxes in a continuous subform.

ChangingColorFields.mdb (beginner)
This sample illustrates how to change the color of a control on a form based on information in the form. In this case, if Check 14 checkbox is checked then the values of Desc will be red, otherwise it will be blue. You can also check for values in t

CharacterScramble.mdb (intermediate)
This application illustrates to scramble character data WITHIN A FIELD. To Randomize FIELDS WITHING A TABLE, see my sample Datascramble.mdb.

ChooseReportFields.mdb (intermediate)
This sample very roughly illustrates how to create a query programmatically which chooses the FIELDS to be reported on a report based on the selected values of a multi-select list box.

ChooseReportFieldsRows.mdb (expert)
This sample very roughly illustrates how to create a query programmatically which chooses the FIELDS to be reported on a report based on the selected values of a multi-select list box and then allows the user to specify the ROWS to be reported.

ChooseReportFromList.mdb (beginner)
These forms illustrate how to create a clean form for launching reports.

ChooseReportFromList2.mdb (intermediate)

These forms illustrate some advanced features in a "Report Switchboard". For less complex examples, see "ChooseReportFromList.mdb"
"Choose Report or Parameter Form" allows the user to select either a report directly or a form in which

ChooseReportFromList3.mdb (intermediate)
This sample shows an alternate way to open reports with a parameter form. It allows the user to select either a report directly or a form in which they can choose a parameter for the report.

CleanFileHTML.mdb (intermediate)
This sample removes the HTML tags from an html document and writes it into a separate text file. See Module 1 for the code.

ComboChoosesRecord.mdb (beginner)
This illustrates how to have a combo box in which you can choose a value and have that record appear in the form.

CompactBackEndDB.mdb (expert)
Illustrates how to compact a Back-End database from the Front-End.

CompactDatabase.mdb (expert)
This sample illustrates how to compact the current database programmatically. It assumes that the tables reside locally. (To compact a Back-end database, see my sample "CompactBackEndDB.mdb".)

CompareTwoTables.mdb (intermediate)
This sample shows how you can programmatically compare two tables and write out the differences to a third table.

ConditionalFormatting.mdb (beginner)
This sample illustrates how to change the color of a control on a form based on information in the form. In this case, if Check 14 checkbox is checked then the values of Desc will be red, otherwise it will be blue. You can also check for values in t

ContinuousFormWithNewRecordOnTop.mdb (intermediate)
This sample illustrates two ways to have a continous form with the new record on top. The first method uses unbound controls in the header of the form and the second uses a bound subform set to Data Entry.

ConvertDateTimeToFormattedString.mdb (intermediate)
This sample shows how to calculate the difference between two times and display the result in 'xx days, xx hours, xx minutes' format.

ConvertStringStuff.mdb (intermediate)
Demonstrates how to convert a variety of strings into their numeric equivalents and back.

CopyObjectsFromBE.mdb (intermediate)
This sample demonstrates how to copy objects from the Back-End database into the Front-End. In this specific case the objects are queries.

CreateDescriptionProperty.mdb (intermediate)
This sample demonstrates how to create and modify the "Description" property of the following Database objects: Tables, Table Fields, Queries, Query Fields, Forms, Reports, Macros, and Modules.

CreateMultiSelectQuery.mdb (intermediate)
This database illustrates how to create a query programmatically based on the selected values of a multi-select list box.

CreateQueries2.mdb (intermediate)
This database illustrates several examples of programmatically creating queries.

CreateQueries3.mdb (intermediate)
This form shows how to create a query programmatically from a form with option boxes.

CreateQueries4.mdb (expert)
This form illustrates how to create a relatively flexible inter-active query facility for an end-user. It allows you to choose the table, then shows the fields available for that table and then values available for the fields.

CreateQueries5.mdb (expert)
This set of samples illustrate how to create a flexible query generator for your users. It is similar to CreateQueries4.mdb, but no longer limits the query to 3 criteria.

CreateQueryFields.mdb (intermediate)
This sample illustrates how to programmatically create a query based on a table or another query and select only those fields that you want to see. It also has a form, which shows how to restrict the rows based on a criteria.

CreateTableFromLinked.mdb (intermediate)
This sample illustrates how to create a native Access 2000 table from a linked Excel table. This process can be easily modified to do the same thing for linked Text or ODBC tables.

CrossTabReport.mdb (intermediate)
The problem with crosstab queries has always been that if you try to make a report based on one, you inevitably have problems with field names when the crosstab changes. This sample demonstrates how you can get around this problem.

CSVtoFixed.mdb (intermediate)
This sample reads a CSV file and exports it as a fixed width file. Module 1 (CSVtoFixed) does the entire process in code. For very large records, Module 2 (CSVtoFixed2) uses a table to allow you to create your own Export Spec.

CurrentTab.mdb (beginner)
This form illustrates how to programmatically scroll between tabs of a tab control on a form. See the code behind the buttons for details.

DAO.mdb (beginner)
This database contains various examples of Data Definition Language (DDL) and Data Manipulation Language (DML) of both DAO and SQL.

DataEntryMask.mdb (beginner)
This sample illustrates a way to restrict what kind of characters are input into a textbox without using an input mask. This method checks the characters as they are typed and only allows legal characters.

DataScramble.mdb (intermediate)
This application illustrates how to scramble data in a table for confidentiality reasons. To scramble Text data WITHIN A FIELD, see my sample CharacterScramble.mdb.

DateStuff.mdb (intermediate)
This example illustrates how to do a variety of date calculations like: First day of Month, Last Day of Month, First Day of Next Month, First Day of Current Week, Last Day of Current Week, First Day of Current Work Week, Last Day of Current Work Week.

DeleteTablesAtStart.mdb (beginner)
This sample illustrates how to delete ImportError tables at start up. For illustration purposes, the RunMacro button executes the code, but if you rename the macro to AutoExec, it will run each time the database opens.

Denormalize.mdb (intermediate)
This example takes data in a normalized table and writes it to another table in denormalized form.

DirectoryList.mdb (intermediate)
This sample shows how to "role your own" directory/file list.

EmailingSpecificReports.mdb (expert)
This illustrates how to email the same report with different data to a variety of users.

ExportFormattedFixed.MDB (intermediate)
This shows how to export to a fixed width text file programmatically.

ExportToExcel.mdb (intermediate)
This sample demonstrates how to export data from a database using the TransferSpreadsheet method, but have the data populate a formatted spreadsheet. The trick here is to export the data to a NEW tab in the Excel workbook and link the fields from the new

ExpressionEvaluation.mdb (intermediate)
This form illustrates how to make a pop-up dialog to calculate values to put into a field on a form.

FlatFileDemo.mdb (beginner)
This example illustrates some of the difficulties inherent in bad (non-normalized) database design and how they can be corrected. It is not a formal tutorial on Normalization, rather it is a series of examples which allow you to demonstrate the kind of tr

FormSubform.mdb (beginner)
This form illustrates how to use Domain functions to do a variety of tasks in a Form/Subform situation.

GetPathFileName.mdb (intermediate)
This form shows how to extract just the Path or just the File name from the db.Name property.

Greenbar.mdb (beginner)
This sample illustrates how to make your report a "greenbar" report that is one that has alternating lines of color. Check behind the individual reports for the relevant code.

HasContinued.mdb (intermediate)
This example illustrates two methods of how to work around the 'HasContinued' bug, so that you can get a 'Continued' status for group headers within a report.

ImplementingM2MRelationship.mdb (beginner)
This example illustrates two different methods of implementing in a form a Many-to-Many relationship that has been resolved by a linking table.

ImportHTML.mdb (intermediate)
This sample illustrates how to use the LineInput statement to import text from a text file in an extremely unconventional format. The file is an HTML file, and the code searchs for the HTML tags to find specific information and puts that in a field.

ImportLineInput.mdb (intermediate)
This sample illustrates how to use the LineInput statement to import text from a text file with a mixed format. Each line of the file is a separate record, but is delimited in a variety of ways.

ImportSpreadsheet.mdb (intermediate)
This sample illustrates 3 things:
1) How to back up a database using the Compact Method,
2) Create a native Access 2000 table from a linked Excel table
3) Import data to an existing table.

ImportToTempDatabase.mdb (intermediate)
This illustrates how to programmatically create a database to hold a temp table for updating.

ImproveFormPerformance.mdb (intermediate)
One way to improve the performance of a form that is bound to a LARGE dataset is to split the data into smaller chunks. This form uses the "Phonebook" method to do that. The idea comes from those flip-phone indexes. Click one of the 27 buttons at the t

KeepingDatabasesInSync.mdb (intermediate)
Illustrates a method for keeping Access Front-Ends in sync with a Master Network Copy. Uses a DOS batch file.

KeepingDatabasesInSync2.mdb (intermediate)
Illustrates a method for keeping Access Front-Ends in sync with a Master Network Copy. This is an "All Access" solution, so does not require a batch file.

LinkTables.mdb (intermediate)
This example illustrates how to programmatically link tables from a users designated database.

LinkTextFiles.mdb (intermediate)
This sample illustrates how to programmatically link TEXT files to your database.

ListBoxExcludeOnClick.mdb (intermediate)
This sample illustrates two ways to remove a value from a bound list box after it has been selected (double-clicked).

ListBoxSetTableValue.mdb (intermediate)
This sample illustrates how to use a list box to update a "Selected" field in a table and also read which items are selected in the table and set those list box items to True.

ListBoxSorting.mdb (intermediate)
This sample database show various ways of sorting a listbox by clicking on the header of each column.

ListboxSubform.mdb (intermediate)
This sample illustrates use a list box to simulate a simple subform. The user can either double-click the listbox or click the edit button. Then a popup form appears which allows him to select multiple values. These values are written to a separate tab

ListBoxTypeIn.mdb (intermediate)
These forms illustrate how to use a binary search routine to jump to a value in a list box as you are typing in a text box. It is similar to how the Index tab works in Windows Help files.

LogUsersOff.mdb (intermediate)
This sample illustrates how to automatically log all of your users out of an application for maintenance purposes.

LogUsersOffNonUse.mdb (intermediate)
This sample illustrates how to automatically log your user out of an application after a period of inaction.

MaxQueryProblem.mdb (beginner)
The Problem: You want to create a query which will return a MAX Date and the corresponding Amount, grouping them on the Customer Name.

Median.mdb (intermediate)
Surprisingly, Access has no Median function, so you have to write one of your own. This sample database shows how to do that.

MoveList1.mdb (intermediate)
This example illustrates how to move values between list boxes much like many of the Access wizards.

Movelist2.mdb (intermediate)
Illustrates how to move values between multi-select list boxes much like many of the Access wizards.

MoveUpDownList.mdb (intermediate)
This sample illustrates how to make items in an unbound listbox move up and down in the list.

MultiColumnListBox.mdb (beginner)
Access Listboxes do not allow multiple columns that 'snake'. In order to build the funtionality, you must use multiple listboxes. This sample shows how.

MultipleLabels.mdb (intermediate)
This form illustrates how to print multiples of specific labels.

MultipleLabelsOffset.mdb (intermediate)
This sample is a more complete version of MultipleLabels.mdb. It is meant to be a simple minded stand-alone address label program.

MultipleLabelsOffsetBatch.mdb (intermediate)
This sample is a similar to MultipleLabelsOffset.mdb, but prints multiple labels for multiple people in a batch.

MultiSelect.mdb (intermediate)
Form illustrates two methods how to write values selected in a multi-select list box into a table.

MultiSelectProblem.mdb (intermediate)
There are two methods of moving data from a multi-list select box. This form illustrates why the ItemsSelected collection is not reliable.

NoDataInReports.mdb (beginner)
This sample illustrates how to cancel a report that has no data in it, using the OnNoData event of the report.

NormalizeDenormalize.mdb (intermediate)
It illustrates how to take a table which has fields like Value1, Value2, Value3,... and writes them into a table which has one field (Value) with each of the values in a new record rather than having them all in one record.

NotInList.mdb (intermediate)
This sample illustrates a variety of ways to use the NotInList event of a combo box.

NotInListAlternatives.mdb (intermediate)
This sample illustrates several methods other than Not In List to add new records to a combo box that is Limited to the List.

NumberedQuery.mdb (beginner)
This sample illustrates two different ways to create a query with a sequenced number column.

NumberPad.mdb (beginner) as of 7/14/2005
This sample shows how to create a number pad for inputting a Social Security Number.

OpenArgs.mdb (intermediate)
This application illustrates how to use OpenArgs to solve a variety of problems.

OrderListBox.mdb (intermediate)
This sample shows how to make a listbox reorder by clicking on the column header. It also shows how to use the same button to sort ASC or DESC

OtherProblem.mdb (expert)
This example shows one method of handling the situation where the user wants a combo box to pick from, but also wants to have an "Other" category where they can add free-text qualifying the "Other".

OutputText.mdb (intermediate)
This illustrates a simple way to precisely format data for text output.

ParaQueryDelete.mdb (beginner)
Illustrates a couple of different ways to use Delete parameter queries from forms.

ParaQuerySelect.mdb (beginner)
This form illustrates how to send parameters to a parameter query via a form.

ParseStuff.mdb (beginner)
The included modules demonstrate a variety of parsing problems, where information in one field needs to be put into several fields.

Pictures.mdb (beginner)
It is usually preferable NOT to store OLE objects like pictures in the table itself. It causes massive database bloat. The usual solution is to store the pictures as files on the hard drive and store the file name in the table. This sample demonstrates

PicturesLoad.mdb (intermediate)
This sample demonstrates how to use the OpenSaveFile API to find the picture file you want loaded into the database.

PrintingSpecificReports.mdb (expert)
This illustrates how to print the same report multiple times with different data.

PrintMultipleLabel.mdb (intermediate)
This form illustrates how to print multiple labels which matches the value of the record in the form.

PrintOneLabel.mdb (intermediate)
This form illustrates how to print one label which matches 'the value of the record in the form.

RandomRecords.mdb (beginner)
This application shows how to return a specific number of random records from a table.

ReadTableFields.mdb (expert)
This sample demonstrates a couple of uses for Reading the table structure programmatically.

ReallyBadDatabase.mdb (beginner)
This ZIP file contains two databases: "ReallyBadDatabase.mdb" and "ReallyBadDatabaseReborn.mdb". The first, of course, represents several common errors made when developing your first database. The second shows one way to properly design the database.

RecordNavigation.mdb (intermediate)
This form illustrates how to exactly duplicate the actions of the default record navigation buttons including the RecordNumber Of TotalRecords feature

Reduce97.mdb (expert)
This was originally designed as a way to delete large amounts of data from very large databases.

RelinkOnOpen.mdb (intermediate)
This sample illustrates how to automatically relink the tables if the database has been moved.

RemoveDuplicates.mdb (beginner)
Illustrates a method for removing duplicate records from a table.

RemoveSpaces.mdb (beginner)
This sample removes the spaces and carriage returns from text typed into the text box.

ReportInBackEndDatabase.mdb (intermediate)
This sample illustrates how to run a report that exists in the Back-End database.

ResettingReportPageNumbers.mdb (beginner)
This sample illustrates how to reset page numbers back to 1 for each group when grouping a report.

ReturnSQLfromQuery.mdb (beginner)
This sample demonstrates how to read the SQL statement from a saved query using the .SQL property of the querydef object.

RoundingStuff.mdb (beginner)
This sample shows a variety of rounding techniques, the most common being rounding to the nearest x decimal places. The function RoundIt is similar to the Round function in VB. All the code is stored in the basRounding module.

RunningSumInQuery.mdb (beginner)
This sample illustrates how to create a Running Sum in a query.

RunRemoteMacro.mdb (intermediate)
This sample illustrates how to run a macro in a remote database to automate a process.

SaveFileToSpecificDirectory.mdb (intermediate)
This sample shows how to use the Windows API to bring up the OpenSaveFile dialog box and save a file to a directory of your choosing.

SeekWithLinkedTable.mdb (intermediate)
It is usually said that the Seek method cannot be used with a linked table. This is true, it cannot. However, it WILL work if you open the table in the remote database explicitly with the OpenDatabase method. This sample illustrates how to do this.

SelfJoin.mdb (intermediate)
Demonstrates a number of ways to use a Self-Join in Access 97.

SetHyperlink.mdb (intermediate)
This sample allows the user to double-click on a control, browse to a file, and save the filename in a field in a table.

SettingLabels.mdb (intermediate)
This form gets the values of the labels from the Description of the Field in the table.

SigFigs.mdb (beginner)
This example illustrates how to round a number to a specified number of significant figures.

Snapshot.mdb (beginner)
It is usually preferable NOT to store OLE objects like snapshots in the table itself. It causes massive database bloat. The usual solution is to store the snapshots as files on the hard drive and store the file name in the table. This sample demonstrat

SQLDAOLoader.mdb (expert)
This sample shows how to modify the structure of a Back-End database using SQL DDL (data definition language) statements and DAO code. This sample does much the same as "SQLLoader.mdb", but does not require text files to hold the SQL. Further, it utiliz

SQLLoader.mdb (expert)
This sample shows how to modify the structure of a Back-End database using SQL DDL (data definition language) statements. The actual code is found in the Back-end database (SQLLoaderBE.mdb).

StringStuff.mdb (intermediate)
This sample illustrates a number of useful string functions. See the individual functions for details.

SubForm3Levels.mdb (expert)
Access does not allow more than two levels of subforms. At times, however, your data model indicates the need for more levels. This is one way to implement a third level through the use of a list box.

SubformReference.mdb (Beginner)
This sample illustrates how to reference controls on subforms and subsubForms. It also illustrates how to set the focus to these controls.

SubQuery.mdb (beginner)
This sample illustrates how to use a Sub Query to return the Maximum value of a group and the values of the other fields associated with it.

SubQueryInFROM.mdb (beginner)
Demonstrates how to use a subquery in the FROM clause of a query

SynchronizedSubforms.mdb (beginner)
This sample illustrates how to keep two subforms synchronized with each other on a main form.

TooFewParameters.mdb (beginner)
This sample illustrates two ways to open a DAO recordset based on a saved query with parameter values stored in a form.

TopQuery.mdb (beginner)
Illustrates issues involving the TOP predicate

TrainingRegistration.mdb (intermediate)
This sample is a general-purpose training registration database. It is a complete application that demonstrates many of the features of the other sample databases.

TwelveBalls.mdb (beginner)
A fun problem in Nasty Nested IF statements

UIDesign.mdb (intermediate)
Shows a variety of samples of good and bad User Interface design techniques.

Unbound.mdb (intermediate)
This form illustrates how to use UNBOUND controls to add, delete, and edit records in a table using the DAO add/edit/update methods.

UnboundSQL.mdb (intermediate)
This form illustrates how to use UNBOUND controls to add, delete, and edit records in a table using SQL statements, rather than the DAO add/edit/update methods.

UpdateDirect.mdb (intermediate)
This sample illustrates how to programmatically update a table directly from a text file.

UpdateDirectFixed.mdb (expert)
This sample illustrates how to programmatically update a table directly from a FIXED WIDTH text file.

UpdateImportSeekFind.mdb (intermediate)
This sample illustrates how to programmatically update a table from a temp table.

UpdateRemote.mdb (expert)
This sample illustrates how to run an update to a Back End (BE) database remotely for performance reasons.

UpdateWStatus.mdb (intermediate)
This database illustrates two things: 1) How to update a table programmatically with values from another table and 2) how to implement a pop-up status meter.

UserSeesOnlyHisRecords.mdb (beginner)
This sample shows how you can restrict data being displayed to just those records that match the user's network Login ID.

VacationUsed.mdb (beginner)
This sample illustrates one way to dynamically calculate Vacation Days Available

YearsMonthsDays.mdb (intermediate)
Below are a variety of functions created by different developer's to calculate the difference between two dates, which can also be used for Age Calculations. Some work in some situations and not in others. You'll have to decide which is best.

Zip97.mdb (intermediate)
This sample was created before the advent of WinZip in an attempt to automate the compression process. It uses a DOS version of PKZIP an Zip2Exe. It will also create an self-extracting zip file.

Roger's Access Library



Multiple: PowerPoint templates

Brought to you by Sonia Coleman, these 208 free PowerPoint template kits are for your personal use in the presentations you create for yourself or others. Each set contains 8 templates. They can be used for any presentation you are creating for yourself, a client, your job, school, church, etc.




Multiple: Word templates & utilities

Brought to you by Malcolm Smith, there are a number of Word templates and utilities here to make using Word easier!

Memo With Database
This is an example of a simple Memo template. However this example shows how one could have a database that holds information on the sender. For example, in a company one may want to have details of the sender's name, phone and fax number. So, all one has to do is to select the initials from a list and then the rest of the work is done for the user. Of course this could be expanded to have details such as e-mail address and whatever. But, the idea of this code is to demonstrate what can be done; it is up to the developer to expand upon this to suit one's own requirements.

Letter Template - 1
Malcolm Smith says – “Most of the documents which I use are based upon a template. When, for example, I am writing a letter I use this template. Please feel free to download and use it.”

Letter Template - 2
An enhancement on the above letter template is that there is a button on the letter dialogue box that prints the recipient's address onto an envelope.

Email With Outlook
Malcolm Smith says – “Recently I was asked to help a company in California to create some code which would enable them to send some data from a bookmark in a template to a certain address by a button click. This template simply opens up an Outlook 2000 object, creates a mail object and then sends it.”

Find & Replace Template
This is a simple template that was just knocked up in a matter of moments. All this does is maintain, in the document, a list of phrases which one wishes to change. This version lets the user search through a directory structure for the list of documents in which to search and replace.

Clear Clipboard
A simple enough request; how does one clear the clipboard? This code does just this. Load this template into the Startup folder. This code uses the DataObject object.

Glossary Builder
This code will copy the selected text from one document and into another document (the 'Glossary Document') where the insertion point was.

New Document
Quite often the default New Document dialog box, as presented by Word, is a pain. If one has a load of different templates then it is quite possible that one doesn't want to go trawling through folders and large icon views of templates. This, then, is an alternative. What this offers is a simple dialog box which pops up when the user clicks on the new document icon or on File|New and is given a list of templates which are listed in the associated Templates.ini (attached).




Motion Path Tools add-in

This freeware add-in features the following handy tools:
1. Motion Path End Position: It will create duplicate shapes at the position where each of the selected motion path animation ends. Using this tool you can quickly determine the end position of the animation at design time.
2. Align/Join Motion Paths: This tool can align the selected motion path assigned to a shape end to end. It can also consolidate the individual segments of motion to create a single continuous path.

OfficeTips by Shyam Pillai




Workarea: Free add-in for PowerPoint

This free add-in is a PowerPoint implementation for the Work menu that is available within Microsoft Word.

OfficeTips by Shyam Pillai




Shape console (PowerPoint 2000 or later)

Shape console will display a miniature-floating window within PowerPoint, which will show the current selected shapes on the slide. This is very handy when you wish to tab through shapes that are layered below another. You will always know exactly what shape is selected with this application.

OfficeTips by Shyam Pillai




Music Span: add-in for PowerPoint

With this add-in you can play and loop a custom audio track across slides. You can play a single clip across slides or play different clips for each slide. The supported audio formats are WAV/MP3/WMA/MIDI.

OfficeTips by Shyam Pillai




Sequential Save: add-in for PowerPoint

This add-in will add a button to the Standard toolbar, every time you click on that button, it will create a backup of the last saved version on the disk and then will save the existing file. You can either create a single backup file or multiple sequential backups. The add-in does not delete any presentation nor does it change the original presentation in any manner.

OfficeTips by Shyam Pillai




Word/Phrase search add-in

Search and locate keywords/phrases in a presentation during edit mode or slide show mode. It will list out the occurrences. Click on the desired item and it will jump to that slide. This is a free add-in.

OfficeTips by Shyam Pillai




Capture Show add-in

PowerPoint 2002 did away with the ability to print slides with the animations. This add-in is a replacement for that functionality and more. The add-in captures slide builds as you present. You can capture builds into slides of a presentation or as images into a specified folder.

OfficeTips by Shyam Pillai




Color Scheme Manager

You can store 16 color schemes within a template/presentation. However there is no quick approach available to copy a color scheme from one presentation to another. The most common approach would be to apply a template/presentation containing the desired color scheme into the presentation.

Color Scheme Manager allows you to create a master database of color schemes which can be assigned names. One can copy import/export selected color schemes from/into other presentations at a click of a button.

OfficeTips by Shyam Pillai




Single Slideshow Window Add-in

This add-in is designed to ensure that any point in time only one slide show window exists. This is very useful when you have several linked slideshows. By default PowerPoint opens a separate slide show window for each linked show while leaving the original window open. You are left with several windows and one needs to exit out of each window manually. This add-in rids you of that annoying task. When a new slide show window is opened the add-in will close the previous one and hence at any point only one window will be running.

OfficeTips by Shyam Pillai





"How do I rewind Flash Movies automatically during a slide show?" This question comes up often on the Microsoft Public PowerPoint Group, so Shyam Pillai finally decided to work on it. FlashBack is the result. FlashBack will rewind the Flash movies inserted using the Shockwave Flash control automatically.

OfficeTips by Shyam Pillai





Use LiveWeb to insert web pages into a PowerPoint slide and refresh the pages real-time during slide show. Display web pages without ever leaving the confines of your PowerPoint slide show. No coding required. LiveWeb works with documents off your local drive too. You can specify relative paths. LiveWeb will also look for files in the presentation folder if the files have local drive information and cannot be located at the location specified by the user during slideshow. LiveWeb encapsulates the need to insert a web browser control manually and write code to update the web pages within the control during the slide show. It consists of two components.

1. Wizard component - Create a list of web sites which you wish to add to the slides.

2. Real-time update component - Automatically refreshes the page every time you visit the slide that contains the web browser control.

With LiveWeb you can display acrobat documents (PDF), java applets, VRML etc within the slide show real-time.

OfficeTips by Shyam Pillai





LiveImage automatically refreshes the linked image every time you visit the slide which contains the image. Just load the add-in and start the show.

OfficeTips by Shyam Pillai





Corrective flip for images when older version presentations are opened in XP. The add-in will perform the task correctly provided the presentation opened has not been saved previously under XP. PowerPoint XP resets the flip values when a save is performed hence this fix works only for presentations which haven't been saved in PowerPoint XP.

OfficeTips by Shyam Pillai





PowerPoint updates the links contained with a presentation when it is opened and when the slide show begins, it does not refresh this information during the course of the show if the show has been set to loop. This add-in performs that task. It will refresh all information within the running show while it loops.

OfficeTips by Shyam Pillai




No ESCape Add-in (for 2000/XP)


A User can exit out of a show accidentally/intentionally by pressing the ESC key. This add-in disables the functionality of the ESC key.

Note: If the show is set to run in Kiosk mode, disabling the ESC key will provide no way of getting out of a slide show, hence please ensure that you have provided an escape route (e.g an invisible shape set to End show) to exit the show.

OfficeTips by Shyam Pillai



MS Office

FaceID browser for Microsoft Office

Microsoft Office menus contain a lot more icons that are hidden from the general user. These can be suitably used by developers for their customized solutions. However determining the FaceID value of a particular icon can be time consuming. FaceID browser is a tool that is just right for the task. It's quick & simple. Check it out.

OfficeTips by Shyam Pillai




Custom Show Print Add-in ver 1.7.1


1. Resolves the Page numbering issue when dealing with Custom shows in PowerPoint
2. Print specific slides within the custom show with appropriate page numbers using the Print range options.
For PowerPoint 97 or later.

OfficeTips by Shyam Pillai




AutoEvents Add-in (for PowerPoint 2000 and later)

PowerPoint does not include any automatic macro support that is available in Excel & Word. The only ones available are Auto_Open & Auto_Close macros. However these fire automatically provided they are within an add-in.

PowerPoint 2000 introduces application level events. Taking advantage of it, it's possible to create our own bunch of automatic macros that get fired. This add-in does just that. It executes the following macros, if present, in a presentation (Please note the declarations).

Sub Auto_Open() - Gets executed immediately after the presentation is opened.

Sub Auto_Close() - Gets executed prior to the presentation is closed.

Sub Auto_Print() - Gets executed prior to the presentation being printed.

Sub Auto_ShowBegin() - Gets executed when the show begins.

Sub Auto_ShowEnd() - Gets executed when the show ends.

Sub Auto_NextSlide(Index as Long) - Gets executed before the slideshow moves onto the next slide. Index represents the SlideIndex of the Slide about to be displayed.

OfficeTips by Shyam Pillai




Set Default View Add-in (for PowerPoint 2000/XP)

PowerPoint 2000 always opens a new presentation in the Normal view. There is no way to revert this behavior except to switch to the desired view and then ensure that the file is saved in this desired view so that the next time it is opened it will open in this view. The Set Default View add-in is specifically designed to tackle this annoyance. Load the add-in and it will add a Views combo box on the Standard Toolbar. Using which you can set the default view in which a presentation should be opened whenever you start a new presentation or open an existing one for editing.

OfficeTips by Shyam Pillai




Set Default Layout Add-in (for PowerPoint 2000 or later)

PowerPoint always creates a new presentation with a slide with the Title layout. There is no way to set a default layout to be used whenever a new slide is inserted into the presentation. The Set Default Layout add-in is specifically designed to tackle this annoyance. Load the add-in and select the default slide layout, from this list.

OfficeTips by Shyam Pillai




Multiple: Data Models

Provided by Microsoft Access Database Solutions.

Here you will find free examples of some database design, data models. These models will cover sample database designs and will be fully structured and normalized to allow future expansion.
Whilst these models cover certain entities and facts, they should be used only as guidelines as to the database design that you are intending to implement.

The following data models are available for free download

Book Collection
Employees and Projects
Customer Product Orders
Video Rentals Store
Holiday Cottage Bookings
Fishing Lakes and Members
Motor Vehicle Insurance Policy Management
Golf Clubs and Membership Handicaps
Personal Contacts and Addresses
Vehicle Details and Information
Football or Soccer Team Players and Fixtures
Students, Courses and Tutors

Microsoft Access Database Solutions




Multiple: Database Examples

Provided by Microsoft Access Database Solutions.

Here you will find Microsoft Access Database examples and sample files available to download. These files are intended to demonstrate particular examples and functionality available within the Microsoft Access development area. All files are zipped using WinZip and are created in Microsoft Access 2000 unless otherwise stated, they should also operate using Microsoft Access 2002 (XP) or Microsoft Access 2003.

Example of Many-To-Many Relationships
Example of creating a Login Form
How to create a simple text search facility
Example of creating a Start-Up Form
Example of using the Common Dialog API in an Access Database
Example of opening Microsoft Word documents from an Access Database
Sample forms/reports/queries
Filtering the results of a query using a Drop-Down combo box
Sending E-Mails From Microsoft Access
Generating a List from Microsoft Access System Table
Filter one combo box based on another combo box selection
Automatically Increment a Fields Value
Using a Microsoft Access List box to pass criteria to a query
Searching Records using a Microsoft Access List Box
Sorting Columns in a Microsoft Access List box
Filter to only view Current User's Records in a form or query
Viewing a Live Web Site on a Microsoft Access Form
Using an Option Group to Filter Records Displayed in a Form
Sorting Report Fields on the Fly
Checkboxes and using the Triple State Property
How to use the Not In List event of a combo box
Preventing Duplicate Records from being entered via a form
Popping Up Reminders in Microsoft Access
Stop mouse wheel from scrolling through records in a Microsoft Access form
Printing the Record on the Form to a Report
Date Parameters for generating Microsoft Access Reports
Automating Archiving Records in Microsoft Access, using Append and Delete Queries
Showing or Hiding Microsoft Access Subforms
Sending E-Mails from a Microsoft Access Form

Microsoft Access Database Solutions




Multiple: Excel examples and add-ins

Provided by Charles Williams of

This page contains the FastExcel example problems and Excel Add-In files he has written for Excel 97, Excel 2000 and Excel 2002.

NameManager V3.2

If you are in need of a utility to manage defined names in your workbooks, this one is a must-have.

List all names in your active workbook.
Easily work with long names and long refers-to formulae.
Filter names using 13 filters, e.g. "With external references", "With errors", hidden, visible.
Combine filters using And/Or.
Show just names that contain a substring.
Show just names unused in worksheet cells.
Work with single or multiple selected names.
Edit Local, Global, Relative and Absolute names.
Edit them in a simple dialog or make a list, edit the list and update all names in one go.
Delete, hide, unhide, localise, globalise selected names with a single mouse click.
Evaluate, Analyse and GoTo names.
A1/R1C1 toggle.
Works with localised Excel versions (English and European).
Screens and error messages in 7 languages.
Modeless multi-workbook working with Excel 2000, 2002 & 2003.

Counting Functions: run-time functions for the FastExcel Dynamic Range Wizard

This addin contains a set of functions designed to be used in Dynamic Range Names. The addin can be used as a runtime library for workbooks whose dynamic range names have been geberated by the FastExcel Dynamic Range Wizard. The addin works with Excel 97, Excel2000, Excel 2002 and Excel 2003.

RangeCalc Add-In: one of the FastExcel command buttons

This addin adds a Calculate Range button to Excel. This button calculates the currently selected cells, and times the calculation using the MicroTimer high-resolution timer. RangeCalc automatically expands the current selection to include all the cells of any multi-cell array formulae that partly intersect the selected range. The addin works with Excel97, Excel2000 and Excel2002, and bypasses the Iteration and Array Formulae quirks in Excel2002.

Add-In Loader Version 2

The Add-In Loader is designed to solve some of the problems of maintaining and loading Add-Ins on a network:

* Dynamic reversioning of add-ins with automatic link updating.
* Control from a central point which add-ins will be automatically loaded when Excel starts.
* Control from a central point which add-ins will be available for on-demand loading.
* Enable updated versions of an add-in to be placed on a server at the same time as previous versions are in use.
* Optionally specify and/or change the network path for each add-in.
* No Registry changes for load and unload.

The Add-In Loader helps the User:

* Improve Excel start-up time by bypassing the loading of add-ins that are only needed intermittently.
* Minimize memory use by loading add-ins when needed and easily unloading them when not needed.
* Dynamically load the latest version of an add-in, whilst unloading any version that is currently loaded.
* Dynamically update any links that point to old versions of loaded add-ins when a workbook is opened or an add-in is loaded.

Calculation Tracer

This Add-In file contains User Functions and a macro designed to enable you to explore Excel's calculation sequence and dependency handling.
Note that interpreting the results and designing conclusive experiments can be complex.

The functions work by incrementing and returning a counter each time they are calculated:

* ZeroCalcSeq - a Macro which sets the counter to zero.

* CalcSeqCountSet(theInput) - Function - Counter is set to theInput.

* CalcSeqCountRef(theRange) - Function - Counter is incremented as a dependency on theRange.

* CalcSeqCountVol() - The function is volatile: - Counter is incremented at each Calculation - used to check the effect of volatile on calculation sequence.

Memory Limit example of interlinked sheets

A workbook with a VBA macro that generates a variable number of interlinked worksheets. It demonstrates an extreme case of the memory used by interlinked sheets, some of Excel's memory limits, and that it is possible to create a workbook that you cannot re-open!

UDF Areas Bug Example and Bypass

Howard Kaikow, discovered a bug that can occur in Excel's processing of D119multi-area input arguments for a UDF, and can result in the UDF calculating incorrectly. The bug exists in Excel 97, Excel 2000 and Excel 2002. Some of Excel's own functions also have this problem (but not SUM).
The bug occurs when a UDF has a multi-area input argument that refers to the sheet containing the function formula, and Excel recalculates the function when a different sheet is the activeSheet. In this case the function will incorrectly use data from the activesheet.
It is possible to bypass the bug at the expense of making the UDF Volatile, modifying it and adding a dummy worksheet containing a worksheet calculate event.

High Resolution Timers

This Add-In contains VBA function wrappers for Windows API high-resolution timers. These functions are useful when you need greater accuracy than is available from the VBA Timer (which turns out to be most of the time). VBA Timer is also quite slow to execute.
Portions of the code in these functions are from Ken Getz and John Green.
Mostly I use the code in these functions as timers within VBA, but you can also use them as UDF's.
To use as a timer, call the function, execute the thing you want to time, call the function again and subtratct the second time from the first.
The functions measure elapsed time rather than processor time.
The two functions are:
Microtimer: returns a Double containing seconds. Resolution about 1 microsecond and takes about 5 microseconds to execute in Win ME at 1200MHZ
Millitimer: returns a Long containing milliseconds. Resolution about 1 millisecond and takes about 5 microseconds to execute in Win ME at 1200MHZ




Multiple: Sample spreadsheets: Data Validation

Provided by Debra Dalgleish of

Dependent Cell List Offset:
Select a region from the first dropdown list, and the dependent cell's validation list shows only the customers in that region. Based on a lookup table sorted by Region.

Dynamic Chart: Select Start and End dates from Data Validation lists; chart updates automatically to display sales for selected time period.

Input Message in Textbox: Select a cell that contains a data validation input title or message, and a textbox appears. Control the size, position and formatting of the message.
Dependent ShipTo Location: Select a customer and then select a shipping location from the dependent dropdown. VLookup formulas return the shipping address for the selected location.

Dependent Data Validation Combobox: Double-click a cell that contains a data validation list, and a combobox appears -- font size can be set, more than 8 rows displayed, autocomplete can be enabled. List in second column is dependent on selection in first column.

Create Chart from Current Data: Select a value from a data validation dropdown, and an event procedure captures the current data, and creates a scatter chart.

Dependent Cell List Lookup: Select an item from the first dropdown list, and the dependent cell's validation list shows only the accessories available for that item. Based on a lookup table where applicable accessories are marked.

Dependent Cell Examples: Two samples of code altering dependent cells.
1) Selection from two lists must be different, or cell is cleared
2) Select from either list, and adjacent cell is filled in.

Data Validation Combobox Named Lists: Double-click a cell that contains a data validation list, and a combobox appears -- font size can be set, more than 8 rows displayed, autocomplete can be enabled. Lists are stored in named ranges on a separate sheet. Macros must be enabled.

Update Dependent Cell: Selection from first list runs event code that selects the first item from a dependent list in an adjacent cell.

Variable Validation: Selection from the first list runs event code that changes the validation in adjacent cell.

Data Validation Spinner: Use a spin button control to select the next or previous item from a data validation list.

Cooking Time Planner: Plan your meal preparation, using Data Validation to enter food items. Formulas calculate the cooking schedule, based on the target mealtime.

Limit Selection List: If you have a lengthy list, it's hard to find an item in the data validation dropdown. Use an Advanced Filter to create a short list, and select from that. Event code creates the short list.

Update Validation Selections: If you change an item in a data validation source list, the worksheet may show previously selected items. Event code can update the worksheet when you update the source list.

Update Multiple Validation Lists: In a workbook with multiple data validation lists, type a new value in a cell that contains data validation, and it's automatically added to the appropriate source list, and the list is sorted; a macro automates the list updates.

Data Validation Combobox: Double-click on a cell that contains a data validation list, and a combobox appears -- font size can be set, more than 8 rows displayed, and autocomplete can be enabled. Macros must be enabled.

Default to First Value: Data validation limits values that can be entered; event macro enters the default value for the selected option.

Happy Face Gauge: Data validation limits values that can be entered; event macro adjusts the curve.

Select Multiple Items from Dropdown List: Select multiple items from a dropdown list; an event macro stores selections in adjacent cell, or in same cell.

Assign Employees to Single Task Per Day: Names are removed from data validation dropdown list once they've been assigned to a daily task.

Create Dependent List for Selected Column: The first dropdown list is based on column headings. The second list contains unique items from the selected column, sorted in descending order.

Combine Multiple Lists into One: A data validation list from a worksheet must come from contiguous cells in a single column or row. This example uses formulas to combine three dynamic lists into one master list.

Cross Dependent Validation Lists: Selection from the first list controls the items available in the dependent cells. Selections in the dependent lists control the dropdown items in the first list.

Update Validation List: Type a new value in a cell that contains data validation, and it's automatically added to the source list, and the list is sorted; a macro automates the list updates.

Dynamic Validation List: Shows customers with start and end dates that include selected date, macro automates the list creation.

Model Pricing Scenario: Uses data validation to create dropdown lists, Scenarios to store variables, macro automates scenario display.

Purchase Order: Uses data validation to create dropdown lists, VLookups to return values from named ranges on different sheets.

Data Validation Checklist: Uses data validation to create dropdown lists, with only checked items appearing in the list.

Chart Selected Date Range: Uses data validation to create dropdown lists, and dynamic named ranges to plot the selected date range.

Machine Capacity: Uses data validation to create dropdown lists, and the VLookup and Match functions to extract information from a table.

Data Validation "Columns": Data Validation dropdown displays product name and ID; an event procedure changes the selection to product name.

Data Validation Change: Select a Product from the Data Validation list; an event procedure changes the product name to a product code.

Order Form: Select items from the dependent Data Validation lists; a VLookup formula extracts the unit price.

Invoice for Selected Number: Uses Data Validation and VLookup to extract details for an invoice.

Assign Employees: Ensure that each employee is only assigned once per day




Multiple: Sample spreadsheets: Filters

Provided by Debra Dalgleish of

Colour Filter Headings: Event code colours the filtered column headings, when an AutoFilter is applied.

Create New Sheets from Filtered List: Uses Advanced Filter to create separate sheet of orders for each sales rep visible in a filtered list; macro automates the filter.

Map Based Filter: Uses code to extract regional data for the selected map shape.

Extract Random Records: Uses code to extract a random set of records from a database.

Summarize Budget Details: Uses Advanced Filter to summarize budget details for selected month, or full year; macro automates the filter.

Update Sheets from Master: Uses an Advanced Filter to send data from Master sheet to individual worksheets -- creates a list of unique items, creates a sheet for each item, then replaces old data with current.

Filter Rows for Text String: Uses Worksheet Change event, and Advanced Filter, to display rows which contain specifiec text string and/or region.

Highlight AutoFilter Headings: User Defined Function, and conditional formatting, highlight column headings where filters are applied.

Filter for Date Range: Uses Data Validation and Advanced Filter to extract a list of orders in selected date range; file contains a macro which automates the filter (XL2000 & XL2002 only)

Phone List for Selected Name: Uses Data Validation and an Advanced Filter to extract a list of customers with the selected Last Name; file contains a macro which automates the filter (XL2000 and XL2002 only)

Create New Sheets from List: Uses an Advanced Filter to create separate sheet of orders for each sales rep; macro automates the filter.

Product List with Chart: Uses Advanced Filter to extract a list of products by Category; dynamic chart displays the results; file contains a macro which automates the filter

Product List by Week Number: Uses Advanced Filter to extract a list of products for the selected week number; file contains a macro which automates the filter

Product List by Category: Uses Data Validation and Advanced Filter to extract a list of products for selected category; file contains a macro which automates the filter




Multiple: Sample spreadsheets: Conditional Formatting

Provided by Debra Dalgleish of

Highlight Amounts Over Limit: A formula totals hours worked. Conditional formatting highlights hours over regulated limit. Pivot table totals weekly hours.

Highlight Column Headings: To guide users, highlight columns headings when an item is selected from a data validation dropdown list.




Multiple: Sample spreadsheets: UserForms

Provided by Debra Dalgleish of

Parts Database with Comboboxes: UserForm with comboboxes for data entry, with database on a hidden worksheet. Pivot table summarizes the inventory.

Parts Database: Simple example of creating a UserForm for data entry, with the database on a hidden worksheet.

Assign TeeOff Times: Uses an Advanced Filter to schedule selected players in specific time slot; file contains a macro that automates the filter. This version has a UserForm in which to enter data:




Multiple: Sample spreadsheets: PivotTables

Provided by Debra Dalgleish of

Pivot Page Spinner: Use a spinner from the Control toolbox to select the next item in the page field.

Change Pivot Source Data: Modify captions in a pivot table, and the matching data in the pivot table source is changed.

Hide Page Field Items: Mark items on a worksheet list, and hide those items in the page field. Change page field selection in main pivot table, and same selection is made in related pivot tables.

Change Page Field: Change page field selection in main pivot table, and same selection is made in related pivot tables.

Pivot 97/2000 Show Marked Items: Mark items on a worksheet list, and display those items in the pivot table.

Multiple Consolidation Ranges: Create a normal pivot table from multiple ranges, by using MS Query to join the data. One sample file contains the pivot table, the other contains the source data tables.

Employee Time Tracking: record hours worked; Pivot Table report and chart




Multiple: Sample spreadsheets: Functions

Provided by Debra Dalgleish of

Dynamic List:
Uses Vlookup formula to create a dynamic list of completed items.

Grant Distribution: Uses ranking to distribute available funds to applicants, based on request amount.

Database Functions: Uses DSUM and DCOUNT to calculate totals in a database.

Print Unmarked Invoices: Uses VLookup to create an invoice; a macro prints unmarked items from the list, and marks as printed.

Invoice for Marked Item: Uses VLookup to create an invoice for the marked item in a list; a macro clears old marks.

Extract Items with Formulas: Extract items from a list to a new workbook, with formulas intact; contains a macro.

Extract List of Unique Items: Use formulas to extract unique items from a list.

Budget vs Actual: Enter budget and actual amounts; summary sheet shows totals year to date; contains macros.

ComboBox Selection: Uses a combo box and the Index function to extract an address from a list.

Bingo Cards: The RAND function is used to produce sets of 3 unique Bingo cards.

Date Calculation: enter today's date; create list of upcoming Wednesdays/Saturdays

Daily Walking Record: enter steps walked each day; set thresholds, keep track of days thresholds reached.




Multiple: Sample spreadsheets: Scenarios

Provided by Debra Dalgleish of

Alternative to Scenario: Excel Scenarios are limited to 32 changing cells. This method could use as many variables as columns.

Model Pricing Scenario: Data validation creates dropdown lists, Scenarios store variables, macro automates the scenario display.




Multiple: Word add-ins

Provided by Charles Kenyon of

Letterhead system: This is a system for self-updating letter forms. The idea is to have letterhead components stored in one location and have letter forms reference that location when used so that form letters will have the latest letterhead information. If you are generating your letterhead on the computer or if your letter forms need to accommodate pre-printed letterhead, you should look at this!
Gender Toolbar: Lets you use a custom document property to make gender-specific fields in Word 97 or 2000. Contains built in toolbar, instructions for installation (and un-installation), autotext entries & macros.
The gender-specific words can be set/changed at the click of a button.
The fields can be inserted from the toolbar. Once fields have been inserted from the toolbar, the document with fields can be used by someone else who does not have the add-in by manually changing the document property. Otherwise, they retain their gender just as if they were ordinary text.
Checkbox Add-In: Lets you put active checkboxes in your documents without protecting them as forms! Designed as a tutorial with macros that you can read. Combines use of AutoText, MacroButton Fields, and macros to achieve clickable checkboxes. You can use this as an Add-In and give this capability to all your documents or copy the components to your other templates for use in individual documents. Now with a ?/Y/N Toggle as well. Now includes a separate document on a substitute for the REF field with form checkboxes
Legal Toolbars: Based on a toolbar described in Word 97 for Law Firms & Word 2000 for Law Firms - Includes date field menu that inserts various date fields using AutoText entries.
Web Toolbar Handler: Use as an Add-In or copy code and toolbar to your own global template. Disables Web toolbar and provides menu command for you to view the toolbar when you want to.




Multiple: Word Tutorials

Provided by Charles Kenyon of

ASK Fields Tutorial: Two three-page tutorials using ASK fields with bookmarks that are referenced by REF fields. Also uses FillIn fields to show the differences. One tutorial is a template, the other is a document. Contains macros that update REF fields in headers and footers. Contains macros that update only certain types of fields.

StyleRef Field Tutorial: Two-page tutorial document on the StyleRef field - no macros

Letterhead Textboxes and Styles Tutorial: Two page template showing use of textboxes in the headers/footers to reserve space for preprinted letterhead, styles for a letterhead, and use of the StyleRef field to put information into the second-page header automatically. No macros.

IncludeText Field Tutorial: Ten-page tutorial with three documents demonstrating how to use the IncludeText Field with bookmarks. Shows interaction of styles and how to use a hidden Page field in one document to get continuous page numbering of separate documents - three methods shown for hiding the Page field! Also demonstrates weird interaction of headers and footers in source document.

UserForm (aka Bookmarks and Fields Sampler): Example of a basic userform, bookmarks and REF and IF fields in a Locked Form - (Yes, this tutorial uses both a UserForm and a protected "online" form)

AutoText Sampler: Shows drop-down fields and AutoText menus. Intended as a tutorial.
Nested Tables Demo: Demonstrates potential problems caused by using nested tables in Word 2000 if the document is going to be opened in Word 97. Also shows a pseudo-nested table that is Word 97 compatible.

Calculated Date Field Tutorial: Two-page word document showing complex calculated date field using IF, CREATEDATE, DATE, SET, and other fields.

Buttons Listing: A template with toolbars that have all (or at least many) of the button icons built-in to Office with their face-ids. Over 1000 button images. (This is a tool for customizing Word, not really a tutorial although it does show the VBA that was used to generate the toolbars)

Word Questions and Answers: 160+ page Word document in Zip format with Five-Page TOC Although written about earlier versions of Word, this guide is very useful and may have answers you'll have difficulty finding elsewhere.

Envelope Tutorial: Graphics in return address and more. Uses EnvelopeExtra AutoText entries.




Multiple: Word Templates & Documents

Provided by Charles Kenyon of

WordFAQ: The Microsoft Word New Users' Newsgroup FAQ in Word form

CD Jewel Case: basic template to print CD jewel case inserts (front & back) in Word. Very rudimentary.

Business Cards: Two business card layouts: 3-part with graphic, straight text - both use custom document properties and fields - template
Styles In a Networked Environment: Word document - transcription of newsgroup discussion of this topic. This is a round-table discussion by some very experienced people on different philosophies regarding styles and methods of implementing them.
Digital Signature in Document Samples: Two word documents that have been "digitally signed" using an email certificate and an Authenticode certificate. Purpose is to demonstrate messages that come up and how they change if the document is altered.




Word VBA Code Cleaner

Cleans out the "junk" code in your VBA files. Supported and maintained by Bill Coan, a Microsoft Word MVP.

Word templates get bloated through repeated editing and saving of the VBA code. Bloated templates can often throw invalid page faults for no apparent reason when running VBA code.

Why Word doesn't clean up after itself when saving a template which contains a VBA project is one of the great mysteries of life. Excel suffers from the same problem.

The way to fix this is to export and remove all the modules from the template, and then save the template. Then re-import all the modules again, save the template, recompile and save the template again. If you have been editing the VBA over a long period, and saving many times, you may be astonished at how much smaller the new template is.

The Word Code Cleaner (see below) automates the portion involving export and import of code modules. In addition to cleaning your project this way, the Word Code Cleaner can optionally strip comments, blank lines, and spaces from your code. This allows you to achieve the minimum possible file size for your project.

Bill Coan at




Hidden File Detector

Hidden File Detector is a free utility that allows you to quickly and easily detect and display hidden file-containing fields in a Word document. It reliably detects hidden fields no matter where they reside in the document, including headers and footers and the draw layer.

Uncover malicious fields in your Word documents!

Bill Coan at




Task Pane Controller

Task Pane Controller is a free utility that lets you control the behavior of Word's new task pane feature. Task Pane Controller requires Word 2002 or later.

Harness the power of the Word 2002-2003 Task Pane!

Bill Coan at




XML in Office 2003

XML support is the most dramatic change in Office since Word and Excel first got bundled together for marketing purposes nearly 20 years ago. And now, Bill Coan has put together a comprehensive report to help you learn all about it.

Read this breakthrough report about XML in Office 2003!

Bill Coan at




Sample XML Project

Experience the power of XML in Office 2003 for yourself!

Bill Coan at