Excel 2003 VBA no longer referencing

[michael.p.ryan1]

I created a couple of functions in a separate module called "Functions" and one for Subprocedures named likewise, both located in the Personal workbook.

I had a function called "ConvertCalDate" defined as a date. It worked perfectly fine and I was able to reference it in any of my workbooks.

After creating a function called "LatestCalDateAddress" to return the more recent of two compared dates from two separate workbooks, VBA stopped referencing the Personal workbook from other workbooks. I can still reference a function from within the Personal workbook just fine. When I try to run any function outside the personal workbook now, it gives the error, "Sub or Function not defined"

One more thing before I present the code, I AM able to insert any of the functions from the Personal workbook into a worksheet without any problems.

The function is as follows:

Function LatestCalDateAddress(CompareDate1 As Range, CompareDate2 As Range) As Range
    
    Dim dtDate(1 To 2) As Date
    Dim strDate(1 To 2) As String
    Dim intDate As Integer, intMonth As Integer, intDay As Integer, intYear As Integer, intSlash As Integer
    
    strDate(1) = UpdateCalDate(CompareDate1.Value)
    strDate(2) = UpdateCalDate(CompareDate2.Value)
        
    If InStr(1, CompareDate1.Value, "AT CAL ") > 0 Or InStr(1, CompareDate1.Value, "at cal ") > 0 Then
        dtDate(1) = ConvertAtCalDate(CompareDate1)
    Else
        dtDate(1) = ConvertCalDate(CompareDate1)
    End If
    
    If InStr(1, CompareDate2.Value, "AT CAL ") > 0 Or InStr(1, CompareDate2.Value, "at cal ") > 0 Then
        dtDate(2) = ConvertAtCalDate(CompareDate2)
    Else
        dtDate(2) = ConvertCalDate(CompareDate2)
    End If
    
    If dtDate(1) > dtDate(2) Then
        Set LatestCalDateAddress = CompareDate1
    Else
        Set LatestCalDateAddress = CompareDate2
    End If
End Function

Function LatestCalDateWorkbook(Workbook1 As Workbook, CompareDate1 As Range, Workbook2 As Workbook, CompareDate2 As Range) As Workbook
    
    Dim dtDate(1 To 2) As Date
    Dim strDate(1 To 2) As String
    Dim intDate As Integer, intMonth As Integer, intDay As Integer, intYear As Integer, intSlash As Integer
    Dim wksWorksheet(1 To 2) As Worksheet
    
    Set wksWorksheet(1) = CompareDate1.Worksheet
    Set wksWorksheet(2) = CompareDate2.Worksheet
    
    strDate(1) = UpdateCalDate(CompareDate1.Value)
    strDate(2) = UpdateCalDate(CompareDate2.Value)
        
    If InStr(1, CompareDate1.Value, "AT CAL ") > 0 Or InStr(1, CompareDate1.Value, "at cal ") > 0 Then
        dtDate(1) = ConvertAtCalDate(CompareDate1)
    Else
        dtDate(1) = ConvertCalDate(CompareDate1)
    End If
    
    If InStr(1, CompareDate2.Value, "AT CAL ") > 0 Or InStr(1, CompareDate2.Value, "at cal ") > 0 Then
        dtDate(2) = ConvertAtCalDate(CompareDate2)
    Else
        dtDate(2) = ConvertCalDate(CompareDate2)
    End If
    
    If dtDate(1) > dtDate(2) Then
        Set LatestCalDateWorkbook = Workbook1
    Else
        Set LatestCalDateWorkbook = Workbook2
    End If
End Function

The SubProcedure that uses the code is:

Sub FindInitialReference()
'
' Macro2 Macro
' Updates Recods such as CAL Dates Macro recorded 5/1/2012 by michael.ryan
'
' Keyboard Shortcut: Ctrl+Shift+F
'
    Dim intCount As Integer, intWorkbook As Integer, intRow As Integer
    Dim strCalID As String, rngCalID(1 To 2) As Range, rngSearchCalID As Range
    Dim strCalDue As String, strTurnTime As String, strWorkbook As String
    Dim strLatestCalDue As String, strLatestTurnTime As String
    Dim wbkLatest As Workbook, rngLatest As Range
    Dim blFound As Boolean, blNotFound As Boolean
    Dim lngCellColor(1 To 3) As Long
    Dim strTest As String
          
    strWorkbook = ActiveWorkbook.Name
    
    For intRow = ActiveCell.Row - 2 To Range("Tool_Inventory").Rows.Count
                              
        Range("Tool_Inventory").Cells(intRow, 1).Activate
                              
        Set rngSearchCalID = Range("E" & ActiveCell.Row)
        strCalID = rngSearchCalID.Value
        
        lngCellColor(2) = rngSearchCalID.Interior.Color
        
        lngCellColor(1) = rngSearchCalID.Offset(, -1).Interior.Color
        lngCellColor(3) = rngSearchCalID.Offset(, 1).Interior.Color
        
        rngSearchCalID.Interior.Color = 65535
        rngSearchCalID.Offset(, -1).Interior.Color = 16777215
        rngSearchCalID.Offset(, 1).Interior.Color = 16777215
        
        If strCalID = "" Then GoTo Finish_Find
        
        Windows("Avionics  Tooling workcopy").Activate
        
        Sheets("Tooling Inventory").Select

Updated_E_Number:
        Set rngCalID(1) = Cells.Find(What:=strCalID, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
                :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                False, SearchFormat:=False)
        
        If rngCalID(1) Is Nothing Then
            If blNotFound = False Then
                If Left(strCalID, 1) = "E" Then
                    strCalID = "E-" & Right(strCalID, Len(strCalID) - 1)
                    blNotFound = True
                    GoTo Updated_E_Number
                End If
                
                blNotFound = True
            Else
                MsgBox ("This tool is not found in Avionics Tooling Workcopy")
                Exit Sub
            End If
        End If
        
        If blNotFound = True Then
            
            If Left(strCalID, 1) = "E" Then
                Windows(strWorkbook).Activate
                
                rngSearchCalID.Value = strCalID
            Else
                strCalID = strCalID
            End If
        End If
        
        Set rngCalID(2) = Range("A1")
        
No_Match:
        If rngCalID(1).Address = rngCalID(2).Address Then
            Windows(strWorkbook).Activate
            rngSearchCalID.Offset(1).Activate
            GoTo Finish_Find
        Else
            Set rngCalID(1) = Cells.Find(What:=strCalID, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
                :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                False, SearchFormat:=False)
            Set rngCalID(2) = rngCalID(1)
        End If
            
        If rngCalID(1) Is Nothing Then
            
            MsgBox "Tool Not Found"
            
            Exit Sub
        Else
            blFound = True
        End If
        
        rngCalID(1).Activate
        
        strCalDue = "='[Avionics  Tooling workcopy.xls]Tooling Inventory'!$F$" & rngCalID(1).Row
        strTurnTime = "='[Avionics  Tooling workcopy.xls]Tooling Inventory'!$G$" & rngCalID(1).Row
        
        Select Case InputBox("Are the tools Matching?", , "Yes", 50, 50)
        
        Case "Yes"
                                    
            [COLOR=Red]Set rngLatest = latestCalDateAddress(rngSearchCalID, rngCalID(1))[/COLOR]
            
            'Set wbkLatest = latestCalDateWorkbook(strWorkbook, rngSearchCalID, Workbooks("Avionics  Tooling workcopy"), rngCalID(1))
            
            Windows(strWorkbook).Activate
            
            rngSearchCalID.Offset(0, 1).Formula = strCalDue
            rngSearchCalID.Offset(0, 2).Formula = strTurnTime
            rngSearchCalID.Offset(1, 0).Activate
            
            Windows("Avionics  Tooling workcopy").Activate
            Set rngCalID(1) = Cells.FindNext(rngCalID(1))
            GoTo No_Match
        Case "No"
            Windows("Avionics  Tooling workcopy").Activate
            Set rngCalID(1) = Cells.FindNext(rngCalID(1))
            GoTo No_Match
        Case ""
            Windows(strWorkbook).Activate
            
            rngSearchCalID.Interior.Color = lngCellColor(2)
            rngSearchCalID.Offset(, -1).Interior.Color = lngCellColor(1)
            rngSearchCalID.Offset(, 1).Interior.Color = lngCellColor(3)
            
            Exit Sub
        End Select

Finish_Find:
        rngSearchCalID.Interior.Color = lngCellColor(2)
        rngSearchCalID.Offset(, -1).Interior.Color = lngCellColor(1)
        rngSearchCalID.Offset(, 1).Interior.Color = lngCellColor(3)
        
    Next intRow
End Sub

I plan on using the range and workbook variables to get the data of the later calibration date and the data offset one cell to the right of the date