Function SpecialFunctionX(RngData As Range, dblSubtractItem As Double, dblConstant As Double) As Double
With Excel.Application
SpecialFunctionX = .Max(.Sum(RngData) - dblSubtractItem, dblConstant)
End With
End Function


Function SpecialFunctionY(RngData As Range, dblConstant As Double) As Double
With Excel.Application
SpecialFunctionY = .Min(.Sum(RngData), dblConstant)
End With
End Function


Good day, beautiful people of the forum,

I recently tested the VBA code above and it returns an error, #value!

To give you a better idea of what I’m doing, I basically just copied and pasted the VBA code provided to me by an acquaintance, into excel, clicked on Visual Basic > Insert> Module. I have a workbook where I use =MAX(SUM and=MIN(SUM many times. The only thing that changes is the range.

I basically went in to all my cells deleted =MAX(SUM and=MIN(SUM then replacing them with =SpecialFunctionX and =SpecialFunctionY while keeping all existing ranges like (G12:G45)-G46,0), (G12:G45),15) , (G50:G88)-G89,0) (G50:G88),15) .etc

So instead of =MAX(SUM(G12:G45)-G46,0) it would be =SpecialFunctionX(G12:G45)-G46,0).
And instead of =MIN(SUM(G12:G45),15) it would be =SpecialFunctionY(G12:G45),15)

I also use ranges like =MAX(SUM(F11,F12,F13)-F14,0) or =MIN(SUM(F35,G35,H35),15).

I want to be able to go into my existing cells after pasting the VBA code then swap out the existing text =Max(Sum for =SpecialFunctionX and =Min(Sum for =SpecialFunctionY and have them work like the original array function.

Thank you all, any assistance would be greatly appreciated.