Macros do not run when spreadsheet is used on a different computer

[Hubert]

I created spreadsheets with multiple Macros. When I transfer the spreadsheets
to other computers sometimes the Macros do simple not run at all or the
debugger comes on or create error messages or even more confusing some Macros
in the same spreadsheet run no problem. Some users had Excel crashing while
trying to run the Macros.

The most common error message is:
Run-time error 32809 Application-defined or object-defined error

The debugger comes on i. e. on commands like:
Worksheets("Sheet 1").ShowAllData
For Counter = 1 To 90
Orientation:=xlTopToBottom
The Calendar Control 11.0 I use with some Macros also seems not to be
recognized by other users.


The Macros and userforms are attached to the spreadsheet and present when I
transfer the spreadsheets.

The computers I transfer the spreadsheets to are similiar to mine both in
hardware and software as well as version. We run Windows XP and Excel 2003.
It does not seem to be a simple issue like setting security levels etc. I
noticed that other users do not automatically update their Windows/Excel
software and suspect that libraries are missing or some VBA commands are not
recognised by their VBA software. Anyone who can help me to identify the
cause for the problems and has solutions a big "Thanks".

 

[JP]

Hubert,

1. Shouldn't it be "Worksheets("Sheet1").ShowAllData" or do you
actually have a sheet named "Sheet <space> 1" ? Also note that the
worksheet has to be currently filtered using the AutoFilter command.

2. Is "Counter" Dimmed as Long/Integer?


HTH,
JP

 

[Hubert]

Hi JP,

Thanks for your response.

1. The worksheet reference is valid. The show all data is actually part of
an if loop checking first if a filter is on before executing.
2. The Dim is declared as a Range

The more research I do the more it looks like the code in itself is not the
issue at least if it is run on the machine the code was created.

I. e. the calendar control I use seems to be loaded into the VBA library
used by Excel when you load MS Access. So anyone not running MS-Office Pro or
does not use Access on his/her machine has no valid reference and so the
calendar object returns errors or plainly does not work. Testing on various
machines on the same platform of Excel 2003 it seems depending at date of
purchase and install different versions of libraries are used severly
impairing the transportability of Macros from machine to machine even if they
run the same version of Excel.

Does anyone know how and where to update libraries for Excel 2003 to the
latest version in order to establish at least a common platform within the
same version of Excel?

 

[JP]

If I am not mistaken, the object library for Excel is the actual
excel.exe executable. At least, that is how it is for me (I am using
2003 as well).


HTH,
JP

 

[Gord Dibben]

This will probably not help you very much but...............

The Calendar Control mscal.ocx is not installed without Access.

I don't believe there are any updates for Libraries for those who do not have
Access installed

You can download a copy from here.

http://www.fontstuff.com/vba/vbatut07.htm

See notes on registering etc.


Gord Dibben MS Excel MVP

 

[JP]

Also, for what it's worth, I disagree with you on this point:

The more research I do the more it looks like the code in itself is not the
issue at least if it is run on the machine the code was created.

If the code is so machine-specific that it won't even run elsewhere,
then you are writing it in a way that is not portable and probably
makes a lot of assumptions (which of course you can control on your
own machine).


HTH,
JP

 

[Hubert]

Thanks Gord, downloading and registering the calendar control on the users
machines actually resolved the issues around the transportability of the
macros as it relates to the calendar function.

If I do not get anymore info regarding updating VBA library versions and
controls
I guess I have to resign myself to redo the Macros on the machine with the
lowest update versions of VBA and no Access installed and hope for the best.

Thanks
Hubert