VBA Timezone issue

Question

I have set up a script to populate UTC and Beijing when EST is entered it works fine.

isDst = isdaydat(CDate(Me.dtefrm.Value))

varest = Application.WorksheetFunction.Text(Me.estfrm.Value, "hh:mm:ss")

Me.estfrm.Value = varest
If isDst = True Then
    ut = CDate(varest)
    ut = DateAdd("h", 4, ut)
    ut = Application.WorksheetFunction.Text(ut, "hh:mm:ss")
    Me.utcfrm.Value = ut
    bt = CDate(ut)
    bt = DateAdd("h", 8, bt)
    bt = Application.WorksheetFunction.Text(bt, "hh:mm:ss")
    Me.beifrm.Value = bt

    Else

    ut = CDate(varest)
    ut = DateAdd("h", 5, ut)
    ut = Application.WorksheetFunction.Text(ut, "hh:mm:ss")
    Me.utcfrm.Value = ut
    bt = CDate(ut)
    bt = DateAdd("h", 8, bt)
    bt = Application.WorksheetFunction.Text(bt, "hh:mm:ss")
    Me.beifrm.Value = bt

End If

However, when I try to modify this to calculate EST and Beijing when UTC is given, I run into an error. I wonder if it has to do with the fact that I'm subtracting from UTC instead of adding to EST and I know excel freaks out with negative time values. When I did this on columns, I would use the Mod function but when I try to insert a worksheet Mod function it doesn't seem to show up in the intellisense.

isDst = isdaydat(CDate(Me.dtefrm.Value))

varutc = Application.WorksheetFunction.Text(Me.utcfrm.Value, "hh:mm:ss")

Me.utcfrm.Value = varutc

If isDst = True Then
    et = CDate(varutc)
    et = DateAdd("h", -4, et)
    et = Application.WorksheetFunction.Text(et, "hh:mm:ss")
    Me.estfrm.Value = et
    bt = CDate(varutc)
    bt = DateAdd("h", 8, bt)
    bt = Application.WorksheetFunction.Text(bt, "hh:mm:ss")
    Me.beifrm.Value = bt

    Else

    et = CDate(varutc)
    et = DateAdd("h", -5, ut)
    et = Application.WorksheetFunction.Text(et, "hh:mm:ss")
    Me.estfrm.Value = et
    bt = CDate(varutc)
    bt = DateAdd("h", 8, bt)
    bt = Application.WorksheetFunction.Text(bt, "hh:mm:ss")
    Me.beifrm.Value = bt

End If

When I run this and try to enter time into the UTC box, I get Runtime error "5" invalid procedure or argument and when I debug it goes to the line

et = Application.WorksheetFunction.Text(et, "hh:mm:ss")

in the second routine, I posted above.

I'm pretty sure this has to do with negative value. Help!

Answer 1

First, I would suggest changing all occurrences of Application.WorksheetFunction.Text to Format (to use a VBA function instead of a worksheet function). The existing parameters should be fine as-is.

Application.WorksheetFunction.Text and Format both return strings (text) -- not dates, and therefore can be used to display dates but not to do calculations with them.

If you're not declaring your variables, you should be. You can ensure that none are missed by adding a line Option Explicit at the top of every module.

A simplified version of your code:

Dim isDST As Boolean, varUTC As Date

isDST = isdaydat(CDate(Me.dteFrm.Value))
varUTC = CDate(Me.utcFrm.Value)

Me.utcFrm.Value = Format(varUTC, "hh:mm:ss")
Me.estFrm.Value = Format(DateAdd("h", IIf(isDST, -4, -5), varUTC), "hh:mm:ss")
Me.beiFrm.Value = Format(DateAdd("h", 8, varUTC), "hh:mm:ss")

Answer 2

I didn't fully understand your code but presumed that you have a user form with text boxes for the different times. Proceeding from that idea I thought that you shouldn't have a problem with negative numbers unless you took a wrong approach. In principle, you should calculate date/times, not merely times, because if you don't you can't accommodate the different dates you must invariably countenance if you compare Beijing time with EST. Therefore my code below takes any time you might enter, adds the current day to it, converts the result to UTC and then converts UTC to the target times. The default is the computer's time.

The code below should be pasted into the code sheet of a userform.

Option Explicit

    ' In the TbxUTC's Tag property:-
    ' enter a positive or negative value indicating the time difference
    ' between your computer's time and UCT (8 = China time)

    ' In the TbxBEI and TbxEST's Tag properties:-
    ' enter a positive or negative value indicating the time difference
    ' between the named time zone and UCT (Beijing +8, EST -5)

Private Sub UserForm_Initialize()
    ' 06 Jan 2018
    SetTime
End Sub

Private Sub TbxUTC_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    ' 06 Jan 2018
    Cancel = Not SetTime(Me.ActiveControl)
End Sub

Private Sub TbxBEI_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    ' 06 Jan 2018
    Cancel = Not SetTime(Me.ActiveControl)
End Sub

Private Sub TbxEST_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    ' 06 Jan 2018
    Cancel = Not SetTime(Me.ActiveControl)
End Sub

Private Function SetTime(Optional Ctl As MSForms.TextBox) As Boolean
    ' 06 Jan 2018
    ' return Not True if an invalid date was entered

    ' set the format in which to display the time (date):-
    Const TimeFormat As String = "HH:mm:ss (ddd)"

    Dim Local_Utc As Integer                    ' computer time : UTC
    Dim Bei_Utc As Integer                      ' Beijing time : UTC (= +8)
    Dim Est_Utc As Integer                      ' EST : UTC (= -5)
    Dim UTC, EST, BEI
    Dim Tmp As String, n As Integer

    Local_Utc = TbxUTC.Tag                      ' Set in Tag property:-
    Bei_Utc = TbxBEI.Tag
    Est_Utc = TbxEST.Tag

    If Ctl Is Nothing Then                      ' upon initialisation
        ' reset time to computer time
        UTC = Now() - (Local_Utc / 24)
    Else
        Tmp = Ctl.Text
        n = InStr(Tmp, "(")
        If n Then Tmp = Left(Tmp, n - 1)
        UTC = Trim(Tmp)
        If IsDate(UTC) Then
            UTC = CDate(UTC)
            If Int(UTC) = 0 Then UTC = UTC + CLng(Date)
            If Not (Ctl Is TbxUTC) Then UTC = UTC - (Val(Ctl.Tag) / 24)
        Else
            MsgBox "Please enter a valid time.", _
                   vbExclamation, "Invalid time/date format"
            Exit Function
        End If
    End If

    Application.EnableEvents = False
    TbxUTC.Text = Format(UTC, TimeFormat)
    TbxBEI.Text = Format(UTC + (Bei_Utc / 24), TimeFormat)
    TbxEST.Text = Format(UTC + (Est_Utc / 24), TimeFormat)
    Application.EnableEvents = True
    SetTime = True
End Function

The userform must have three text boxes named "TbxUTC", TbxBEI" and "TbxEST". The Tag properties of these text boxes must show the time difference between the named time zone and UTC. In the case of TbxUTC that value should be zero but I used the Tag to hold the time difference between your computer time and UTC instead.

When the form starts up the current (computer) time will be shown. You can then enter another time in any of the 3 text boxes and the code will fill the other two with the matching time in the other zones.