Excel VBA : Object Tips
by Dermot Balson - Independent Developer
- ActiveWorkbook Fails in an XLA:
If you make your workbook into an XLA and
use it to drive another workbook, any references to ActiveWorkbook will
relate to the other workbook, not the XLA. Use ThisWorkbook to refer to the XLA.
- Get your Custom Function Listed in the Function Wizard:
From a module sheet, select View..Object Browser and add a description in the Options Dialog.
- Print out a Specific Bunch of Sheets in One Go:
Worksheets(PArray).PrintOut will print out the sheets whose names are contained in the array PArray,
eg. PArray(1) might = "Sheet1".
- Making Macro Sheets xlVeryHidden:
A macro can't make its own module sheet
xlVeryHidden. You have to do this from another workbook/sheet.
- Page Formatting:
Generally, use Excel 4 functions for speed, eg. PAGE.SETUP.
VBA can be v_e_r_y slow in this area. To read up on Excel 4 functions, open
macrofun.hlp. To run Excel 4 macros, use ExecuteExcel4Macro ("MacroName").
To get the number of pages that will print use :
NumPages = ExecuteExcel4Macro("Get.Document(50)")
- Grabbing the Last Cell in a Range:
SpecialCells(xlLastCell) should do this,
but is a little misleading. It doesn't operate on a range, but always the
entire worksheet. Try something like this :
Cells(16384, ActiveCell.Column).End(xlUp).Select (ie. search from bottom up)
- Removing Unwanted Links from Workbooks:
Search for and delete ! references,
and/or delete hidden names, as in:
Dim a_name As Variant 'dimension variable used in loop
For Each a_name In ActiveWorkbook.Names
If a_name.Visible = False Then a_name.Delete
Also, check all names (Insert Name Define) to see if they reference external workbooks.
Finally, try Edit Links and then click the Change Link Source button.
- Where is the User on the Spreadsheet?:
Hard to say. Excel has very few events, and most of them involve clicking,
pressing enter, or activating an object (eg. a sheet). Clicking on a cell
cannot be picked up; nor pasting; nor tabbing between controls/boxes on a dialog sheet.
Written by: Dermot Balson
OF CONTENTS] [SEARCH]