TRIM Function gives Compiler Error!!!!

[feltra]

Hi,

I am facing a curious problem where, seemingly, Trim() function gives
Compile Error: Object/Library not found. Following is a synopsis:


Code and defenitions:
a. Defined a form with name "ReportForm". It has two Multipages
"Main" and "BIN Values".

b. Upon clicking a CommandButton in the form, the code (for the
buttonclick) copies the form variables into program variables and
calls a procedure DoNewTicketFile() [that takes no parameters]

c. I have defined the code for this procedure in a separate module
called "CodeModule1".


What happens at run time:
The execution stops with a "Compile error: Object or Library not
found", yellow-highlighting the procedure defenition statement [ie.
Sub DoNewTicketFile{} ]. And down in the code, about a couple of
Set worksheet statements later, there is an IF statement that checks
the Trim value of a cell. This Trim is higlighted in blue. Am I
right in thinking that VBA cannot find this Trim function in the
library?? (incredulous as it seems)?


Other behavior:
1. A simple test for Trim (such as ?Trim(" abcd ") in the Immediate
window also gives error!

2. On loading some other application (a previous version of this
application, without the userform), the Immediate window is able to
execute the Trim statement.

3. Strangest thing: I developed the code for my friend. It runs ok
on my PC. But it is giving error on my friends PC. He says that
there has been no installation changes in Excel.

4. The above problem description are taken from behavior observed on
my friends PC.



The question(s):
A. Has anybody ever faced this kind of problem? What did you have to
do to eliminate it?

B. I am stumped as where to look and what to do. I can only think of
brute force things which are not gauranteed to work - such as giving
all the code in the code module for the form (instead of a new
module). Even as i say it, that sounds so dense...

C. Any ideas / pointers / hints as what to do, how to nail the exact
problem (which library is missing or has duplicate names bad enough
for VBA to confuse such a basic thing as Trim() function), websites
etc, will be very very very helpful.


Extra Info:
a) Both "Analysis ToolPak" and "Analysis ToolPak-VBA" Add-ons are
switched on in both the PCs.
b) The Excel version is 2002. (Office XP 2002, running on Windows XP)


Advance thanks for any & all inputs.

Best Regards,
-feltra ([email protected])

 

[Dave Peterson]

With that project active, click on Tools|References.

Look for a reference marked MISSING. You'll have to decide if you need to
replace it or just uncheck it.

The line marked in error may not (will probably not!) have anything to do with
the missing reference.

Hi,

I am facing a curious problem where, seemingly, Trim() function gives
Compile Error: Object/Library not found. Following is a synopsis:

Code and defenitions:
a. Defined a form with name "ReportForm". It has two Multipages
"Main" and "BIN Values".

b. Upon clicking a CommandButton in the form, the code (for the
buttonclick) copies the form variables into program variables and
calls a procedure DoNewTicketFile() [that takes no parameters]

c. I have defined the code for this procedure in a separate module
called "CodeModule1".

What happens at run time:
The execution stops with a "Compile error: Object or Library not
found", yellow-highlighting the procedure defenition statement [ie.
Sub DoNewTicketFile{} ]. And down in the code, about a couple of
Set worksheet statements later, there is an IF statement that checks
the Trim value of a cell. This Trim is higlighted in blue. Am I
right in thinking that VBA cannot find this Trim function in the
library?? (incredulous as it seems)?

Other behavior:
1. A simple test for Trim (such as ?Trim(" abcd ") in the Immediate
window also gives error!

2. On loading some other application (a previous version of this
application, without the userform), the Immediate window is able to
execute the Trim statement.

3. Strangest thing: I developed the code for my friend. It runs ok
on my PC. But it is giving error on my friends PC. He says that
there has been no installation changes in Excel.

4. The above problem description are taken from behavior observed on
my friends PC.

The question(s):
A. Has anybody ever faced this kind of problem? What did you have to
do to eliminate it?

B. I am stumped as where to look and what to do. I can only think of
brute force things which are not gauranteed to work - such as giving
all the code in the code module for the form (instead of a new
module). Even as i say it, that sounds so dense...

C. Any ideas / pointers / hints as what to do, how to nail the exact
problem (which library is missing or has duplicate names bad enough
for VBA to confuse such a basic thing as Trim() function), websites
etc, will be very very very helpful.

Extra Info:
a) Both "Analysis ToolPak" and "Analysis ToolPak-VBA" Add-ons are
switched on in both the PCs.
b) The Excel version is 2002. (Office XP 2002, running on Windows XP)

Advance thanks for any & all inputs.

Best Regards,
-feltra ([email protected])

 

[Helmut Weber]

Hi Dave,

it seems, string-functions
are the canaries in the coal mines.

--

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Vista Small Business, Office XP

 

[feltra]

Hi Dave,

Thanks for the fast response. I am relieved to see that you also
think this is a spurious error... Will ask my friend to try that out
on his PC.

Will update as soon as i hear from my friend.

Thanks a lot & Warm Regards,
-feltra

With that project active, click on Tools|References.

Look for a reference marked MISSING. You'll have to decide if you need to
replace it or just uncheck it.

The line marked in error may not (will probably not!) have anything to do with
the missing reference.

Hi,

I am facing a curious problem where, seemingly, Trim() function gives
Compile Error: Object/Library not found. Following is a synopsis:

Code and defenitions:
a. Defined a form with name "ReportForm". It has two Multipages
"Main" and "BIN Values".

b. Upon clicking a CommandButton in the form, the code (for the
buttonclick) copies the form variables into program variables and
calls a procedure DoNewTicketFile() [that takes no parameters]

c. I have defined the code for this procedure in a separate module
called "CodeModule1".

What happens at run time:
The execution stops with a "Compile error: Object or Library not
found", yellow-highlighting the procedure defenition statement [ie.
Sub DoNewTicketFile{} ]. And down in the code, about a couple of
Set worksheet statements later, there is an IF statement that checks
the Trim value of a cell. This Trim is higlighted in blue. Am I
right in thinking that VBA cannot find this Trim function in the
library?? (incredulous as it seems)?

Other behavior:
1. A simple test for Trim (such as ?Trim(" abcd ") in the Immediate
window also gives error!

2. On loading some other application (a previous version of this
application, without the userform), the Immediate window is able to
execute the Trim statement.

3. Strangest thing: I developed the code for my friend. It runs ok
on my PC. But it is giving error on my friends PC. He says that
there has been no installation changes in Excel.

4. The above problem description are taken from behavior observed on
my friends PC.

The question(s):
A. Has anybody ever faced this kind of problem? What did you have to
do to eliminate it?

B. I am stumped as where to look and what to do. I can only think of
brute force things which are not gauranteed to work - such as giving
all the code in the code module for the form (instead of a new
module). Even as i say it, that sounds so dense...

C. Any ideas / pointers / hints as what to do, how to nail the exact
problem (which library is missing or has duplicate names bad enough
for VBA to confuse such a basic thing as Trim() function), websites
etc, will be very very very helpful.

Extra Info:
a) Both "Analysis ToolPak" and "Analysis ToolPak-VBA" Add-ons are
switched on in both the PCs.
b) The Excel version is 2002. (Office XP 2002, running on Windows XP)

Advance thanks for any & all inputs.

Best Regards,
-feltra ([email protected])

 

[Dave Peterson]

Yeah.

I wonder why that is. More commonly used, maybe????

 

[Peter T]

- also DateTime functions

Regards,
Peter T

 

[feltra]

Hi Dave,

You are a gem! Yes, that was the problem. Two entries were listed
as MISSING and were checked on. In hindsight, these 2 were ActiveX
Controls and something called Control Shapes that I had clicked on
while playing around in forms. Although I would like to hide behind
"I am new to forms" I ought to have known better than to send an XLS
file to my friend with ActiveX Controls turned on.... Poor practice
on my part.

Once again, thank you so much for the timely help. He had a deadline
to meet and now thats ok.

Best Regards,
-feltra

With that project active, click on Tools|References.

Look for a reference marked MISSING. You'll have to decide if you need to
replace it or just uncheck it.

The line marked in error may not (will probably not!) have anything to do with
the missing reference.

Hi,

I am facing a curious problem where, seemingly, Trim() function gives
Compile Error: Object/Library not found. Following is a synopsis:

Code and defenitions:
a. Defined a form with name "ReportForm". It has two Multipages
"Main" and "BIN Values".

b. Upon clicking a CommandButton in the form, the code (for the
buttonclick) copies the form variables into program variables and
calls a procedure DoNewTicketFile() [that takes no parameters]

c. I have defined the code for this procedure in a separate module
called "CodeModule1".

What happens at run time:
The execution stops with a "Compile error: Object or Library not
found", yellow-highlighting the procedure defenition statement [ie.
Sub DoNewTicketFile{} ]. And down in the code, about a couple of
Set worksheet statements later, there is an IF statement that checks
the Trim value of a cell. This Trim is higlighted in blue. Am I
right in thinking that VBA cannot find this Trim function in the
library?? (incredulous as it seems)?

Other behavior:
1. A simple test for Trim (such as ?Trim(" abcd ") in the Immediate
window also gives error!

2. On loading some other application (a previous version of this
application, without the userform), the Immediate window is able to
execute the Trim statement.

3. Strangest thing: I developed the code for my friend. It runs ok
on my PC. But it is giving error on my friends PC. He says that
there has been no installation changes in Excel.

4. The above problem description are taken from behavior observed on
my friends PC.

The question(s):
A. Has anybody ever faced this kind of problem? What did you have to
do to eliminate it?

B. I am stumped as where to look and what to do. I can only think of
brute force things which are not gauranteed to work - such as giving
all the code in the code module for the form (instead of a new
module). Even as i say it, that sounds so dense...

C. Any ideas / pointers / hints as what to do, how to nail the exact
problem (which library is missing or has duplicate names bad enough
for VBA to confuse such a basic thing as Trim() function), websites
etc, will be very very very helpful.

Extra Info:
a) Both "Analysis ToolPak" and "Analysis ToolPak-VBA" Add-ons are
switched on in both the PCs.
b) The Excel version is 2002. (Office XP 2002, running on Windows XP)

Advance thanks for any & all inputs.

Best Regards,
-feltra ([email protected])