Excel VBA: Getting an error when using worksheet codename reference but not when using sheets("tab name")

Excel VBA: I get a compile error when using worksheet codename references in my workbook, but not when using sheets ("tab name"). Happens only if I have vba code associated with the worksheet in which I am referencing.  Happens only to SOME users but not all.  I had to switch over to using codename references, and as soon as I replaced all "sheets ("Main").xxx" with "sheet3.xxx" direct reference, SOME users are now getting "compile error: Object library invalid or contains references to object definitions that could not be found" and what the user sees is VBA IDE interface highlighting the first line of code which is associated with the worksheet that I am now using "sheet3.xxx" references on.  It's not specific line of code, but I find it's the fact that ANY code is associated with sheets that I am now referencing using direct codenames, and only SOME users.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
On the users where you get this error..did you went to tools-->references in vb editor to see for any missing library? If you see the library is missing and it's been used in the code then you need to install that software to get the library otherwise simply uncheck the missing one if you are not using them..

It will generally will start from missing and then the library name...

mizetreestump11Author Commented:
I will need a bit of time to check that with some users who are not computer savvy.  If the issue were occurring on my PC, it would be much easier.  I will let you know if that is the problem.
mizetreestump11Author Commented:
I've just checked myself, on a user's PC who is getting the compile error.  There are no references marked as "missing" on their PC.  Tools --> References, scrolled through the entire list, only 5 references marked, same on my PC (no error) as theirs, none indicate "missing" on their PC, which if I recall from another issue I was having, the word "missing" shows right in that list of references if they don't have it.  
     If I change all occurrences of "sheet6.xxx" to "sheets("MainList")." throughout my code in the spreadsheet (don't use sheet codenames), those users no longer have a compile error.  

Here's a bit of background:  This is a spreadsheet that I’ve turned into an application over time, now with 10’s of thousands of lines of code, hundreds of macros, user forms, now driven by user forms/GUIs.  I’ve had a couple of “growth problems” over the life of this spreadsheet application, and this the latest of them.  Here describes two earlier ones, maybe this will click a switch ‘ON’ with an idea:

First, I hit the 64k maximum limit for individual macros.  Had to keep trimming my code, breaking it into calls to smaller routines, from a main routine, to get around that.

Second, I began to hit an intermittent issue, similar to this one I am having now, where occasionally, but not always, it would crash at a line like this:  ‘sheets(“MyList”).Range(xxxx).xxx’ with an error:
"Run-time error 32809 Application-defined or object-defined error"
I knew nothing was “wrong” with the syntax of the line because it was intermittent.  After researching, I suspected that too many internal pointers or resources must be being used, and my solution was to convert ALL of my of the  expressions using RANGE - ‘sheets(“MyList”).Range(xxxx).xxx’ – use CELLS instead, such as ‘ sheets(“MyList”).cells(xx, xx).xxx’.  

This stopped all intermittent crashes on the “Range” commands.

Second, I was having what seems to be a similar issue where users, as well as myself, intermittently again, were seeing the SAME crash error above, but this time, the focus I think was on using the named tabs reference:


That line caused the Application-defined or object-defined error (Run Time 32809) intermittently.  
So what I did on this last set of changes, to fix this:  I began using sheet CODENAMES in place of the tabbed name in quotes:


Now, no longer ANYONE having any intermittent crashes when using the CODENAME references all throughout my macros.

But this is where, now, for some users, they are getting a the COMPILE error.  It’s EVERY TIME (not intermittent).  But, MOST of us are fine, no crash error ever, everything is good.  I enabled developer on some users PC’s who were getting this compile error.  When they click DEBUG on the popup VBA error, they see the first line of code highlighted which is behind the “MyList” worksheet.  Seems really odd that now that I am using sheet6. ..and referencing the same worksheet as an object, using codename, instead of by tabbed name:
As opposed to before
For the same exact worksheet, that always had the same macros, declarations, and subs on it, that suddenly NOW, only after using sheet codename references, some users are getting a COMPILE error, it’s not liking the fact that there is code associated with that worksheet.

Sorry for the long winded explanation, but I can’t help but believe that my problems may be resource based, and I may be reaching max internal issues on some user PC’s, with an error that may not be what it appears, and why I explained these other “growing pains” issues with the code that were similar.
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Saurabh Singh TeotiaCommented:
What i can make out from your reading comments is that eventually that workbook which you are using have become corrupted here and their and that's why it's giving you an error at odd times because had been your code wrong all the users have been experiencing it but now if not references issue then it's a system issue and their can be lot of reasons because of system the code gives me an error..

At a times a simple restart just fixes the issue in the code where it gave an earlier error on the same line because of the cache memory getting refreshed..Also it's always a good practice to specify the workbook name like sheets("Your sheet name here").Select or activate

Try using select and see does it still throw error at weird times or it goes through??


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
I agree, Saurahb726 provided the solution which I accept.  However I do not see any "accept as solution" button anywhere, so it looked like I abandoned.  OK to close, thank you.
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.