WorkSheet

'Worksheet Object
'Represents a worksheet.
'Remarks
'
'
'The Worksheet object is a member of the Worksheets collection. The Worksheets collection contains all the Worksheet objects in a workbook.
'
'The Worksheet object is also a member of the Sheets collection. The Sheets collection contains all the sheets in the workbook (both chart sheets and worksheets).
'
'
'Example
'
'
'Use Worksheets(index), where index is the worksheet index number or name, to return a single Worksheet object. The following example hides worksheet one in the active workbook.
'
'Visual Basic for Applications
'Worksheets(1).Visible = False
'
'The worksheet index number denotes the position of the worksheet on the workbook’s tab bar. Worksheets(1) is the first (leftmost) worksheet in the workbook, and Worksheets(Worksheets.Count) is the last one. All worksheets are included in the index count, even if they’re hidden.
'
'The worksheet name is shown on the tab for the worksheet. Use the Name property to set or return the worksheet name. The following example protects the scenarios on Sheet1.
'

'Create a macro to find out if the workbook belongs to him.
Sub Wokbookname()

Name = Application.UserName
ans = MsgBox("Is your name: " & Name, vbYesNo)
If ans = vbYes Then MsgBox "Great"
If ans = vbNo Then MsgBox "Ohh I am Sorry"

End Sub

Sub testWrksheets()
   
    Worksheets("sheet14").Select
   
    Worksheets("sheet12").Select False                  'Selecting multiple sheet
   
    Worksheets(5).Select        'Indexing of worksheets
   
    Sheet16Code.Select      'anotherway to selectsheet(by 2nd name of the sheet)
   
    Worksheets.Add          'adding a worksheet
   
    Worksheets.Add Worksheets("Sheet14")        'adding a worksheet before sheet14
   
    Worksheets.Add , Worksheets("Sheet12")      'adding a worksheet after sheet12 or you can _
                                                    write before:=/after:=worksheets("Sheet14") for clear refernce
   
    Worksheets.Add Before:=Worksheets(1)        'adding sheet to the start
   
    Worksheets.Add after:=Worksheets(Sheets.Count)      'adding sheet to the end
   
    Worksheets.Add after:=Worksheets(Sheets.Count), Count:=3    'adding 3 new sheets to the end
   
    Sheets.Add Before:=Worksheets(1), Type:=XlSheetType.xlChart     'adding chart type worksheet
   
    Application.DisplayAlerts = False
   
    Sheets(Sheets.Count).Delete       'deleting last sheet of the workbook
   
    Application.DisplayAlerts = True    'please note sheets include charts and other type but worksheets only refer to spreadsheet
   
    Charts.Delete   'delete all object type in one go
   
    Worksheets("sheet1").Copy , Worksheets("sheet25")   'copying sheet to the specific location
   
    Worksheets("sheet1").Copy   'without option if copied it will be copied in new brand new workbook
   
    Worksheets("sheet1").Copy Workbooks("book1").Sheets(1)      'coping to a another workbook
   
    Sheet1.Move after:=Sheets(Sheets.Count)      'moving of sheet
   
    Worksheets("sheet28").Name = "somethingelse"       'Name change of sheet
   
    Worksheets("somethingelse").Visible = xlSheetHidden     'hiding a sheet
   
    Worksheets("somethingelse").Visible = xlSheetVisible    'Unhiding a sheet
   
    Worksheets("somethingelse").Visible = xlSheetVeryHidden 'user cant unhide the sheet without code

End Sub

WorkBooks

'Workbook Object
'Represents a Microsoft Excel workbook.
'Remarks
'
'
'The Workbook object is a member of the Workbooks collection. The Workbooks collection contains all the Workbook objects currently open in Microsoft Excel.
'
'ThisWorkbook property
'The ThisWorkbook property returns the workbook where the Visual Basic code is running. In most cases, this is the same as the active workbook. However, if the Visual Basic code is part of an add-in, the ThisWorkbook property won’t return the active workbook. In this case, the active workbook is the workbook calling the add-in, whereas the ThisWorkbook property returns the add-in workbook.
'
'If you’ll be creating an add-in from your Visual Basic code, you should use the ThisWorkbook property to qualify any statement that must be run on the workbook you compile into the add-in.
'

Option Explicit
'Example WorkBooks
Sub TestWorkBook1()

Workbooks.Add
ActiveWorkbook.SaveAs "C:\Users\Vinay Kumar\Desktop\testWB.xlsx"
ActiveWorkbook.Close

End Sub

Sub TestWorkBook2()

Workbooks.Add.SaveAs Environ("userprofile") & "\Desktop\testWB56.xlsx"
ActiveWorkbook.Close
ActiveWorkbook.Save

End Sub

Sub TestWorkBook3()

Workbooks.Open (Environ("userprofile") & "\desktop\testwb5.xlsx")

End Sub

With Statement

'With Statement
'
'Executes a series of statements on a single object or a user-defined type.
'
'Syntax
'
'With Object
'[statements]
'
'End With
'
'The With statement syntax has these parts:
'
'Part Description
'object Required. Name of an object or a user-defined type.
'statements Optional. One or more statements to be executed on object.
'
'Remarks
'
'The With statement allows you to perform a series of statements on a specified object without requalifying the name of the object. For example, to change a number of different properties on a single object, place the property assignment statements within the With control structure, referring to the object once instead of referring to it with each property assignment. The following example illustrates use of the With statement to assign values to several properties of the same object.


Option Explicit

'Example With Statement
Sub TestWithStatement()
   
    With Range("a1:f1")
        .Interior.Color = vbRed
        .Font.Color = vbBlue
        .Font.Size = 18
        .Select
        .ColumnWidth = 20
    End With
   
End Sub

Type Declartion

'
'Type Statement
'
'Used at module level to define a user-defined data type containing one or more elements.
'
'Syntax
'
'[Private | Public] Type varname
'elementname [([subscripts])] As type
'[elementname [([subscripts])] As type]
'. . .
'
'End Type
'
'The Type statement syntax has these parts:
'
'Part Description
'Public Optional. Used to declare user-defined types that are available to all procedures in all modules in all projects.
'Private Optional. Used to declare user-defined types that are available only within the module where the declaration is made.
'varname Required. Name of the user-defined type; follows standard variable naming conventions.
'elementname Required. Name of an element of the user-defined type. Element names also follow standard variable naming conventions, except that keywords can be used.
'subscripts When not explicitly stated in lower, the lower bound of an array is controlled by the Option Base statement. The lower bound is zero if no Option Base statement is present.
'type Required. Data type of the element; may be Byte, Boolean, Integer, Long, Currency, Single, Double, Decimal (not currently supported), Date, String (for variable-length strings), String * length (for fixed-length strings), Object, Variant, another user-defined type, or an object type.

Option Explicit
'Type Declartion(User Define Type) at module level only
Type Address
    NameNumber As String
    Street As String
    Town As String
    Country As String
    PostCode As String
End Type

Type Contact
    Title As String
    FirstName As String
    LastName As String
    DateofBirth As Date
   
    HomeAddress As Address
    WorkAddress As Address
End Type

Private Type Student

    Name As String
    Hindi As Integer
    Eng As Integer
    Math As Integer
    Science As Integer
    Behaviour As Behaviours
End Type

Private Enum Behaviours
    Good
    Bad
    Naughty
    Ugly
    Sweet
End Enum

Private Sub TestStudentType()

Dim NewStudent As Student

NewStudent.Name = "Ridhi"
NewStudent.Hindi = 45
NewStudent.Math = 63
NewStudent.Eng = 85
NewStudent.Science = 96
NewStudent.Behaviour = Good
NewStudent.Behaviour = Bad

MsgBox NewStudent.Behaviour     ' Enum output are in numbers start from 0, use SELECT CASE _
                                        to display as string value

Worksheets("Type Declaration").Activate

Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select

ActiveCell.value = NewStudent.Name
ActiveCell.Offset(0, 1).value = NewStudent.Hindi
ActiveCell.Offset(0, 1).value = NewStudent.Eng
ActiveCell.Offset(0, 1).value = NewStudent.Math
ActiveCell.Offset(0, 1).value = NewStudent.Science

End Sub

Sub TestContact()

Dim C As Contact

C.FirstName = "Candy"
C.HomeAddress.NameNumber = "90"

MsgBox C.FirstName
MsgBox C.HomeAddress.NameNumber
End Sub

Text File

''TextStream Object
'
'Description
'
'Facilitates sequential access to file.
'
'Syntax
'
'TextStream.{property | method}
'
'The property and method arguments can be any of the properties and methods associated with the TextStream object. Note that in actual usage TextStream is replaced by a variable placeholder representing the TextStream object returned from the FileSystemObject.
'

Option Explicit
'Create Text file on desktop and write few lines and close it.
Sub CreatingNewTextFile()

Dim fso As Scripting.FileSystemObject
Dim txt As Scripting.TextStream

Set fso = New Scripting.FileSystemObject
Set txt = fso.CreateTextFile(Environ("userprofile") & "\desktop\test1.txt")

    txt.Write "Created on: " & Now & vbNewLine
    txt.WriteLine "Created by: " & Environ("username")
    txt.WriteBlankLines (2)
    txt.Write ("Data starts from here:")
     txt.WriteBlankLines (2)
txt.Close

Set fso = Nothing

End Sub
'Append a text file and write from the excel file
Sub AddDataToaTextFile()

Dim fso As Scripting.FileSystemObject
Dim txt As Scripting.TextStream
Dim counter As Integer
Dim r As Range

Set fso = New Scripting.FileSystemObject
Set txt = fso.OpenTextFile(Environ("userprofile") & "\desktop\test1.txt", ForAppending, False)

Sheet2.Activate

For Each r In Range("a1", Range("a1").End(xlDown))

    For counter = 1 To Range("a1", Range("a1").End(xlToRight)).Cells.Count
        txt.Write r.Offset(0, counter - 1).value
            If counter < Range("a1", Range("a1").End(xlToRight)).Cells.Count Then txt.Write vbTab
    Next counter
   
    txt.WriteLine
   
Next r
txt.Close
Set fso = Nothing

End Sub

'Append a CSV file and write from the excel file
Sub AddDataToaCSVFile()

Dim fso As Scripting.FileSystemObject
Dim txt As Scripting.TextStream
Dim counter As Integer
Dim r As Range

Set fso = New Scripting.FileSystemObject
Set txt = fso.OpenTextFile(Environ("userprofile") & "\desktop\test1.csv", ForAppending, True)

Sheet2.Activate

For Each r In Range("a1", Range("a1").End(xlDown))

    For counter = 1 To Range("a1", Range("a1").End(xlToRight)).Cells.Count
        txt.Write r.Offset(0, counter - 1).value
            If counter < Range("a1", Range("a1").End(xlToRight)).Cells.Count Then txt.Write ","
    Next counter
    txt.WriteLine
   
Next r
txt.Close
Set fso = Nothing

End Sub
'Reading from text file and pasting it on excel
Sub ReadfromTextFile()

Dim fso As Scripting.FileSystemObject
Set fso = New Scripting.FileSystemObject
Dim txt As Scripting.TextStream
Dim tabposition As Integer
Dim textline As String

Set txt = fso.OpenTextFile(Environ("userprofile") & "\desktop\test1.txt", ForReading)
'txt.SkipLine

Worksheets.Add

Do Until txt.ReadLine = "Data starts from here:"
Loop

Do Until txt.AtEndOfStream

     textline = txt.ReadLine
     tabposition = InStr(textline, vbTab)
   
     Do Until tabposition = 0
        ActiveCell.value = Left(textline, tabposition - 1)
        ActiveCell.Offset(0, 1).Select
        textline = Right(textline, Len(textline) - tabposition)
        tabposition = InStr(textline, vbTab)
     Loop
   
    ActiveCell.value = textline
    ActiveCell.Offset(1, 0).End(xlToLeft).Select
   
Loop

txt.Close
Set fso = Nothing

End Sub

'Reading from text file and pasting on excel via text to column
Sub ReadfromTextFileEasierMethod()

Dim fso As Scripting.FileSystemObject
Set fso = New Scripting.FileSystemObject
Dim txt As Scripting.TextStream


Set txt = fso.OpenTextFile(Environ("userprofile") & "\desktop\test1.txt", ForReading)

Worksheets.Add
Do Until txt.AtEndOfStream

   
        ActiveCell.value = txt.ReadLine
       
        ActiveCell.Offset(1, 0).Select
   
Loop
Range("A:A").TextToColumns Tab:=True
txt.Close
Set fso = Nothing

End Sub
'Easiest method to open text file in excel
Sub ReadfromTextFileEasiestMethod()

Workbooks.OpenText filename:=Environ("userprofile") & "\desktop\test1.txt", Tab:=True

End Sub

cell Selection

'Selecting and Activating Cells
'
'When you work with Microsoft Excel, you usually select a cell or cells and then perform an action, such as formatting the cells or entering values in them. In Visual Basic, it is usually not necessary to select cells before modifying them.
'
'For example, if you want to enter a formula in cell D6 using Visual Basic, you do not need to select the range D6. You just need to return the Range object for that cell, and then set the Formula property to the formula you want, as shown in the following example.
'

Option Explicit

Sub TestSelectCells()

Range("a8").Select
ActiveCell.value = 11

Cells(8, 2).Select
ActiveCell.value = "vinay"

[d10].Select
ActiveCell.value = "New Way to refer cells"

End Sub

Sub Name_SelectedCells()

Range("Vendor").Font.Color = rgbBlue
[segment].Font.Color = rgbRed
Range("year").Font.Color = rgbGreen
[value].Font.Color = rgbDarkRed

'relative cell refernce
Range("L5").End(xlDown).Offset(1, 0).Select
ActiveCell.value = ActiveCell.Offset(-1, 0).value + 1
Range("i5", Range("i5").End(xlDown).End(xlToRight)).Interior.Color = rgbAliceBlue

'using current region
Range("a1").CurrentRegion.Select
Selection.Copy
Worksheets("Sheet6").Select
Range("a1").Select
Range("a1").PasteSpecial
Range("a1").PasteSpecial xlPasteColumnWidths

End Sub

Case Statement

'
'Select Case Statement
'
'Executes one of several groups of statements, depending on the value of an expression.
'
'Syntax
'
'Select Case testexpression
'[Case expressionlist-n
'[statements-n]]
'
'...
'
'
'[Case Else
'[elsestatements]]
'
'End Select

Option Explicit
'Example Select Case
Sub TestSelectCase()

Dim ProductPrice As Integer

ProductPrice = InputBox("What is the price of product?")

    Select Case ProductPrice
           
            Case Is <= 2000
                MsgBox "Discount Percent:5% and discounted amount is: " & ProductPrice * 0.05
           
            Case Is <= 5000
                MsgBox "Discount Percent:10% and discounted amount is: " & ProductPrice * 0.1
           
            Case Else
                MsgBox "Discount Percent:15% and discounted amount is: " & ProductPrice * 0.15
           
    End Select
           
    End Sub