How can we tell converttocurrency what quantities of notes and coins are available?

[Max Bialystock]

Here's an ingeious user defined array function written by Chip Pearson.


Function ConvertToCurrency(ByVal Amt As Double) As Variant
Dim Ndx As Integer
Dim Counter As Integer
Dim Arr As Variant
Arr = Array(100, 50, 20, 10, 5, 1, 0.25, 0.1, 0.05, 0.01)
For Ndx = LBound(Arr) To UBound(Arr)
Counter = 0
While (Amt + 0.001) >= Arr(Ndx)
Counter = Counter + 1
Amt = Amt - Arr(Ndx)
Wend
Arr(Ndx) = Counter
Next Ndx
ConvertToCurrency = Arr
End Function


It converts a dollar&cents value to the number of 100 dollar bills, 50s,
20s,
10s, 5s, 1s, 25c, 10c, 5c, and pennies.

Put a value in A1 and then select
A2:J2 and type this in:
=converttocurrency(a1) and hit control-shift-enter.



But suppose the actual coins and notes available are limited in their
quantities.

For example, suppose in the notes and coins on hand there is only one $1
bill available.

If the sum to be split up is $3.

We need a result like: $1 x 1, 25c x 8.

How can we tell converttocurrency what quantities of notes and coins are
available?

 

[Tom Ogilvy]

Function ConvertToCurrency(ByVal Amt As Double) As Variant
Dim Ndx As Integer
Dim Counter As Integer
Dim Arr As Variant
Arr = Array(100, 50, 20, 10, 5, 1, 0.25, 0.1, 0.05, 0.01)
' Available quantities of the above
arr1 = Array(0, 0, 0, 0, 0, 1, 20, 20, 5, 8)
For Ndx = LBound(Arr) To UBound(Arr)
Counter = 0
While (Amt + 0.001) >= Arr(Ndx) And Counter + 1 <= arr1(Ndx)
Counter = Counter + 1
Amt = Amt - Arr(Ndx)
Wend
Arr(Ndx) = Counter
Next Ndx
ConvertToCurrency = Arr
End Function

 

[Max Bialystock]

Thanks Tom.

Is it possible to populate arr1 from a range in Sheet1?

Max

 

[Tom Ogilvy]

Function ConvertToCurrency(ByVal Amt As Double, rng as Range) As Variant
Dim Ndx As Integer
Dim Counter As Integer
Dim Arr As Variant
Dim Arr1 as Variant, i as Long
Arr = Array(100, 50, 20, 10, 5, 1, 0.25, 0.1, 0.05, 0.01)
Arr1 = Arr
i = lbound(arr)
for each cell in rng
Arr1(i) = cell.value
i = i + 1
Next
' Available quantities of the above
'arr1 = Array(0, 0, 0, 0, 0, 1, 20, 20, 5, 8)
For Ndx = LBound(Arr) To UBound(Arr)
Counter = 0
While (Amt + 0.001) >= Arr(Ndx) And Counter + 1 <= arr1(Ndx)
Counter = Counter + 1
Amt = Amt - Arr(Ndx)
Wend
Arr(Ndx) = Counter
Next Ndx
ConvertToCurrency = Arr
End Function

 

[Max Bialystock]

Sorry Tom, I don't fully understand.
If the available quantities are in the Range("A3:J3")

 

[Tom Ogilvy]

select 10 cells in the same row,
in the formula bar enter

=ConvertToCurrency(B5,A3:J3)

commit with Ctrl+shift+Enter rather than just enter.

B5 contains the amount in dollars and cents (3 in your example)

A3:J3 holds the quantities corresponding to $100, $50, $20, etc

Worked fine for me.

 

[Tom Ogilvy]

If calling from VBA

Dim v as variant

v = ConvertToCurrency(Range("B5").Value, Range("A3:J3"))

 

[Max Bialystock]

Tom it works beautifully, thank you.
What I didn't understand was that I had to put the range in the brackets.
Max

 

[Guest]

It is best to put dependencies in the argument of the function. That is how
Excel determines that it needs to recalculate the function when a cell upon
which it is dependent is changed. If I just hardcoded a range in the
function itself, then changing the quantity available would not cause the
function to recalculate.