CODEWITHSUNDEEP
excelvba
Андрей Баранов
Андрей Баранов

Reputation: 423

How to get date from cell in excel

I've got function in VBA that operates with dates from cells. But I got By ref error when I choose cells. What can be the problem? This is my code

Function DifferenceInYears(existdate As Date, estimdate As Date) As Double

    Dim yearDifference As Integer
    Dim monthDifference As Integer
    Dim dayDifference As Integer
    yearDifference = 0
    monthDifference = 0
    dayDifference = 0
    If (estimdate <= existdate) Then
        MsgBox "Input correct range"
        GoTo myerr
    End If
    Dim tempDate As Date
    IsDateLeapDay = False
    Dim existYear As String
    Dim estimYear As String
    existYear = Year(existdate)
    estimYear = Year(estimdate)
    estimMonth = Month(estimdate)
    existMonth = Month(existdate)

    and so on...

Upvotes: 0

Views: 35906

Answers (2)

GisMofx
GisMofx

Reputation: 1024

This should get you started:

Sub GetDatesAndComputeElapsedYears()

   Dim d1 As String
   Dim d2 As String

   d1 = Range("a2").Value2 'put a date in A2 Formatted as date(cell format)
   d2 = Range("b2").Value2 'put a date in B2 Formaated as date(cell format)

   Dim date1 As Date
   Dim date2 As Date

   date1 = CDate(d1) 'converts serialized date to DATE Object
   date2 = CDate(d2)

   Dim years

   years = DateDiff("yyyy", date1, date2) 'use this for date difference calculations

   MsgBox CStr(years), vbOKOnly, "Years Elapsed"

End Sub

Upvotes: 5

LimaNightHawk
LimaNightHawk

Reputation: 7093

Just guessing here, but whatever function is calling this code, is NOT passing in an actual VB Date, and since you haven't qualified your arguments with the ByVal, VB assumes it to be ByRef. (Prefixing nothing—which is what you've done—is the same as prefixing with ByRef.) E.g., 

existdate As Date

is the same as

ByRef existdate As Date

But much different than

ByVal existdate As Date

You can try to make the arguments ByVal. Or you can change the argument type to Variant (which is what a Cell's value actually is, and what I'm guessing you're actually passing in).

Try changing your method signature to this:

Function DifferenceInYears(ByVal existdate As Date, ByVal estimdate As Date) As Double

...or this:

Function DifferenceInYears(existdate As Variant, estimdate As Variant) As Double

You'll probably have to do some casting inside or outside the function, but that should get you closer and solve your By ref error issue.

Upvotes: 0

Related Questions