The Andrew N. Wiggins Consultancy

Contact me at

Experimental Site


This site designed by Byg Software Ltd


The ANW.BIZ Home Page
Print Directory
VBS Script

Internet Explorer - How do I get IE to remember passwords?
Tools > Internet Options : Content > AutoComplete : check "Prompt me to save passwords"
How to fill a cell's formula/value down without changing the format

1. Point to the Fill Handle, and press the RIGHT mouse button.
2. Drag down, then release the button.
3. From the shortcut menu, choose Fill Without Formatting

Where have my favorites gone?
Tools > Internet Options : Advanced, then uncheck: "Enable Personalized Favorites Menu"
.. and not for IE but for the "Start" menu
Start > Settings > Taskbar & Start Menu, then uncheck the box "Use Personalised Menus"
I don't know exactly what went wrong but ... ... when I restarted Excel, all the toolbars were missing.
I looked at View > Toolbars, but they simply aren't there!
Used this piece of code to restore them:
'' Purpose : Cycle through all toolbars and re-enable them 
'' Written : 19-Jul-2002 by Andy Wiggins, Byg Software Limited 
Sub RestoreMissingToolbars() 
Dim x
    For Each x In Application.CommandBars 
        Application.CommandBars(x.Name).Enabled = True 
    Next End 



Hi everybody. In the task pane for New Workbook, is there a way I can
change the color of the commands in there. I don't like that blue

Debra Dalgleish:
The colour is controlled by the settings in Internet Explorer.

1. In IE 6.0, choose Tools>Internet Options
2. On the General tab, click Colors
3. Click the Unvisited button
4. Select a colour, click OK, OK, OK
5. Start Excel, choose File>New, and the new colour should be displayed


Object Orientation

Abstraction Identifying real-world entities
Encapsulation Objects communicate via their public interface (hiding their internals)
  1. Classes can implement different behaviour for the same method or property. (Triangle & circle = different. Both have "Drawshape" method, but it produces different results for each.
  2. Class can have several methods with the same name but with different arguments.
Inheritance A new class can gain the functionality of an existing class.
Dates in VBA use the American format. Avoid date literals. Instead use the VBA DateSerial function or the Excel Date function because they are not ambiguous.

The problem is caused by getting Excel to implicitly convert the date string to a number (to put in the cell), which it does with a US-bias. Performing the conversion *explicitly*, using CDate() tells Excel to use your regional settings for the conversion.

XL uses templates with the extension .xlt.  If you place a workbook named "Book.xlt" in the XLStart folder, that workbook will be used as the template for new workbooks.

However, XL files, once created, are not attached to the template file the way Word docs are. A change in the template has no effect on .xls files.
To add negative numbers use: =SUMIF(A1:A10,"<0")
How do I find Excel's add-in directory? Application.UserLibraryPath
Lost your mouse? Press shift F10 for shortcut menus
To stop a user from saving, put this code in the "ThisWorkbook" module:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Cancel = True
End Sub

Get data from a closed workbook:
Sub GetDataFromClosedFile()
Dim lStr_Path As String
Dim lStr_FileName As String
Dim lStr_SheetName As String
Dim lStr_Combined As String
    lStr_Path = "k:\byg\excel\dev"
    lStr_FileName = "chartthings2.xls"  '<==this could change in a loop
    lStr_SheetName = "Sheet2"
    lStr_Combined = "sum('" & lStr_Path & "\[" & _
                 lStr_FileName & "]" & lStr_SheetName & "'!r2c3:r3c5)"
    Range("d10") = ExecuteExcel4Macro(lStr_Combined)
End Sub
From "The Simpsons"
Here are three methods that can highlight errors in your code before you distribute it to users:
  1. Debug > Compile
    This checks your code and performs a pseudo compile
  2. Tools > Options > Editor Format
    Change the code colour for "Keyword text" to, say, blue. When you type in the code it would have stayed the colour of "Identifier text"
  3. When typing in keywords (or what you think is a keyword) always use lower case.
    If it really is a keyword, the VBA editor will correct it to it's own style, e.g:
    Type in "interger" and you will see "interger"
    Type in "integer" and you will see "Integer"