Examples

Excel Examples

Most of these files are demonstrations of principles, or partial solutions to popular problems. Download the files to your PC so you can study them. All files are compressed in the popular zip format. All of these are placed in the public domain. If any of them help you to solve a problem, please send me some outline details of the project you were working on, which example you used, and how it helped you.
Sections	General Menu Routines Administration Utilities Excel for Lotus 123 Users Excel with Access Databases Excel with Word Accountants Project Planning Worksheet Strictly VBA Games Other!

Click on a link and it will take you to the page giving more details about the methods employed and a link so you can download the example file.

General
ISO Dates	How to calculate an ISO week number. How to calculate a week commencing date from a week number and year.

Menu Routines
Active Workbook	Excel 97 and above. It contains VBA code that will activate a menu only for the active workbook.
Active Subs Workbook	Excel 97 and above. It contains VBA code that will activate a menu only when the workbook it is in is active. This is an extension to the above example which includes sub-menus.
Menu Bar Maker	To demonstrate how to create a menu bar and use it in place of the "Worksheet Menu Bar" The current state of the "Worksheet Menu Bar" is retained, so you don't lose any customisation to the menus.
Menu Bar Maker with Subs	This code extends that found in "MenuBarMaker". It uses a procedure ("AddMenuItem") to stop duplication of code. This can make the resulting code more succinct, readable and easier to maintain. This has limitations which are demonstrated where special cases, e.g., to show that a button is checked, require additional code.
Pop Up Menu	How to create and implement a popup menu

Administration Utilities
osesame	Easily locate and open regularly used files. An Excel workbook to help you organise yourself and your projects. Opens multiple files. List all Excel files in a directory. Reports file sizes. Capture information about an open file. Most effective when added to your XLSTART or ALTSTART directory.
Log File	Create a log file for your projects as you work. Add this into your code while debugging, or use it in projects to audit user activity.
filelist	List file names, from a directory you select, in the current worksheet. Uses the Excel "GetOpenFilename" method.
doublesave	Save a workbook and create a backup version in the same directory. Click on the button and the workbook will be saved, and backed up, into the directory where you downloaded it. There are two backups created with this version: one as filename_yyyymmdd_hhmmss.xls and filename_yyyymmdd.xls. The first gives you an incremental backup everytime you save your work. This is especially useful when developing workbooks The second ensures you have a daily backup based on the final save you make each day.
Custom Document Properties	Demonstrate the use of Custom Document Properties through VBA's CustomDocumentProperties property.

Excel for Lotus 123 Users
XINDEX	In Excel there is no direct equivalent for Lotus 123's XINDEX function. This link will show you how to recreate it using Excel
LotusGT	Demonstrates a VBA substitute for Lotus's GRANDTOTAL function.
LotusInp	Mimic Lotus's {?} macro command using Excel's "DataEntryMode". Also demonstrates the use of "OnKey" events.
ChooseItem_97	VBA: Excel 97 and above. Mimics the Lotus "Choose-Item" macro command.

Excel with Access Databases
Access Log File	Access Log File - Record Excel data in an Access database using DAO.
Using SQL in Excel	Using Excel's VBA, read and write to a database using SQL. There are two demonstrations available for DAO and ADO.
Using SQL.REQUEST	This workbook demonstrates how to get data direct from an MS Access table, or from an open or closed MS Excel workbook using the workbook function SQL.REQUEST.

Excel with Word
is_word	VBA: Use this routine to check whether, Word, Access, Notepad, or a host of other applications are currently running on your PC.
fonts	Use Excel (and Word) to list all available fonts on your PC. Click here for sample output - notice Euro symbol is displayed.

Accountants
Cash_Analysis	The purpose of this workbook is to demonstrate how to analyse note and coin breakdown for making up a cash payroll.
Bread-Roll	The "Bread-Roll" consolidation method - great for accountants. See how simple it is to consolidate any combination of your organisation's accounts.
Currency Conversion	How to set up a currency conversion
Loan_Calculator	Here are two worksheets that can help you calculate the likely repayments you will have to make on mortgages and personal loans (including HP and conditional sale agreements).
Ageing	Ageing analysis of data without using VBA
StckCtrl	An example of using Excel's "SUMIF" function in stock control.
Sales	Update graph data without changing the source ranges. Useful if you produce graphs on a regular basis. NO VBA USED.
Invoice	Using VLOOKUP on an invoice, including a demonstration of how to exclude blank lines from an address. NO VBA USED.
Rounding	Ever wanted to know how to round a price to the nearest e.g., 20 pence or 20 cents? Here's how
SubTotals	Subtotal your analysis on random lines - No VBA.
num2wrds	VBA: A function to convert numbers to words. File includes two cheque writing routines. One based on the numbers to words macros, and another based on formula lookups.

Project Planning
x97gantt	Excel 97 and above. A demonstration of how a Gantt chart can be constructed in Excel. Useful for controlling small projects.
timesheet	A basic time sheet, suitable for many, and a way to analyse the time and cost of projects on which you are working. NO VBA USED

Worksheet (No VBA)
indirect	How to use Excel's "INDIRECT" function.
Validate and Indirect	Shows how using "Data > Validation" and "INDIRECT()" you can populate a cell from two dependant lists
Dynamic Formatting	Two methods demonstrating how to include the dynamic formatting of cell contents in your spreadsheets.
FunWithSUM	Is the SUM function really limited to 30 arguments. This workbook shows you that it isn't!
SmallAndLarge	Demonstrates using the SMALL and LARGE functions.
Using WORKDAY formula	This file demonstrates the use of the "WORKDAY" function from the Analysis ToolPak. The public holiday list represents those used in England. The Analysis ToolPak must be installed and enabled for this demonstration to work.
rand_nos	It can be done. Pick a series of unique random numbers WITHOUT using VBA. Another example of using Excel's iteration feature.
Duplicate Entries	Check for duplicate entries using Conditional Formatting
ColumnLetters	Get column numbers from an letter value, or column letters from a numeric value

Worksheet with VBA
range	Demonstration of adding and removing rows from a range
dubclick	Demonstration of setting up and using VBA's "OnDoubleClick"
dubclick97	Demonstration of setting up and using VBA's "BeforeDoubleClick" worksheet event. Based on DubClick.Xls. It's worth comparing this workbook to the original as example of the way Excel's event constructions have been changed.
mzoomer	Interactively see the effect of changes you make to Excel's zoom setting
mzoomer_97	Alternative version for Excel 97, 2000 and XP. Interactively see the effect of changes you make to Excel's zoom setting
apcaller	Short demo that reports which of three buttons was pressed
hidd_fns	Two VBA functions demonstrating the use of AutoFilter to sum and count the visible rows in a range.
fit_scrn	Fit a range into the available screen area.
adjwidth	Fed up with cells showing "###########", then use VBA to adjust the width of a single cell where the number is too large to fit.
More Than Three Column Sort	With Excel you can only sort a maximum of three columns using the Data > Sort option. This workbook demonstrates a method to sort four or more columns.
prime	Is a number a prime number? This workbook has the functions that let you know.
Overwrite And RetainFormula	Demonstrates how user input is captured and used to amend a formula with the "SheetSelectionChange" event
vlookup2	Look up on two fields with this alternative to VLOOKUP

Strictly VBA
easyxcel	VBA: Some basic macros for the beginner
ref_val	VBA: A demonstration of how ByRef and ByVal work
dialogvw	VBA: Cycle through Excel's dialog boxes.
password	VBA: For Excel 5 and 95. Use a masked password routine to protect your work.
File Ops And Collection	This is an example of reading from a file, storing the file data in a collection, copying the collection to an array. To use this demonstration, you will need to create a text file called "c:\temp\testfile.txt"; details are in the download file.
ListBoxUse	Shows how to add data to a ListBox select data paste result into some cells cope with "Cancel" key This workbook adds a list of open workbooks to the ListBox.

Games
Lotto	VBA: Pick your own lottery numbers. These VBA routines show how to pick a series of unique random numbers and then sort them into order.
Cricket	This workbook demonstrates the use of Excel's ITERATION feature whilst doing something entertaining. NO VBA USED.
GolfScores	Based on a newsgroup request. Winning player gets one point for the hole. Tied holes receive no score.

Other!
shakey	Shakespearian insult generator.
tlj	Business English: a three letter jargon generator, especially for report writers.
eurosong	Eurovision Song contest score sheet. This file demonstrates the use of the LARGE and COUNTIF functions.
goalseek	Excel 5 and 95 have problems with their goalseeking in VBA. This is a way around it using an Excel 4 macro called by VBA.

expd_box	VBA: Place an expanding dialog box on a sheet.
age_diff	Calculate the difference between two dates. Demonstrates the use of date formulas.