UDF works in debug mode but it doesn't give value into the cell-CodePudding

I'm working with a VBA Excel UDF for the first time. My target is to create something similar to "SumIf" but that gets secondary criteria by a list of item (wrote into a cell and separated by a "|").

I've already tested this UDF by a call back sub , to go through step by step process, and here it works fine (this is the test sub: MsgBox SommaSeIncludo(Range("A:A"), Range("B:B"), Range("B1"), Range("C:C"), "D1") but, I don't know why, if I put the formula into the cell it shows only a "value" error ( =SommaSeIncludo(A:A;B:B;B1;C:C;D1) ). Do you have some idea?

This is my code:

Function SommaSeIncludo(ByVal intSomma As Range, ByVal intCriteri1 As Range, ByVal criteri1 As Range, ByVal intCriteri2 As Range, ByVal listaCriteri2 As String) As String

    Dim listaCriteri2Array As Variant
    Dim numberOfListaCriteri2ArrayItems As Integer
    Dim thisItemCriteria As String
    Dim subTotal As Double
    Dim total As Double
    Dim thisSheet As Worksheet
    
    Set thisSheet = ThisWorkbook.Sheets("MySheet")
    
    total = 0
    listaCriteri2Array = Split(thisSheet.Range(listaCriteri2).Value, "|")
    numberOfListaCriteri2ArrayItems = UBound(listaCriteri2Array) - LBound(listaCriteri2Array)
    
    For i = 0 To numberOfListaCriteri2ArrayItems
        subTotal = 0
        thisItemCriteria = listaCriteri2Array(i)
              
        subTotal = WorksheetFunction.SumIfs(intSomma, intCriteri1, criteri1.Value, intCriteri2, thisItemCriteria)
        
        
        total = subTotal   total
    Next
    
    SommaSeIncludo = total
 
End Function

CodePudding user response：

Following suggestions in the comments:

Short answer is you can get it work by making D1 into a string when you call the function:

=SommaSeIncludo(A:A;B:B;B1;C:C;"D1")

But probably better to change it to a range by changing the first line of the function to

Function SommaSeIncludo(ByVal intSomma As Range, ByVal intCriteri1 As Range, ByVal criteri1 As Range, ByVal intCriteri2 As Range, ByVal listaCriteri2 As Range) As Long

If you want it to reference D1 in a specified sheet, I only know the slightly long-winded construction in the Split statement to convert the range to a string, concatenate it with the sheet name, and convert back to a range:

 listaCriteri2Array = Split(Range("MySheet" & "!" & listaCriteri2.Address).Value, "|")

See

Maybe it's better not to tie it to a particular sheet though, as you can always put MySheet!D1 in the function call, and leave the Split line as

listaCriteri2Array = Split(listaCriteri2.Value, "|")