[RESOLVED] Compilation Error...

[pgag45]

hey All,

so I have a project that is working perfect on the computer I am developing... However, on some computers, with the same version of Excel, they are getting compilation errors with lines of code such as

.Format or .Mid ... It was easy enough to fix by just putting a

VBA.Format or VBA.Mid before the method, but why do some computers need this .VBA and some don't?

Am I not including a reference that I need to be? Or is it just best style to put .VBA in front of these methods??

Thanks much,

PG

[westconn1]

from what i have seen, usually this happens when something is not available on the users computer that is referenced in the original, check what references are checked on your computer and if any are missing on computers where there is a problem

[anhn]

Format(), Mid(), Left(), Right(), ... are core functions of VB/VBA. The reason it comes up with a misleading error message on these functions is at least one of your references (that exists on your PC and not related to those functions) is missing on other PCs.

1. From your PC, uncheck all references that no need for the project. If you don't know which ones, just uncheck each of them to see what happens (if it is required, your project will complain when you uncheck it or when you compile the project). After doing that, you can make sure all remain references in the list are minimum requirement.

2. On other PCs, if you still have that error, check the references list to see any reference comes with the word "MISSING", that is the marker of missing references, they need to be installed or registered on those PCs.

You can do step 2 first to find out the culprits first if you want.

[pgag45]

great thanks for the help! I will try this tomorrow..
is there anyway to do step 2 programmatically?

As in, during the start up of the program, enable all necessary references that aren't enabled in that particular computer?

thanks again

[anhn]

As in, during the start up of the program, enable all necessary references that aren't enabled in that particular computer?

No need to do this if all required references are available on those PCs.
On the other hand, if a required reference is missing then you cannot enable it.

great thanks for the help! I will try this tomorrow..
is there anyway to do step 2 programmatically?

Yes, but it is quite complicated. If you really want to do it you can search the forums or MSDN.
If your project is not "fancy" (use external objects) then no need to do that, it will be fine after you do step 1.

[pgag45]

Ok... so I need all the references that I have in my project.

Why, would something as basic as VBA reference not be enabled on someone else's computer?

When they get a copy of my workbook, the references don't automatically match what I have set in mine?

[si_the_geek]

References are links to external programs (EXE's/DLL's/etc), and those external programs are not imported into your project (which is good, as some of them are hundreds of megabytes, and other reasons too).

In order for References to work, the relevant external program (which may be just a single file, or a whole package) needs to be installed on that computer.

How you could/should install them depends on what References you use in your project.

[pgag45]

They're all basic references, mostly all Microsoft, that come in the installation of Excel...

so you're saying, that if the reference isn't enabled on someone else's computer, it's because what I'm trying to link to doesn't exist?

[si_the_geek]

so you're saying, that if the reference isn't enabled on someone else's computer, it's because what I'm trying to link to doesn't exist?

Pretty much - if the References window shows "Missing", that item (or at least the particular version of it that you selected) is not installed.

They're all basic references, mostly all Microsoft, that come in the installation of Excel...

Unless the References are to Excel itself, they were probably not installed by Excel - the References window shows all of the available items that are installed on the computer, no matter how they got installed (by Windows, a program you installed, etc).

If you tell us what References you are using (and which of those are causing problems), we can help you work out what to do.

[pgag45]

Well I installed my workbook on someone else's computer and it didn't have Visual Basic for Applications installed, I assume.

because, they complained about the compilation error. I went to their computer and debug and it was crashing on Mid or Format (functions of VBA). So I guess instead of checking the reference, I just threw VBA in front of those references to make a quick fix. Why would they not have Visual Basic for Applications reference?

[si_the_geek]

After being involved in literally hundreds of threads like this one, I am almost completely certain that they have got VBA, and there is very little chance that it is in any way the cause of the problem - but instead just a symptom.

The error messages you are dealing with at the moment are misleading due to the horrendous complexity of a complier, and can easily be given for something else rather than VBA (if you want, I can explain how to easily make it happen for a completely different library instead).

There was a good explanation in post #3, and there is a longer version here from our FAQs (while that article is for VB rather than VBA, the only difference is how you get to the References window).


So far, 3 of us have tried to explain to you that you should be dealing with any Missing references you may have (which in most cases takes less than a minute, but possibly more here), yet you seem unwilling to do that. Note that if you have got any Missing references, anything else you do will be a waste of time - you will still need to fix the references to get things working properly.

Out of all of the threads on this topic, the only ones that haven't been solved are where people have taken your approach.. while I can understand the confusion of one thing giving an error due to something unrelated, please don't let this be another thread that fails, and instead tell us:

• what References are you using,
• which ones are marked as Missing?

[pgag45]

Ok... I myself, have no missing references. On my machine this compiles fine, and runs fine.

I gave this workbook to someone else, and they were getting compilation errors after hitting certain methods... i assume because compile on demand is activated. they were crashing on lines such as Mid or Format, I assume, because VBA reference was not activated, yet it is activated on my machine. to fix this, I went back to my machine and put

VBA.Format() or w/e, and gave them the workbook back, it worked fine... so I explicity called the VBA method, instead of perhaps going on to their machine and activating the reference.

The problem right now is fixed... but I'm more concerned about the future. When i start distributing this, I can't always be on their computer, so I wanted to know if I could programatically check to say if a reference I am using is on theirs as well. I'm using references such as DAO or ADO, which I just want to ensure are enabled on a user's machine as well.

[si_the_geek]

Ok... I myself, have no missing references. On my machine this compiles fine, and runs fine.
...

We have been well aware of that since the start - and all of our suggestions are appropriate for it.

I'm using references such as DAO or ADO, which I just want to ensure are enabled on a user's machine as well.

Rather than "such as DAO or ADO", please (please!!! ) give us a detailed list of what is ticked in the References list - and if possible, check the references screen on the other computer to see which ones are listed as "Missing".


We can help you sort this out properly, but only if you let us.

[pgag45]

As I said.

They were missing Visual Basic for Applications Reference.

[si_the_geek]

Wow, this is the first time I have ever known that to happen!

Is there any chance you could you post a screenshot of the References screen? (I like keeping little reminders of odd situations)


By the way, you hadn't actually said that before - the closest you had got was "I assume".

The only certain things you had said before was that you were getting "compilation errors" on things like Format, and it was fixed by using VBA.Format (which as we have been trying to say, is actually very misleading, and is almost always caused by something else being missing).

[anhn]

You are so patient, Si. I gave up. Let than 1 minute to fix rather than many hours. Don't waste to much time.
If Mid() fails and VBA.Mid() works that proves that VBA library is NOT missing.

[si_the_geek]

Yep, but unfortunately this error does cause that - and understandably so, as the way it is handled by VB/VBA is terribly misleading (it should show the References screen rather than highlighting any code).

I know the check we asked for usually takes only a few seconds, but it will be longer in this case as it is happening on a different computer.


pgag45, I know that you have just marked this thread as Resolved (without getting what you want), but we can fix this with minimal effort from you.

Please just humour us, and post a screenshot of the References screen from the other computer.

We will then either be able to fix this in very few posts (it usually takes just 2 from that point), or change our focus to whatever the actual problem is in this case.


However it will be solved, we do actually need to know the full list of References that you have got ticked.