Early vs. Late Binding

Article contributed by Dave Rado

There are two ways to use Automation (or OLE Automation) to programmatically control another application.

Late binding uses CreateObject to create and instance of the application object, which you can then control. For example, to create a new instance of Excel using late binding:

Dim oXL As Object
Set oXL = CreateObject(“Excel.Application”)

On the other hand, to manipulate an existing instance of Excel (if Excel is already open) you would use GetObject (regardless whether you’re using early or late binding):

Dim oXL As Object
Set oXL = GetObject(, “Excel.Application”)

To use early binding, you first need to set a reference in your project to the application you want to manipulate. In the VB Editor of any Office application, or in VB itself, you do this by selecting Tools + References, and selecting the application you want from the list (e.g. Microsoft Excel 8.0 Object Library).

To create a new instance of Excel using early binding:

Dim oXL As Excel.Application
Set oXL = New Excel.Application

In either case, incidentally, you can first try to get an existing instance of Excel, and if that returns an error, you can create a new instance in your error handler.

Advantages of Early Binding

    1. Your code will run considerably faster, because it can all be compiled up front. With late binding, the code relating to an application you declared as an object has to, in effect, be compiled as it runs.
    2. Because your code can all be compiled up front, debugging is far easier – select Debug + Compile, and the compiler will be able to spot syntax errors which would have been missed had you used late binding.
    3. You have full access in your project to intellisense (type a keyword and a dot to get a popup list of properties and methods supported by that keyword, select one to insert it; type a keyword and press F1 to launch the Help topic on that keyword).
    4. You have full access to the application’s object model via the Object Browser and VBA Help.
    5. You have access to the application’s built-in constants. For instance, if you are automating Word from Excel, you can use:

Dim objWord As Word.Application
Set objWord = New Word.Application

With objWord
.Visible = True
.Activate
.WindowState = wdWindowStateMaximize
.Documents.Open (“c:\temp\temp.doc”)
End With 

Furthermore, when you type

.WindowState =

you’ll get a pop-up list of the supported constants, and can simply pick wdWindowStateMaximize from the list.

If you used late binding, you would need to use:

    .WindowState = 1

.. and you would need to know (by looking it up in Word’s Object Browser) that the value of the constant wdWindowStateMaximize happens to be 1.

All this makes programming using early binding immeasurably easier than using late binding.

Advantages of Late Binding

  1. The main advantage is that code which uses late binding is more certain to be version-independent

    If you set a reference in a Word 97 project to Microsoft Excel 8.0 Object Library, then the project will run OK on a machine which has Office 2000 installed. Word 2000 changes the reference on the fly to theMicrosoft Excel 9.0 Object Library.

    But as they famously say, YMMV. Problems have been found in certain circumstances. For instance, if you run a Word 97 project containing a reference to the Excel 8.0 object library on a machine with Office 2000 installed, it will run OK, but you may get the occasional cannot open macro storage error unless you save the project in Word 2000. If you do save it in Word 2000, the reference will change to the Excel 9.0 object library. So if you use early binding and support a mixed environment, it may be safest to create separate Word 97 and Word 2000 versions of your addins, despite the maintenance overhead.

  2. The more references your project contains, the larger the file size and the longer it takes to compile.
  3. Some programming environments don’t allow you to create references to another application.

Summary

Personally, as someone who finds programming difficult at the best of times, I would never dream of using late binding – why make life harder for yourself than it has to be? But some programming geniuses prefer to use late binding, because of the peace of mind it gives them regarding version independence – or maybe some of them just enjoy the challenge! <g> But you pays your money and makes your choice …

To those unfortunate souls using programming environments in which you have to use late binding, all I can say is: Look on the bright side – you could have ended up as an Assembly language programmer …

How to make a device with unsigned driver work?

Windows cannot verify the digital signature for the drivers required for this device. A recent hardware or software change might have installed a file that is signed incorrectly or damaged, or that might be malicious software from an unknown source. (Code 52)

Recently when I reinstall my Win7 64x system on my PC, I had difficulty of install my TEAC UD503 DAC on it through the USB port. No matter how many times that I uninstall and install the driver, system just kept telling me the driver is unsigned.

The reason? I think it is because TEAC’s driver has a expired signed certificate for Windows system.

After tried so many times based on the information from internet, nothing works. The only time I can install the hardware is by pressing F8 and enter test mode for the system which will skip verifying the signed drivers, but after rebooting the hardware got disabled again.

Finaly I found a software which can solve this issue perfectly and now I am using my UD 503 DAC perfectly: 64Signer V1.2. The software was developed by programmer Zhang Pei, posted at http://www.yiiyee.cn/Blog/64signer/

Here are the steps to solve the issue in English:

捕获

  1. Make sure the enable test signing option is ticked.
  2. select the .sys driver file that you need to sign. (for my TEAC UD503 DAC, the file is teacahsd.sys, which is in c:\windows\system32\drivers folder.
  3. Reboot your computer, uninstall the software from your windows device manager, and plug in your hardware again, and you can use it now. 🙂

 

PS: all the tools ultilized by him are from SDK from Microsoft, which are not available from a GUI, he just created a much easier way for other developers and users to solve the issue. 🙂

 

You can download the software from my blog below:

64Signer V1.2

How to Acquire Vlookup in 3 minutes and never forget it?

As a financial professional, I use vlookup on an everyday basis. But for some of you who do not use Excel that often, using Vlookup might be a painful experience, either with other people’s help, or with Excel formula insert function.

insert formula

Is it that hard to use? Maybe not. I will help you understand it and remember it forever.

Firstly, what is Vlookup? Why do you need it so much?

I have a nick name for it: Vlinkup. Why? Because most of the time, it is the most handy tool you can use to Link two spreadsheet. (Remember it, you will need it to remember the formula)

Here is the example you need to solve:

You have a table of fruit orders with weight of orders, you want to link it to another spreadsheet, which includes the prices to calculate the total price to pay.

How can you use Vlookup to solve it?

2. formula

graph

5

Now the key is the shared same data (column) between the two spreadsheets, the maze is the area which includes the shared same data and the value you want to fetch.

There are three factors you need to remember in the Vlookup formular: Key (shared same value), Maze (the area which includes the shared value and the value you need to fetch to the other spreadsheet), and the steps, how many columns between the shared value and the fetched value, as shown below:

4

Ps: the last parameter, in practical work, always input as “false”.

 Now close your eyes and draw the picture of the maze in your mind. Next time when you want to use vlookup, think of the picture, you will know the formula, you have my word  :) :

 3

6

 

And here are some bonus, 3 tips for vlookup formula:

  1. Sometimes when you get #N/A with vlookup, don’t forget to check the format of the key value. It is highly possible they are both numbers and one of them is formatted as text.7
  2. Always make sure to add $ mark to your search area (your maze), the reason is that you can only get consistent result by absolute reference. It is all right that you don’t understand. Just remember, we need MONEY.

         How you do it? Select the whole area string, such as “[Book2]Sheet1!   A2:D6” and press F4.

8

  1. When the Maze area is too big, you don’t need to use your finger to count column after column. Use your mouse to select the area, and the small text box beside your cursor can tell you.
  2. 9

The principle I applied in this article is inspired by Tony Buzan’s book 《The most important graph in the world》

And I want to thank his co-author Jennifer Goddard for her kindness to sign my copy. 🙂

I highly recommend it to everybody, it could be a inspiration for you to change your life.