416-621-9348 cgreaves@chrisgreaves.com
Home Services Products

How To Build an Excel Library (Home); You Gotta Start Somewhere ; Creating A Function Library ; Compiling A Workbook To A Library ; Creating A Macro Library ; Referencing A Library ; A Toolbar Menu ; Making The Macro Library Available ; Some Minor Points ; Maintaining The Utility Function Library ; End-users And The Utility Function Library

You Gotta Start Somewhere

Suppose, fresh and starry-eyed from the macro course, you decide to write a macro, or three, to clear the contents of cells in Excel.

You know already that selecting cells and tapping the delete key deletes everything.

You have experimented with various methods of Delete, Clear, and find that no matter what you do, Excel provides no easy way to delete the numeric data you’ve keyed in, while leaving the text data and formulae intact.

Finally you discover or create a little macro that does the trick:

Public Sub WipeData()
' Dean Cardno; Vancouver; Br. Columbia
    Dim cl As Range
    For Each cl In ActiveCell.CurrentRegion
        If IsNumeric(cl.Value) Then ' ignore character data
            If cl.HasFormula = False Then ' as long as it is not a formula
                cl.Select
                cl.ClearContents ' clear the contents of the cell
            Else
            End If
        Else
        End If
    Next cl
End Sub

You could paste this code into an Excel VBA module and run it to see that it works.

Hooray!

Your instructor told you that functions are more useful, and that a properly written function can be used in a variety of ways, so you go to work and change the SUBroutine to a FUNCTION and write three (or more) macros that use the function:

Public Function WipeData(rng As Range)
    Dim cl As Range
    For Each cl In rng
        If IsNumeric(cl.Value) Then ' ignore character data
            If cl.HasFormula = False Then ' as long as it is not a formula
                cl.ClearContents ' clear the contents of the cell
            Else
            End If
        Else
        End If
    Next cl
End Function
Public Sub WipeCurrentRegion()
    Call WipeData(ActiveCell.CurrentRegion) ' Restrict activity to a block of cells
End Sub
Public Sub WipeCurrentSheet()
    Call WipeData(ActiveSheet.UsedRange) ' Restrict activity to cells on the active sheet
End Sub
Public Sub WipeCurrentBook()
    Dim wks As Worksheet
    For Each wks In Application.ActiveWorkbook.Worksheets
        Call WipeData(wks.UsedRange) ' Restrict activity cells in all sheets of the active workbook
    Next wks
End Sub

Now comes the parting of the ways.

The function (singular) belongs in a function library.

The macros (all three of them) belong in a macro library.

We will create and compile a function library, and we will maintain that library by accumulating our developer functions within.

We will create and compile a macro library, and we will maintain that library by accumulating our end-user macros within.

No wait! there’s MORE!

Our macro library will reference our function library, so that our macros can call our functions. Our functions will not need to call our macros. Only end-users use macros as a means of tapping into our powerful developer functions.

Our macro library will include a toolbar-menu system that provides a mouse-based method of accessing the macros.

The end-user will see the macros (and buttons) of the macro library; the function library will be hidden from view.

Creating A Function Library

Loading

Toronto and Mississauga, Friday, December 03, 2010 8:45 AM

Copyright © 1996-2010 Chris Greaves. All Rights Reserved.