Date format, with invalid DATE content, generates "Runtime Error '6' Overflow Error"

[ronwessel]

I can't believe I have never run into this before. I have a cell that contains 114119882. However, this cell is erroneously formatted as DATE. When a macro runs, and references this cell.value in any way, it gets the "Runtime Error '6', Overflow" error.


Simple setup:
Place 114119882 into cell A1.
Format the cell as DATE.
Run macro:
Sub show_error()
MsgBox Range("A1").Value
End Sub


I thought "formatting" was all cosmetic, and would never cause VBA to crash. I guess I was wrong. So, HOW do I "process" this cell?

(Please don't tell me to format the cell as General, or Number. I cannot control what user's do to their data, and want to make my VBA industrially able to handle this situation.)

Thanks,

 

[Fishboy]

I can't believe I have never run into this before. I have a cell that contains 114119882. However, this cell is erroneously formatted as DATE. When a macro runs, and references this cell.value in any way, it gets the "Runtime Error '6', Overflow" error.


Simple setup:
Place 114119882 into cell A1.
Format the cell as DATE.
Run macro:
Sub show_error()
MsgBox Range("A1").Value
End Sub


I thought "formatting" was all cosmetic, and would never cause VBA to crash. I guess I was wrong. So, HOW do I "process" this cell?

(Please don't tell me to format the cell as General, or Number. I cannot control what user's do to their data, and want to make my VBA industrially able to handle this situation.)

Thanks,

Curious, I followed your instructions but did not get a runtime error as per my screenshot below:

 

[ronwessel]

I cannot see your screenshot. Also, I tried to post my screen shot, but can only find a URL image insert. Not sure how to paste directly.

Anyway, I am using Excel 2013, but I also tried in in Excel 2003, and get the same results.

 

[JoeMo]

Usually when you try to format a number that is too large to be comprehended by Excel's calendar, Excel returns ####### ... to the cell. When you select that cell you see the number in the formula bar only (the cell being filled with ####...). If you see the number in the cell, perhaps it is not formatted as a date.

 

[ronwessel]

JoeMO,

You are correct. The cell is filled with pound-signs (###########). Again, I can correct the situation by simply changing the format of the cell. However, my interest is how to make the VBA industrial-proof enough to deal with the CELL.VALUE without getting an error. In this case, I simply want to process the number (114119882).

Thanks,

Ron

 

[JoeMo]

JoeMO,

You are correct. The cell is filled with pound-signs (###########). Again, I can correct the situation by simply changing the format of the cell. However, my interest is how to make the VBA industrial-proof enough to deal with the CELL.VALUE without getting an error. In this case, I simply want to process the number (114119882).

Thanks,

Ron

Try adapting the code below to your macro. Assumes you have the "too large" date in A1 formatted as a date.

Sub test()
Dim x As Long
On Error Resume Next
MsgBox [A1]
If Err.Number = 6 Then   ' ErrNumber 6 is overflow
    [A1].NumberFormat = "general"
   x = [A1]
   MsgBox x
End If
On Error GoTo 0
'Convert A1 back to date format if desired
'Process x 
End Sub

 

[ronwessel]

JoeMo,

Thanks for your example code. However, my preference is to NOT change anything on the source-data. I simply want to process the data, without changing anything. That's the part I cannot figure out a technique. Unless I change the number-format to General, or Date, I cannot avoid the VBA error. If I change the format to General, then I have the burden of changing it back once I'm done processing it. Therefore, I don't want to change it in the first-place! Just want to process it without producing errors.

Again, I appreciate your example, but not my preference....

 

[JoeMo]

JoeMo,

Thanks for your example code. However, my preference is to NOT change anything on the source-data. I simply want to process the data, without changing anything. That's the part I cannot figure out a technique. Unless I change the number-format to General, or Date, I cannot avoid the VBA error. If I change the format to General, then I have the burden of changing it back once I'm done processing it. Therefore, I don't want to change it in the first-place! Just want to process it without producing errors.

Again, I appreciate your example, but not my preference....

Not much of a burden to change the format back to date in your code - one line will do it. And you are left with a variable that represents the number itself for further processing. Did you run the simple test code I posted? Your preference seems to be untenable.

 

[ronwessel]

JoeMO,

Yes, I ran your code. But I had already coded a solution similar to yours.

Sorry, I just don't like the solution. My VBA is processing other people's files, and I don't want to be responsible for changing anything erroneously. Just because an error-code is captured, does NOT necessarily mean universally, the issue was caused by THIS SPECIFIC situation. There may be other issues causing the same error-code. Therefore, I do NOT want the burden of figuring out if this is the specific reason for the error, or the other potentially HUNDREDS of reasons that could generate this error-code. Safest bet, is: DON'T CHANGE ANYTHING!

Now, if you disagree, I can respect that. But given that you don't know my situation, what data it is, the importance of my not changing any of this data, etc., for you to assume my preference is "untenable", might be.... untenable. (I would have preferred "indefensible".) Again, I sincerely thank you for your suggestion, and taking the time to code your example. However, I am looking for an approach that does NOT require me to change the spreadsheet. I think I made this clear very early in the thread. So, if you have a solution that does NOT require me to change the spreadsheet, then I welcome your feedback. I already had your solution when I posted. I posted because I was hoping for a better one, and still hope for an improvement.

 

[JoeMo]

JoeMO,

Yes, I ran your code. But I had already coded a solution similar to yours.

Sorry, I just don't like the solution. My VBA is processing other people's files, and I don't want to be responsible for changing anything erroneously. Just because an error-code is captured, does NOT necessarily mean universally, the issue was caused by THIS SPECIFIC situation. There may be other issues causing the same error-code. Therefore, I do NOT want the burden of figuring out if this is the specific reason for the error, or the other potentially HUNDREDS of reasons that could generate this error-code. Safest bet, is: DON'T CHANGE ANYTHING!

Now, if you disagree, I can respect that. But given that you don't know my situation, what data it is, the importance of my not changing any of this data, etc., for you to assume my preference is "untenable", might be.... untenable. (I would have preferred "indefensible".) Again, I sincerely thank you for your suggestion, and taking the time to code your example. However, I am looking for an approach that does NOT require me to change the spreadsheet. I think I made this clear very early in the thread. So, if you have a solution that does NOT require me to change the spreadsheet, then I welcome your feedback. I already had your solution when I posted. I posted because I was hoping for a better one, and still hope for an improvement.

The simple code I posted does not change the values in the sheet (no quantitative change to the sheet), simply the format, which allows extracting the value you want to process further.