VBA - what is causing this overflow error?

Question

I am encountering a bug in VBA. As I am just a few weeks in, the code itself probably lacks a lot of best practices.

But besides that, in this specific case I get an Overflow error on the following line

dateDifference = DateDiff("d", currentDate, olderDate, vbMonday) in the section

'========================
'make cell BLUE
'========================

The full code is listed below. Does anybody have an idea, what is causing this issue? As a greenhorn my guess is, this has to do with constantly reassigning 3 variables in the loop?

Thanks a lot in advance.

Sub HrReporting_Step07_ApplyCellColouring()

ThisWorkbook.Activate

'========================
'Variables for looping
'========================
'declarations
Dim rowCount As Integer
Dim i As Integer
Dim srcColourColumnIntRed1 As Integer
Dim srcColourColumnIntRed2 As Integer
Dim srcColourColumnIntYellow As Integer
Dim srcColourColumnIntGreen As Integer
Dim srcColourColumnIntBlue1 As Integer
Dim srcColourColumnIntBlue2 As Integer
'variable declaration specifically for date calculations that are needed for colouring cells YELLOW or BLUE
Dim olderDate As Date
Dim currentDate As Date
Dim dateDifference As Integer

'assignments
srcColourColumnIntRed1 = Range("Table1[Availability Status]").Column
srcColourColumnIntRed2 = Range("Table1[Sum of Current Calendar % Allocated]").Column
srcColourColumnIntYellow = Range("Table1[Coming Available Category]").Column
srcColourColumnIntGreen = Range("Table1[CW-1]").Column
srcColourColumnIntBlue1 = Range("Table1[Current Calendar]").Column
srcColourColumnIntBlue2 = Range("Table1[Current Calendar End Date]").Column

rowCount = Range("Table1[Coming Available Category]").Count + 1

'========================
'make cell RED
'========================
For i = 2 To rowCount
'based on following conditions
'   1. Column "Sum of Current Calendar % Allocated" is lower or equal to 60 %
'   2. Column "Availability Status" = Now Available
    If Cells(i, srcColourColumnIntRed1).Value = "Now Available" _
    Or Cells(i, srcColourColumnIntRed2).Value <= 60 _
    Then Cells(i, 1).Interior.Color = RGB(255, 0, 0)
Next i

'========================
'make cell YELLOW
'========================
For i = 2 To rowCount
'based on following condition
'   1. Column "Coming Available Category" = Available in the next 2 weeks
    If Cells(i, srcColourColumnIntYellow).Value = "Resource First Available Day 1-7 Days" _
    Or Cells(i, srcColourColumnIntYellow).Value = "Resource First Available Day 8-14 Days" _
    Then Cells(i, 1).Interior.Color = RGB(255, 255, 0)
Next i

'========================
'make cell BLUE
'========================
For i = 2 To rowCount
'based on following conditions
'   1. Column "Current Calendar" unequal to "Booked To A Project"
'   2. Column "Current Calendar" unequal to empty
'   3. Column "Current Calendar End Date" < to 42 days AND > 12 days

olderDate = Cells(i, Range("Table1[Current Calendar End Date]").Column)
currentDate = Date
dateDifference = DateDiff("d", currentDate, olderDate, vbMonday)

    If (Cells(i, srcColourColumnIntBlue1).Value <> "Booked To A Project" _
        And Cells(i, srcColourColumnIntBlue1).Value <> "") _
        Or (dateDifference <= 42 And dateDifference > 14) _
        Then Cells(i, 1).Interior.Color = RGB(0, 0, 255)
Next i

'========================
'make cell GREEN
'========================
For i = 2 To rowCount
'based on following condition
'   1. Name does not exist in previous weeks' sheet, identified by VLOOKUP being #N/A
    If WorksheetFunction.IsNA(Cells(i, srcColourColumnIntGreen)) _
    Then Cells(i, 1).Interior.Color = RGB(0, 255, 0)
Next i
End Sub

When you get the error, what are the values of `currentDate` and `olderDate`? — braX, Jul 29 '20 at 17:04
Don't use `Integer`, use `Long`... https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long — BigBen, Jul 29 '20 at 17:07
`olderDate ` appears to be referencing a _range_ - perhaps you need to add `.Value`? — D Stanley, Jul 29 '20 at 17:08
You will get that error if `dateDifference` computes to more than `32,767` days -- the limit of the VBA `Integer` data type is `-32,768 to 32,767`. Especially since the `DateDiff` function returns a `Variant(Long)` data type, you should declare `dateDifference` as `Long`. This is not a bug. This is a consequence of the documented design parameters and specifications of VBA — Ron Rosenfeld, Jul 29 '20 at 17:23
However, if you are getting a value larger than an integer can hold, there is most likely a problem with one of your date values, since that many days would equate to more than 89 years. — braX, Jul 29 '20 at 17:41
@braX not sure what the values are. When i’m in debug mode this error does not occur. — Fortun, Jul 29 '20 at 18:22
@BigBen thanks for the hint. I will try to change it to Long, and see if this solves the issue. Will let you know. — Fortun, Jul 29 '20 at 18:23

score 0 · Accepted Answer · answered Aug 02 '20 at 09:46

0

It turned out that the comments from BigBen and Ron Rosenfeld solved my issue. I needed to simply declare dateDifference as Long, and the Overflow error was gone. Thank you.

answered Aug 02 '20 at 09:46

Fortun

37
9

VBA - what is causing this overflow error?

1 Answers1