How to return value in Excel UDF Function?

Question

I got some code written in a function in VBA, but I'm stuck on how to return the output values areaAnswer1 & areaAnswer2 inside the if - else statments. I'm still new to this. Any help & suggestions are very much appreciated.

Function dateArea(inputDate1 As Date, t1 As Date, t2 As Date, duration As Integer, output As Integer) As Integer
    endOfYear = Workbook.Date(Year(inputDate1), 12, 31)
    inputDate2 = Workbook.Date(Year(inputDate1) + 1, Month(inputDate1), Day(inputDate1))
    endOfDate1 = Workbook.Date(Year(inputDate1) + duration, Month(inputDate1), Day(inputDate1))
    endOfDate2 = Workbook.Date(Year(inputDate2) + duration, Month(inputDate2), Day(inputDate2))
    areaBase1 = endOfYear - inputDate1
    areaBase2 = inputDate2 - endOfYear
    totalArea1 = areaBase1 * 365
    totalArea2 = areaBase2 * 365
    triangleBase1 = endOfDate1 - inputDate1
    triangleHypo1 = Workbook.Sqrt((365 * 365) + (triangleBase1 * triangleBase1))
    triangleBase2 = t1 - inputDate2
    triangleHypo2 = triangleHypo1 * triangleBase2 / triangleBase1
    triangleHeight2 = Workbook.Sqrt((triangleHypo2 * triangleHypo2) - (triangleBase2 * triangleBase2))
    triangleArea2 = (triangleBase2 * triangleHeight2) / 2
    triangleBase3 = (inputDate2 - endOfYear) + (t1 - inputDate2)
    triangleHypo3 = triangleBase3 * triangleHypo2 / (t1 - inputDate2)
    triangleHeight3 = Workbook.Sqrt((triangleHypo3 * triangleHypo3) - (triangleBase3 * triangleBase3))
    triangleArea3 = (triangleBase3 * triangleBaseHeight3) / 2
    areaDiffBot2 = triangleArea3 - triangleArea2
    triangleBase4 = 365 + (t1 - inputDate2)
    triangleHypo4 = triangleBase4 * triangleHeight2 / (t1 - inputDate2)
    triangleHeight4 = Workbook.Sqrt((triangleHypo4 * triangleHypo4) - (triangleBase4 * triangleBase4))
    triangleArea4 = (triangleBase4 * triangleHeight4) / 2
    areaDiffBot1 = triangleArea4 - triangleArea3
    triangleHeight5 = 365 * (endOfDate1 - t2) / triangleBase1
    triangleHypo5 = Workbook.Sqrt((triangleHeight5 * triangleHeight5) + ((endOfDate1 - t2) * (endOfDate1 - t2)))
    triangleArea5 = (endOfDate1 - t2) * triangleHeight5 / 2
    triangleBase6 = (endOfDate1 - t2) + areaBase1
    triangleHeight6 = (triangleBase6) * 365 / (endOfDate1 - t2)
    triangleHypo6 = Workbook.Sqrt((triangleBase6 * triangleBase6) + (triangleHeight6 * triangleHeight6))
    triangleArea6 = (triangleBase6 * triangleHeight6) / 2
    areaDiffTop1 = triangleArea6 - triangleArea5
    triangleBase7 = triangleBase6 + areaBase2
    triangleHeight7 = triangleBase7 * triangleHeight6 / triangleBase6
    triangleHypo7 = Workbook.Sqrt((triangleBase7 * triangleBase7) + (triangleHeight7 * triangleHeight7))
    triangleArea7 = (triangleBase7 * triangleHeight7) / 2
    areaDiffTop2 = triangleArea7 - triangleArea6
    totalUsedArea1 = areaDiffTop1 + areaDiffBot1
    totalUsedArea2 = areaDiffTop2 + areaDiffBot2
    areaAnswer1 = totalArea1 - totalUsedArea1
    areaAnswer2 = totalArea2 - totalUsedArea2
    If output = 1 Then

    ElseIf output = 2 Then

    ElseIf output = 3 Then

    Else

    End If
End Function

Oh, yeah. Sorry, I'll fix that.

Ian Clement Fosgate
– Ian Clement Fosgate

2016-07-02 09:39:38 +00:00
Commented Jul 2, 2016 at 9:39 — Ian Clement Fosgate
– Ian Clement Fosgate, Commented Jul 2, 2016 at 9:39

trincot · Accepted Answer · 2016-07-02 09:39:38Z

2

In VBA you set the return value by assigning it to the function like this:

areaAnswer1 = totalArea1 - totalUsedArea1
areaAnswer2 = totalArea2 - totalUsedArea2
If output = 1 Then
    dateArea = areaAnswer1
ElseIf output = 2 Then
    dateArea = areaAnswer2
ElseIf output = 3 Then
    ' ...etc

Note that the assignment to the function does not exit the function. In this case you don't need that immediate exit, as you are already at the end of it. But in some cases you'll want to exit the function as soon as you have assigned the return value:

Exit Function

answered Jul 2, 2016 at 9:39

trincot

357k38 gold badges282 silver badges339 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user4039065 · Accepted Answer · 2016-07-02 09:40:35Z

2

Just have the function equal a typed value or one of the variables at the end.

select case output
    case 1
        dateArea = totalUsedArea1 
    case 2
        dateArea = totalUsedArea2 
    case 3
        dateArea = totalUsedArea3 
    case else
        'do something or nothing
end select

Note that you have specified returning an integer which cannot contain a decimal and must be smaller than 32667 (or there abouts).

answered Jul 2, 2016 at 9:40

user4039065

1 Comment

user1016274 Over a year ago

16 bits integer -> 2^16 = 32768.

Collectives™ on Stack Overflow

How to return value in Excel UDF Function?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related