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,

 

[ronwessel]

For the love of God. These spreadsheets are NOT mine, and I do not want to alter anything about them. Period. Changing values. Changing formats. Anything.

I guarantee, an end-user would disagree with your assessment of no-change, if the macro changed a "DATE" cell to "GENERAL", causing 1/1/2015 to suddenly display as 42005.