PDA

View Full Version : Late-binding --pls sort out the confusion



energizek
10-15-2009, 01:50 PM
I have developed a sampling application in Excel 2007 that uses a lot of VBA automation. When I developed the program I was unaware of the early/late binding issues and mistakenly did everything in early binding. Now I need to convert the code to late binding so that it can run on Office 2000 and 2003 platforms. I understand the concept of late binding and the need to declare objects; however I can't find any step-by-step explanation for actually re-coding the program. I am a self-taught programmer and everything I've found assumes a lot of knowledge.
I would greatly appreciate answers to the following basic questions:
1) How do I know which parts of my code are objects that needs to be put in the form:
Dim Var1 as Object
Set Var1 = createObject (.....)
For example I have the following piece of code:
Dim FrmCtrl as MSForms.Control -does this type of code need to be recoded?
2) Does it make a difference if all of the late binding occurs within an excel application that is already open and running (i.e. I don't need to call up other Office applications or export the code somewhere)?
3) Are all of the enumeration variables that will need to be replaced with constants identified with an "xl" preceding their name? As in "xldown". In other words, if I find and replace all of these variables with the appropriate constant will I have taken care of this issue?
4) Should I be concerned about all of the userforms, command buttons and activeX controls that are being used in my application? Will these be able to work in Office 200 & 2003?
5) Do I need to change the automation aspects such as: workbook_open, commandbutton1_click, worksheet_activate, etc...? I used these automations heavily in my coding and am now concerned that this may have impeded the compatibility of my program.

Any other resources, books, blogs, forums, examples you can recommend would be VERY MUCH appreciated. I've just wasted a full work week surfing the web and trying to solve this issue on my own.

Thank you,
Katie

Bob Phillips
10-15-2009, 03:25 PM
I have developed a sampling application in Excel 2007 that uses a lot of VBA automation. When I developed the program I was unaware of the early/late binding issues and mistakenly did everything in early binding. Now I need to convert the code to late binding so that it can run on Office 2000 and 2003 platforms. I understand the concept of late binding and the need to declare objects; however I can't find any step-by-step explanation for actually re-coding the program. I am a self-taught programmer and everything I've found assumes a lot of knowledge.

I would greatly appreciate answers to the following basic questions:

1) How do I know which parts of my code are objects that needs to be put in the form:
Dim Var1 as Object
Set Var1 = createObject (.....)

For example I have the following piece of code:
Dim FrmCtrl as MSForms.Control -does this type of code need to be recoded?

No, forms are implicitly referenced in Excel, so forms can be used safely.


2) Does it make a difference if all of the late binding occurs within an excel application that is already open and running (i.e. I don't need to call up other Office applications or export the code somewhere)?

It can't occur ina closed Excel app, that just makes no sense.


3) Are all of the enumeration variables that will need to be replaced with constants identified with an "xl" preceding their name? As in "xldown". In other words, if I find and replace all of these variables with the appropriate constant will I have taken care of this issue?

Yes, in principle, but my approach is to define my own enumerated list with the same names and values, so I can still use the recognisable constant values in the code.


4) Should I be concerned about all of the userforms, command buttons and activeX controls that are being used in my application? Will these be able to work in Office 200 & 2003?

No, don't be concerned.


5) Do I need to change the automation aspects such as: workbook_open, commandbutton1_click, worksheet_activate, etc...? I used these automations heavily in my coding and am now concerned that this may have impeded the compatibility of my program.

No, these are events not objects, it is not these that are early/late binding.


Any other resources, books, blogs, forums, examples you can recommend would be VERY MUCH appreciated. I've just wasted a full work week surfing the web and trying to solve this issue on my own.

I have written a page on the technique I use. It is an Outlook mailing invoked from excel, the principle applies.

See http://xldynamic.com/source/xld.EarlyLate.html

GTO
10-15-2009, 04:56 PM
I have developed a sampling application in Excel 2007...

3) Are all of the enumeration variables that will need to be replaced with constants identified with an "xl" preceding their name? As in "xldown". In other words, if I find and replace all of these variables with the appropriate constant will I have taken care of this issue?


Hi Katie,

Say, maybe I am misunderstanding that question, but are you asking if you have to replace stuff like Range("A1").End(xlDown) with the numeric?

BTW, I saw that this was your first post and you just recently joined. Welcome :) , and a friendly Howdy from Arizona :hi:.

Mark

Aflatoon
10-16-2009, 12:39 AM
If you are not automating other applications, then you probably don't need to late bind at all. What you may find is that you have used a feature of Excel 2007 that simply doesn't exist in earlier versions, in which case you are out of luck and will have to rethink. That is why it is generally recommended to develop an application in the earliest version you will have to support, although you may still have to adjust things to get them to run in 2007.