What is the syntax to run a procedure based on a timer?

Sub <procedure name>()
   'code to be run
   Application.OnTime Now + TimeValue("00:00:00"), "<procedure name>"
End Sub
'example
Sub runsEveryTenSeconds()
    If formClock.Visible = False Then Exit Sub

    Call updateClock
    Application.OnTime Now + TimeValue("00:00:10"), "runsEveryTenSeconds"
End Sub

 

Advertisements

What is the syntax to format a date?

FormatDateTime (date, format)
‘date – mandatory parameter
‘format – specifies which format to use; optional parameter

 

Format values

  • 0 = vbGeneralDate – Default.
  • 1 = vbLongDate
  • 2 = vbShortDate
  • 3 = vbLongTime
  • 4 = vbShortTime

 

‘examples
MsgBox (FormatDateTime("2064-08-15 20:25")) ‘returns 8/15/2064 8:25:00 PM
MsgBox (FormatDateTime(#5/21/2075#, 1)) ‘returns Tuesday, May 21, 2015
MsgBox (FormatDateTime(#5/21/2075#, 2)) ‘returns 5/21/2075
MsgBox (FormatDateTime("12:34:17", 3)) ‘returns 12:34:17 PM
MsgBox (FormatDateTime("2050-08-21 12:34:17", 4)) ‘returns 12:34

What is the syntax to return the difference between two times?

DateDiff (interval, date1, date2, firstdayofwk, firstdayofyr)
‘interval – can be one of the following listed in bullet points; mandatory
‘date1, date2 are mandatory
‘firstdayofweek – can be values 1-7 for Sunday-Saturday, or 0 for system’s version; optional parameter
‘firstdayofyear – really useless optional parameter, just ignore it

Interval values

  • d – day of the year.
  • m – month of the year
  • yyyy -Year
  • w -Weekday
  • ww -week
  • q -quarter
  • h -Hour
  • n -Minute
  • s –Second
‘examples
MsgBox (DateDiff("yyyy", #2/23/2024#, #12/5/2034#)) '10 years
MsgBox (DateDiff("d", #2/1/2050#, #1/28/2050#)) '-4 days
MsgBox (DateDiff("h", "01-Jan-20 00:00:00", "01-Jan-20 23:59:00")) '23 hours
MsgBox (DateDiff("n", "01-Jan-20 12:00:00", "01-Jan-20 12:59:00")) '59 minutes
MsgBox (DateDiff("d", Date, txtDate.Value)) 'difference between a user inputted date and today

 

What is the syntax to get the date after adding some time?

DateAdd (interval, number, date)
'all three are mandatory parameters
‘interval – can be one of the following listed in bullet points
‘number – can be any +/- integer
'date – can be variant or literal, represents starting date

Interval values

  • d – day of the year.
  • m – month of the year
  • yyyy -Year
  • w -Weekday
  • ww -week
  • q -quarter
  • h -Hour
  • n -Minute
  • s –Second
‘examples
MsgBox (DateAdd("yyyy", 1, #1/1/2040#)) '1/1/2041
MsgBox (DateAdd("q", 1, #1/17/2040#)) '4/17/2040
MsgBox (DateAdd("d", -10, #1/17/2040#)) '1/7/2040
MsgBox (DateAdd("ww", 1, date1)) '1/6/1900
MsgBox (DateAdd("h", -1, "01-Jan-2040 12:00:00")) '1/1/2040 11:00:00 AM
MsgBox (DateAdd("n", 1, "01-Jan-2040 12:00:00")) '1/1/2040 12:01:00 PM
MsgBox (DateAdd("s", 1, "01-Jan-2040 12:00:00")) '1/1/2040 12:00:01 PM