0

I'm coding a function to compare two dates. My problem is with the bold line. When the code executes I get an overflow error. After several searches on the internet I understood that this type of error could occur when using an interger instead of a long one, which is not my case.

I've got about 235 thousand lines to process and I'm in a bit of a rush to finish programming this function before the end of the month.

Does anyone have any idea why this error occurred?

This is my code :

Public Function date_dif(sheet As String, number As Integer, colonnetarget As Integer, colonnedate1 As Integer, Optional colonnedate2 As Integer)

Dim last_row As Long
Dim sht, sht2 As Worksheet
Dim ligne As Integer
Dim formule As String
Dim crit1, crit2 As String

Set sht = ThisWorkbook.Worksheets(sheet)

last_row = sht.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row

If colonnedate2 <> 0 Then
    **For ligne = 2 To last_row**
        sht.Cells(ligne, colonnetarget).Value = DateDiff("d", sht.Cells(ligne, colonnedate1).Value, sht.Cells(ligne, colonnedate2).Value)
    Next
Else
    For ligne = number To last_row
        sht.Cells(lignxe, colonnetarget).Value = DateDiff("d", sht.Cells(ligne, colonnedate1).Value, Now())
    Next
End If


End Function
Jonathan Willcock
  • 5,012
  • 3
  • 20
  • 31
gibbs44
  • 3
  • 2
  • 1
    last_row is a Long but ligne is an Integer. if last_row is bigger than an Integer, you will get Overflow. Change ligne to Long as well. I am voting to close as a typo – Jonathan Willcock Jul 13 '23 at 10:40
  • Just *always* use `Long` in VBA. `Integer` was defined as 2 bytes and therefore has a maximum value of 32768. On a 32bit (or 64 bit) system it occupies 4 bytes and VBA checks during runtime the limits (to keep compatibility), so you don't save memory space anyhow - see https://stackoverflow.com/a/47024456/7599798 – FunThomas Jul 13 '23 at 11:08

1 Answers1

1

The following things don't make sense in your code:

Dim last_row As Long
...
Dim ligne As Integer
...
    **For ligne = 2 To last_row**

As last_row should be Long, so should be the counter, going up to that Long :-)

Dominique
  • 16,450
  • 15
  • 56
  • 112