Hướng dẫn Excel VBA - Cách viết mã trong bảng tính bằng Visual Basic

Giới thiệu

Đây là hướng dẫn về cách 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 rằng "Hãy nghĩ về một thế giới không có Excel. Đó là điều không thể đối với tôi." Chà, có lẽ thế giới không thể 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 nhiều hơn một chút so với dân số châu Âu và nhiều hơn 25 lần so với năm 1996.

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

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 có 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 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

Qua bài viết này, bạn sẽ học:

  1. VBA là gì
  2. Tại sao bạn 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

Các 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.

Đối tượng : Excel là hướng đối tượng, có nghĩa là mọi thứ đều là một đố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 và thực hiện các hành động với các đố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 để điều đó ngấm vào!

Thủ tục : một thủ tục là một đoạn mã VBA, được viết trong Visual Basic Editor, để thực hiện một nhiệm vụ. Đôi khi, đây cũng đượ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âu lệnh VBA thực hiện một hoặc nhiều hành động
  • Hàm: một nhóm 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ị

Lưu ý: 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 bất kỳ thời gian nào để học các chức năng nâng cao hơn của Excel, có thể bạn đã gặp khái niệm về “macro”. Người dùng Excel có thể ghi lại các macro, bao gồm các lệnh / tổ hợp phím / nhấp chuột 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 được ghi lại tạo ra mã VBA, sau đó bạn có thể kiểm tra. Thực sự khá thú vị khi ghi lại một macro đơn giản và sau đó xem mã VBA.

Hãy nhớ rằng đôi khi việc ghi macro có thể dễ dàng và nhanh hơn thay vì viết tay một thủ tục VBA.

Ví dụ, có thể bạn làm việc trong lĩnh vực quản lý dự án. Mỗi tuần một lần, bạn phải biến một báo cáo thô được xuất 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 dành cho lãnh đạo. Bạn cần định dạng tên của các dự án vượt quá ngân sách bằng văn bản màu đỏ đậm. Bạn có thể ghi lại các thay đổi định dạng dưới dạng macro và chạy nó bất cứ khi nào bạn cần thực hiện thay đổi.

VBA là gì?

Visual Basic for Applications là một ngôn ngữ lập trình do Microsoft phát triển. Mỗi chương trình phần mềm trong bộ Microsoft Office được đi kèm với ngôn ngữ VBA mà không phải trả thêm 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 một 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 đốt bằng củi thì sao? Không thể làm điều đó trong một lò nướng thương mại tiêu chuẩn. VBA là lò nướng bánh pizza.

Yum.

Tại sao sử dụng VBA trong Excel?

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

Nhưng nghiêm túc đấy.

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 nhau. Tùy thuộc vào hoàn cảnh, 10 phút trong Excel có thể giống như vĩnh cửu 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ứ diễn ra tốt đẹp. Đó là lúc bạn nên tự hỏi mình, tại sao tôi lại dành 10 giờ trong 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 tệp khác sau khi thực hiện các thay đổi đối với tệp Excel, một thủ tục VBA có thể là giải pháp cho bạn.

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

  • Tự động hóa các tác vụ lặp đi lặp lại
  • Tạo 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 với lượng lớn dữ liệu

Bắt đầu thiết lập để viết VBA trong Excel

Tab 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 ruy-băng:

  1. Trên tab Tệp, đi tới Tùy chọn> Tùy chỉnh Ruy-băng.
  2. Trong Tùy chỉnh ruy-băng và bên dưới 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.

Trình chỉnh sửa 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.

Ví dụ về VBA trong Excel

Đầu tiên, hãy tạo một tệp để chúng tôi có thể sử dụng.

  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ở VBA Editor

Hãy lắc lư 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 Chèn -> Mô-đun mới

Viết mã này trong 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 Sub

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ị.

Ta da!

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

Tùy thuộc vào sự 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ác 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; nó sẽ không chạy nếu sổ làm việc được mở qua mã từ một sổ làm việc khác (Workbook_Open sẽ làm điều đó, hãy tìm hiểu thêm về sự khác biệt giữa hai loại này).
  • 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 đều 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à private. Đ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.
  • msgBox: đâ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ị 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 một 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 được truy cập không thường xuyên (giả sử mỗi quý một lần), nhưng được cập nhật tự động hàng ngày bằng một quy trình VBA khác. Khi nó được truy cập, nó sẽ được nhiều người ở nhiều phòng ban, trong toàn công ty truy cập.

  • Vấn đề: Hầu hết khi người dùng truy cập tệp, họ đều bối rối về mục đích của tệp này (tại sao nó tồn tại), làm thế nào nó được cập nhật thường xuyên, ai là người duy trì nó và họ nên tương tác với nó như thế nào. Những người mới tuyển dụng luôn có rất nhiều câu hỏi, và bạn phải điền đi điền lại những câu hỏi này.
  • Giải pháp: tạo một tin nhắn người dùng có 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 hàm 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, 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 chức năng 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 Chèn -> Mô-đun mới

Viết mã này trong cửa sổ Mô-đun (không dán!):

Sub UserReportQuery ()

Làm mờ đầu vào người dùng càng lâu

Dim câu trả lời dưới dạng số nguyên

UserInput = vbYesNo

Answer = MsgBox ("Xử lý Báo cáo XYZ?", UserInput)

If Answer = vbYes Then ProcessReport

Kết thúc Sub

Sub ProcessReport ()

MsgBox ("Cảm ơn bạn đã xử lý Báo cáo XYZ.")

Kết thúc Sub

Lưu và điều hướng trở 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ờ hãy nhấp 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 thứ hai, 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ụ số 3. Nhưng trước tiên...

Làm thế nào nó đang 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 xem qua những thứ mới:

  • Dim UserInput As Long: Dim là viết tắt của "thứ nguyên" 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. Trong tiếng Anh đơn giản, dòng này có nghĩa là "Đây là một biến được gọi là" UserInput "và đó là một loại biến Dài."
  • Dim Answer As Integer: khai báo một biến khác được gọi là “Answer” với kiểu dữ liệu là Integer. Tìm hiểu thêm về các loại 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.
  • Answer = MsgBox (“Xử lý Báo cáo XYZ?”, UserInput): gán giá trị của biến Answer là một hàm MsgBox và biến UserInput. Có, một biến trong một biến.
  • If Answer = vbYes Then ProcessReport: đây là câu lệnh “If”, một câu lệnh điều kiện, cho phép chúng ta nói nếu x là đúng thì hãy thực hiện y. Trong trường hợp này, nếu người dùng đã chọn “Có”, thì hãy 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 theo 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 thứ cấp 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 nhiều cách khác nhau.

  • Sự cố: Mỗi khi cần tạo một trong những báo cáo này, người dùng sẽ mở tệp và thay đổi định dạng và biểu đồ; Vv và Vv. Tệp này đang được chỉnh sửa rộng rãi ít nhất 3 lần mỗi tuần và mất ít nhất 30 phút mỗi lần chỉnh sửa.
  • Giải pháp: 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 một 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 chức năng 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 một 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 lại trên một phạm vi giá trị cụ thể - mảng hoặc phạm vi ô. Trong tiếng Anh đơn giản, một vòng lặp cho biết “với mỗi x, làm y”.

Trong VBA Editor, chọn Chèn -> Mô-đun mới

Viết mã này trong cửa sổ Mô-đun (không dán!):

Sub LoopExample ()

Dim X As Integer

Đối với X = 1 đến 100

Phạm vi ("A" & X) .Value = X

X tiếp theo

Kết thúc Sub

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:

Vv, cho đến hàng thứ 100.

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

  • Dim X As Integer: khai báo biến X là kiểu dữ liệu là Integer.
  • For X = 1 To 100: đây là phần bắt đầu của vòng lặp For. Nói một cách đơn giản, nó ra lệnh cho 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 thi cho đến khi X = 100, thực hiện lần cuối, rồi dừng lại.
  • Range ("A" & X) .Value = 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ì X = 1 ban đầu nên ô đầu tiên sẽ là A1, lúc này vòng lặp sẽ đưa X vào ô đó.
  • Tiếp theo X: điều này cho biết 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; có rất nhiều trường hợp sử dụng tiềm năng. Đâ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 của các 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 trong Cột A, loại sản phẩm trong 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 trong Cột C và giá trung bình thị trường của từng loại sản phẩm trong tờ khác.

Bạn cần tìm ra giá bán lẻ của từng sản phẩm. Bạn đang nghĩ rằng đó phải là chi phí của các thành phần cộng thêm 20%, nhưng cũng có thể là 1,2% dưới mức trung bình của thị trường nếu có thể. Vòng lặp For-Next sẽ cho phép bạn thực hiện kiểu 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 để thêm các giá trị cụ thể vào một mảng riêng biệt chỉ khi 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 mỗi giá trị trong một phạm vi, ví dụ: tính toán các khoản phí bổ sung và cộng 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

Phần kết luận

Bây giờ chúng ta đã nói về bánh pizza và bánh nướng xốp và ồ, làm thế nào để viết mã VBA trong bảng tính Excel, hãy cùng thực hiện kiểm tra học tập. 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ử 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ể hoàn thành chỉ với một chút mã trong bảng tính Excel của bạn.

Chúc bạn viết mã vui vẻ!

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

  • Lập trình Excel VBA cho Dummies, 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. Là một nhà giáo dục trước đây, tôi liên tục tìm kiếm sự giao thoa giữa học tập và giảng 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.dev.