1

I'm having an issue with tool I developed in excel for one of our offices. It is a big enough file with couple of macros in it, but it works smooth day to day.

However every few days file will through error 'Can't find project or library' and file will crash. I know the standard guides are that when this error appears, it is to go to vba>tools>referneces and uncheck the missing library, however this is not the case in here. Simply when this happens file crashes and restarts and if it will happen once, every other attempt to open back the file will result in same error in crash. I mean file can be absolutely fine, you will save it, go back to it and error happens. So I always have to recover the file which absolutely destroys it, however I can at least recover from it information uploaded there by the users and copy it into the template.

So 2 questions I have is what is causing this to happen? Both myself and the other office are using the same version of excel so compatibility should not be an issue.

2 questions is is there anything that can be done to prevent this error from happening.

Damian M
  • 11
  • 1
  • 1
  • 3
  • https://stackoverflow.com/questions/54559743/excel-vba-cant-find-project-or-library/60075328#60075328 Scroll to Greg Dodds answer - this works! – Alan Marsh Aug 10 '20 at 15:47

3 Answers3

2

I have exactly this problem. As stated, once the error occurs, it it results in a permanent "unfixable" loop. I use powerquery and linked data and this appears to randomly "damage" the file as described - most frequently when excel crashes for some reason.

I have found either of the following to work: - Open the file on a different computer that is not on the LAN. This appears to be a key requirement. - Open the file with "Excel Online"

In each case, simply open the file and save it with a new name. Move the new file back to the work PC and it will once more open perfectly.

On rare occasions (if powerquery is in use), it is necessary to "refresh all" data connections before saving the new file.

As a bonus, the new file is often smaller than the original.

user10477032
  • 21
  • 2
  • When all else failed, this worked for me. I opened my workbook in Excel online (Office 365, in the browser, which doesn't support macros anyway), saved it with a new file name (still using .xlsm file extension), and reopened in the desktop software. It worked. – Sean McCarthy Mar 09 '20 at 15:14
0

This worked for me:

In VB go to Tools » References 1) Uncheck the library "Crystal Analysis Common Controls 1.0". Or any library. Just leave these 5 references: 1) Visual Basic For Applications (This is the library that defines the VBA language.) 2) Microsoft Excel Object Library (This defines all of the elements of Excel.) 3) OLE Automation (This specifies the types for linking and embedding documents and for automation of other applications and the "plumbing" of the COM system that Excel uses to communicate with the outside world.) 4) Microsoft Office (This defines things that are common to all Office programs such as Command Bars and Command Bar controls.) 5) Microsoft Forms 2.0 This is required if you are using a User Form. This library defines things like the user form and the controls that you can place on a form. Then Save.

nishit dey
  • 458
  • 1
  • 7
  • 21
  • Did this stop this error from happening? I have no 'Crystal Analysis Common Controls 1.0' selected and all others that are selected are same as your, except the last one but not using user forms. – Damian M Aug 09 '17 at 15:00
0

I've had similar nasty issues.

First thing to do is use the CodeCleaner a free utility from AppsPro This will export your modules and then re-import them, because internally they get a lot of binary "lint" which can cause problem.

Second thing to suggest is start breaking up your code base. So start removing modules to see which module is the offender. Horrible I know but how can you tell otherwise where the problem is.

Third suggestion is to always fully qualify your functions. So instead of Len(sMyString) write VBA.Len(sMyString) that helps prevent false negative compile errors.

S Meaden
  • 8,050
  • 3
  • 34
  • 65
  • Hmmm.. I see.. the problem is however that tool might be working absolutely fine for few days and error will happen out of nowhere, I don't know what triggers it so can't really test it like that.. and what exactly is the benefit of exporting and reimporting my modules? Is this to be done after error happens or before? Because of after it won't be possible cause the file just crashes. – Damian M Aug 09 '17 at 15:12
  • From AppsPro link 'During the process of creating VBA programs a lot of junk code builds up in your files. If you don't clean your files periodically you will begin to experience strange problems caused by this extra baggage. Cleaning a project involves exporting the contents of all its VBComponents to text files, deleting the components and then importing the components back from the text files.' – S Meaden Aug 09 '17 at 16:04