Capacity overflow - excel vba [duplicate]

Question

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

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
– Jonathan Willcock, Commented Jul 13, 2023 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 stackoverflow.com/a/47024456/7599798 — FunThomas
– FunThomas, Commented Jul 13, 2023 at 11:08

Dominique · Accepted Answer · 2023-07-13 10:44:01Z

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 :-)

answered Jul 13, 2023 at 10:44

Dominique

17.6k20 gold badges81 silver badges161 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gibbs44 Over a year ago

Thank you for all your comment my code work perfectly now. =)

Collectives™ on Stack Overflow

Capacity overflow - excel vba [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related