How to use the FORMAT Function with Dates (VBA)

How to use the FORMAT Function with Dates (VBA)

• Example
  • Dim LValue As String
  • LValue = Format(Date, "yyyy/mm/dd")
• Format ( expression, [ format, [ firstdayofweek, [firstweekofyear] ] ] )
• Format(#17/04/2004#, "Short Date")
  • Result: '17/04/2004'
• Format(#17/04/2004#, "Long Date")
  • Result: 'April 17, 2004'
• Format(#17/04/2004#, "yyyy/mm/dd")
  • Result: '2004/04/17'

get path of current workbook

get path of current workbook

• for just the path itself (without the workbook name)
  • Application.ActiveWorkbook.Path
• for the path with the workbook name
  • Application.ActiveWorkbook.FullName

CountIf

CountIf

• COUNTIF( range, criteria )
• =COUNTIF(A2:A7, D2)
• =COUNTIF(A:A, D2)
• =COUNTIF(A2:A7, ">=2001")
• You can also use a named range in the COUNTIF function
• =COUNTIF(family, D2)
• =COUNTIF(family, ">=2001")
• To use a cell reference in the criteria
  • =COUNTIF(C4:C19,">="&A1)

Loop through all workbooks

Loop through all workbooks

• Dim WS_Count as integer
• Dim I as integer
• WS_Count = Activeworkbook.Worksheets.Count
• For I = 1 to WS_Count
• Msgbox Activeworkbook.Worksheets(I).Name
• Next I