Passing variable to function in VBA module - Resolved

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?

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.

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.

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