8

My customer is getting a Compile Error; Can't find project or Library on his version of Excel 2010, however i am not getting this on my version of 2010. How can i adjust this code so it will not appear. When the error appears in the following code the text "cell" in "For each cell in selection" is highlighted:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$9" Then
Columns("D:CB").Select
Selection.EntireColumn.Hidden = False
Application.ScreenUpdating = False

Sheet17.Range("E48:CB48").Select

For Each cell In Selection
    If cell = 0 Then
       Range(cell.Address).EntireColumn.Hidden = True
    End If
Next

Application.ScreenUpdating = True
Sheet17.Range("b9").Select
End If

End Sub`

My customer is also reporting a bug in the following code with the word "Response" being highlighted. This, as well, is not an issue for me, on my version of Excel 2010. Any and all help is greatly appreciated.

If Sheet1.Range("E18") = 3 Then
Response = MsgBox("Reminder Emails have been set to be sent automatically at " &               Sheet1.Range("f18").Value & ", " & Sheet1.Range("Q4").Value & " day(s) before" & vbCrLf & "the scheduled appointment. Do you want to send reminder e-mails now anyway?", vbYesNo)
    If Response = vbNo Then
    Exit Sub
    End If
    End If
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
Excel For Freelancers
  • 156
  • 2
  • 6
  • 17
  • You are using `Response` to hold a long variable so this shouldn't be trigering an error (unless perhaps `Response` is part of the object model of a library reference - guess). I would use `lngResponse` for the name and dimension it as long. – brettdj Jul 18 '12 at 03:22
  • 2
    This may be identical to: http://stackoverflow.com/questions/507191/cant-find-project-or-library-for-standard-vba-functions – Smandoli Jul 18 '12 at 04:06
  • OK i will give that a try. Since on my machine, all of these options work fine, i have to wait for the customer feedback, which is a bit frustrating. Thanks so much again – Excel For Freelancers Jul 18 '12 at 04:08
  • Can you pls post a screenshot of your references, ie in the VBIDE goto `Tools - References` and capture what references are checked. – brettdj Jul 18 '12 at 04:21
  • 1
    Not dimensioning your variable won't be the issue so I think you have marked it solved prematurely ......... seeing your References would be useful. – brettdj Jul 18 '12 at 04:48
  • @brettdj I have sent the email to the customer regarding a screenshot of the VBIDE. A screenshot of mine perhaps would not be as helpful since i did not encounter the issue (At least until i added the 'Option Explicit' above the code, then both of the exact errors presented, just the way the customer error screen-shots looked like with the "Cell" and "Response" text highlighted.) – Excel For Freelancers Jul 18 '12 at 09:05
  • Pls show us YOUR screenshot. Then we may be able to see the issue :) – brettdj Jul 18 '12 at 09:46
  • @brettdj I have combined both the customers screen shot, and my code as well into one screenshot. Is this helpful? [link]https://dl.dropbox.com/u/1220666/VBA-Error.png – Excel For Freelancers Jul 18 '12 at 12:59
  • No - your error is due to lack of dimensioning (flagged by the Option Explict). Your customer's error is different - we need to see his/her References to see what the Library issue may be – brettdj Jul 18 '12 at 13:07
  • @brettdj Hmmm Ok, I will email him and have him send me a screen shot of his references. Thanks for the heads up. I will let him know it may still have the issue. Thank You.. (Yes i can see that the errors are different, in my haste of wanting to fix this problem i may have been a bit premature) I will keep you updated. – Excel For Freelancers Jul 18 '12 at 14:31

1 Answers1

9

In the VBA window, go to Tools --> References and ensure the same libraries are toggled on for all computers. Also make sure all active libraries are in the same order top-to-bottom.

Many libraries "come standard" but one may need to be toggled on. Or, a library reference may need to be toggled off due to a functional interference. A library may be altogether missing, but I doubt this is the case since it's a fairly standard suite and you aren't aware of having tinkered with it.

This is a typical issue and usually not considered too great a burden on your distribution clientele. If it is, you can rework your code to use fewer references; or you may be able to load the needed libraries programmatically (but I haven't ever tried that).

I suggest you include Option Explicit at the top of all modules. This problem looks a bit like a failure to declare your variables; and I think that requirement can vary by setting. Option Explicit will force all variables to be declared, which is beneficial in general and could cause all client installs to act the same.

Smandoli
  • 6,919
  • 3
  • 49
  • 83
  • Ok, i was aware of this, but i was actually hoping there was something wrong with my code, so i would not have to ask my customer to do this. Based on your answer, I will go ahead and ask him to check his VBA Libraries as you have suggested. (I am hoping this is a rare occurrence, as this application will be widely distributed) Thank you for your quick respoinse – Excel For Freelancers Jul 18 '12 at 02:58
  • @raphuket The libraries should be on **automatically** - ie the problem is they are missing from the machine rather than not toggled – brettdj Jul 18 '12 at 03:11
  • @brettdj Hmmmm, Ok. Obviously I am new to VBA, and i just sent my customer an email, with instructions, to check for this. If his libraries are 'missing' then i am guessing only a re-install of excel would be needed? Or is this 'missing library' something he can install, or i can send him? Sorry for all the questions, but if this is a widespread issue with other customers, i will have to find a work-around. – Excel For Freelancers Jul 18 '12 at 03:15
  • 1
    @raphuket. It depends. For example you may have references to specific libraries (such as Outlook 2010), whereas the customter may have Outlook 2007. In this case "late binding" rather than "early binding" fixes the mis-match. See [this reference](http://word.mvps.org/faqs/interdev/earlyvslatebinding.htm). Can you post a screenshort of the references that you have checked for this project? – brettdj Jul 18 '12 at 03:47
  • @brettdj and discussion -- see my update to the answer. I don't think it's correct to say 'libraries should be on automatically.' They may be missing, or they may be toggled off. – Smandoli Jul 18 '12 at 03:55
  • When you send a file with library references to another person they are on by default. I think its rather unlikely the file recipient has toggled off the settings then reported an error. – brettdj Jul 18 '12 at 04:01
  • @brettdj Thank you for the additional information. I am just learning about the 'late binding' and i believe that will lead me to a solution. here is a screenshot of the issue [link]https://dl.dropbox.com/u/1220666/VBA-Error.png Could it be a s simple as a missing "Dim Cell as Range" ? – Excel For Freelancers Jul 18 '12 at 04:03
  • @brettdj -- Right, an Excel document stores its own reference requirements and therefore can be expected to behave the same on different computers. That makes sense, thanks for filling in your thinking. – Smandoli Jul 18 '12 at 04:04
  • @Smandoli Great advice regarding the Option Explicit. I will use that as well. Thank you – Excel For Freelancers Jul 18 '12 at 04:15
  • @Smandoli Thanks for all of your help. I am getting better at this every day. Using the Option Explicit code, i was able to see right away that my Cell variable was not defined. Screenshot: [link]https://dl.dropbox.com/u/1220666/VBA-Error-Found.png I will do the same for the other code, and cross my fingers that this solves the issue on my customers machine. If you have any other suggestions, links or ideas, it would also be appreciated. – Excel For Freelancers Jul 18 '12 at 04:28