អំពី VBA (ត)

២-Worksheets

Workbook គឺជាស៊េរីនៃ worksheets ជាច្រើន។នេះមានន័យថាworkbook ផ្ទុកនូវworksheets ជាច្រើន។

-ការចូលទៅកាន់ worksheet

Sub Exercise()

​​​​​​​​​​​​​​​​​​​​​​​​​​                    ​​​     Dim journalentry as worksheet      

    Set journalentry=Workbooks.Item(1).Worksheets(2)

End Sub

រឺ

Sub Exercise()

     Dim journalentry as worksheet

    Set journalentry=Workbooks.Item(1).Sheets.Item("Sheet3")

End Sub

          រឺ

Sub Exercise()

    Dim journalentry as worksheet

    Set journalentry=Workbooks(1).Worksheets(2)

End Sub

          -ការ select worksheet

Private Sub Exercise()

    Sheets("Sheet1").Select

End Sub

-ការដាក់ឈ្មោះអោយ worksheet ដោយប្រើប្រាស់ Name ប្រូភើធី

Private Sub Exercise()

    Sheets("Sheet1").Name = "Employees Records"

End Sub

            -បន្ថែម worksheet ដោយប្រើប្រាស់ Add  method របស់ sheets collection

Private Sub cmdNewWorksheet_Click()

    Sheets.Add

End Sub

            -លុប worksheet ដោយប្រើប្រាស់ delete method

Private Sub cmdRemoveSheet_Click()

    Worksheets("Sheet3").Delete

    txtRemoveSheet.Text = ""

End Sub

២-ជួរឈរ(Columns) របស់Worksheet

-ការចូលទៅកាន់ជួរឈរ(Column) ដោយប្រើ index

Sub Exercise()

    ' This refers to the 1th column

    Workbooks(1).Worksheets(2).Columns(1)

    ' This refers to the 12th column

    Workbooks(1).Worksheets(2).Columns(12)

End Sub

-ការចូលទៅកាន់ជួរឈរ(Column) ដោយប្រើ name/label

Sub Exercise()

     “This refers to the column named/labeled A

    Columns("A")

    ' This refers to the column named DR

    Columns("DR")

End Sub

Sub ColumnReference()

    ‘ Refer to the range of columns D to G

    Columns("D:G")

End Sub

Sub Exercise()

    ‘This refers to Columns H, D, and B

    Range("H:H, D:D, B:B")

End Sub

            -ការជ្រើសរើសជួរឈរ(column selection)

Sub Exercise()

    ‘This selects the fourth column

    Columns(4).Select

End Sub

Sub Exercise()

    ‘This selects the column labeled ADH

    Columns("ADH").Select

End Sub

Sub Exercise()

    ‘This selects the range of columns from Column D to Column G

    Columns("D:G").Select

End Sub

Sub Exercise()

    ‘This selects Columns B, D, and H

    Range("H:H, D:D, B:B").Select

End Sub

-ការបង្កើតជួរឈរ(Column)

Sub CreateColumn()

    Columns(3).Insert

End Sub

Sub CreateColumns()

    Range("H:H, D:D, B:B").Insert

End Sub

-ការលុបជួរឈរ(Column)

Sub DeleteColumn()

  ​​​​​​​​​​​​​​​​​​​​​​       'delete 1 column

    Columns("D:D").Delete

End Sub

Sub DeleteColumns()

    ‘delete many columns

    Columns("D:F").Delete

End Sub

-ការកំនត់តំលៃអោយទំហំទទឹងនៃជួរឈរ

Sub Exercise()

    Columns("C").ColumnWidth = 4.5

End Sub

Sub Exercise()

    Range("C:C, E:E, H:H").ColumnWidth = 5#

End Sub

-ការកំនត់អោយទំហំនៃជួរឈររីក រឺរួមដោយស្វ័យប្រវត្តិ

Private Sub Exercise()

    Selection.Columns.AutoFit

End Sub

-ការលាក់ រឺបង្ហាញជួរឈរ

Private Sub Exercise()

  ​​​​​​       'hide column F

    Columns("F:F").Select

    Selection.EntireColumn.Hidden = True

End Sub

Private Sub Exercise()

    ‘reveal column F

    Columns("F:F").Select

    Selection.EntireColumn.Hidden = True

End Sub

៣-ជួរដេក(Rows) របស់Worksheet

-ការចូលទៅកាន់ជួរដេក

Sub Exercise()

  ​​​​       'Refere to 5th row

    Workbooks.Item(1).Worksheets.Item(2).Rows(5)

End Sub

Sub Exercise()

          'Refere to 2th to 6th row

    Rows("2:6")

End Sub

Sub Exercise()

     'Refere to 3th, 5th, and 8th row

    Range("3:3, 5:5, 8:8")

End Sub

-ការជ្រើសរើសជួរដេក

Sub Exercise()

  ​​​​​​​​​       'select 6th row

    Rows(6).Select

End Sub

Sub Exercise()

    ‘select from 2th to 6^th row

    Rows("2:6").Select

End Sub

Sub Exercise()

    ‘select 3th, 5^th, and 8^th row

    Range("3:3, 5:5, 8:8").Select

End Sub

Sub Exercise()

    ‘select all rows

    Rows.Select

End Sub

      -ការកំនត់កំពស់អោយជួរដេក

Sub Exercise()

​​​​​​​​

    Rows(6).RowHeight = 2.5

End Sub

         -ការបង្កើតជួរដេក

Sub Exercise()

​​​​​​​          'add 1 row

    Rows(3).Insert

End Sub

Sub Exercise()

    ‘add many rows

    Range("3:3, 6:6, 10:10").Insert

End Sub

         -ការលុបជួរដេក

Sub Exercise()

​​​​​          'delete 1 row

    Rows(3).Delete

End Sub

Sub Exercise()

    'delete many rows

    Range("3:3, 6:6, 10:10").Delete

End Sub

-ការលាក់ជួរដេក

Private Sub Exercise()

    Rows("6:6").Select

    Selection.EntireRow.Hidden = True

End Sub

Posted in: Excel 2007,VBA