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:

    Sub Remove_Hidden_Names()
      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
      Next
    End Sub
  

  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.

---

[HOME] [TABLE OF CONTENTS] [SEARCH]