VBA – User Designed Functions (UDF) - Renaming array functions

**hbsonly** · 07-03-2013, 11:59 AM

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.

**MrShorty** · 07-03-2013, 12:40 PM

Per forum rules, you should wrap your function code in code tags. The forum rules button at the top of the page will explain how to if needed.

The #Value error indicates a significant failure in the process of calling the function. In particular, the code calls for three arguments to the function, but the function call (with the mismatched parantheses and all) is only sending one argument to the function. This will prevent the function from executing and will trigger the value error. The function call needs to look like specialfunctionx(g12:g45,g46,0).

I don't see a simple Find/Replace being able to do make this kind of substitution, because each find/replace will need to replace the entire text of the function. It will not work to simply replace a part of the function text.

In many ways, I don't see a UDF being all that useful for this kind of thing. What is your reason for wanting to incorporate the UDF into these spreadsheets?

**hbsonly** · 07-03-2013, 01:31 PM

Thank you for letting me know of the forum rules. I still have a lot to learn about excel and my knowledge of VBA is severely limited. My employer, wants me to be able to rename array functions. However, in this case I am merely calling it SpecialFunctionX and SpecialFunctionY because the functions haven't been given permanent names yet.

I know it's a lot to ask, but if someone could provide me with a VBA code I can copy and paste and have it function appropriately and easily that would be greatly appreciated.

Thanks to any and all who can help, this is my first request on this forum and I hope I will be able to return the favor to other forum members soon.

**shg** · 07-03-2013, 01:38 PM

At a glance, the VBA is fine (if of limited value), it's the formula, as MrShorty pointed out:

=SpecialFunctionX(G12:G45, G46, 0)

**hbsonly** · 07-03-2013, 01:47 PM

Ok assuming the VBA code is correct, how would I restructure the following

=MAX(SUM(G7:G21)-G23,0) to become =SpecialFunctionX
and
=MIN(SUM(G7:G21),15) to become =SpecialFunctionY The 15 represents the capped total of hours an individual can work.

I'm assuming my formula structure is off somewhere.

Originally Posted by shg

At a glance, the VBA is fine (if of limited value), it's the formula, as MrShorty pointed out:

=SpecialFunctionX(G12:G45, G46, 0)

**shg** · 07-03-2013, 01:55 PM

I don't understand your question. You have to change the formula from what you have to what MrShorty and I showed you.

**hbsonly** · 07-03-2013, 02:00 PM

Thanks for the help, it actually pushed me in the right direction I figured it out, more or less. I might have to ask more questions later, but thanks for now. I'm extremely grateful.