Compile error when running a vba script.

[Guest]

I have created a macro to combine multiple files which works on my PC using
excel 2000. However, when others run the macro, they get the follwoing error
:

Comipile error in hidden module : Module 3

It makes no difference on the version of excel they are using , be it 2000
or XP. On further investigation, runnimg the procedure from the VB editor
the error highlights the "left" of the first left function used and gives the
error :

Compile error : can't find project or library

From the fact that the errors occurs on a variery of versions of excel
including 2000, I have concluded that the remedy lies in the installation of
the excel package ot the add ins installed. I have compared mine and another,
(who gets the error) but can find very little different.

Does any one have any ideas where to look to reslove the problem?

 

[Tom Ogilvy]

on a problematic machine, after the error, hit reset, then go into
tools=>references and see if you see any references Marked as MISSING. If
so this is the cause of your problem. I suspect you are not using this
reference in your code, so go back to your machine and remove it - or if you
are using it, you need to install the referenced file on the users machine
(or if it should already be there, find it using the browse button in the
reference dialog).

Note that references are specific to individual workbooks, so when you do
this make sure your workbook is the activeproject (in the project explorer
at least one of the items (sheet, module, etc) is selected. The references
dialog is specific to the active project.

 

[Guest]

Thanks for the suggestion. I have done as you suggested, and double checked
it, and was unable to find any missing references. In addition I have
checked the references that are ticked and they are the same on both machines

Any more suggetsions ?

 

[Tom Ogilvy]

I suspect the workbook with the problem is not the active PROJECT (active
project, not active workbook in Excel itself - these are not necessarily the
same) when you go into tools references. As I said, tools=>references
refers to the active Project and references are set at the workbook/project
level.

If you get the error
Compile error : can't find project or library

a missing reference is about the only thing that will cause that error.