# Passing variable to function in VBA module - Resolved

**ATNO/TW**- Super Moderator
- Posts: 23473
- Loc: Woodbridge VA

I'm writing a fairly basic application to automate vacation acrual rates based on years of service and then calculate current year-to-date acrued vacation time based on the employees acrual rate.

I've got the calculation for the length of employment working just fine with this function using DateDiff:

This is in it's own module and is called in an Access 2000 Form by this procedure call:

=YearsOfEmployment([date_of_hire])

(date_of_hire is the value stored in the employee_info table)

This works just fine and returns the current length of employment in years. Now here's where I'm stuck. To determine the employees acrual rate I need to evaluate how many years they have in and assign them the acrual rate they are entitled to. I've tried a variety of ideas, but I think this comes very close, but I'm missing something somewhere. I'm sure it's because the YearsOfEmployment variable is in a different function, and that's where I'm stuck -- getting that calculated result passed into this function:

Like I said, I know this really can't be all that complicated to do, but I'm just a beginner whose figuring this stuff out as I go. (I'm only halfway through the VBA book I'm using and still have to work my way through the 2 Volume Access Developer's Handbooks *lol)

Once this part is solved the next step is to multiply the Acrual rate determined in the above procedure by the number of months already past in any given year. I already have the function that determines the number of months working just fine but I'm using an expression and I suspect I'm going to need to change that to a function similar to what I did for YearsOf Employment. THis is the expression I'm using that works

I think I can change that to a function OK, but I think I'm going to come up stuck again trying to get the result for Acrual to be used to calculate the number of vacation hours acrued year-to-date.

Any help would be much appreciated. I've been trying to get this to work for over 8 hours between the last three days. I figured that was enough effort on my own and time to ask for help. I'm probably making it more complicated than it really is.

I've got the calculation for the length of employment working just fine with this function using DateDiff:

**Code**: [ Select ]

Public Function YearsOfEmployment(varDateOfHire As Variant) As Integer

Dim varAge As Variant

If IsNull(varDateOfHire) Then YearsOfEmployment = 0: Exit Function

varAge = DateDiff("yyyy", varDateOfHire, Now)

If Date < DateSerial(Year(Now), Month(varDateOfHire), _

Day(varDateOfHire)) Then

varAge = varAge - 1

End If

YearsOfEmployment = CInt(varAge)

End Function

Dim varAge As Variant

If IsNull(varDateOfHire) Then YearsOfEmployment = 0: Exit Function

varAge = DateDiff("yyyy", varDateOfHire, Now)

If Date < DateSerial(Year(Now), Month(varDateOfHire), _

Day(varDateOfHire)) Then

varAge = varAge - 1

End If

YearsOfEmployment = CInt(varAge)

End Function

- Public Function YearsOfEmployment(varDateOfHire As Variant) As Integer
- Dim varAge As Variant
- If IsNull(varDateOfHire) Then YearsOfEmployment = 0: Exit Function
- varAge = DateDiff("yyyy", varDateOfHire, Now)
- If Date < DateSerial(Year(Now), Month(varDateOfHire), _
- Day(varDateOfHire)) Then
- varAge = varAge - 1
- End If
- YearsOfEmployment = CInt(varAge)
- End Function

This is in it's own module and is called in an Access 2000 Form by this procedure call:

=YearsOfEmployment([date_of_hire])

(date_of_hire is the value stored in the employee_info table)

This works just fine and returns the current length of employment in years. Now here's where I'm stuck. To determine the employees acrual rate I need to evaluate how many years they have in and assign them the acrual rate they are entitled to. I've tried a variety of ideas, but I think this comes very close, but I'm missing something somewhere. I'm sure it's because the YearsOfEmployment variable is in a different function, and that's where I'm stuck -- getting that calculated result passed into this function:

**Code**: [ Select ]

Public Function Acrual(ByVal x As Variant) As Variant

' Dimension the variable.

Dim x As Variant

' Start the Select Case structure.

Set x = YearsOfEmployment

Select Case x

' Test to see if x less than or equal to 5.

Case Is <= 5

' Display a message box.

x = 6.67

' Test to see if x less than or equal to 10 and greater than 5.

Case 6 To 10

x = 10

' Test to see if x lgreater than 10.

Case Is > 10

x = 13.33

Acrual = CInt(x)

End Select

End Function

' Dimension the variable.

Dim x As Variant

' Start the Select Case structure.

Set x = YearsOfEmployment

Select Case x

' Test to see if x less than or equal to 5.

Case Is <= 5

' Display a message box.

x = 6.67

' Test to see if x less than or equal to 10 and greater than 5.

Case 6 To 10

x = 10

' Test to see if x lgreater than 10.

Case Is > 10

x = 13.33

Acrual = CInt(x)

End Select

End Function

- Public Function Acrual(ByVal x As Variant) As Variant
- ' Dimension the variable.
- Dim x As Variant
- ' Start the Select Case structure.
- Set x = YearsOfEmployment
- Select Case x
- ' Test to see if x less than or equal to 5.
- Case Is <= 5
- ' Display a message box.
- x = 6.67
- ' Test to see if x less than or equal to 10 and greater than 5.
- Case 6 To 10
- x = 10
- ' Test to see if x lgreater than 10.
- Case Is > 10
- x = 13.33
- Acrual = CInt(x)
- End Select
- End Function

Like I said, I know this really can't be all that complicated to do, but I'm just a beginner whose figuring this stuff out as I go. (I'm only halfway through the VBA book I'm using and still have to work my way through the 2 Volume Access Developer's Handbooks *lol)

Once this part is solved the next step is to multiply the Acrual rate determined in the above procedure by the number of months already past in any given year. I already have the function that determines the number of months working just fine but I'm using an expression and I suspect I'm going to need to change that to a function similar to what I did for YearsOf Employment. THis is the expression I'm using that works

**Code**: [ Select ]

=IIf(Int(Format("1/1","y"))>Int(Format(Date(),"y")),DateDiff("m","1/1",Date())-1,DateDiff("m","1/1",Date()))

I think I can change that to a function OK, but I think I'm going to come up stuck again trying to get the result for Acrual to be used to calculate the number of vacation hours acrued year-to-date.

Any help would be much appreciated. I've been trying to get this to work for over 8 hours between the last three days. I figured that was enough effort on my own and time to ask for help. I'm probably making it more complicated than it really is.

**rtm223**- Mastermind
- Posts: 1855
- Loc: Uk

**Code**: [ Select ]

Public Function Acrual(ByVal x As Variant) As Variant

'skip some stuff

Set x = YearsOfEmployment

'skip some stuff

End Function

'skip some stuff

Set x = YearsOfEmployment

'skip some stuff

End Function

- Public Function Acrual(ByVal x As Variant) As Variant
- 'skip some stuff
- Set x = YearsOfEmployment
- 'skip some stuff
- End Function

x=YearsOfEmployment. With no parameter passed.

**Code**: [ Select ]

Public Function YearsOfEmployment(varDateOfHire As Variant) As Integer

Dim varAge As Variant

If IsNull(varDateOfHire) Then YearsOfEmployment = 0: Exit Function

'skip some stuff

End Function

Dim varAge As Variant

If IsNull(varDateOfHire) Then YearsOfEmployment = 0: Exit Function

'skip some stuff

End Function

- Public Function YearsOfEmployment(varDateOfHire As Variant) As Integer
- Dim varAge As Variant
- If IsNull(varDateOfHire) Then YearsOfEmployment = 0: Exit Function
- 'skip some stuff
- End Function

if the parameter is null (it is because there is nothing passed) return 0 and exit

YearsOfEmployment is not a variable, it is a function and as I understand it it will be reevaluated whenever you call it.

I think you are needing a variable YearsOfEmployment and a function called GetYearsOfEmployment.

**Code**: [ Select ]

Dim YearsOfEmployment

YearsOfEmployment = GetYearsOfEmployment(parameterName)

'....... later on .......

x = YearsOfEmployment

YearsOfEmployment = GetYearsOfEmployment(parameterName)

'....... later on .......

x = YearsOfEmployment

- Dim YearsOfEmployment
- YearsOfEmployment = GetYearsOfEmployment(parameterName)
- '....... later on .......
- x = YearsOfEmployment

Don't hold me to that but I <i>think</i> that is the problem

*removed by because I was being dumb lol* but I hope this helps

**ATNO/TW**- Super Moderator
- Posts: 23473
- Loc: Woodbridge VA

Thank you rtm -- I didn't quite follow you at first, but it got me thinking in the right direction, and I solved it. Here's the working function:

Now I can move on to part two *grins. That should go a bit easier (I hope)

*I'm donating you 100 points for that bit of help. Thanks again!

**Code**: [ Select ]

Public Function GetYearsOfEmployment(varDateOfHire As Variant) As Variant

Dim varAge As Variant

If IsNull(varDateOfHire) Then GetYearsOfEmployment = 0: Exit Function

' Calculate the years of employment

varAge = DateDiff("yyyy", varDateOfHire, Now)

If Date < DateSerial(Year(Now), Month(varDateOfHire), _

Day(varDateOfHire)) Then

varAge = varAge - 1

End If

GetYearsOfEmployment = CInt(varAge)

' Start the Select Case structure.

Select Case GetYearsOfEmployment

' Test to see if GetYearsOfEmployment is less than or equal to 5.

Case Is <= 5

GetYearsOfEmployment = 6.67

' Test to see if GetYearsOfEmployment is less than or equal to 10 and greater than 5.

Case 6 To 10

GetYearsOfEmployment = 10

' Test to see if GetYearsOfEmployment is greater than 10.

Case Is > 10

GetYearsOfEmployment = 13.33

GetYearsOfEmployment = CVar(GetYearsOfEmployment)

End Select

End Function

Dim varAge As Variant

If IsNull(varDateOfHire) Then GetYearsOfEmployment = 0: Exit Function

' Calculate the years of employment

varAge = DateDiff("yyyy", varDateOfHire, Now)

If Date < DateSerial(Year(Now), Month(varDateOfHire), _

Day(varDateOfHire)) Then

varAge = varAge - 1

End If

GetYearsOfEmployment = CInt(varAge)

' Start the Select Case structure.

Select Case GetYearsOfEmployment

' Test to see if GetYearsOfEmployment is less than or equal to 5.

Case Is <= 5

GetYearsOfEmployment = 6.67

' Test to see if GetYearsOfEmployment is less than or equal to 10 and greater than 5.

Case 6 To 10

GetYearsOfEmployment = 10

' Test to see if GetYearsOfEmployment is greater than 10.

Case Is > 10

GetYearsOfEmployment = 13.33

GetYearsOfEmployment = CVar(GetYearsOfEmployment)

End Select

End Function

- Public Function GetYearsOfEmployment(varDateOfHire As Variant) As Variant
- Dim varAge As Variant
- If IsNull(varDateOfHire) Then GetYearsOfEmployment = 0: Exit Function
- ' Calculate the years of employment
- varAge = DateDiff("yyyy", varDateOfHire, Now)
- If Date < DateSerial(Year(Now), Month(varDateOfHire), _
- Day(varDateOfHire)) Then
- varAge = varAge - 1
- End If
- GetYearsOfEmployment = CInt(varAge)
- ' Start the Select Case structure.
- Select Case GetYearsOfEmployment
- ' Test to see if GetYearsOfEmployment is less than or equal to 5.
- Case Is <= 5
- GetYearsOfEmployment = 6.67
- ' Test to see if GetYearsOfEmployment is less than or equal to 10 and greater than 5.
- Case 6 To 10
- GetYearsOfEmployment = 10
- ' Test to see if GetYearsOfEmployment is greater than 10.
- Case Is > 10
- GetYearsOfEmployment = 13.33
- GetYearsOfEmployment = CVar(GetYearsOfEmployment)
- End Select
- End Function

Now I can move on to part two *grins. That should go a bit easier (I hope)

*I'm donating you 100 points for that bit of help. Thanks again!

**rtm223**- Mastermind
- Posts: 1855
- Loc: Uk

**ATNO/TW**- Super Moderator
- Posts: 23473
- Loc: Woodbridge VA

**rtm223**- Mastermind
- Posts: 1855
- Loc: Uk

Ahhh fair enough then I suppose if it is just running through a preset set of commands then there's no point in adding lots of subroutines and functions.

Good luck with the project ATNO, and thanks for the points, I spent all of mine on the "activities"

BTW I would only spend about 3 hours on a problem before trying to get someone else to help, but I admire your resolve

Good luck with the project ATNO, and thanks for the points, I spent all of mine on the "activities"

BTW I would only spend about 3 hours on a problem before trying to get someone else to help, but I admire your resolve

**ATNO/TW**- Super Moderator
- Posts: 23473
- Loc: Woodbridge VA

I'm back to this project. Last week I got all the functions to correctly perform six different calculations I needed. Now, when the form loads, I need to have it update all records (about 45 total) with the appropriate values of the calculated fields. (I need to update the table to write reports based on the table). I'm having a small struggle with the syntax to do this, and exactly where to put it. I'm assuming either the onLoad or the onOpen event would be the best place, but I'm a little lost.

If it helps, the table name is employee_info, and the table columns that need updated are YearsOfService, MonthsOfService, AcrualRate, ProjectedAcrual, ActualAcrual, DaysRemaining.

(This is in an Access Database, not SQL if it makes a difference in the syntax). Thanks for any help.

If it helps, the table name is employee_info, and the table columns that need updated are YearsOfService, MonthsOfService, AcrualRate, ProjectedAcrual, ActualAcrual, DaysRemaining.

(This is in an Access Database, not SQL if it makes a difference in the syntax). Thanks for any help.

**rtm223**- Mastermind
- Posts: 1855
- Loc: Uk

I tend to go for VB6 applications with external databases and this makes everything slightly different, so I'm not 100% on this, but I think in you are probably looking at something like

However, if you have fields that are directly calculated from other fields at run-time, then I would suggest that the <b>calculated</b> fields can be considered redundant. If the data is re-calculated everytime the system is run, then there is no need to actually <i>store</i> the values.

Therefore I would call the calculations from custom next and previous buttons (whilst disabling the form default buttons). These custom buttons can be done with the form properties and a button wizard. Basically you will be calculating the values for each record one at a time, as you view them.

This could cause some problems if you are producing reports of the data, although I'm not sure.

From a database design and normalization point of view, this would be the way to go, as the table <i>"contains non-key dependancies"</i>, violating 3rd normal form (I have been studying for my exam you see ).

**Code**: [ Select ]

do while not employee_info.eof

'run your functions

'move to next record

loop

'run your functions

'move to next record

loop

- do while not employee_info.eof
- 'run your functions
- 'move to next record
- loop

However, if you have fields that are directly calculated from other fields at run-time, then I would suggest that the <b>calculated</b> fields can be considered redundant. If the data is re-calculated everytime the system is run, then there is no need to actually <i>store</i> the values.

Therefore I would call the calculations from custom next and previous buttons (whilst disabling the form default buttons). These custom buttons can be done with the form properties and a button wizard. Basically you will be calculating the values for each record one at a time, as you view them.

This could cause some problems if you are producing reports of the data, although I'm not sure.

From a database design and normalization point of view, this would be the way to go, as the table <i>"contains non-key dependancies"</i>, violating 3rd normal form (I have been studying for my exam you see ).

**ATNO/TW**- Super Moderator
- Posts: 23473
- Loc: Woodbridge VA

That makes sense. Actually, while I was waiting for an answer I was continuing to research and have pretty much come to the conclusion that setting up a query for the calculated fields and basing the report off of the query is probably the easiest method. I think I can handle that.

One other question, though. My HR manager threw a kink into this this morning though. The final function GetYearsOfEmployment I posted above does exactly what it is supposed to do. However, for employees who are at 4 and 9 years of service approaching 5 and 10 years respectively, I need to some how compensate for the change in acrual rate from their anniversary date until the end of the year.

For example, one employee is at 4 years, but on September 1st she hits her five year anniversary. So currently she is acruing 6.67 hours per month, but from Sept 1st through Dec 31st she will be acruing 10 hours per month.

Any suggestions on how to approach this?

One other question, though. My HR manager threw a kink into this this morning though. The final function GetYearsOfEmployment I posted above does exactly what it is supposed to do. However, for employees who are at 4 and 9 years of service approaching 5 and 10 years respectively, I need to some how compensate for the change in acrual rate from their anniversary date until the end of the year.

For example, one employee is at 4 years, but on September 1st she hits her five year anniversary. So currently she is acruing 6.67 hours per month, but from Sept 1st through Dec 31st she will be acruing 10 hours per month.

Any suggestions on how to approach this?

**rtm223**- Mastermind
- Posts: 1855
- Loc: Uk

**ATNO/TW wrote**:

For example, one employee is at 4 years, but on September 1st she hits her five year anniversary. So currently she is acruing 6.67 hours per month, but from Sept 1st through Dec 31st she will be acruing 10 hours per month.

Any suggestions on how to approach this?

Any suggestions on how to approach this?

I think I understand what you mean, but not 100% sure. The only thing I can suggest is to approach it in the same way I approach any algorithm design that is confusing me. Sit down and write out the process.

Do it as a flowchart, or pseudocode, however you feel most comfortable. Just write out step by step, structured instructions for the manual process so that <i>anyone</i> can follow them. Once you have done that, half the work is done and you just have to worry about syntax and lexicon, the procedures should become quite easy to write.

**ATNO/TW**- Super Moderator
- Posts: 23473
- Loc: Woodbridge VA

**ATNO/TW**- Super Moderator
- Posts: 23473
- Loc: Woodbridge VA

I'm almost, there!!! This reminds me so much of story problems from back in high school *lol

OK, rtm, I followed your advice and worked my way through it. It almost works....almost.

I'm using a function GetProjectedAcrued to calculate the projected vacation hours for the year for each employee. If the current record (employee) is in their fourth year of service, they will need to have a calculation for a split rate - the old rate and the new rate (as noted in my post above). So if they are in their forth year, I call function GetFourYear for this calculation. I've debugged all the compile errors and the script makes the calculation, but the result is wrong. The GetFourYear function determines this year's anniversary date, then identifies how many months should be calculated at the old rate of 6.67 and how many months at the new rate of 10 hours per month.

For example sake, I have one employee whose anniversary was in June, so 6 months would be at 6.67 and 6 months at 10, so the equation I'm trying to achieve is (6 * 6.67) + (6 * 10) = 100 (total hours) (Of course I have to do this all with variables, since everyone's anniversary date is different). Like I said it makes a calculation, but in this example the result is 720. I can't for the life of me see where I screwed up to get that calculation. Also, I have tried it with CVar, CInt and CLng, all with the same results. Here are the two functions. Hopefully someone will spot what I'm missing:

Function GetProjectedAcrued

Function GetFourYear:

OK, rtm, I followed your advice and worked my way through it. It almost works....almost.

I'm using a function GetProjectedAcrued to calculate the projected vacation hours for the year for each employee. If the current record (employee) is in their fourth year of service, they will need to have a calculation for a split rate - the old rate and the new rate (as noted in my post above). So if they are in their forth year, I call function GetFourYear for this calculation. I've debugged all the compile errors and the script makes the calculation, but the result is wrong. The GetFourYear function determines this year's anniversary date, then identifies how many months should be calculated at the old rate of 6.67 and how many months at the new rate of 10 hours per month.

For example sake, I have one employee whose anniversary was in June, so 6 months would be at 6.67 and 6 months at 10, so the equation I'm trying to achieve is (6 * 6.67) + (6 * 10) = 100 (total hours) (Of course I have to do this all with variables, since everyone's anniversary date is different). Like I said it makes a calculation, but in this example the result is 720. I can't for the life of me see where I screwed up to get that calculation. Also, I have tried it with CVar, CInt and CLng, all with the same results. Here are the two functions. Hopefully someone will spot what I'm missing:

Function GetProjectedAcrued

**Code**: [ Select ]

Public Function GetProjectedAcrued(varDateOfHire As Variant) As Variant

Dim varAge As Variant

If IsNull(varDateOfHire) Then GetProjectedAcrued = 0: Exit Function

'Function to determine length of service in months

'Dimension the variable

Dim tServiceMonths As Double

'Calculate date difference between Date of Hire and Now

tServiceMonths = (DateDiff("m", varDateOfHire, Now))

If (DatePart("d", varDateOfHire) > DatePart("d", Now)) Then

tServiceMonths = tServiceMonths - 1

End If

If tServiceMonths < 0 Then

tServiceMonths = tServiceMonths + 1

End If

GetProjectedAcrued = CInt(tServiceMonths)

' Start the Select Case structure.

Select Case GetProjectedAcrued

' Test to see if x less than or equal to 48.

Case Is <= 48

' Display a message box.

GetProjectedAcrued = 6.67

' Test to see if x less than or equal to 108 and greater than 60.

Case 61 To 108

GetProjectedAcrued = 10

' Test to see if x is greater than 120.

Case Is > 120

GetProjectedAcrued = 13.33

' Test to see if x less than or equal to 60 and greater than 48 to determine 4 years of service

Case 49 To 60

Call GetFourYear([date_of_hire])

' Test to see if x less than or equal to 120 and greater than 108 to determine 9 years of service

Case 109 To 120

GetProjectedAcrued = 10

GetProjectedAcrued = CVar(GetProjectedAcrued)

End Select

GetProjectedAcrued = CVar(GetProjectedAcrued) * 12

GetProjectedAcrued = CInt(GetProjectedAcrued)

End Function

Dim varAge As Variant

If IsNull(varDateOfHire) Then GetProjectedAcrued = 0: Exit Function

'Function to determine length of service in months

'Dimension the variable

Dim tServiceMonths As Double

'Calculate date difference between Date of Hire and Now

tServiceMonths = (DateDiff("m", varDateOfHire, Now))

If (DatePart("d", varDateOfHire) > DatePart("d", Now)) Then

tServiceMonths = tServiceMonths - 1

End If

If tServiceMonths < 0 Then

tServiceMonths = tServiceMonths + 1

End If

GetProjectedAcrued = CInt(tServiceMonths)

' Start the Select Case structure.

Select Case GetProjectedAcrued

' Test to see if x less than or equal to 48.

Case Is <= 48

' Display a message box.

GetProjectedAcrued = 6.67

' Test to see if x less than or equal to 108 and greater than 60.

Case 61 To 108

GetProjectedAcrued = 10

' Test to see if x is greater than 120.

Case Is > 120

GetProjectedAcrued = 13.33

' Test to see if x less than or equal to 60 and greater than 48 to determine 4 years of service

Case 49 To 60

Call GetFourYear([date_of_hire])

' Test to see if x less than or equal to 120 and greater than 108 to determine 9 years of service

Case 109 To 120

GetProjectedAcrued = 10

GetProjectedAcrued = CVar(GetProjectedAcrued)

End Select

GetProjectedAcrued = CVar(GetProjectedAcrued) * 12

GetProjectedAcrued = CInt(GetProjectedAcrued)

End Function

- Public Function GetProjectedAcrued(varDateOfHire As Variant) As Variant
- Dim varAge As Variant
- If IsNull(varDateOfHire) Then GetProjectedAcrued = 0: Exit Function
- 'Function to determine length of service in months
- 'Dimension the variable
- Dim tServiceMonths As Double
- 'Calculate date difference between Date of Hire and Now
- tServiceMonths = (DateDiff("m", varDateOfHire, Now))
- If (DatePart("d", varDateOfHire) > DatePart("d", Now)) Then
- tServiceMonths = tServiceMonths - 1
- End If
- If tServiceMonths < 0 Then
- tServiceMonths = tServiceMonths + 1
- End If
- GetProjectedAcrued = CInt(tServiceMonths)
- ' Start the Select Case structure.
- Select Case GetProjectedAcrued
- ' Test to see if x less than or equal to 48.
- Case Is <= 48
- ' Display a message box.
- GetProjectedAcrued = 6.67
- ' Test to see if x less than or equal to 108 and greater than 60.
- Case 61 To 108
- GetProjectedAcrued = 10
- ' Test to see if x is greater than 120.
- Case Is > 120
- GetProjectedAcrued = 13.33
- ' Test to see if x less than or equal to 60 and greater than 48 to determine 4 years of service
- Case 49 To 60
- Call GetFourYear([date_of_hire])
- ' Test to see if x less than or equal to 120 and greater than 108 to determine 9 years of service
- Case 109 To 120
- GetProjectedAcrued = 10
- GetProjectedAcrued = CVar(GetProjectedAcrued)
- End Select
- GetProjectedAcrued = CVar(GetProjectedAcrued) * 12
- GetProjectedAcrued = CInt(GetProjectedAcrued)
- End Function

Function GetFourYear:

**Code**: [ Select ]

Public Function GetFourYear(dteDate As Date) As Date

' This function finds the next anniversary of a date.

' If the date has already passed for this year, it returns

' the date on which the anniversary occurs in the following year.

Dim dteThisYear As Date

' Find corresponding date this year.

dteThisYear = DateSerial(Year(Date), Month(dteDate), Day(dteDate))

' Determine whether it's already passed.

'If dteThisYear < Date Then

'GetFourYear = DateAdd("yyyy", 1, dteThisYear)

'Else

GetFourYear = CDate(dteThisYear)

'End If

Dim tMonths As Double

Dim varYearEnd As Date

Dim AnniversaryDate As Date

AnniversaryDate = CDate(GetFourYear)

'Calculate the difference between anniversary date and end of year,

tMonths = (DateDiff("m", AnniversaryDate, #12/31/2004#))

If (DatePart("d", AnniversaryDate) > DatePart("d", #12/31/2004#)) Then

tMonths = tMonths - 1

End If

If tMonths < 0 Then

tMonths = tMonths + 1

End If

GetFourYear = CLng(tMonths)

GetFourYear = CVar(GetFourYear)

Dim NewRate As Long

Dim OldRate As Long

NewRate = GetFourYear

OldRate = 12 - NewRate

'add total months at new rate to total months at old rate to get total projected acrual for the year

Dim TotalRate As Long

TotalRate = (NewRate * 10) + (OldRate * 6.67)

GetFourYear = CVar(TotalRate)

End Function

' This function finds the next anniversary of a date.

' If the date has already passed for this year, it returns

' the date on which the anniversary occurs in the following year.

Dim dteThisYear As Date

' Find corresponding date this year.

dteThisYear = DateSerial(Year(Date), Month(dteDate), Day(dteDate))

' Determine whether it's already passed.

'If dteThisYear < Date Then

'GetFourYear = DateAdd("yyyy", 1, dteThisYear)

'Else

GetFourYear = CDate(dteThisYear)

'End If

Dim tMonths As Double

Dim varYearEnd As Date

Dim AnniversaryDate As Date

AnniversaryDate = CDate(GetFourYear)

'Calculate the difference between anniversary date and end of year,

tMonths = (DateDiff("m", AnniversaryDate, #12/31/2004#))

If (DatePart("d", AnniversaryDate) > DatePart("d", #12/31/2004#)) Then

tMonths = tMonths - 1

End If

If tMonths < 0 Then

tMonths = tMonths + 1

End If

GetFourYear = CLng(tMonths)

GetFourYear = CVar(GetFourYear)

Dim NewRate As Long

Dim OldRate As Long

NewRate = GetFourYear

OldRate = 12 - NewRate

'add total months at new rate to total months at old rate to get total projected acrual for the year

Dim TotalRate As Long

TotalRate = (NewRate * 10) + (OldRate * 6.67)

GetFourYear = CVar(TotalRate)

End Function

- Public Function GetFourYear(dteDate As Date) As Date
- ' This function finds the next anniversary of a date.
- ' If the date has already passed for this year, it returns
- ' the date on which the anniversary occurs in the following year.
- Dim dteThisYear As Date
- ' Find corresponding date this year.
- dteThisYear = DateSerial(Year(Date), Month(dteDate), Day(dteDate))
- ' Determine whether it's already passed.
- 'If dteThisYear < Date Then
- 'GetFourYear = DateAdd("yyyy", 1, dteThisYear)
- 'Else
- GetFourYear = CDate(dteThisYear)
- 'End If
- Dim tMonths As Double
- Dim varYearEnd As Date
- Dim AnniversaryDate As Date
- AnniversaryDate = CDate(GetFourYear)
- 'Calculate the difference between anniversary date and end of year,
- tMonths = (DateDiff("m", AnniversaryDate, #12/31/2004#))
- If (DatePart("d", AnniversaryDate) > DatePart("d", #12/31/2004#)) Then
- tMonths = tMonths - 1
- End If
- If tMonths < 0 Then
- tMonths = tMonths + 1
- End If
- GetFourYear = CLng(tMonths)
- GetFourYear = CVar(GetFourYear)
- Dim NewRate As Long
- Dim OldRate As Long
- NewRate = GetFourYear
- OldRate = 12 - NewRate
- 'add total months at new rate to total months at old rate to get total projected acrual for the year
- Dim TotalRate As Long
- TotalRate = (NewRate * 10) + (OldRate * 6.67)
- GetFourYear = CVar(TotalRate)
- End Function

**rtm223**- Mastermind
- Posts: 1855
- Loc: Uk

Again, I'm not sure what the problem is, but I would go through this in either debug step-into (or step-over - which ever executes each line individually) mode, or I would start adding in msgbox()'s to display the values of variables.

This line:
You know to be correct, so at some point along the line the variables are getting calculated incorrectly.

Go though an example one line at a time, calculating each step manually, and see at what point your answers start to differ from the program's.

Hope it helps.

This line:

**Code**: [ Select ]

TotalRate = (NewRate * 10) + (OldRate * 6.67)

Go though an example one line at a time, calculating each step manually, and see at what point your answers start to differ from the program's.

Hope it helps.

**ATNO/TW**- Super Moderator
- Posts: 23473
- Loc: Woodbridge VA

**ATNO/TW**- Super Moderator
- Posts: 23473
- Loc: Woodbridge VA

rtm -God Bless you and the MsgBox() *grins!!!

I got it! Actually, I discovered that the second function was almost written correctly. When I elliminated these two lines the calculation for TotalRate was correct:

The problem with the last line of the GetFourYear function, though was it kept returning a date. I redefined the function As Variant instead of Date and changed the last line of the function to:

Finally in the GetProjectedAcrued function I changed For Case 49 To 60 to:

The only thing left to do to complete the application is to do the same thing for Case 109 To 120 for the nine year employees, but that's a no-brainer now. I completely appreciate your guiding my thought processes through this little exercise. Specifically, I'm greatful that you didn't just write the code for me and forced me to work through it. This is the first VBA application I've ever written from scratch and at the moment I'm feeling pretty good about it with much thanks to you.

Cheers!

One small last question for this line:

for #12/31/2004# I tried to set that for just 12/31 so that it defaults to the current year, but the VB editor keeps tacking on the year 2004. I'm sure there has to be a way to do this so it doesn't have to be manually changed each year. Do you (or anyone) know how?

I got it! Actually, I discovered that the second function was almost written correctly. When I elliminated these two lines the calculation for TotalRate was correct:

**Code**: [ Select ]

GetFourYear = CLng(tMonths)

GetFourYear = CVar(GetFourYear)

GetFourYear = CVar(GetFourYear)

- GetFourYear = CLng(tMonths)
- GetFourYear = CVar(GetFourYear)

The problem with the last line of the GetFourYear function, though was it kept returning a date. I redefined the function As Variant instead of Date and changed the last line of the function to:

**Code**: [ Select ]

GetFourYear = CDec(TotalRate)

Finally in the GetProjectedAcrued function I changed For Case 49 To 60 to:

**Code**: [ Select ]

Case 49 To 60

GetProjectedAcrued = CVar(GetFourYear([date_of_hire])): Exit Function

GetProjectedAcrued = CVar(GetFourYear([date_of_hire])): Exit Function

- Case 49 To 60
- GetProjectedAcrued = CVar(GetFourYear([date_of_hire])): Exit Function

The only thing left to do to complete the application is to do the same thing for Case 109 To 120 for the nine year employees, but that's a no-brainer now. I completely appreciate your guiding my thought processes through this little exercise. Specifically, I'm greatful that you didn't just write the code for me and forced me to work through it. This is the first VBA application I've ever written from scratch and at the moment I'm feeling pretty good about it with much thanks to you.

Cheers!

One small last question for this line:

**Code**: [ Select ]

tMonths = (DateDiff("m", AnniversaryDate, #12/31/2004#))

for #12/31/2004# I tried to set that for just 12/31 so that it defaults to the current year, but the VB editor keeps tacking on the year 2004. I'm sure there has to be a way to do this so it doesn't have to be manually changed each year. Do you (or anyone) know how?

**rtm223**- Mastermind
- Posts: 1855
- Loc: Uk

debugging is a pain until you can find a set of techniques that work for you. Even then it can be a nightmare. Personally I think the only thing going for VB (other than ease of use) is the debugging tools included. Glad you got it sorted.

I havn't used datediff before, and TBH I havn't done a lot of work with date manipulation. However, I checked the MSDN entry:

http://msdn.microsoft.com/library/defau ... tediff.asp

and it looks as though you can make the <i>Date2</i> into a variable.

I guess to set this variable the best way would be to get the current year (from today's date), and then add that to the month and date ("12/31"). It might even be possible to create this as a string and then convert the string into a date/time variable.

Like I said I'm not to familiar with using dates in VB, but that is the direction i would try to go in.

**ATNO/TW wrote**:

One small last question for this line:

for #12/31/2004# I tried to set that for just 12/31 so that it defaults to the current year, but the VB editor keeps tacking on the year 2004. I'm sure there has to be a way to do this so it doesn't have to be manually changed each year. Do you (or anyone) know how?

**Code**: [ Select ]

tMonths = (DateDiff("m", AnniversaryDate, #12/31/2004#))

for #12/31/2004# I tried to set that for just 12/31 so that it defaults to the current year, but the VB editor keeps tacking on the year 2004. I'm sure there has to be a way to do this so it doesn't have to be manually changed each year. Do you (or anyone) know how?

I havn't used datediff before, and TBH I havn't done a lot of work with date manipulation. However, I checked the MSDN entry:

http://msdn.microsoft.com/library/defau ... tediff.asp

and it looks as though you can make the <i>Date2</i> into a variable.

I guess to set this variable the best way would be to get the current year (from today's date), and then add that to the month and date ("12/31"). It might even be possible to create this as a string and then convert the string into a date/time variable.

Like I said I'm not to familiar with using dates in VB, but that is the direction i would try to go in.

**ATNO/TW**- Super Moderator
- Posts: 23473
- Loc: Woodbridge VA

Thanks. I'll look a little closer into that. I did try it as a variable, but I was getting the same result. Whenever I would set the variable, it would still default to 2004. I'm sure I was doing something wrong. And don't worry, this was my first VB script, so up until now I hadn't ever worked with date manipulation either, but the lesson will come in handy, because I need to do some date manipulatoin with some PHP scripts I'm working on.

Again, thanks for your help.

Again, thanks for your help.

**ATNO/TW**- Super Moderator
- Posts: 23473
- Loc: Woodbridge VA

To make this complete, here's how I set the Current year December 31 as a variable:

DateValue converts the string to a date and since the year is not specified it assumes the current year based on system time. Then I just used the myDate variable for Date2

**Code**: [ Select ]

Dim myDate As Date

myDate = DateValue("December 31")

myDate = DateValue("December 31")

- Dim myDate As Date
- myDate = DateValue("December 31")

DateValue converts the string to a date and since the year is not specified it assumes the current year based on system time. Then I just used the myDate variable for Date2

Page **1** of **1**

To Reply to this topic you need to LOGIN or REGISTER. It is free.

## Post Information

- Total Posts in this topic: 19 posts
- Users browsing this forum: No registered users and 41 guests

- You
**cannot**post new topics in this forum - You
**cannot**reply to topics in this forum - You
**cannot**edit your posts in this forum - You
**cannot**delete your posts in this forum - You
**cannot**post attachments in this forum