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