VBA function assign value to vba array

[gero92]

Hi everyone, I am creating a VBA function and I want to multiply the variable "con" by a number that will be next to the EventDateRange varible the vba function takes from excel. this new variable lets call it "prob" will have to follow a similar i loop as the Caldate below. "con" will be multiplied by prob that changes as the EventDateRange change. So if DateX is in between the first 2 event dates it will multiply by the first value of "prob". Happy to explain more if not clear. Thanks!

Public Function GetValueTobeAsigned(EventDateRange As Range, DateX As Date, TheValue As Integer) As String
   Dim ans As String
     'eventdaterange is the event dates range
     'the value is the column with the difference positive of days from the special date
    ans = ""
    If (TheValue <= 5) Then 'return empty string for distance to the date less than or equal to five
        ans = ""
        GetValueTobeAsigned = ans
        Exit Function


    End If

    Dim dd As Integer
    Dim CalDate() As Date, CalDateRange() As Variant
    Dim nrows As Integer, CalenderDate2 As Date
    nrows = EventDateRange.Rows.Count - 1
    ReDim CalDate(0 To nrows) As Date

    Dim dat() As Variant
    dat = EventDateRange.Value2
'value2 for number format
    For i = 0 To nrows
        CalDate(i) = CDate(dat(i + 1, 1)) 'get the array of event dates
    Next i
    'Converts the number format to a date

    Dim minday As Integer
    Dim con As Double
    con = 10
    'number to assign to each calendar date
    For i = 1 To nrows
        If (DateX = CalDate(i - 1)) Then ' returns empty string for any event date equal to the given date and break out of  the loop
            GetValueTobeAsigned = ""
            Exit Function
        End If

        If (DateX = CalDate(i)) Then ' returns empty string for any event date equal to the given calendar date and break out of  the loop
            GetValueTobeAsigned = ""
            Exit Function
        End If
        If (DateX > CalDate(i - 1) And DateX < CalDate(i)) Then
            minday = Abs(DateDiff("d", CalDate(i - 1), CalDate(i))) - 2
            'Datediff finds the distance between dates, "d" for days. 2 are the event dates not to be included

            Dim divisor As Double
            divisor = Abs(con - minday)
            ans = CStr(con / divisor)
            GetValueTobeAsigned = ans
            Exit Function
        End If

    Next i


GetValueTobeAsigned = ans
'This is in case it did not enter the For Loop
End Function