What is the difference between Worksheets and Sheets?

  • Worksheets include all worksheets in a workbook, excluding sheets who are type charts.
  • Sheets include all sheets in a workbook, including sheets who are type charts
  • If you are not using chart sheets, using either one yields the same results
Advertisements

What is the syntax to declare procedures?

Private Function <your name> ()
   ‘your code here
   ‘callable only by procedures in the same module
End Function


Public Function <your name> ()
   ‘callable from any procedure
End Function

 
Private Sub <your name> ()
   ‘callable only by procedures in the same module
   ‘cannot be called from Macros dialog box
End Sub

 
Public Sub <your name> ()
   ‘callable from any procedure of any module
   ‘can run procedure from Macros dialog box
End Sub

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 a procedure?

  • Grouped statements that relate to a specific task
  • Used for complex tasks that can be further broken down into smaller tasks
  • Two types of procedures: sub and function
Function <name>(<parameters>)
‘callable from anywhere in program
‘can return values
‘put function declarations in a module sheet
End Function
Sub <name>(<parameters>)
‘do not return values
End Sub