日期格式VBA Excel出现问题

Question

我有一个Excel工具，它可以将当前时间和日期转换为UTC时间，然后将该时间和日期保存在数据库（也是Excel文件）中。问题是，例如，当墨西哥的某个人试图使用该工具时，日期的格式不同（DD-MM-YY而不是MM-DD-YY），并且它被错误地保存在数据库中。以下是将当前时间和日期转换为UTC的代码：

Option Explicit
Public Declare Function SystemTimeToFileTime Lib _
  "kernel32" (lpSystemTime As SYSTEMTIME, _
  lpFileTime As FILETIME) As Long
Public Declare Function LocalFileTimeToFileTime Lib _
  "kernel32" (lpLocalFileTime As FILETIME, _
  lpFileTime As FILETIME) As Long
Public Declare Function FileTimeToSystemTime Lib _
  "kernel32" (lpFileTime As FILETIME, lpSystemTime _
  As SYSTEMTIME) As Long
Public Type FILETIME
    dwLowDateTime As Long
    dwHighDateTime As Long
End Type
Public Type SYSTEMTIME
    wYear As Integer
    wMonth As Integer
    wDayOfWeek As Integer
    wDay As Integer
    wHour As Integer
    wMinute As Integer
    wSecond As Integer
    wMilliseconds As Integer
End Type
Public Function LocalTimeToUTC(dteTime As Date) As Date
    Dim dteLocalFileTime As FILETIME
    Dim dteFileTime As FILETIME
    Dim dteLocalSystemTime As SYSTEMTIME
    Dim dteSystemTime As SYSTEMTIME
    dteLocalSystemTime.wYear = CInt(Year(dteTime))
    dteLocalSystemTime.wMonth = CInt(Month(dteTime))
    dteLocalSystemTime.wDay = CInt(Day(dteTime))
    dteLocalSystemTime.wHour = CInt(Hour(dteTime))
    dteLocalSystemTime.wMinute = CInt(Minute(dteTime))
    dteLocalSystemTime.wSecond = CInt(Second(dteTime))
    Call SystemTimeToFileTime(dteLocalSystemTime, _
      dteLocalFileTime)
    Call LocalFileTimeToFileTime(dteLocalFileTime, _
      dteFileTime)
    Call FileTimeToSystemTime(dteFileTime, dteSystemTime)
    LocalTimeToUTC = CDate(dteSystemTime.wMonth & "/" & _
      dteSystemTime.wDay & "/" & _
      dteSystemTime.wYear & " " & _
      dteSystemTime.wHour & ":" & _
      dteSystemTime.wMinute & ":" & _
      dteSystemTime.wSecond)
End Function

然后这里是写日期和时间的公式：localtimetoutc(NOW())

然后，我只需将此单元格的值保存到VBA中的一个变量（一个变量）中，然后将该变量的值粘贴到数据库中。它非常适合美国用户，但有时对非美国用户来说会失败。我怎样才能确保它不会失败？也就是说，如何强制VBA使用我需要的格式？

问候，

Answer 1

尝试：

Public Function LocalTimeToUTC(dteTime As Date) As Date
    Dim dteLocalFileTime As FILETIME
    Dim dteFileTime As FILETIME
    Dim dteLocalSystemTime As SYSTEMTIME
    Dim dteSystemTime As SYSTEMTIME
    dteLocalSystemTime.wYear = CInt(Year(dteTime))
    dteLocalSystemTime.wMonth = CInt(Month(dteTime))
    dteLocalSystemTime.wDay = CInt(Day(dteTime))
    dteLocalSystemTime.wHour = CInt(Hour(dteTime))
    dteLocalSystemTime.wMinute = CInt(Minute(dteTime))
    dteLocalSystemTime.wSecond = CInt(Second(dteTime))
    Call SystemTimeToFileTime(dteLocalSystemTime, _
      dteLocalFileTime)
    Call LocalFileTimeToFileTime(dteLocalFileTime, _
      dteFileTime)
    Call FileTimeToSystemTime(dteFileTime, dteSystemTime)
    LocalTimeToUTC = DateSerial(dteSystemTime.wYear, dteSystemTime.wMonth, dteSystemTime.wDay) + TimeSerial(dteSystemTime.wHour, dteSystemTime.wMinute, dteSystemTime.wSecond)
End Function