Thẻ: vba cơ bản

doi-tuong-application-trong-excel-vba

Đối tượng Application trong Excel VBA

Đố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

personal-macro-workbook-la-gi

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

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

Logo Học Excel Online Inverse white

Đăng ký học qua Email Listen@hocexcel.online

Hộ kinh doanh Học Excel Online.
Số ĐK: 17A80048102

© Học Excel Online. All rights reserved.