yes, users without Excel will case code to break in your application - even code that has nothing to do with the Excel.
If you have references set and those objects are not available on the target machine then you do have a broken reference. And yes such a setup will break “general” code and functions in your application EVEN if you not running or trying to run the actual
code that works with Excel.
So the above tends to be a challenge + issue. If a reference is missing, then it tends (will most often) break general code. Again, EVEN code that has nothing to do with the external word or Excel application.
I also suggest that unless you have VERY robust error handling in your code that you ALWAYS distribute a compiled accDE as compared to an accDB. The reason for this is that a compiled accDE can continue to run correctly EVEN if the application encounters
an error. If you use an accDB and an error occurs the runtime will shut down (full version does not). So while a compiled accDE is VERY resilient to errors (all local and even global VBA variables NEVER get re-set – and this includes when errors occur). And
as noted the application will keep running when you distribute an accDE.
With an accDB, ANY SINGLE un-handled errors not only re-set and blow out all VBA variables (and thus cause issues with your application), you also find that the runtime will after displaying the error THEN shutdown. So runtime accDB will shut down with
one little error, but an accDE NEVER shutdown to un-handled errors – the result is a FAR more reliable application and one that works well even if you have limited error handling code.
Now without question one might have word, or outlook or Excel bits and parts in your application that makes use of these external applications. However, if such applications like excel or word are being used by your application and you set a reference? If
these target applications are not installed, then you have a broken reference.
The solution is quite simple in the above. You have to convert your code from what is called “early” binding (you have reference to those external applications) to “late” binding in which you ALWAYS remove refinances to those external programs.
In most cases the change from early to late binding is rather minor and simple. If your goal is to have your application run without “always” having those target applications then you MUST use late binding else your VBA code will experience errors and break
– this EVEN includes code in your application that does not even use the external program(s) and has ZERO to do with the external application. (read this paragraph carefully again if you must to grasp this issue).
Of course the other reason why your references can break is that the path name and location of 2016 programs is different then 2010. While in the past Access could “guess” and would automatic change the references for you, the other issue is office 2016
is now OFTEN what is called CTR (click to run). So CTR version of Access have a slightly different location and install path then the full “MSI” version.
Converting your code to late binding – how?
I recommended when you are actually working on the code to use early binding, and then right before you compile to a accDE to prepare for distribution you change that code to late binding and test it a few times to ensure it works.
Typical early binding code of word (but same for Excel) will look like:
Dim myWord As New Word.Application ' early bind
Code follows to use MyWord object such as:
myWord.Documents.Open ("c:\MyWordDocs\test.docx")
To change above to late binding, you use this code:
'Dim myWord As New Word.Application ' early bind
Dim myWord As Object ' late bind
Set myWord = CreateObject("Word.Application")
myWord.Documents.Open ("c:\MyWordDocs\test.docx")
You then REMOVE the reference to word in the VBA->tools->references.
Once you removed the reference to word, you will find that your word (or excel or whatever) code will continue to run and MOST important is that your VBA compiles.
At this point you then:
Test if your code compiles (VBA editor->debug->compile
After you both tested your code, ensue it compiles, you THEN compile the application to the accDE and this is then used on the machines with runtime.
Note that you don’t necessary have to use a compiled accDE, but as noted the advantages as per above are significant.
When you have to make “extensive” modification to the actual code that uses word, or say excel from your application, then your comment out the above “dim” statements and un-comment out the original dim statement with early binding, set a reference to word,
and then now develop + make changes to your word code. When done developing you flip the code back to late binding (it only takes a few seconds of your time to do this). Note that for “general” code changes or very “minor” changes to your VBA code that
uses the external application you likely don’t even have to change back to early binding. However the “main” advantage of using early binding is that:
You get inteli-sense in the VBA editor (that is the drop down and suggestions of the word/excel properties and methods as you type).
You get compile errors when you choose/use a method not suggested by the VBA editor.
When you flip the application to late binding then you lose BOTH of the above two main features of early binding.
Regards,
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
