Compare Strings in Cases Insensitive Cases

When you compare 2 Strings for Case insensitive cases use
Option Compare Text at the top of the sub procedure.

One small example

Option Compare Text

Sub check()
If ("A" = "a") Then

MsgBox "Case insensitive"
End If
End Sub

Listing of File Names with Different Folders

Public pathname As String
Public fileformat As String
Dim strfile As String
Dim rowcount As Long
'Parameters are passed from Forms
Sub ListFiles()

rowcount = 2
strfile = Dir(pathname & "\" & fileformat, vbNormal)
    If Len(strfile) = 0 Then
            MsgBox "No file Exists", vbOKOnly

    End If
    Application.ScreenUpdating = False
    Do While Len(strfile) > 0
 
        Cells(rowcount, 3) = strfile
        Cells(rowcount, 4) = FileDateTime(pathname & "\" & strfile)
        rowcount = rowcount + 1
        'get nextfile from Folder
        strfile = Dir
    Loop
    Columns.AutoFit
End Sub

Convert Excel File To PDF

Sub Excel2PDFConverter()
With Application.FileDialog(msoFileDialogFolderPicker)
              .Show
              Path = .SelectedItems(1)
End With
              ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & "\" & "exceltopdf"
End Sub

Creating a Folder Inside SubFolder

'take reference of Microsoft Scripting Runtime
Sub createFolderinsideSubFolder()
Dim fso As Scripting.FileSystemObject
Dim parentfolder As Object
Dim subfolder As Object
Dim myfolder As String

Set fso = CreateObject("Scripting.FileSystemObject")
myfolder = "D:\Somu\"
Set parentfolder = fso.GetFolder(myfolder)
For Each subfolder In parentfolder.SubFolders
'searching all SubFolders inside D:\Somu\
myfolder = subfolder.Path & "\2013"
    If Not fso.FolderExists(myfolder) Then
        MkDir (myfolder)
    End If
Next

End Sub

ConnectionString Briefing(Used in SQL Server connectivity)

In computing, a Connectionstring is a string that specifies information about a data source and the means of connecting to it. It is passed in code to an underlying driver or provider in order to initiate the connection. Whilst commonly used for a database connection, the data source could also be a spreadsheetor text file.  ConnectionString property can be set only when the connection is closed. The connection string is parsed immediately after being set. If errors in syntax are found when parsing, a runtime exception, such asArgumentException, is generated. Other errors can be found only when an attempt is made to open the connection.
The connection string may include attributes such as the name of the driver, server and database, as well as security information such as user name and password.
For Reference:
www.connectionstrings.com

For example:
 con_string = "DRIVER={MySQL ODBC 3.51 Driver};user=internalros;password=internalros;database=TBS; server=206.71.169.000;option=18475"
ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=NEW\SQLEXPRESS;"

ODBC-it is designed for connecting to relational databases.
However, OLE DB can access relational databases as well as nonrelational databases.
List of Parameters for Microsoft OLE-DB Provider:

https://docs.google.com/file/d/0B23eJ2xd9ODyZDg2ZFdCZUM1MWM/edit?usp=sharing

Suffixes after variable name in VBA

Dim A!, B@, C#, D$, E%, F&

Debug.Print "A! - " & TypeName(A)
Debug.Print "B@ - " & TypeName(B)
Debug.Print "C# - " & TypeName(C)
Debug.Print "D$ - " & TypeName(D)
Debug.Print "E% - " & TypeName(E)
Debug.Print "F& - " & TypeName(F)

A! - Single
B@ - Currency
C# - Double
D$ - String
E% - Integer
F& - Long

For Reference:

http://support.microsoft.com/kb/110264

Export Userform to Another Workbook

Dim vbcomponent As Variant
Sub exportForm()
On Error Resume Next
Dim wbSource As Workbook, wbDestination As Workbook
Set wbSource = Workbooks.Open("C:\abc\DIR\Desktop\Book1")
Set wbDestination = ThisWorkbook
For Each vbcomponent In wbSource.VBProject.VBComponents
        If (vbcomponent.Name = "displayForm") Then
     
                wbSource.VBProject.VBComponents(vbcomponent.Name).Export "C:\temp\displayForm.frm"
                wbDestination.VBProject.VBComponents.Import "C:\temp\displayForm.frm"
        End If
     
     
Next
        Kill "C:\temp\displayForm.frm"
        Kill "C:\temp\displayForm.frx"
     
wbSource.Close
End Sub

Sending mails without taking Outlook Reference

If your mailId is configured to Outlook Express; following code will send mails of each excel sheet

Sub Mail_every_Worksheet()

    Dim strDate As String
    Dim sh As Worksheet
    Application.ScreenUpdating = False
    For Each sh In ThisWorkbook.Worksheets
        If sh.Range("a1").Value Like "*@*" Then
            sh.Copy
            strDate = Format(Date, "dd-mm-yy") & " " & Format(Time, "h-mm-ss")
            ActiveWorkbook.SaveAs "Part of " & ThisWorkbook.Name _
                                & " " & strDate & ".xls"
            ActiveWorkbook.SendMail ActiveSheet.Range("a1").Value, _
                                    ActiveSheet.Range("b1").Value
            ActiveWorkbook.ChangeFileAccess xlReadOnly
         
            ActiveWorkbook.Close False
        End If
    Next sh
    Application.ScreenUpdating = True

End Sub

Calculate Age Using Nested Select Case

Sub calculateAge()
Dim tempdate As Date, sysdate As Date, mydob As Date
Dim y As Integer, m As Integer, d As Integer
mydob = CDate(Application.InputBox("Select your DoB", "DoB", Default:=Format(Date, "mm/dd/yyyy"), Type:=2))
sysdate = Format(Date, "mm/dd/yyyy")
tempdate = DateSerial(Year(Date), Month(mydob), Day(mydob))

Select Case (tempdate > sysdate)

    Case True
                y = Year(sysdate) - Year(mydob) - 1
             
                Select Case Day(mydob) > Day(Date)
                    Case True
                           
                        m = -Month(mydob) - 12 * (tempdate > sysdate) + Month(Date) - 1
                        d = Day(DateSerial(Year(Date), Month(Date), 0)) - Day(mydob) + Day(Date)
                    Case False
                        m = -Month(mydob) - 12 * (tempdate > sysdate) + Month(Date)
                        d = Day(Date) - Day(mydob)
                End Select
    Case Else
                y = Year(sysdate) - Year(mydob)
                Select Case Day(mydob) > Day(Date)
                    Case True
                           
                        m = Month(Date) - Month(mydob) - 1
                        d = Day(DateSerial(Year(Date), Month(Date), 0)) - Day(mydob) + Day(Date)
                    Case False
                        m = Month(Date) - Month(mydob)
                        d = Day(Date) - Day(mydob)
                End Select
       
End Select
MsgBox "Your Age is" & y & " years " & m & "months" & d & "days"
End Sub

Remote Connection for SQL Server

Dim rec As New ADODB.Recordset
Dim con As New ADODB.Connection
Dim col As Long, row As Long

con_string = "DRIVER={MySQL ODBC 3.51 Driver};user=internalros;password=internalros;database=TBS;server=206.71.169.000;option=18475"
con.ConnectionString = con_string
con.Open

Brief Introduction to Sensitivity Analysis

A technique used to determine how different values of an independent variable will impact a particular dependent variable under a given set of assumptions. This technique is used within specific boundaries that will depend on one or more input variables, such as the effect that changes in interest rates will have on a bond's price.

One of the finest features in Microsoft Excel is sensitivity analysis using either a table (Excel 2003) or 'What-if' in Excel 2007. Suppose you want to start a cybercafe or a restaurant in a new mall. You have done a study on the footfall and the kind of people who visit the mall. You have also found out about the business atmosphere, security and rent or the outright purchase price. You also know the rates in the market that other businesses are charging, let's say, for surfing the net per hour. You then estimate your capital costs like doing up the cybercafe and the price of the computers. You also use the Excel spreadsheet to estimate and calculate the number of people you'll need to run the show and the amount of salaries you'll have to pay. You have also estimated other variable costs like electricity and phone.

From the above data in the Excel worksheet you can calculate your total monthly or yearly costs. Now based on a certain price that you will charge the customers, number of computers and working hours you can calculate your revenue per month or per year. From the data of revenue and income you can easily calculate the profit. Till now everything was easy to implement in Excel.

Now you decide to find out how your profit can vary if you vary the charge per hour or the number of people who will visit your cybercafe or establishment. Of course, you cannot charge what you want but you can get a good estimate by observing what others are charging and what quality of service and environment they are providing.

Arranging all your data properly, click on 'Data' in the ribbon in Microsoft Office 2007 or 'Data' in the menu bar in Excel 2003. In Excel 2007 select 'What-if' analysis and finally 'Data Table...'. In the popup window in the 'Row input cell' type the data that you have input horizontally next to the profit and in the 'column input cell' write down the price and vary it it by 1% 0r 2% so that that Excel can perform an analysis for, say., $0.5 per hour charge for a cybercafe to $1.5 per hour. The horizontal values can be the number of people per hour or month or year that will visit the shop and keep on varying the values by a certain estimated percentage. Click 'OK' and you can see how your profit varies with the number of customers and the price you charge. This is also known as a two variable table because you calculated the changes in your profit based on two parameters - price and number of customers.

Contribution by Dr. Dinesh K Takyar



<!--[if !supportLineBreakNewLine]-->
<!--[endif]-->

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