PDA

View Full Version : Sleeper: UDF - show partial results in formula palette



Felix Atagong
08-25-2005, 04:20 AM
(this item has also been posted on MrExcel.com, but no answers there)

I have a UDF that calculates a certain percentage (= profit) on top of the given amount, but in between a minimum and maximum (Excel2000).

When using the Formula button the so-called Formula Palette opens and people can fill in the amounts in the right boxes. But on built-in functions partial results show when entering a box, but this doesn't happen in my UDF.

Example (from what I would like to see to appear in the formula palette):

Amount: 100 = 100
Minimum: 10 = 110 This just shows 10, not 110.
Percentage: 50 = 150 This shows 50, not 150.
Maximum: 30 = 130 This shows 30 not 130.

The final result is 130 (this does happen in my UDF).
Any suggestions?




Public Function MinPerMax( _
Amount As Double, _
Optional Minimum As Double, _
Optional Percentage As Double, _
Optional Maximum As Double) _
As Variant
If IsMissing(Amount) Or Amount = Empty Then
MinPerMax = 0
Else
Dim Resultaat(1 To 3) As Double
If IsMissing(Minimum) Or Minimum = Empty Then
Minimum = 0
End If
Resultaat(1) = Amount + Minimum
If IsMissing(Percentage) Or Percentage = Empty Then
Percentage = 0
End If
Resultaat(2) = Amount * (100 + Percentage) / 100
If IsMissing(Maximum) Or Maximum = Empty Then
Maximum = 9999999
End If
Resultaat(3) = Amount + Maximum
If Resultaat(1) > Resultaat(2) Then
MinPerMax = Resultaat(1)
Else
MinPerMax = Resultaat(2)
End If
If MinPerMax > Resultaat(3) Then
MinPerMax = Resultaat(3)
End If
End If
If Minimum > Maximum Then
MinPerMax = "ERROR: max < min"
End If
End Function

Justinlabenne
08-25-2005, 05:41 AM
Are you wanting to see the functions resultant output updating at the bottom of the Formula Pallete dialog evey time you add or change a value in the arguments?

It appears to be doing so for me in Version 2002 (xp). Can't say in 2000, don't have it at home to test.

Felix Atagong
08-25-2005, 07:26 AM
Not at the bottom, it already happens there, but at the side of each box...
So if I put 10 in the minimum box I would like to see 110 (= amount + minimum) at the right hand side of the minimum box.
Right from the percentage box I would like to see 150 (= amount + 50% on top).
At the maximum box 130 (amount + maximum).
Possible or not ?