Can't find project or library error on Format function

[abberyfarm]

Hi,

I have this code in a macro and it works on my laptop.

 Randomize
        c = Format(rnd(1), "#0.00")
        c = Round(rnd, 2)

However, when I try run it on my desktop I get an error 'Can't find project or library' with the 'Format' word highlighted.

Could anybody tell me how to find out which library is missing and how I activate it?

Thank you

also posted here http://www.ozgrid.com/forum/showthread.php?t=176680&p=655767#post655767

 

[VoG]

Check for missing references Missing References In VBA

 

[abberyfarm]

Hello, I followed the instructions on that page and cannot find a missing reference, you wouldn't by any chance know which reference this function belongs to and what I should be looking for?

 

[RoryA]

You have to check it on the desktop machine, not the laptop, with that workbook active and you are looking for any checked reference that has 'MISSING:' at the start of it.

 

[VoG]

Afraid not. Does this work

c = VBA.Format(Rnd(1), "#0.00")

 

[abberyfarm]

That does work, thanks, they are different versions of excel 2007 and 2010,maybe that was it.

 

[mugginsjr]

I get the same "Can't find project or library" message when trying to put a formatted current date into a cell. The word Date is highlighted as the trouble maker:

Sheets("Report").Range("D6").Value = Format([COLOR="#40E0D0"]Date[/COLOR], "mm/dd/yy")

I also tried inserting "VBA." before "Format" but received the same error message. Any ideas on this? Does Excel 2013 have a different command for inserting the current date?

 

[Norie]

Have you checked for MISSING references under Tools>References...?

 

[mugginsjr]

I get the same "Can't find project or library" message when trying to put a formatted current date into a cell. The word Date is highlighted as the trouble maker:

Sheets("Report").Range("D6").Value = Format([COLOR=#40e0d0]Date[/COLOR], "mm/dd/yy")

I also tried inserting "VBA." before "Format" but received the same error message. Any ideas on this? Does Excel 2013 have a different command for inserting the current date?

OK, I discovered the problem above is that I must put "VBA." in front of both "Format" and also "Date" in order for it to work properly. (as shown below)

Sheets("Report").Range("D6").Value = [COLOR="#800000"]VBA.[/COLOR]Format([COLOR="#800000"]VBA.[/COLOR]Date, "mm/dd/yy")

I also noticed there are 4 different references to "Visual Basic for Applications" on the references list, 3 in addition to the one that is checked, but only one of them can be selected at a time. Could it be possible the wrong one is selected on this particular workbook?

The unchecked ones are:

Microsoft Visual Basic for Applications: ----> C:/windows/system32/msvvm50.dll
Microsoft Visual Basic for Applications: ----> C:/windows/system32/msvvm60.dll
Microsoft Visual Basic for Applications: ----> C:/windows/system32/VEN2232.OLB

The checked one also showed VEN2232.OLB. It would not allow me to uncheck it and gave me an error message "can not uncheck, currently in use." When I tried to add one of the others I got an error message "name conflict, too similar to other reference."

So............., I saved the work book under a temp name, exported all modules and forms, and deleted all code on the individual sheets. Then I saved and reopened, reimported all forms and modules, and replaced all codes on individual sheets on the temp workbook.

Checking the references on the temp WB now I see that the checked Visual Basic for Applications reference has a different location, C:/Program Files/Common Files/Microsoft shared/VBA/VBA7.1/ ----- And that's where it stops. The location and filename are too long to be displayed entirely on the pop-up box.

So now the script works well on the new temp WB without any error messages and without having to use the "VBA." prefix. I think they might have added something that possibly tries to detect which references are needed, but dkfs....

I love and applaud that MS is dedicated to constantly improving their products. Excel and VBA capabilities are the most ingenious programming I have ever seen. With that said, every time they update their programs it takes me time to re-write my code to become compliant - again. Thankfully this is the only bug I've seen with 2013 so far.

PS: I have about 30 other workbooks that all use the same "Format(Date, 'mm/dd/yyyy')" with no trouble. I still have not figured out why this one particular workbook was prone to this glitch.

 

[mugginsjr]

Have you checked for MISSING references under Tools>References...?

Thanks Norie. Yes.