Search and Find
By Sections
By Levels
The Excel Workbooks
The Excel spreadsheets are available
with the downloadable tutorial
excel-tutorial-sumproduct.xls
To make sure that your new programming skills are not used to re-invent the wheel here is a tutorial on the most useful and powerful function I Excel. If you are learning VBA to automate your reporting process SUMPRODUCT is the first tool. You develop a small VBA procedure to import the data but it is SUMPRODUCT that does the reporting job.
vba-tutorial-editor.xls
A tutorial to discover the Visual Basic Editor and the Macro Recorder.
vba-example-workbook-open.xls
When you open the workbook you want cell A1 of the sheet "Introduction" to be selected.
vba-short-macros.xls
25 short and useful macros.
vba-example-dynamic-chart.xls
In a presentation you are showing a chart that summarizes the sales results for 40 items. You want to show/hide any number of items for a better view of certain families of products.
vba-tutorial-message-boxes.xls
There are many types of message boxes. Here is a tutorial showing them all with step by step development instructions.
vba-example-select-case.xls
When there are too many conditions the "IF" statement can become cumbersome. Here is an example using "Select Case"
vba-example-nested-for-next.xls
You need to go down a certain number of cells in a certain number of columns to perform a certain operation. You need to nest two "FOR/NEXT". Here is how it is done.
vba-example-filtering-workbooks.xls
You receive a set of data for many branches and you want to create an individual workbook for each branch.
vba-example-filtering-worksheets.xls
You receive a set of data for many branches and you want to create an individual worksheet for each branch.
vba-example-conso-databases.xls
Colleagues send you their individual database in an Excel workbook and you want to create a single larger database with all the data. The number of small databases can vary from month to month and your VBA procedure must be able to cope with this fact.
vba-example-conso-tables.xls
Colleagues send you their individual table of results in an Excel workbook and you want to create a single table with all the data. The number of small tables can vary from month to month and your VBA procedure must be able to cope with this fact.
vba-example-data-web.xls
When the data is refreshed each minute from the web by Excel a VBA procedure kicks in to create a database of historical data.
vba-template-numbers-letters.xls
With VBA you can develop new Excel Functions that work like any other Excel function (SUM, IF, AVERAGE). Here is a function translating $49.55 into "Forty nine dollars and fifty five cents"
vba-template-turf.xls
In marketing a TURF analysis is important. If you have a large budget you can buy a program that performs such an analysis for a large set of data but with a smaller budget you are stuck with an application that can only analyze 20 products. Here is a TURF analysis for up to 150 products. And thanks to the variable of the type VARIANT calculation time is very short even with 3,000 surveyed clients.
vba-example-reporting.xls
From the raw data to a complex report at the click of a button.
vba-example-variant-variable.xls
Using a variable of the VARIANT type this application executes 1,000,000 calculations in less than 5 seconds.
vba-form-database.xls
A basic form to allow the user to feed a database.
vba-form-date-span.xls
A basic form requiring a date span for a query.
vba-form-password.xls
Protecting a single VBA procedure: a simple form requiring a password fro the VBA procedure to be executed.
vba-tutorial-combo-boxes.xls
When you choose a certain value in the first combo box you want the choice in the second combo box to be different. We call it cascading combo boxes and here is how it is done.
vba-form-spin-button.xls
The spin buttons allows you to create userforms that are more efficient than with text boxes and "sexier".
Other Workbooks and Files
4 Excel Files used by the "Consolidators":
database2.xls
database1.xls
test-1.xls
test-2.xls
The bar code font: code39.ttf