Why does this VBA function give me an error?

[karloqs]

Hi Guys

I'm just starting out with VBA so any help would be most appreciated.
I wanted I'd try to write a custom function which returns the percentage complete for a project based on today's date and the duration of the total project.
The last line of the code is what is causing the #value! error when I divide the Progress and Duration variables.
As a test, I've tried adding them together which gives me the correct answer.
So I know I must have done something wrong with either the variable types or dividing dates?

Public Function PercentComplete(StartDate As Date, EndDate As Date) As Double
'Define variables
Dim Duration As Date
Dim Progress As Date
Dim TodaysDate As Date
'Set TodaysDate variable to current date
'Now() is a VBA function which returns current system date
TodaysDate = Now()
'Work out number of dayes between start date and end date
Duration = Application.WorksheetFunction.Days(EndDate, StartDate)
'Work out number of dayes between todays date and end date
Progress = Application.WorksheetFunction.Days(TodaysDate, StartDate)
'trying to divide progress by duration to give a percentage complete
PercentComplete = Progress / Duration

End Function

 

[joeu2004]

Duration and Progress should be type Long or Double. (*)

Apparently, the division operator does not like type Date operands, even though they are started as type Double internally.

Moreover, when you subtract dates, the result should be a number of days, not a "date".

The function also returns #VALUE if the version of Excel does not support the DAYS function, like mine (Excel 2010). But that #VALUE error occurs before the last line.

Nevertheless, there is no need to use the DAYS function, since you require that StartDate and EndDate be type Date, not possibly text. Simply write:

Duration = EndDate - StartDate
Progress = TodaysDate - StartDate

Also, use the VBA Date function instead of VBA Now, to wit:

TodaysDate = Date

The Now function includes time of day. I do not believe that is your intent. But including the time of day can affect the percentage.

(That is certainly true if you use my alternative to the DAYS function. It is unclear if that is also true if you use the DAYS function with bona fide Excel dates (numeric), not text. I cannot test it.)

And of course, you could dispense with the TodaysDate variable altogether and simply write:

Progress = Date - StartDate


-----
(*) Resist the temptation to use type Integer instead of type Long. That changes the type of the division to type Single instead of type Double, according to the "/ operator" help page in VBA. It is better to use type Double, especially if you want to return the result to Excel, which effectively uses type Double internally.

 

[karloqs]

It now works a treat!
Thanks Joeu2004 for the quick and clear reply
Very much appreciated.
Now I can happily move on to my next VBA challenge

 

[joeu2004]

Apparently, the division operator does not like type Date operands, even though they are started as type Double internally.

You're welcome. Glad it all made sense, despite my nonsensical typos. The word "started" was meant to be "stored"(!). I seem to be doing a lot more of that lately. (sigh)