VBAExcel

Bước 8 - Bây giờ, nhấp đúp vào nút và phác thảo quy trình phụ sẽ được hiển thị như trong ảnh chụp màn hình sau

Bước 9 - Bắt đầu mã hóa bằng cách thêm một tin nhắn

Private Sub say_helloworld_Click[]
   MsgBox "Hi"
End Sub

Bước 10 - Nhấp vào nút để thực hiện quy trình phụ. Đầu ra của thủ tục phụ được hiển thị trong ảnh chụp màn hình sau. Đảm bảo rằng bạn đã bật chế độ thiết kế. Chỉ cần nhấp vào nó để bật nó lên nếu nó không bật

Lưu ý - Trong các chương tiếp theo, chúng tôi sẽ minh họa bằng cách sử dụng một nút đơn giản, như được giải thích từ bước #1 đến bước 10. Do đó, điều quan trọng là phải hiểu kỹ chương này.

Đây là bài hướng dẫn viết mã trong bảng tính Excel bằng Visual Basic for Applications [VBA]

Excel là một trong những sản phẩm phổ biến nhất của Microsoft. Vào năm 2016, Giám đốc điều hành của Microsoft đã nói : "Hãy nghĩ về một thế giới không có Excel. Điều đó là không thể đối với tôi. ” Chà, có lẽ thế giới không thể suy nghĩ nếu không có Excel

• Năm 1996, có hơn 30 triệu người dùng Microsoft Excel [nguồn]
• Ngày nay, ước tính có khoảng 750 triệu người dùng Microsoft Excel. Con số này cao hơn một chút so với dân số của Châu Âu và gấp 25 lần người dùng so với năm 1996

Chúng tôi là một gia đình lớn hạnh phúc

Trong hướng dẫn này, bạn sẽ tìm hiểu về VBA và cách viết mã trong bảng tính Excel bằng Visual Basic

điều kiện tiên quyết

Bạn không cần bất kỳ kinh nghiệm lập trình trước đó để hiểu hướng dẫn này. Tuy nhiên, bạn sẽ cần

• Làm quen từ cơ bản đến trung cấp với Microsoft Excel
• Nếu bạn muốn làm theo các ví dụ VBA trong bài viết này, bạn sẽ cần quyền truy cập vào Microsoft Excel, tốt nhất là phiên bản mới nhất [2019] nhưng Excel 2016 và Excel 2013 sẽ hoạt động tốt
• Sẵn sàng thử những điều mới

Mục tiêu học tập

Trong suốt bài viết này, bạn sẽ học

1. VBA là gì
2. Tại sao bạn sẽ sử dụng VBA
3. Cách thiết lập trong Excel để viết VBA
4. Cách giải quyết một số vấn đề trong thế giới thực với VBA

Khái niệm quan trọng

Dưới đây là một số khái niệm quan trọng mà bạn nên làm quen để hiểu đầy đủ hướng dẫn này

Các đối tượng. Excel hướng đối tượng, nghĩa là mọi thứ đều là đối tượng - cửa sổ Excel, sổ làm việc, trang tính, biểu đồ, ô. VBA cho phép người dùng thao tác, thực hiện các thao tác với đối tượng trong Excel

Nếu bạn chưa có bất kỳ kinh nghiệm nào về lập trình hướng đối tượng và đây là một khái niệm hoàn toàn mới, hãy dành một chút thời gian để hiểu rõ điều đó

thủ tục. một thủ tục là một đoạn mã VBA, được viết trong Visual Basic Editor, để hoàn thành một nhiệm vụ. Đôi khi, điều này còn được gọi là macro [thêm về macro bên dưới]. Có hai loại thủ tục

• chương trình con. một nhóm các câu lệnh VBA thực hiện một hoặc nhiều hành động
• Chức năng. một nhóm các câu lệnh VBA thực hiện một hoặc nhiều hành động và trả về một hoặc nhiều giá trị

Ghi chú. bạn có thể có các hàm hoạt động bên trong các chương trình con. Bạn sẽ thấy sau

macro. Nếu bạn đã dành thời gian tìm hiểu thêm về chức năng nâng cao của Excel, có lẽ bạn đã gặp khái niệm về “macro. ” Người dùng Excel có thể ghi macro, bao gồm các lệnh/tổ hợp phím/lần nhấp của người dùng và phát lại chúng với tốc độ cực nhanh để hoàn thành các tác vụ lặp đi lặp lại. Các macro đã ghi tạo mã VBA, sau đó bạn có thể kiểm tra mã này. Thực sự khá thú vị khi ghi lại một macro đơn giản và sau đó xem mã VBA

Xin lưu ý rằng đôi khi việc ghi macro có thể dễ dàng và nhanh hơn thay vì viết mã thủ công cho quy trình VBA

Ví dụ, có thể bạn làm việc trong quản lý dự án. Mỗi tuần một lần, bạn phải chuyển một báo cáo xuất thô từ hệ thống quản lý dự án của mình thành một báo cáo rõ ràng, được định dạng đẹp mắt cho lãnh đạo. Bạn cần định dạng tên của các dự án vượt ngân sách bằng chữ in đậm màu đỏ. Bạn có thể ghi lại các thay đổi định dạng dưới dạng macro và chạy macro đó bất cứ khi nào bạn cần thực hiện thay đổi

Visual Basic for Applications là một ngôn ngữ lập trình được phát triển bởi Microsoft. Mỗi chương trình phần mềm trong bộ Microsoft Office đều đi kèm với ngôn ngữ VBA mà không mất thêm chi phí. VBA cho phép người dùng Microsoft Office tạo các chương trình nhỏ hoạt động trong các chương trình phần mềm Microsoft Office

Hãy nghĩ về VBA giống như một lò nướng bánh pizza trong nhà hàng. Excel là nhà hàng. Nhà bếp đi kèm với các thiết bị thương mại tiêu chuẩn, như tủ lạnh lớn, bếp và lò nướng thông thường - đó là tất cả các tính năng tiêu chuẩn của Excel

Nhưng nếu bạn muốn làm bánh pizza bằng củi thì sao? . VBA là lò nướng pizza

ngon

Vì pizza nướng bằng củi là ngon nhất

Nhưng nghiêm túc

Rất nhiều người dành nhiều thời gian cho Excel như một phần công việc của họ. Thời gian trong Excel cũng di chuyển khác đi. Tùy thuộc vào hoàn cảnh, 10 phút trong Excel có thể dài như vô tận nếu bạn không thể làm những gì mình cần hoặc 10 giờ có thể trôi qua rất nhanh nếu mọi thứ đều suôn sẻ. Đó là lúc bạn nên tự hỏi mình, tại sao tôi lại dành 10 giờ cho Excel?

Đôi khi, những ngày đó là không thể tránh khỏi. Nhưng nếu bạn đang dành 8-10 giờ mỗi ngày trong Excel để thực hiện các tác vụ lặp đi lặp lại, lặp lại nhiều quy trình giống nhau, cố gắng dọn dẹp sau khi những người dùng khác của tệp hoặc thậm chí cập nhật các tệp khác sau khi các thay đổi được thực hiện đối với tệp Excel,

Bạn nên cân nhắc sử dụng VBA nếu bạn cần

• Tự động hóa các tác vụ lặp đi lặp lại
• Tạo các cách dễ dàng để người dùng tương tác với bảng tính của bạn
• Thao tác một lượng lớn dữ liệu

Thẻ nhà phát triển

Để viết VBA, bạn cần thêm tab Nhà phát triển vào dải băng, vì vậy bạn sẽ thấy dải băng như thế này

Để thêm tab Nhà phát triển vào dải băng

1. Trên tab Tệp, đi tới Tùy chọn > Tùy chỉnh Ruy-băng
2. Bên dưới Tùy chỉnh Dải băng và bên dưới Các tab Chính, hãy chọn hộp kiểm Nhà phát triển

Sau khi bạn hiển thị tab, tab Nhà phát triển vẫn hiển thị, trừ khi bạn bỏ chọn hộp kiểm hoặc phải cài đặt lại Excel. Để biết thêm thông tin, hãy xem tài liệu trợ giúp của Microsoft

Biên tập viên VBA

Điều hướng đến tab Nhà phát triển và nhấp vào nút Visual Basic. Một cửa sổ mới sẽ bật lên - đây là Visual Basic Editor. Với mục đích của hướng dẫn này, bạn chỉ cần làm quen với ngăn Project Explorer và ngăn Thuộc tính thuộc tính

Trước tiên, hãy tạo một tệp để chúng tôi chơi xung quanh

1. Mở một tệp Excel mới
2. Lưu nó dưới dạng sổ làm việc hỗ trợ macro [. xlsm]
3. Chọn tab Nhà phát triển
4. Mở trình soạn thảo VBA

Hãy bắt đầu với một số ví dụ đơn giản để giúp bạn viết mã trong bảng tính bằng Visual Basic

Ví dụ 1. Hiển thị Thông báo khi Người dùng Mở Sổ làm việc Excel

Trong VBA Editor, chọn Insert -> New Module

Viết mã này vào cửa sổ Mô-đun [không dán. ]

Sub Auto_Open[]
MsgBox ["Chào mừng bạn đến với Sổ làm việc XYZ. "]
Kết thúc phụ

Lưu, đóng sổ làm việc và mở lại sổ làm việc. Hộp thoại này sẽ hiển thị

tà đà

Làm thế nào là nó làm điều đó?

Tùy thuộc vào mức độ quen thuộc của bạn với lập trình, bạn có thể có một số phỏng đoán. Nó không đặc biệt phức tạp, nhưng có khá nhiều thứ đang diễn ra

• Sub [viết tắt của “Subroutine]. hãy nhớ ngay từ đầu, “một nhóm câu lệnh VBA thực hiện một hoặc nhiều hành động. ”
• Auto_Open. đây là chương trình con cụ thể. Nó tự động chạy mã của bạn khi tệp Excel mở ra - đây là sự kiện kích hoạt quy trình. Auto_Open sẽ chỉ chạy khi sổ làm việc được mở theo cách thủ công;
• Theo mặc định, quyền truy cập của chương trình con là công khai. Điều này có nghĩa là bất kỳ mô-đun nào khác có thể sử dụng chương trình con này. Tất cả các ví dụ trong hướng dẫn này sẽ là các chương trình con công khai. Nếu cần, bạn có thể khai báo các chương trình con là riêng tư. Điều này có thể cần thiết trong một số tình huống. Tìm hiểu thêm về công cụ sửa đổi quyền truy cập chương trình con
• tin nhắnBox. đây là một hàm - một nhóm các câu lệnh VBA thực hiện một hoặc nhiều hành động và trả về một giá trị. Giá trị được trả về là thông báo “Chào mừng bạn đến với Sổ làm việc XYZ. ”

Tóm lại, đây là một chương trình con đơn giản có chứa hàm

Khi nào tôi có thể sử dụng cái này?

Có thể bạn có một tệp rất quan trọng không được truy cập thường xuyên [ví dụ: mỗi quý một lần], nhưng được cập nhật tự động hàng ngày bởi một thủ tục VBA khác. Khi nó được truy cập, nó sẽ được truy cập bởi nhiều người trong nhiều bộ phận, trên toàn công ty

• Vấn đề. Hầu hết khi người dùng truy cập tệp, họ bối rối về mục đích của tệp này [tại sao tệp này tồn tại], tệp được cập nhật thường xuyên như thế nào, ai duy trì tệp và họ nên tương tác với tệp như thế nào. Những người mới tuyển dụng luôn có vô số câu hỏi và bạn phải trả lời đi trả lại những câu hỏi này nhiều lần
• Dung dịch. tạo một thông báo người dùng chứa câu trả lời ngắn gọn cho từng câu hỏi thường gặp này

Ví dụ trong thế giới thực

• Sử dụng chức năng MsgBox để hiển thị thông báo khi có bất kỳ sự kiện nào. người dùng đóng sổ làm việc Excel, người dùng in, một trang tính mới được thêm vào sổ làm việc, v.v.
• Sử dụng hàm MsgBox để hiển thị thông báo khi người dùng cần đáp ứng một điều kiện trước khi đóng sổ làm việc Excel
• Sử dụng hàm InputBox để lấy thông tin từ người dùng

Ví dụ #2. Cho phép người dùng thực hiện một thủ tục khác

Trong VBA Editor, chọn Insert -> New Module

Viết mã này vào cửa sổ Mô-đun [không dán. ]

Sub UserReportQuery[]
Làm mờ UserInput miễn là
Làm mờ câu trả lời dưới dạng số nguyên
UserInput = vbYesNo
Answer = MsgBox["Process the XYZ Report?", UserInput]
If Answer = vbYes Then ProcessReport
End Sub

Sub ProcessReport[]
MsgBox ["Cảm ơn bạn đã xử lý Báo cáo XYZ. "]
Kết thúc phụ

Lưu và điều hướng quay lại tab Nhà phát triển của Excel và chọn tùy chọn “Nút”. Bấm vào một ô và gán macro UserReportQuery cho nút

Bây giờ bấm vào nút. Thông báo này sẽ hiển thị

Nhấp vào “có” hoặc nhấn Enter

Một lần nữa, tada

Xin lưu ý rằng chương trình con phụ, ProcessReport, có thể là bất cứ thứ gì. Tôi sẽ chứng minh nhiều khả năng hơn trong ví dụ #3. Nhưng trước tiên

Làm thế nào là nó làm điều đó?

Ví dụ này được xây dựng dựa trên ví dụ trước và có khá nhiều yếu tố mới. Hãy đi qua những thứ mới

• Làm mờ đầu vào của người dùng trong thời gian dài. Dim là viết tắt của “dimension” và cho phép bạn khai báo tên biến. Trong trường hợp này, UserInput là tên biến và Long là kiểu dữ liệu. Nói một cách dễ hiểu, dòng này có nghĩa là “Đây là một biến có tên là “UserInput” và đó là một loại Biến dài. ”
• Câu trả lời mờ dưới dạng số nguyên. khai báo một biến khác gọi là “Trả lời”, với kiểu dữ liệu là Số nguyên. Tìm hiểu thêm về các kiểu dữ liệu tại đây
• UserInput = vbYesNo. gán giá trị cho biến. Trong trường hợp này, vbYesNo, hiển thị các nút Có và Không. Có nhiều loại nút, tìm hiểu thêm tại đây
• Trả lời = MsgBox[“Xử lý Báo cáo XYZ?”, UserInput]. gán giá trị của biến Answer thành hàm MsgBox và biến UserInput. Vâng, một biến trong một biến
• Nếu Trả lời = vbYes thì ProcessReport. đây là một “câu lệnh If”, một câu lệnh có điều kiện, cho phép chúng ta nói nếu x đúng, thì y có đúng không. Trong trường hợp này, nếu người dùng đã chọn “Yes” thì thực hiện chương trình con ProcessReport

Khi nào tôi có thể sử dụng cái này?

Điều này có thể được sử dụng trong nhiều, nhiều cách. Giá trị và tính linh hoạt của chức năng này được xác định nhiều hơn bởi những gì chương trình con phụ thực hiện

Ví dụ: có thể bạn có một tệp được sử dụng để tạo 3 báo cáo hàng tuần khác nhau. Các báo cáo này được định dạng theo những cách khác nhau đáng kể

• Vấn đề. Mỗi khi cần tạo một trong các báo cáo này, người dùng sẽ mở tệp và thay đổi định dạng cũng như biểu đồ; . Tệp này đang được chỉnh sửa nhiều ít nhất 3 lần mỗi tuần và mất ít nhất 30 phút cho mỗi lần chỉnh sửa
• Dung dịch. tạo 1 nút cho mỗi loại báo cáo, tự động định dạng lại các thành phần cần thiết của báo cáo và tạo các biểu đồ cần thiết

Ví dụ trong thế giới thực

• Tạo hộp thoại để người dùng tự động điền thông tin nhất định trên nhiều trang tính
• Sử dụng hàm InputBox để lấy thông tin từ người dùng, sau đó được điền trên nhiều trang tính

Ví dụ #3. Thêm số vào một phạm vi với vòng lặp tiếp theo

Vòng lặp for rất hữu ích nếu bạn cần thực hiện các tác vụ lặp đi lặp lại trên một phạm vi giá trị cụ thể - mảng hoặc phạm vi ô. Nói một cách dễ hiểu, một vòng lặp có nội dung “với mỗi x, do y. ”

Trong VBA Editor, chọn Insert -> New Module

Viết mã này vào cửa sổ Mô-đun [không dán. ]

Sub LoopExample[]
Dim X dưới dạng số nguyên
For X = 1 To 100
Phạm vi["A . Giá trị = X
X tiếp theo
Kết thúc phụ

Lưu và điều hướng trở lại tab Nhà phát triển của Excel và chọn nút Macro. Chạy macro LoopExample

Điều này sẽ xảy ra

Vân vân, cho đến hàng thứ 100

Làm thế nào là nó làm điều đó?

• Dim X dưới dạng số nguyên. khai báo biến X có kiểu dữ liệu là Integer
• Đối với X = 1 đến 100. đây là điểm bắt đầu của vòng lặp For. Nói một cách đơn giản, nó yêu cầu vòng lặp tiếp tục lặp lại cho đến khi X = 100. X là bộ đếm. Vòng lặp sẽ tiếp tục thực hiện cho đến khi X = 100, thực hiện lần cuối rồi dừng
• Phạm vi["A" & X]. Giá trị = X. điều này khai báo phạm vi của vòng lặp và những gì cần đặt trong phạm vi đó. Vì ban đầu X = 1 nên ô đầu tiên sẽ là A1, lúc này vòng lặp sẽ đưa X vào ô đó
• X tiếp theo. điều này báo cho vòng lặp chạy lại

Khi nào tôi có thể sử dụng cái này?

Vòng lặp For-Next là một trong những chức năng mạnh mẽ nhất của VBA; . Đây là một ví dụ phức tạp hơn, đòi hỏi nhiều lớp logic, nhưng nó truyền đạt thế giới khả năng trong các vòng lặp For-Next

Có thể bạn có danh sách tất cả các sản phẩm được bán tại tiệm bánh của mình ở Cột A, loại sản phẩm ở Cột B [bánh ngọt, bánh rán hoặc bánh nướng xốp], chi phí nguyên liệu ở Cột C và chi phí trung bình thị trường của từng loại sản phẩm ở Cột A.

Bạn cần tính xem giá bán lẻ của từng sản phẩm là bao nhiêu. Bạn đang nghĩ rằng đó phải là chi phí nguyên liệu cộng thêm 20%, nhưng cũng là 1. 2% dưới mức trung bình thị trường nếu có thể. Vòng lặp For-Next sẽ cho phép bạn thực hiện loại tính toán này

Ví dụ trong thế giới thực

• Sử dụng vòng lặp với câu lệnh if lồng nhau để chỉ thêm các giá trị cụ thể vào một mảng riêng biệt nếu chúng đáp ứng các điều kiện nhất định
• Thực hiện các phép tính toán học trên từng giá trị trong một phạm vi, e. g. tính toán các khoản phí bổ sung và thêm chúng vào giá trị
• Lặp qua từng ký tự trong một chuỗi và trích xuất tất cả các số
• Chọn ngẫu nhiên một số giá trị từ một mảng

Bây giờ chúng ta đã nói về bánh pizza và bánh nướng xốp và vâng, cách viết mã VBA trong bảng tính Excel, chúng ta hãy kiểm tra kiến ​​thức. Xem nếu bạn có thể trả lời những câu hỏi này

• VBA là gì?
• Làm cách nào để thiết lập để bắt đầu sử dụng VBA trong Excel?
• Tại sao và khi nào bạn sẽ sử dụng VBA?
• Một số vấn đề tôi có thể giải quyết với VBA là gì?

Nếu bạn có một ý tưởng hợp lý về cách bạn có thể trả lời những câu hỏi này, thì điều này đã thành công

Cho dù bạn là người dùng không thường xuyên hay người dùng thành thạo, tôi hy vọng hướng dẫn này cung cấp thông tin hữu ích về những gì có thể được thực hiện chỉ với một chút mã trong bảng tính Excel của bạn

Mã hóa vui vẻ

Tài nguyên học tập

• Lập trình VBA Excel cho người mới bắt đầu, John Walkenbach
• Bắt đầu với VBA, Tài liệu Microsoft
• Học VBA trong Excel, Lynda

Một chút về tôi

Tôi là Chloe Tucker, một nghệ sĩ và nhà phát triển ở Portland, Oregon. Từng là một nhà giáo dục, tôi không ngừng tìm kiếm sự giao thoa giữa học và dạy, hay công nghệ và nghệ thuật. Liên hệ với tôi trên Twitter @_chloetucker và xem trang web của tôi tại chloe. nhà phát triển

QUẢNG CÁO

QUẢNG CÁO

QUẢNG CÁO

QUẢNG CÁO

QUẢNG CÁO

QUẢNG CÁO

QUẢNG CÁO

QUẢNG CÁO

QUẢNG CÁO

QUẢNG CÁO

QUẢNG CÁO

QUẢNG CÁO

Nếu bạn đọc đến đây, hãy tweet cho tác giả để cho họ thấy bạn quan tâm. Tweet một lời cảm ơn

Học cách viết mã miễn phí. Chương trình giảng dạy mã nguồn mở của freeCodeCamp đã giúp hơn 40.000 người có được việc làm với tư cách là nhà phát triển. Bắt đầu

VBA trong Excel là gì?

Excel VBA có dễ không?

VBA có dễ hơn Python không?

Sự khác biệt giữa VBA và Excel là gì?