Example files

How did it come about?

What is going on in Excel's function BESSELK? I wouldn't know. But I sure can make it work and that is worth something. For most functions it fortunately
goes like that apart from
making them work, I also understand what they do. That's how FunctiesAlle.xls came about.
To make a long story short: I tried to put 'all' of Excel's functionality into practice. All functions, all chart types, all formatting options. I must admit: extremely
boring and far from reality but at the same time, nice to have at hand as a reference. Here and there, I used simple real life examples.
Regarding Excel and Word, I caught up to version 2003. Access and PowerPoint stay initially limited to 2003.
I no longer maintain the old files.
In the Access samples, for example, you'll find the steps of normalization, all field types with their (in-) possibilities, all possible ways to create
queries, all types of forms
and reports etc.PowerPoint ... just one file, one presentation. With all this program is capable of.

All together, you'll find about 125 Mb of information and examples!

It stands to reason that on the Internet, people have shown what is known and what a program is capable of. In these example files you'll also find things that I don't know or things that cannot be
done marked in red (as far as I'm aware of, that is). By doing so, these matters stand out. Not the way things are usually done, but perhaps a more honest approach. Assistance in these matters
(additions, solutions) would be highly appreciated. He who knows everything casts the first stone.

If you appreciate my activities and want to encourage me working on it more, you can either send me greetings, patches or make a donation:

How to use?

Most Excel workbooks are listed twice: finished and blank. The finished version shows formatting, functions, animations, etc. In the blank copy, you can work your way.
Most example files contain macros. According to me, they are safe :-). Switch the macro security to Medium that you can choose to (not) use them.

The other examples are listed once.

The files

The content on this website may not be used for commercial purposes. The presentations and sample files (in whole or in part) may not be used in trainings, classes / courses, books or presentations without my express permission. I hope these files will add to your knowledge of the programs covered.

The use of the files is at your own risk.

For best performance [Save] and view the files locally. That will work better than [Open] in the browser.

I go for 'Captains log, star date ...'. Until then we are stuck with: leap years, ISO week numbers and negative times ... Also the date of the third (and first) Tuesday
of september and upcoming months can be calculated.

Some nice and silly charts: the Netherlands getting hot, a waterfall, a histogram, the quadratic equation, a chart-vector drawing and other. See the differences between
ten rounding functions and how to brighten up your family album with charts. In-cell
charts: the 2010 Sparklines in 2003. The modulo charts are stunning.

The third waterfall graph and a second box plot, this time created by Hessel de Walle. Most charts came from Hessel: the traffic lights, dynamic graphs by row and by column and more. In the zip-file an overview of the techniques used.

Moronism and reality in one workbook: all kinds of math (Fermat, Kaprekar, Mersenne, Pascal), primes, combinations and permutations vs. iterations, mini RSA, check numbers
and a working Conways Life.NB: You may ignore the message on circular references when opening the file: click
[Cancel].

Over 200 pivot tables, from simple to complex: all eleven functions, lay outs and usage
of an external source. The workbook includes all table formats, calculated fields
and items and a hand made steering.

Exegesis and examples on using formulas and functions. The workbook contains (almost)
all theory in pocket size.NB: There's no need to update links on opening. And click [Cancel]
at the message regarding the circular references.

All (2003: 335 - 2010: 418) Excel functions working! But no explanation of difficult functions such
as MDURATION (calculates the modified - mcAuley - duration for a security), GAMMAINV
(the inverse of the gamma cumulative distribution) or IMLN (the natural logarithm
of a complex number). And a few 'User Defined function' samples.

Some add-ins contain their own functions; sometimes documented, sometimes not. Here
a report of my struggle with the ASAP-Utilities, Name Manager, Polynomials and XlXtrFun.
To mention just one: horizontal and vertical lookup (one or two demensional!) to the nearest value in stead of the smaller value.NB: You'll have to install the add-ins mentiond on your own computer first. ;-)

Another add-in: Fast Excel Name Manager.
With Name Manager you can do more than with Excel's manager. The over 80 functions can do more and are faster than the Excel versions. Some can handle 'regular expressions'!

Fantastic, those fractals. Inspired by a book, I've misused Excel to depict this phenomenon. The working (!) Mira fractals on the last sheet are by Ad Commeren.
Excel frequently 'chokes' on the quantity of data.

Basic rules for the setup of an Excel list but also anything on sorting, filtering, applying functions in a list etc. Also a trick to compare two lists and some
example lists with charts (treated with macros). In the zip three 'list compare' tools by others (with credits!).

Ripe and rotten: sensible and absurd examples. A smart kilometer admini­stration,
naming of sheet tabs and Excel starts 'talking'. A function macros parses an address
in street, number and extention and Driehoek calculates the missing side of a right-angled
triangle.

Main topic of this workbook is programming in VBA. It contains 11 sheets, 3 forms, 18 modules, 4 class modules and hunderds of sample procedures and functions.

Access

File

Mb

Zipped

Comments

Attention please! The database showcase files are still 'under construction'.
On the other hand: I expect these files are worth while publishing.
You should download the auxiliary files (Hulpbestanden) anyhow. Place them in the same folder
in the database and make this folder the working directory for Access.

A database full of twaddle: all data types, field properties, lookup fields, cross-field
validation and indexes. Twenty one tables with simple (and funny) samples.
tblFieldValidations is about the mystery 'validation rule Is Not Null, Required and Allow Zero Length'.
Three forms show all data types, field formatting and that 'mystery'.

In BitsAndPieces2, I try to visualize 'normalisation'.
You'll find about the same data four times. At first, all data is in one flat table: normal form zero.
In the fourth version, 'everything' is split out in main, junction and domain tables.
NB: I've used the Lookup Wizard to show foreign key values as readable data.

All kinds of queries in about 140 samples. Sorting, filtering, calculation (even ranking numbers);
one or more source tables; inner, outer and self joins; action queries and SQL. All topics are covered one by one.

All sorts of forms. From an AutoForm, via all options in the Forms Wizard to a main-sub-sub-subform. And of course the Switchboard.
Programming controls is matter to discuss separately. For this see KleineDemos.mdb.
But all events are shown in frm10##.

Using the User-level Security Wizard you really lock up a database. Rights can be set by database object and by user or group; from nothing, by read only up to managing.
See AccessBeveiliging.docx for the users and their passwords.
The zip file contains the necessary mdw-file and amongst others a batch file and
a shortcut to open the database with the correct workgroup file.

By replication, two or more users can work in separate versions of a database. By synchronization, the replica set is updated.
The zip file contains 19 files: global, local, anonymus and partial replicas plus a little documentation.

KleineDemos is one big, pleasant 'mess'. Keep the <Shift> down while opening to go backstage.
In fact there are 3.5 db's in this package: persons with hobbies, a counter registration, an administration for guided tours and some small fry with fantastic forms. Too much to mention here.
Also the 'operation' may be seen: a switchboard, four layers deep with ten (10)
buttons and an added command.

My first Access add-in!
In my permanent struggle to document databases, I had the need of VBA-routines that
a) would capture the database (the names and properties of tables, queries and fields) in tables and
b) would be available in any database.
The result goes herewith. The file must be located in "Documents and Settings\UserName\Application Data\Microsoft\AddIns".
The commands appear in the menu Tools below Add-Ins.

Could anything be easier in a word processing program like Word than character formatting (Font)?
It appears not: 21 pages bare and only examples of the possibilities. (Was this my first Word 2010 file ever?)

The lesser-known phenomenon of 'section' inside out: margins, headers and footers, page border, ... Everything in the flesh.
I had to make five auxiliary files because some settings conflict within one file.

This example file is hardly suitable for publication. Much remains to be done, it is truly 2003 and it is a hotchpotch of English and Dutch. Yet, because so many nice things can be done with tables: Tabellen.doc.