VBA overflow when work with .value of date field that has ##############

Irish_Griffin

Board Regular
Joined
Jan 16, 2009
Messages
138
Hi Forum,
Great news, just landed a decent vba gig, looks like I'll be coming back to the community ! Been a while.

So I'm already in trouble... lol, ahhhh VBA, I remember this. lol

I've got an on change event that test to make sure the user puts in an actual date.
The cell it's testing is formatted as a date.

when the user puts in gibberish, the cell reads ################

When "###############" goes through my code it throughs an over flow error.

Code:
If Me.Cells(Target.Row, Target.Column).Value <> "" And IsDate(Me.Cells(Target.Row, Target.Column).Value) = False Then
        MsgBox "not a date"
    End If

How can I test for this ################# condition ?

Thanks,
Griff
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
eeewwwww... yuck ness solution

Code:
On Error GoTo Not_a_date
    If IsDate(Me.Cells(Target.Row, Target.Column).Value) = False Then
Not_a_date:
        MsgBox "not a date"
    End If
 
Upvote 0
Just FYI - the infinite stream of hashtags will happen if a negative number is entered in a cell formatted as date or time. It will not happen if text is entered in the cell. Either way the IsDate check will sort out invalid entries.
 
Upvote 0
eeewwwww... yuck ness solution

Code:
On Error GoTo Not_a_date
    If IsDate(Me.Cells(Target.Row, Target.Column).Value) = False Then
Not_a_date:
        MsgBox "not a date"
    End If
Be wary of the IsDate function... it will accept things you might not want it to (for example, "1:23.45 a 12-23/45" as well as dates in both mdy and dmy ordering).
 
Upvote 0
Thanks guys, always good to I'm not alone in the VBA world... lol

ya, I know the in's and outs of isdate()

So the field format is a date
field value = "12122016" (php date format )
cell shows #################

Test code
Code:
dim test_for_error as string
test_for_error = Me.Cells(Target.Row, Target.Column).Value

this errors out, runtime 6 , overflow..... ridiculous.... ahhh vba.... good times
 
Upvote 0
Well... I'm not proud of an Error Goto .... such yucky code.... but I don't see another solution

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

Dim test_for_error As String
On Error GoTo Not_a_date
test_for_error = Me.Cells(Target.Row, Target.Column).Value
On Error GoTo 0

If Me.Cells(Target.Row, Target.Column - 1).Value = "Start Date" Or Me.Cells(Target.Row, Target.Column - 1).Value = "Start Date" Then
    
    If IsDate(Me.Cells(Target.Row, Target.Column).Value) = False Then
Not_a_date:
        Me.Cells(Target.Row, Target.Column).Value = ""
        MsgBox "not a date"
    End If

End If
End Sub

Let me know if you have a better way to test for this condition.....

Cheers, feels good to back at this stuff,
-Griff :rolleyes:
 
Upvote 0
LOL... already got my first endless loop.... what a way to get back to this stuff.....

See if you would have spotted this loop :

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Me.Cells(Target.Row, Target.Column - 1).Value = "Start Date" Or Me.Cells(Target.Row, Target.Column - 1).Value = "Start Date" Then
    
    If IsDate(Me.Cells(Target.Row, Target.Column).Value) = False Then
Not_a_date:
        MsgBox "not a date"
        Me.Cells(Target.Row, Target.Column).Value = ""
    End If

End If

No BREAK key on my LAPTOP.... lol had to cntrl+alt+delete out.... good stuff :ROFLMAO:
 
Upvote 0
So the field format is a date
field value = "12122016" (php date format )
cell shows #################
That may be a date to "php", but VB sees it as a large integer value (Long data type). Unfortunately, both your month and day parts of the date are the same, so I cannot tell which order the date values are in (mdy or dmy). With dates, it is always a good idea to make the day value larger than 12 so we can tell the ordering. And, since both numbers are 12, I cannot use the current month as a clue as to which value is the month number. So, what is the date order for the parts of that number?
 
Upvote 0
That may be a date to "php", but VB sees it as a large integer value (Long data type). Unfortunately, both your month and day parts of the date are the same, so I cannot tell which order the date values are in (mdy or dmy). With dates, it is always a good idea to make the day value larger than 12 so we can tell the ordering. And, since both numbers are 12, I cannot use the current month as a clue as to which value is the month number. So, what is the date order for the parts of that number?

Hi Rick, thanks for jumping in.

My post is asking about handeling / seeing overflow error befor it breaks the code.
I know the date format in the example is incorrect.

For clarification, I'm making a user form, where the vba ensures they are inputting a proper date format.
Thus I'm debugging when they don't , hence the example.... can't have it error out on my users.

ps php date format is ddmmyyyy

cheers
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top