#VALUE! error when calling UDF function on worksheet

Question

I am trying to create a function that will calculate the Total quantity of a product with number "pn001" and minus the sum in another sheet, the answer should be displayed in sheet where the function is called

Function salio() As Long

    Dim Sumact As Range
    Dim Sumact1 As Range
    Dim Remain As Long
    Dim RR As Long
    Dim R As Long

    Set Sumact = Sheets(1).Range("A1")
    Set Sumact1 = Sheets("SALES").Range("A1")
    Sheets("STOCK-OUT").Select
    Sumact = Application.SumIf(Range("C3:C5"), "pn001", Range("E3:E5"))
    RR = CLng(Sumact)
    Sheets("SALES").Select
    Sumact1 = Application.SumIf(Range("D2:D5"), "pn001", Range("F2:F5"))
    Remain = CLng(Sumact1)
    R = RR - Remain
    salio = R
End Function

All I get is the #VALUE! error when I attempt to use this on a worksheet.

**What is your question??** You already have some code. Does it work?? — Gary's Student, Apr 17 '15 at 10:57
Do you really need a UDF to do this? Your code is just imitating what Excel's native formulas can already do. Surely you can accomplish this without any VBA? — SierraOscar, Apr 17 '15 at 11:48

score 0 · Accepted Answer · edited May 23 '17 at 11:50

If you have this code in a Module code sheet and not a worksheet code sheet, then I suspect that your problems have to do with using .Select in function. The Select method can be used in macro Subs, but you cannot be switching around worksheets in a function to be used on a worksheet. You also cannot put values into any other cells that the one that the UDF function resides in.

Function salio() As Long
    'Dim Sumact As Range   you cannot set these to a range to receive a sumif
    'Dim Sumact1 As Range   you cannot set these to a range to receive a sumif
    Dim Sumact As Long
    Dim Sumact1 As Long
    Dim Remain As Long
    Dim RR As Long
    Dim R As Long

    'Set Sumact = Sheets(1).Range("A1")   you cannot set these to a range to receive a sumif
    'Set Sumact1 = Sheets("SALES").Range("A1")   you cannot set these to a range to receive a sumif
    With Sheets("STOCK-OUT")  'do not .Select - Use With ... End With to reference another worksheet
        Sumact = Application.SumIf(.Range("C3:C5"), "pn001", .Range("E3:E5"))
    End With
    RR = CLng(Sumact)
    With Sheets("SALES")  'do not .Select - Use With ... End With to reference another worksheet
        Sumact1 = Application.SumIf(.Range("D2:D5"), "pn001", .Range("F2:F5"))
    End With
    Remain = CLng(Sumact1)
    R = RR - Remain
    salio = R
End Function

I've left some dead code in there as well as several comments. You will also want to review How to avoid using Select in Excel VBA macros.

#VALUE! error when calling UDF function on worksheet

1 Answers1