Nội dung
Ngay cả khi bạn không biết cách lập trình, có rất nhiều nơi (sách, trang web, diễn đàn) nơi bạn có thể tìm thấy mã macro VBA được tạo sẵn cho một số lượng lớn các tác vụ điển hình trong Excel. Theo kinh nghiệm của tôi, hầu hết người dùng sớm hay muộn đều thu thập bộ sưu tập macro cá nhân của họ để tự động hóa các quy trình thông thường, cho dù đó là dịch công thức thành giá trị, hiển thị tổng bằng từ hay tính tổng các ô theo màu sắc. Và ở đây vấn đề nảy sinh - mã macro trong Visual Basic cần được lưu trữ ở đâu đó để có thể sử dụng sau này trong công việc.
Tùy chọn dễ nhất là lưu mã macro trực tiếp trong tệp đang làm việc bằng cách đi tới trình chỉnh sửa Visual Basic bằng phím tắt Khác+F11 và thêm một mô-đun trống mới qua menu Chèn - Mô-đun:
Tuy nhiên, có một số nhược điểm với phương pháp này:
- Nếu có nhiều tệp đang hoạt động và cần có macro ở mọi nơi, chẳng hạn như macro để chuyển đổi công thức thành giá trị, thì bạn sẽ phải sao chép mã trong mọi cuốn sách.
- Không được quên lưu tệp ở định dạng hỗ trợ macro (xlsm) hoặc ở định dạng sách nhị phân (xlsb).
- Khi mở một tệp như vậy bảo vệ vĩ mô mỗi lần sẽ đưa ra một cảnh báo cần được thừa nhận (tốt, hoặc vô hiệu hóa hoàn toàn tính năng bảo vệ, điều này có thể không phải lúc nào cũng mong muốn).
Một giải pháp thanh lịch hơn sẽ là tạo bổ trợ của riêng bạn (Bổ trợ Excel) - một tệp riêng biệt có định dạng đặc biệt (xlam) chứa tất cả các macro “yêu thích” của bạn. Ưu điểm của phương pháp này:
- Nó sẽ đủ thôi kết nối tiện ích bổ sung một lần trong Excel - và bạn có thể sử dụng các thủ tục và hàm VBA của nó trong bất kỳ tệp nào trên máy tính này. Do đó, lưu các tệp đang làm việc của bạn ở định dạng xlsm- và xlsb-là không bắt buộc, bởi vì. mã nguồn sẽ không được lưu trữ trong chúng, nhưng trong tệp bổ trợ.
- Sự bảo vệ bạn cũng sẽ không bị làm phiền bởi macro. các tiện ích bổ sung, theo định nghĩa, là các nguồn đáng tin cậy.
- Có thể làm được tab riêng biệt trên ribbon Excel với các nút đẹp để chạy macro bổ trợ.
- Phần bổ trợ là một tệp riêng biệt. Của anh dễ dàng mang theo từ máy tính sang máy tính, chia sẻ nó với đồng nghiệp hoặc thậm chí bán nó 😉
Hãy xem qua toàn bộ quá trình tạo phần bổ trợ Microsoft Excel của riêng bạn theo từng bước.
Bước 1. Tạo tệp bổ trợ
Mở Microsoft Excel với một sổ làm việc trống và lưu nó dưới bất kỳ tên phù hợp nào (ví dụ: MyExcelAddin) ở định dạng bổ trợ bằng lệnh Tệp - Lưu dưới dạng hoặc chìa khóa F12, chỉ định loại tệp Phần bổ trợ Excel:
Xin lưu ý rằng theo mặc định, Excel lưu trữ các phần bổ trợ trong thư mục C: UsersYour_nameAppDataRoamingMicrosoftAddIns, nhưng về nguyên tắc, bạn có thể chỉ định bất kỳ thư mục nào khác thuận tiện cho mình.
Bước 2. Chúng tôi kết nối phần bổ trợ đã tạo
Bây giờ phần bổ trợ mà chúng tôi đã tạo ở bước cuối cùng MyExcelAddin phải được kết nối với Excel. Để thực hiện việc này, hãy chuyển đến menu Tệp - Tùy chọn - Tiện ích bổ sung (Tệp - Tùy chọn - Phần bổ trợ), nhấn nút Giới thiệu (Đi) ở cuối cửa sổ. Trong cửa sổ mở ra, nhấp vào nút Đánh giá (Duyệt) và chỉ định vị trí của tệp bổ trợ của chúng tôi.
Nếu bạn đã làm đúng mọi thứ, thì MyExcelAddin sẽ xuất hiện trong danh sách các tiện ích bổ sung có sẵn:
Bước 3. Thêm macro vào bổ trợ
Phần bổ trợ của chúng tôi được kết nối với Excel và hoạt động thành công, nhưng chưa có một macro nào trong đó. Hãy lấp đầy nó. Để thực hiện việc này, hãy mở trình soạn thảo Visual Basic bằng phím tắt Khác+F11 hoặc bằng nút Visual Basic chuyển hướng nhà phát triển (Nhà phát triển). Nếu các tab nhà phát triển không nhìn thấy, nó có thể được hiển thị thông qua Tệp - Tùy chọn - Thiết lập ruy-băng (Tệp - Tùy chọn - Tùy chỉnh ruy-băng).
Phải có một cửa sổ ở góc trên bên trái của trình chỉnh sửa Dự án (nếu nó không hiển thị, hãy bật nó qua menu Chế độ xem - Trình khám phá dự án):
Cửa sổ này hiển thị tất cả các sổ làm việc đang mở và chạy các phần bổ trợ Microsoft Excel, bao gồm cả phần bổ trợ của chúng tôi. Dự án VBA (MyExcelAddin.xlam) Chọn nó bằng chuột và thêm một mô-đun mới vào nó qua menu Chèn - Mô-đun. Trong mô-đun này, chúng tôi sẽ lưu trữ mã VBA của các macro bổ trợ của chúng tôi.
Bạn có thể nhập mã từ đầu (nếu bạn biết cách lập trình) hoặc sao chép mã từ nơi nào đó làm sẵn (dễ hơn nhiều). Để thử nghiệm, hãy nhập mã của một macro đơn giản nhưng hữu ích vào mô-đun trống được thêm vào:
Sau khi nhập mã, bạn đừng quên bấm vào nút lưu (đĩa đệm) ở góc trên bên trái.
Macro của chúng tôi Công thứcToValues, như bạn có thể dễ dàng hình dung, chuyển đổi công thức thành các giá trị trong một phạm vi được chọn trước. Đôi khi những macro này còn được gọi là thủ tục. Để chạy nó, bạn cần chọn các ô có công thức và mở một hộp thoại đặc biệt Macros từ tab nhà phát triển (Nhà phát triển - Macro) hoặc phím tắt Khác+F8. Thông thường, cửa sổ này hiển thị các macro có sẵn từ tất cả các sổ làm việc đang mở, nhưng các macro bổ trợ không hiển thị ở đây. Mặc dù vậy, chúng ta có thể nhập tên thủ tục của mình vào trường tên macro (Tên macro)và sau đó nhấp vào nút chạy (chạy) - và macro của chúng tôi sẽ hoạt động:
| |
Tại đây, bạn cũng có thể chỉ định một phím tắt để khởi chạy nhanh macro - nút này chịu trách nhiệm cho việc này Thông số (Tùy chọn) trong cửa sổ trước Macro:
Khi gán các phím, hãy nhớ rằng chúng có phân biệt chữ hoa chữ thường và bố cục bàn phím. Vì vậy, nếu bạn chỉ định một tổ hợp như Ctrl+Й, then, in fact, in the future you will have to make sure that you have the layout turned on and press additionally sự thay đổiđể có được chữ cái viết hoa.
Để thuận tiện, chúng ta cũng có thể thêm một nút cho macro của mình vào thanh công cụ truy cập nhanh ở góc trên bên trái của cửa sổ. Để làm điều này, hãy chọn Tệp - Tùy chọn - Thanh công cụ Truy cập Nhanh (Tệp - Tùy chọn - Tùy chỉnh Thanh công cụ Truy cập Nhanh), và sau đó trong danh sách thả xuống ở đầu cửa sổ, tùy chọn Macros. Sau đó macro của chúng tôi Công thứcToValues có thể được đặt trên bảng điều khiển bằng nút Thêm (Thêm) và chọn một biểu tượng cho nó bằng nút Thay đổi (Chỉnh sửa):
Bước 4. Thêm các chức năng vào phần bổ trợ
Nhưng thủ tục vĩ mô, cũng có macro chức năng hoặc như chúng được gọi là UDF (User Defined Function = chức năng do người dùng xác định). Hãy tạo một mô-đun riêng trong tiện ích bổ sung của chúng tôi (lệnh menu Chèn - Mô-đun) và dán mã của hàm sau vào đó:
Có thể dễ dàng nhận thấy rằng chức năng này là cần thiết để trích xuất VAT từ số tiền đã bao gồm VAT. Tất nhiên, không phải là nhị thức Newton, nhưng nó sẽ giúp chúng ta làm ví dụ để chỉ ra các nguyên lý cơ bản.
Lưu ý rằng cú pháp của một hàm khác với một thủ tục:
- xây dựng được sử dụng Hàm số …. Chức năng kết thúc thay vì Sub… End Sub
- sau tên của hàm, các đối số của nó được chỉ định trong dấu ngoặc
- trong phần thân của hàm, các phép tính cần thiết được thực hiện và sau đó kết quả được gán cho một biến có tên của hàm
Cũng lưu ý rằng chức năng này không cần thiết và không thể chạy như quy trình macro trước đó thông qua hộp thoại Macros và nút chạy. Một hàm macro như vậy nên được sử dụng như một hàm trang tính chuẩn (SUM, IF, VLOOKUP…), tức là chỉ cần nhập vào bất kỳ ô nào, chỉ định giá trị của số tiền có VAT làm đối số:
… Hoặc nhập thông qua hộp thoại tiêu chuẩn để chèn một hàm (nút fx trong thanh công thức), chọn một danh mục Đã xác định người dùng (Đã xác định người dùng):
Thời điểm khó chịu duy nhất ở đây là sự vắng mặt của mô tả thông thường của chức năng ở cuối cửa sổ. Để thêm nó, bạn sẽ phải làm như sau:
- Mở Visual Basic Editor bằng phím tắt Khác+F11
- Chọn phần bổ trợ trong bảng Project và nhấn phím F2để mở cửa sổ Trình duyệt đối tượng
- Chọn dự án bổ trợ của bạn từ danh sách thả xuống ở đầu cửa sổ
- Nhấp chuột phải vào chức năng xuất hiện và chọn lệnh Bất động sản.
- Nhập mô tả chức năng vào cửa sổ Mô tả
- Lưu tệp bổ trợ và khởi động lại excel.
Sau khi khởi động lại, hàm sẽ hiển thị mô tả mà chúng tôi đã nhập:
Bước 5. Tạo tab tiện ích bổ sung trong giao diện
Cuối cùng, mặc dù không bắt buộc, nhưng cảm giác thú vị sẽ là tạo một tab riêng biệt với một nút để chạy macro của chúng tôi, sẽ xuất hiện trong giao diện Excel sau khi kết nối bổ trợ của chúng tôi.
Thông tin về các tab được hiển thị theo mặc định được chứa trong sách và phải được định dạng bằng mã XML đặc biệt. Cách dễ nhất để viết và chỉnh sửa mã như vậy là với sự trợ giúp của các chương trình đặc biệt - trình soạn thảo XML. Một trong những tiện ích nhất (và miễn phí) là chương trình của Maxim Novikov Trình chỉnh sửa XML ruy-băng.
Thuật toán làm việc với nó như sau:
- Đóng tất cả các cửa sổ Excel để không có xung đột tệp khi chúng tôi chỉnh sửa mã XML bổ trợ.
- Khởi chạy chương trình Ribbon XML Editor và mở tệp MyExcelAddin.xlam của chúng tôi trong đó
- Có nút tab ở góc trên bên trái, thêm đoạn mã cho tab mới:
- Bạn cần đặt trong dấu ngoặc kép trống id tab và nhóm của chúng tôi (bất kỳ số nhận dạng duy nhất nào) và trong nhãn - tên của tab của chúng tôi và một nhóm các nút trên đó:
- Có nút nút trên bảng điều khiển bên trái, thêm mã trống cho nút và thêm thẻ vào đó:
- nhãn mác là văn bản trên nút
- imageMso - đây là tên có điều kiện của hình ảnh trên nút. Tôi đã sử dụng biểu tượng nút màu đỏ có tên AnimationCustomAddExitDialog. Tên của tất cả các nút có sẵn (và có hàng trăm nút trong số đó!) Có thể được tìm thấy trên một số lượng lớn các trang web trên Internet nếu bạn tìm kiếm từ khóa “imageMso”. Để bắt đầu, bạn có thể vào đây.
- onAction - đây là tên của thủ tục gọi lại - một macro ngắn đặc biệt sẽ chạy macro chính của chúng ta Công thứcToValues. Bạn có thể gọi thủ tục này là bất cứ điều gì bạn thích. Chúng tôi sẽ bổ sung nó sau một chút.
- Bạn có thể kiểm tra tính đúng đắn của mọi thứ được thực hiện bằng cách sử dụng nút có dấu kiểm màu xanh lục trên đầu thanh công cụ. Tại cùng một nơi, nhấp vào nút có đĩa mềm để lưu tất cả các thay đổi.
- Đóng trình soạn thảo XML ruy-băng
- Mở Excel, đi tới trình soạn thảo Visual Basic và thêm thủ tục gọi lại vào macro của chúng tôi Giết Công thứcđể nó chạy macro chính của chúng ta để thay thế các công thức bằng các giá trị.
- Chúng tôi lưu các thay đổi và quay lại Excel, kiểm tra kết quả:
Đó là tất cả - phần bổ trợ đã sẵn sàng để sử dụng. Điền vào nó với các thủ tục và chức năng của riêng bạn, thêm các nút đẹp - và việc sử dụng macro trong công việc của bạn sẽ trở nên dễ dàng hơn nhiều.
- Macro là gì, cách sử dụng chúng trong công việc của bạn, nơi lấy mã macro trong Visual Basic.
- Cách tạo màn hình giật gân khi mở sổ làm việc trong Excel
- Sách Macro Cá nhân là gì và cách sử dụng nó