Thẻ: khóa học vba

doi-tuong-worksheet-trong-excel-VBA.jpg

Đối tượng Worksheet trong Excel VBA

Written by dtnguyen on . Posted in VBA. Leave a Comment

Đối tượng Worksheet trong Excel VBA là đối tượng đại diện cho một Worksheet trong lập trình VBA. Các thao tác với Worksheet khi các bạn làm việc với Excel (thủ công, bằng tay) như di chuyển sheet, copy sheet, đổi tên sheet, xóa sheet, … đều có thể thực hiện tự động bằng code VBA.

Đối tượng Worksheets trong Excel VBA là một tập hợp các Worksheet trong một Workbook

Các loại Sheet trong một tài liệu Excel

Worksheet chúng ta đề cập đến trong bài viết này, thực ra chỉ là một loại Sheets trong Workbook Excel. Đã bao giờ các bạn gặp các loại Sheets khác chưa?

doi-tuong-worksheet-trong-excel-vba-01

Các bạn có thể nhìn thấy ở trong hình trên, trong cửa sổ Project – VBAProject của VBE, trong Book1.xlsm, chúng ta có thấy Sheet1 là một Worksheet, đối tượng mà chúng ta vẫn quen thuộc. Số lượng Worksheets trong một Workbook được thể hiện qua câu lệnh:

Debug.Print ThisWorkbook.Worksheets.Count

và kết quả là trong tài liệu Excel này, chúng ta có 1 Worksheet. Tuy nhiên, ở câu lệnh thứ 2:

Debug.Print ThisWorkbook.Sheets.Count

kết quả của câu lệnh này lại là 5. Tại sao lại như vậy? Ok, chúng ta còn có thể nhìn thấy một đối tượng nữa ở trong cửa sổ Project, đó là đối tượng Chart2 và nếu bạn nào đã từng bấm nhầm phím tắt ALT + F11 thì, bạn sẽ có thể tạo ra được 1 sheet mà chỉ có biểu đồ, không có gì khác ngoài biểu đồ, chúng ta có thể gọi là Chart Sheet, và Chart Sheet này cũng có mặt trong các thành viên của đối tượng Sheets ở trong Thisworkbook.Sheets. Tuy nhiên, như vậy mới có 2, vậy con số 5 ở đây là ở đâu ra?

doi-tuong-worksheet-trong-excel-vba

Như các bạn đã thấy ở hình trên, một số sheets có tên Macro ở đầu, những sheets này đã được tạo ra như thế nào? Những sheets này có thể được tạo ra khi bạn bấm phím tắt CTRL + F11, và loại sheet này còn không được thể hiện trong VBA Project. Những Sheets này, được gọi là Macro Sheet, chỉ tồn tại với lý do tương thích ngược với những file Excel cũ, những file Excel vẫn sử dụng Excel Macro 4.0 (Ngôn ngữ VBA ra đời ở Excel phiên bản 5).

Như vậy, các bạn đã biết rằng, chúng ta có nhiều hơn một loại sheet trong một Workbook, tuy nhiên, trong bài này, chúng ta chỉ tập trung vào đối tượng Worksheet mà thôi.

Cách tham chiếu tới một Worksheet trong Excel VBA

Trước khi đi làm việc được với một Worksheet, thì chúng ta cần tìm hiểu cách tham chiếu tới Worksheet đó.

Như ở bài Hệ thống các đối tượng trong Excel VBA thì các bạn đã biết, để tham chiếu tới một Worksheet, chúng ta có nhiều cách, tham chiếu từ Application, hay từ Workbook thì còn tùy vào trường hợp của chúng ta sử dụng. Nếu các bạn làm việc với nhiều Workbooks cùng lúc, thì chúng ta cần tham chiếu tới Worksheet từ “tầng Workbook”. Ví dụ, chúng ta muốn tham chiếu tới worksheet có tên “Data” ở trong Workbook đang chạy code:

ThisWorkbook.Worksheets("Data")

Nếu chúng ta chỉ làm việc trong phạm vi 1 workbook, thì bạn có thể tham chiếu Worksheets “Data” như sau:

Worksheets("Data")

Tham chiếu tới Worksheet theo tên

Để tham chiếu tới một Worksheet theo tên trong Excel VBA, chúng ta có thể viết câu lệnh như sau:

Set dataSheet = Worksheets("Data")

vì Worksheet cũng là Sheet, nên chúng ta có thể tham chiếu tới Sheet Data như sau:

Set dataSheet = Sheets("Data")

Tham chiếu tới Worksheet theo thứ tự (index)

Để tham chiếu tới một Worksheet theo thứ tự trên Sheetbar trong Excel VBA, chúng ta có thể viết câu lệnh như sau:

Set dataSheet = Worksheets(1)

Lưu ý Trong hình sau, chúng ta muốn tham chiếu tới Worksheet có tên là Sheet1 theo thứ tự trên Sheetbar của Excel, thứ tự của đối tượng này là 1 chứ không phải là 5, bởi vì 4 đối tượng trước nó không thuộc vào loại Worksheet.

Nếu chúng ta tham chiếu bằng Sheets thì code sẽ là:

Set dataSheet = Sheets(5)

doi-tuong-worksheet-trong-excel-vba

Tham chiếu tới Worksheet theo code name trong VBA

Đối với hai cách tham chiếu tới Worksheet ở hai phần trước, sẽ có hạn chế trong quá trình sử dụng đó là:

  • Người dùng có thể đổi tên Worksheet
  • Người dùng có thể đổi vị trí Worksheet trên Sheetbar

Điều này đưa chúng ta đến một cách thứ ba để có thể tham chiếu đến một Worksheet trong VBA: theo code name hay (name) trong cửa sổ Properties ở trong VBA Editor:

doi-tuong-worksheet-trong-excel-vba

Trong hình trên: trên SheetBar, Worksheet của chúng ta tên là Data, chúng ta sẽ tham chiếu tới Worksheet này như thế nào:

  • Trong cửa sổ Project – VBAProject, chúng ta thấy đối tượng dataSheet(Data) – tên Sheet được để trong ngoặc đơn
  • Trong cửa sổ Properties – dataSheet: để đổi tên tham chiếu trong VBA, chúng ta có thể đổi trong cửa sổ này, ở mục (Name)

Một số phương thức của đối tượng Worksheet trong VBA Excel

Phương thức Activate

Phương thức Activate của đối tượng Worksheet trong VBA dùng để kích hoạt một Worksheet, phương thức này tương đương với hành động chúng ta dùng chuột click vào tên Sheet ở trên SheetBar.

Kích hoạt Worksheet có tên Data, cú pháp một trong những cách sau:

Worksheets("Data").Activate
Sheets("Data").Activate
Worksheets(1).Activate
Sheets(5).Activate
dataSheet.Activate

Tư vấn của Thanh: trừ khi bạn biết chính xác bạn đang làm gì, thì bạn mới nên sử dụng phương thức này của đối tượng Worksheet, bởi vì phương thức này có thể dẫn đến rất nhiều lỗi trong code VBA của bạn.

Phương thức Calculate

Phương thức Calculate của đối tượng Worksheet trong VBA khi được kích hoạt sẽ giúp tính toán lại các công thức trên Sheet này.

Worksheets("Data").Calculate

Phương thức Copy

Phương thức Copy của đối tượng Worksheet trong VBA giúp chúng ta sao chép một Worksheet. Việc sao chép này có thể được thực hiện trong một Workbook, hoặc sang một Workbook khác.

Worksheets("_worksheet_cần_copy").Copy Before:=_Worksheet_A_
Worksheets("_worksheet_cần_copy").Copy After:=_Worksheet_A_

Một số ví dụ:
Sao chép Sheet “Data” ra phía trước của Sheet có tên là “Analysis” trong cùng Workbook:

Worksheets("Data").Copy Before:=Worksheets("Analysis")
' hoặc
Sheets("Data").Copy Before:=Sheets("Analysis")

Sao chép Sheet “Data” trong Workbook “Thanh.xlsm” ra phía sau Sheet “Big” trong Workbook “Master.xlsm”:

Workbooks("Thanh.xlsm").Worksheets("Data").Copy After:=Workbooks("Master.xlsm").Worksheets("Big")

Phương thức Delete

Phương thức Delete của đối tượng Worksheet trong VBA được sử dụng để xóa một hay một mảng các Sheets.

Lưu ý: Thao tác xóa Sheets sử dụng code VBA sẽ không thể hoàn tác (Undo) được, nếu bạn thực hành trên file Excel của bạn, hãy đảm bảo sao lưu lại các file Excel trước khi thực hiện code.

Để xóa Sheets có tên “Big”, chúng ta có thể viết đoạn code như sau:

Sheets("Big").Delete

Sau khi thực hiện code, bạn sẽ nhận được thông báo của Excel, bấm Delete nếu bạn muốn tiếp tục xóa.

thong-bao-xac-nhan-xoa-sheets-vba-1.jpg

Để xóa đồng thời 2 sheets có tên là “Big” và “Analysis”, không hiện hộp thoại thông báo xóa Sheets, chúng ta có thể sử dụng đoạn code sau đây:

Application.DisplayAlerts = False
Sheets(Array("Big", "Analysis")).Delete
Application.DisplayAlerts = True

chi tiết về các sử dụng Application.DisplayAlerts hãy tham khảo bài viết Đối tượng Application trong Excel VBA

Phương thức Move

Phương thức Move của đối tượng Worksheet trong VBA giúp chúng ta di chuyển một Worksheet.

Để di chuyển Sheet “Big” ra phía trước Sheet “Data” và Sheet “Analysis” ra phía sau Sheet “Data”, ta có thể dùng code sau đây:

 Sheets("Big").Move Before:=Sheets("Data")
Sheets("Analysis").Move After:=Sheets("Data")

Để di chuyển cùng lúc đồng thời 2 sheets “Big”, “Data” ra phía trước Sheet “Analysis”, chúng ta có thể dùng code sau đây:

Sheets(Array("Big","Data")).Move Before:=Sheets("Analysis")

Thuộc tính Name

Thuộc tính Name của đối tượng Worksheet trong VBA giúp bạn có được tên trên SheetBar của một Worksheet.

Ví dụ, lấy tên của Worksheet ở vị trí thứ nhất trên SheetBar:

Debug.print Worksheets(1).Name

Ngoài ra, chúng ta có thể đổi tên Worksheet ở vị trí thứ nhất trên SheetBar như sau:

Worksheets(1).Name = "Very Big Data"

Thuộc tính Visible

Video liên quan tới phần này:

YouTube video

Thuộc tính Visible của đối tượng Worksheet trong VBA giúp bạn thiết lập sự ẩn / hiện của một Worksheet.

Để hiện Worksheet “Data”:

Worksheets("Data").Visible = xlSheetVisible

Để ẩn Worksheet “Data”, nhưng có thể cho hiện lại ở trên SheetBar:

Worksheets("Data").Visible = xlSheetHidden

Để ẩn Worksheet “Data”, nhưng không cho phép hiện lại bằng thao tác trên SheetBar:

Worksheets("Data").Visible = xlSheetVeryHidden

Như vậy, qua bài viết này, Thanh đã giới thiệu với các bạn một số phương thức và thuộc tính của đối tượng Worksheet trong Excel VBA. Đối tượng Worksheet trong Excel VBA còn rất nhiều phương thức, thuộc tính và thành phần nữa, để kết hợp nhuần nhuyễn và hiểu rõ cách kết hợp tốt nhất những thành phần này cho ứng dụng VBA của bạn, hãy tham khảo khóa học VBA của Thanh:

Khóa học VBA cơ bản:

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

Toàn bộ các khóa học lập trình VBA:

Tham khảo toàn bộ các khóa học lập trình VBA

doi-tuong-workbook-trong-vba-excel

Đối tượng Workbook trong Excel VBA

Written by dtnguyen on . Posted in VBA. Leave a Comment

Đối tượng Workbook trong Excel VBA là đối tượng đại diện cho một file Excel và là đối tượng chúng ta sẽ làm việc cùng khi muốn tự động hóa các thao tác liên quan đến Workbook như tạo Workbook mới, lưu Workbook, mở Workbook, đóng Workbook …

Cách tham chiếu đến đối tượng Workbook

Trong trường hợp bạn đang làm việc với nhiều Workbook trong một Instance của phần mềm Excel, bạn cần biết cách để tham chiếu chính xác đến Workbook bạn cần. Chúng ta có một số đối tượng để phân biệt như sau

Đối tượng ThisWorkbook

Đối tượng ThisWorkbook là một đối tượng thuộc loại Workbook, khi bạn gõ đoạn code sau vào cửa sổ Immediate (View > Immediate Window) thì cũng sẽ nhận được tên kiểu là Workbook:

?typename(ThisWorkbook)

ThisWorkbook trong trường hợp này là chính Workbook mà code VBA/Macro của bạn đang được thực thi.

Đối tượng ActiveWorkbook

Đối tượng ActiveWorkbook là một đối tượng thuộc loại Workbook, khi bạn gõ đoạn code sau vào cửa sổ Immediate (View > Immediate Window) thì cũng nhận được tên kiểu là Workbook:

?typename(ActiveWorkbook)

ActiveWorkbook là Workbook đang được người dùng chọn bằng chuột, đang ở phía trước các Workbook khác, đang có focus, hoặc là Workbook được gọi bởi phương thức Activate của đối tượng Workbook:

Tham chiếu tới Workbook bằng tên file Excel:

Set wb = Application.Workbooks("Book1.xlsx").Activate

Tham chiếu tới Workbook số thứ tự khi mở ra:

Set wb = Application.Workbooks(2).Activate

Và tất nhiên, bạn cũng có thể gọi Activate trên đối tượng ThisWorkbook

Set wb = ThisWorkbook.Activate

Tham chiếu tới Workbook trong VBA Excel

Vì Workbook là một đối tượng, nên khi khai báo biến và khởi tạo giá trị, chúng ta phải dùng từ khóa Set

Các cách để tham chiếu tới Workbook như sau:

' Tham chiếu tới ThisWorkbook 
Set wb1 = ThisWorkbook
' Tham chiếu tới ActiveWorkbook
 Set wb2 = ActiveWorkbook
' Mở Workbook tại đường dẫn và tham chiếu tới Workbook đó
 Set wb3 = Workbooks.Open("_đường_dẫn_tới_file_excel")
' Tham chiếu tới Workbook bằng số thứ tự
 Set wb4 = Workbooks(3)
' Tham chiếu tới Workbook bằng tên file Excel
 Set wb5 = Workbooks("Book1.xlsx")
' Tham chiếu tới Workbook mới được tạo ra
 Set wb6 = Workbooks.Add

Cách đóng Workbook trong VBA Excel

Sau khi đã tham chiếu và làm việc với Workbook, chúng ta có thể đóng Workbook bằng Code VBA. Để đóng Workbook, chúng ta gọi phương thức Close trên biến Workbook chúng ta đã tham chiếu tới. Ví dụ để đóng một Workbook và lưu lại những thay đổi đã được thực hiện trên Workbook đó, chúng ta code như sau:

Sub close_wb()
   Dim wb As Workbook
   Set wb = Workbooks.Open("C:\Book1.xlsx")
   wb.Close Savechanges:=True
End Sub

Để đóng một Workbook và không lưu bất kỳ thay đổi nào, bạn có thể dùng đoạn code như sau:

Sub close_wb()
   Dim wb As Workbook
   Set wb = Workbooks.Open("C:\Book1.xlsx")
   wb.Close Savechanges:=False
End Sub

Như vậy, với việc đóng Workbook, thì chúng ta có thể sử dụng thêm tham số Savechanges để có thể thực hiện lưu hoặc không lưu những thay đổi trên Workbook.

Cách mở một Workbooks trong VBA Excel

Để mở một Workbook trong VBA Excel, chúng ta có thể sử dụng phương thức Open như sau

Sub open_wb()
   Dim wb As Workbook
   Set wb = Workbooks.Open("C:\Book1.xlsx")
   ' Làm việc tiếp với đối tượng wb
End Sub

 Lưu Workbook với code VBA Excel

Để lưu một Workbook trong code VBA Excel thì chúng ta có một số phương thức hay sử dụng như sau

Lưu Workbook sử dụng phương thức save

Trong trường hợp bạn muốn lưu Workbook lại thường xuyên để đề phòng cho trường hợp mất điện, lưu những thay đổi trong khi làm việc, thì bạn có thể gọi phương thức save trên đối tượng Workbook như sau

Sub save_wb()
   Dim wb As Workbook
   Set wb = Workbooks.Open("C:\Book1.xlsx")
   wb.Save
End Sub

Lưu Workbook sử dụng phương thức SaveAs

Để lưu Workbook với một tên khác, vào một đường dẫn khác, thì chúng ta có thể gọi phương thức SaveAs trên đối tượng Workbook như sau:

Sub save_wb()
   Dim wb As Workbook
   Set wb = Workbooks.Open("C:\Book1.xlsx")
   wb.SaveAs Filename:="_đường_dẫn_\Tên_file.xlsx"
End Sub

Nếu kết hợp với việc tách dữ liệu từ một File ra nhiều file, thì trong khi chạy vòng lặp để tách dữ liệu, thì bạn sẽ cần gọi SaveAs và thay đổi đường dẫn tùy theo file Excel đang được tách ra. Nhưng để xử lý trường hợp này, bạn nên dùng phương thức SaveCopyAs như hướng dẫn ở đoạn ngay sau đây.

Lưu Workbook sử dụng phương thức SaveCopyAs

Một ứng dụng lý tưởng để sử dụng SaveCopyAs chúng ta có thể nghĩ tới là dùng để sao lưu dữ liệu, ví dụ bạn sẽ chạy đoạn code để sao lưu dữ liệu vào một thư mục và đặt tên cho bản sao lưu theo thời gian chẳng hạn

Sub save_wb()
   Dim wb As Workbook
   Set wb = Workbooks.Open("C:\Book1.xlsx")
   wb.SaveCopyAs Filename:="_đường_dẫn_\" & Format(Now,"hh_mm_ss") & "Tên_file.xlsx"
End Sub

Hi vọng là qua bài viết này Thanh đã chia sẻ tới các bạn những thao tác hay dùng nhất với đối tượng Workbook trong VBA Excel. Một phần lớn những kiến thức này sẽ trở nên hữu dụng khi bạn kết hợp với các kiến thức trong khóa học VBA cùng Thanh tại địa chỉ:

Khóa học VBA cơ bản:

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

Toàn bộ các khóa học lập trình VBA:

Tham khảo toàn bộ các khóa học lập trình VBA

 

doi-tuong-application-trong-excel-vba

Đối tượng Application trong Excel VBA

Written by dtnguyen on . Posted in VBA. Leave a Comment

Đối tượng Application trong Excel VBA là đối tượng đại diện cho phần mềm Excel của Microsoft trong ngôn ngữ lập trình VBA, các thiết lập liên quan đến phần mềm, cách tính toán, các chế độ tùy chọn đều có thể được thiết lập dựa trên các phương thức và thuộc tính của đối tượng Application này.

Trong khuôn khổ bài viết này, Thanh sẽ giới thiệu với các bạn những đoạn code VBA hay được sử dụng nhất liên quan đến đối tượng Application.

DisplayAlerts – cách ẩn thông báo khi dùng code VBA để xóa Sheet

Giả sử bạn muốn xóa Sheet ở trong Excel, hãy tưởng tượng xem, nếu bạn “nhỡ” chạy đoạn code VBA sau đây và phải bấm thông báo xác nhận xóa Sheets 10 lần? Làm thế nào để khắc phục được vấn đề này?

Sub test_xoa_sheet()
    For i = 1 To 10
        Sheets(i).Delete
    Next i
End Sub

Đây là thông báo mà bạn cần bấm để xác nhận (10 lần):

thong-bao-xac-nhan-xoa-sheets-vba

Để không phải bấm thông báo này 10 lần như trong tình huống phía trên, bạn sẽ cần sử dụng tới một thuộc tính của đối tượng Application trong Excel đó là DisplayAlerts cách sử dụng sẽ như đoạn code sau đây:

Sub test_xoa_sheet()
    Application.DisplayAlerts = False
    For i = 1 To 10
        Sheets(i).Delete
    Next i
    Application.DisplayAlerts = True
End Sub

Trong khi sử dụng vòng lặp để xóa các Sheets có vị trí từ 1 tới 10 thì chúng ta sẽ thiết lập thuộc tính DisplayAlert của đối tượng Application là False, sau khi xóa xong thì chúng ta nên trả lại giá trị cho thuộc tính này là True.

Thuộc tính Path của đối tượng Application

Thuộc tính Path của đối tượng Application được dùng để lấy đường dẫn tới thư mục chứa ứng dụng (Excel)

Sub test_path()
    Debug.Print Application.Path
End Sub
' Kết quả trên hệ điều hành Windows: 
'  C:\Program Files\Microsoft Office\root\Office16
' Kết quả trên hệ điều hành MacOS:
'  /Applications/Microsoft Excel.app

Như các bạn đã thấy, cùng một câu lệnh, nhưng nếu chúng ta chạy trên mỗi máy khác nhau, sẽ có kết quả khác nhau, các bạn để ý nếu các bạn muốn viết code VBA mà có thể chạy được cho cả hệ điều hành Windows, cả hệ điều hành MacOs thì chúng ta cần lưu ý tới đường dẫn, và đặc biệt là ký tự ngăn cách giữa các thư mục, bởi vì trên Windows, ký tự ngăn cách là dấu \ còn trên MacOs, thì ký tự ngăn cách là dấu / điều này sẽ dẫn chúng ta đến phần tiếp theo khi nói về đường dẫn để mở File

Thuộc tính PathSeparator của đối tượng Application trong VBA Excel

Chúng ta có một đoạn code đơn giản như sau để mở một Workbook:

Sub test_path_separator()
    Dim wb As Workbook
    Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & "Book2.xlsx")
End Sub

Mục đích của chúng ta là mở một file Excel tên là “Book2.xlsx” trong cùng thư mục với Workbook đang chạy code VBA. Trong đoạn code trên, phần "\" có thể gây ra lỗi bởi vì \ là ký tự ngăn cách đường dẫn trên Windows, còn trên MacOs, thì dùng ký tự /. Vậy nên, để an toàn nhất, chúng ta sẽ sử dụng thuộc tính PathSeparator và sẽ sửa lại đoạn code như sau:

Sub test_path_separator()
    Dim wb As Workbook
    Set wb = Workbooks.Open(ThisWorkbook.Path & Application.PathSeparator & "Book2.xlsx")
End Sub

ScreenUpdating – tăng tốc code VBA Excel đơn giản

Nếu code VBA của bạn thực hiện nhiều thao tác xử lý trên giao diện của Excel, ví dụ như lọc, sắp xếp, thì thuộc tính ScreenUpdating của đối tượng Application sẽ giúp bạn giảm thiểu thời gian thực thi code bằng cách tắt chế độ cập nhật màn hình khi code đang chạy. Tất nhiên sau khi chạy xong code VBA thì chúng ta cần bật lại chế độ này để những thay đổi như công thức, bộ lọc, Pivot Table, … có thể được cập nhật khi chúng ta tương tác. Cách sử dụng ScreenUpdating cũng rất đơn giản như sau để các bạn có thể hình dung và so sánh sự khác biệt:

Sub no_screen_updating()
    For i = 1 To 10000
        Sheets(1).Cells(i, 1) = i
    Next i
    MsgBox "Done"
End Sub
Sub with_screen_updating()
    Application.ScreenUpdating = False
    For i = 1 To 10000
        Sheets(1).Cells(i, 1) = i
    Next i
    Application.ScreenUpdating = True
    MsgBox "Done"
End Sub

Application.Wait – Hẹn giờ chạy code VBA

Có những thao tác chúng ta sẽ cần code VBA chạy chậm lại, hoặc chạy có “khoảng thời gian” nghĩ giữa các vòng lặp, ví dụ như là việc gửi email trong VBA chẳng hạn. Chúng ta có thể sử dụng Application.Wait để làm điều này, hãy tham khảo đoạn code sau đây, MsgBox sẽ hiện lên sau 3s kể từ khi chúng ta chạy Sub:

Sub test_wait()
    Application.Wait Now + TimeValue("00:00:03")
    MsgBox "Sau 3s code se chay"
End Sub

Ngoài ra, để thực thi code VBA vào một thời điểm cụ thể, bạn chỉ cần bỏ Now đi:

Sub test_wait()
    Application.Wait TimeValue("00:00:03")
    MsgBox "Code se chay vao luc 00:00:03"
End Sub

Trên đây là những thuộc tính của đối tượng Application trong lập trình VBA Excel thường hay được sử dụng nhất. Để biết thêm những ứng dụng của các thuộc tính này trong các trường hợp cụ thể như thế nào, bạn hãy tham khảo các khóa học VBA từ Thanh:

Khóa học VBA cơ bản:

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

Toàn bộ các khóa học lập trình VBA:

Tham khảo toàn bộ các khóa học lập trình VBA

he-thong-doi-tuong-trong-excel-vba

Hệ thống các đối tượng trong Excel VBA

Written by dtnguyen on . Posted in VBA. Leave a Comment

Trước khi đi học lập trình VBA hay bất cứ ngôn ngữ nào, một điều rất quan trọng chúng ta nên chú ý đến là hệ thống các đối tượng ở trong ngôn ngữ lập trình đó. Khi hiểu được hệ thống các đối tượng trong VBA, thì bạn sẽ có được hình dung rõ ràng hơn về cấu trúc của code, sẽ làm việc hiệu quả hơn với từng loại đối tượng.

Các thành phần đối tượng trong Excel VBA

Đối tượng Application

Các đối tượng trong Excel VBA có mối quan hệ cha-con. Ở bậc cao nhất, chúng ta có đối tượng Application, trong trường hợp bạn sử dụng VBA để tương tác với Excel, thì ngầm định đối tượng Application này là đối tượng Excel, và sẽ có những thành phần và phương thức tương ứng. Nếu các bạn sử dụng VBA bên trong môi trường phần mềm Microsoft Access, thì Application này sẽ là đối tượng Access. Trong bài viết này, chúng ta nói về các thành phần đối tượng và phương thức, thuộc tính trong Excel VBA.

he-thong-doi-tuong-trong-excel-vba

Application.Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1").value = 234

Ví dụ để có thể tham chiếu tới ô A1, sau đó ghi dữ liệu là số 234 vào ô A1 thì chúng ta có thể viết code như dòng trên, tuy nhiên, khi bạn hiểu được cách phân cấp các đối tượng trong Excel VBA, thì bạn có thể rút ngắn đoạn code VBA trên khá nhiều.

Đối tượng Workbooks

Đối tượng Workbooks là một tập hợp các Workbooks Excel đang được mở, là một thành phần con của đối tượng Application. Khi muốn tham chiếu tới Workbooks, thì bạn sử dụng cú pháp

Application.Workbooks("_tên_workbook_")

Thường thì chúng ta có thể bỏ Application, và chỉ tham chiếu tới Workbooks như sau là đủ:

Workbooks("_tên_workbook_")

Lý do cho việc này là, hiếm khi chúng ta sẽ sử dụng cùng nhiều hơn 1 Instances của Excel, và trong 1 Instance của Excel, thì chúng ta không được phép mở Workbook trùng tên.

Đối tượng Worksheets

Mỗi Workbook, lại có thể chứa nhiều đối tượng Worksheets, và bạn có thể tham chiếu tới một Worksheet có tên là “Sheet1” trong File Excel có tên là “Book1.xlsx” như sau:

Application.Workbooks("Book1.xlsx").Worksheets("Sheet1")

Tuy nhiên, như lý do đã giải thích ở phần trên, chúng ta có thể yên tâm bỏ Application trong dòng code trên đi. Nếu trong trường hợp, code VBA của bạn chỉ làm việc trong File Excel “Book1.xlsx”, thì chúng ta có thể yên tâm bỏ luôn phần Workbooks(“Book1.xlsx”) đi, bởi vì lúc này, trong cùng một Workbook, thì VBA sẽ biết phải tham chiếu tới Sheet nào:

Worksheets("Sheet1")

Nhưng nếu code VBA của bạn làm việc với nhiều Workbooks, có thể trong những Workbooks này, cũng có Sheet tên là “Sheet1”, thì lúc này, bạn cần tham chiếu tới Sheet1 có kèm theo tham chiếu tới Workbook để code VBA không bị lẫn lộn:

Workbooks("Book1.xlsx").Worksheets("Sheet1")

Đối tượng Range

Sau đối tượng Worksheets, chúng ta có thể tham chiếu tới đối tượng Range, cũng tương tự như kiến thức ở phần trước, nếu code VBA chỉ làm việc với nội dung trên 1 bảng tính duy nhất, thì bạn có thể tham chiếu tới Range rất ngắn gọn:

Range("A1")

Nhưng nếu code VBA của bạn sẽ làm việc với nhiều Sheets khác nhau trong cùng một Workbook, thì chắc chắn tham chiếu của chúng ta tới ô A1, cần phải cụ thể hơn:

Worksheets("Sheet1").Range("A1")

Tương tự như vậy khi bạn làm việc với nhiều Workbooks khác nhau, thì chúng ta cần viết đầy đủ “đường dẫn” tới đối tượng Range của chúng ta:

Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1")

Cách tra cứu các thành phần và phương thức với đối tượng trong Excel VBA

Các thành phần và phương thức, cũng như thuộc tính, hay hằng số của mỗi đối tượng rất nhiều, nhưng thật may mắn, ngay phía bên trong trình soạn thảo code VBA của chúng ta đã có chức năng để chúng ta có thể tra cứu được, để mở các cửa sổ này, khi đang ở bên trong trình soạn thảo code VBA, bạn hãy bấm nút F2 trên bàn phím (Nếu không được, hãy thử bấm FN + F2 )

tim-kiem-cac-doi-tuong-va-thanh-phan-trong-vba

Trong cửa sổ này, bạn có thể tìm kiếm các đối tượng trong Excel VBA và tra cứu các thông tin liên quan đến các thuộc tính, phương thức và những hằng số như hình trên.

Các đối tượng và thuộc tính của VBA trong Excel rất nhiều, vậy làm thế nào để bạn có thể tiết kiệm thời gian tra cứu và lập trình tự động hóa được ngay những công việc bạn vẫn mong muốn? Hãy đồng hành cùng Thanh trong các khóa học VBA nâng cao năng suất công việc:

Khóa học VBA cơ bản:

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

Toàn bộ các khóa học lập trình VBA:

Tham khảo toàn bộ các khóa học lập trình VBA

personal-macro-workbook-la-gi

Personal Macro Workbook là gì, cách sử dụng code VBA trong nhiều Files

Written by dtnguyen on . Posted in VBA, VBA nâng cao. Leave a Comment

Trong bài viết này, Thanh sẽ chia sẻ với các bạn cách tạo ra và sử dụng Personal Macro Workbook trong Excel, cách để bạn có thể lưu trữ tập trung và sử dụng nhiều đoạn code VBA trong bất cứ workbook Excel nào.

Bình thường, khi viết một Macro, hay code VBA, thì bạn chỉ có thể sử dụng Macro hay đoạn code VBA này trong File Excel bạn đang sử dụng. Nếu bạn cũng muốn sử dụng trong những Files khác thì sao? Copy một đoạn code qua lại không phải là cách hay, bởi vì khi bạn nâng cấp hoặc thay đổi đoạn code, thì sự thay đổi này sẽ không được phản ánh lại trong các file khác. Cách giải quyết cho vấn đề này là sử dụng Personal Macro Workbook, lưu tất cả những đoạn code VBA hay dùng vào một file tên là Personal.xlsb. Điều này sẽ giúp bạn có thể sử dụng lại tất cả code VBA đã lưu  khi mở bất cứ file Excel nào.

Personal Macro Workbook là gì?

Personal Macro Workbook là một file Excel đặc biệt, được đặt tên là Personal.xlsb trong các phiên bản Excel 2007 – 2019 hoặc Personal.xls trong các phiên bản trước. Bất cứ Macro nào lưu trong file này, bạn đều có thể sử dụng khi bạn bật Excel lên. Mặc định thì file này ẩn, nên bạn sẽ không nhìn thấy khi bật Excel. Bạn cũng không cần làm gì để kích hoạt tính năng này bởi vì Excel biết nơi file này được lưu và sẽ tự động tìm file này mỗi khi khởi động.

Ngoài việc lưu trữ được Macros, Personal Macro Workbook còn có thể lưu trữ được Sub, Functions, bao gồm cả những User Defined Functions (UDF) mà bạn viết ra để phục vụ công việc của mình.

Mặc định thì file Personal.xlsb không tồn tại, bạn cần làm một thao tác duy nhất là tạo ra file này, và lưu vào đó ít nhất một đoạn Macro.

Personal Macro Workbook được lưu ở đâu?

Trên một bản cài đặt tiêu chuẩn của Excel, thì file Personal.xlsb được lưu trong thư mục XLSTART ở trong những đường dẫn sau đây.

Trên Windows 10, Windows 6, và Windows Vista:

C:\Users\User Name\AppData\Roaming\Microsoft\Excel\XLSTART

Trên Windows XP:

C:\Documents and Settings\User Name\Application Data\Microsoft\Excel\XLSTART\

Lưu ý Bình thường thì thư mục AppData bị ẩn, để mở được thư mục này, bạn có thể viết vào trong thanh địa chỉ của Windows Explorer đường dẫn sau và nhấn nút Enter:

%AppData%

 

personal-macro-workbook-la-gi-01

Lưu ý Nếu thư mục XLSTART không tồn tại trên máy tính của bạn, điều này có nghĩa là Personal Macro Workbook chưa được tạo ra, chúng ta sẽ cùng nhau tạo ra file này ở bước tiếp theo.

Cách tạo ra Personal Macro Workbook trong Excel

Để tạo ra một Personal Macro Workbook, bạn có thể sử dụng chức năng ghi Macro trong thẻ Developer, nếu chưa bật được thẻ này, bạn hãy theo dõi hướng dẫn ở bài viết sau đây:

Cách hiện thẻ Developer để ghi Macro và viết Code VBA trong Excel

Để tạo ra một Personal Macro Workbook, hãy theo từng bước sau đây:

    1. Trong bất kỳ Workbook Excel nào, chọn thẻ Developer > trong nhóm lệnh Code, chọn Record Macro
      personal-macro-workbook-la-gi-02
    2. Trong cửa sổ Record Macro, chọn Personal Macro Workbook trong drop-down tại Store macro in (nơi lưu Macro), sau đó bấm OK.
      personal-macro-workbook-la-gi-03
    3. Nếu bạn muốn ghi một Macro nào đó, hãy thực hiện ở bước này, nhưng mục đích ở đây là việc tạo ra Personal Macro Workbook, vậy nên bạn có thể đi tới bước tiếp theo.
    4. Trên thẻ Developer, click nút Stop Recording
      personal-macro-workbook-la-gi-04
    5. Lưu và đóng tất cả các Workbook Excel đang mở. Excel sẽ hỏi bạn có muốn lưu thay đổi đối với Personal Macro Workbook không, hãy bấm nút Save
      personal-macro-workbook-la-gi-05
      Thông báo này sẽ xuất hiện mỗi khi bạn cập nhật hoặc thay đổi nội dung của Personal Macro Workbook.

Sau các bước này, thì bạn đã tạo thành công Personal Macro Workbook, file Personal.xlsb sẽ được tạo ra và tự động mở mỗi khi Excel khởi động. Giờ bạn đã có một nơi để có thể lưu trữ những đoạn code hay sử dụng một cách hiệu quả hơn. Câu hỏi đặt ra là làm thế nào bạn có thể viết code vào trong file này? Có một số cách sau đây mà bạn có thể tham khảo:

  • Sử dụng chức năng Macro Recorder sau đó chọn nơi lưu Macro là Personal Macro Workbook.
  • Tự viết Macro hay code VBA trong Visual Basic Editor
  • Copy code từ những nơi khác
  • Import Macro từ một file .bas

personal-macro-workbook-la-gi-06

Làm thế nào để cập nhật Macro trong Personal Macro Workbook

Một khi file Personal.xlsb đã được lưu trên máy của bạn, bạn có thể xem, tạo mới code VBA, sửa code VBA ở trong file này với Visual Basic Editor. Tất nhiên bạn cũng có thể copy code VBA ở đây ra các file khác của bạn.

Để cập nhật các code VBA trong file Personal.xlsb, bạn hãy theo các bước sau:

  1. Mở trình soạn thảo code VBA – Visual Basic for Applications bằng cách bấm phím tắt ALT + F11 hoặc click nút Visual Basic trên thẻ Developer
  2. Trong cửa sổ Project, tìm project có tên là PERSONAL.XLSB sau đó đi tới thư mục Modules của project này, rồi click đúp vào Module đầu tiên.
  3. Trong cửa sổ chỉnh sửa code VBA, bạn có thể viết thêm, sửa, cập nhật thêm các đoạn code VBA tại đây
  4. Sau khi cập nhật và sửa xong code, bạn có thể lưu và đóng Personal Macro Workbook lại.

Cách sử dụng code VBA lưu trong Personal Macro Workbook

Macros và các đoạn code VBA lưu trong Macro Personal Workbook có thể sử dụng được ở bất cứ file Excel nào. Để sử dụng các Macro đã lưu, bạn hãy làm như sau:

  1. Trên thẻ Developer, bấm nút Macros hoặc bấm phím tắt ALT + 8
  2. Trong cửa sổ Macro, chọn trong danh sách Macro mà bạn muốn chạy, sau đó bấm nút Run
    personal-macro-workbook-la-gi-07

Cách Export để sao lưu Personal Macro Workbook

Bạn có sưu tầm được một bộ code VBA và Macro rất hữu dụng và muốn sử dụng bộ sưu tập này trên nhiều máy khác nhau, hoặc đơn giản bạn muốn chuyển Personal Macro Workbook này đi, hãy làm theo cách sau để có thể export được Personal Macro Workbook của bạn:

  1. Mở Visual Basic Editor
  2. Trong cửa sổ Project Explorer, chọn dự án PERSONAL.XLSB bấm chuột phải vào Module bạn muốn sao lưu rồi chọn Export File … trong menu.
  3. Chọn nơi lưu file rồi bấm Save.
    personal-macro-workbook-la-gi-08

Để Import một file .bas chứa code VBA, bạn sẽ cần bấm chuột phải ở thư mục Modules, sau đó chọn Import … sau đó chọn file cần import.

Cách chia sẻ toàn bộ Personal Macro Workbook trong Excel

Có nhiều cách chúng ta có thể chia sẻ file Personal.xlsb này, hãy tìm hiểu từng cách có thể sử dụng tốt nhất trong trường hợp nào.

Chia sẻ trực tiếp toán bộ file Personal.xlsb

Có thể đây là cách dễ nhất khi bạn muốn chia sẻ một Personal Macro Workbook với một người chưa thiết lập chức năng này, hoặc bạn chuyển sang một máy tính mới và copy Personal Macro Workbook sang một máy tính mới. Tuy nhiên khi chúng ta chia sẻ file Personal Macro Workbook với một máy đã tồn tài code Personal Macro Workbook rồi, thì chúng ta cần cẩn thận nếu không, bạn sẽ ghi đè nội dung đã có ở trong thư mục XLSTART. Hãy chắc chắn về việc bạn muốn làm trong trường hợp này.

Copy Macro tới file khác

Nếu bạn chỉ cần một số Sub hay functions cụ thể, bạn có thể copy những Sub hay function này ra những Workbook hay file khác, hoặc thậm chí email đoạn code cho người khác. Tuy nhiên khi bạn copy code đi nơi khác, hãy chú ý tên của các Macro và Functions này không bị trùng lặp với các Macros có sẵn ở các file Excel khác.

Export macros ra một file .bas

Có lẽ đây là một cách tốt trong trường hợp này, chúng ta không cần quá nhiều thao tác bằng tay và không phải suy nghĩ nhiều, chỉ việc export rồi import các file .bas từ Workbook này qua Workbook khác. Trong trường hợp này, chúng ta cần xem xét cho trường hợp có thể một số macro sẽ không hoạt động trên các máy khác bởi vì bạn viết code VBA tham chiếu tới một đường dẫn hay một Sheet cụ thể trên hệ thống của bạn.

Cách kích hoạt Personal Macro Workbook

Có thể khi phần mềm Excel của bạn nâng cấp, hay bị đóng vì một lý do nào đó, có thể những Macro trong Personal Macro Workbook sẽ không hoạt động nữa. Đây là cách bạn kích hoạt lại Personal Macro Workbook:

  1. Mở Excel Options: vào thẻ File > Options
  2. Trong cửa sổ Excel Options, chọn mục Add-ins 
  3. Trong drop-down Manage bên dưới, chọn Disabled Items và chọn nút Go …
  4. Trong cửa sổ Disable Items, chọn Personal Macro Workbook và bấm nút Enable để kích hoạt.
  5. Khởi động lại Excel

personal-macro-workbook-la-gi-09

Cách tắt file Personal.xlsb nhưng giữ Macros vẫn chạy được

Mặc định thì Personal Macro Workbook sẽ không được mở ra khi bạn mở Excel. Nếu vì lý do nào đó, khi bạn mở Excel lên mà thấy file này, thì cách xử lý như sau:

  1. Chọn cửa sổ Excel đang mở file PERSONAL.XLSB
  2. Trên thẻ View, trong nhóm lệnh Window, chọn nút Hide

personal-macro-workbook-la-gi-10

Như vậy thôi là bạn có thể làm cho file Personal.xlsb không bị mở ra nữa và các macros hay code VBA vẫn có thể được sử dụng.

Cách mở file Personal.xlsb trong Excel

Ngược lại với thao tác ở đoạn trên, nếu bạn muốn file Personal.xlsb được mở, thì đây là các bước sẽ cần thực hiện:

  1. Trong thẻ View của nhóm lệnh Window, chọn nút Unhide.
  2. Trong cửa sổ Unhide, chọn PERSONAL.XLSB và bấm OK.

personal-macro-workbook-la-gi-11

Cách xóa bỏ Personal Macro Workbook

Nếu bạn muốn xóa bỏ hoàn toàn Personal Macro Workbook trên máy của bạn, hãy xóa file Personal.xlsb trong thư mục XLSTART, hãy theo các bước sau đây để không gặp lỗi khi xóa:

  1. Đóng toàn bộ các file Excel đang mở.
  2. Mở cửa sổ Windows Explorer.
  3. Tùy vào phiên bản Windows của bạn, hãy mở đường dẫn tới thư mục XLSTART như đã hướng dẫn ở trên.
  4. Xóa file PERSONAL.XLSB bằng nút Delete hoặc bấm chuột phải vào file, rồi chọn Delete trong menu.

Như vậy, qua bài viết này, Thanh đã hướng dẫn các bạn cách chúng ta có thể lưu lại những đoạn code hay sử dụng, những đoạn code chúng ta sưu tầm được. Nếu bạn còn chưa sưu tầm được đoạn code nào, hãy tham gia khóa học VBA của Thanh để bắt đầu nâng cao hiệu suất công việc để thành công hơn, địa chỉ đăng ký:

VBA cơ bản – Tự động hóa Excel với lập trình VBA cho người mới bắt đầu

Lập trình VBA nâng cao trong Excel