VBA.Left
 

As long as it's slow tonight, I have a general question.

I have a workbook that worked fine for a long time.
I do sometimes open it in Excel 2000, 2002, and 2003.
One day, for whatever reason, any Left or Right functions
that I used in my VBA code suddenly started giving me errors??

I've since gone back and changed all the functions to
VBA.Left and VBA.Right and everything works fine.

Does anyone have an explanation as to why this might
happen??

Thanks,
John

VBA.Left
 

It is usually cause by a missing reference in the the VBE. (one or more
references will be shown as MISSING)

It is corrected by removing the reference or finding it.

Don't know if that is the case here or not.

--
Regards,
Tom Ogilvy

John Wilson wrote in message
...
As long as it's slow tonight, I have a general question.

I have a workbook that worked fine for a long time.
I do sometimes open it in Excel 2000, 2002, and 2003.
One day, for whatever reason, any Left or Right functions
that I used in my VBA code suddenly started giving me errors??

I've since gone back and changed all the functions to
VBA.Left and VBA.Right and everything works fine.

Does anyone have an explanation as to why this might
happen??

Thanks,
John

VBA.Left
 

Tom,

The error occurs on the Compile.
Just tried it again (removed the "VBA." from the VBA.Left)
Says "Wrong number of arguments or invalid property assignment"
Checked the References. Nothing "Missing"
Put the "VBA." back in and it compiled fine.

My code is working and I've made it a habit to use "VBA."
when using these functions but I'm still perplexed about the "why".

Thanks,
John

"Tom Ogilvy" wrote in message
...
It is usually cause by a missing reference in the the VBE. (one or more
references will be shown as MISSING)

It is corrected by removing the reference or finding it.

Don't know if that is the case here or not.

--
Regards,
Tom Ogilvy

John Wilson wrote in message
...
As long as it's slow tonight, I have a general question.

I have a workbook that worked fine for a long time.
I do sometimes open it in Excel 2000, 2002, and 2003.
One day, for whatever reason, any Left or Right functions
that I used in my VBA code suddenly started giving me errors??

I've since gone back and changed all the functions to
VBA.Left and VBA.Right and everything works fine.

Does anyone have an explanation as to why this might
happen??

Thanks,
John

VBA.Left
 

John,

I have run into that very problem myself
from time to time as I installed Excel software with extensive VBA macros
on various computers. Tools-References
never showed any references as missing,
and code checking for a broken link
never revealed one either (see page 512
of Excel 2002 VBA by Bullen et al for the
technique). What invariable fixed the problem was doing a reinstall of Office
and if that didn't work of both Office and
the Windows operating system. (This did not win me friends, however, because of
the down-time required to effect this.)

When I read your message to the forum, I did a search of the MS Knowledge Base
and found nothing on the topic there. But when I did a search on Google with
keyword VBA.Left found several articles
on the subject. The most rational explanation advanced was that Excel may be
trying to extract the Left( )
and similar functions that may be contained in one of the other object
libraries listed under Tools-References, which is why writing VBA.Left( ) gets
you to the proper one. Now that i know this, I will do a search and replace of
all built-in functions in my Excel programs, changing Left( ) to VBA.Left( ),
and similarly with Chr( ), Mid( ), Right( ), Date, Space( ), etc.

-- Dennis Eisen

VBA.Left
 

Dennis,

I have run into that very problem myself
Was wondering if I was the only one that experienced this problem.

The most rational explanation advanced was that Excel may be
trying to extract the Left( )
and similar functions that may be contained in one of the other object
libraries listed under Tools-References, which is why writing VBA.Left( )
gets
you to the proper one.
That does make sense.

changing Left( ) to VBA.Left( ),
and similarly with Chr( ), Mid( ), Right( ), Date, Space( ), etc.
Did that already on all my workbooks.

Still at a loss as to why it suddenly became a requirement???
Obviously, something that I (and yourself) did, but what??
I have no clue.

Thanks for the explanation,
John

"DennisE" wrote in message
...
John,

I have run into that very problem myself
from time to time as I installed Excel software with extensive VBA macros
on various computers. Tools-References
never showed any references as missing,
and code checking for a broken link
never revealed one either (see page 512
of Excel 2002 VBA by Bullen et al for the
technique). What invariable fixed the problem was doing a reinstall of
Office
and if that didn't work of both Office and
the Windows operating system. (This did not win me friends, however,
because of
the down-time required to effect this.)

When I read your message to the forum, I did a search of the MS Knowledge
Base
and found nothing on the topic there. But when I did a search on Google
with
keyword VBA.Left found several articles
on the subject. The most rational explanation advanced was that Excel may
be
trying to extract the Left( )
and similar functions that may be contained in one of the other object
libraries listed under Tools-References, which is why writing VBA.Left( )
gets
you to the proper one. Now that i know this, I will do a search and
replace of
all built-in functions in my Excel programs, changing Left( ) to
VBA.Left( ),
and similarly with Chr( ), Mid( ), Right( ), Date, Space( ), etc.

-- Dennis Eisen

VBA.Left
 

Just for fun, run Rob Bovey's code cleaner on the file and see if it clears
up.

http://www.appspro.com

--
Regards,
Tom Ogilvy

John Wilson wrote in message
...
Dennis,

I have run into that very problem myself
Was wondering if I was the only one that experienced this problem.

The most rational explanation advanced was that Excel may be
trying to extract the Left( )
and similar functions that may be contained in one of the other object
libraries listed under Tools-References, which is why writing
VBA.Left( )
gets
you to the proper one.
That does make sense.

changing Left( ) to VBA.Left( ),
and similarly with Chr( ), Mid( ), Right( ), Date, Space( ), etc.
Did that already on all my workbooks.

Still at a loss as to why it suddenly became a requirement???
Obviously, something that I (and yourself) did, but what??
I have no clue.

Thanks for the explanation,
John

"DennisE" wrote in message
...
John,

I have run into that very problem myself
from time to time as I installed Excel software with extensive VBA
macros
on various computers. Tools-References
never showed any references as missing,
and code checking for a broken link
never revealed one either (see page 512
of Excel 2002 VBA by Bullen et al for the
technique). What invariable fixed the problem was doing a reinstall of
Office
and if that didn't work of both Office and
the Windows operating system. (This did not win me friends, however,
because of
the down-time required to effect this.)

When I read your message to the forum, I did a search of the MS
Knowledge
Base
and found nothing on the topic there. But when I did a search on Google
with
keyword VBA.Left found several articles
on the subject. The most rational explanation advanced was that Excel
may
be
trying to extract the Left( )
and similar functions that may be contained in one of the other object
libraries listed under Tools-References, which is why writing
VBA.Left( )
gets
you to the proper one. Now that i know this, I will do a search and
replace of
all built-in functions in my Excel programs, changing Left( ) to
VBA.Left( ),
and similarly with Chr( ), Mid( ), Right( ), Date, Space( ), etc.

-- Dennis Eisen

VBA.Left
 

Tom,

run Rob Bovey's code cleaner on the file
I use Rob's Code Cleaner religiously on a lot of my workbooks.
Tried it again with this one, just to be sure, and I still get the
compile error.

It's just strange that for years I've never had to preface any
of these and then one day, all of a sudden, I do.

Of course, if I could retrace my steps that fateful day, I'd know
because I'm certain it's something that I did or some program
that I loaded that cased it.

Oh well, I'll just have to religiously use the preface from now on.
Can't hurt.

Thanks again,
John

"Tom Ogilvy" wrote in message
...
Just for fun, run Rob Bovey's code cleaner on the file and see if it
clears
up.

http://www.appspro.com

--
Regards,
Tom Ogilvy

John Wilson wrote in message
...
Dennis,

I have run into that very problem myself
Was wondering if I was the only one that experienced this problem.

The most rational explanation advanced was that Excel may be
trying to extract the Left( )
and similar functions that may be contained in one of the other object
libraries listed under Tools-References, which is why writing
VBA.Left( )
gets
you to the proper one.
That does make sense.

changing Left( ) to VBA.Left( ),
and similarly with Chr( ), Mid( ), Right( ), Date, Space( ), etc.
Did that already on all my workbooks.

Still at a loss as to why it suddenly became a requirement???
Obviously, something that I (and yourself) did, but what??
I have no clue.

Thanks for the explanation,
John

"DennisE" wrote in message
...
John,

I have run into that very problem myself
from time to time as I installed Excel software with extensive VBA
macros
on various computers. Tools-References
never showed any references as missing,
and code checking for a broken link
never revealed one either (see page 512
of Excel 2002 VBA by Bullen et al for the
technique). What invariable fixed the problem was doing a reinstall of
Office
and if that didn't work of both Office and
the Windows operating system. (This did not win me friends, however,
because of
the down-time required to effect this.)

When I read your message to the forum, I did a search of the MS
Knowledge
Base
and found nothing on the topic there. But when I did a search on
Google
with
keyword VBA.Left found several articles
on the subject. The most rational explanation advanced was that Excel
may
be
trying to extract the Left( )
and similar functions that may be contained in one of the other object
libraries listed under Tools-References, which is why writing
VBA.Left( )
gets
you to the proper one. Now that i know this, I will do a search and
replace of
all built-in functions in my Excel programs, changing Left( ) to
VBA.Left( ),
and similarly with Chr( ), Mid( ), Right( ), Date, Space( ), etc.

-- Dennis Eisen

VBA.Left
 

Can you send me the workbook. I would like to play with it.



if it is too sensitive, then never mind.

Also, what version of Excel are you using?

--
Regards,
Tom Ogilvy

John Wilson wrote in message
...
Tom,

run Rob Bovey's code cleaner on the file
I use Rob's Code Cleaner religiously on a lot of my workbooks.
Tried it again with this one, just to be sure, and I still get the
compile error.

It's just strange that for years I've never had to preface any
of these and then one day, all of a sudden, I do.

Of course, if I could retrace my steps that fateful day, I'd know
because I'm certain it's something that I did or some program
that I loaded that cased it.

Oh well, I'll just have to religiously use the preface from now on.
Can't hurt.

Thanks again,
John

"Tom Ogilvy" wrote in message
...
Just for fun, run Rob Bovey's code cleaner on the file and see if it
clears
up.

http://www.appspro.com

--
Regards,
Tom Ogilvy

John Wilson wrote in message
...
Dennis,

I have run into that very problem myself
Was wondering if I was the only one that experienced this problem.

The most rational explanation advanced was that Excel may be
trying to extract the Left( )
and similar functions that may be contained in one of the other
object
libraries listed under Tools-References, which is why writing
VBA.Left( )
gets
you to the proper one.
That does make sense.

changing Left( ) to VBA.Left( ),
and similarly with Chr( ), Mid( ), Right( ), Date, Space( ), etc.
Did that already on all my workbooks.

Still at a loss as to why it suddenly became a requirement???
Obviously, something that I (and yourself) did, but what??
I have no clue.

Thanks for the explanation,
John

"DennisE" wrote in message
...
John,

I have run into that very problem myself
from time to time as I installed Excel software with extensive VBA
macros
on various computers. Tools-References
never showed any references as missing,
and code checking for a broken link
never revealed one either (see page 512
of Excel 2002 VBA by Bullen et al for the
technique). What invariable fixed the problem was doing a reinstall
of
Office
and if that didn't work of both Office and
the Windows operating system. (This did not win me friends, however,
because of
the down-time required to effect this.)

When I read your message to the forum, I did a search of the MS
Knowledge
Base
and found nothing on the topic there. But when I did a search on
Google
with
keyword VBA.Left found several articles
on the subject. The most rational explanation advanced was that
Excel
may
be
trying to extract the Left( )
and similar functions that may be contained in one of the other
object
libraries listed under Tools-References, which is why writing
VBA.Left( )
gets
you to the proper one. Now that i know this, I will do a search and
replace of
all built-in functions in my Excel programs, changing Left( ) to
VBA.Left( ),
and similarly with Chr( ), Mid( ), Right( ), Date, Space( ), etc.

-- Dennis Eisen

VBA.Left
 

Tom,

I'm using 2000.
It's not sensitive but I have to get rid of my protection scheme
and a few links before I forward it on.

Thanks,
John


"Tom Ogilvy" wrote in message
...
Can you send me the workbook. I would like to play with it.



if it is too sensitive, then never mind.

Also, what version of Excel are you using?

--
Regards,
Tom Ogilvy

John Wilson wrote in message
...
Tom,

run Rob Bovey's code cleaner on the file
I use Rob's Code Cleaner religiously on a lot of my workbooks.
Tried it again with this one, just to be sure, and I still get the
compile error.

It's just strange that for years I've never had to preface any
of these and then one day, all of a sudden, I do.

Of course, if I could retrace my steps that fateful day, I'd know
because I'm certain it's something that I did or some program
that I loaded that cased it.

Oh well, I'll just have to religiously use the preface from now on.
Can't hurt.

Thanks again,
John

"Tom Ogilvy" wrote in message
...
Just for fun, run Rob Bovey's code cleaner on the file and see if it
clears
up.

http://www.appspro.com

--
Regards,
Tom Ogilvy

John Wilson wrote in message
...
Dennis,

I have run into that very problem myself
Was wondering if I was the only one that experienced this problem.

The most rational explanation advanced was that Excel may be
trying to extract the Left( )
and similar functions that may be contained in one of the other
object
libraries listed under Tools-References, which is why writing
VBA.Left( )
gets
you to the proper one.
That does make sense.

changing Left( ) to VBA.Left( ),
and similarly with Chr( ), Mid( ), Right( ), Date, Space( ), etc.
Did that already on all my workbooks.

Still at a loss as to why it suddenly became a requirement???
Obviously, something that I (and yourself) did, but what??
I have no clue.

Thanks for the explanation,
John

"DennisE" wrote in message
...
John,

I have run into that very problem myself
from time to time as I installed Excel software with extensive VBA
macros
on various computers. Tools-References
never showed any references as missing,
and code checking for a broken link
never revealed one either (see page 512
of Excel 2002 VBA by Bullen et al for the
technique). What invariable fixed the problem was doing a
reinstall
of
Office
and if that didn't work of both Office and
the Windows operating system. (This did not win me friends,
however,
because of
the down-time required to effect this.)

When I read your message to the forum, I did a search of the MS
Knowledge
Base
and found nothing on the topic there. But when I did a search on
Google
with
keyword VBA.Left found several articles
on the subject. The most rational explanation advanced was that
Excel
may
be
trying to extract the Left( )
and similar functions that may be contained in one of the other
object
libraries listed under Tools-References, which is why writing
VBA.Left( )
gets
you to the proper one. Now that i know this, I will do a search
and
replace of
all built-in functions in my Excel programs, changing Left( ) to
VBA.Left( ),
and similarly with Chr( ), Mid( ), Right( ), Date, Space( ), etc.

-- Dennis Eisen