• Trang chủ
  • Giới thiệu
    • Học gia sư kế toán tại Hà Nội
  • Excel
  • Kế toán
  • Kinh nghỉệm
  • DAX
  • VBA
  • Văn bản
  • Access
  • Liên hệ
    • Chính sách bảo mật
Chủ Nhật, Tháng Ba 26, 2023
  • Login
No Result
View All Result
NEWSLETTER
Học gia sư kế toán
  • Trang chủ
  • Giới thiệu
    • Học gia sư kế toán tại Hà Nội
  • Excel
  • Kế toán
  • Kinh nghỉệm
  • DAX
  • VBA
  • Văn bản
  • Access
  • Liên hệ
    • Chính sách bảo mật
  • Trang chủ
  • Giới thiệu
    • Học gia sư kế toán tại Hà Nội
  • Excel
  • Kế toán
  • Kinh nghỉệm
  • DAX
  • VBA
  • Văn bản
  • Access
  • Liên hệ
    • Chính sách bảo mật
No Result
View All Result
Học Gia sư Kế toán
No Result
View All Result

HƯỚNG DẪN HOÀN CHỈNH VỀ ĐỐI TƯỢNG WORKSHEET VBA

by My Lê
19/02/2023
in VBA
0
HƯỚNG DẪN HOÀN CHỈNH VỀ ĐỐI TƯỢNG WORKSHEET VBA

Originally posted on 12/02/2022 @ 21:32

HƯỚNG DẪN HOÀN CHỈNH VỀ ĐỐI TƯỢNG WORKSHEET VBA

  • Chuyên mục:
  • Excel VBA

Bài viết dưới đây, Học Gia Sư Kế Toán hướng dẫn đầy đủ cách sử dụng Excel worksheet VBA. Nếu muốn biết cách thực hiện nhanh chóng thì có thể xem bảng hướng dẫn tóm gọn dưới đây.

Hướng dẫn nhanh về worksheet VBA

Lưu ý: Các worksheet trong bảng dưới đây không chỉ định workbook, có nghĩa là Worksheet chứ không phải ThisWorkbook.Worksheets, wk.Worksheets… Mục đích để làm các ví dụ dễ đọc hơn. Trên thực tế khi sử dụng worksheet thì phải chỉ định workbook nếu không workbook sẽ được sử dụng theo mặc định.

 

Tác vụ Cách thực hiện
Truy cập worksheet theo tên Worksheets(“Sheet1”)
Truy cập worksheet theo vị trí từ bên trái ở trên sheet bar Worksheets(2)
Worksheets(4)
Truy cập worksheet sheet đầu tiên, tính từ bên trái qua ở trên sheet bar Worksheets(1)
Truy cập worksheet sheet đầu tiên, tính từ bên phải qua Worksheets(Worksheets.Count)
Truy cập bằng cách sử dụng tên mã worksheet (chỉ workbook hiện tại) xem phần code name (tên mã) bên dưới
Truy cập bằng bằng tên mã worksheet (workbook khác) xem phần code name (tên mã) bên dưới
Truy cập worksheet đang hoạt động Activesheet
Khai báo biến worksheet Dim sh As Worksheet
Gán biến worksheet Set sh = Worksheets(“Sheet1”)
Thêm worksheet Worksheets.Add
Thêm worksheet và gán cho biến Set sh = Worksheets.Add
Thêm worksheet vào vị trí đầu tiên (bên trái) Worksheets.Add Before: = Worksheets (1)
Thêm worksheet vào vị trí cuối cùng (bên phải) Worksheets.Add after: = Worksheets (Worksheets.Count)
Thêm nhiều Worksheet Worksheets.Add Count: = 3
Kích hoạt Worksheet sh. Active
Sao chép worksheet sh.Copy
Sao chép sau worksheet sh1.Copy After: = Sh2
Sao chép trước một worksheet sh1.Copy Before: = Sh2
Xóa worksheet sh.Delete
Xóa worksheet mà không có cảnh báo Application.DisplayAlerts = False
sh.Delete
Application.DisplayAlerts = True
Thay đổi tên worksheet sh.Name = “Data”
Hiển thị / ẩn worksheet sh.Visible = xlSheetHidden
sh.Visible = xlSheetVisible
Lặp qua tất cả các trang  (For loop) Dim i As Long
For i = 1 To Worksheets.Count
    Debug.Print Worksheets (i) .Name
Next i
Lặp qua tất cả từng worksheet (For each )loop  Dim sh As Worksheet
For Each sh In Worksheets
    Debug.Print sh.Name
Next

Giới thiệu

Ba yếu tố quan trọng nhất của VBA là workbook, worksheet và cells. Trong tất cả các mã chúng ta viết, 90 sẽ liên quan đến 1 hoặc cả 3 yếu tố trên.

Sử dụng worksheet để truy cập vào các ô của nó rất phổ biến trong VBA. Chúng ta có thể sử dụng để ẩn, thêm, di chuyển hoặc sao chép worksheet khác. Tuy nhiên chúng ta sẽ sử dụng chủ yếu để thực hiện các hành động trên một hoặc nhiều ô.

Sử dụng worksheet đơn giản hơn workbook. Với workbook bạn phải mở chúng sau đó tìm thư mục nằm trong đó và kiểm tra xem có đang được sử dụng hay không. Với worksheet, nó có hiển thị trong workbook hay không.

Truy cập worksheet

Trong VBA, mỗi workbook sẽ có tập hợp nhiều worksheet. Mỗi worksheet trong workbook sẽ có mục để nhập vào một bộ sưu tập, bộ sưu tập được gọi là worksheets và hiểu đơn giản là nó chứa các worksheet trong workbook. Bạn phải đặt tên thì mới có thể truy cập vào worksheet.


VBA101 92019 nho

Đoạn mã dưới đây viết “Hello World” trong Ô A1 của Sheet1, Sheet2 và Sheet3 của workbook hiện tại:

1-Truy cập worksheet
1-Truy cập worksheet

Bộ sưu tập worksheets luôn luôn nằm trong workbook. Nếu chúng ta không chỉ định workbook thì workbook được sử dụng theo mặc định.

2-Truy cập worksheet
2-Truy cập worksheet

Ẩn worksheet

Các ví dụ sau đây cho thấy cách ẩn và cách hiện một worksheet:

3-Ẩn worksheet
3-Ẩn worksheet

Nếu bạn không muốn người dùng truy cập vào worksheet thì có thể đặt ở chế độ “hide” (ẩn). Có nghĩa nó chỉ có thể được hiển thị bằng mã.

4-Ẩn worksheet
4-Ẩn worksheet

Bảo vệ Worksheet

Một ví dụ khác nếu bạn muốn bảo vệ Worksheet:

5-bảo mật wooksheet
5-bảo mật wooksheet

Subscript nằm ngoài phạm vi

Khi bạn sử dụng worksheet có thể gặp lỗi.

Điều này có nghĩa bạn đang cố gắng truy cập một worksheet không tồn tại. Lý do có thể:

  1. Worksheet được cung cấp cho bộ sưu tập worksheets viết sai chính tả.
  2. Tên worksheet đã thay đổi.
  3. Worksheet đã bị xóa.
  4. Chỉ số này quá lớn, ví dụ : bạn sử dụng worksheets(5) nhưng chỉ có 4 worksheets.
  5. Workbook đang sử dụng sai, ví dụ Workbooks ( “book1.xlsx” ) .Worksheets (“Sheet1”) thay vì Workbooks ( “book3.xlsx” ) .Worksheets (“Sheet1”) .

Nếu gặp sự cố này hãy sử dụng  Loop Through the worksheets để hiển thị tên của tất cả các worksheet trong bộ sưu tập.

Sử dụng Index (chỉ mục) để truy cập worksheet

Ngoài cách sử dụng tên để truy cập worksheet thì có thể sử dụng Index. Index đề cập đến vị trí tab worksheet trong workbook. Ví dụ đoạn mã sau đây:

6- sử dụng index để cấp quyền wooksheet
6- sử dụng index để cấp quyền wooksheet

Trong ví dụ trên sử dụng Debug.Print để xuất ra Immediate window. Để xem cửa sổ này chọn View→ Immediate window (hoặc ctrl + G).

Sử dụng code name của worksheet

Phương pháp tốt nhất để truy cập worksheet là sử dụng code name (tên mã). 

Thay đổi tên worksheet không làm thay đổi code name  có nghĩa là vẫn có thể tham chiếu worksheet theo tên mã. 

Trong hình ảnh, bạn có thể thấy rằng code name là tên ngoài ngoặc và tên worksheet nằm trong ngoặc.

7-code-name

Bạn có thể thay đổi cả tên worksheet và code name trong cửa sổ thuộc tính của worksheet (xem hình ảnh bên dưới).

8-code-name-properties1
8-code-name-properties1

Nếu mã của bạn tham chiếu đến code name thì người dùng có thể thay đổi tên của worksheet và nó sẽ không ảnh hưởng đến mã của bạn. Trong ví dụ dưới đây, chúng tôi tham chiếu trực tiếp worksheet bằng cách sử dụng code name.

9-code-name
9-code-name

Điều này làm cho mã dễ đọc và an toan toàn ngay cả thay đổi tên workbook.

Code name trong các workbook khác

10 code name

Có một nhược điểm khi sử dụng code name là nó chỉ tham chiếu đến các worksheet trong workbook mà chứa mã như Thisworkbook.

Tuy nhiên chúng ta có thể sử dụng một hàm đơn giản để tìm code name của một worksheet trong workbook khác.

Ví dụ  trên có nghĩa khi người dùng thay đổi code name của worksheet thì mã cũng không bị ảnh hưởng.

Ngoài ra chúng ta có thể sử dụng VBAProject của workbook để làm điều tương tự. Ví dụ:

11-code-name
11-code-name

Tóm tắt code name

1.Code name của worksheet có thể được sử dụng trực tiếp trong mã, ví dụ: Sheet1.Range.

  1. Code name vẫn hoạt động nếu tên worksheet thay đổi. 
  2. Code name chỉ được sử dụng cho các worksheet trong cùng một workbook với mã.
  3. Bất cứ khi nào hiển thị ThisWorkbook.Worksheets (“sheetname”),thì chúng ta có thể thay nó bằng code name của worksheet.
  4. Có thể sử dụng chức năng SheetFromCodeName để lấy codename của worksheet từ một workbook khác.

Sử dụng Activesheet

Đối tượng Activesheet ám chỉ worksheet đang hoạt động. Sử dụng Activesheet nếu xác định chắc chắn bạn muốn sử dụng worksheet đang hoạt động hiện tại.

 Nếu không phải thì xác định worksheet mà bạn muốn sử dụng. 

12-active
12-active

Nếu muốn sử dụng phương pháp Range mà không đề cập đến worksheet thì activesheet sẽ mặc định.

Khai báo đối tượng worksheet

Khai báo đối tượng worksheet rất hữu ích giúp mã của bạn gọn gàng và dễ đọc hơn.

Ví dụ dưới đây sẽ hiển thị mã cập nhật các vùng dữ liệu. Sub đầu tiên không khai báo đối tượng worksheet, các sub còn lại khai báo đối tượng worksheet nên mã nhìn rõ ràng hơn.

13 object

14-object
14-object

Chúng ta cũng có thể sử dụng lệnh With với đối tượng worksheet.

15-object
15-object

Truy cập worksheet trong Nutshell

Để không gặp rắc rối trong việc truy cập worksheet, phần này sẽ tóm gọn một cách dễ hiểu.

1.Nếu muốn sử dụng bất kỳ worksheet nào hiện đang hoạt động thì sử dụng Activesheet.

ActiveSheet.Range("A1") = 55
  1. Nếu muốn worksheet nằm trong cùng một workbook làm việc với mã thì sử dụng code name.
Sheet1.Range("A1") = 55

3.Nếu worksheet nằm trong một workbook làm việc khác thì đầu tiên mở workbook và sau đó truy cập worksheet.

' Get workbook
Dim wk As Workbook
Set wk = Workbooks.Open("C:DocsAccounts.xlsx", ReadOnly:=True)

' Then get worksheet
Dim sh As Worksheet
Set sh = wk.Worksheets("Sheet1")

Nếu muốn tránh người dùng thay đổi code name của worksheet thì sử dụng chức năng SheetFromCodename từ code name.

' Get workbook
Dim wk As Workbook
Set wk = Workbooks.Open("C:DocsAccounts.xlsx", ReadOnly:=True)

' Then get worksheet
Dim sh As Worksheet
Set sh = SheetFromCodeName("sheetcodename",wk)

Thêm worksheet

Các ví dụ dưới đây sẽ hướng dẫn cách thêm một worksheet mới vào workbook . Nếu không cung cấp bất kỳ đối số nào cho hàm Add thì worksheet mới sẽ được đặt trước activesheet.

Khi thêm một worksheet nó sẽ được tạo với tên mặc định như “ Worksheet4”. Nếu bạn muốn thay đổi tên thì thực hiện bằng cách sử dụng thuộc tính Name.

Ví dụ sau thêm một worksheet mới và đổi tên thành “Accounts”. Nếu một worksheet có tên “Accounts” đã tồn tại thì sẽ xuất hiện lỗi.


Public Sub AddSheet()

    Dim sht As Worksheet

    ' Adds new sheet before active sheet
    Set sht = ThisWorkbook.Worksheets.Add
    ' Set the name of sheet
    sht.Name = "Accounts"

    ' Adds 3 new sheets before active sheet
    ThisWorkbook.Worksheets.Add Count:=3

End Sub

Trong ví dụ trước, chúng ta thêm worksheet liên quan đến worksheet đang hoạt động (activesheet) . Chúng ta cũng có thể chỉ định vị trí đặt worksheet mới . Để làm điều này bạn chỉ định worksheet nào chèn trước, worksheet nào chèn sau, ví dụ:

Public Sub AddSheetFirstLast()

    Dim shtNew As Worksheet
    Dim shtFirst As Worksheet, shtLast As Worksheet

    With ThisWorkbook

        Set shtFirst = .Worksheets(1)
        Set shtLast = .Worksheets(.Worksheets.Count)

        ' Adds new sheet to first position in the workbook
        Set shtNew = Worksheets.Add(Before:=shtFirst)
        shtNew.Name = "FirstSheet"

        ' Adds new sheet to last position in the workbook
        Set shtNew = Worksheets.Add(After:=shtLast)
        shtNew.Name = "LastSheet"

    End With

Xóa worksheet

Để xóa một worksheet chỉ cần sử dụng lệnh gọi Delete

Dim sh As Worksheet
Set sh = ThisWorkbook.Worksheets("Sheet12")
sh.Delete

Excel sẽ hiển thị thông báo cảnh báo khi bạn xóa worksheet. Nếu muốn ẩn thông báo sử dụng mã bên dưới:

Application.DisplayAlerts = False
sh.Delete
Application.DisplayAlerts = True

Có hai vấn đề chú ý khi xóa worksheet. Nếu bạn cố gắng truy cập worksheet sau khi xóa sẽ xuất hiện lỗi “Subscript out of Range” mà chúng ta đã thấy trong phần trên.

Dim sh As Worksheet
Set sh = ThisWorkbook.Worksheets("Sheet2")
sh.Delete

' Dòng này sẽ phát sinh lỗi "Subscript out of Range" vì "Sheet2" không tồn tại
Set sh = ThisWorkbook.Worksheets("Sheet2")

Vấn đề thứ 2 là khi bán một biến cho đối tượng worksheet, nếu cố gắng sử dụng biến này sau khi worksheet bị xóa thì sẽ gặp lỗi Automation.

Run-Time error -21147221080 (800401a8′) Automation Error

Nếu sử dụng code name thay cho tên của worksheet thì điều này sẽ khiến excel gặp sự cố chứ không phải lỗi Automation. Ví dụ:

sh.Delete

' Dòng này sẽ ra lỗi Automation error
Debug.Assert sh.Name

Nếu bạn gán biến cho worksheet thì sẽ hoạt động ổn hơn:

sh.Delete

' Gán biến sh tới "sheet3"
Set sh = Worksheets("sheet3")

' Dòng này sẽ không có lỗi
Debug.Assert sh.Name

Lặp qua các worksheet

Workbook bao gồm các worksheet, chúng ta có thể xem từng worksheet trong bộ sưu tập worksheets bằng cách sử dụng For each Loop hoặc For Loop.

Ví dụ sau đây sử dụng For Loop:


Public Sub LoopForEach()

    ' Writes "Hello World" into cell A1 for each worksheet
    Dim sht As Worksheet
    For Each sht In ThisWorkbook.Worksheets
         sht.Range("A1") = "Hello World"
    Next sht 

End Sub

Ví dụ tiếp theo sử dụng For Loop:


Public Sub LoopFor()
    
    ' Writes "Hello World" into cell A1 for each worksheet
    Dim i As Long
    For i = 1 To ThisWorkbook.Worksheets.Count
         ThisWorkbook.Worksheets(i).Range("A1") = "Hello World"
    Next sht

End Sub

Đây là cách truy cập tất cả các workbook đang mở và truy cập tất cả các worksheet trong Thisworkbook. Tiếp theo truy cập tất cả các worksheet trong tất cả các workbook đang mở.

Lưu ý: nếu sử dụng mã này để ghi vào worksheet thì lưu mọi thứ trước vì có thể dữ liệu không chính xác có thể ghi vào worksheet.


Public Sub AllSheetNames()

    ' Prints the workbook and sheet names for
    ' all sheets in open workbooks
    Dim wrk As Workbook
    Dim sht As Worksheet
    For Each wrk In Workbooks
        For Each sht In wrk.Worksheets
            Debug.Print wrk.Name + ":" + sht.Name
        Next sht
    Next wrk

End Sub

Sử dụng bộ sưu tập Sheets

Workbook có một bộ sưu tập khác tương tự như worksheets gọi là sheets. Điều này đôi khi gây ra sự nhầm lẫn cho người dùng. Để phân biệt trước tiên cần biết về một sheet biểu đồ như sau:

1.Tạo biểu đồ trên bất kỳ sheet nào.

  1. Nhấp chuột phải vào biểu đồ và Move.
  2. Chọn tùy chọn đầu tiên là “ New sheet ” và click ok.
  • Bộ sưu tập worksheets đề cập đến tất cả các worksheet trong một sổ làm việc. Nó không bao gồm các biểu đồ.
  • Bộ sưu tập Sheets đề cập đến tất cả các worksheet thuộc workbook nhưng bao gồm các sheets của biểu đồ.

Có hai ví dụ mã bên dưới. Đầu tiên đi qua tất cả các sheets trong workbook, xuất tên và loại của sheet. Ví dụ thứ hai làm tương tự với bộ sưu tập Worksheets. 

Để thử ví dụ cần thêm sheet biểu đồ vào workbook để thấy sự khác biệt :


Public Sub AllSheetNames()

    ' Prints the workbook and sheet names for
    ' all sheets in open workbooks
    Dim wrk As Workbook
    Dim sht As Worksheet
    For Each wrk In Workbooks
        For Each sht In wrk.Worksheets
            Debug.Print wrk.Name + ":" + sht.Name
        Next sht
    Next wrk

End Sub

Nếu không có sheet biểu đồ thì việc sử dụng bộ sưu tập sheets cũng giống như sử dụng bộ sưu tập worksheets.

 


·
·
·
Bài viết khác của cùng tác giả

  • Tất cả
  • Python
  • SQL
  • VBA

3.000.000VND 1.800.000VND
PYXL101
Xem chi tiết

3.000.000VND 1.600.000VND
SQL100
Xem chi tiết

499.000VND 199.000VND
PY100
Xem chi tiết

Bài viết liên quan

Cách tạo menu chuyển động đẹp trong Excel sử dụng VBA

Cách lọc lấy số trong ô lẫn cả số và chữ hoặc ngược lại

Cách xóa nội dung trùng lặp trong một ô của Excel

Tải về miễn phí File Excel quản lý kho bằng excel VBA

Xác định ngày bất kỳ là thứ mấy trong tuần

Hướng dẫn cách đếm ký tự, ký tự đặc biệt trong một ô hoặc một dải ô trong Excel


Khóa học liên quan

Khóa học Tự động hóa Excel với lập trình VBA cho người mới bắt đầu
Chi tiết khóa học

Khóa học Lập trình VBA nâng cao trong Excel
Chi tiết khóa học

  

© Học Gia Sư Kế Toán. All rights reserved.

My Lê

My Lê

My Lê tên thật là Mê Ly đã có 10 năm kinh nghiệm trong ngành kế toán.

Next Post
MÃ VBA ĐỂ LƯU NHƯ MỘT PHIÊN BẢN MỚI NẾU TỆP ĐÃ CÓ SẴN

MÃ VBA ĐỂ LƯU NHƯ MỘT PHIÊN BẢN MỚI NẾU TỆP ĐÃ CÓ SẴN

Recommended

Cách đặt bảo vệ và hủy bảo vệ trang trong Excel khi có hoặc không có mật khẩu

Cách đặt bảo vệ và hủy bảo vệ trang trong Excel khi có hoặc không có mật khẩu

1 tháng ago
Tùy chỉnh biểu đồ Excel: thêm tiêu đề, trục, chú thích, nhãn dữ liệu và nhiều thứ khác

Tùy chỉnh biểu đồ Excel: thêm tiêu đề, trục, chú thích, nhãn dữ liệu và nhiều thứ khác

1 tháng ago

Tin mới nhất

    Fanpage

    • Liên hệ
    • Giới thiệu
    • Đặt quảng cáo
    Liên hệ chúng tôi qua email : hocgiasuketoan.com@gmail.com

    © 2022 Hocgiasuketoan Premium magazine by Hocgiasuketoan

    No Result
    View All Result
    • Trang chủ
    • Giới thiệu
      • Học gia sư kế toán tại Hà Nội
    • Excel
    • Kế toán
    • Kinh nghỉệm
    • DAX
    • VBA
    • Văn bản
    • Access
    • Liên hệ
      • Chính sách bảo mật

    © 2022 Hocgiasuketoan Premium magazine by Hocgiasuketoan

    Welcome Back!

    Login to your account below

    Forgotten Password?

    Retrieve your password

    Please enter your username or email address to reset your password.

    Log In