Copy Specific Cells from files under Folder/SubFolder

Option Explicit
Dim objFso As Object, objFolder As Object, pathname As String, eachfile As Variant, objFolder1 As Object, eachfolder As Object
Dim tempcounter As Long, wb As Workbook, i As Integer, j As Integer


Sub copyFromSpecificfolderandsubfolder()
On Error Resume Next
pathname = "D:\Somu\ExcelClasses Notes"
Set objFso = New Scripting.FileSystemObject
Set objFolder = objFso.GetFolder(pathname)
Set objFolder1 = objFolder.SubFolders
Set wb = ThisWorkbook
tempcounter = 1
Call readfile(objFolder, tempcounter)

For Each eachfolder In objFolder1

Call readfile(eachfolder, tempcounter)

Next
tempcounter = 0
End Sub

Sub readfile(myFolder As Scripting.Folder, tempcounter)

For Each eachfile In myFolder.Files
    If checkExtension(eachfile) = True Then
        tempcounter = tempcounter + 1
 
        Workbooks.Open eachfile
        ActiveWorkbook.Sheets(1).Range("A2:C2").Copy wb.Sheets(1).Range("A" & tempcounter)
        ActiveWorkbook.Close
    End If
Next
End Sub

Public Function checkExtension(eachfile) As Boolean
i = InStr(1, eachfile, ".")
j = Len(eachfile)

Select Case Mid(eachfile, i + 1, (j - i))
    Case "xls"
        checkExtension = True
    Case "Xlsm"
        checkExtension = True
    Case "xlsx"
        checkExtension = True
    Case "xlsb"
        checkExtension = True
    Case Else
        checkExtension = False
End Select
End Function

Split data of a Single workbook into different workbook

Option Explicit
Dim wb As Workbook, ws As Worksheet, myrng As Range, i As Integer, x As Integer
Dim fso As Object, fldr As Object, fl As Object, item_, itemcol As Collection
Dim path As String, rawdata
Sub migrateData()
Application.ScreenUpdating = False
On Error Resume Next
path = "D:\Somu\Task12092013-3"
 Set fso = New Scripting.FileSystemObject
 Set fldr = fso.GetFolder(path)
 Set itemcol = New Collection
 'For Each fl In fldr.Files
 Set wb = ThisWorkbook
 Set ws = wb.ActiveSheet
 MsgBox ws.UsedRange.Address
 Set myrng = ws.Range(ws.UsedRange.Address)
 item_ = ws.Range("d2:" & ws.Range("d2").End(xlDown).Address).Value
  For i = 1 To UBound(item_, 1)
        itemcol.Add item_(i, 1), item_(i, 1)

  Next
  For x = 1 To itemcol.Count
    myrng.AutoFilter , field:=4, Criteria1:=itemcol(x)
    rawdata = ws.UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible)
        For Each fl In fldr.Files
               If Left(fl.Name, InStr(1, fl.Name, ".") - 1) = itemcol(x) Then
                Workbooks.Open path & "\" & fl.Name
                ActiveWorkbook.ActiveSheet.Range("a" & ActiveSheet.UsedRange.Rows.Count).Resize(UBound(rawdata, 1), UBound(rawdata, 2)) = rawdata
                ActiveWorkbook.Save
                ActiveWorkbook.Close
               Else
                    Workbooks.Add
                    ActiveWorkbook.ActiveSheet.Range("a1") = "City"
                    ActiveWorkbook.ActiveSheet.Range("b1") = "Map Code"
                    ActiveWorkbook.ActiveSheet.Range("c1") = "Model"
                    ActiveWorkbook.ActiveSheet.Range("d1") = "Country"
                    ActiveWorkbook.ActiveSheet.Range("e1") = "Batch Refference"
                    ActiveWorkbook.ActiveSheet.Range("f1") = "Source"
                    ActiveWorkbook.ActiveSheet.Range("a2").Resize(UBound(rawdata, 1), UBound(rawdata, 2)) = rawdata
                    ActiveWorkbook.SaveAs path & "\" & itemcol(x), FileFormat:=56
                    ActiveWorkbook.Close
                    Exit For
               End If
 
        Next
  Next
End Sub


For file:
https://drive.google.com/file/d/0B23eJ2xd9ODydUtzYkwtaU5ZMDA/edit?usp=sharing

A Simple VBA Code for Creating PivotTable

Dim pttbl As PivotTable
Sub createPivot()
On Error Resume Next
For Each pttbl In ThisWorkbook.Sheets(2).PivotTables
        ThisWorkbook.Sheets(2).Range(pttbl.TableRange2.Address).Delete
Next

   ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ThisWorkbook.Sheets(1).Range("A1").CurrentRegion.Address). _
   CreatePivotTable TableDestination:=ThisWorkbook.Sheets(2).Range("C5"), TableName:="PivotTable1"
    With ThisWorkbook.Sheets(2).PivotTables("PivotTable1")
        .PivotFields("Processed by").Orientation = xlRowField
        .PivotFields("status").Orientation = xlDataField
        .PivotFields("Transaction type").Orientation = xlPageField
     
    End With

End Sub




https://drive.google.com/file/d/0B23eJ2xd9ODyRFgxWGhhVldGUzQ/edit?usp=sharing

Sending Attachment through OutLook

Sub sendMail
Dim outApp As New Outlook.Application, outMail As Variant,mailId as String
Set outMail = outApp.CreateItem(olMailItem)
        With outMail
            .To = mailId
            .Subject = Mid(wb.Name, 1, InStr(wb.Name, ".") - 1)
            .Body = "Hi," & vbNewLine & "     Please find the attachment" & vbNewLine & "Regards," &        vbNewLine & "Soumyendu"
            .Attachments.Add (wb.Path & "\" & wb.Name)
         
            .Display
        End With
    wb.Close


End sub

Count Colored Cells through VBA

Option Explicit
Dim myrng As Range, rowcount As Integer, criteria As Range, cell As Variant, criteriaIndex As Long
Dim tempcounter As Long
Function countColoredcells(ByVal criteria As Range) As Long
rowcount = Sheets(3).Range("A1").End(xlDown).Row
Set myrng = ThisWorkbook.Sheets(3).Range("A1:A" & rowcount)
tempcounter = 0
    criteriaIndex = criteria.Interior.ColorIndex
    For Each cell In myrng
        If (cell.Interior.ColorIndex = criteriaIndex) Then
          tempcounter = tempcounter + 1
        End If
    Next
    countColoredcells = tempcounter
End Function

VBA Code for Autofilter with dynamic criteria

Option Explicit
Dim myrng As Range
Dim rowcount As Byte
Dim cell As Variant
Dim MyArray() As Variant, temp As Integer



Sub filterwithdynamicArray()
rowcount = Sheets(1).Range("H2").End(xlDown).Row
temp = 0

Set myrng = Sheets(1).Range("H2:H" & rowcount)
    For Each cell In myrng
        temp = temp + 1
        ReDim Preserve MyArray(1 To temp)
        MyArray(temp) = cell
    Next cell
    Sheets(1).Range("A1").AutoFilter field:=2, Criteria1:=MyArray, Operator:=xlFilterValues
End Sub

File to download:

https://drive.google.com/file/d/0B23eJ2xd9ODyWVFsYVhYZkxJODQ/edit?usp=sharing

CustomPercentile Function using VBA

Option Explicit
Option Base 1
Dim notemptyrng As Boolean
Function customPercentile(myrng As Range, myval As Double) As Variant
Dim Data() As Double
Dim i  As Long, j As Long, nearestRank As Long
    If isRangeEmpty(myrng) = False Then
            customPercentile = "Input Range is Empty"
            Exit Function
    End If
    If (myval < 0 Or myval > 100) Then
        customPercentile = "Percentile must be between 0& 100"
   
    End If
    ReDim Data(myrng.Rows.Count * myrng.Columns.Count)
    For i = 1 To myrng.Rows.Count
        For j = 1 To myrng.Columns.Count
                If IsNumeric(myrng.Cells(i, j)) = True Then
                    Data(i * j) = myrng.Cells(i, j)
                Else
                    customPercentile = "Error exists in" & myrng.Cells(i, j).Address
                    Exit Function
                End If
        Next
    Next
    Call myBubbleSort(Data)
    nearestRank = Round(myval / 100 * (myrng.Rows.Count * myrng.Columns.Count) + 0.5, 0)
   
    Select Case myval
        Case 0: customPercentile = Data(1)
        Case Else: customPercentile = Data(nearestRank)
    End Select
   
End Function

Function isRangeEmpty(myrng1 As Range) As Boolean
        notemptyrng = False
        If Application.WorksheetFunction.CountA(myrng1) > 0 Then
            notemptyrng = True
        End If
        isRangeEmpty = notemptyrng
End Function

Function myBubbleSort(myArray() As Double) As Variant
    Dim i As Long
    Dim j As Long
    Dim tempval As Variant
    For i = LBound(myArray) To UBound(myArray) - 1
        For j = i + 1 To UBound(myArray)
                If myArray(i) > myArray(j) Then
                tempval = myArray(j)
                myArray(j) = myArray(i)
                myArray(i) = tempval
                End If
        Next
    Next
End Function

Updating Master File Using VBA

VBA Code for updating Master file sheet by different name from other other workbooks with same sheet name under  folder( with same sheet name)




Option Explicit
Dim fso As FileSystemObject
Dim fl As file
Dim fldr As Folder, i As Integer
Dim wb As Workbook, rowcount As Integer, rowcount1 As Integer
Sub updateSheet()
On Error Resume Next
Application.ScreenUpdating = False
Set fso = New FileSystemObject
For i = 4 To ThisWorkbook.Sheets.Count
     
    If (fso.FolderExists(ThisWorkbook.Path & "\" & Sheets(i).Name) = True) Then
     
      Set fldr = fso.GetFolder(ThisWorkbook.Path & "\" & Sheets(i).Name & "\")
      rowcount = ThisWorkbook.Sheets(i).Range("a2").End(xlDown).Row
   
        For Each fl In fldr.Files
            Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & Sheets(i).Name & "\" & fl.Name)
            rowcount1 = wb.Sheets(1).Range("A2").End(xlDown).Row
         
            wb.Sheets(1).Range("A2:T" & rowcount1).Copy Destination:=ThisWorkbook.Sheets(i).Range("A" & rowcount)
            wb.Close
            Set wb = Nothing
        Next
    End If
Next
End Sub

Convert File Format in a Folder

VBA code for converting excel files to *.xls format in a folder

Option Explicit
Dim pathname As String, fso As New Scripting.FileSystemObject, folder, fl As Object
Sub renameFiles()

      With Application.FileDialog(msoFileDialogFolderPicker)
            .Title = "Select folder"
            If .Show = True Then
                pathname = .SelectedItems(1)

            Else
                MsgBox "Folder not selected"
            End If

      End With
      Set folder = fso.GetFolder(pathname)
   
      For Each fl In folder.Files
   
           If fso.GetExtensionName(pathname & "\" & fl.Name) Like "xl*" Then
                        fl.Name = "Somu.xls"
             
           End If
      Next
      Set folder = Nothing
      Set fso = Nothing
End Sub

https://drive.google.com/file/d/0B23eJ2xd9ODyc21jdXpkaUtLVUk/edit?usp=sharing

Update table via deleting records

Option Explicit
Dim clearedData As Range, cell As Range
Dim deletedataCounter As Byte
Sub updateDat()
    Set clearedData = Sheets(2).Range("A2:A" & Sheets(2).Range("A2").CurrentRegion.Rows.Count)
    [a1].Select
    deletedataCounter = 0
    Do
        ActiveCell.Offset(1, 0).Select
            For Each cell In clearedData
                If ActiveCell.Value = cell.Value Then
                  Range(ActiveCell.Address, ActiveCell.Offset(0, 2)).Select
                  Selection.Delete xlShiftUp
                  deletedataCounter = deletedataCounter + 1
                End If
            Next cell
    Loop Until ActiveCell.Value = ""
    MsgBox deletedataCounter & "Cheques Cleared" & [a1].CurrentRegion.Rows.Count & "Pending"
    Set clearedData = Nothing
End Sub

https://drive.google.com/file/d/0B23eJ2xd9ODydDRQd1BhcEJDVEk/edit?usp=sharing

Copy Range from Another Workbook through VBA

Option Explicit
Dim fso As FileSystemObject
Dim fl As File
Dim fldr As Folder
Public wb As Workbook, wbnew As Workbook
Dim fldrpath As String
Sub trackSheet()
Set wb = ThisWorkbook
On Error GoTo ErrorHandler
Set fso = New Scripting.FileSystemObject
Application.FileDialog(msoFileDialogFolderPicker).Title = "Choose Folder"
Application.FileDialog(msoFileDialogFolderPicker).Show
fldrpath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
Set fldr = fso.GetFolder(fldrpath)
For Each fl In fldr.Files
    wb.Sheets.Add after:=Sheets(Sheets.Count)
 
    wb.Sheets(Sheets.Count).Name = fl.Name
Call copySheet(fldrpath & fl.Name)


Next
Set wb = Nothing
Exit Sub
ErrorHandler:

MsgBox "Select Folder"

End Sub



Sub copySheet(ByVal pathname As String)
 i = InStr(pathname, ".")
 extn = Mid(pathname, i, Len(pathname) - (i - 1))
 If extn Like ".xl*" Then
        Set wbnew = Workbooks.Open(pathname)
        Application.DisplayAlerts = False
        wbnew.Sheets(1).UsedRange.Copy Destination:=wb.Sheets(wb.Sheets.Count).Range("A1")
        wb.Save
        wbnew.Close
 End If
End Sub

https://drive.google.com/file/d/0B23eJ2xd9ODycEVOclQ5TmJkYnM/edit?usp=sharing

Application.Caller Example in VBA

Option Explicit
Dim myrng As Range
Dim shp As Shape
Sub displayData()
Set shp = ThisWorkbook.Sheets(1).Shapes(Application.Caller)

        Select Case shp.TextFrame.Characters.Text
            Case "Display Data Hiding Zero"
                  shp.TextFrame.Characters.Text = "Display all Data"
                  For Each myrng In Range("D2:D23")
                     If myrng = 0 Then myrng.EntireRow.Hidden = True
                 
                  Next
            Case Else
                  shp.TextFrame.Characters.Text = "Display Data Hiding Zero"
                  Cells.EntireRow.Hidden = False
        End Select

End Sub

For details about Application.Caller plz refer:

http://msdn.microsoft.com/en-us/library/bb220902%28v=office.12%29.aspx

Example file link

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

Bubble Sort in VBA

Option Explicit
Dim myarray(), lookuprange As Range
Dim myrng As Range, mycell, i As Integer, k As Integer, l As Integer, tempvar

Sub sortarrangeData()
Application.ScreenUpdating = False
ReDim myarray(countUnique(Sheets(1).Range("A2:A31")))
Set myrng = ThisWorkbook.Sheets(1).Range("A2:A31")
For Each mycell In myrng
        If mycell <> mycell.Offset(1, 0) Then
            myarray(i) = mycell
            i = i + 1
         
        End If
Next
i = 0
'bubble sort
For k = 0 To countUnique(Sheets(1).Range("A2:A31")) - 1
    For l = k + 1 To countUnique(Sheets(1).Range("A2:A31"))
         If myarray(k) > myarray(l) Then
            tempvar = myarray(k)
            myarray(k) = myarray(l)
            myarray(l) = tempvar
         End If
       
       
    Next
 
Next
For l = 1 To countUnique(Sheets(1).Range("A2:A31"))
 
    Set lookuprange = ThisWorkbook.Sheets(1).Cells.Find(myarray(l), LookIn:=xlValues, lookat:=xlWhole)
    'MsgBox myarray(l) & "Addess" & lookuprange.Address
    Cells(l + 1, 5) = myarray(l)
    Cells(l + 1, 6) = lookuprange.Offset(0, 1)
    Cells(l + 1, 7) = lookuprange.Offset(1, 1)
    Cells(l + 1, 8) = lookuprange.Offset(2, 1)
Next
End Sub


Function countUnique(rng As Range) As Long
Dim coll As New Collection
Dim cell As Variant

On Error Resume Next
    For Each cell In rng
     
        coll.Add CStr(cell.Value), CStr((cell.Value))

    Next
    countUnique = coll.Count
 
    Set coll = Nothing
End Function

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

VBA Sample Code for Two Dimensional Array

Option Explicit
Dim daterng As Range, otherrng As Range, i As Integer, j As Integer, tempCounter As Integer, tempcounternew As Integer
Dim tempcounternew1 As Integer, tempcounternew2 As Integer, tempcounternew3 As Integer
Dim myArray()
Sub realignmentofreport()
Set daterng = ThisWorkbook.Sheets(1).Range("B10:B13")
Set otherrng = ThisWorkbook.Sheets(1).Range("B3:B6")
tempCounter = 0
tempcounternew = 0
tempcounternew1 = 0
tempcounternew2 = 0
tempcounternew3 = 0
On Error Resume Next
    ReDim myArray(1 To daterng.Rows.Count * 5, 1 To otherrng.Rows.Count)
    For i = 1 To UBound(myArray())
   
        tempCounter = tempCounter + 1
       
              
        Select Case (tempCounter < 6)
            Case True
               
                tempcounternew = tempcounternew + 2
                myArray(i, 1) = ThisWorkbook.Sheets(1).Range("B10")
                myArray(i, 2) = ThisWorkbook.Sheets(1).Range("B10").Offset(0, tempcounternew)
                myArray(i, 3) = Application.WorksheetFunction.VLookup(ThisWorkbook.Sheets(1).Range("B10").Offset(0, tempcounternew - 1), [lookuprng], 2, 0)
                myArray(i, 4) = Application.WorksheetFunction.VLookup(ThisWorkbook.Sheets(1).Range("B10").Offset(0, tempcounternew - 1), [lookuprng], 3, 0)
            Case Else
                Select Case (tempCounter < 11) And (tempCounter > 5)
                Case True
                    tempcounternew1 = tempcounternew1 + 2
                   
                    myArray(i, 1) = ThisWorkbook.Sheets(1).Range("B11")
                    myArray(i, 2) = ThisWorkbook.Sheets(1).Range("B11").Offset(0, tempcounternew1)
                    myArray(i, 3) = Application.WorksheetFunction.VLookup(ThisWorkbook.Sheets(1).Range("B11").Offset(0, tempcounternew1 - 1), [lookuprng], 2, 0)
                    myArray(i, 4) = Application.WorksheetFunction.VLookup(ThisWorkbook.Sheets(1).Range("B11").Offset(0, tempcounternew1 - 1), [lookuprng], 3, 0)
                 Case Else
                    Select Case (tempCounter < 16) And (tempCounter > 10)
                        Case True
                            tempcounternew2 = tempcounternew2 + 2
                           
                            myArray(i, 1) = ThisWorkbook.Sheets(1).Range("B12")
                            myArray(i, 2) = ThisWorkbook.Sheets(1).Range("B12").Offset(0, tempcounternew2)
                            myArray(i, 3) = Application.WorksheetFunction.VLookup(ThisWorkbook.Sheets(1).Range("B12").Offset(0, tempcounternew2 - 1), [lookuprng], 2, 0)
                            myArray(i, 4) = Application.WorksheetFunction.VLookup(ThisWorkbook.Sheets(1).Range("B12").Offset(0, tempcounternew2 - 1), [lookuprng], 3, 0)
                   
                        Case Else
                            Select Case (tempCounter < 21) And (tempCounter > 15)
                                Case True
                                    tempcounternew3 = tempcounternew3 + 2
                                   
                                    myArray(i, 1) = ThisWorkbook.Sheets(1).Range("B13")
                                    myArray(i, 2) = ThisWorkbook.Sheets(1).Range("B13").Offset(0, tempcounternew3)
                                    myArray(i, 3) = Application.WorksheetFunction.VLookup(ThisWorkbook.Sheets(1).Range("B13").Offset(0, tempcounternew3 - 1), [lookuprng], 2, 0)
                                    myArray(i, 4) = Application.WorksheetFunction.VLookup(ThisWorkbook.Sheets(1).Range("B13").Offset(0, tempcounternew3 - 1), [lookuprng], 3, 0)
                            End Select
                    End Select
                End Select
        End Select
       
       
  Next
    Sheets(1).Range("H19").Resize(tempCounter, UBound(myArray, 2)) = myArray
End Sub
 https://drive.google.com/file/d/0B23eJ2xd9ODyQTY0blFYRk9McVU/edit?usp=sharing

VBA Code to Display Which Button was Pressed

Sub ClickonwhichButton()

         ' Assign the calling object to a variable.
         ButtonName = Application.Caller

         ' Display the name of the button that was clicked.
         Select Case ButtonName

            ' NOTE: When you type the name of the button, note that
            ' Visual Basic is case and space sensitive when comparing                                                
            ' strings. For example, "Button 6" and "button6" are not the 
            ' same.
            Case "Button 6"
            MsgBox Application.Caller & "  was Clicked"

            Case "Button 7"
            MsgBox Application.Caller & " was clicked."

            Case "Button 8"
            MsgBox Application.Caller & " was clicked."

         End Select

End Sub

Extracting Button Caption on a Click Event of a Button

'Assign this macro to button


Sub test()
MsgBox ActiveSheet.Buttons(Application.Caller).Caption
End Sub

Load MultipleImageFilename in Excel Using VBA

Dim fl, i As Integer, fildialog As Variant

Sub LoadImageFile()
 Set fildialog = Application.FileDialog(msoFileDialogFilePicker)
    With fildialog
        .AllowMultiSelect = True
         .Title = "Select Image"
        .Filters.Clear
     
        .Filters.Add "Image Files", "*.jpg,*.bmp,*.png,*.gif"
        i = 1
        If .Show = True Then
                    For Each fl In .SelectedItems
                        ThisWorkbook.Sheets(1).Range("A" & i) = fl
                   
                        i = i + 1
                    Next
        Else
            MsgBox "Precess cancelled"
        End If
    End With
End Sub

Virtual Key Code List

For Reference:


http://msdn.microsoft.com/en-us/library/ms927178.aspx

Updation of file using VBA

First update data from raw file to final file , second  when i do any changes in raw file it will automatic change in final workbook. Keep both files in same folder


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

Using # In If Condition

# is used only for numeric values in VBA


Option Explicit
Dim mydata, tempval, i

Sub getData()
mydata = Application.InputBox("Enter Data", "Data", Type:=2)
For i = 1 To Len(mydata)
    If Mid(mydata, i, 1) Like "#" Then
        tempval = tempval & Mid(mydata, i, 1)
    End If
Next i
MsgBox tempval
tempval = vbNullString
End Sub

Finding files name from folders/Subfolders

first of all you will add FSO References

Step for Add:- Tools-Reference-Microsoft Scripting Runtime
and try below code

Sub File_name()

Dim fso As FileSystemObject
Dim fl As File
Dim fldr As Folder
Dim wb As Workbook
Set wb = ThisWorkbook
Set fso = New FileSystemObject
Application.FileDialog(msoFileDialogFolderPicker).Title = "Choose Folder"
Application.FileDialog(msoFileDialogFolderPicker).Show
Dim fldpath As String
fldpath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)
& "\"
Set fldr = fso.GetFolder(fldpath)
i = 2
For Each fl In fldr.Files
Sheet3.Cells(i, "D").Value = fl.Name
i = i + 1
Next fl
End Sub

Form Control vs. ActiveX Control in MS Excel

Difference	ActiveX Controls	Excel Controls
Excel versions	97, 2000	5, 95, 97, 2000
Which toolbar?	Control Toolbox	Forms
Controls available	CheckBox, TextBox, CommandButton, OptionButton, ListBox, ComboBox, ToggleButton, SpinButton, ScrollBar, Label, Image	Label, GroupBox, Button, CheckBox, OptionButton, ListBox, ComboBox, ScrollBar, Spinner
Macro code storage	In the code module for the Sheet	In any standard VBA module
Macro name	Corresponds to the control name (e.g., CommandButton1_Click)	Any name you specify.
Correspond to...	UserForm controls	Dialog Sheet controls
Customization	Extensive, using the Properties box	Minimal
Respond to events	Yes	Click or Change events only

Convert Time Zone through VBA

Option Explicit
Private Type SYSTEMTIME
    wYear As Integer
    wMonth As Integer
    wDayOfWeek As Integer
    wDay As Integer
    wHour As Integer
    wMinute As Integer
    wSecond As Integer
    wMilliseconds As Integer
End Type
Private Type TIME_ZONE_INFORMATION
    Bias As Long
    StandardName(31) As Integer
    StandardDate As SYSTEMTIME
    StandardBias As Long
    DaylightName(31) As Integer
    DaylightDate As SYSTEMTIME
    DaylightBias As Long
End Type
Private Declare Function GetTimeZoneInformation Lib "kernel32" (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long
'Purpose     :  Converts local time to GMT.
'Inputs      :  dtLocalDate                 The local data time to return as GMT.
'Outputs     :  Returns the local time in GMT.
'Author      :  Andrew Baker
'Date        :  13/11/2002 10:16
'Notes       :
'Revisions   :
Public Function ConvertLocalToGMT(dtLocalDate As Date) As Date
    Dim lSecsDiff As Long
 
    'Get the GMT time diff
    lSecsDiff = GetLocalToGMTDifference()
    'Return the time in GMT
    ConvertLocalToGMT = DateAdd("s", -lSecsDiff, dtLocalDate)
End Function

'Purpose     :  Converts GMT time to local time.
'Inputs      :  dtLocalDate                 The GMT data time to return as local time.
'Outputs     :  Returns GMT as local time.
'Author      :  Andrew Baker
'Date        :  13/11/2002 10:16
'Notes       :
'Revisions   :
Public Function ConvertGMTToLocal(gmtTime As Date) As Date
    Dim Differerence As Long
 
    Differerence = GetLocalToGMTDifference()
    ConvertGMTToLocal = DateAdd("s", Differerence, gmtTime)
End Function

'Purpose     :  Returns the time lDiff between local and GMT (secs).
'Inputs      :  dtLocalDate                 The local data time to return as GMT.
'Outputs     :  Returns the local time in GMT.
'Author      :  Andrew Baker
'Date        :  13/11/2002 10:16
'Notes       :  A positive number indicates your ahead of GMT.
'Revisions   :
Public Function GetLocalToGMTDifference() As Long
    Const TIME_ZONE_ID_INVALID& = &HFFFFFFFF
    Const TIME_ZONE_ID_STANDARD& = 1
    Const TIME_ZONE_ID_UNKNOWN& = 0
    Const TIME_ZONE_ID_DAYLIGHT& = 2
 
    Dim tTimeZoneInf As TIME_ZONE_INFORMATION
    Dim lRet As Long
    Dim lDiff As Long
 
    'Get time zone info
    lRet = GetTimeZoneInformation(tTimeZoneInf)
 
    'Convert diff to secs
    lDiff = -tTimeZoneInf.Bias * 60
    GetLocalToGMTDifference = lDiff
 
    'Check if we are in daylight saving time.
    If lRet = TIME_ZONE_ID_DAYLIGHT& Then
        'In daylight savings, apply the bias
        If tTimeZoneInf.DaylightDate.wMonth <> 0 Then
            'if tTimeZoneInf.DaylightDate.wMonth = 0 then the daylight
            'saving time change doesn't occur
            GetLocalToGMTDifference = lDiff - tTimeZoneInf.DaylightBias * 60
        End If
    End If
End Function

Data Validation Using VBA

Please find the attachment

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

Example of Select Case in VBA

Dim rng As Range, totalsalary As Long

Sub calculatesalary()
Set rng = Application.InputBox("Select Range", "salary", Type:=8)
For Each cell In rng
        Select Case cell.Value
        Case Is <= 900
         totalsalary = cell.Value + (cell.Value * 0.1)
        Case 901 To 1000
         totalsalary = cell.Value + (cell.Value * 0.125)
        Case Is > 1000
        totalsalary = cell.Value + (cell.Value * 0.15)
        End Select
        cell.Offset(, 1) = totalsalary
Next
End Sub

Using Enumeration In VBA

VBA code to develop a function to calculate salary with allowance



Public Enum commission
grade1 = 100
grade2 = 125
grade3 = 150
End Enum

Function Calculatesalary(ByVal rng As Range) As Long
Dim salary, totalsalary As Long, myrng As Range

Set myrng = rng

salary = CLng(myrng.Value)

Select Case (salary <= 900)
Case True

    totalsalary = salary + salary * ((commission.grade1) / 1000)
 
Case Else
    Select Case (900 < salary <= 1000)
    Case True
    totalsalary = salary + salary * ((commission.grade2) / 1000)
    Case Else
        Select Case (1000 < salary)
        Case True
            totalsalary = salary + salary * ((commission.grade3) / 1000)
        End Select
    End Select

End Select

Calculatesalary = totalsalary


totalsalary = 0
End Function

For details read

http://www.cpearson.com/excel/Enums.aspx

Count of Vowels In A String USING VBA

Option Explicit
Dim i As Integer, j As Integer
Dim myval As String
Sub countofVowels()
myval = Application.InputBox("Enter a word", "Word", Type:=2)

For i = 1 To Len(myval)
If LCase(Mid(myval, i, 1)) Like "[a,e,i,o,u]" Then
j = j + 1
End If


Next
MsgBox "Total count of Vowels" & j
i = 0
j = 0
End Sub

Insert Multiple Row before Unique Value through VBA

Region	Product	Grand_Total
ANZ	Tech	159
ANZ	OFM	70
ANZ	OFM	70
TC	HCIL	41
TC	HCIL	297
ASEAN	Apps	80
ASEAN	Apps	587
ASEAN	Systems	350
ASEAN	Apps	34
MGI	ORC	600
MGI	ORC	658
MGI	ORC	750
DL	ORG	340
DL	ORG	123
DL	ORG	107
GC	Systems	161
GC	Apps	83
GC	Apps	83
HR	CI	611
HR	CI	113
HR	CI	596
IN	Tech	551
IN	Tech	832
IN	Tech	66
KR	Tech	275
KR	OFM	87
KR	OFM	81
MP	HCL	665
MP	HCL	579
HP	MPGC	662
HP	MPGC	672
HP	MPGC	319
HP	MPGC	772
HP	MPGC	129

For example  if you want to insert a row after every unique region here is code

Option Explicit
Dim mycoll As Collection, strrow As String, finalstrrow As String
Dim myrng As Range, rowcount As Long, cell, i As Integer
Sub insertRowafterUnique()
rowcount = ThisWorkbook.Sheets(1).Range("A1").End(xlDown).Row
Set mycoll = New Collection
Set myrng = ThisWorkbook.Sheets(1).Range("A2:A" & rowcount)
strrow = vbNullString
On Error Resume Next
    For Each cell In myrng
        mycoll.Add cell, CStr(cell)

    Next
 
    For i = 2 To rowcount
            If ThisWorkbook.Sheets(1).Range("A" & i) <> ThisWorkbook.Sheets(1).Range("A" & (i + 1)) Then
                strrow = strrow & (i + 1) & ":" & (i + 1) & ","
             
 
 
            End If
    Next
    finalstrrow = Left(strrow, Len(strrow) - 1)
    ThisWorkbook.Sheets(1).Range(finalstrrow).EntireRow.Insert
End Sub

Find vs Search

Find
- Case Sensitive
- Can't using Wildcard

Seach
- Not Case Sensitive
- Can Using Wildcard

Create Validation by Removing Duplicates

A

B

C

D

B

C

A

F

G

D

A

Option Explicit
Dim rowcount As Long
Dim myarray As New Collection, tempval As String, finaltempval As String

Sub addValidation()
On Error Resume Next
rowcount = Sheets(2).Range("a3").End(xlDown).Row
For i = 3 To rowcount
tempval = vbNullString
myarray.Add Range("A" & i), Range("A" & i)

Next i
For j = 1 To myarray.Count
    tempval = tempval & myarray(j) & ","

Next j

finaltempval = Mid(tempval, 1, Len(tempval) - 1)

With Range("B1").Validation

    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=finaltempval

End With
finaltempval = vbNullString
End Sub

Hide Formula in Workbook

Goto format of cell - protection- check Hidden - OK....after this protect your sheet...

Setting Up Expiry Date for Workbook

Private Sub Workbook_Open()
    Dim ExpDate As Date
    ExpDate = #8/17/2013 10:00:00 AM#
    
    If Now > ExpDate Then ThisWorkbook.Close
    MsgBox "This workbook will expired at " & Format(ExpDate, "dd mmmm yyyy hh:mm:ss AM/PM")
End Sub

VBA Automation for MS Excel File

In Master Sheet there are three cells in attached sheet G1,G2 & J2.  When I will run macro it should check that whether values are entered in these cells.  If any one of cells are blank, cell should be RED and give message that values are not entered. and then it should ask Do you want to enter data now, if yes input box for entering value.


Option Explicit
Dim birth As Range, PANNo As Range, empName As Range, userInput



Sub validateData()
Application.ScreenUpdating = False
Set birth = [G2]
Set PANNo = [J2]
Set empName = [G1]
If IsEmpty(empName) Then
    [empName].Interior.ColorIndex = 3
    [empName].Interior.Pattern = xlSolid
    If MsgBox("Employee Name is empty;Please enter now", vbYesNo) = vbYes Then
        userInput = Application.InputBox("Please Enter Employee name", Type:=2)
        If Len(userInput) > 0 Then
            [empName].Value = userInput
            [empName].Interior.ColorIndex = 2
        End If
    End If
End If
If IsEmpty(birth) Then
    [birth].Interior.ColorIndex = 3
    [birth].Interior.Pattern = xlSolid
    If MsgBox("Date of Birth is empty; Please enter now", vbYesNo) = vbYes Then
    userInput = vbNullString
        userInput = Application.InputBox("Enter DOB", "DOB in mm/dd/yyyy format", Default:=Format(Date, "mm/dd/yyyy"), Type:=2)
        If IsDate(userInput) Then
            [birth].Value = userInput
            [birth].Interior.ColorIndex = 2
        End If
    End If

 
End If
If IsEmpty(PANNo) Then

    [PANNo].Interior.ColorIndex = 3
    [PANNo].Interior.Pattern = xlSolid
    If MsgBox("PAN No. is empty;Please enter now", vbYesNo) = vbYes Then
        userInput = vbNullString
        userInput = Application.InputBox("Enter PAN No.", "PAN No.", Type:=2)
        If Len(userInput) = 10 Then
            [PANNo].Value = userInput
            [PANNo].Interior.ColorIndex = 2
        End If
    End If
End If
End Sub

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

Use Split in VBA

Sub useSplitinVBA()
Dim myarry1 As Variant
Dim i As Integer
On Error Resume Next
myarry1 = Split(Sheet1.Cells(1, 1).Value, "\")
    For i = LBound(myarry1) To UBound(myarry1)
 
        If WorksheetFunction.Find(",", myarry1(i)) > 0 Then
        myarry2 = Split(myarry1(i), ",")
            For k = LBound(myarry2) To UBound(myarry2)
            MsgBox myarry2(k)
            Next
        End If
 
    Next
End Sub

Using Resize Property to Change the size of a range

The Resize property enables you to change the size of a range based on the location of the active cell.
You can create a new range as you need it.

For Example

Sub rngresize()

    Set Rng = Range("B1:B16").Find(What:="0", LookAt:=xlWhole, LookIn:=xlValues)
 
    Rng.Offset(, -1).Resize(, 2).Interior.ColorIndex = 15


End Sub


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

Print Userform in Landscape Format

Private Declare Sub keybd_event Lib "user32" _
(ByVal bVk As Byte, _
ByVal bScan As Byte, _
ByVal dwFlags As Long, _
ByVal dwExtraInfo As Long)

'dwFlags parameter of keybd_event controls various aspects of function operation. _

'This parameter can be one or more of the following values.

'KEYEVENTF_KEYUP
'if specified, the key is being released.If not specified, the key is being depressed.

'KEYEVENTF_EXTENDEDKEY
'If specified, the scan code was preceded by a prefix _
'byte having the value 0xE0 (224).

Private Const KEYEVENTF_KEYUP = &H2
Private Const KEYEVENTF_EXTENDEDKEY = &H1
'Print Screen key
Private Const VK_SNAPSHOT = &H2C
'Alt Key
Private Const VK_MENU = &H12
'Left Alt Key
Private Const VK_LMENU = &HA4

Private Sub CommandButton1_Click()
    Dim wshTemp As Worksheet
    DoEvents

    ' Simulate pressing ALT+Printscreen to copy the form window (=picture) to
    ' the clipboard
    keybd_event VK_LMENU, 0, KEYEVENTF_EXTENDEDKEY, 0
    keybd_event VK_SNAPSHOT, 0, KEYEVENTF_EXTENDEDKEY, 0
    keybd_event VK_SNAPSHOT, 0, KEYEVENTF_EXTENDEDKEY + KEYEVENTF_KEYUP, 0
    keybd_event VK_LMENU, 0, KEYEVENTF_EXTENDEDKEY + KEYEVENTF_KEYUP, 0
    DoEvents

    ' Add a worksheet named Temp
    ThisWorkbook.Worksheets.Add
    ActiveSheet.Name = "Temp"
    Set wshTemp = ThisWorkbook.Worksheets("Temp")

    ' Paste the picture, set print orientation to landscape en print it
    With wshTemp
     .Paste
     .PageSetup.Orientation = xlLandscape
     .PrintOut
    End With

    ' Delete the worksheet Temp and suppress the not-saved Warning.
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets("Temp").Delete
    Application.DisplayAlerts = True
End Sub

For reference:

http://msdn.microsoft.com/en-us/library/windows/desktop/ms646267.aspx

Searching Files of All Format in a Folder

Option Explicit
Dim pathname As String
Dim i As Integer
Dim fso As Object
Dim folder1 As Object
Dim file1 As Object
Sub getFileDetailsunderSpecifiedFolder()
pathname = Application.InputBox("Provide path of specified Folder", "Pathname", Type:=2)
i = 2
Set fso = New Scripting.FileSystemObject
Set folder1 = fso.GetFolder(pathname)
    For Each file1 In folder1.Files
        Cells(i, 1) = file1.Name
        Cells(i, 2) = Environ("Username")
        Cells(i, 3) = file1.DateLastAccessed
        Cells(i, 4) = file1.DateLastModified
   
        i = i + 1
   
    Next
End Sub


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

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