The Andrew N. Wiggins Consultancy

Contact me at webmaster@anw.biz

Experimental Site

This site designed by Byg Software Ltd

Newsgroup Answers

These are the standard answers I give to questions that keep arising in Excel newsgroups. Also my standard reply to unsolicited emails I receive.

Sorry, I'm not in a position to answer specific questions.

Please address your problem to an appropriate newsgroup. Here are a few suggestions:

microsoft.public.excel.programming
microsoft.public.excel.worksheet.functions
microsoft.public.excel.misc
comp.apps.spreadsheets

Please do not post to more than one and do not attach any files.

Regards
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy

It's in the "Worksheet with VBA" section on page: http://www.bygsoftware.com/examples/examples.htm

This workbook demonstrates a method to sort four or more columns.

Access Log File - Record Excel data in an Access database using DAO

It's in the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

The code is open and commented.

The code is open and commented.

It's in the "Accountants" section on page: http://www.bygsoftware.com/examples/examples.htm

Ageing analysis of data without using VBA

It's in the "Worksheet with VBA" section on page: http://www.bygsoftware.com/examples/examples.htm

Short demo that reports which of three buttons was pressed

It's in the "Accountants" section on page: http://www.bygsoftware.com/examples/examples.htm

The purpose of this workbook is to demonstrate how to analyse note and coin breakdown for making up a cash payroll. (No VBA used)

It's in the "Accountants" section on page: http://www.bygsoftware.com/examples/examples.htm

The "Bread-Roll" consolidation method - great for accountants. See how simple it is to consolidate any combination of your organisation's accounts. (No VBA used)

The Excel download file is here:
http://www.bygsoftware.com/examples/zipfiles/cricket.zip

It's in the "Games" section on page: http://www.bygsoftware.com/examples/examples.htm

This workbook demonstrates the use of Excel's ITERATION feature whilst doing something entertaining. (No VBA used)

It's in the "Administration Utilities" section on page: http://www.bygsoftware.com/examples/examples.htm

This saves a workbook and creates 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.zip and filename_yyyymmdd.zip. 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.

The code is open and commented.

It's in the "Worksheet with VBA" section on page: http://www.bygsoftware.com/examples/examples.htm

Demonstration of setting up and using VBA's "BeforeDoubleClick" worksheet event.

The code is open and commented.

It's in the "Worksheet" section on page: http://www.bygsoftware.com/examples/examples.htm

Is the SUM function really limited to 30 arguments. This workbook shows you that it isn't!

Essentially, you just put an extra set of brackets in the formula =SUM((1,2, ... ,n))

It's in the "Project Planning" section on page: http://www.bygsoftware.com/examples/examples.htm

A demonstration of how a Gantt chart can be constructed in Excel. Useful for controlling small projects. (No VBA)

It shows how to create, read and write INI files

This page is based on ISO 8601.

It includes the Excel formula and an online week-number calculator using Microsoft's spreadsheet component.

It's in the "Accountants" section on page: http://www.bygsoftware.com/examples/examples.htm

This workbook includes 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).

Or from the "Menu Routines" section on page:
http://www.bygsoftware.com/examples/examples.htm

It contains VBA code that will activate a menu only when the workbook it is in is active.

The code is open and commented.

Or from the "Menu Routines" section on page: http://www.bygsoftware.com/examples/examples.htm

Demonstrates how to create and implement a popup menu.
The workbook covers two areas:
* Creating a popup menu
* Using the Right_Click event

The code is open and commented.

It's in the "Worksheet with VBA" section on page: http://www.bygsoftware.com/examples/examples.htm

Interactively see the effect of changes you make to Excel's zoom setting. Including, fitting the width of the current selection to the screen.

The code is open and commented.

You can find it on page:
http://www.bygsoftware.com/examples/excel_addin.htm

This workbook shows how to create an interactive add-in controlled by a toolbar. The toolbar is created when the add-in is loaded, and deleted when the add-in is closed. .

The code is open and commented.

It contains the "SPELLNUMBER" function, which can be used in your spreadsheets,  two methods to convert numbers to words and two cheque writing routines.

The code is open and commented.

It demonstrates how user input is captured and used to amend a formula with the "SheetSelectionChange" event.

The code is open and commented.

One click on "Save and BackUp" and your work is saved and sequentially backed up. This is ideal for anyone developing in Excel, especially where a development history is required.

It's in the "Accountants" section on page: http://www.bygsoftware.com/examples/examples.htm

Update graph data without changing the source ranges. Useful if you produce graphs on a regular basis. (No VBA used)

They are in the "Other!" section on page:
http://www.bygsoftware.com/examples/examples.htm

A Shakespearian insult generator and a "three letter jargon" (TLJ) generator, especially for report writers.

This might be a help for getting data to and from Excel and Access: It includes examples of using variables in SQL queries.
http://www.bygsoftware.com/examples/sql.html

Or you can get there from the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

It demonstrates how to use SQL in Excel's VBA to:

* create a database,
* create a table
* insert records
* select records,
* update records,
* delete records,
* delete a table,
* delete a database.

DAO and ADO files available.

You can also download the demonstration file called "excelsql.zip".

The code is open and commented.

It's in the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

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.

Recently updated to show the use of SQL.REQUEST in the same workbook.

The code is open and commented.

http://www.bygsoftware.com/Excel/SQL/update_using_sql.htm

Using an UPDATE query in SQL.REQUEST to change values in closed workbooks.
How to COUNT data in an Excel table and in an MsAccess database using
SQL.REQUEST.

It's in the "Project Planning" section on page: http://www.bygsoftware.com/examples/examples.htm

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)

It's in the "Worksheet (No VBA)" section on page: http://www.bygsoftware.com/examples/examples.htm

Shows how using "Data > Validation" and "INDIRECT()" you can populate a cell from two dependant lists

It's in the "Worksheet" section on page: http://www.bygsoftware.com/examples/examples.htm

Look up on two fields with this alternative to VLOOKUP

It's in the "Worksheet" section on page: http://www.bygsoftware.com/examples/examples.htm

This file demonstrates the use of the ""WORKDAY"" function from the Analysis ToolPak, including public holidays. (The Analysis ToolPak must be installed and enabled for this demonstration to work). "

It's in the "Excel for Lotus 123 Users" section on page: http://www.bygsoftware.com/examples/examples.htm

In Excel there is no direct equivalent for Lotus 123's XINDEX function. This workbook shows two Excel formula constructions that achieve the same result.

The first example uses two additional inputs. It uses the Excel functions: INDEX and MATCH The second example uses the same inputs as the Lotus XINDEX function. It uses the Excel functions: INDEX, MATCH and OFFSET.

There are also two additional pieces of VBA showing how to use this in code and capture an error condition.

The code is open and commented.

There is also an alternative construction using SUMPRODUCT at:
http://www.bygsoftware.com/Excel/functions/sumproduct.htm

Regards
Andy Wiggins FCCA
www.BygSoftware.com
Home of "Byg Tools for VBA" and "The Excel Auditor"

Regards
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy