Home
Up

 
Using Parameter arrays in VBA. A parameter array can be used to pass an array of arguments to a procedure. You don't have to know the number of elements in the array when you define the procedure.
 
How can I create a hyperlink from a worksheet cell to a specific procedure in a VBA module?
 
Depends on which version of Excel you are using.

In Excel 97:
Insert > Hyperlink: In "Named location in file (optional):" put the name of the procedure.
If the procedure name is MyProc, you would enter "MyProc".

In Excel 2000:
Insert > Hyperlink: Click on "Existing File or Web Page", then in "Type the file or Web-page name" put the name of a procedure preceeded by a hash (#).
If the procedure name is MyProc, you would enter "#MyProc".

In Excel 2002 (XP):
Insert > Hyperlink: Click on "Existing File or Web Page", then in "Address" put the name of a procedure preceeded by a hash (#).
If the procedure name is MyProc, you would enter "#MyProc".
 
 
Using worksheet functions in VBA

Either:

Application.Average(<args>)
WorksheetFunction.Average(<args>)

Application was the Excel 5/95 method and it is still supported for backward compatibility. With WorksheetFunction you get pop-up tips.

There is a slight performance benefit in using the WorksheetFunction object, possibly because the direct Application object has a backward compatibility layer that calls the WorksheetFunction object..

Error handling is also different.

The function "Substitute" prefers "Application.Substitute", as using "WorksheetFunction.Substitute" can cause problems.

 
Remove blank cells

''Leaves the other rows untouched

Sub RemoveBlankCells()
    Range("A3:A10").SpecialCells(xlCellTypeBlanks).Delete (xlShiftUp)
End Sub

 
Undo the last X actions:

Sub zapem()
    For x = 1 To 50
        SendKeys "%{bs}"
    Next x
End Sub


Greg Glynn 5/3/3 microsoft.public.excel.programming
 
Test range contents are all numeric

Accountants (as well as others) should always carry out the following audit when they sum or look at a sum...

=SUMPRODUCT(Range+0)

And compare the result with the result of =SUM(Range).

"Aladin Akyurek" 7/5/3
 microsoft.public.excel.worksheet.functions

 
 
 
 
 
 
 
 
Wiggins Law:
Whatever you do will always takes longer than you think, even after taking into account Wiggins' law
 

Feedback: Please write to us

 

Published, 15 November 2002
Last updated 23 December 2006 23:00