Results 1 to 4 of 4
  1. #1
    Star Lounger SoonerJim's Avatar
    Join Date
    Aug 2011
    Temple, Texas USA
    Thanked 0 Times in 0 Posts

    Error message on Function that worked in the past (Excel 2010)

    Hello there. Hope you have a safe and Happy New Year. I'm a pharmacist and work with what are called "National Drug Code" numbers. They are separated into sections, ususally four to six numbers in the first part, four in the second part, and two in the third part. I have a custom function which has worked for several years. It inserts the dashes at the appropriate place.
     Function NDC(ByVal X As Variant)
            Dim m
            If Right(X, 1) = " " Then
            m = Left(X, 5) & "-" & Mid(X, 6, 4) & "-" & Mid(X, 10, 2)
            ElseIf Len(X) = 12 Then
            m = Left(X, 6) & "-" & Mid(X, 7, 4) & "-" & Right(X, 2)
            ElseIf Len(X) = 11 Then
            m = Left(X, 5) & "-" & Mid(X, 6, 4) & "-" & Right(X, 2)
            ElseIf Len(X) = 10 Then
            m = Left(X, 4) & "-" & Mid(X, 5, 4) & "-" & Right(X, 2)
           m = "error"
           End If
           NDC = m
          End Function
    When I try to use the function now, I get a "Compile Error - Can't find project or Library." The red text above is highlighted by the VBA engine. I do not know what to do to fix this, and I'm wondering if it will error out on the next line if the first one gets fixed. Anyone have an idea?
    Attached Files Attached Files
    Last edited by RetiredGeek; 2013-12-30 at 12:52. Reason: Changed Indent codes to code codes.

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Thanked 105 Times in 90 Posts
    Jim W,

    Works fine for me, so the code is OK.

    Probably you have missing VBA References - open the VBA editor (ALT + F11) . . . Tools . . . References and see what isn't checked - you should have at least the following items checked:

    Visual Basic for Applications
    Microsoft 11.0 Object Library

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts
    I believe that MartinM has the answer, but a possible solution would be to eliminate the function altogether. If you entered the values as numbers, you could just use the custom format( format cells - custom - type:

    To display it with the dashes already in it. This would even work if the number had no leading zeroes.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Pa, USA
    Thanked 621 Times in 566 Posts

    As Martin states, you must have the Visual Basic for Applications but the second must have library is the Microsoft Excel 14.0 Object Library (or version depending on version of Excel). Not the Microsoft Office 14.0 Object Library (or lower version). Although Martin didn't specify which one, I am sure he meant to specify that these are the only 2 you cannot de-reference. No additional libraries are needed to run your macro for the content it contains.

    Since the code is sound (as Martin is most correct), I would suggest copy and pasting your macro into Notepad, delete the module, save and close the workbook, reopen and create a new module, then repaste you code into it. See if that solves your problem.

    Last edited by Maudibe; 2013-12-30 at 17:54. Reason: spelling

Posting Permissions

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