Home
Up

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.

Top

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

Top

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
 

   

Top

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.
   

Top

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.
 
   

Top

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.
   

Top

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.
   

Top

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.
 
   

Top

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
 
   

Top

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
 

Top

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

   

Top

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.
 

   

Top

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.
 
   

Top

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.
 
   

Write to us