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

Question

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

You can use the debugger even if the function is used as UDF: Simply set a breakpoint to the first executable line `Set thisSheet...` . Now you have to force Excel to calculate: Either modify anything in the ranges you pass as parameter, or enter `ActiveSheet.EnableCalculation = False` and then `ActiveSheet.EnableCalculation = True` into the immediate window. — FunThomas, Mar 30 '22 at 11:18
Data sample (as pastable text) to allow reproduction of your problem would be useful. Also, what line returns the error? (*Start by putting breakpoints on the lines after the `DIM` statements and see how far it gets*) — Ron Rosenfeld, Mar 30 '22 at 11:19
Are you using the formula in the same workbook as the code? Also you have the return type as `String`, shouldn't it be `Long`? Also also, in your formula, you're passing in a range as the 4th parameter - not a string, you might want to explicitly convert that in your code. — SierraOscar, Mar 30 '22 at 11:22

Tom Sharpe · Accepted Answer · 2022-03-30T12:13:30.450

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, "|")

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

1 Answers1