CODEWITHSUNDEEP
vbaexcel
B.C
B.C

Reputation: 3

Function works in immediate window but not in worksheet?

I am trying to make a public function which looks up a user supplied number in a different workbook and extract information from it. The user will give a plan number (which is really a string with a number in it) and the function will look up this number in another workbook to collect a file number and a crossing associated with the plan number.Right now I have this function working when it is called in the immediate window but it will return #VALUE! when called on in a worksheet. Here is what i have.

Public Function indexfind(Plan As String) As String
    Dim wks As Worksheet
    Dim d As Range
    Dim filenumber As String
    Dim crossing As String
    Dim Plannumber As Double
    Plannumber = Val(Left(Plan, 4))

    Workbooks.Open ("File Path")
    Workbooks("PLANINDE.xls").Worksheets("Index").Activate
    Set wks = Workbooks("PLANINDE.xls").Worksheets("Index")
    Set d = wks.Range("G:G").Find(Plannumber, , xlValues)

    If d Is Nothing Then
        indexfind = "Plan Does Not Exist"
        Workbooks("PLANINDE.xls").Close (False)
    Exit Function
    End If

    filenumber = wks.Cells(d.Row, 2).Value
    crossing = wks.Cells(d.Row, 3).Value

    Workbooks("PLANINDE.xls").Close (False)

    Debug.Print filenumber
    Debug.Print crossing
    Debug.Print Plannumber

    indexfind = "No Plan Folder. File Number:" & filenumber & " crossing:" & crossing
End Function

Upvotes: 0

Views: 1543

Answers (1)

Tim Williams
Tim Williams

Reputation: 166790

Your code cannot work as a function called directly from a worksheet. VBA UDF's are restricted as to what they can do.

https://support.microsoft.com/en-us/help/170787/description-of-limitations-of-custom-functions-in-excel

Upvotes: 4

Related Questions