Building a Library of Routines – ?Exists

We are early in the process of building a library of routines that will make creating analytical reports from database extracts a snap.  In our last post I explained the basic template that provides the skeleton for just about every function I write. 

Today, we are adding our first functions to our library - 5 of them.  These functions test to see if certain objects exist in our spreadsheet so we’ll know if we need to create them, or just refresh them (in some cases).   The first thing we need to do is create a module to contain these routines. 

1. Get to the VBE (Alt-F11)
2. From the VBE menu navigate Insert > Module
3. Rename the new Module from Module1 to modGeneral (using the Properties Window). 
4. Select and copy the code below
5. Paste into the Code Window (*see next paragraph)
6. Make any corrections to code that didn’t paste correctly
7. From the VBE menu navigate File > Export File…
8. Save modGeneral and remember where you saved it.

Unfortunately, the code won’t paste 100% properly.  You will have to add carriage returns and perhaps fix a few things until your code looks exactly like what you see here.  Yes – it’s a pain.  But it’s easier than typing it yourself and far easier than writing from scratch.  (If someone knows a better method, please let me know so I can improve this blog.  Thanks.)

Next Post: Settings routine – This routine puts XL on hold while VBA code runs to speed it along and prevent XL Worksheet events from firing when they shouldn’t.

'Version: 10/14/2009

'General Spreadsheet Routines

Option Explicit

Global Const Success = False
Global Const Failure = True
 

Function NameExists(sName As String) as Boolean

'   NameExists:     Determine if a name exists in a spreadsheet

'   Parameters:     sName - Name to be checked
'   Example:        If Not NameExists("Data") then Setup_Data("Data")

'     Date   Init Modification
'   01/01/01 CWH  Initial Programming

    On Error GoTo ErrHandler
    NameExists = False     'Assume not found
   
    Dim objName As Object
   
    For Each objName In Names
        If objName.Name = sName Then
            NameExists = Right(Names(sName).Value, 5) <> "#REF!"
            Exit For
        End If
    Next
   
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "NameExists - Error#" & Err.Number & vbCrLf & Err.Description, _
        vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0

End Function

Function ShapeExists(sName As String) as Boolean

'   ShapeExists:    See if a Shape Exists

'   Parameters:     sName - Shape Name to be checked
'   Example:        If not ShapeExists("EasyButton") then _
'		    Create_Easy_Button "easy", "Show_Prompt", 10, 8

'     Date   Init Modification
'   01/01/01 CWH  Initial Programming

    On Error GoTo ErrHandler
    ShapeExists = False     'Assume not found
   
    Dim objName As Object
   
    For Each objName In ActiveSheet.Shapes
        If objName.Name = sName Then
            ShapeExists = True
            Exit For
        End If
    Next

ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "ShapeExists - Error#" & Err.Number & vbCrLf & Err.Description, _
        vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0

End Function

Function WorkSheetExists(sName As String) as Boolean

'   WorkSheetExists:See if a Worksheet Exists

'   Parameters:     sName - Worksheet Name to be checked
'   Example:        If not WorkSheetExists("Data") then Setup_Data("Data")

'     Date   Init Modification
'   01/01/01 CWH  Initial Programming

    On Error GoTo ErrHandler
    WorkSheetExists = False     'Assume not found
   
    Dim objName As Object
   
    For Each objName In Worksheets
        If objName.Name = sName Then
            WorkSheetExists = True
            Exit For
        End If
    Next
   
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "WorkSheetExists - Error#" & Err.Number & vbCrLf & Err.Description, _
        vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0

End Function

Function PivotTableExists(sWorksheet As String, sName As String) as Boolean

'   PivotTableExists:See if a PivotTable Exists

'   Parameters:     sName - PivotTable Name to be checked
'   Example:        If not PivotTableExists("pvtHrs") then Setup_pvtHrs

'     Date   Init Modification
'   01/01/01 CWH  Initial Programming

   On Error GoTo ErrHandler
    PivotTableExists = False     'Assume not found
   
    Dim objName As Object
   
    For Each objName InWorksheets(sWorksheet).PivotTables
        If objName.Name = sName Then
            PivotTableExists = True
            Exit For
        End If
    Next
   
ErrHandler:
   
    If Err.Number <>0 Then MsgBox _
        "PivotTableExists - Error#" & Err.Number & vbCrLf & Err.Description, _
        vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0

End Function

Function ChartExists(sName As String) as Boolean
    
'   ChartExists:    See if a Chart Exists

'   Parameters:     sName - Chart Name to be checked
'   Example:        If not ChartExists("chtHrs") then Setup_chtHrs

'     Date   Init Modification
'   01/01/01 CWH  Initial Programming

    On Error GoTo ErrHandler
    ChartExists = False     'Assume not found

    Dim objName As Object
   
    For Each objName In Charts
        If objName.Name = sName Then
            ChartExists = True
            Exit For
        End If
    Next

ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "ChartExists - Error#" & Err.Number & vbCrLf & Err.Description, _
        vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0

End Function