“无法找到项目或库”的标准 VBA 函数

所以我不得不在我的电脑上运行其他人的 Excel 应用程序，而且我在诸如日期、格式、十六进制、中间等标准函数上得到了“无法找到项目或库”。

一些研究表明，如果我在这些函数前面加上“ VBA”，比如“ VBA.Date”，那么它就会工作得很好。

网页建议它必须与我的项目参考我的系统，而他们必须是确定的开发人员的系统。我将在一段时间内从其他人那里处理这个问题，并将这些应用程序分发给许多其他人，所以我需要了解我需要修复的 Excel 设置出了什么问题，或者需要在 xls 文件中修改哪些内容，以便它能在各种系统上运行。我不想让每个人都用“ VBA”作为一个明确的参考，但是如果没有理想的解，我想这就是我们要做的。

  • 如何在项目属性/引用/等中隐含“ VBA”？

亚当

483348 次浏览
小开
最佳答案

I have seen errors on standard functions if there was a reference to a totally different library missing.

In the VBA editor launch the Compile command from the menu and then check the References dialog to see if there is anything missing and if so try to add these libraries.

In general it seems to be good practice to compile the complete VBA code and then saving the document before distribution.

小开

I have experienced this exact problem and found, on the users machine, one of the libraries I depended on was marked as "MISSING" in the references dialog. In that case it was some office font library that was available in my version of Office 2007, but not on the client desktop.

The error you get is a complete red herring (as pointed out by divo).

Fortunately I wasn't using anything from the library, so I was able to remove it from the XLA references entirely. I guess, an extension of divo' suggested best practice would be for testing to check the XLA on all the target Office versions (not a bad idea in any case).

小开

In my case, it was that the function was AMBIGUOUS as it was defined in the VBA library (present in my references), and also in the Microsoft Office Object Library (also present). I removed the Microsoft Office Object Library, and voila! No need to use the VBA. prefix.

小开

I had the same problem. This worked for me:

  • In VB go to Tools » References
  • 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.
小开

In my case I was checking work done on my office computer (with Visio installed) at home (no Visio). Even though VBA appeared to be getting hung up on simple default functions, the problem was that I had references to the Visio libraries still active.

小开

I found references to an AVAYA/CMS programme file? Totally random, this was in MS Access, nothing to do with AVAYA. I do have AVAYA on my PC, and others don't, so this explains why it worked on my machine and not others - but not how Access got linked to AVAYA. Anyway - I just unchecked the reference and that seems to have fixed the problem

小开

In my case, I could not even open "References" in the Visual Basic window. I even tried reinstalling Office 365 and that didn't work. Finally, I tried disabling macros in the "Trust Center" settings. When I restarted Excel, I got the warning message that macros were disabled, and when I clicked on "enable" I no longer got the error message.

Later I re-enabled all macros in the "Trust Center" settings, and the error message didn't show up!

Hey, if nothing else works for you, try the above; it worked for me! :)

Update: The issue returned, and this is how I "fixed" it the second time:

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.

小开

I've had this error on and off for around two years in a several XLSM files (which is most annoying as when it occurs there is nothing wrong with the file! - I suspect orphaned Excel processes are part of the problem)

The most efficient solution I had found has been to use Python with oletools https://github.com/decalage2/oletools/wiki/Install and extract the VBA code all the modules and save in a text file.

Then I simply rename the file to zip file (backup just in case!), open up this zip file and delete the xl/vbaProject.bin file. Rename back to XLSX and should be good to go.

Copy in the saved VBA code (which will need cleaning of line breaks, comments and other stuff. Will also need to add in missing libraries.

This has saved me when other methods haven't.

YMMV.

小开

Even when all references are fine the prefix problem causes compile errors.

What about creating a find and replace sub for all 'built-in VBA functions' in all modules, like this:

replace text in code module

e.g. "= Date" will be replaced with "= VBA.Date".

e.g. " Date(" will be replaced with " VBA.Date(" .

(excluding "dim t As Date" or "mydate")

All vba functions for find and replace are written here :

vba functions list

小开

For those of you who haven't found any of the other answers work for you.

Try this:

Close out of the file, email it to yourself or if you're at work, paste it from the network drive to your desktop, anything to get it to open in "protected mode".

Now open the file

DON'T CLICK ANY ENABLE EDITING OR THE YELLOW RIBBON

Go to the VBA Editor

Go to Debug - - Compile VBA Project, if "Compile VBA Project" is greyed out, then you may need to click the yellow ribbon one time to enable the content, but DO NOT enable macros.

After you click Compile, save, close out of the file. Reopen it, enable everything and it should be OK. This has worked for me 100% of the time.


提交答案