Opening a second workbook breaks the UDF in the first workbook

[rtemen]

I have a workbook, Book-A, that I have defined a UDF.
When I open a second workbook, it breaks the UDF on the first workbook.

How do I stop this?

Rich

 

[RoryA]

Correct the code in the UDF.

 

[rtemen]

Correct the code in the UDF.

I have no idea what to 'Correct'. Here is my code, please let me know what to correct.

Function myPrice(myPart As String, myVol As Integer)
    
    Application.Volatile
    
    Dim myCol As Integer
    Dim myCavity As Integer
    Dim myCycleTime As Integer


    myCol = Application.WorksheetFunction.Match(myPart, Range("Item_Name"), 0)
    myCavity = Application.WorksheetFunction.Index(Range("Molding_Data"), Application.WorksheetFunction.Match("Cavities", Range("pRows"), 0), myCol)
    myCycleTime = Application.WorksheetFunction.Index(Range("Molding_Data"), Application.WorksheetFunction.Match("Cycle Time", Range("pRows"), 0), myCol)


    myPrice = myCavity * myCycleTime / myVol
    
End Function

 

[RoryA]

I was trying to get you to post the code - hard to fix something you can't see...

You need to qualify your Range objects with the correct workbook and worksheet or, preferably, pass the ranges as arguments to the UDF.

 

[rtemen]

I was trying to get you to post the code - hard to fix something you can't see...

You need to qualify your Range objects with the correct workbook and worksheet or, preferably, pass the ranges as arguments to the UDF.

I am rather new to this, so here is my next attempt. The line set wb = thisworkbook doesn't work.

Function myPrice(myPart As String, myVol As Integer)
    
    Application.Volatile
    
    Dim myCol As Integer
    Dim myCavity As Integer
    Dim myCycleTime As Integer
    Dim wb As Workbook
    Dim ws As Worksheet
    
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Sheet1")
    
    With ws
    
    myCol = Application.WorksheetFunction.Match(myPart, .Range("Item_Name"), 0)
    myCavity = Application.WorksheetFunction.Index(.Range("Molding_Data"), Application.WorksheetFunction.Match("Cavities", .Range("pRows"), 0), myCol)
    myCycleTime = Application.WorksheetFunction.Index(.Range("Molding_Data"), Application.WorksheetFunction.Match("Cycle Time", .Range("pRows"), 0), myCol)
    
    End With


    myPrice = myCavity * myCycleTime / myVol
    
End Function

 

[rtemen]

It looks like I figured it out.
I had Googled the subject and found many examples showing the 'Set wb = ThisWorkbook'.
I finally tried
Set wb = Workbooks("AdvancedQuote.xlsm")
and now all seems well.

Rich

 

[RoryA]

ThisWorkbook refers to the workbook containing the code. If that's the workbook you want to refer to, it is better to use that than its name, in case that changes.