Beyond Excel: VBA and Database Manipulation

Oct 22 2009   4:26PM GMT

Building a Library of Routines – ?Exists



Posted by: Craig Hatmaker
Tags:
database
development
excel
Microsoft Excel
ms query
odbc
sql
vba

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

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: