Apply variables to function (1 Viewer)

Drand

Registered User.
Local time
Tomorrow, 08:46
Joined
Jun 8, 2019
Messages
100
Hi

I am developing a budget model.

In one area there are 3 alternative approaches to calculating budget income for the year.

It is one of these options that has me stumped. It works on a calculation of income per day (all days are the same) x the number of working days in each month. I found a useful function to calculate the working days as follows;

Code:
Public Function CalcWorkdays(StartDate, EndDate) As Integer

   Dim LTotalDays As Integer
   Dim LSaturdays As Integer
   Dim LSundays As Integer

   On Error GoTo Err_Execute

   CalcWorkdays = 0

   If IsDate(StartDate) And IsDate(EndDate) Then
      If EndDate <= StartDate Then
         CalcWorkdays = 0
      Else
         LTotalDays = DateDiff("d", StartDate - 1, EndDate)
         LSaturdays = DateDiff("ww", StartDate - 1, EndDate, 7)
         LSundays = DateDiff("ww", StartDate - 1, EndDate, 1)

         'Workdays is the elapsed days excluding Saturdays and Sundays
         CalcWorkdays = LTotalDays - LSaturdays - LSundays

      End If

   End If

   Exit Function

Err_Execute:
   'If error occurs, return 0
   CalcWorkdays = 0

End Function

I would like to be able to call this function into my form code where StartDate and EndDate are variables determined from other input.

Is this possible?

Many thanks
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:46
Joined
May 7, 2009
Messages
16,393
you can add an Unbound textbox (txtCalc) on your form that will received the
result from the function.

you add code (AfterUpdate event) to each textbox (startDate/endDate):

Code:
private sub startDate_AfterUpdate()
Call fnCalc()
end sub

private sub endDate_AfterUpdate()
Call fnCalc()
end sub

private function fnCalc()
Dim var As Variant
var = Null
If IsDate([startDate]) And IsDate([endDate]) Then
    var = CalcWorkdays([startDate], [endDate]) * [incomePerDayField]
End If
Me!txtCalc = var
end function
 

Drand

Registered User.
Local time
Tomorrow, 08:46
Joined
Jun 8, 2019
Messages
100
Thank you so much for that. Perfect!
 

Users who are viewing this thread

Top Bottom