UDF works in sub but not in sheet

[StuH]

Hi, I've written the following function in VBA that returns the expected output when run through a sub, but returns a #VALUE error when used in the worksheet. The function essentially wants to look up which sales mechanics (from the called workbook) have been utilized in periods where the spend is greater than zero.

I have other functions working in a similar way that don't return errors. Does anyone have any suggestions?

Function Mechanics(myRange As Range)

    Dim i, j As Integer
    Dim str As String
    Dim wb As Workbook
    Dim checkArray() As Variant
    Dim outputArray() As String
    
    Application.ScreenUpdating = False

    'If no mechanics, leave blank
    If WorksheetFunction.CountIfs(myRange, ">0") = 0 Then
        Mechanics = ""
        Exit Function
    Else
        str = ""
        
        'Loop through range, checking if there has been spend
        For i = 1 To myRange.Cells.Count
            'If there has, add mechanic name to string
            If myRange.Offset(i - 1, 0).Resize(1, 1).Value > 0 Then
                str = str & myRange.Offset(i - 1, 6 - myRange.Column).Resize(1, 1).Value
            End If
        Next i
        
        'Open Mechanic lookup workbook and copy contents to an array
        Set wb = Workbooks.Open("[I]Path to mechanic lookup file[/I]")
        wb.Activate
                
        checkArray = Range("A2:B" & WorksheetFunction.CountA(Range("A:A"))).Value
        
        wb.Close (False)
        
        j = 0
        ReDim outputArray(j)
        
        'Loop through the array lookup column
        For i = 1 To UBound(checkArray, 1)
            'If lookup item is in string, add mechanic to output array
            If InStr(1, str, checkArray(i, 1), 1) > 0 Then
                outputArray(j) = checkArray(i, 2)
                j = j + 1
                ReDim Preserve outputArray(j)
            End If
        Next i
        
        'Return a comma delimited list of mechanics
        For i = LBound(outputArray) To UBound(outputArray) - LBound(outputArray) - 1
            Mechanics = Mechanics & outputArray(i) & ", "
        Next i
        
        Mechanics = Left(Mechanics, Len(Mechanics) - 2)
                        
    End If
    
    Application.ScreenUpdating = True

End Function

 

[Fluff]

Hi & welcome to MrExcel.
As far as I know, you cannot open a workbook in a Function that's called from the sheet

 

[StuH]

Thanks - that would make sense as to the error I'm receiving!

Are there any ways to work around that? Ideally this function would work across a large number of workbooks, that could all then reference a single master lookup sheet. This would help if a new mechanic is introduced, it need only be added to the master lookup rather than retrospectively updating every single product workbook we have.

 

[Fluff]

I suspect that you would have to use a macro, rather than a UDF.

 

[StuH]

I've made a fix that works but I'm not 100% pleased with. Added a helper sheet with links to the master file and now I update links in the function formula and generate the array from the worksheet. It now returns the expected output within the function though.

 

[Fluff]

Glad you sorted it & thanks for the feedback.