PDA

View Full Version : Solved: Excel & Word object libraries 11 vs 9



jwise
05-14-2007, 08:19 AM
With the help of several people on VBAX, I was able to get an application that updated and printed a Word document with Excel-calculated variables. This code is now working in three forms. The first form takes the data and prints a Word document using each data row in the spreadsheet. Version 2 prints only the "selected" row. Version 3 saves this Word file as opposed to printing it. The previous versions create the Word document, print it, then throw it away.

The 4th version was to create the Word document (like Ver 3) but only for the selected row (like Ver 2).

I use Excel 2000 (object library 9.0?) here and Excel 2003(object library 11.0) on my other PC. I often email the .xls files between these systems so that I can work on this problem from either machine.

Now my code will not compile, and it is giving me strange errors and indicating that Word is missing "Object library 11.0". When I attempt to
compile this new routine, VBA apparently comiles everything in this sheet (there are several routines) and it indicates errors in some of them also. These routines worked before. On Friday, I did send the .xls to the XL2003 machine so I could add data and write this 4th routine.

My intuition tells me that some piece of code compiled at the XL2003 machine has referenced a routine that is only present in XL2003. So when I try to compile on XL2000, I get this "missing object".

My understanding was that VBA is an interpreted language, so it should not be trying to use 11.0 object libraries in a 9.0 system.

What do I do about this? I'm pretty sure that there is no new feature in XL2003 that my code uses. I did this joint development platform (XL2000 and XL2003) on many other programs. How can I prevent this?

lucas
05-14-2007, 09:01 AM
Hello Jwise,
You need to look into early and late binding. Quote from Jon Peltiers website:

The advantage to Late Binding is that the developer does not have to worry about the wrong version of an object library being installed on the user's computer. For example, if I know my client will be automating Word, but I don't know which version, I can use Late Binding, and not worry that my reference to Word XP will break on an Office 2000 machine. I could set a reference to the earliest version that the client might use, say, Excel 97, or I can simply use Late Binding.
Link to his page on early and late binding:
http://peltiertech.com/Excel/EarlyLateBinding.html
another link about it from Bob's(XLD) website:
http://www.xldynamic.com/source/xld.EarlyLate.html

The rule of thumb common thread seems to be to develop using early binding so that your intellisense, etc. works for you while developing and then convert to late binding for release to use......hope this helps.

jwise
05-14-2007, 03:15 PM
Thanks for your reply.

I read your reply and both referenced items. It all makes sense EXCEPT that this is an interpreted language. Most folks call "binding" what we mainframer's call "link-editing". This is where external references are resolved. But this is only with COMPILED code, so this is completely outside my understanding.

I am trying to understand. Please do not infer that I am being argumentative.

I know that there are JAVA implementations where the JAVA code is "compiled" into "byte" code. Unlike ordinary compiled code, the "byte" code is not in the instruction set of the host computer. The "byte" code is interpreted. Is this what is going on?

Since I mentioned that there are four routines (with the first three working as designed), I could not believe my eyes when I tried to use them and got these "object" errors. I only worked with #4. The first three were working on BOTH machines. A new problem on #4 was not a big surprise, but the other problems were. I did a "compile" under VBE and all the routines were compiled, not just #4, and errors were detected that were not errors previously.

I have the feeling that if I would export this code out of VBA and then re-import it into a "clean" workbook/worksheet, that the problem would go away. I could also understand this if there was some construct that I used which was XL2003 ONLY. If this were the case, then I would not be getting compile errors on the previously working stuff.

So, is it safe to assume that if I had done ALL the development work on either XL2000 or XL2003, that this problem would not exist? If so, there ought to be a way to go to the other platform and "start from scratch" and eliminate this problem. And lastly, why did this problem just pop up? I've been doing this "split development time" for several months.

Thanks.

JonPeltier
05-15-2007, 09:48 AM
Remove any references to the wrong version of Office except for Excel (VBE Tools menu > References...).

Use Rob Bovey's Code Cleaner (http://apspro.com) to remove any scraps of intermediate code left over from "compiling". This basically exports and removes each module, saves the stripped workbook, then reimports the modules. If you can't use Rob's utility, then manually export the modules (and userforms), save the workbook, and import the modules.

jwise
05-15-2007, 10:26 AM
Your comments confirm my suspicion that there are some vestages of code stored in the VBA objects BESIDES the source. Apparently, I picked up a reference to Word 11 doing while working on the Office 2003 machine, and this object and the source was shipped back to the Office 2000 machine. Thus the trouble began.

You also confirmed my suspicion that if I exported all the source to "plain DOS files" and then re-imported it all back to a new workbook, the problem would vanish.

I think this is a deficiency in Excel's design. I understand that the next product will have new features, and you can't use those features in the "old" version, but my code doesn't utilize these "additional" features. So I think I should be able to move development to and fro version 9/11 without consideration. If I choose to utilize a v 11 new feature, I should get a problem when I go back to v 9.

This is obviously not your problem, and there probably are complexities that I am unaware of, but I feel better having said it! Cheap therapy!

Thanks again for not only an answer, but a vision of how it really works.

jwise
05-15-2007, 10:38 AM
Jon,

I attempted to follow the link to Rob Bovey's site. Is this correct? Is it
www.appspro.com (http://www.appspro.com) ?

Bob Phillips
05-15-2007, 11:31 AM
VBA is not fully compiled to machine language as the code runs on Windows PCs and Macs, and it is clear that the code is interpreted at run-time, a programming error,
such as incorrectly setting a range variable, is not trapped until that code is executed.

VBA code is translated and stored as machine independent codes, Opcodes. When the code is compiled or executed, the Opcodes are translated to Excodes, which are specific to the platform.

When you run VBA code which has not been compiled, the compilation is performed as-needed.

In addition to syntax checking, compiling a VBA project writes out the ExCodes and stores them in the workbook. This improves the workbook's startup time, since object references are already resolved where possible, and there is no need for a compilation when the code is first run. It does not make the VBA code faster, as VBA compiles at run time if necessary, that is the code is always compiled.

Those references, specific to the product version, are stored with the workbook if you use early binding.

PS Yes, that is Rob's site.

jwise
05-15-2007, 01:03 PM
This answer settles this problem.

This is very similar to several implementations, i.e. mainframe JAVA and Rexx code (at least USED to) work this way. I have heard of several products that "compiled" to an intermediate language, and then this intermediate was interpreted at run time. Thus you've eliminated some of the overhead of interpretation, and you still had machine independence. I think there are some emulators that work similarly (emulate one machine's architecture on another machine). This also buys some protection for the intellectual property rights of the program's author, since the dis-assembly of the intermediate code usually does a poor job of reconstructing the original source.

Thanks again to all who replied. I did download the Rob Bovey code and it did process my source. Magically the errors evaporated, and all is well on this front. Thanks to all responders... your knowledge is appreciated and your willingness to share it is remarkable.