Hi, I am using the below code which interacts a lot with excel. I heard of the Application.ScreenUpdating = False, would that help me reduce the execution time of the code? How does it work? Thank you!


Public Function GetValue(EventDateRange As Range, _
DateX As Date, _
TheValue As Integer) As String

Application.ScreenUpdating = False

Dim i As Integer
Dim startDate As Date
Dim endDate As Date

Dim sRange As Range
Set sRange = Range(Range("B2"), Range("B2").End(xlDown))
Dim SpecialEvent As String
Dim SpecialEventRow As Integer
Dim Probability As Double
Dim sCell As Range
Dim CalendarRange As Range
Dim counter As Integer

If (TheValue <= 5) Then 'return empty string for distance to the date less than or equal to five
GetValue = ""
Exit Function
End If


' Loops through event column
For i = 1 To EventDateRange.Count - 1
startDate = EventDateRange(i)
endDate = EventDateRange(i + 1)

If EventDateRange(i) = DateX Or EventDateRange(i + 1) = DateX Then
GetValue = ""
Exit Function
End If


SpecialEvent = ""
Probability = 0

' Column Special Event (B)
For Each sCell In sRange
If IsDateInDateRange(CDate(sCell), startDate, endDate) Then
SpecialEvent = sCell.Value
Probability = sCell.Offset(0, -1).Value

' Select range from Calendar based on start/end date (E)
Set CalendarRange = GetRangeByDates(startDate, endDate)


If Not CalendarRange Is Nothing Then

SpecialEventRow = GetRowInRange(CDate(SpecialEvent), CalendarRange)
If IsDateInRange(DateX, CalendarRange) Then

' Debug.Print DateX & _
' " - " & startDate & "-" & endDate & _
' " -> " & SpecialEvent & " - " & Probability & _
' " - " & SpecialEventRow & _
' " - " & CalendarRange.Rows(0).Row + 1 & _
' " - " & CalendarRange.Rows(CalendarRange.Rows.Count - 1).Row & _
'" - " & TheValue

Dim c As Range
counter = 0
' counts the eligible rows
For Each c In CalendarRange
'Debug.Print c
If Val(c.Offset(0, 1)) > 5 Then
If CDate(c) <> startDate And CDate(c) <> endDate Then
counter = counter + 1
End If
End If
Next


GetValue = (10 / counter) * Probability
Exit Function

End If
End If

End If
Next

Next

' GetValue = "0"

End Function



Public Function IsDateInDateRange(inputDate As Date, _
startDate As Date, _
endDate As Date) As Boolean

IsDateInDateRange = inputDate >= startDate And inputDate <= endDate


End Function

' Gets Range from Calendar/column E by Start/End dates
Public Function GetRangeByDates(startDate As Date, endDate As Date) As Range

Dim cRange As Range
Set cRange = Range(Range("E2"), Range("E2").End(xlDown))
Dim startCell As Range
Dim endCell As Range

' Find StartDate and EndDate in column E (calendar)
Dim cCell As Range
For Each cCell In cRange
If CDate(cCell) = startDate Then
Set startCell = cCell
End If
If CDate(cCell) = endDate Then
Set endCell = cCell
End If
Next

If startCell Is Nothing Or endCell Is Nothing Then
Set GetRangeByDates = Nothing
Else
Set GetRangeByDates = Range(startCell, endCell)
End If

End Function

' Checks if input date is in provided Range, returns true or false
Function IsDateInRange(inputDate As Date, xlRange As Range) As Boolean

Dim c As Range
For Each c In xlRange
If inputDate = CDate(c.Value) Then
IsDateInRange = True
Exit Function
End If
Next
IsDateInRange = False

End Function

' Gets row location of provided date in Range
' It's used to get Special Event row number in column E
Function GetRowInRange(inputDate As Date, xlRange As Range) As Integer
Dim c As Range
For Each c In xlRange
If CDate(c.Value) = inputDate Then
GetRowInRange = c.Row
Exit Function
End If
Next
GetRowInRange = -1 ' if date not found in range then return -1
End Function