Excel cannot enable references / missing functions

Question

I have a working excel workbook that has VBA code and macros to help in data input. I use a calendar to capture dates and read data out of cells. The application works 100% on my machine (application developed in 2007 but saved as a .XLS – 97/2000 for compatibility in the office). When I give the file to other people, the calendar control and the “TRIM()” function do not work. On my machine I can get to the references section but on all other machines in the office the “references” function is grayed out. …. Any idea why a common object like the calendar and a common function like trim() do not work on some machines

Answer 1

The calendar control has many different versions, you need to build it out of the version they have on their machine.

Answer 2

Are the references greyed-out in a new workbook on the other PCs? References are usually only greyed-out when code is running. The calendar control was (is?) notorious for not running on the various different releases, so various work-arounds have been developed, such as Allen Browne's calendar pop-up. Trim is part of Visual Basic for Applications and you will probably find that the function is fixed once you get rid of the missing Calendar control reference.

Answer 3

VBA will automatically update references to the newest version. Example: If I reference Office 10.0 and you have Office 12.0 on your machine the reference will automatically be updated from ten to twelve. But it does not downgrade them. So if you have a reference to Office 12.0 and deploy it on a machine that only has Office 10.0 then you code will break.

There are two ways around this. If you want to stay with early binding then you will need to obtain a copy of the oldest version of the library in use. Then reference that file.

The usual alternative would be to go to late binding. However this can be little more challenging with embedded controls.