Creating a UDF that only looks to a specific workbook, not just the active workbook

[Lang43]

Good Morning Everyone,

I have a bit of a problem. I created a glorified lookup UDF in order to be able to properly look up values from one tab to another within a workbook.

The problem I am having is that there are multiple worbooks(like templates) that have the exact same setup. So if the user has more than one workbook open and the workbook recalculates, both open workbooks look to the Activeworkbook and the inactive workbook pulls the wrong info.

Current Code:

[CODE][CODE]Function NetPay(Scenario As Range)
   Dim CC As Integer
   CC = Application.Caller.Column
   
    Select Case Scenario
        
        Case 1: NetPay = ActiveWorkbook.Sheets("Scenarios").Cells(4, CC)
        Case 2: NetPay = ActiveWorkbook.Sheets("Scenarios").Cells(7, CC)
        Case 3: NetPay = ActiveWorkbook.Sheets("Scenarios").Cells(10, CC)
        Case 4: NetPay = ActiveWorkbook.Sheets("Scenarios").Cells(13, CC)
        Case 5: NetPay = ActiveWorkbook.Sheets("Scenarios").Cells(16, CC)
        Case 6: NetPay = ActiveWorkbook.Sheets("Scenarios").Cells(19, CC)
        Case 7: NetPay = ActiveWorkbook.Sheets("Scenarios").Cells(22, CC)
        Case 8: NetPay = ActiveWorkbook.Sheets("Scenarios").Cells(25, CC)
        Case 9: NetPay = ActiveWorkbook.Sheets("Scenarios").Cells(28, CC)
        
    End Select
    
    
End Function

I am trying to use a =Cell("filename") variation within the workbook to specify the workbook in a cell, then write code in a UDF that will make each UDF specifically Look that workbooks tab for the information.

Something like this

Function NetPay(Scenario As Range, Filename As Variant)
   Dim CC As Integer
   CC = Application.Caller.Column
   
    Select Case Scenario
        
        Case 1: NetPay = Filename.Sheets("Scenarios").Cells(4, CC)
        Case 2: NetPay = Filename.Sheets("Scenarios").Cells(7, CC)
        Case 3: NetPay = Filename.Sheets("Scenarios").Cells(10, CC)
        Case 4: NetPay = Filename.Sheets("Scenarios").Cells(13, CC)
        Case 5: NetPay = Filename.Sheets("Scenarios").Cells(16, CC)
        Case 6: NetPay = Filename.Sheets("Scenarios").Cells(19, CC)
        Case 7: NetPay = Filename.Sheets("Scenarios").Cells(22, CC)
        Case 8: NetPay = Filename.Sheets("Scenarios").Cells(25, CC)
        Case 9: NetPay = Filename.Sheets("Scenarios").Cells(28, CC)
        
    End Select
    
    
End Function

So, as you can see, I have drawn a blank as to how to make this UDF only look to the file it is in and then look up the value, instead of just looking up the active workbooks tab.

This is more of a neccesity for data integrity.

ANyone happen to have an idea on how to make this work better?

 

[pgc01]

Hi

If I understand correctly the udf should refer to the workbook where the cell that uses the udf is located.

You can start by getting the workbook

Dim wb As Workbook

Set wb = Application.Caller.Parent.Parent

and then use wb to qualify the sheets:

        Case 5: NetPay = wb.Sheets("Scenarios").Cells(16, CC)

 

[Lang43]

Excellent! I keep forgetting those easy codes. Still learning and I appreciate your help!

 

[pgc01]

You're welcome. Thanks for the feedback.