I recently received an Excel file from internet. And I wanted to do some small modifications to it. It was macro enabled excel file. Then I opened the VBA editor and also checked the references. If you don’t know what references are, they are pointers to Type libraries or DLL files.
There was reference to office web components add-in. But it was not available on my system. So it was listed as a “MISSING” reference. When we copy excel file from one device to another, Type libraries and DLL files are not transferred with it. Only the pointer are transferred. This is the reason for missing references. However in this file, functionality related to that add-in was not needed anymore. So I removed that part from the code. But I didn’t remove the tick from that missing reference.
Next I ran the macro. Then I got a compile error.
When I click OK button it highlighted the code like this.
So it was highlighting a code related to Microsoft scripting runtime. But Microsoft scripting runtime was available in my system and there was no any error in this coding part or reference. Then I realized that this was occurred due to tick in the Missing reference. And I found that the program work without error, when I remove that tick from the missing library. So if you have a missing reference in your vba project then this can cause very strange compile errors like this.