+ Reply to Thread
Results 1 to 5 of 5

Can't find project or library error with VBA script.

Hybrid View

  1. #1
    Registered User
    Join Date
    05-25-2014
    Posts
    2

    Can't find project or library error with VBA script.

    I am having problems with a VBA script I wrote.
    It works fine with several versions of Excel (2003, 2010, and 2013).
    But the problem arises when I move from Windows XP to Windows 7 or 8.

    I use a scanner to scan barcode serial numbers into a textbox, and then populate these serial numbers with other associated data into a spreadsheet.
    The serial numbers are supposed to be separated by each line and placed into an array.

    I get a compile error "Cant find project or library" with CHR highlighted in blue from this line of code...

    SNs = Split(Str, Chr(10))

    Once again, it works fine on many machines with XP, but not on Win 7 or 8.

    Any help would be appreciated.

    Thank you.
    Glenn


    Private Sub CommandButton1_Click()
        'This is the button that fills the CPE Imports sheet with the serial numbers from the user form
        Dim EmptyRow As Integer
        Dim Str, Str2, a As String
        Dim SNs() As String 'array
        Dim UpperBound As Long
        Dim CorpItem As String
        
        'This line will determine the corporation.
       
        
        If Range("Info!D10").Value = "01719" Then
            CorpItem = "Good"
        ElseIf Range("Info!D10").Value = "19204" Then
            CorpItem = "Functional"
        Else
            CorpItem = "Good"
        End If
    
        'Make sure Sheet5 is Active
        Sheet5.Activate
    
        'Determine emptyRow
        EmptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
    
        'set the Str variable to the values of the serial numbers in the Text Box
        Str = TextBox1.Value
        'split the variable "Str" by returns "Chr(10)", and populate it into the SNs() array.
        SNs = Split(Str, Chr(10))
        'Set the variable "UpperBound" to the upper bound of the array SNs()
        UpperBound = UBound(SNs) - 1
    
        For i = 0 To UpperBound
            Str2 = SNs(i)
            Len1 = Len(Str2)
            Str2 = Left(Str2, Len1 - 1)
            'the last line above ^^^^ will truncate the newline character (the little square) or chr(10) off of the end of the serial number
            
            'first check to see if the length of Str2 is 19 digits long...
            If Len(Str2) = 19 Then
                '... and truncate the right 13 digits.
                Str2 = Right(Str2, 13)
            End If
            'now check length of Str2 to see if it is 14 digits or less
            If Len(Str2) > 14 Then
              '... and if not, save the 14 digits to the right
              Str2 = Right(Str2, 14)
            End If
            'Now, lettuce fill the columns A, B, and C, with the scanned in items...
            Range("A" & EmptyRow + i).Value = UCase(Str2)
            Range("B" & EmptyRow + i).Value = CorpItem
            Range("C" & EmptyRow + i).Value = ListBox1.Value
        Next
        'clear the text box so that a double click or more will not over populate the spreadsheet.
        TextBox1.Text = ""
        Range("Info!D10").Value = ""
        OptionButton1.Value = False
        OptionButton2.Value = False
        EmptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
        MsgBox EmptyRow
        Range("A" & EmptyRow).Select
    End Sub

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Can't find project or library error with VBA script.

    In the VBE, Tools > References, look for the one that starts with MISSING and untick it.

    Your code also has undeclared variables (i, Len1), and incorrectly declared variables, e.g.,

    Dim Str, Str2, a As String
    ... declares a as a String and the others as Variants.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: Can't find project or library error with VBA script.

    Hi Glenn,

    Welcome to the forum

    It is strange that this is happening. Chr(10) is certainly a well recognised attribute/function, so why it is bugging out is strange? By default they should be available in all versions of Excel, but if you are developing in one version and running in another version that may be an issue, but doubtful.

    Try this: SNs = Split(Str, vbLf)
    Regards,
    Rudi

  4. #4
    Registered User
    Join Date
    05-25-2014
    Posts
    2

    Re: Can't find project or library error with VBA script.

    I would like to thank both for the quick reply. The Tools, References, MISSING was the one that did the trick.

    And the other clean up helped too.

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Can't find project or library error with VBA script.

    You're welcome.

+ 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. Can't find Project or Library Error
    By ElsiePOA in forum Excel General
    Replies: 2
    Last Post: 12-13-2010, 02:33 AM
  2. error can't find project or library
    By cedtech23 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-21-2006, 01:48 PM
  3. [SOLVED] Can' find project or library Error
    By Nicholas B in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-22-2006, 11:50 AM
  4. Can't find project or library Error?
    By RS in forum Excel General
    Replies: 0
    Last Post: 02-15-2006, 12:15 AM
  5. Error:Can't find project or library
    By Casey in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-13-2005, 05:05 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