Basic question

[hal8000]

I am relatively new to VB programming and have a very basic question. I have been using VB with very little knowledge of the nuisances of what I am doing. Basically hack attacks that seem to work, but I want to understand the reasons behind the code now. Can somone tell what the difference is between accessing excel using these two forms. Why you would use one over the other. Some things seem to work using one way and some work using the other.

Set xl = CreateObject("Excel.Application")
Dim wrkbook As Excel.Workbook
Set wrkbook = xl.Workbooks.Open("F:\My Documents\weeklyrpt.xls")

Dim xl As Excel.Application
Set xl = New Excel.Application
xl.Workbooks.Open "F:\My Documents\weeklyrpt.xls"

 

[jebry]

Hi!

Set xl = CreateObject("Excel.Application")
Dim wrkbook As Excel.Workbook
Set wrkbook = xl.Workbooks.Open("F:\My Documents\weeklyrpt.xls")

Dim xl As Excel.Application
Set xl = New Excel.Application
xl.Workbooks.Open "F:\My Documents\weeklyrpt.xls"

The first example uses late binding of the xl object variable. When you use late binding you will not need a reference to Excel in Access' reference list. Also late binding tends to be more efficient for memory use since the object variable isn't dimensioned until the code is run (if the code is in some decision statement then the memory may not be set aside at all. The early binding requires a reference to Excel in the list and the object is dimensioned whenever the code runs. You need to be careful with the late binding to make sure your object variable doesn't lose its reference to the Excel application or your code could have troubles.

The second difference between the two code snippets is that in the second example you don't reference the workbook with an object variable you just use the application to open it. This will severely limit what you can do with the workbook in code. In the first example you are using a variable to store a reference to the workbook and that will allow you to do most anything with the workbook in VBA that you could do actually in the workbook.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[fumei]

From a beginner's point of view, it is preferable to use early, rather than late, binding.

Early binding allows you to use the Object Browser to get reference information, plus the VB Editor "knows" the details of the object type. It can show you all the available properties and methods (OK, most of them) when you type a period after the variable.

This is most convenient for learning.

There are some advantages of late binding, especially for communication with older applications. However, late binding requires communication with the automation server to determine whether it supports a given property or method, with each line of code. Say you have code that sets four properties of an object. Each instruction requires communication to see if that property is supported.

Early binding does not, as those references are already known. Of course the drawback is, in fact, specifically that overhead. Nevertheless, early binding is the way to go.

Gerry

 

[PHV]

Nevertheless, early binding is the way to go
Except if the same code must run on different PC with different versions of office installed.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[hal8000]

Thanks everyone for the information. I am trying to make the conversion from hacking to actually knowing what I am doing and this type of information is valuable to me.

 

[fumei]

PHV - agreed. I should have added to

especially for communication with older applications

, and earlier version of Office itself.

Good point.

as usual.

Gerry