Microsoft Excel Basic Coding

Yang berhubungan dengan Cell

How to refer to one cell in a particular sheet?
Style1: Using range property
Use: Range property + A1 Notation

Example:

Sub onecell()
    Sheet1.Range("A1").Select
End Sub

Style 2: Using index number
Use: Row Index number, Column index number, cells property
Example:
Sub onecell()
    Sheet1.cells(1,1).Select
End Sub

Note: Also good for used with looping statement.

How to refer to one cell that relative to another active cell? (Relativity)
Use: offset property
Example:
This can be both do to style 1 and style 2

Sub onecell()
    Sheet1.cells(1,1).offset(1,2).Value = 10
end sub


How to refer to cells (more than one cell)
Style1: Using A1 Notation
Use: Range property + A1 Notation
Example:
Sub manycells1()
    Sheet1.Range("A1:A3").Select
End Sub

Style2: Using Range Object
Use: dim, set, object variable

Example:
sub manycells2()
    dim a as range
    set a = sheet1.range("A1:A3")
    a.select
end sub

Style3: Using shortcut notation
As the title implies, this is the shorcut of using Range word. That is, you don't have to type word Range or use Quotation marks anymore for shortcuts.

Use: [A1:A2] or [MyRange] if you have define those cells in name manager.

Example:
Sub manycells()
    Sheet1.[MyRange].Select
End sub

Note: You don't have to type word "Range" or use Quotation marks after Sheet1.

How to refer to all cell in a particular sheet
Use: cells
Example:
sub allcell()
    sheet1.cells.activate
end sub

Yang berhubungan dengan Sheet

How to refer a one sheet?
Style 1:
Use Worksheets name
Example:
Sub onesheet()
    Worksheets("sheet2").Select
End Sub

Style2: 
Use Worksheets index number
Example:
Sub onesheet()
    Worksheets(2).Select
End Sub

How to refer to more than one sheets?
Use: worksheets property and array function
Example:
Sub several_sheets()
    worksheets(Array("Sheet1","Sheet2")).Select
end sub

How to refer to all sheet in a workbook?
Use: Worksheets property without specifications.
Example:
Sub all_sheet()
    Worksheets.Select
End Sub

Yang berhubungan dengan Workbook

How to create a new workbook
Use: workbooks.add
Example:
Sub AddOne()
    Workbooks.add
End sub

How to activate a workbook
Use: workbooks properties and book name(for i.e. : "book1")
Example:
Sub ActivateOne()
    Workbooks("book1").activate
End sub

How to open a workbook
Use: Open method and, path to the file
Example:
Sub OpenUp()
    Workbooks.Open ("C:\Mybook")
End Sub
How to save a workbook
Use: saves or save and path to the file

Example:
Sub Save()
    Workbooks("Mybook").SaveAs ("E:\Mybook")
End Sub

Now you have enough tool to do these tasks:
1. Copy and paste from sheet1 to sheet2
Untuk pemilihan sel saya lebih prefer menggunakan keyword range daripada cells (lebih praktis) meskipun untuk seleksi satu sel saja saya tetap menggunakan keyword range.

Sub copy_paste()
    Sheet1.Range("A1:A2").Copy
    Sheet2.Range("A1:A2").PasteSpecial
End Sub

Well, you can implement those with macro recording though.

2. Adding a comment on a cell or cells
Sub