Category: VBA

Excel Geeking: Checking If A Workbook Is Present

December 16th, 2013
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.

Excel Geeking: Using VBA With Configuration Files

December 3rd, 2013

Nothing like a good old fashioned configuration file to make you feel like you’re programming in 1995. But you know what? Sometimes you just need to do it. And it still amazes me how many off-the-shelf applications still rely on INI files to store their settings (I’m looking at you, LotusNotes!).

Personally, I like INI files. I find them easy to work with and easy to deploy. And, of course, it doesn’t hurt that you can read and write to them using VBA.

If you Google reading or writing to INI files using VBA, you’ll get a bunch of different results, all pointing you in the same basic direction. Specifically, you need to use two different Windows APIs in order to do this “GetPrivateProfileString”, and “WritePrivateProfileString”. One reads from an INI file, the other writes to it. I’m going to assume you can figure out which is which.

Invariably, the examples you uncover on Google show that these routines are always separate. One routine fo reading, one for writing.

I hate that.

I like my code as concise as possible while still being as modular as possible. What do I mean by that? I mean that I want a routine (or function in this case) that I can call from another point in the code and choose whether I’m reading or writing to the configuration file. And I want the routine portable enough so I can drop it into any application I want and not have to worry about customizing it too terribly much.

And so, I give you the code below, commented for your pleasure:


'*******************************************************************************
' Declaration for Reading and Wrting to an INI file.
'*******************************************************************************

'++++++++++++++++++++++++++++++++++++++++++++++++++++
' API Functions for Reading and Writing to INI File
'++++++++++++++++++++++++++++++++++++++++++++++++++++

' Declare for reading INI files.
Private Declare Function GetPrivateProfileString Lib "kernel32" _
    Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As String, _
                                      ByVal lpKeyName As Any, _
                                      ByVal lpDefault As String, _
                                      ByVal lpReturnedString As String, _
                                      ByVal nSize As Long, _
                                      ByVal lpFileName As String)As Long
                                      
' Declare for writing INI files.
Private Declare Function WritePrivateProfileString Lib "kernel32" _
    Alias "WritePrivateProfileStringA" (ByVal lpApplicationName As String, _
                                        ByVal lpKeyName As Any, _
                                        ByVal lpString As Any, _
                                        ByVal lpFileName As String)As Long


'++++++++++++++++++++++++++++++++++++++++++++++++++++
' Enumeration for sManageSectionEntry funtion
'++++++++++++++++++++++++++++++++++++++++++++++++++++

Enum iniAction
    iniRead = 1
    iniWrite = 2
End Enum
'*******************************************************************************
' End INI file declaratin Section.
'*******************************************************************************

Function sManageSectionEntry(inAction As iniAction, _
                             sSection As String, _
                             sKey As String, _
                             sIniFile As String, _
                             Optional sValue As String)As String
'*******************************************************************************
' Description:  This reads an INI file section/key combination and
'               returns the read value as a string.
'
' Author:       Scott Lyerly
' Contact:      scott.c.lyerly@gmail.com
'
' Notes:        Requires "Private Declare Function GetPrivateProfileString" and
'               "WritePrivateProfileString" to be added in the declarations
'               at the top of the module.
'
' Name:                 Date:           Init:   Modification:
' sManageSectionEntry   26-Nov-2013     SCL     Original development
'
' Arguments:    inAction    The action to take in teh funciton, reading or writing to
'                           to the INI file. Uses the enumeration iniAction in the
'                           declarations section.
'               sSection    The seciton of the INI file to search
'               sKey        The key of the INI from which to retrieve a value
'               sIniFile    The name and directory location of the INI file
'               sValue      The value to be written to the INI file (if writing - optional)
'
' Returns:      string      The return string is one of three things:
'                           1) The value being sought from the INI file.
'                           2) The value being written to the INI file (should match
'                              the sValue parameter).
'                           3) The word "Error". This can be changed to whatever makes
'                              the most sense to the programmer using it.
'*******************************************************************************

On Error GoTo Err_ManageSectionEntry

    ' Variable declarations.
    Dim sRetBuf         As String
Dim iLenBuf         As Integer
Dim sFileName       As String
Dim sReturnValue    As String
Dim lRetVal         As Long
    
    ' Based on the inAction parameter, take action.
    If inAction = iniRead Then  ' If reading from the INI file.

        ' Set the return buffer to by 256 spaces. This should be enough to
        ' hold the value being returned from the INI file, but if not,
        ' increase the value.
        sRetBuf = Space(256)

        ' Get the size of the return buffer.
        iLenBuf = Len(sRetBuf)

        ' Read the INI Section/Key value into the return variable.
        sReturnValue = GetPrivateProfileString(sSection, _
                                               sKey, _
                                               "", _
                                               sRetBuf, _
                                               iLenBuf, _
                                               sIniFile)

        ' Trim the excess garbage that comes through with the variable.
        sReturnValue = Trim(Left(sRetBuf, sReturnValue))

        ' If we get a value returned, pass it back as the argument.
        ' Else pass "False".
        If Len(sReturnValue) > 0 Then
            sManageSectionEntry = sReturnValue
        Else
            sManageSectionEntry = "Error"
        End If
ElseIf inAction = iniWrite Then ' If writing to the INI file.

        ' Check to see if a value was passed in the sValue parameter.
        If Len(sValue) = 0 Then
            sManageSectionEntry = "Error"

        Else
            
            ' Write to the INI file and capture the value returned
            ' in the API function.
            lRetVal = WritePrivateProfileString(sSection, _
                                               sKey, _
                                               sValue, _
                                               sIniFile)

            ' Check to see if we had an error wrting to the INI file.
            If lRetVal = 0 Then sManageSectionEntry = "Error"

        End If
End If
    
Exit_Clean:
    Exit Function
    
Err_ManageSectionEntry:
    MsgBox Err.Number & ": " & Err.Description
    Resume Exit_Clean

End Function

Some thoughts on what’s going on here:

I’ve taken the two APIs that you would call to read and write to a configuration file and put them into a single function. I’m choosing which action to take based on the inbound inAction parameter.

That parameter, inAction, you may have noticed is an enumeration set up at the top of this module. I like the enumeration in this case because it lets me be very specific in my choice of action to take with regard to the INI file. Yes, I could have saved four lines of code by using a boolean instead. I could have set the function to “read” if TRUE and “write” if FALSE. Except I hate that option. I like enumerations for explicitly spelling out what my options are for value for a specific parameter. That way there’s no confusion.

So what does the implementation of this function look like? Glad you asked! I’ve included a sample routine below that you easily use to test this function out.


Sub SampleINIFunctionImplementaion()

    Const sINI_FILE As String = "C:\Users\scott\Desktop\fruits & veggies.ini"

    Dim sReturn As String

    ' Read the ini file
    sReturn = sManageSectionEntry(iniRead, "Produce", "Fruit", sINI_FILE)
    MsgBox sReturn
    sReturn = sManageSectionEntry(iniRead, "Produce", "Vegetable", sINI_FILE)
    MsgBox sReturn

    ' Write to the ini file
    sReturn = sManageSectionEntry(iniWrite, "Produce", "Fruit", sINI_FILE, "banana")
    sReturn = sManageSectionEntry(iniWrite, "Produce", "Vegetable", sINI_FILE, "squash")

 End Sub

That’s about it. Feel free to copy and paste and use for your own Excel applications.

(Fine print: use at your own risk, blah blah blah…)

Excel Geeking: Backdoor Into Your Excel Files

November 21st, 2013

When I’m building a spreadsheet application, invariably I have to have a routine that initializes the application. Often times I have sheets I need to hide from the end user. These are usually sheets full of configuration settings or system tables, things like that. I might also have toolbars that call certain functions or routines. I might have password protection on some of the sheets. You get the idea.

What I usually do is to add a small function that allows me to crack open the full workbook without having to go into the code itself to open everything up.

I start by putting an empty file of some sort in the same directory location as the application workbook. I’ll call it something like “debug.ini” or “password.txt” or “backdoor.ini” or something similar. It can actually be anything really. The file won’t hold anything, it just needs to be present.

Once the file is in place, you can add the code below as a function in a module. If you’re following any of the practices from “Professional Excel Development” by Bullen, Bovey, and Green, then you’d probably stick this in a module called MSystemCode. But it’s totally up to you.

Function bDebugMode() As Boolean ' Description: This checks the whether the application should be opened in debug mode. ' ' Author: Scott Lyerly ' Contact: scott.c.lyerly@gmail.com ' ' Name: Date: Init: Modification: ' bDebugMode 21-Nov-2013 SCL Original development ' ' Arguments: None ' ' Returns: Boolean TRUE = success, FALSE = failure '
' Review the length of the path and file name of the debug file using Dir. If Len(Dir(ThisWorkbook.Path & "\debug.ini")) = 0 Then ' The the lenght is zero (0), the file does not exist and ' we are not in debug mode. bDebugMode = False Else ' If the length is greater than zero (0), we are in debug mode. bDebugMode = True End If
End Function

I’ve got plenty of comments in the code, so I don’t think I need to break it down further here.

Once this function is in place, add the code below to the Workbook_Open event so that it fires whenever the file is opened.

Private Sub Workbook_Open()
' Check if the application is in debug mode. ' If it is not, initialize the application for the user. If Not bDebugMode Then InitializeApplication Else ' If we are in debug mode, open the file up and unprotect the Demand tab. OpenFileForDevelopment End If
End Sub

You can see that I have two branches this application can go down. One is to proceed as normal, with the application not opening in debug mode. I typically call the routine for that branch something like “InitializeApplication”. It’s where I store all the code I want to run before the user can use the file.

The second branch might be called “OpenFileForDeveloment”. This is the branch to go down when I want to unhide everything, unprotect everything, and prevent user interface items like toolbars from being created.

Using this practice, I can open the file in a user mode or a debug/development mode without touching a single line of code.
Excel Geeking: Changing a Query Parameter in Access from Excel

November 13th, 2013

If you read the “About Me” page, then you’ll see that the first thing I say is that I’m an Excel guru. And I am. It’s what I do for a living where I work (among other things).

But till now, I haven’t logged any Excel or VBA related posts. Not a one.

Let’s change that!

Fair warning, this one is going to be geeky. Now’s your chance to bail.

Still there?

Okay, let’s go then!

I do a lot of “Excel as a front end” work, where Excel is the user interface, and the data gets sent back to a database, usually Access, because it so prevalent.

I ran into a situation where I needed to pass a parameter to a query in Access because that query was a perquisite of my running another query that had the first one as a dependency.

Most of the time, when I’m connecting to Access from Excel, I use ADO. But in this case, I used DAO because it was simpler. Below is the resulting routine, in which I pass a string variable to the query prior to using that query elsewhere. Fully commented for your reading pleasure.

Sub Pass_Parameter_To_Query(sWeek As String) ' This passes a parameter to the max of time qry in order to enable dynamic time period run of the report. ' ' Inbound: sWeek = a string variable containing the week in question, in whatever format is required by the query. ' ' NOTE: in order to run this, you'll need a reference to the Microsoft DAO X.X Object Library.

' Variables declarations. Dim sDBPath As String ' This is the path of the DB. Dim sDBName As String ' This is the name of the DB Dim accObj As Object ' This will be our Access object Dim dbs As DataBase ' This is the database object we'll use (requires the reference) Dim qryDef As QueryDef ' This is the query definition we'll use (requires the reference) Dim sSQL As String ' This is our SQL string. Dim sQueryName As String ' This is our query name.

' We'll start out by setting the string variables.

' The DB path and name are actually global constants I use elsewhere, so I don't set them specifically here. sDBPath = msDATABASE_PATH sDBName = msDATABASE_NAME ' This is the name of the query we'll be changing. sQueryName = "qryTimeMaxes" ' This is out SQL statement: the dynamic part that will be fed from a user form is the Week field. sSQL = "SELECT Max(MonRptData.Year) AS MaxOfYear, Max(MonRptData.[Fiscal Season]) AS [MaxOfFiscal Season], Max(MonRptData.[Fiscal Quarter]) AS [MaxOfFiscal Quarter], Max(MonRptData.Month) AS MaxOfMonth, MonRptData.Week " _ & "FROM MonRptData " _ & "GROUP BY MonRptData.Week " _ & "HAVING (((MonRptData.Week)=" & sWeek & "));"
' Now we'll play with our objects (nothing dirty!) ' First we set the access object. The access object isn't really an access object at all, just a plain old ordinary object that we make into an access object using the "Access.Application" string. Set accObj = CreateObject("Access.Application") ' Then we open the object, using an access object model method, OpenCurrentDatabase, the the DB set by our path and DB name strings. accObj.OpenCurrentDatabase sDBPath & sDBName, False ' Next we'll set the database variable to the database we've just opened. ' Note that, because we didn't make our DB visible when we opened it, you can't see it, but it's open nevertheless. Set dbs = accObj.CurrentDb ' Now we'll get into the query itself. ' First we delete the old one... dbs.QueryDefs.Delete sQueryName ' ... then we recreate it with our new SQL string Set qryDef = dbs.CreateQueryDef(sQueryName, sSQL) ' That's it. The only left if to clean up. We'll close the database... dbs.Close ' ...then destroy our objects to free up all the juicy memory they take up. Set qryDef = Nothing Set dbs = Nothing Set accObj = Nothing
End Sub
If you followed that all the way through, then you’ll probably realize that I didn’t technically pass the parameter to the query in question. What I actually did was delete the query, then recreate it with the new parameter. This gives you the same result as setting the query parameter if you were actually in the Access database.

That’s it. Hope somebody out there finds it useful.

Category: VBA

Excel Geeking: Checking If A Workbook Is Present

Excel Geeking: Using VBA With Configuration Files

Excel Geeking: Backdoor Into Your Excel Files

Excel Geeking: Changing a Query Parameter in Access from Excel