How to handle a missing VBA reference (To Excel)

[Dan2kx]

Hi guys,

I am in the process of migrating some access projects to 2010 (from 2003) and have hit another problem,

The background: we have upgraded some PC to office 2010, but not all, and some have had office removed (and we are using the viewers and access runtime).

Problem: in one project i have a reference to Excel (11.0) so that i can export some things straight to excel, now if excel isn't installed, it cant comit that reference, is there anything i can do to overcome this? (removing the reference invalidates the code objects, and it gets "all shouty")

i was thinking of somehow "blocking off" the excel code/modules if the reference couldn't be found, so that i didnt have to run two versions of the Front End.

Any ideas?

Cheers

[NeoPa]

You could try identifying the files found on the PCs where the software is installed (Tools | References lists where the actual files are found with the file names.) and copying just these files to any PC that doesn't have the software installed.

I'm afraid I can't tell you if this is in accordance with the license as I really don't know. I don't imagine it conflicts, but I can't be certain.

[Dan2kx]

I thought about that, but the file referenced is excel.exe itself, so I imagined, it wouldn't work, but I could try

[NeoPa]

In that case I'm pretty sure it would be an infringement of the license and I couldn't possibly recommend that.

[Dan2kx]

Yeah, I thought I was clutching at straws a bit! Looks like its the hard way again!!
Unless its possible to deactivate code on the fly? Which sounds just as ludicrous!!

[Stewart Ross]

There is another approach to working in a dual Excel 2003/2010 environment, but it can be a pain to implement. You could change your code to be late-bound so that a specific reference to Excel is not required. This allows the use of Excel 11 (2003) and 12 or greater (2007, 2010) without setting references which conflict. Moreover, you can use the SAME code in both environments as long as you understand (and cater for) the differences in some cases introduced by Excel 2007 and above.

To implement this you would have to replace all Excel-specific object definitions with generic ones (referring for example to an Object type instead of, say an Excel.Worksheet ); you would have to define in your module header any Excel constants you use; and you'd have to replace the early-binding instantiation of the Excel instance with a late-bound version.

Example of explicit definition of Excel VBA constants

Const xlLastCell = 11
Const xlLeft = -4131
Const xlMaximized = -4137
Const xlMinimized = -4140
Const xlNext = 1
Const xlNo = 2
Const xlNone = -4142
Const xlPrevious = 2
Const xlSolid = 1
Const xlSum = -4157
Const xlThin = 2
Const xlTop = -4160
Const xlWBATWorksheet = -4167
Const xlExcel8 = 56
Const xlOpenXMLWorkbookMacroEnabled = 52
Const xlOpenXMLWorkbook = 51

Example of Use of Generic Object Definitions

Private Function fFindNextBlankCellDown(ws As Object, ByVal StartRow As Long, ByVal currentcol As Long) As Long
    Dim wsRange As Object
    Dim varContents As Variant
    Set wsRange = ws.Cells(StartRow, currentcol)
    varContents = wsRange.Value
    Do Until IsEmpty(varContents)
        StartRow = StartRow + 1
        Set wsRange = ws.Cells(StartRow, currentcol)
        varContents = wsRange
    Loop
    fFindNextBlankCellDown = wsRange.Row
    Set wsRange = Nothing
End Function

Example of late-binding instantiation of Excel object

Public Sub InstantiateExcel()
    Set objExcel = CreateObject("Excel.Application")
    strFileExtension = ".xls"
    If objExcel.Application.Version >= 12 Then
        objExcel.Application.DefaultSaveFormat = xlOpenXMLWorkbook
        strFileExtension = ".xlsx"
    End If
End Sub

(Note that this example is from a class module, where the Excel object variable objExcel is a private object global to the class module, as is the string strFileExtensio n - hence why these are not passed as parameters to the InstantiateExce l sub.)

A disadvantage of using late binding during development is that without a reference to the Excel module there is no intellisense for the Excel object variables within the VBA editor environment. Get an object method application wrong and you'll need to look it up in a different environment (I use Excel itself to do so when necessary).

As you will also see from the example instantation sub above, the default format for Excel files is not the same for Excel 11 and 12 files - if you simply force the file extension to .XLS in Excel 2007 or 2010 the actual file format used is NOT forced to the Excel 2003 format. Before I discovered this problem I had error messages in both environments - in 2003, where the .XLS file was not recognised (as it was actually in a 2007 format), and in 2007 (where the .xls extension raised a non-fatal warning that the file format was not correct).

If you need to cater for the situation you outline where you do not have Excel installed at all you could check for success in instantiating Excel, perhaps like this, where the function below returns True if Excel can be instantiated and False otherwise:

Public Function ExcelInstalled() As Boolean
    Dim objExcel As Object
    On Error GoTo NotInstalled
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Quit
    ExcelInstalled = True
    Exit Function
NotInstalled:
    ExcelInstalled = False
End Function

The late-bound code itself will not cause errors in such an environment, but you would need to ensure that you do not call any of the Excel-related functions and subs if Excel is not installed.

-Stewart

[Dan2kx]

Thanks for the advice Stewart, i'm not entirely sure what you mean by early/late bound, unless you just mean referenced, or not (and then referred to in the function?

Here is an example of my code:

Function ExcelATE(qryName As String, var As Byte)
On Error GoTo Trap
Dim fn As String, xlApp As Excel.Application, xlBook As Excel.Workbook, x As Long, y As Long, z As Long
    If MsgBox("Would you like to view data in EXCEL?", vbQuestion + vbYesNo, "How would you like to view data...") _
            = vbNo Then DoCmd.OpenQuery qryName, , acReadOnly: Exit Function
    fn = FolderFromPath(CurrentDb.Name) & qryName & ".xls"
    DoCmd.SetWarnings False
    Kill (fn)
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, qryName, fn, True
        Set xlApp = CreateObject("Excel.Application")
        Set xlBook = xlApp.Workbooks.Open(fn)
            With xlApp
                .Cells.Select
                .Cells.EntireColumn.AutoFit
                .Range("A1").Select
            End With
            Select Case var
                Case 1
                    With xlApp
                        .Range("A1:M1").Select
                        .Selection.Interior.ColorIndex = 48
                        x = 2
                        Do Until .Range("A" & x).Value = Empty
                            .Range("A" & x & ":M" & x).Select
                            If .Range("E" & x).Value < .Range("D" & x).Value Then
                                With .Selection.Interior
                                    .ColorIndex = 6
                                    .Pattern = xlSolid
                                End With
                            End If
                            If .Range("G" & x).Value < Date + 28 Then .Selection.Font.ColorIndex = 3
                            x = x + 1
                        Loop
                        .Range("A1").Select
                    End With
                Case 2
                    With xlApp
                        .Range("A1:P1").Select
                        .Selection.Interior.ColorIndex = 48
                        x = 2
                            Do Until .Range("A" & x).Value = Empty
                                y = x: z = x
                                If .Range("E" & x).Value < Date + 28 Then .Range("A" & x & ":P" & x).Select: .Selection.Font.ColorIndex = 3
                                If .Range("A" & x).Value = .Range("A" & x + 1).Value Then
                                    Do Until .Range("A" & x).Value <> .Range("A" & x + 1).Value
                                        If .Range("E" & x).Value < Date + 28 Then .Range("A" & x & ":P" & x).Select: .Selection.Font.ColorIndex = 3
                                        x = x + 1
                                        z = x
                                    Loop
                                    x = x + 1
                                Else
                                    x = x + 1
                                End If
                                .Range("A" & y & ":P" & z).Select
                                .Selection.Borders(xlDiagonalDown).LineStyle = xlNone
                                .Selection.Borders(xlDiagonalUp).LineStyle = xlNone
                                With .Selection.Borders(xlEdgeLeft)
                                    .LineStyle = xlContinuous
                                    .Weight = xlMedium
                                    .ColorIndex = xlAutomatic
                                End With
                                With .Selection.Borders(xlEdgeTop)
                                    .LineStyle = xlContinuous
                                    .Weight = xlMedium
                                    .ColorIndex = xlAutomatic
                                End With
                                With .Selection.Borders(xlEdgeBottom)
                                    .LineStyle = xlContinuous
                                    .Weight = xlMedium
                                    .ColorIndex = xlAutomatic
                                End With
                                With .Selection.Borders(xlEdgeRight)
                                    .LineStyle = xlContinuous
                                    .Weight = xlMedium
                                    .ColorIndex = xlAutomatic
                                End With
                                .Selection.Borders(xlInsideVertical).LineStyle = xlNone
                                .Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
                            Loop
                        .Range("A1").Select
                    End With
            End Select
        xlBook.Save
        xlApp.Visible = True
        Set xlApp = Nothing
        Set xlBook = Nothing
        DoCmd.SetWarnings True
Exit Function

Trap:
    Select Case Err.Number
        Case 53, 1004
            Resume Next
        Case Else
            MsgBox Err.Number & " - " & Err.Description
            Exit Function
    End Select
End Function

So if i change the Dim line to be

Dim xlApp as Object

And keep line #10 same, will that just work? or do i need to still make constants of all the "xl" references?

[Stewart Ross]

You need to change ALL object definitions that currently reference Excel objects from their Excel.xxxx form to Object. For example, in line 3 above you have

Dim [...], xlApp As Excel.Application, xlBook As Excel.Workbook, [...]

which has to be changed to

Dim [...], xlApp As Object, xlBook As Object, [...]

Line 10 is indeed a late-bound creation of the Excel object.

With the normal practice of early binding you refer explicitly to a specified object library, so that the VBA interpreter 'knows' straight away at compile time where to find the definition of the methods concerned. With late binding, the VBA interpreter can't resolve references at compile time at all. Late-bound references to objects and their methods can only be resolved at run-time (hence 'late binding', as the VBA interpreter will not be able to 'know' if the method calls are correctly formed until the code is actually run).

And yes, you will have to define Excel VBA constants such as xlSolid, xlDiagonalDown etc in your module header - or alternatively you must replace each such reference with its actual numeric value. There are many such instances in the sample code you provided.

With early binding, the compiler resolves the symbolic constants to their numeric values as contained in the object library. It can't do that with late binding - so you must either substitute their numeric values directly into your code wherever you use an Excel constant, or define them as constants in the module header as appropriate.

It is easy enough to do a search for constants beginning with 'xl....', then copy these to a list in notepad, say. You can look up the value of each constant in the Excel object library from the VBA editor before you get rid of the Excel reference in your code - just change to the object view and search for each constant (eg xlSolid). The object window will show you the constant's numeric value.

Late binding carries a performance penalty, but in your case this is neglible compared to the benefits of the approach. For more information, please see our Insight article on Early vs Late Binding by our expert ADezii.

-Stewart

[NeoPa]

Personally, I avoid late-binding like the plague. I much prefer code to be tidy and defined. It makes it so much more maintainable.

That said Dan, if you want to explore late-binding, and you may well be in such a situation now, I couldn't think of anyone better to introduce/explain it to you than Stewart. Not only is his understanding very full, but he also has that uncommon ability to explain things well and fully. You couldn't be in better hands (I imagine after only a couple of posts so far in the thread, that's already obvious to you anyway).

[Stewart Ross]

@NeoPa - thank you for your very kind comments.

I agree entirely about avoiding late binding if at all possible, and would emphasise the point you make about tidiness and maintainability .

I myself have used late binding with Excel only when working in a dual A2003/A2007 environment, as it was the only practicable way to avoid problems with the references. For example, if changes were made to VBA code in A2007 the A2003 Excel reference was changed automatically and without warning by Access to the A2007 version - which resulted in failures when running again in the A2003 environment. This became unmanageable at the time, hence the late-bound approach.

Now that the environment in which my code is operating is almost entirely A2007 (with the odd A2010 instance) the same code is back to being early bound with all its normal object definitions etc in place.

Thanks again for your kind comments

-Stewart

[Dan2kx]

Thanks again fellas, i must admit i do like to keep my code nice and tidy.

However, i'll give it a try, and if it becomes to tedious, ill just remove it and have a seperate version for PC with excel installed.

Ill let you know how i get on