VBA Code for Calendar(without ActiveX control)

Dim selecteddate As Date
Dim cbtarget As msforms.ComboBox, cbtarget1 As msforms.ComboBox
Dim myrng As Range

Private Sub ComboBox1_Change()
Dim dayscount
Dim tempvar
Dim dayName
Dim currentdate
Dim i As Integer, j As Integer
Dim cmdbutton1 As msforms.CommandButton
dayName = Format(DateSerial(year(Date), ComboBox1.ListIndex + 1, 1), "ddd")
Dim counter As Integer, daycounter As Integer
Dim cmdbutton() As Variant
dayscount = Day(DateSerial(year(Date), ComboBox1.ListIndex + 2, 0))
cmdbutton = Array("CommandButton1", "CommandButton2", "CommandButton3", "CommandButton4", "CommandButton5", "CommandButton6", "CommandButton7", "CommandButton8", "CommandButton9", "CommandButton10", "CommandButton11", "CommandButton12", "CommandButton13", "CommandButton14", "CommandButton15", "CommandButton16", "CommandButton17", "CommandButton18", "CommandButton19", "CommandButton20", "CommandButton21", "CommandButton22", "CommandButton23", "CommandButton24", "CommandButton25", "CommandButton26", "CommandButton27", "CommandButton28", "CommandButton29", "CommandButton30", "CommandButton31", "CommandButton32", "CommandButton33", "CommandButton34", "CommandButton35", "CommandButton36", "CommandButton37", "CommandButton38")
currentdate = Format(Date, "dd")
If ComboBox1.Value <> vbNullString Then
    If dayName = "Sun" Then
        counter = 0
        daycounter = 1
    ElseIf dayName = "Mon" Then
        Calendar.Controls(cmdbutton(0)).Visible = False
        counter = 1
        daycounter = 1
    ElseIf dayName = "Tue" Then
        Calendar.Controls(cmdbutton(0)).Visible = False
        Calendar.Controls(cmdbutton(1)).Visible = False
        counter = 2
        daycounter = 1
    ElseIf dayName = "Wed" Then
        Calendar.Controls(cmdbutton(0)).Visible = False
        Calendar.Controls(cmdbutton(1)).Visible = False
        Calendar.Controls(cmdbutton(2)).Visible = False
        counter = 3
        daycounter = 1
    ElseIf dayName = "Thu" Then
        Calendar.Controls(cmdbutton(0)).Visible = False
        Calendar.Controls(cmdbutton(1)).Visible = False
        Calendar.Controls(cmdbutton(2)).Visible = False
        Calendar.Controls(cmdbutton(3)).Visible = False
        counter = 4
        daycounter = 1
    ElseIf dayName = "Fri" Then
        Calendar.Controls(cmdbutton(0)).Visible = False
        Calendar.Controls(cmdbutton(1)).Visible = False
        Calendar.Controls(cmdbutton(2)).Visible = False
        Calendar.Controls(cmdbutton(3)).Visible = False
        Calendar.Controls(cmdbutton(4)).Visible = False
        counter = 5
        daycounter = 1
    ElseIf dayName = "Sat" Then
        Calendar.Controls(cmdbutton(0)).Visible = False
        Calendar.Controls(cmdbutton(1)).Visible = False
        Calendar.Controls(cmdbutton(2)).Visible = False
        Calendar.Controls(cmdbutton(3)).Visible = False
        Calendar.Controls(cmdbutton(4)).Visible = False
        Calendar.Controls(cmdbutton(5)).Visible = False
        counter = 6
        daycounter = 1
       
    End If
    For i = counter To (counter + dayscount) - 1
       
        Calendar.Controls(cmdbutton(i)).Visible = True
        Calendar.Controls(cmdbutton(i)).BackColor = RGB(135, 206, 250)
        Calendar.Controls(cmdbutton(i)).ForeColor = RGB(102, 0, 51)
        Calendar.Controls(cmdbutton(i)).FontBold = True
        Calendar.Controls(cmdbutton(i)).Caption = daycounter
        If (daycounter = currentdate) And ((ComboBox1.ListIndex + 1) = Month(Date)) Then
           
            Calendar.Controls(cmdbutton(i)).BackColor = RGB(255, 255, 204)
           
        End If
        daycounter = daycounter + 1
    Next i
    For j = (counter + dayscount) To 37
        Calendar.Controls(cmdbutton(j)).Visible = False
    Next j
End If

End Sub
Private Sub CommandButton1_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton1.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub
Private Sub CommandButton2_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton2.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub
Private Sub CommandButton3_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton3.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub
Private Sub CommandButton4_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton4.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub
Private Sub CommandButton5_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton5.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub
Private Sub CommandButton6_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton6.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub
Private Sub CommandButton7_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton7.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub
Private Sub CommandButton8_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton8.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub
Private Sub CommandButton9_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton9.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub
Private Sub CommandButton10_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton10.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub
Private Sub CommandButton11_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton11.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub
Private Sub CommandButton12_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton12.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub
Private Sub CommandButton13_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton13.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub
Private Sub CommandButton14_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton14.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub
Private Sub CommandButton15_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton15.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub
Private Sub CommandButton16_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton16.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub
Private Sub CommandButton17_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton17.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub
Private Sub CommandButton18_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton18.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub
Private Sub CommandButton19_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton19.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub
Private Sub CommandButton20_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton20.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub
Private Sub CommandButton21_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton21.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub
Private Sub CommandButton22_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton22.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub
Private Sub CommandButton23_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton23.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub
Private Sub CommandButton24_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton24.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub
Private Sub CommandButton25_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton25.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub
Private Sub CommandButton26_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton26.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub
Private Sub CommandButton27_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton27.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub
Private Sub CommandButton28_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton28.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub
Private Sub CommandButton29_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton29.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub
Private Sub CommandButton30_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton30.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub
Private Sub CommandButton31_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton31.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub
Private Sub CommandButton32_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton32.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub
Private Sub CommandButton33_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton33.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub
Private Sub CommandButton34_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton34.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub
Private Sub CommandButton35_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton35.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub
Private Sub CommandButton36_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton36.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub
Private Sub CommandButton37_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton377.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub
Private Sub CommandButton38_Click()
Set cbtarget1 = Calendar.ComboBox2
Set cbtarget = Calendar.ComboBox1
selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton38.Caption)
PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")
Unload Calendar
End Sub

Private Sub Label3_Click()
End Sub
Private Sub UserForm_Initialize()
Set myrng = ThisWorkbook.Sheets(1).Range("L1:L12")
Set cbtarget = Me.ComboBox1
Set cbtarget1 = Me.ComboBox2
cbtarget.List = myrng.Cells.Value
cbtarget1.Value = year(Date)
cbtarget.Value = Format(Date, "mmm")

Label3.ForeColor = 255
End Sub

Generate Next Id For AphaNumericValue

Excel formula     ="P"&TEXT(MAX(--SUBSTITUTE(A2:A33,"P","")+1),"00000") Press Control+Shift+Enter Data

P00001

P00003

P00004

P00005

P00006

P00007

P00008

P00010

P00011

P00013

P00014

P00015

P00016

P00018

P00019

P00020

P00021

P00022

P00023

P00024

P00034

P00036

P00037

P00039

P00040

P00041

P00042

P00043

P00044

P00047

P00048

P00049

Calendar In VBA without ActiveX Control

Dim selecteddate As Date

Dim cbtarget As msforms.ComboBox, cbtarget1 As msforms.ComboBox

Dim myrng As Range

Private Sub ComboBox1_Change()

Dim dayscount

Dim tempvar

Dim dayName

Dim currentdate

Dim i As Integer, j As Integer

Dim cmdbutton1 As msforms.CommandButton

dayName = Format(DateSerial(year(Date), ComboBox1.ListIndex + 1, 1), "ddd")

Dim counter As Integer, daycounter As Integer

Dim cmdbutton() As Variant

dayscount = Day(DateSerial(year(Date), ComboBox1.ListIndex + 2, 0))

cmdbutton = Array("CommandButton1", "CommandButton2", "CommandButton3", "CommandButton4", "CommandButton5", "CommandButton6", "CommandButton7", "CommandButton8", "CommandButton9", "CommandButton10", "CommandButton11", "CommandButton12", "CommandButton13", "CommandButton14", "CommandButton15", "CommandButton16", "CommandButton17", "CommandButton18", "CommandButton19", "CommandButton20", "CommandButton21", "CommandButton22", "CommandButton23", "CommandButton24", "CommandButton25", "CommandButton26", "CommandButton27", "CommandButton28", "CommandButton29", "CommandButton30", "CommandButton31", "CommandButton32", "CommandButton33", "CommandButton34", "CommandButton35", "CommandButton36", "CommandButton37", "CommandButton38")

currentdate = Format(Date, "dd")

If ComboBox1.Value <> vbNullString Then

    If dayName = "Sun" Then

        counter = 0

        daycounter = 1

    ElseIf dayName = "Mon" Then

        Calendar.Controls(cmdbutton(0)).Visible = False

        counter = 1

        daycounter = 1

    ElseIf dayName = "Tue" Then

        Calendar.Controls(cmdbutton(0)).Visible = False

        Calendar.Controls(cmdbutton(1)).Visible = False

        counter = 2

        daycounter = 1

    ElseIf dayName = "Wed" Then

        Calendar.Controls(cmdbutton(0)).Visible = False

        Calendar.Controls(cmdbutton(1)).Visible = False

        Calendar.Controls(cmdbutton(2)).Visible = False

        counter = 3

        daycounter = 1

    ElseIf dayName = "Thu" Then

        Calendar.Controls(cmdbutton(0)).Visible = False

        Calendar.Controls(cmdbutton(1)).Visible = False

        Calendar.Controls(cmdbutton(2)).Visible = False

        Calendar.Controls(cmdbutton(3)).Visible = False

        counter = 4

        daycounter = 1

    ElseIf dayName = "Fri" Then

        Calendar.Controls(cmdbutton(0)).Visible = False

        Calendar.Controls(cmdbutton(1)).Visible = False

        Calendar.Controls(cmdbutton(2)).Visible = False

        Calendar.Controls(cmdbutton(3)).Visible = False

        Calendar.Controls(cmdbutton(4)).Visible = False

        counter = 5

        daycounter = 1

    ElseIf dayName = "Sat" Then

        Calendar.Controls(cmdbutton(0)).Visible = False

        Calendar.Controls(cmdbutton(1)).Visible = False

        Calendar.Controls(cmdbutton(2)).Visible = False

        Calendar.Controls(cmdbutton(3)).Visible = False

        Calendar.Controls(cmdbutton(4)).Visible = False

        Calendar.Controls(cmdbutton(5)).Visible = False

        counter = 6

        daycounter = 1

        

    End If

    For i = counter To (counter + dayscount) - 1

        

        Calendar.Controls(cmdbutton(i)).Visible = True

        Calendar.Controls(cmdbutton(i)).BackColor = RGB(135, 206, 250)

        Calendar.Controls(cmdbutton(i)).ForeColor = RGB(102, 0, 51)

        Calendar.Controls(cmdbutton(i)).FontBold = True

        Calendar.Controls(cmdbutton(i)).Caption = daycounter

        If (daycounter = currentdate) And ((ComboBox1.ListIndex + 1) = Month(Date)) Then

            

            Calendar.Controls(cmdbutton(i)).BackColor = RGB(255, 255, 204)

            

        End If

        daycounter = daycounter + 1

    Next i

    Calendar.Controls(cmdbutton(0)).ForeColor = 255

    Calendar.Controls(cmdbutton(7)).ForeColor = 255

    Calendar.Controls(cmdbutton(14)).ForeColor = 255

    Calendar.Controls(cmdbutton(21)).ForeColor = 255

    Calendar.Controls(cmdbutton(28)).ForeColor = 255

    Calendar.Controls(cmdbutton(35)).ForeColor = 255

    For j = (counter + dayscount) To 37

        Calendar.Controls(cmdbutton(j)).Visible = False

    Next j

End If

End Sub

Private Sub CommandButton1_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton1.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton2_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton2.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton3_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton3.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton4_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton4.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton5_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton5.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton6_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton6.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton7_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton7.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton8_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton8.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton9_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton9.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton10_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton10.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton11_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton11.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton12_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton12.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton13_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton13.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton14_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton14.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton15_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton15.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton16_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton16.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton17_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton17.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton18_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton18.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton19_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton19.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton20_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton20.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton21_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton21.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton22_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton22.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton23_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton23.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton24_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton24.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton25_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton25.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton26_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton26.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton27_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton27.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton28_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton28.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton29_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton29.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton30_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton30.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton31_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton31.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton32_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton32.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton33_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton33.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton34_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton34.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton35_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton35.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton36_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton36.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton37_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton377.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub CommandButton38_Click()

Set cbtarget1 = Calendar.ComboBox2

Set cbtarget = Calendar.ComboBox1

selecteddate = DateSerial(cbtarget1.Value, cbtarget.ListIndex + 1, CommandButton38.Caption)

PlayerMaster.TextBox6 = Format(selecteddate, "dd-mmm-yyyy")

Unload Calendar

End Sub

Private Sub Label3_Click()

End Sub

Private Sub UserForm_Initialize()

Set myrng = ThisWorkbook.Sheets(1).Range("L1:L12")

Calendar.BackColor = RGB(188, 143, 143)

Set cbtarget = Me.ComboBox1

Set cbtarget1 = Me.ComboBox2

cbtarget.List = myrng.Cells.Value

cbtarget1.Value = year(Date)

cbtarget.Value = Format(Date, "mmm")

Label3.ForeColor = 255

End Sub

Example of a Nested Select Case using VBA

Sub CheckCell()
Dim Msg As String
Select Case IsEmpty(ActiveCell)
Case True
Msg = "is blank."
Case Else
Select Case ActiveCell.hasFormula
Case True
Msg = "has a formula"
Case False
Select Case IsNumeric(ActiveCell)
Case True
Msg = "has a number"
Case Else
Msg = "has text"
End Select
End Select
End Select
MsgBox "Cell " & ActiveCell.Address & " " & Msg
End Sub

Why to Use Dictionary Object in VBA

VBA has two types of storing collection of datas:

1.Array

2.VBA collection

When you want to compare large data set(A key and Value),Dictionary object is much quicker. Take a reference of Microsoft Scripting Runtime as mentioned below

Advantages of dictionary over Collection are:

1. You can use any value for keys, including numbers. Only requirement is that the key value can be contained in a variant.

2. The Dictionary object's Item member is a read-write property, not a method.

3. The Dictionary object has an Exists method to allow you to check if a key has already been used.

Sub test()

' Declare the dictionaries.

Dim Dict1 As Dictionary

Dim Dict2 As Dictionary

' Create a variant to hold the object.

Dim vContainer1

Dim vContainer2

' Create the dictionary instances.

Set Dict1 = New Dictionary

Set Dict2 = New Dictionary

With Dict1

  'set compare mode

  .CompareMode = BinaryCompare

  ' Add items to the dictionary.

  .Add 1, "Item 1"

  .Add 2, "Item 2"

  .Add 3, "Item 3"

End With

With Dict2

  'set compare mode

  .CompareMode = BinaryCompare

  ' Add items to the dictionary.

  .Add 1, "Item 1a"

  .Add 2, "Item 2"

  .Add 3, "Item 4"

End With

' Compare the two dictionaries.

For Each vContainer1 In Dict1

  If Not Dict2.Exists(vContainer1) Then

    MsgBox vContainer1 & " is in Dict1 but not in Dict2"

  Else ' Item exists so lets check the size.

    If Dict2.Item(vContainer1) <> Dict1.Item(vContainer1) Then

    MsgBox "Key item " & vContainer1 & " is different"

    End If

  End If

Next

End Sub

ANSI vs. Unicode and the Alias Clause

ANSI

ANSI is the most popular character standard used by personal computers. Because the ANSI standard uses only a single byte to represent each character, it is limited to a maximum of 256 character and punctuation codes. Although this is adequate for English, it doesn't fully support many other languages.

Unicode

Unicode is a character-encoding scheme that uses 2 bytes for every character. The International Standards Organization (ISO) defines a number in the range of 0 to 65,535 (216 – 1) for just about every character and symbol in every language (plus some empty spaces for future growth). On all 32-bit versions of Windows, Unicode is used by the Component Object Model (COM), the basis for OLE and ActiveX technologies. Unicode is fully supported by Windows NT. Although both Unicode and DBCS have double-byte characters, the encoding schemes are completely different.

If you are using Windows XP or later, then you should use Unicode encoding instead of ANSI. ANSI is a legacy encoding and is provided for backward compatibility with older applications. You should always use Unicode encoding if the application supports it.

All Windows API functions that have textual parameters come in two flavors: Those thatoperate on ANSI strings have an A suffix, whereas those that operate on Unicode strings have a W suffix. For example,although the documentation and searches on MSDN talk about FindWindow, the Windows DLLs do not actually contain a function of that name—they contain two functions called FindWindowA and FindWindowW. We use the Alias statement to provide the actual name (case sensitive) for the function contained in the DLL.

Synchronous V/S Asynchronous XMLHTTPRequest

Synchronous vs. Asynchronous. In the context of the XmlHttpRequest object, when you use Synchronousrequests, the request is carried out in line with the process that called it, and everything waits on it to complete. Asynchronous requests, however, once submitted, don't wait on a response. A listener is set up to listen for the response, but the execution of the code doesn't stop and wait for it. 

Get URL/Win Explorer from Web Browser

Sub GetOpenWindowsList()

    Dim SWs As New SHDocVw.ShellWindows
    Dim IE As SHDocVw.InternetExplorer
 
    For Each IE In SWs
 
     'MsgBox IE.LocationName
'below mentioned line retrieves URL/Explorer from Internet Explorer
     MsgBox IE.LocationURL
   
   
   
    Next IE
 
 
End Sub

MSXML2.XMLHTTP Application in VBA

It's an utlity to retrieve info from Amazon(US/UK) based on MSXML Services from Microsoft.
Benefits of this model:

1.Comparatively faster
2.Stand Alone Application
3.No Limit on no. of ISBN
4.Doen't get interrupted when a wrong ISBN encountered
5.Start/Stop at your will.

You can view this application on following url:



https://docs.google.com/open?id=0B23eJ2xd9ODyTjJNc1NaLVlQemM

Web Application using MSXML2 for VBA

As I have promised to begin with MSXML blog, I giving you a snippet of VBA code to retrieve data amazon.com for a particular ISBN

Option Explicit
Sub getDatafromAmazon()
Dim baseurl As String
Dim oXML As MSXML2.XMLHTTP
Dim mainurl As String
Dim tag1 As String
Dim tag2 As String
Dim shtml
Dim htmlbody
Dim authorname As String
Dim i As Long
Dim j As Long
On Error GoTo Errorhandler
baseurl = "http://www.amazon.com/s/ref=nb_sb_noss?url=search-alias%3Daps&field-keywords="
tag1 = "<a href=""/Adam-Greenspan/e/B001IQW882/"
tag2 = "</a>"
Set oXML = New MSXML2.XMLHTTP
mainurl = baseurl & Range("A3").Value
Range("B3").Value = mainurl
    oXML.Open "GET", mainurl, True
    oXML.send
    Do
        DoEvents
    Loop Until oXML.readyState = 4
    shtml = oXML.responseText
  
   
    i = InStr(shtml, tag1)
    i = i + 83
   
    j = InStr(i, shtml, tag2)
    authorname = Mid(shtml, i, j - i)
    Range("C3").Value = authorname
    Exit Sub
   
Errorhandler:
    MsgBox "Error" & Err.Description
  
End Sub

What is XMLHttpRequest

We are coming up with some excited blogs on MSXML2.XMLHTTP Application. Before that you need to get some brief idea about XMLHttpRequest to understand code

                            XMLHttpRequest (XHR) is an API available in web browser scripting languages such as JavaScript.

It is used to send HTTP or HTTPS requests directly to a web server and load the server response data directlyback into the script. The data might be received from the server as  XML[4], HTML, or as plain text.Data from the response can be used directly to alter the DOM of the currently active document in the browser window without loading a new web page document. The response data can also be evaluated/manipulated by client-side scripting.

               The following sections demonstrate how a request using the XMLHttpRequest object functions within a conforming user agent based on the W3C Working Draft.The HTTP and HTTPS requests of the XMLHttpRequest object must be initialized through the open method.This method must be invoked prior to the actual sending of a request to validate and resolve the request method, URL, and URI user information to be used for the request.This method does not assure that the URL exists or the user information is correct.This method can accept up to five parameters, but requires only two, to initialize a request.

                              open( Method, URL, Asynchronous, UserName, Password ) 

               The first parameter of the method is a text string indicating the HTTP request method to use.The request methods that must be supported by a conforming user agent, defined by the W3C draft for the XMLHttpRequest object, are currently listed as the following.

                                                            GET (Supported by Internet Explorer 7 (and later), Mozilla 1+)

                                                            POST (Supported by Internet Explorer 7 (and later), Mozilla 1 (and later))

                                                HEAD (Supported by Internet Explorer 7 (and later))

                                                PUT

                                                DELETE

                                                OPTIONS (Supported by Internet Explorer 7 (and later))

               However, request methods are not limited to the ones listed above. The W3C draft states that a browser may support additional request methods at their own discretion. The second parameter of the method is another text string, this one indicating the URL of the HTTP request. The W3C recommends that browsers should raise an error and not allow the request of a URL with either a different port or ihost URI component from the current document.The third parameter, a boolean value indicating whether or not the request will be asynchronous, is not a required parameter by the W3C draft.

The default value of this parameter should be assumed to be true by a W3C conforming user agent if it is not provided. An asynchronous request ("true") will not wait on a server response before continuing on with the execution of the current script. It will instead invoke the onreadystatechange event listener of the XMLHttpRequest object throughout the various stages of the request. A synchronous request ("false") however will block execution of the current script until the request has been completed, thus not invoking the onreadystatechange event listener.

               The fourth and fifth parameters are the username and password, respectively. These parameters, or just the username, may be provided for authentication and authorization if required by the server for this request.

The send method

To send an HTTP request, the send method of the XMLHttpRequest must be invoked. This method accepts a single parameter containing the content to be sent with the request.

                                                      Send( Data )

             This parameter may be omitted if no content needs to be sent.

After a successful and completed call to the send method of the XMLHttpRequest,responseText will contain the response of the server in plain text by a conforming user agent, regardless of whether or not it was understood as XML.

Integrate Google Chrome With VBA

Instead of opening url with Internet Explorer u can use Google Chrome to navigate l using VBA.







Sub test()

  Dim chromePath As String

  chromePath = "C:\Users\Username\AppData\Local\Google\Chrome\Application\chrome.exe"

  Shell (chromePath & " -url http:matrix.in")

End Sub

0& in VBA Parlance

0 is a number.
Unfortunately, there are several number data types - Integer, Long, Single, Double, Currency, etc.

With no data type identifier after the 0, it is implied to be of the native Integer (the default). Therefore this 0 is expressed as an Integer, which makes it a 16-bit 0.

However, many API functions use Longs and you should not pass an Integer 0 into a Long (that's like putting M&Ms in a cookie jar!)
You should pass a Long 0, which will be a 32-bit 0, as the function requests a 32-bit long long number.
So, therefore you add a &:

An & after a number or a variable means that it is a Long (which is 32-bits).
0& is a 32-bit 0.
x& is a 32-bit variable (this is obsolete because it is better to Dim x As Long rather than Dim x& - the x As Long is clearly a long, and the x& is not obvious)

&H is hexadecimal notation - used when functions are trying to show or set specific bit values - this is used when it would not be efficient to express a value in base 10 notation.

For example: 0011 0000 1000 1101 (16-bit integer) these are often used to set bit flags.
You can use a simple lookup table to convert this to hexadecimal
&H308D (if you're like me, you've memorized it - it's only 16 numbers)

It takes much more time to convert this to decimal.
:)

And you should be able to answer the last question on your own now. :)

P.S. ByVal 0& is passed to an API parameter declared as Any to specify that you don't want to put anything there.

How to Build an Array Formula in Excel 

An array formula is a special formula that operates on a range of values in Excel . When you build an array formula in a worksheet, you press Ctrl+Shift+Enter to insert an array formula in the array range.

To get an idea of how you build and use array formulas in a worksheet, consider the example below. This worksheet is designed to compute the biweekly wages for each employee. It will do this by multiplying each employee's hourly rate by the number of hours worked in each pay period. Use an array formula instead of creating the following formula in cell R10 and copying it down to cells R11 through R13:

Building an array formula to calculate hourly wages for the first pay period.

=A4*R4

You can create the following array formula in the array range:

={A4:A7*R4:R7}

This array formula multiplies each of the hourly rates in the 4 x 1 array in the range A4:A7 with each of the hours worked in the 4 x 1 array in the range R4:R7. This same formula is entered into all cells of the array range (R10:R13) as soon as you complete the formula in the active cell R10. To see how this is done, follow along with the steps required to build this array formula:

1.      Make cell R10 the active cell, and then select the array range R10:R13 and type = (equal sign) to start the array formula.

You always start an array formula by selecting the cell or cell range where the results are to appear. Note that array formulas, like standard formulas, begin with the equal sign.

2.      Select the range A4:A7 that contains the hourly rate for each employee, type * (an asterisk for multiplication), and then select the range R4:R7 that contains the total number of hours worked during the first pay period.

3.      Press Ctrl+Shift+Enter to insert an array formula in the array range.

Excel inserts braces around the formula and copies the array formula {=A4:A7*R4:R7} into each of the cells in the array range R10:R13.

When entering an array formula, you must remember to press Ctrl+Shift+Enter instead of just the Enter key because this key combination tells Excel that you're building an array formula, so that the program encloses the formula in braces and copies it to every cell in the array range.

The figure below shows you the February wage table after completing all the array formulas in three ranges: R10:R13, AI10:AI13, and AJ10:AJ13. In the second cell range, AI10:AI13, the following array formula was entered to calculate the hourly wages for the second pay period in February:

Hourly wage spreadsheet after entering all three array formulas.

{=A4:A7*AI4:AI7}

The following array formula was entered in the third cell range, AJ10:AJ13, to calculate the total wages paid to each employee in February 2010:

{=R10:R13+AI10:AI13}

When you enter an array formula, the formula should produce an array with the same dimensions as the array range that you selected. If the resulting array returned by the formula is smaller than the array range, Excel expands the resulting array to fill the range. If the resulting array is larger than the array range, Excel doesn't display all the results. When expanding the results in an array range, Excel considers the dimensions of all the arrays used in the arguments of the operation. Each argument must have the same number of rows as the array with the most rows and the same number of columns as the array with the most columns.