Hi,
I have written a user defined function that provides an error (#value) when called from excel (excel 2016). I have written a test procedure to test whether the function returns the expected output and this works fine. However calling the function from a worksheet provides the #value error. I have checked the range and use it as an array function (CTRL+SHIFT+ENTER). It seems that the function isn't called because setting a breakpoint in the function and pressing F9 doesn't provide any response. I have checked everything I could think of (input ranges, automatic calculation turned on, etc.) but I can't figure out why the function doesn't work. I hope some one can help me out!
Please find below the function
the code below provides the test function which provides the expected results
I have written a user defined function that provides an error (#value) when called from excel (excel 2016). I have written a test procedure to test whether the function returns the expected output and this works fine. However calling the function from a worksheet provides the #value error. I have checked the range and use it as an array function (CTRL+SHIFT+ENTER). It seems that the function isn't called because setting a breakpoint in the function and pressing F9 doesn't provide any response. I have checked everything I could think of (input ranges, automatic calculation turned on, etc.) but I can't figure out why the function doesn't work. I hope some one can help me out!
Please find below the function
Code:
Public Function cashreceipt(percentagePaidUpfront As Variant, finalPaymentWeeksBeforeLeave As Variant, line As Variant, monthOfYear As Variant, orderValues As Variant, bookingDuration As Variant, weekDistribution As Variant) As Variant
'Determine the boundaries of all input variants
Dim ppuC, ppuR As Long
Dim fpwblC, fpwblR As Long
Dim moyC, moyR As Long
Dim ovC, ovR As Long
Dim bdC, bdR As Long
Dim wdC, wdR As Long
ppuC = UBound(percentagePaidUpfront, 2)
ppuR = UBound(percentagePaidUpfront, 1)
fpwblC = UBound(finalPaymentWeeksBeforeLeave, 2)
fpwblR = UBound(finalPaymentWeeksBeforeLeave, 1)
moyC = UBound(monthOfYear, 2)
moyR = UBound(monthOfYear, 1)
ovC = UBound(orderValues, 2)
ovR = UBound(orderValues, 1)
bdC = UBound(bookingDuration, 2)
bdR = UBound(bookingDuration, 1)
wdC = UBound(weekDistribution, 2)
wdR = UBound(weekDistribution, 1)
'Check whether the size of the variants match
'Dim message As String
'
'If ppuC <> fpwblC Or ppuR <> fpwblR Then
' message = MsgBox("Size of PercentagePaidUpfront and finalPaymentWeeksBeforeLeave must be equal", vbOKOnly + vbCritical)
' Exit Function
'ElseIf moyC <> ovC Then
' message = MsgBox("The number of columns of monthsofYear and orderValues must be equal", vbOKOnly + vbCritical)
' Exit Function
'ElseIf ppuR <> 1 Or fpwblR <> 1 Then
' message = MsgBox("PercentagePaidUpfront and finalPaymentWeeksBeforeLeave must be a single line vector", vbOKOnly + vbCritical)
' Exit Function
'End If
'Determine output (note that the size of the output variant should be similar to the size of the Order values range)
Dim cb As Variant
ReDim cb(1 To ovR, 1 To ovC)
'Determine for each order size the applicable cash distribution
''Supporting variables
Dim a, b, c, d, e, f, g As Long
Dim noMonths As Long
Dim noWeeks As Long
Dim noWeeksremaining As Long
Dim noWeeksSupp As Long
Dim repMonth As Long
Dim immediatePaymentPerc As Double
Dim finalPaymentWeek As Double
Dim immediatePayment As Double
Dim orderValue As Double
Dim wd() As Variant
Dim dis() As Variant
Dim counter As Long
For a = 1 To ovR 'Process per line (i.e. all calculations are performed by line (row))
For b = 1 To ovC 'Process an individual ordervalue
repMonth = monthOfYear(1, b) 'applicable reporting month
noWeeks = bookingDuration(a, repMonth + 1) 'max number of weeks that an ordervalue is outstanding
noMonths = Application.WorksheetFunction.Max(Application.WorksheetFunction.RoundUp(noWeeks / 4, 0), 1) 'Determine the number of months in the projection
ReDim wd(1 To wdR, 1 To 1) 'temporary variant containing the bookingordervalues per week (i.e. monthly bookingorder value is translated into a weekly bookingorder distribution)
ReDim dis(1 To wdR, 1 To noMonths) 'temporary variant containing the cashdistribution of the weekly booking order values
immediatePaymentPerc = percentagePaidUpfront(1, repMonth) 'Retrieve the immediate payment percentage (i.e. the percentage of the OrderValue that is paid immediately)
finalPaymentWeek = finalPaymentWeeksBeforeLeave(1, repMonth) 'Retrieve finalPaymentweek value (i.e. the week (compared to the startdate of the journey) when the final payment is made
orderValue = orderValues(a, b) 'Ordervalue for the respective line and month
immediatePayment = immediatePaymentPerc * orderValue 'Determine the value that is paid immediately
For c = 1 To wdR 'determine the weekly distribution (input for the cash distribution)
wd(c, 1) = (1 - immediatePaymentPerc) * orderValue * weekDistribution(c, 2)
Next c
For d = 1 To wdR 'Allocate the weekly order distribution to monthly buckets (note that it is assumed that all months contain 4 weeks
noWeeksremaining = d + noWeeks - finalPaymentWeek
For e = 1 To noMonths
If e = 1 Then
noWeeksSupp = 4
Else
noWeeksSupp = e * 4
End If
If noWeeksremaining <= noWeeksSupp Then
dis(d, e) = wd(d, 1)
Exit For
End If
Next e
Next d
For f = 1 To noMonths 'Allocate all results to the output variant
If f = 1 Then counter = b Else counter = counter + 1 'Supporting variable to dermine the allocation position in the output variant
For g = 1 To wdR
If counter <= ovC Then
If f = 1 And g = 1 Then cb(a, counter) = cb(a, counter) + immediatePayment
cb(a, counter) = cb(a, counter) + dis(g, f)
Else
Exit For
End If
Next g
Next f
Next b
Next a
cashreceipts = cb
End Functionthe code below provides the test function which provides the expected results
Code:
Sub test()
Dim percentagePaidUpfront As Variant
Dim finalPaymentWeeksBeforeLeave As Variant
Dim line As Variant
Dim monthOfYear As Variant
Dim orderValues As Variant
Dim bookingDuration As Variant
Dim weekDistribution As Variant
Dim tmp As Variant
percentagePaidUpfront = Range("UpfrontPayment").Value
finalPaymentWeeksBeforeLeave = Range("FinalPayment").Value
line = Sheets("Bookings&Revenue").Range("B11:B24").Value
monthOfYear = Sheets("Bookings&Revenue").Range("e5:ef5").Value
orderValues = Sheets("Bookings&Revenue").Range("e11:ef24").Value
bookingDuration = Range("bookingDuration").Value
weekDistribution = Range("WeekDistribution").Value
ReDim tmp(1 To 14, 1 To 132)
tmp = cashreceipt(percentagePaidUpfront, finalPaymentWeeksBeforeLeave, line, monthOfYear, orderValues, bookingDuration, weekDistribution)
End Sub
