VBA.Left

  • Thread starter Thread starter John Wilson
  • Start date Start date
J

John Wilson

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
 
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.
 
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
 
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
 
Dennis,
I have run into that very problem myself
Click to expand...
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.
Click to expand...
That does make sense.
changing Left( ) to VBA.Left( ),
and similarly with Chr( ), Mid( ), Right( ), Date, Space( ), etc.
Click to expand...
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
 
Tom,
run Rob Bovey's code cleaner on the file
Click to expand...
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
 
Can you send me the workbook. I would like to play with it.

(e-mail address removed)

if it is too sensitive, then never mind.

Also, what version of Excel are you using?
 
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
 
Back
Top