Problems moving VBA in Excel worksheet from Office 2000 to XP

[dean12]

I've got an Excel spreadsheet with tons of attached VBA code. This has been written on an Office 2000 installation.

When it get's sent to a user with Office XP and they save, edit, whatever, and return it to me, there is now a problem with the REFERENCES.

The references have been changed to want to include Microsoft office 10 object library and I do not have that - I have the office 9 object library.

I've got lots of users that mix Office 2000 and Office XP and moving files between us is becoming a problem.

I thought that doing a SAVE AS Microsoft Excel 97-2000 file type would solve the problem but it does not appear to do so.

Any ideas? I just need it to open whatever would be the default object library for Microsoft Excel is on the current machine.

 

[rdodge]

Sounds like a case of late binding. You can use the application's Version property to determine which version is in use. Given the issues you are coming into, you may have your code using early binding, which in general is easier to work with and doesn't take as long to process, but there are certain cases where early binding doesn't work which forces the use of late binding for the version independence, but is also harder to work with. Here's a couple of links that will help provide you with the steps and explanation of the 2 binding methods.

http://www.dicks-clicks.com/excel/olBinding.htm

http://www.mvps.org/word/FAQs/InterDev/EarlyvsLateBinding.htm

And for those that really wants to get technical, here's the MS Documentation of it.

http://support.microsoft.com/defaul...port/kb/articles/Q245/1/15.ASP&NoWebContent=1

http://support.microsoft.com/default.aspx?scid=kb;EN-US;247579

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[dean12]

So I guess the issue is that to get around problems when one user has Office 2000 and another has Office XP, I need to do late binding in the VBA code......

My question is where can I read up on this and get some good details on the process. The previous post did give some starter information but I read that and it's just not clear to me where the OBJECT is to be used and not used.

For example I am in Excel. I can setup an OBJECT for the application - no problem. Now do I have to use this object instance as a prefix in front of all cell references? What about functions like "LEN" or "MID"?

And how does one get rid of the specific REFERENCE to the OBJECT library. Every time I try Excel refuses and says the Microsoft 9.x Object library is required and in use.

 

[RobBroekhuis]

This depends on how many times you need to make this transition. I bet if you export the VBA components from your existing workbook, and import them into a newly created workbook (in XP), everything works fine. You can even automate this by macros, but it becomes cumbersome as a routine means of distributing your application.
Does that help?


Rob

 

[rdodge]

It sounds like you are in Excel VBA, and if that is the case, then it is true, you can not get rid of the Excel object library reference.

However, if you are attempting to create an Excel object reference via code within another application, what you can try to use is the following:

Dim XLApp as Object
On Error Resume Next
Set XLApp = GetObject(,"Excel.Application&quot
If Err.Number<>0 Then
Err.Clear
Set XLApp = CreateObject(&quot;Excel.Application&quot
End If
On Error Goto 0

This code would normally be ran in an application other than Excel VBA, such as Access VBA. It first attempts to get a reference to a currently openned Excel file, but if there is no Excel application open, it will then open a new instance of it.

If this is an Excel file that you are attempting to open, but it fails due to something that requires Excel 2002, try having one of the other users who has Office XP to find out what has been included in the file that is in XP, but not in 2000.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[rdodge]

Good one Rob, I forgot about using that part of VBA. I had to do that between 97 and 2000, but that was due to a compilation issue since Excel 2000 compiles very differently from Excel 97. I haven't known of Excel 2000 and Excel 2002 to compile any differently, which is probably why I didn't think about this export/import route.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[dean12]

Well, if I work under Excel XP and then send to a user with Excel 2000, they have to ALT-F11 and go to TOOLS --> REFERENCES and uncheck the missing library and check the one they have.

Tedious - hard to believe there isn't some better solution to this - Seems awfully stupid on Microsoft's part.