I’m like a lot of Excel gurus, I suspect, in that I’ll see something that Excel does and think “I can do that better”. Or, better yet, I’ll see something Excel doesn’t do at all an think, “Oh yeah, I have to build that.” It doesn’t really matter if I need the utility or will ever use it more than once, the idea that it could be done looms out there like a challenge waiting to be met.
(Remind me to tell you sometime about the latest windmill I’m tilting toward, and have been for over a week.)
In the last several years of constructing utilities, I’ve come to rely on a simple little routine to tell me whether I should use the routine. No, it doesn’t do anything magical like evaluate my worksheets and choose the best tool automatically. If it did that, then we’d be verging on the days of Skynet. No this is more about whether the environment is right for a utility.
See, I have some utilities that require a workbook to be present. No workbook, no use for the utility. Worse yet, no workbook, potential error as the utility tries to locate one.
With that in mind, I add this tiny function to any add-ins that could potentially be loaded without a workbook being present. It checks for a workbook and returns a TRUE or FALSE depending on what it finds:
Function bWorkbookPresent() As Boolean
‘*******************************************************************************
‘ Description: This function test to see if there is a workbook available.
‘
‘ Author: Scott Lyerly
‘ Contact: scott.c.lyerly@gmail.com
‘
‘ Name: Date: Init: Modification:
‘ bWorkbookPresent 16-DEC-2013 SCL Original development
‘
‘ Arguments: None
‘
‘ Returns: Boolean TRUE=workbook present; FALSE=workbook is absent
‘*******************************************************************************
‘ Variable declaration
Dim wkb As Workbook
‘ We’ll turn off error handling to keep this from throwing an error.
On Error Resume Next
‘ Try to set the workbook variables.
Set wkb = ActiveWorkbook
‘ Check if the variable is Nothing
If wkb Is Nothing Then
‘ If it is, then there was no workbook present to set the variable, meaning
‘ there is no workbook present.
MsgBox “No workbook present.” & _
vbNewLine & vbNewLine & _
“Operation cancelled.”, _
vbOKOnly + vbExclamation, _
“Workbook Error”
‘ Return FALSE for the function
bWorkbookPresent = False
Else
‘ Otherwise, there was a workbook present, so we’re good.
‘ Return TRUE for the function.
bWorkbookPresent = True
End If
‘ Make sure to turn error handling back on.
On Error GoTo 0
End Function
Implementing this is really easy, just a single line of code:
If Not bWorkbookPresent Then Exit Sub
That’s it. Feel free to reuse it. Enjoy.
Scott C Lyerly 