Year, Month, Day of a Date

Dim exampleDate As Date
exampleDate = DateValue("Jun 19, 2010")
MsgBox Year(exampleDate)

DateAdd

Dim firstDate As Date, secondDate As Date

firstDate = DateValue("Jun 19, 2010")
secondDate = DateAdd("d", 3, firstDate)

MsgBox secondDate

Current Date & Time

MsgBox Now

Hour, Minute and Second

MsgBox Hour(Now)

TimeValue

MsgBox TimeValue("9:20:01 am")

Loop in EXCEL VBA

Max	Min	Average	Date
20	16		3/1/2001
22	17		3/2/2001
27	21		3/3/2001
29	23		3/4/2001
25	20		3/5/2001
25	21		3/6/2001
24	16		3/7/2001
23	17		3/8/2001
22	20		3/9/2001
21	26		3/10/2001
27	21		3/11/2001
20	16		3/12/2001
22	17		3/13/2001
26	20		3/14/2001
23	18		3/15/2001
29	23		3/16/2001
27	21		3/17/2001
28	22		3/18/2001
25	20		3/19/2001

'=========================================================== 
http://jeetexltips.blogspot.in/
'===========================================================
Sub DO_Until()
' This loop runs until there is nothing in the next column
    Do
    ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
    ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, 1))
End Sub

Sub Do_While()
' This loop runs as long as there is something in the next column
    Do While IsEmpty(ActiveCell.Offset(0, 1)) = False
    ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
    ActiveCell.Offset(1, 0).Select
    Loop
End Sub

Sub Do_While_Not()
' This loop runs as long as there is something in the next column
    Do While Not IsEmpty(ActiveCell.Offset(0, 1))
    ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
    ActiveCell.Offset(1, 0).Select
    Loop
End Sub

Sub DO_Until2()
' This loop runs as long as there is something in the next column
' It does not calculate an average if there is already something in the cell
    Do
    If IsEmpty(ActiveCell) Then
        ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
    End If
    ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, 1))
End Sub

Sub DO_Until3()
' This loop runs as long as there is something in the next column
' It does not try to calculate an average if there is already something in the cell
' nor if there is no data to average (to avoid #DIV/0 errors).
    Do
    If IsEmpty(ActiveCell) Then
        If IsEmpty(ActiveCell.Offset(0, -1)) And IsEmpty(ActiveCell.Offset(0, -2)) Then
            ActiveCell.Value = ""
        Else
            ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
        End If
    End If
    ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, 1))
End Sub

Sub FOR_NEXT()
' This loop repeats for a fixed number of times determined by the number of rows in the range
    Dim i As Integer
    For i = 1 To Selection.CurrentRegion.Rows.Count - 1
    ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
    ActiveCell.Offset(1, 0).Select
    Next i
End Sub

Sub FOR_NEXT()
' This loop repeats a fixed number of times getting its reference from elsewhere
    Dim i As Integer
    Dim intRowCount As Integer
    intRowCount = Range("A1").CurrentRegion.Rows.Count - 1
    For i = 1 To intRowCount
    ActiveCell.FormulaR1C1 = "=Average(RC[-5],RC[-6])"
    ActiveCell.Offset(1, 0).Select
    Next i
End Sub

Sub DO_Until4()
' This loop does the calculating itself and writes the result into each cell
    Do
    ActiveCell.Value = WorksheetFunction.Average(ActiveCell.Offset(0, -1).Value, ActiveCell.Offset(0, -2).Value)
    ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, 1))
End Sub

An Example of Looping Over Worksheets

Imagine that (somewhat egotistically) you decide to name all of your worksheets in a workbook after your company name (for us it's Wise Owl). 

Sub RenameWorksheets()

'a reference to each worksheet in the active workbook

Dim ws As Worksheet

'the index number to use

Dim SheetNumber As Integer

SheetNumber = 0

For Each ws In Worksheets

'for each worksheet, rename it

SheetNumber = SheetNumber + 1

ws.Name = "Wise Owl " & SheetNumber

Next ws

End Sub

Delete Worksheet

Sub dlt_Sheet()
    Dim ws As Worksheet
         On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets("Sheet1").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0   
End Sub