DATESERIAL problem

[Jomathr]

I have an excel vba file that is used in multiple country and on spanish system the code I have using the DATESERIAL return a custom error that I created to inform the user. I am wondering if the behavior of DATESERIAL differ depending on regional settings of the computer since it return a date.

here is the code I use:

Private Sub TxtDate_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Dim langue As String
Dim LastDay As Date
Dim FirstDay As Date
Dim test3 As Integer
FirstDay = Range("AcctingPeriod").Value + 3 & "/01/" & Range("FiscalYear").Value - 1
LastDay = DateSerial(Year(FirstDay), Month(FirstDay) + 1, 0)  ' last day of the month
langue = Worksheets("Config").Range("Language")


With Me.TxtDate.Value
If Range("AcctingPeriod").Value + 4 > 12 Then
    FirstDay = Range("AcctingPeriod").Value - 9 & "/01/" & Range("FiscalYear").Value
    LastDay = DateSerial(Year(FirstDay), Month(FirstDay) + 1, 0)  ' last day of the month
    If TxtDate.Value >= FirstDay And TxtDate.Value <= LastDay Then
    Else
        MsgBox Worksheets(langue).Range("DateError")
        Cancel = True
    End If
Else
    If TxtDate.Value >= FirstDay And TxtDate.Value <= LastDay Then
    Else
        MsgBox Worksheets(langue).Range("DateError")
        Cancel = True
    End If
End If
End With
End Sub

if the DATESERIAL depend on regional settings is there a way around it to check the date entered using either Format() or tweaking the code to accept any date format. The default format I use in the textbox is YYYY/MM/DD.

Hope someone encountered this before.

Thank you in advance for your time

[GTO]

Hi there,

I have no experience in dealing with regional differences (or "local expectations"), so you may well get a far better answer than this. That said, I would suspect what you are handing to 'FirstDay' (a String) to be the issue, not DateSerial. I say this because where you plug the integers into DateSerial are dependable, but in FirstDay you are depending on the String being interpreted to the correct date. I would try plugging the bits in "("AcctingPeriod").Value - 9 & "/01/" & Range("FiscalYear").Value" in as the arguments (forced to longs if needed) for DateSerial and see if that corrects the issue.

Hope that helps,

Mark

[Jomathr]

Thank you for your answer,

it did work, tried a few different regional setting and it solved the problem, thank you very much!

but if the string input was wrong wouldn't I get a type mismatch since I declared firstday as a date? And it wouldn't explain why the code work perfectly in a English(Canada) setting. not sure I understand the difference