I use EXCEL extensively at work and have become facinated with the power and versatility of spreadsheets, particularly EXCEL.
The following collection reflects some of the work I have done.
I owe much of my knowledge to the texts written by John Walkenback and Eric Wells, John Walkenbach's Home Page and the EXCEL-L email newsgroup. Whenever I have used examples from these sources I've attempted to give credits to these and other sources.
My EXCEL files
These files are mostly EXCEL 5 workbooks compressed with pkzip and saved as self-extracting archives. Simply download the files and execute them to extract the EXCEL file.
DataValidation - ReducingList (EXCEL 97)
Data validation is a groovy little thing. Amongst other things it allows you to control the data items that are available for entry into cells. This gives you a little drop-down list beside the cells to choose from. Sometimes you want to offer a list of values to a set of cells but you don't want the same value to be selected twice. This example sheet shows you just how this can be achieved with formulae only - no VBA.
File Listing Utility (EXCEL 97 only)
Finding out how much of your diskspace is being used by a particular segment of your directory structure has always been difficult. Explorer won't tell you anything except the total for the current directory - it ignores sub-directories. This utility will list all the files that appear within a selected directory and includes all files in all sub-directories. It even lists the file dtaes and times as well as the file size. A splash screen keeps you informed of its progress during long downloads.
ColourfulDialogs (colourdb.exe)
EXCEL has always insisted that dialogs are boring. Dull grey backgrounds and the only bit of colour to be seen anywhere was the blue heading bar. Now here's an example of how to put colour into your dialogs by adding a background image. Click on the dialogs and your backgrounds will change. I've used a few BMP files to give you the idea. Some of these you'll recognise. This works very well in EXCEL 5 under Windows3.11 and 95. It is a bit jumpy in EXCEL97.
Here is a timesheet I wrote to deal with FLEXI-TIME. It copes with flexible working hours, part-time workers, negative as well as positive time credits and partitioning time worked by task.
Serial Communications to EXCEL via Windows 3.x (XLcomm.exe)
Here is a sheet I developed because I wanted to connect a laser dendrometer (used to measure tree heights among other things) to a portable computer running EXCEL. After much frustration I was given a copy of a modem routine written in VBA. From this I created the XLcomm.xls.
This sheet monitors the selected port for input and places each line of input (defined by your choice of CR, LF or CFLF) in a new cell.
This is designed as an example and could be easily modified to parse input into multiple cells or screen out unwanted characters.
Unfortunately this works only in Windows 3.x because it uses the 16 bit API. The 32 bit API calls used in Windows 95 / EXCEL 97 are quite different and I haven't got around to converting them. Maybe one day!!!!
Serial Communications to EXCEL WINDOWS95 (XLcomm97.exe)
The day has arrived thanks to Marshallsoft! This routine does exactly the same things as XLCOMM but works in Windows 95 (and probably 97,98,2000 and NT but I haven't checked them). To run this you need to install the WSC32.DLL file. This is available from MARSHALLSOFT software as part of the WSC4VB serial communications library.
This sheet monitors the selected port for input and places each line of input (defined by your choice of CR, LF or CFLF) in a new cell.
This is designed as an example and could be easily modified to parse input into multiple cells or screen out unwanted characters.
Control a Chart using a Listbox (dddemo.exe)
This sheet shows how to control the contents of a chart using a listbox and a couple of named ranges. The ranges are named using indirect() function so if you add or delete data from the data sheet it automatically updates the listbox.
There are only two worksheets and no VBA!
Fill Table Using Dialog Box (filltabl.exe)
This sheet shows how to enter data directly from a Dialog Sheet to a worksheet without using any VBA. It uses two listboxes to set the row and column and an OFFSET function to define the Target Cell.
This works very quickly and directly.
It is best employed when you want to populate the table with the index value from the list rather than the text value in the listbox.
This sheet demonstrates the power of using form items like list-boxes on your spreadsheet. This single spreadsheet replaces a 10 page FORTRAN program that took about 10 times longer to run.
The spreadsheet is inifitely more user friendly.
Progress Indicator (PROGIND2.EXE)
This modeless XL97 UserForm was originally created by John Walkenbach.
I have modified it a little to show that you can change all aspects of the text displayed on the userform while it is being displayed. This works ONLY with XL97.
John deserves all of the credit for this trick!!
List Every Item Only Once in a List Box (uniqlist.exe)
Here is a sheet that takes a list of data entries and extracts a list of the values that does not include any duplicates. This is achieved using only Array Formulae and some named ranges. No VBA is used in this sheet.
List Every Item Only Once in a SORTED List Box (uniqlist-sorted.exe)
And here is the version that creates a sorted list! This is achieved using some Array Formulae and some named ranges.
Add Label Values to Chart Points (addlabel.exe)
Here is a macro that allows you to label the points on your chart with text or numbers held in cells. You select the chart series you want to label and press the button. The routine asks you for an offset (the number of cells away from the y-values). Both negative and positive values are acceptable. Entering 0 will label the points with their y-value.
OnTime() Event Manager (ontime.exe)
This module sheet demonstrates the control of all your OnTime events. It does this by keeping a list of all scheduled events and then running itself whenever an event is due. It then runs the event and deletes it from it's data set. It can also be run in a few other modes that delete scheduled events, show what events are due or delete all events. It could easily be modified to do things like rescheduling all events by x minutes. This is currently in developement mode and may not work in all situations. As always comments and modifications are welcome.
This utility allows you to view and delete all of the names in your active workbook. It lists the global names and the local names separately. You can delete multiple names simultaneously as the dialog will allow multiple selection. This was written for EXCEL 5 under Win3.11. It has been tested in Windows95 with EXCEL 5. Other configurations have not been tested. As always comments and and suggestions for modifications are welcome.
To run:- Run findname.exe (self extracting archive) , open the workbook you want to check, load findname.xla, run macro ListNames.
Recent File List (filmanxl.exe)
EXCEL 5 has a recent file list of 4 files, EXCEL97 allows 9.
This utility allows you to maintain a LOOOOONG! list of previously used files. The utility keeps the list in recently used order and automatically deletes files from the list if they no longer exist. There is a button to activate the normal "open file" dialog if the file you want is not in the list. You can also trim the list down to any length you like any time you like.
Assign the macro "c:\whereveryouputthisfile\filmanxl.xls!FileManager" to the openfile button on your toolbar (or another button if you like).
This was written for EXCEL 5 under Win3.11. It has been tested in Windows95 with EXCEL 5. Other configurations have not been tested. As always comments and suggestions for modifications are welcome.
If you have any comments, criticisms, job offers etc then email me at fcfhsp@netspace.net.au.