Application.ScreenUpdating = False

[Gero92]

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

[Keebellah]

Good morning, you're really enthusiastic duplicating your own post. I suggest you delete on, probably clicked once too many
Depending on what your code is doing, I'm not reading it all
Screen Updtaing set to false is time saving because the system does not have to refresh the screen every time a value is updated but you mus NOT forget to set it back to tru after the last iteration.
If you've got worksheet events loaded then you should also disable ApplicationEvents

I have these two macros that I invoke at the beginning of the actual loop and calculations and at the end

Please Login or Register  to view this content.

So your macro whatever it is

Please Login or Register  to view this content.

Hope this helps

[NeedForExcel]

@Keebellah - Setting the calculation mode to xlCalculationManual is not always helpful...

@Gero92 - I also suggest you use code tags to format your code properly to make it more readable.