VBA本地时区调整 [英] VBA local timezone adjustment
问题描述
说我有一个本地太平洋时间(UTC-8)Excel值。例如, 41656.67297
是 2014年1月17日下午4:09 。我无法控制此值。
Say I have a local Pacific Time (UTC-8) Excel value. For example 41656.67297
is 17 January 2014 4:09 PM
. I do not have control of this value.
我想相对于用户本地时间调整给定值。举例来说,都柏林的某人正在使用Excel工作簿。我想调整给定的数字并将其显示在他的本地时间 2014年1月18日上午12:09
。
I want to adjust the given value relative to the users local time. Say for example someone in Dublin is using the Excel workbook. I want to adjust the given number and display it in his local time, 18 January 2014 12:09 AM
. Is there any built in Excel functionality to make this smooth?
推荐答案
VBA本身不提供此选项,但是有Windows API,可让您执行所需的操作。 本文介绍了操作方法和此处是 GetTimeZoneInformation
上的MSDN页面。
VBA doesn't provide an option for this natively, but there's a Windows API that will allow you to do what you're looking for. This article describes how to do so and here is the MSDN page on GetTimeZoneInformation
.
在CPearson页面上,首先进行以下声明:
From the CPearson page, make these declarations first:
Option Explicit
Private 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
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' NOTE: If you are using the Windows WinAPI Viewer Add-In to get
' function declarations, not that there is an error in the
' TIME_ZONE_INFORMATION structure. It defines StandardName and
' DaylightName As 32. This is fine if you have an Option Base
' directive to set the lower bound of arrays to 1. However, if
' your Option Base directive is set to 0 or you have no
' Option Base diretive, the code won't work. Instead,
' change the (32) to (0 To 31).
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Type TIME_ZONE_INFORMATION
Bias As Long
StandardName(0 To 31) As Integer
StandardDate As SYSTEMTIME
StandardBias As Long
DaylightName(0 TO 31) As Integer
DaylightDate As SYSTEMTIME
DaylightBias As Long
End Type
''''''''''''''''''''''''''''''''''''''''''''''
' These give symbolic names to the time zone
' values returned by GetTimeZoneInformation .
''''''''''''''''''''''''''''''''''''''''''''''
Private Enum TIME_ZONE
TIME_ZONE_ID_INVALID = 0 ' Cannot determine DST
TIME_ZONE_STANDARD = 1 ' Standard Time, not Daylight
TIME_ZONE_DAYLIGHT = 2 ' Daylight Time, not Standard
End Enum
Private Declare Function GetTimeZoneInformation Lib "kernel32" _
(lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long
Private Declare Sub GetSystemTime Lib "kernel32" _
(lpSystemTime As SYSTEMTIME)
然后此函数将找到用户的本地时间并从GMT偏移:
Then this function will find the user's local time and offset from GMT:
Function LocalOffsetFromGMT(Optional AsHours As Boolean = False, _
Optional AdjustForDST As Boolean = False) As Double
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' LocalOffsetFromGMT
' This returns the amount of time in minutes (if AsHours is omitted or
' false) or hours (if AsHours is True) that should be *added* to the
' local time to get GMT. If AdjustForDST is missing or false,
' the unmodified difference is returned. (e.g., Kansas City to London
' is 6 hours normally, 5 hours during DST. If AdjustForDST is False,
' the resultif 6 hours. If AdjustForDST is True, the result is 5 hours
' if DST is in effect.)
' Note that the return type of the function is a Double not a Long. This
' is to accomodate those few places in the world where the GMT offset
' is not an even hour, such as Newfoundland, Canada, where the offset is
' on a half-hour displacement.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim TBias As Long
Dim TZI As TIME_ZONE_INFORMATION
Dim DST As TIME_ZONE
DST = GetTimeZoneInformation(TZI)
If DST = TIME_ZONE_DAYLIGHT Then
If AdjustForDST = True Then
TBias = TZI.Bias + TZI.DaylightBias
Else
TBias = TZI.Bias
End If
Else
TBias = TZI.Bias
End If
If AsHours = True Then
TBias = TBias / 60
End If
LocalOffsetFromGMT = TBias
End Function
您可以使用以下代码测试代码:
You can test the code with:
MsgBox (LocalOffsetFromGMT())
然后将此偏移量添加到原始时间,您可以执行以下操作:
Then to add this offset to the original time you can do the following:
OriginalTime + TIME(LocalOffsetFromGMT(True, True), 0, 0)
这篇关于VBA本地时区调整的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!