英文:
Capacity overflow - excel vba
问题
以下是翻译好的部分：
"我正在编写一个比较两个日期的函数。我的问题出在粗体行上。
当代码执行时，我收到了一个溢出错误。经过多次在互联网上搜索后，我明白了这种类型的错误可能发生在使用整数而不是长整数时，但这并不是我的情况。
我需要处理大约23.5万行数据，并且我急于在本月底之前完成编写这个函数。
有人知道为什么会发生这个错误吗？
这是我的代码：
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(ligne, colonnetarget).Value = DateDiff("d", sht.Cells(ligne, colonnedate1).Value, Now())
Next
End If
End Function
希望对你有所帮助。
英文:
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
答案1
得分: 1
以下是已翻译的内容：
以下是您的代码中不合理的部分：
Dim last_row As Long
...
Dim ligne As Integer
...
For ligne = 2 To last_row
由于 last_row 应为 Long，因此计数器也应为 Long，以适应该 Long 的范围 
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论