AnsweredAssumed Answered

VBA Timezone

Question asked by David Lane on Mar 27, 2020
Latest reply on Mar 27, 2020 by David Lane

I have a macro which makes a calculation based on the current hour. The problem is the calculation needs to always be the same no matter what timezone the user in located in. I have not done much VBA in Solidworks, mostly just Excel up to this point.

 

I found this code which will translate the current time to whatever timezone is desired. There is a problem with the two lines marked in bold. How can I fix this? I originally found the code here: VBA to to convert current system time to any desired Time Zone - YouTube 

Option Compare Database
Option Explicit

Private Const TIME_ZONE_ID_STANDARD As Long = 1
Private Const TIME_ZONE_ID_DAYLIGHT& = 2
Dim dteStart As Date, dteFinish As Date
Dim dteStopped As Date, dteElapsed As Date
Dim boolStopPressed As Boolean, boolResetPressed As Boolean


Private Type SYSTEMTIME
wyear As Integer
wmonth As Integer
wdayofweek As Integer
whour As Integer
wminute As Integer
wsecond As Integer
wmilliseconds As Integer
End Type

Private Type TIME_ZONE_INFORMATION
bias As Long
Standardname(1 To 63) As Byte
standarddate As SYSTEMTIME
standardbias As Long
Daylightname(0 To 63) As Byte
daylightdate As SYSTEMTIME
daylightbias As Long
End Type
Private Declare Function GetTimeZoneInformation Lib "kernel32" (IpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long
Public resetMe As Boolean
Public myVal As Variant


Public Function mytime() As String

'-----------------------------
'Thanks for downloading the code.
'Please visit our channel for a quick explainer on how to use this code.
'Feel free to update the code as per your need and also share with your friends.
'Download free codes from http://vbaa2z.blogspot.com
'Support our channel: youtube.com/vbaa2z
'Author: L Pamai (vbaa2z.team@gmail.com)
'-----------------------------

Dim tzi As TIME_ZONE_INFORMATION
Dim gmt As Date
Dim dwbias As Long
Dim tmp As String
Select Case GetTimeZoneInformation(tzi)
Case TIME_ZONE_ID_DAYLIGHT
dwbias = tzi.bias + tzi.daylightbias
Case Else
dwbias = tzi.bias + tzi.standardbias
End Select
gmt = DateAdd("n", dwbias, Now + TimeSerial(5, 30, 0))
tmp = Format$(gmt, "MM/DD/YYYY HH:MM:SS AM/PM")
mytime = tmp
End Function

Outcomes