+ Reply to Thread
Results 1 to 26 of 26

Bypass missing reference VBA error

  1. #1
    Registered User
    Join Date
    10-27-2014
    Location
    Lisbon
    MS-Off Ver
    2010
    Posts
    24

    Bypass missing reference VBA error

    Hello forum, this is my first post and I'm an excel newbie, so bear with me...

    I created a complex excel file to be shared by several users.

    It is using a special application by a third-party supplier that only certain users have it installed on their computers.

    This means that the ones that don't have the application installed get the "Compile error in hidden module: XXXX. This error commonly occurs when code is incompatible with the version, platform, or architecture of this application....." error.

    I know I can create a code to disable the missing reference and not have to go to each user and disable it manually on each computer. However, I want the reference to continue like this on these users, I want to be able to bypass this error, because it's not important that the users that get the error don't have access to this third-party application.

    I hope I've been completely clear on my problem and maybe someone could give me a hand on how to create a code to bypass this error. Essentially make it not appear, just that.

    I'm using excel 2010.

    Thanks!

  2. #2
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,009

    Re: Bypass missing reference VBA error

    Late binding the code is usually the simplest option since you then don't have a reference set at all.
    Everyone who confuses correlation and causation ends up dead.

  3. #3
    Registered User
    Join Date
    10-27-2014
    Location
    Lisbon
    MS-Off Ver
    2010
    Posts
    24

    Re: Bypass missing reference VBA error

    Thanks for the quick reply, but can you please elaborate on that? I've googled late binding however I don't think it'll work for me.

    I don't have any code for this reference so I can't really late bind it. The reference is a third-party application, it does not belong to the office suite or anything like that, it's a Nielsen add-in. There are a few sheets where I use this add-in and it doesn't require me to write any code whatsoever.

  4. #4
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,009

    Re: Bypass missing reference VBA error

    Why do you have a reference set if you don't use any code? Simply uncheck the reference and save the workbook.

  5. #5
    Registered User
    Join Date
    10-27-2014
    Location
    Lisbon
    MS-Off Ver
    2010
    Posts
    24

    Re: Bypass missing reference VBA error

    I can't uncheck the reference because the workbook is using it, and I want it to be used for every user who opens the file.

    However, there are users who don't have the Nielsen add-in installed on their computers so they get the error I described on the first post. It's ok for them to get this error because it's irrelevant for them to have the add-in or not, still I don't want them to uncheck the reference because then there are the users who have to use the add-in and then won't be able to access it without checking the reference again.

    I have plenty of code on the workbook for other stuff the file does, however it has nothing to do with the reference I'm dealing with.

    Do you see my issue now? It's frustrating because all I want is to bypass the error, just make it never appear on any situation, on any user's computer.

  6. #6
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,009

    Re: Bypass missing reference VBA error

    No I don't. If the code isn't using the reference you don't need it.

  7. #7
    Registered User
    Join Date
    10-27-2014
    Location
    Lisbon
    MS-Off Ver
    2010
    Posts
    24

    Re: Bypass missing reference VBA error

    Of course I need it.

    It's a third party add-in, I don't know how to explain this in excel terms.

    I have a program installed inside excel from Nielsen, it allows me to add market research databases to the workbook and I add these databases with a software visual interface, NOT CODE. And this is the reference missing in some users, because as I said these users don't have the add-in installed.

    BUT, as there are users who need to access the add-in, I cannot simply uncheck the reference and make it unavailable. I just want to bypass the error.... isn't there a simple way to do this? Bypassing a VBA error?

  8. #8
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,009

    Re: Bypass missing reference VBA error

    Again, if your code is not using the add-in, it does not require a reference to it to be set in the VBA project. The add-in will of course still need to be installed but that is a completely different matter.

  9. #9
    Registered User
    Join Date
    10-27-2014
    Location
    Lisbon
    MS-Off Ver
    2010
    Posts
    24

    Re: Bypass missing reference VBA error

    I understand, but if the reference is not set then the required users will not be able to use the add-in.

    There is no solution to my problem then? Can't I just write some code to ignore the error?

  10. #10
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,009

    Re: Bypass missing reference VBA error

    Quote Originally Posted by lamarao View Post
    I understand, but if the reference is not set then the required users will not be able to use the add-in.
    Why not, exactly?

  11. #11
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: Bypass missing reference VBA error

    Stepping back a little...

    You cannot trap the error if the 3rd party application is not installed as the error will occur when the application is compiled by Excel - and no code is actually running at that time.

    However, if you use late binding then all your users will still be able to use the code, but you can then trap the error for those users who do not the other application/Add in, or whatever.

    Using a phone so am not giving a solution - just trying to get this topic back on track first.

  12. #12
    Registered User
    Join Date
    10-27-2014
    Location
    Lisbon
    MS-Off Ver
    2010
    Posts
    24

    Re: Bypass missing reference VBA error

    First of all thank you both for replying to my problem...

    Quote Originally Posted by romperstomper View Post
    Why not, exactly?
    The workbook has "elements" of the 3rd party add-in embedded into it, into 2 sheets to be precise.

    I can only uncheck the reference if the workbook is not using the add-in, meaning I would have to remove these "elements", which then defeats the purpose of having them in the workbook in the first place.

    Of course the users with the missing reference can uncheck it, but then by saving the file the next user who needs to use the add-in won't be able to.

    Quote Originally Posted by cytop View Post
    Stepping back a little...

    You cannot trap the error if the 3rd party application is not installed as the error will occur when the application is compiled by Excel - and no code is actually running at that time.

    However, if you use late binding then all your users will still be able to use the code, but you can then trap the error for those users who do not the other application/Add in, or whatever.

    Using a phone so am not giving a solution - just trying to get this topic back on track first.
    Sounds like there maybe a solution after all?

    Essentially, the users who don't have the add-in installed can't click any cell without the error showing up.

    However, if they open up the VBA editor, the error points to a code I have for a date picker in a certain sheet.

    I read in an article the following: "This usually manifests itself as an error message along the lines of “Can’t find project or library” with the debugger highlighting what appears to be a fairly innocuous line of code – often involving a native VBA function like Date, Left or Format." So I'm guessing it has nothing to do with this code, it's just some default debugging for missing references.

  13. #13
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: Bypass missing reference VBA error

    ...highlighting what appears to be a fairly innocuous line of code ...
    That's what Excel usually does in this situation - you need to actually go look at the Tools/References menu to see which reference is missing.

    As an example of late binding (Assuming you're familiar with Early Binding). Copy to a module and step through LateBound1 to sonfirm it'll work, then step LateBound2


    Please Login or Register  to view this content.

  14. #14
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,009

    Re: Bypass missing reference VBA error

    If you actually require the reference to use the embedded objects and aren't using code, then late binding won't work. Your only option is going to be to ensure that you fully qualify all function calls in your code - e.g. using VBA.Date rather than just Date, and so on.

  15. #15
    Registered User
    Join Date
    10-27-2014
    Location
    Lisbon
    MS-Off Ver
    2010
    Posts
    24

    Re: Bypass missing reference VBA error

    Quote Originally Posted by romperstomper View Post
    If you actually require the reference to use the embedded objects and aren't using code, then late binding won't work.
    I was going to say that, I think.. But thanks anyway cytop.

    Quote Originally Posted by romperstomper View Post
    Your only option is going to be to ensure that you fully qualify all function calls in your code - e.g. using VBA.Date rather than just Date, and so on.
    Can you elaborate on that please? I have the following code for a Date and Time Picker ActiveX control:

    Please Login or Register  to view this content.
    When opening the VBA editor after getting the missing reference error, the debugger highlights "date", which goes in accordance with the article I mentioned above..

  16. #16
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,009

    Re: Bypass missing reference VBA error

    You'd have to use:
    Please Login or Register  to view this content.

  17. #17
    Registered User
    Join Date
    10-27-2014
    Location
    Lisbon
    MS-Off Ver
    2010
    Posts
    24

    Re: Bypass missing reference VBA error

    Quote Originally Posted by romperstomper View Post
    You'd have to use:
    Please Login or Register  to view this content.
    This worked, kind of.

    Users can now select cells without the error showing up. However, it shows up again when inputting data into any cell, with a slight difference: it no longer points to a hidden module in sheet1, but in the ThisWorkbook object.

    And now, the code present in ThisWorkbook highlighted in the debugger is the following:

    Please Login or Register  to view this content.
    This code prevents users from screwing up cell formatting by forcing a paste values or paste formulas.

  18. #18
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,009

    Re: Bypass missing reference VBA error

    Try:
    Please Login or Register  to view this content.

  19. #19
    Registered User
    Join Date
    10-27-2014
    Location
    Lisbon
    MS-Off Ver
    2010
    Posts
    24

    Re: Bypass missing reference VBA error

    That last one didn't work. It stays the same.

  20. #20
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,009

    Re: Bypass missing reference VBA error

    Is there an error message?

  21. #21
    Registered User
    Join Date
    10-27-2014
    Location
    Lisbon
    MS-Off Ver
    2010
    Posts
    24

    Re: Bypass missing reference VBA error

    Yes, the same one. Everything stays the same as I described on the 2nd post of this page. It's as if adding "excel" to those lines didn't have any effect whatsoever.

  22. #22
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,009

    Re: Bypass missing reference VBA error

    What if you also change this line:
    Please Login or Register  to view this content.

  23. #23
    Registered User
    Join Date
    10-27-2014
    Location
    Lisbon
    MS-Off Ver
    2010
    Posts
    24

    Re: Bypass missing reference VBA error

    It worked!! Unbelievable. I can't thank you enough!

    At least for now no error showed up, let's hope it stays that way.

  24. #24
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,009

    Re: Bypass missing reference VBA error

    Fingers crossed!

  25. #25
    Registered User
    Join Date
    11-09-2018
    Location
    Czechia
    MS-Off Ver
    2007
    Posts
    1

    Re: Bypass missing reference VBA error

    Hello,
    I have the similar problem.

    I do some measurements using XLS VBA on PC connected to external hardware via OPC server using 3rd party library64\opcdaauto.dll.
    After measurement, I need to share file on other PCs without OPC server connected. But Error message occurs after start. I would like to disable Error message but want to keep 3rd party library linked.

    OPC module starts with declaration of Public Variables. If I comment them, no error disappears. But if I put them to Public Sub with "If" condition checking computer name, they are not initiated when OPC server is connected

    Please Login or Register  to view this content.

    Would be conditional compilation helpful in this case?

  26. #26
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,049

    Re: Bypass missing reference VBA error

    dayslypper welcome to the forum

    Unfortunately your post does not comply with Rule 4 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Missing reference in reference library???
    By JapanDave in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-20-2013, 05:00 AM
  2. Remove missing reference, update outlook reference
    By Snoopy2003 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-29-2011, 08:35 PM
  3. Bypass the run time error if user enters a wrong value
    By Richard Flame in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-30-2007, 11:26 AM
  4. Bypass Circular Reference?
    By noobdisaster in forum Excel General
    Replies: 2
    Last Post: 10-21-2005, 02:05 PM
  5. Replies: 2
    Last Post: 08-01-2005, 09:39 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1