UDF works in sub but not in sheet

StuH

New Member
Joined
Feb 1, 2021
Messages
3
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?

VBA Code:
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
As far as I know, you cannot open a workbook in a Function that's called from the sheet
 

StuH

New Member
Joined
Feb 1, 2021
Messages
3
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
I suspect that you would have to use a macro, rather than a UDF.
 
Solution

StuH

New Member
Joined
Feb 1, 2021
Messages
3
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,048
Messages
5,639,769
Members
417,111
Latest member
buyukbang

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top