Why you should alway use .Cells when calling a range object

A sneaky little cause of possible bugs is (inconsistent use of) the .Cells-property when calling Range-objects. This opening sentence should have scared of all the non-geeks :).

Let me explain why. Consider next code example:

Sub TestCells()

    Dim rMyRange As Range
    Dim rCell As Range
    
    '1. Initialize range
    Set rMyRange = Worksheets(1).Range("A1:B3")
    '2. Set range to first column only
    Set rMyRange = Intersect(rMyRange, rMyRange.Columns(1))
    
    '3. Display address
    Debug.Print rMyRange.Address
    
    'Iterate through the cells of the column
    For Each rCell In rMyRange
        '4. Display individual cell addresses
        Debug.Print rCell.Address
    Next

End Sub

The example is simple enough: reset a range to its first column and display the adresses of the individual cells.
When you run this example this is the obvious output:

$A$1:$A$3
$A$1
$A$2
$A$3

Looks can be deceiving though. Some of you may have already noticed the cumbersome way of determining the first column part of the range.
Why not simply do it like this?:

    '2. Set range to first column only
    Set rMyRange = rMyRange.Columns(1)

Ok smarty pants, predict the output after you changed that line of code and run the example again. Same output? Not so much:

$A$1:$A$3
$A$1:$A$3

How can this be? For reasons beyond me the default property of a returned range is sometimes different according how you went about getting it.

The function Application.Intersect returns a collection of individual cells as expected; as a default a defined ranges and its  .Cells-property are identical; just comment out the line ‘Set rMyRange = rMyRange.Columns(1)’ and run the example. This is the expected output:

$A$1:$B$3
$A$1
$B$1
$A$2
$B$2
$A$3
$B$3

Apparently range-properties like Range.Columns and Range.Rows (and god knows what else) play by different rules; Range and Range.Cells no longer point at the some thing.
This is all the more confusing because the adresses of the different range-results are the same.

I ran into this issue when I was refactoring some code in Connexa and wanted to get rid of redundant Intersect-calls. The only way to tackle this inconsistency is to always call the .Cells collection of the range after you set it:

For Each rCell In rMyRange.Cells
    '4. Display individual cell addresses
    Debug.Print rCell.Address
Next

Live and learn!

 

Activate a Connexa add-in manually

Sometimes a Connexa add-in is not correctly installed or loaded; the Connexa tab is not present after Excel is opened.
It occurs when antivirus software or security policies prevented you from installing the setup.exe correctly or when MS Office was installed using Click-to-Run.
Follow the next few steps to activate the add-in manually:

– Open Excel
– Select the File tab in the ribbon and select ‘Options’:

– In Excel Options select Add-ins
– Make sure ‘Excel Add-ins’ is displayed in the drop-down and click button ‘Go’:

– In the Add-ins screen click button ‘Browse..’:

– In the Browse for file dialog navigate to the folder where the add-in is located (default location is: C:\Program Files [x86]\Connexa XS [Trial])
– Select the add-in file Connexa.xlam.
– Click ‘OK’ to exit the dialog.
– Click ‘OK’ in the Add-ins screen to activate the Connexa add-in:

– The add-in is loaded. Prove of which is the new Connexa tab and the following message:

InstallAddin-5

Handling MS Access form errors generically

As a database owner you will not want users to edit data in MS Access tables directly. It’s one of the reasons forms exist; some of the obvious advantages are:

  • Specific fields can be locked or hidden
  • The field order can be changed
  • Event-handling can be implemented

However, errors in forms can occur due to inadequate data handling. If this happens the messages displayed are often not very user-friendly. For a common user following message looks quite intimidating:

“The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate values and try again.”

This is especially true for forms with a datasheet view since they display multiple records.

To address this issue I’ve created a generic solution to handle data-related form errors. It consists of two components:

1. Generic code

The routine HandleFormError is called from the indivual Form_Error events. Therefore insert following code it in a vba module:

'Constants
Public Const APP = "My application"
Public Const ERR_ONETOMANYCONFLICT = 3101
Private Const ERR_RELATEDRECORDS1 = 3200
Private Const ERR_RELATEDRECORDS2 = 3201
Private Const ERR_REQUIREDDATA = 3314
Private Const ERR_DUPLICATEKEY = 3022
Private Const ERR_DATATYPE = 2113
Private Const ERR_INPUTMASK = 2279
Private Const ERR_NULLKEY = 3058
Private Const ERR_NULLVALUE = 3162
Private Const ERR_ZEROLENGTHSTRING = 3315
Private Const ERR_DATAVALIDATION1 = 2107
Private Const ERR_DATAVALIDATION2 = 3317
Private Const ERR_ITEMNOTINLIST = 2237

' Handle data-related form errors by showing user-friendly messages.
' Assign the appropiate return value to the Response parameter.
Public Sub HandleFormError(oForm As Form, _
        DataErr As Integer, ByRef Response As Integer)

    Const NEXT_MSG = "See next message for details."

    Select Case DataErr

        Case ERR_REQUIREDDATA
            MsgBox "Required value missing!" & vbCr & _
                   NEXT_MSG, vbInformation, APP
            Response = acDataErrDisplay

        Case ERR_RELATEDRECORDS1, ERR_RELATEDRECORDS2
            MsgBox "Relation conflict!" & vbCr & _
                   NEXT_MSG, vbInformation, APP
            Response = acDataErrDisplay

        Case ERR_NULLKEY, ERR_NULLVALUE, ERR_ZEROLENGTHSTRING
            MsgBox "Field can not be empty.", vbInformation, APP
            Response = acDataErrContinue

        Case ERR_DUPLICATEKEY
            MsgBox "The value is already in use to " & _
                   "create an unique record. Change the value.", _
                    vbInformation, APP
            Response = acDataErrContinue

        Case ERR_DATATYPE, ERR_INPUTMASK
            MsgBox "The value has an incorrect data type" & vbCr & _
                   "(f.i. text value in a numeric field).", _
                   vbInformation, APP
            Response = acDataErrContinue

        Case ERR_ITEMNOTINLIST
            MsgBox "Select an item from the list.", vbInformation, APP
            Response = acDataErrContinue

        Case ERR_DATAVALIDATION1, ERR_DATAVALIDATION2
            'Custom validation rules: show corresponding error message
             MsgBox AccessError(DataErr), vbInformation, APP
             Response = acDataErrContinue

        Case Else
            'Display the default error message
            Response = acDataErrDisplay

    End Select

End Sub

Regarding the errors ERR_REQUIREDDATA, ERR_RELATEDRECORDS1 and ERR_RELATEDRECORDS2 a work-around was needed due to a MS-bug. Ideally we would like to show the error messages with the field specific information using our own messagebox formatting (just like the errors ERR_DATAVALIDATION1 and ERR_DATAVALIDATION2). However, in that case the original message is not shown correctly. Therefore the default message is preceeded with our own. An alternative could be to comment out the cases regarding ERR_REQUIREDDATA, ERR_RELATEDRECORDS1 and ERR_RELATEDRECORDS2, so that only the default error message is shown.

2. Event-code per form

Per individual form implement next event:

' Data-related error is triggered in the form
Private Sub Form_Error(DataErr As Integer, Response As Integer)

    'Check for one-to-many relation conflict
    If DataErr = ERR_ONETOMANYCONFLICT Then
        'User entered data on the "many" side for which
        'there is no matching record on the "one" side.
        '[TODO:] Specify [field] in message
         MsgBox "First select a(n) [field]", vbInformation, APP
        Response = acDataErrContinue
    Else
        'Other data error; go to generic form error handling
        Call HandleFormError(Me, DataErr, Response)
    End If

End Sub

In this way I’ve been able to prevent a lot of cumbersome coding.  I hope it will benefit you as much as it did me. Feel free to use this code and alter the messages to your own liking.

 

Deep dive: Opening an encrypted Access file via VBA based on the shortcut to the workgroup information file

 

Connexa XS add-ins enable you to select and alter Access databases from Excel.
Some databases, however, have been encrypted using user-level security; a workgroup information file handles the security regarding database objects and a workgroup file shortcut is needed to allow individual users to open the encrypted database with the right permissions.
We wanted users to be able to connect to these secured databases from Excel as well. This second article in our Deep Dive series explains how we did this.
The easiest way to open a secured Access database is to let the user select the link to the workgroup file (to be able to select a .lnk file in an Open File-dialog is another issue which will not be discussed in this article). The user-specific arguments stored in the shortcut target path are subsequently used to automatically open the encrypted Access file.

Typically the target path of a workgroup file shortcut looks like this:

“%ProgramFiles%\Microsoft Office\OFFICE[version]\MSACCESS.EXE” “\[path]\MyDb.mdb” /WRKGRP “\[path]\MyWorkgroup.mdw” /USER MyUser /PWD MyPassword

Often the last two arguments (username and password) are omitted in which case the user will have to enter his/her credentials manually when opening the database.

Underneath code excerpt demonstrates in a simplified manner how we accomplished this. In order to access the shortcut target you’ll need Wscript to instantiate a shell object. The shell creates a temporary link object based on the selected shortcut. The property TargetPath of the link object contains the string value with the desired argument information.
Finally the arguments are determined through string manipulation of the TargetPath .

Option Explicit
'Global constants
Public Const ERR_ABORT = 666

'Members
Private msWorkgroup As String
Private msUser As String
Private msPassword As String

'Test routine DetermineShortcutArgs
Sub TestMdwLinkArguments()

    Dim sLinkFile As String
    Dim sDbFile As String

    On Error GoTo ErrH

    'Determine the path to the mdw-shortcut
    sLinkFile = "[path]\MyShortcut.lnk"

    'Call the routine with the proper argument
    sDbFile = DetermineShortcutArgs(sLinkFile)

    'Display the argument values in the immediate window (Ctrl+G)
    Debug.Print "Database:", sDbFile
    Debug.Print "Workgroup:", msWorkgroup
    Debug.Print "User:", msUser
    Debug.Print "Password:", msPassword

    Exit Sub
ErrH:
    Select Case Err.Number

        Case ERR_ABORT
            MsgBox Err.Description, vbExclamation
        Case Else
            MsgBox "An unexpected error occurred:" & vbCr & _
                Err.Description, vbCritical
    End Select
End Sub

' Get the properties stored in the shortcut to the workgroup file
Private Function DetermineShortcutArgs(sFilename As String) As String

    Dim oShell As Object
    Dim oLink As Object
    Dim sarrArgs() As String
    Dim sArg As String
    Dim iPos As Integer
    Dim i As Integer

    On Error GoTo ErrH

    'Instantiate shell object
    Set oShell = CreateObject("WScript.Shell")

    'Create a new temporary shortcut to get the arguments
    Set oLink = oShell.CreateShortcut(sFilename)

    'Check validity of the link.
    If InStr(1, oLink.TargetPath, "MSACCESS.EXE", vbTextCompare) = 0 Then
        Err.Raise ERR_ABORT, , "The workgroup link is invalid."
    End If

    'Split the arguments into a string array
    sarrArgs = Split(oLink.Arguments, "/")

    'Loop through the array
    For i = LBound(sarrArgs) To UBound(sarrArgs)

        'First argument contains the target database
        If i = 0 Then
            DetermineShortcutArgs = _
                    Trim$(Replace(sarrArgs(i), Chr(34), vbNullString))
        Else
            'Manipulate the string to extract the property name
            iPos = InStr(1, sarrArgs(i), " ")
            sArg = LCase$(Left(sarrArgs(i), iPos - 1))

            'Determine the property value based on the property name
            Select Case sArg

                Case "wrkgrp"
                    msWorkgroup = GetArgValue(iPos, sarrArgs(i))
                Case "user"
                    msUser = GetArgValue(iPos, sarrArgs(i))
                Case "pwd"
                    msPassword = GetArgValue(iPos, sarrArgs(i))

            End Select
        End If
    Next

ErrH:
    'Clean up
    Set oLink = Nothing
    Set oShell = Nothing
    If Err.Number <> 0 Then
        Err.Raise Err.Number, Err.Source, Err.Description
    End If
End Function

'Distill the value part from the argument and return it
Private Function GetArgValue(iPos As Integer, sArgument As String) _
                                                            As String
    GetArgValue = _
    Trim$(Replace$(Mid(sArgument, iPos + 1), Chr(34), vbNullString))
End Function

User-level security is not supported anymore by the new file formats in MS Office Access 2007/2010. However it still works for .mdb files, even in the latest Office versions. Since no easy alternative exists (except elaborate programming) expectations are that workgroup security will be around for a while. Thus the code presented here will hopefully be of some use to someone!

Deep dive: applying VBA error handling the right way

The second article in the ‘Deep Dive’-series covers the way error handling was implemented in our products. In order to develop robust software consistent use of code error handling is vital. Basic principles will be discussed and the manner they were applied in our add-ins. Visual Basic code examples will be gradually extended to help clarify the concepts. Prerequisite for this article is a basic knowledge of VBA error handling syntax.

Introduction

When something happens during code execution that is not meant to happen you’ll want to control how the code should respond.

Error handling (also called exception handling) shouldn’t be implemented in every routine.  Only add  it to the following types of routines:

  1.  Main routines; routines that are the starting point of code execution. Execution may be triggered by an event, user (inter)action or timer.
  2. Routines that need to process logically anticipated exceptions. It is undesirable to handle all errors centrally in one main routine. That’s why expected exceptions should be dealt with as local as possible.
  3. Routines that require logging / user messaging in case of an (un)expected error
  4. Routines that require clean up actions in case of an (un)expected error

Consider next simple code example. The only thing that happens in case of an error is that a message to the user is displayed in our own format:

Sub Main()

    Dim i As Integer

    On Error GoTo ErrH

    MsgBox "Quotient of 1 / " & i & " = " & 1 / i

    Exit Sub
ErrH:
    MsgBox Err.Description, vbCritical
End Sub

If we want to distinguish between expected and unexpected exceptions the code example should be expanded as follows. Try the sample with these input values: 0, 1 and x.

Sub Main()

    Dim i As Integer

    On Error GoTo ErrH

    i = InputBox(Prompt:="Value:", Default:=0)

    MsgBox "Quotient of 1 / " & i & " = " & 1 / i, vbInformation

    Exit Sub
ErrH:
    Select Case Err.Number

        Case 11
            MsgBox "Enter a value <> 0", vbExclamation
        Case Else
            MsgBox "An unexpected error occurred:" & vbCr & _
                Err.Description, vbCritical
    End Select
End Sub

Let’s extend our example even further by adding a clean up action that should always be carried out. Pay attention to the Exit Sub-call!

Sub Main()

    Dim i As Integer

    On Error GoTo ErrH

    i = InputBox(Prompt:="Value:", Default:="x")

    Application.StatusBar = "Calculating 1 / " & i & "..."

    MsgBox "Quotient of 1 / " & i & " = " & 1 / i, vbInformation

CleanUp:
    Application.StatusBar = False
    Exit Sub
ErrH:
    Select Case Err.Number

        Case 11
            MsgBox "Enter a value <> 0", vbExclamation
        Case Else
            MsgBox "An unexpected error occurred:" & vbCr & _
                Err.Description, vbCritical
    End Select
    Resume CleanUp
End Sub

Expanding the code example to a scenario

As functionality grows, code will obviously be refactored into separate routines. Extended functionality also impacts process flow and thus error handling.  This is covered in the final code expansion.  A separate sub CalculateQuotient is created which expects the input value as a parameter. Because of this the need arises to process exceptions differently; in the child sub the expected errors are raised using our own error numbers (like ERR_INPUT_IS_ZERO). These custom errors are subsequently handled in the main routine.
Other changes: the sub CalculateQuotient is called multiple times and another exception, initialization and clean up action are added.

Private Const ERR_INPUT_IS_ZERO = 666
Private Const ERR_INPUT_IS_TOO_HIGH = 667

Sub Main()

    On Error GoTo ErrH

    Call CalculateQuotient(InputBox(Prompt:="Value:", Default:=500))
    Call CalculateQuotient(InputBox(Prompt:="Value:", Default:=2))

    ActiveSheet.Cells(1).Select
    MsgBox "Proces has ended", vbInformation

CleanUp:
    Application.StatusBar = False
    Application.CutCopyMode = False
    Exit Sub
ErrH:
    Select Case Err.Number

        Case ERR_INPUT_IS_ZERO
            MsgBox "Enter a value <> 0", vbExclamation
        Case ERR_INPUT_IS_TOO_HIGH
            MsgBox "Value is too high!", vbExclamation
        Case Else
            MsgBox "An unexpected error occurred:" & vbCr & _
                Err.Description, vbCritical
    End Select
    Resume CleanUp
End Sub

Sub CalculateQuotient(i As Integer)

    Application.StatusBar = "Calculating 1 / " & i & "..."
    ActiveCell.Copy

    'i = "x" 'Uncomment to test unexpected error in this routine

    If i = 0 Then
        Err.Raise ERR_INPUT_IS_ZERO
    ElseIf i > 5 Then
        Err.Raise ERR_INPUT_IS_TOO_HIGH
    End If

    MsgBox "Quotient of 1 / " & i & " = " & 1 / i, vbInformation

End Sub

Identifying error handling scenario’s

For every exception it needs to be determined whether code execution should be aborted or not when the exception occurs. In addition also should be decided if an error should be handled centrally or locally. Theoretically this results in four different error handling scenario’s. Underneath picture shows all cases schematically:

What follows now is a discussion of all four cases. The appropiate code is offered so you can apply it to your specific situation.

Case 1: Handle centrally, abort process

The code sample above implements case 1 when an error occurs in  a child sub. The main routine handles all errors after which the process is terminated; the second quotient is not calculated and the message ‘”Proces has ended”‘ is not displayed. This is the most common scenario of dealing with exception handling.

Case 2: Handle centrally, continue process

In some cases you’ll want the process to continue after an expected exception has arisen. For instance if you only want to log the incident if the error occurs. In that case central error handling is not advised because it would require GoTo constructions that would obscure clear process flow. Conclusion: in such cases exceptions need to be processed in the child sub itself, including clean up actions. This is discussed next.

Case 3: Handle locally, continue process

Consider next sample in which this is realised. Note that error handling in the main routine is limited to unexpected errors only. Also note our custom error numbers have changed scope: from member level constants to routine level constants . Finally pay attention to the cleanup action in the child sub; the CleanUp:..Exit Sub-construction is absent in CalculateQuotient. The reason for this is that this mechanism doesn’t work if an error is raised in the error handling section of a routine; this is done in the child sub in case of an unexpected error.
The sample can easily be adjusted to process expected exceptions locally AND centrally (control question: what changes should be made to the code?).

Sub Main()

    On Error GoTo ErrH

    Call CalculateQuotient(InputBox(Prompt:="Value:", Default:=8))
    Call CalculateQuotient(InputBox(Prompt:="Value:", Default:=2))

    ActiveSheet.Cells(1).Select
    MsgBox "Proces has ended", vbInformation

CleanUp:
    Application.StatusBar = False
    Exit Sub
ErrH:
    MsgBox "An unexpected error occurred:" & vbCr & _
            Err.Description, vbCritical

    Resume CleanUp
End Sub

Sub CalculateQuotient(i As Integer)

    Const ERR_INPUT_IS_ZERO = 666
    Const ERR_INPUT_IS_TOO_HIGH = 667

    On Error GoTo ErrH

    Application.StatusBar = "Calculating 1 / " & i & "..."
    ActiveCell.Copy

    'i = "x" 'Uncomment to test unexpected error in this routine

    If i = 0 Then
        Err.Raise ERR_INPUT_IS_ZERO
    ElseIf i > 5 Then
        Err.Raise ERR_INPUT_IS_TOO_HIGH
    End If

    MsgBox "Quotient of 1 / " & i & " = " & 1 / i, vbInformation

ErrH:
    Application.CutCopyMode = False 'Clean up action!

    Select Case Err.Number

        Case 0
            'No error
        Case ERR_INPUT_IS_ZERO
            MsgBox "Enter a value <> 0", vbExclamation
        Case ERR_INPUT_IS_TOO_HIGH
            MsgBox "Value is too high!", vbExclamation
        Case Else
            Err.Raise Err.Number, Err.Source, Err.Description
    End Select
End Sub

Case 4: Handle locally, abort process

The last scenario also deals with exceptions on a local level. However, code execution should be halted after that. Use the following VBA to accoplish this. Its structure looks a lot like that of Case 3. There are two differences though:

  1. A new custom error number is introduced: ERR_IGNORE. The reason for this is that exception handling should be ignored in the main routine; this was already done in sub CalculateQuotient
  2. In CalculateQuotient ERR_IGNORE is raised in the error handling section to force the process to terminate.

Note that cleanup actions are performed both in the local ánd main routine!

Private Const ERR_IGNORE = 668

Sub Main()

    On Error GoTo ErrH

    Call CalculateQuotient(InputBox(Prompt:="Value:", Default:=8))
    Call CalculateQuotient(InputBox(Prompt:="Value:", Default:=2))

    ActiveSheet.Cells(1).Select
    MsgBox "Proces has ended", vbInformation

CleanUp:
    Application.StatusBar = False
    Exit Sub
ErrH:
    Select Case Err.Number

        Case ERR_IGNORE
            'Error was already handled locally
        Case Else
            MsgBox "An unexpected error occurred:" & vbCr & _
                Err.Description, vbCritical
    End Select

    Resume CleanUp
End Sub

Sub CalculateQuotient(i As Integer)

    Const ERR_INPUT_IS_ZERO = 666
    Const ERR_INPUT_IS_TOO_HIGH = 667

    On Error GoTo ErrH

    Application.StatusBar = "Calculating 1 / " & i & "..."
    ActiveCell.Copy

    'i = "x" 'Uncomment to test unexpected error in this routine

    If i = 0 Then
        Err.Raise ERR_INPUT_IS_ZERO
    ElseIf i > 5 Then
        Err.Raise ERR_INPUT_IS_TOO_HIGH
    End If

    MsgBox "Quotient of 1 / " & i & " = " & 1 / i, vbInformation

ErrH:
    Application.CutCopyMode = False 'Clean up action!

    Select Case Err.Number

        Case 0
            'No error
        Case ERR_INPUT_IS_ZERO
            MsgBox "Enter a value <> 0", vbExclamation
        Case ERR_INPUT_IS_TOO_HIGH
            MsgBox "Value is too high!", vbExclamation
        Case Else
            Err.Raise Err.Number, Err.Source, Err.Description
    End Select

    'Abort in case of expected error
    Err.Raise ERR_IGNORE

End Sub

I wish this article would’ve been around when I started developing in VBA years ago. The next best thing though, is offering these concepts and code so you don’t have to struggle with it as I had to!

Deep dive: DAO and ADO field type mapping

During the development of our Excel add-ins we were faced with numerous technical challenges. Some of our solutions are discussed in a series of articles called ‘Deep dive’.

Today we publish our first article of the series which handles the mapping of  DAO and ADO field types.

DAO and ADO

In order to implement the two-way communication between MS Excel and MS Access we had to make use of two separate application programming interfaces regarding database objects: DAO 3.6 and ADO 2.8.
Data Access object (DAO) is an API that enables you to write applications that are independent of any particular database management system (DBMS). DAO is optimized for the Microsoft Jet database engine and provides a set of data access objects (database objects, tabledef and querydef objects, recordset objects, and others). It works best with MS Access file types.
ActiveX Data Objects (ADO) is another API to data and information. In some ways it is the successor of DAO. ADO was designed to be the one data interface needed for single and multi-tier client/server and web-based data-driven solution development. Since the introduction of ADO.NET ADO is now primarily used by MS Office products like MS Excel;  Through Visual Basic for Applications (VBA) its type library enables developers to interact with other datasources. The primary benefits of ADO are its ease of use, high speed, low memory overhead, and a small disk footprint.

Object accessibility

To be able to explore the queries and tables within a MS Access from an external application like MS Excel we needed the DAO API. It is not possible to access the system tables of an Access database (particularly table MSysObjects) from outside the Access file itself. The same is true for determining the relations in the datamodel. Moreover some specific properties of data objects in MS Access can only be explored through DAO.
Examples of properties regarding Access table fields:

  • Caption
  • ColumnWidth
  • Description
  • Format
  • ValidationText

Examples regarding Access queries:

  • Parameters
  • SQL

The challenge

Because of its advantages we preferred ADO over DAO with regard to our data manipulation requirements; We only wanted to rely on ADO objects when interacting with Access. This posed a challenge since both type libraries use different field type definitions. For instance: DAO 3.6 uses the constant ‘dbSingle’ (=6) to designate the data type of a field as a single precision numeric value. ADO 2.8 on the other hand uses ‘adSingle’ (=4) for the same field. In setting up our table field objects this issue was tackled easily. We just executed the sql statement ‘SELECT * FROM [table name]’ thus retrieving the ADO fields object definitions in a ADODB.Recordset. No mapping between DAO and ADO field types needed!
For parameterized queries, however, we ran into a problem. From within Excel we wanted to allow users to execute Access queries that require parameters. To further facilitate this we also wanted to enforce the appropiate data type validations per parameter when he/she runs the query from Excel. When a query requires a numeric value as input parameter no text should be allowed as input.
However, the field type of a parameter could only be determined through DAO. The parameters collection of a DAO.QueryDef object exposes the DAO field types. Unfortunately Connexa uses an ADODB.Command object to execute (parameterized) queries: an ADO data type is required as an argument to create an ADODB.Parameter before this parameter can be appended to the command object.

The solution

In order to solve this we had to come up with a mapping routine (among other things). We relied heavily on the information on allenbrowne.com to accomplish this. Below you find the mapping routine as it ended up in the interface class (‘IDS’ stands for Interface Data Source):

'Description: Returns the ADO equivalent of a DAO data type 
'Input: iTypeDB (Integer). The DAO data type of a parameter object 
'Output: (Long). The ADO equivalent of the DAO input argument 
'Author: Emiel Nijhuis, Red Circle Software 
'Changes: Initial version, 7-7-2010 
'Reviewed: 29-7-2010; ref. RV_ENI2010_e481.docx 
'Remarks: None
Private Function IDS_MapToTypeADO(iTypeDB As Integer) As Long
    Select Case iTypeDB
        'Fixed width adWChar does not exist
        Case dbText: IDS_MapToTypeADO = adVarWChar 
        Case dbMemo: IDS_MapToTypeADO = adLongVarWChar
        Case dbByte: IDS_MapToTypeADO = adUnsignedTinyInt
        Case dbInteger: IDS_MapToTypeADO = adSmallInt
        Case dbLong: IDS_MapToTypeADO = adInteger
        Case dbSingle: IDS_MapToTypeADO = adSingle
        Case dbDouble: IDS_MapToTypeADO = adDouble
        Case dbGUID: IDS_MapToTypeADO = adGUID
        Case dbDecimal: IDS_MapToTypeADO = adNumeric
        Case dbDate: IDS_MapToTypeADO = adDate
        Case dbCurrency: IDS_MapToTypeADO = adCurrency
        Case dbBoolean: IDS_MapToTypeADO = adBoolean
        Case dbLongBinary: IDS_MapToTypeADO = adLongVarBinary
        Case dbBinary: IDS_MapToTypeADO = adVarBinary
        Case Else: IDS_MapToTypeADO = adVarWChar

    End Select
End Function

We hope it will benefit you as much as it did us.

Setup program for Excel add-ins

During the development stage of the first version of Connexa XS the need arose for a suitable installation tool for our addin.
I came across this interesting article of Jan Karel Pieterse, a fellow professional Office developer.

The article outlined how to use the product ‘Setup Factory‘ to easily realize a setup file for an Excel add-in. Based on the information in the article and its comments I was able to create a user-friendly and robust installation file for the Connexa product family. I did notice some areas for improvement though, which is incorporated in the information which follows (and by now in the initial article as well).

First some background how Excel handles add-in files like .xlam or .xla:

How Add-ins are Managed in Excel

In Excel, all the user sees is this list of available Add-ins:

You can navigate to this dialog as follows:

  • Excel 2003: Menubar, Tools, Add-ins
  • Excel 2007: Start-button , Excel Options, Add-Ins, drop down: Excel add-ins, Go
  • Excel 2010: File tab, Options, Add-Ins, drop down: Excel add-ins, Go

Which Add-ins are available

Under the hood, Excel keeps score in the registry which Add-ins there are and which are installed.
To build the list in the above dialog, Excel looks in a couple of places:

1. The Add-ins folder:

There are a couple of locations Excel looks for add-ins:

C:\Documents and Settings\UserName\Application Data\Microsoft\AddIns
or altenatively:
C:\Program Files\Microsoft Office\OFFICE11\Library (Excel 2003) ,
C:\Program Files\Microsoft Office\OFFICE12\Library (Excel 2007) etcetera

Any Add-ins in this folder are automatically included in the Add-ins dialog.

2. The registry:

For Add-ins in a different location from the ones shown above, Excel will look in the registry. Keys are added there when you click the browse button to locate an Add-in.

Which Add-ins are Selected

Excel notes what Add-ins are selected. It does so using a number of values in this part of the registry:

HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Options

For each selected Add-in,  Excel adds an entry in this location, successively called “OPEN”, “OPEN1”, “OPEN2”,…

These keys each contain the name of the add-in to be opened (and sometimes some command-line parameters). If an add-in is not in the add-ins folder the full path is included, e.g.:
“C:\Program Files\Connexa XS\Connexa v12.48.xlam”

Be advised that these registry entries will only be added / deleted AFTER Excel is closed.
To be complete: an alternative location in the registry exists, containing values for each Add-in to be shown in the Add-ins dialog:

HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Add-in Manager

The value needed is simply the path to the Add-in, f.i.:
C:\Program Files\Autosafe\autosafe.xla
Since this location plays no role regarding installed add-ins it will not be discussed further.

How To Install An Excel Add-in using Setup Factory

Setup factory does not come with an automatic/wizard driven way to handle the registry entries that need to be produced for Excel, so an Add-in is properly added to the Add-ins dialog and is installed. But it does come with a versatile scripting language that enables you to program these entries yourself quite easily.

Note that only the registry part needs scripting, the remaining stuff (creating the setup screens, configuring how the setup works, where to install your files, what files to install…) is done through easy to use wizards and configuration screens.

Script for registry modifications during Install of an Add-in

The action editor enables you to add actions or scripts to events in Setup Factory. To install an Excel Add-in successfully you need to implement the following actions:

On Startup

The following script is optional but may come in handy if you want the user to close Excel before installing your add-in:

--Check whether Excel is opened.
sValues = Window.EnumerateTitles(true);

for iCount, sValue in sValues do
    if String.Find(sValue, "Microsoft Excel", 1, true)>0 then 

        Dialog.Message("Error", "First close Microsoft Excel."..
        String.Char(10)..String.Char(10)..
        "Installer will now abort.",
        MB_OK, MB_ICONINFORMATION);
        Application.Exit(0);
    end;
end;

On Post Install

To make sure no registry entries are changed until all files have been installed successfully, make sure you add this script to the “On Post Install” action in Setup factory.

The script shown below does the following:

  • Find out which Excel versions are present in the system by reading what numeric keys are present in the next registry location:
    HKEY_CURRENT_USER\Software\Microsoft\Office

    This information is subsequently stored in the array variable sVersions (each version has a numeric entry here: 11.0 for Excel 2003, 12.0 for Excel 2007 and so on).

  • For each of these versions the script then looks in the Excel/Options part of the registry to determine the maximum index number regarding the OPEN registry entries (if any).
  • After that, the new OPEN registry entry containing the first available index number is added to the registry.

The script assumes you have created a -what Setup Factory calls- “Custom Session variable”, named “AddinFileName”, which contains the filename of your add-in

The script:

-- Determine registry key (2 = HK CURRENT USER)
sVersions = Registry.GetKeyNames(2, "Software\\Microsoft\\Office");

-- Iterate through the registry keys per MS Office-version
for iCount1, sVersion in sVersions do    
    -- Try opening the registry key
    sSubKey = "Software\\Microsoft\\Office\\"..sVersion..
              "\\Excel\\Options\\"
    sValues = Registry.GetValueNames(2, sSubKey);

    --initialize index counter
    iIndex = -2
    if sValues then

        --Determine the index of the maximimum OPEN registry entry
        for iCount2, sValue in sValues do

            if (String.Left(sValue, 4) == "OPEN") then            
                --Check whether the user did not already install
                --the same add-in to prevent errors when opening Excel
                sKeysValue = Registry.GetValue(2, sSubKey, sValue, true)  
                if String.Find(sKeysValue, SessionVar.Expand(
                              "%AddinFileName%"), 1, false) > 0 then
                    iIndex = -1
                    -- leave loop
                    break;
                else
                    if (sValue == "OPEN") then
                        iIndex = 0
                    else
                        iIndex = String.ToNumber(String.Mid(
                                 sValue, 5, String.Length(sValue)-4))
                    end;
                end;
            end;
        end;

        -- -1 means: This add-in is already installed; we're done
        if iIndex ~= -1 then        
            --Determine path based on variable "%AddinFileName%
            sAppPath = String.Char(34)..
                       SessionVar.Expand("%AppFolder%")..
                       "\\"..
                       SessionVar.Expand("%AddinFileName%")..
                       String.Char(34)

            -- -2 is the initialized value of the index counter
            if (iIndex == -2) then
                -- OPEN-key does not exist
                Registry.SetValue(2, sSubKey, "OPEN",
                                  sAppPath, REG_SZ)
            else
                Registry.SetValue(2, sSubKey, "OPEN"..(iIndex + 1),
                                  sAppPath, REG_SZ)
            end;
        end;
    end;
end;

How to uninstall an add-in

Of course Setup Factory also generates an uninstall method, both in the start menu and in the Add/remove programs applet of Windows Control Panel.

A registry change is necessary because the proper Options/OPEN entry has to be removed from the registry in order to prevent startup errors during load of Excel. Again the scripts are shown per Setup Factory action.

On Startup

Again, the following script is optional if you want the user to close Excel before uninstalling your add-in:

--Check whether Excel is opened.
sValues = Window.EnumerateTitles(true);

for iCount, sValue in sValues do

    if String.Find(sValue, "Microsoft Excel", 1, true)>0 then                   
        Dialog.Message("Error", "First close Microsoft Excel."..
                        String.Char(10)..String.Char(10)..
                        "Uninstaller will now abort.",
                        MB_OK, MB_ICONINFORMATION);
        -- Make sure the process ends with a value other than 0
        -- so the uninstall can be performed again.
        Application.Exit(EXIT_REASON_USER_ABORTED);
    end;    
end;

On Post Install

The following script does the following:

  • Find out which Excel versions are present in the system by reading what numeric keys are present in this registry location:
    HKEY_CURRENT_USER\Software\Microsoft\Office

    (Each version has its own entry here: 11.0 for Excel 2003, 12.0 for Excel 2007 etc.)

  • Delete the registry key regarding our add-in.

The script:

-- Determine registry key (2 = HK CURRENT USER)
sVersions = Registry.GetKeyNames(2, "Software\\Microsoft\\Office");

-- Iterate through the registry keys per MS Office-version
for iCount1, sVersion in sVersions do

    -- Try opening the registry key
    sValues = Registry.GetValueNames(2,
    "Software\\Microsoft\\Office\\"..sVersion.."\\Excel\\Options");

    if sValues then

        for iCount2, sValue in sValues do

            -- Any installed add-ins present in this Office version?
            if (String.Left(sValue, 4) == "OPEN") then

                sSubKey = "Software\\Microsoft\\Office\\"..sVersion..
                          "\\Excel\\Options\\"
                sKeysValue = Registry.GetValue(2, sSubKey, sValue, true)

                -- Delete the registry key if we encounter our add-in
                if String.Find(sKeysValue, SessionVar.Expand(
                               "%AddinFileName%"), 1, false)>0 then
                    --Dialog.Message(sSubKey, sValue) –-*for debugging*
                    Registry.DeleteValue(2, sSubKey, sValue)
                end;
            end;
        end;  
    end;
end;