Miscellaneous Exercise 1

'Below code will create a NEW WORKBOOK and It will Save the new Workbook to location desktop
Sub Addworkbook()

Set wb = Workbooks.Add
wb.SaveAs filename:="D:\Users\vinay1ku\Desktop\New WB4.xls"

End Sub

'Below code will create a NEW WORKBOOK and It will Save the new Workbook to location desktop; Option2
Sub Addworkbook2()

Workbooks.Add
ActiveWorkbook.SaveAs filename:="D:\Users\vinay1ku\Desktop\New WB5.xls"

End Sub

'How to RENAME the Excel Worksheet Using VBA Macro
Sub RenameFile()

Set ws = ActiveWorkbook.Worksheets("test1")
ws.Name = "test2"

End Sub

'How to RENAME the Excel Worksheet Using VBA Macro; Option2
Sub RenameFile2()

ActiveWorkbook.Worksheets("test1").Name = "test33"

End Sub

'How to count BLANK CELLS/FORMULA and NON_FORMULA cells  in given range in Excel using VBA Macro
Sub BlankCellCount()

Sheet2.Activate
l = Range("b1:b20").Cells.SpecialCells(xlCellTypeFormulas).Count
n = Range("b1:b20").Cells.SpecialCells(xlCellTypeBlanks).Count
m = Range("b1:b20").Cells.SpecialCells(xlCellTypeConstants).Count

MsgBox ("Blank cells: " & n & "; Non-formula cells: " & m & " ; Formula cells: " & l)

End Sub

'How to set the BACKGROUND COLOR OF CELLS using Excel VBA Macro
Sub ColorChange()

Sheet2.Activate
ActiveWorkbook.Worksheets(1).Range("B2:D10").Interior.ColorIndex = 32
Rows("3:3").Select

End Sub

'SEND MAIL through macro with pr without attachement
Sub SendMail()

ActiveWorkbook.SendMail ("kvinay.g@gmail.com")

End Sub

'SEND MAIL through macro with pr without attachement; Option2
Sub sendmail2()

Set outlook = CreateObject("outlook.application")

Set Message = outlook.createitem(0)
    With Message
    .Subject = "test mail"
    .htmlbody = "any textbody"
    .Recipients.Add ("kvinay.g@gmail.com")
    End With

Set myattachments = Message.Attachments
    myattachments.Add "D:\Users\vinay1ku\Desktop\voter id app card.pdf"
    Message.display
    Message.send
   
End Sub

'ARRAY to feed data from worksheet(Single Dimention)
Sub MyArrayTest1()

Dim myarray(1 To 20)                'array always start with 0, if you wish u can mention like shown in example.
    For i = 1 To 20
        myarray(i) = Sheet2.Range("b" & i).value
    Next
    For j = 1 To 20
        Sheet2.Range("m" & j).value = myarray(j)
    Next
   
End Sub

'Array to feed data from worksheet
Sub MyArrayTest2()

    Dim myarray2() As Variant
    Sheet2.Activate
   
    myarray2 = Range("b1:c20")
    MsgBox "Max: " & WorksheetFunction.Max(myarray2) & "," & "Sum: " _
        & WorksheetFunction.Sum(myarray2)

End Sub

'Macro to set range object
Sub RangeTest()

    Dim testrange As Range
    Set testrange = Range("a1:g5")
   
    Sheet2.Activate
    testrange.Select
   
    testrange.Cells.SpecialCells(xlCellTypeBlanks).Select 'Select Blank cells from the range

End Sub

'Find the MAXIMUM from the range
Sub MaxOption()

    Sheet2.Activate
    MsgBox "Max'm in range(C1:C10): " & WorksheetFunction.Max(Range("C1:C10"))
   
End Sub