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

1. ## 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.
Code:
``` 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)
Else
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?

2. 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. 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:
000000-0000-00

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

4. SoonerJim,

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.

HTH,
Maud

#### Posting Permissions

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