I have run into a question where I have used late binding with some Excel exporting. I am creating a function to do formatting and, in the past, have done Early binding so that in a criteria for a function I could use a range object. But, I need to go with late binding and I'm not sure how to substitute this.
Anyone have an idea how to substitute the part in Red using late binding:
Code:
Public Function SetFormat(rng As [color=red]Range[/color])
With rng
.FontSize = 14
.Font.Bold = True
.Font.Color = vbRed
End With
End Function
I know about late binding, and in fact that is my problem. I am doing so, but I want to make my functions more reusable, so I want to be able to call that format function from another function. The problem lies in that I don't know how to convert it to late binding and use the
Public Function SetFormat(rng As Range)
part in red as a parameter passed from another function. It is EASY to do as shown IF I use EARLY binding. But I don't want to use early binding, I want to use LATE binding. So, I run into a compile error if I use rng As Range because without early binding Access doesn't have a clue about RANGE.
So my question actually is how can I still do what I'm doing with late binding? What substitute for rng As Range do I make? Do I use rng As Object and if I do, how do I instantiate it because it needs instantiation before it can receive the passed parameter from wherever it is being called from. That is my dilemma.
Public Sub Start()
Dim rng As Range
Set rng = Worksheets("Sheet1").Range("A1:B2")
SetFormat rng
End Sub
Public Sub SetFormat(rng As Object)
With rng
rng.Font.Size = 14
rng.Font.Bold = True
rng.Font.Color = vbRed
End With
End Sub
Obviously if you are using Access you need a reference to the Excel Object Liberary
Don't know if you cut & paste that code but it should read 'Font.Size' and not 'FontSize' ...
Of course it's best to avoid late binding if possible so you could try this...
Code:
Public Sub Start()
Dim fnt As Font
Set fnt = Worksheets("Sheet1").Range("A1:B2").Font
SetFormat fnt
End Sub
Public Sub SetFormat(fnt As Font)
With fnt
.Size = 14
.Bold = True
.Color = vbRed
End With
End Sub
Bob:
I would think that once you have loaded the users available Excel library with a late binding sequence, that the code you are currently using would work. You just need startup code that loads the library before you try and use the Range function. I'll admit I haven't tried it but it makes sense to me.
!Blue:
Why do you feel we should avoid Late Binding if possible?
Bob:
I would think that once you have loaded the users available Excel library with a late binding sequence, that the code you are currently using would work. You just need startup code that loads the library before you try and use the Range function. I'll admit I haven't tried it but it makes sense to me.
The performance argument against late binding has largely been declared irrelevant these days. There are real advantages to adopting it. IMO Access solutions are difficult enough to deploy. Anything we can do to ease the reference dependency is often a good thing.
(It's much less of an issue for those working in a predictable, corporate environment).
I consider the one requirement for whether binding isn't negotiable to be whenever Event Sinking will be required in your classes.
Conditional compilation can indeed help between early and late as Allan mentions.
However it's a pain to include in each of your procedures.
But you can abstract that effort away from the individual procedures a bit - depending upon who far you're willing to go.
For example you could start writing your own classes to wrap the functionality of the OM you're intending to work with. Then you have only those standard locations to change.
Obviously most would balk at the prospect of recreating an OM - and I'd agree.
But it could be worthwhile - particularly for a reason, such as to bring together the similar methods for DAO and ADO - with the ability to trivially switch between the wo.
More likely though - it's worth just wrapping a few of the very most commonly called methods.
A couple of examples I just mentioned over at UA are CurrentDb
Code:
Private mDb As DAO.Database
Function fCurrentDB() As DAO.Database
On Error Resume Next
Set fCurrentDB = mDb
If fCurrentDB Is Nothing Then
Set mDb = CurrentDb
Set fCurrentDB = mDb
End If
End Function
OpenRecordset
Code:
Public Enum ddDAORst
TypeTable = 1
TypeDynaset = 2
TypeSnapshot = 4
End Enum
Public Function fOpenRecordset(pstrSQL As String, Optional pType As ddDAORst = TypeDynaset, Optional pOption = Null) As DAO.Recordset
On Error GoTo errHere
Dim rst As DAO.Recordset
Set rst = fCurrentDB.OpenRecordset(pstrSQL, pType)
Set fOpenRecordset = rst
ExitHere:
Set rst = Nothing
Exit Function
errHere:
'fErrFullText Err, Err.Description, "fOpenRecordset"
Resume ExitHere
End Function
If you refer to your own locally running fCurrentDb instance in your application instead of CurrentDb - then it's trivial to switch that one location over to late binding at the end.
Personally - though it's one of the biggest culprits as a reference for becoming de-registered for your application (and hence causing the standard reference problems) - DAO isn't atop my list for removing as a reference.
Things like the FSO library, Regular Expressions and Office applications (if you don't need to sink those events!!) are better candidates.
