The Andrew N. Wiggins Consultancy

Contact me at webmaster@anw.biz

Experimental Site

 

This site designed by Byg Software Ltd

 

The ANW.BIZ Home Page

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.

 

Hi,

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

ooo

   
> 3 COL SORT This file might be a help: http://www.bygsoftware.com/examples/zipfiles/MoreThanThreeColumnSort.zip

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 This file might be a help:
http://www.bygsoftware.com/examples/zipfiles/log_file.zip

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.

   
ADD-INs

It contains a workbook download that 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.

   
AGEING ANALYSIS This file might be a help:
http://www.bygsoftware.com/examples/zipfiles/ageing.zip

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

Ageing analysis of data without using VBA

   
CALLER This file might be a help:
http://www.bygsoftware.com/examples/zipfiles/apcaller.zip

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

   
CASH ANALYSIS This file might be a help:
http://www.bygsoftware.com/examples/zipfiles/CashAnalysis.zip

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)

   
CREDIT CARDS You can store numbers of 16 or more digits, such as credit card numbers, but only after formatting the cell(s) as text. You can then perform calculations on those numbers. An example is shown here: http://www.bygsoftware.com/Excel/InterFace/credit_cards.htm
   
CONSOL This file might be a help:
http://www.bygsoftware.com/examples/zipfiles/consol.zip

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)

   
CRICKET This page might help:
http://byg2/BygSoftware/Excel/InterFace/cricket.htm

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)

   
CUSTOM DOCPROP This file might be a help:
http://www.bygsoftware.com/examples/zipfiles/CustomDocumentProperties.zip

The workbook has a family of VBA routines to help you create, amend, and delete Custom Document Properties

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

The code is open and commented.
 
   
   
DOUBLESAVE This file might be a help: http://www.bygsoftware.com/examples/zipfiles/doublesave.zip

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.

   
DUBCLICK97 This file might be a help: http://www.bygsoftware.com/examples/zipfiles/dubclick97.zip

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.

   
FUN WITH SUM This file might be a help: http://www.bygsoftware.com/examples/zipfiles/FunWithSum.zip

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))

   
GANTT This file might be a help:
http://www.bygsoftware.com/examples/zipfiles/x97gantt.zip

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)

   
INI Files This page might be a help: 
http://www.bygsoftware.com/excel/vba/ini_files.htm

It shows how to create, read and write INI files

   
ISO DATES This page might be a help: 
http://www.bygsoftware.com/examples/iso_dates.htm

This page is based on ISO 8601.

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

   
   
LOAN_CALCULATOR This file might be a help: http://www.bygsoftware.com/examples/zipfiles/Loan_Calculator.zip

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).

   
LOG_FILE This might be a help:
http://www.bygsoftware.com/Excel/VBA/LogFile.htm

It shows how to 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.
   
MENUACTIVEWBDEMO This might be a help:
http://www.bygsoftware.com/Excel/VBA/MenuActiveWbDemo.htm

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.

   
MENU _ POP UP This might be a help:
http://www.bygsoftware.com/Excel/VBA/popup_menu_demo.htm

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.

   
MZOOMER This file might be a help:
http://www.bygsoftware.com/examples/zipfiles/mzoomer.zip

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.

   
MZOOMER ADD-IN This file might be a help:
http://www.bygsoftware.com/examples/zipfiles/CommandBars.zip

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.

   
NUM2WRDS This file might be a help:
http://www.bygsoftware.com/examples/zipfiles/num2wrds.zip

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

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.

   
OVERWRITE AND RETAIN FORMULA This file might be a help:
http://www.bygsoftware.com/examples/zipfiles/OverwriteAndRetainFormula.zip

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

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

The code is open and commented.

   
SAVE AND BACKUP This might help:
http://www.bygsoftware.com/saveandbackup.htm

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.

   
SALES This file might be a help: http://www.bygsoftware.com/examples/zipfiles/sales.zip

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)

   
SHAKEY These files might (not) be a help:
http://www.bygsoftware.com/examples/zipfiles/Shakey.zip
http://www.bygsoftware.com/examples/zipfiles/tlj.zip

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.

   
SQL

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.

   
SQLREQUEST This file might be a help:
http://www.bygsoftware.com/examples/zipfiles/UsingSqlRequest.zip

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.

   
SQL.REQUEST - UPDATE This page might be a help:

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.

   
TIMSHEET This file might be a help:
http://www.bygsoftware.com/examples/zipfiles/timsheet.zip

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)

   
VALIDATE and INDIRECT This file might be a help: http://www.bygsoftware.com/examples/zipfiles/validation_and_indirect.zip

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

   
VLOOKUP2 This file might be a help:
http://www.bygsoftware.com/examples/zipfiles/vlookup2.zip

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

   
WORKDAYFORMULA This file might be a help: http://www.bygsoftware.com/examples/zipfiles/UsingWORKDAYformula.zip

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). "

   
XINDEXVBA This file might be a help: http://www.bygsoftware.com/examples/zipfiles/xindexvba.zip

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

   

ooo

Regards 1 The code is open and commented.

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

   
Regards 2 The code is open and commented.

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

 
Published, 03 March 2003
Last updated 19 May 2008 16:30