Back to Excel Newsletter Archives

OzGrid Excel and VBA Newsletter

Search Tips FREE Download!

Newsletter Index

EXCEL TIPS AND TRICKS | EXCEL VBA TIPS AND TRICKS

EXCEL TIPS AND TRICKS

1. SQL TESTER : The SQL Tester is an interactive Query Tool (Excel add-in) for retrieving data and displaying it on the screen in an ad hoc basis from almost all kinds of databases. The output can be placed in worksheets and added to workbooks VBA-projects.

Create Custom Toolbars in Excel & Attach Them to Excel Workbooks

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only $145.00. $59.95 Instant Buy/Download

CUSTOMISING TOOLBARS
 
The toolbars in Excel can be easily manipulated to allow a user to customize their own toolbar.  This is great for situations where there is a shared workbook used by multiple users.  Each user can easily customize their own toolbar with their preferred buttons and built-in menus and even create their own menus and place them on the toolbar.  Users can then show only their own personal toolbar when using the shared workbook.  If you wish, you can customize a toolbar and attach it to a workbook so that the toolbar shows every time the workbook is opened.
 
CREATING A CUSTOMIZED TOOLBAR
 
To create a customized toolbar, go to Tools>Customize and the Customize dialog will pop up.  Click on the NEW button and under TOOLBAR NAME type in a name for your new toolbar, then click OK
 
You will see a blank toolbar with the toolbar name at the left displayed on your screen, you will also see the name of your toolbar in the toolbar list within the Customize dialog.

See Also: Record Excel Macros | Assign Macros . See Also VBA : Create Custom Excel Menu Items | Hide/Restore Excel Toolbars

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only $145.00. $59.95 Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

Got any Excel Questions? Free Excel Help .

See Also: Excel Ranges

Excel: Excluding Headings/Headers from the Current Region/Table

It's often that one needs to work on a range of data, or table, but NOT include any headings/headers. This can be done very easily with the ListHeaderRows Property of an Range Object. Before we can use the ListHeaderRows Property we must first determine the table range. If your table is set up correctly (no blank rows or columns) this can be done very easily via the CurrentRegion Property. That is,

Sub DetermineGoodTable()
Dim rTable As Range

    rTable = Sheet1.Range("A1").CurrentRegion
End Sub

However, if your table DOES include blank rows or columns, we must find the outer top left and bottom right cell. This can be done like below where we know A1 to be our top left cell of the table.
 

Sub DetermineBadTable()
Dim rTable As Range

    With Sheet1
        Set rTable = .Range(.Range("A1"), _
               .Cells(65536, .Range("IV1").End(xlToLeft).Column).End(xlUp))
    End With
    
End Sub

Ok, now we have determined and set a Range variable (rTable) to out table range it's time to redefine the Range variable so no headers/headings are included. Here is how via the use of the ListHeaderRows Property.

Sub GoodTableWithHeaders()
Dim rTable As Range
Dim lHeadersRows As Long

    Set rTable = Sheet1.Range("A1").CurrentRegion
    lHeadersRows = rTable.ListHeaderRows
    
    'Resize the range minus lHeadersRows rows
    If lHeadersRows > 0 Then
        Set rTable = rTable.Resize(rTable.Rows.Count - lHeadersRows)
        'Move new range down to Start at the fisrt data row.
        Set rTable = rTable.Offset(lHeadersRows)
    End If
    
End Sub

What Constitutes a Heading/Header Row

If your table is numeric data and you headings are text (or vice verca), Excel will assume row 1 of the table as a header row. However, if your data AND headings are both numeric, or both text, Excel will consider your table as having NO headers. The way to overcome this is to make your headings different to that of the data. This can be done via bolding, font color/size etc.

Or, should you simply know for a fact that row 1 of the table IS a header row you can use the code below;

Sub GoodTableDataHeaders()
Dim rTable As Range

    Set rTable = Sheet1.Range("A1").CurrentRegion
    
        Set rTable = rTable.Resize(rTable.Rows.Count - 1)
        'Move new range down to Start at the fisrt data row.
        Set rTable = rTable.Offset(lHeadersRows)
 
End Sub

Excel: Add Description & Category for UDF

Sub AddCategoryDescription()
    Application.MacroOptions Macro:="ColorFunction", _
        Description:="Sums or counts cells based on a specified fill color", _
        Category:="Color Functions"
End Sub

Add More Than One Function, Description & Category

It's often the case that more than one custom function needs to be added. In cases like these we can use the Choose function and a simple For Loop .

Sub AddManyCategoryDescription()
Dim strFunction As String
Dim strDescript As String
Dim vCat
Dim lLoop As Long

    With Application
    
        For lLoop = 1 To 3
         
            'Pass function name
            strFunction = Choose(lLoop, "ColorFunction", _
                          "StatsFunction", "DatabaseFunction")
                          
            'Pass function description
            strDescript = Choose(lLoop, _
                         "Sums or counts cells based on a specified fill color", _
                         "A statistical function", "A database function")
                         
            'Pass function category
            vCat = Choose(lLoop, "Color Functions", 4, 6)
            
            .MacroOptions Macro:=strFunction, Description:=strDescript, Category:=vCat
            
        Next lLoop
        
    End With
End Sub

Lot's More: Excel VBA . See Also: Manually Adding UDF to Category and Add Description