Udf with paramarray argument

[HPE]

OBJECTIVE: To create a UDF to calculate forward month’s supply of inventory. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
FORWARD_MONTHS_SUPPLY (CURRENT_INVENTORY, FUTURE_SALES)<o></o>
<o></o>
The UDF will need to be able to handle multiple non-contiguous ranges of various sizes for the FUTURE_SALES argument. The intent here is to allow a continuous calculation when a jump from one range of sales values to another is non-contiguous. Also, if the UDF is used within the last range of data, a historical range(s) of sales values could be entered to supply the UDF with sufficient data to calculate an actual number instead of returning a value of “> <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:metricconverter w:st="on" ProductID="2”">2”</st1:metricconverter>, for example. In attempt to accomplish this, I have set the FUTURE_SALES argument as a paramarray. <o></o>
<o></o>
ISSUES: <o></o>
I have tried to pass the FUTURE_SALES paramarray argument through a union function and then calculate the UDF based on the array resulting from the union. The union function works as I can see the list of values in the resulting array, however, I have been unable to calculate the UDF when using the union array.<o></o>
<o></o>
When using the FUTURE_SALES argument as is, the UDF will not calculate correctly if sales values are not selected with enough quantity to calculate an actual number. This is the how the code is currently set up.<o></o>
<o></o>
I am unsure of the best way to try to move forward. Any guidance would be greatly appreciated.<o></o>
<o></o>
HPE <o></o>

Function FORWARD_MONTHS_SUPPLY(CURRENT_INVENTORY As Variant, ParamArray FUTURE_SALES() As Variant) As Variant
Dim X As Variant
Dim A As Variant
Dim B As Variant
Dim C As Variant
Dim I As Integer
Dim E As Long
Dim CELL As Variant
Dim TOTAL As Variant
Dim TOTALB As Variant
Dim COUNTER As Integer
X = Abs(CURRENT_INVENTORY)
I = 1

Do

For E = LBound(FUTURE_SALES) To UBound(FUTURE_SALES)
For Each CELL In FUTURE_SALES(E)
TOTAL = TOTAL + CELL.Value
COUNTER = COUNTER + 1
TOTALB = FUTURE_SALES(E).Cells(COUNTER).Value
If TOTAL >= X Then Exit For
Next CELL
If TOTAL >= X Then Exit For
Next E

A = TOTAL
B = TOTALB
C = COUNTER

If X = A Then
FORWARD_MONTHS_SUPPLY = C
Exit Function
ElseIf X < A Then
FORWARD_MONTHS_SUPPLY = (C - 1) + ((B - (A - X)) / B)
Exit Function
End If

I = I + 1

Loop Until I > C

FORWARD_MONTHS_SUPPLY = "> " & C

End Function
Function FUTURE_SALES_UNION(ParamArray FUTURE_SALES() As Variant) As Variant
Dim FSALES() As Variant
Dim A As Long
Dim ITEM As Variant
Dim COUNTER As Long
For A = 0 To UBound(FUTURE_SALES)
FUTURE_SALES(A) = FUTURE_SALES(A)
If IsArray(FUTURE_SALES(A)) Then
For Each ITEM In FUTURE_SALES(A)
COUNTER = COUNTER + 1
ReDim Preserve FSALES(1 To COUNTER) As Variant
FSALES(COUNTER) = ITEM
Next ITEM
Else
COUNTER = COUNTER + 1
ReDim Preserve FSALES(1 To COUNTER) As Variant
FSALES(COUNTER) = FUTURE_SALES(A)
End If
Next A
FUTURE_SALES_UNION = FSALES()
End Function

 

[Greg Truby]

This looks like homework, so I'm not going to solve it for you. What I will tell you is that I think your problem lies here

FUTURE_SALES(E).Cells(COUNTER).Value

Set watches on B and TotalB and perhaps on FUTURE_SALES(E).Cells(COUNTER).address and step through the code.

Oh, the range that I passed in to study was like so:

• =FORWARD_MONTHS_SUPPLY(C2,D2,D4:D5)

 

[HPE]

Thank you, Greg. The UDF works as intended with your suggested changes.

HPE

Function FORWARD_MONTHS_SUPPLY(CURRENT_INVENTORY As Variant, ParamArray FUTURE_SALES() As Variant) As Variant
Dim X As Variant
Dim A As Variant
Dim B As Variant
Dim C As Variant
Dim I As Integer
Dim E As Long
Dim CELL As Variant
Dim TOTAL As Variant
Dim TOTALB As Variant
Dim COUNTER As Integer
Dim COUNTERB As Integer
Dim BOUND_COUNTER As Integer
X = Abs(CURRENT_INVENTORY)
I = 1

Do

For E = LBound(FUTURE_SALES) To UBound(FUTURE_SALES)
BOUND_COUNTER = 0
For Each CELL In FUTURE_SALES(E)
TOTAL = TOTAL + CELL.Value
COUNTER = COUNTER + 1
BOUND_COUNTER = BOUND_COUNTER + 1
TOTALB = FUTURE_SALES(E).Cells(BOUND_COUNTER).Value
If TOTAL >= X Then Exit For
Next CELL
If TOTAL >= X Then Exit For
Next E

A = TOTAL
B = TOTALB
C = COUNTER

If X = A Then
FORWARD_MONTHS_SUPPLY = C
Exit Function
ElseIf X < A Then
FORWARD_MONTHS_SUPPLY = (C - 1) + ((B - (A - X)) / B)
Exit Function
End If

I = I + 1

Loop Until I > C

FORWARD_MONTHS_SUPPLY = "> " & C

End Function

 

[HPE]

Just in case someone needs to use this UDF, I made a few final tweaks.

HPE

Function FORWARD_MONTHS_SUPPLY(CURRENT_INVENTORY As Variant, ParamArray FUTURE_SALES() As Variant) As Variant
Dim X As Variant, A As Variant, B As Variant, C As Variant, D As Variant
Dim E As Long
Dim CELL As Variant, TOTAL As Variant, TOTAL_B As Variant
Dim CELLCOUNTER As Integer, COUNTER As Integer, BOUND_COUNTER As Integer

X = Abs(CURRENT_INVENTORY)

For E = LBound(FUTURE_SALES) To UBound(FUTURE_SALES)
For Each CELL In FUTURE_SALES(E)
CELLCOUNTER = CELLCOUNTER + 1
Next CELL
Next E

For E = LBound(FUTURE_SALES) To UBound(FUTURE_SALES)
BOUND_COUNTER = 0
For Each CELL In FUTURE_SALES(E)
TOTAL = TOTAL + CELL.Value
COUNTER = COUNTER + 1
BOUND_COUNTER = BOUND_COUNTER + 1
TOTAL_B = FUTURE_SALES(E).Cells(BOUND_COUNTER).Value
If TOTAL >= X And TOTAL <> 0 Then Exit For
Next CELL
If TOTAL >= X And TOTAL <> 0 Then Exit For
Next E

A = TOTAL
B = TOTAL_B
C = COUNTER
D = CELLCOUNTER

If X = A Then
FORWARD_MONTHS_SUPPLY = C
Exit Function
ElseIf X < A Then
FORWARD_MONTHS_SUPPLY = (C - 1) + ((B - (A - X)) / B)
Exit Function
Else
FORWARD_MONTHS_SUPPLY = "> " & D
End If

End Function