PDA

View Full Version : VBA issue with date format



Goodangel
06-21-2017, 06:41 AM
Hello,

I have an issue with a date format in an excel file.

When I run the report (excel is connected with Noetix and Oracle e business suite) I have results with no problem. Currently I am in Vienna, Austria.

However when a colleague of mine in Vietnam run the same report then his has en error. The VBA code that is running is the following. The date format in my laptop (windows 7) is dd/mm/yyyy and as I saw in my colleague (windows 8 or 10) is mm/dd/yyyy.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$D$7" Then

user_name = InputBox("Please enter your Oracle user name:")
If user_name = "" Then End
Oracle_password = InputBox("Please enter your Oracle password:")
If Oracle_password = "" Then End
end_date = Format(InputBox("Please enter the period end date in the format dd/mm/yyyy (eg 30/06/2013):", Default:=Format(WorksheetFunction.EoMonth(Date, -1), "dd/mm/yyyy")), "dd/mm/yyyy")
If end_date = "" Then End

Range("N10").Value = user_name
Range("N11").Value = Oracle_password
Range("D7").Value = end_date

End If

End Sub



Is the problem coming from the date format and how can I solve it so even if she has different date format to eventually run the report with the one I have.

Thank you in advance for the help.

Regards,

Paul_Hossler
06-21-2017, 06:55 AM
You can use the[#] icon to add
... tags and paste your macro between then

MINCUS1308
06-21-2017, 07:06 AM
This ran without error on my windows 10 machine with the format mm/dd/yyyy.

While this probably isn't the best solution you may try adding the following lines
add this so that it is the first line of your sub

On Error Resume Next
add this just above the 'End Sub' line

On Error GoTo 0

Goodangel
06-21-2017, 07:35 AM
Hello Paul,


Thank you for your reply.

As my VBA knowledge is not the best, what you are actually suggesting as I didn't fully understand.

Thank for the help and sorry for the inconvenience.

Thanks,

Evan

MINCUS1308
06-21-2017, 07:43 AM
Everyone starts somewhere :)
Just copy and paste this:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Target.Address = "$D$7" Then

user_name = InputBox("Please enter your Oracle user name:")
If user_name = "" Then End
Oracle_password = InputBox("Please enter your Oracle password:")
If Oracle_password = "" Then End
end_date = Format(InputBox("Please enter the period end date in the format dd/mm/yyyy (eg 30/06/2013):", Default:=Format(WorksheetFunction.EoMonth(Date, -1), "dd/mm/yyyy")), "dd/mm/yyyy")
If end_date = "" Then End

Range("N10").Value = user_name
Range("N11").Value = Oracle_password
Range("D7").Value = end_date

End If
On Error GoTo 0
End Sub

p45cal
06-21-2017, 07:46 AM
I strongly suspect international settings are the culprit here. In the US/UK the day portion of a format string is d, in France it's j, in Vietnam I haven't the foggiest.
While I have little experience of international problems with vba code, and there may be a much easier solution than the following, have a go with:
Instead of:

end_date = Format(InputBox("Please enter the period end date in the format dd/mm/yyyy (eg 30/06/2013):", Default:=Format(WorksheetFunction.EoMonth(Date, -1), "dd/mm/yyyy")), "dd/mm/yyyy")
try:
myDateFormat = String(2, Application.International(xlDayCode)) & "/" & String(2, Application.International(xlMonthCode)) & "/" & String(4, Application.International(xlYearCode))
end_date = Format(InputBox("Please enter the period end date in the format dd/mm/yyyy (eg 30/06/2013):", Default:=Format(WorksheetFunction.EoMonth(Date, -1), myDateFormat)), myDateFormat)

See also:
https://www.rondebruin.nl/win/s9/win012.htm
http://www.oaltd.co.uk/ExcelProgRef/Ch22/default.htm
https://bettersolutions.com/excel/macros/enumerations-xlapplicationinternational.htm
https://www.mrexcel.com/forum/excel-questions/664404-regional-settings-change-visual-basic-applications-date-formatting.html
https://msdn.microsoft.com/en-us/library/bb177675(v=office.12).aspx

Paul_Hossler
06-21-2017, 08:10 AM
Hello Paul,

As my VBA knowledge is not the best, what you are actually suggesting as I didn't fully understand.

Thank for the help and sorry for the inconvenience.

Evan

Evan, no problem. Just a non-VBA suggestion / request that people use the [#] icon to add the CODE tags since it formats a macro and sets it off to make it easier for people to see

Paul_Hossler
06-21-2017, 08:26 AM
I'm sure P45cal is correct and it is a Localization issue

If the value is recognized by Excel as a Date, then the displayed value will adjust

I made a few changes to your macro, but look at the CDate function




Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim user_name As String, Oracle_Password As String, end_date As String

If Target.Cells(1, 1).Address <> "$D$7" Then Exit Sub

user_name = InputBox("Please enter your Oracle user name:")
If user_name = "" Then Exit Sub ' don't think you meant 'End'

Oracle_Password = InputBox("Please enter your Oracle password:")
If Oracle_Password = "" Then Exit Sub

'this is a string
end_date = Format(InputBox("Please enter the period end date in the format dd/mm/yyyy (eg 30/06/2013):", Default:=Format(WorksheetFunction.EoMonth(Date, -1), "dd/mm/yyyy")), "dd/mm/yyyy")
If end_date = "" Then Exit Sub

Range("N10").Value = user_name
Range("N11").Value = Oracle_Password

If IsDate(Range("D7").Value) Then
Range("D7").Value = CDate(Range("D7").Value) '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
End If

End Sub




Just by changing the Location of where the PC thinks it is, the format of the date value changes

1955719558

Goodangel
06-22-2017, 12:12 AM
Hello guys,


Thank you all for your support!I changed the excel report I have based on p45cal suggestion. I could run it but my colleague in Vietnam again couldn't..I will have a call with her and check the system date format. I will update accordingly!

Thanks once again.

Regards,

Evan

Paul_Hossler
06-22-2017, 06:27 AM
Find out what the PC regions are set to also

That'll be helpful

Goodangel
06-23-2017, 12:40 AM
Hello guys,


I have the date regions which I attach them here. (I also atatch my PC region settings for your information). It is nto totally the same I think. She managed to run the excel report at least but with small issues. I hereby attach my PC settings 19577 And in the following image she is showing me her PC date options. 1957819579What option should be the best for the VBA to run correctly? it seems that this excel file I have is totally dependent on the date. I think because the data are retrieved from Oracle where the date format is dd/mm/yyyy.

Thanks for your continuous help.

Regards,

Evan

mdmackillop
06-23-2017, 02:13 AM
I don't know if the Short Date format can be determined from the PC. I would guess so. Meantime, give this a try.


Option Explicit
Sub Test()
Dim Dt As Date
Dt = InputBox("Enter date in " & DtF)
MsgBox Format(Dt, "ddd mmm dd yyyy") 'Format as required
End Sub


Function DtF()
Select Case CDate(36891)
Case "31/12/2000" 'UK
DtF = "dd/mm/yy"
Case "12/31/2000" 'US
DtF = "mm/dd/yy"
Case "2000/31/12"
DtF = "yy/dd/mm"
Case "2000/12/31"
DtF = "yy/mm/dd"
End Select
End Function

Goodangel
06-26-2017, 04:15 AM
Thank you for your help mdmackillop but I have used p45cal solution which worked eventually. At least my colleague has results even without the same date format as I have. (I have dd mm yyyy and she now has d mm yyyy or something similar..)
There are side effects though because of the following code I think. When she puts the password and user name, she can see the results in the specific cells that are mentioned below..I tried to change the reference cell but the excel is not working..so she has to manually change these cells with dates (luckily in these cells only dates appears..). If there is also a solution to the below or you need more info for a solution let me know! I appreciate your help!! Regards, Evan
Range("N10").Value = user_name
Range("N11").Value = Oracle_password
Range("D7").Value = end_date