By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 2,161 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

How to handle a missing VBA reference (To Excel)

100+
P: 365
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
May 9 '12 #1

✓ answered by 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
Expand|Select|Wrap|Line Numbers
  1. Const xlLastCell = 11
  2. Const xlLeft = -4131
  3. Const xlMaximized = -4137
  4. Const xlMinimized = -4140
  5. Const xlNext = 1
  6. Const xlNo = 2
  7. Const xlNone = -4142
  8. Const xlPrevious = 2
  9. Const xlSolid = 1
  10. Const xlSum = -4157
  11. Const xlThin = 2
  12. Const xlTop = -4160
  13. Const xlWBATWorksheet = -4167
  14. Const xlExcel8 = 56
  15. Const xlOpenXMLWorkbookMacroEnabled = 52
  16. Const xlOpenXMLWorkbook = 51
Example of Use of Generic Object Definitions
Expand|Select|Wrap|Line Numbers
  1. Private Function fFindNextBlankCellDown(ws As Object, ByVal StartRow As Long, ByVal currentcol As Long) As Long
  2.     Dim wsRange As Object
  3.     Dim varContents As Variant
  4.     Set wsRange = ws.Cells(StartRow, currentcol)
  5.     varContents = wsRange.Value
  6.     Do Until IsEmpty(varContents)
  7.         StartRow = StartRow + 1
  8.         Set wsRange = ws.Cells(StartRow, currentcol)
  9.         varContents = wsRange
  10.     Loop
  11.     fFindNextBlankCellDown = wsRange.Row
  12.     Set wsRange = Nothing
  13. End Function
Example of late-binding instantiation of Excel object
Expand|Select|Wrap|Line Numbers
  1. Public Sub InstantiateExcel()
  2.     Set objExcel = CreateObject("Excel.Application")
  3.     strFileExtension = ".xls"
  4.     If objExcel.Application.Version >= 12 Then
  5.         objExcel.Application.DefaultSaveFormat = xlOpenXMLWorkbook
  6.         strFileExtension = ".xlsx"
  7.     End If
  8. 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 strFileExtension - hence why these are not passed as parameters to the InstantiateExcel 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:

Expand|Select|Wrap|Line Numbers
  1. Public Function ExcelInstalled() As Boolean
  2.     Dim objExcel As Object
  3.     On Error GoTo NotInstalled
  4.     Set objExcel = CreateObject("Excel.Application")
  5.     objExcel.Quit
  6.     ExcelInstalled = True
  7.     Exit Function
  8. NotInstalled:
  9.     ExcelInstalled = False
  10. 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

Share this Question
Share on Google+
10 Replies


NeoPa
Expert Mod 15k+
P: 31,307
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.
May 9 '12 #2

100+
P: 365
I thought about that, but the file referenced is excel.exe itself, so I imagined, it wouldn't work, but I could try
May 9 '12 #3

NeoPa
Expert Mod 15k+
P: 31,307
In that case I'm pretty sure it would be an infringement of the license and I couldn't possibly recommend that.
May 9 '12 #4

100+
P: 365
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!!
May 10 '12 #5

Expert Mod 2.5K+
P: 2,545
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
Expand|Select|Wrap|Line Numbers
  1. Const xlLastCell = 11
  2. Const xlLeft = -4131
  3. Const xlMaximized = -4137
  4. Const xlMinimized = -4140
  5. Const xlNext = 1
  6. Const xlNo = 2
  7. Const xlNone = -4142
  8. Const xlPrevious = 2
  9. Const xlSolid = 1
  10. Const xlSum = -4157
  11. Const xlThin = 2
  12. Const xlTop = -4160
  13. Const xlWBATWorksheet = -4167
  14. Const xlExcel8 = 56
  15. Const xlOpenXMLWorkbookMacroEnabled = 52
  16. Const xlOpenXMLWorkbook = 51
Example of Use of Generic Object Definitions
Expand|Select|Wrap|Line Numbers
  1. Private Function fFindNextBlankCellDown(ws As Object, ByVal StartRow As Long, ByVal currentcol As Long) As Long
  2.     Dim wsRange As Object
  3.     Dim varContents As Variant
  4.     Set wsRange = ws.Cells(StartRow, currentcol)
  5.     varContents = wsRange.Value
  6.     Do Until IsEmpty(varContents)
  7.         StartRow = StartRow + 1
  8.         Set wsRange = ws.Cells(StartRow, currentcol)
  9.         varContents = wsRange
  10.     Loop
  11.     fFindNextBlankCellDown = wsRange.Row
  12.     Set wsRange = Nothing
  13. End Function
Example of late-binding instantiation of Excel object
Expand|Select|Wrap|Line Numbers
  1. Public Sub InstantiateExcel()
  2.     Set objExcel = CreateObject("Excel.Application")
  3.     strFileExtension = ".xls"
  4.     If objExcel.Application.Version >= 12 Then
  5.         objExcel.Application.DefaultSaveFormat = xlOpenXMLWorkbook
  6.         strFileExtension = ".xlsx"
  7.     End If
  8. 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 strFileExtension - hence why these are not passed as parameters to the InstantiateExcel 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:

Expand|Select|Wrap|Line Numbers
  1. Public Function ExcelInstalled() As Boolean
  2.     Dim objExcel As Object
  3.     On Error GoTo NotInstalled
  4.     Set objExcel = CreateObject("Excel.Application")
  5.     objExcel.Quit
  6.     ExcelInstalled = True
  7.     Exit Function
  8. NotInstalled:
  9.     ExcelInstalled = False
  10. 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
May 10 '12 #6

100+
P: 365
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:

Expand|Select|Wrap|Line Numbers
  1. Function ExcelATE(qryName As String, var As Byte)
  2. On Error GoTo Trap
  3. Dim fn As String, xlApp As Excel.Application, xlBook As Excel.Workbook, x As Long, y As Long, z As Long
  4.     If MsgBox("Would you like to view data in EXCEL?", vbQuestion + vbYesNo, "How would you like to view data...") _
  5.             = vbNo Then DoCmd.OpenQuery qryName, , acReadOnly: Exit Function
  6.     fn = FolderFromPath(CurrentDb.Name) & qryName & ".xls"
  7.     DoCmd.SetWarnings False
  8.     Kill (fn)
  9.     DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, qryName, fn, True
  10.         Set xlApp = CreateObject("Excel.Application")
  11.         Set xlBook = xlApp.Workbooks.Open(fn)
  12.             With xlApp
  13.                 .Cells.Select
  14.                 .Cells.EntireColumn.AutoFit
  15.                 .Range("A1").Select
  16.             End With
  17.             Select Case var
  18.                 Case 1
  19.                     With xlApp
  20.                         .Range("A1:M1").Select
  21.                         .Selection.Interior.ColorIndex = 48
  22.                         x = 2
  23.                         Do Until .Range("A" & x).Value = Empty
  24.                             .Range("A" & x & ":M" & x).Select
  25.                             If .Range("E" & x).Value < .Range("D" & x).Value Then
  26.                                 With .Selection.Interior
  27.                                     .ColorIndex = 6
  28.                                     .Pattern = xlSolid
  29.                                 End With
  30.                             End If
  31.                             If .Range("G" & x).Value < Date + 28 Then .Selection.Font.ColorIndex = 3
  32.                             x = x + 1
  33.                         Loop
  34.                         .Range("A1").Select
  35.                     End With
  36.                 Case 2
  37.                     With xlApp
  38.                         .Range("A1:P1").Select
  39.                         .Selection.Interior.ColorIndex = 48
  40.                         x = 2
  41.                             Do Until .Range("A" & x).Value = Empty
  42.                                 y = x: z = x
  43.                                 If .Range("E" & x).Value < Date + 28 Then .Range("A" & x & ":P" & x).Select: .Selection.Font.ColorIndex = 3
  44.                                 If .Range("A" & x).Value = .Range("A" & x + 1).Value Then
  45.                                     Do Until .Range("A" & x).Value <> .Range("A" & x + 1).Value
  46.                                         If .Range("E" & x).Value < Date + 28 Then .Range("A" & x & ":P" & x).Select: .Selection.Font.ColorIndex = 3
  47.                                         x = x + 1
  48.                                         z = x
  49.                                     Loop
  50.                                     x = x + 1
  51.                                 Else
  52.                                     x = x + 1
  53.                                 End If
  54.                                 .Range("A" & y & ":P" & z).Select
  55.                                 .Selection.Borders(xlDiagonalDown).LineStyle = xlNone
  56.                                 .Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  57.                                 With .Selection.Borders(xlEdgeLeft)
  58.                                     .LineStyle = xlContinuous
  59.                                     .Weight = xlMedium
  60.                                     .ColorIndex = xlAutomatic
  61.                                 End With
  62.                                 With .Selection.Borders(xlEdgeTop)
  63.                                     .LineStyle = xlContinuous
  64.                                     .Weight = xlMedium
  65.                                     .ColorIndex = xlAutomatic
  66.                                 End With
  67.                                 With .Selection.Borders(xlEdgeBottom)
  68.                                     .LineStyle = xlContinuous
  69.                                     .Weight = xlMedium
  70.                                     .ColorIndex = xlAutomatic
  71.                                 End With
  72.                                 With .Selection.Borders(xlEdgeRight)
  73.                                     .LineStyle = xlContinuous
  74.                                     .Weight = xlMedium
  75.                                     .ColorIndex = xlAutomatic
  76.                                 End With
  77.                                 .Selection.Borders(xlInsideVertical).LineStyle = xlNone
  78.                                 .Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
  79.                             Loop
  80.                         .Range("A1").Select
  81.                     End With
  82.             End Select
  83.         xlBook.Save
  84.         xlApp.Visible = True
  85.         Set xlApp = Nothing
  86.         Set xlBook = Nothing
  87.         DoCmd.SetWarnings True
  88. Exit Function
  89.  
  90. Trap:
  91.     Select Case Err.Number
  92.         Case 53, 1004
  93.             Resume Next
  94.         Case Else
  95.             MsgBox Err.Number & " - " & Err.Description
  96.             Exit Function
  97.     End Select
  98. End Function
  99.  
So if i change the Dim line to be
Expand|Select|Wrap|Line Numbers
  1. 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?
May 11 '12 #7

Expert Mod 2.5K+
P: 2,545
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

Expand|Select|Wrap|Line Numbers
  1. Dim [...], xlApp As Excel.Application, xlBook As Excel.Workbook, [...]
which has to be changed to

Expand|Select|Wrap|Line Numbers
  1. 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
May 11 '12 #8

NeoPa
Expert Mod 15k+
P: 31,307
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).
May 11 '12 #9

Expert Mod 2.5K+
P: 2,545
@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
May 11 '12 #10

100+
P: 365
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
May 11 '12 #11

Post your reply

Sign in to post your reply or Sign up for a free account.