Hướng dẫn này sẽ dạy bạn cách tạo công thức ô bằng VBA.
Công thức trong VBA
Sử dụng VBA, bạn có thể viết công thức trực tiếp vào Dải ô hoặc Ô trong Excel. Nó trông như thế này:
| 123456789 | Công thức phụ_Ví dụ ()'Gán một công thức được mã hóa cứng cho một ôPhạm vi ("b3"). Công thức = "= b1 + b2"'Gán một công thức linh hoạt cho một dải ôPhạm vi ("d1: d100"). Công thứcR1C1 = "= RC2 + RC3"Kết thúc Sub |
Có hai thuộc tính Phạm vi bạn sẽ cần biết:
- .Công thức - Tạo một công thức chính xác (tham chiếu ô được mã hóa cứng). Tốt để thêm công thức vào một ô.
- .FormulaR1C1 - Tạo ra một công thức linh hoạt. Tốt để thêm công thức vào một dải ô nơi các tham chiếu ô sẽ thay đổi.
Đối với các công thức đơn giản, bạn có thể sử dụng Thuộc tính .Formula. Tuy nhiên, đối với mọi thứ khác, chúng tôi khuyên bạn nên sử dụng Máy ghi macro…
Máy ghi macro và công thức ô
Macro Recorder là công cụ cần thiết của chúng tôi để viết công thức ô bằng VBA. Bạn có thể chỉ cần:
- Bắt đầu ghi âm
- Nhập công thức (với tham chiếu tương đối / tuyệt đối nếu cần) vào ô và nhấn enter
- Dừng ghi
- Mở VBA và xem lại công thức, điều chỉnh khi cần thiết và sao chép + dán mã nếu cần.
Tôi thấy nó là dễ dàng hơn nhiều để nhập công thức vào một ô hơn là nhập công thức tương ứng trong VBA.
Lưu ý một số điều:
- Máy ghi Macro sẽ luôn sử dụng thuộc tính .FormulaR1C1
- Trình ghi Macro nhận dạng tham chiếu ô tuyệt đối so với ô tương đối
Thuộc tính VBA FormulaR1C1
Thuộc tính FormulaR1C1 sử dụng tham chiếu ô kiểu R1C1 (trái ngược với kiểu A1 tiêu chuẩn mà bạn quen thấy trong Excel).
Dưới đây là một số ví dụ:
| 12345678910111213141516171819 | Công thức conR1C1_Examples ()'Tham chiếu D5 (Tuyệt đối)'= $ D $ 5Phạm vi ("a1"). Công thứcR1C1 = "= R5C4"'Tham chiếu D5 (Tương đối) từ ô A1'= D5Phạm vi ("a1"). Công thứcR1C1 = "= R [4] C [3]"'Tham chiếu D5 (Hàng tuyệt đối, Cột Tương đối) từ ô A1'= D $ 5Phạm vi ("a1"). Công thứcR1C1 = "= R5C [3]"'Tham chiếu D5 (Hàng Tương đối, Cột Tuyệt đối) từ ô A1'= $ D5Phạm vi ("a1"). Công thứcR1C1 = "= R [4] C4"Kết thúc Sub |
Lưu ý rằng tham chiếu ô kiểu R1C1 cho phép bạn đặt tham chiếu tuyệt đối hoặc tương đối.
Tài liệu tham khảo tuyệt đối
Trong ký hiệu chuẩn A1, một tham chiếu tuyệt đối trông giống như sau: “= $ C $ 2”. Trong ký hiệu R1C1, nó trông giống như sau: “= R2C3”.
Để tạo một tham chiếu ô tuyệt đối bằng cách sử dụng kiểu kiểu R1C1:
- R + Số hàng
- C + Số cột
Ví dụ: R2C3 sẽ đại diện cho ô $ C $ 2 (C là cột thứ 3).
| 123 | 'Tham chiếu D5 (Tuyệt đối)'= $ D $ 5Phạm vi ("a1"). Công thứcR1C1 = "= R5C4" |
Tài liệu tham khảo tương đối
Tham chiếu ô tương đối là tham chiếu ô "di chuyển" khi công thức được di chuyển.
Trong ký hiệu A1 tiêu chuẩn, chúng trông như thế này: “= C2”. Trong ký hiệu R1C1, bạn sử dụng dấu ngoặc [] để bù đắp tham chiếu ô khỏi ô hiện tại.
Ví dụ: Nhập công thức “= R [1] C [1]” vào ô B3 sẽ tham chiếu đến ô D4 (ô 1 hàng bên dưới và 1 cột bên phải ô công thức).
Sử dụng số âm để tham chiếu các ô phía trên hoặc bên trái ô hiện tại.
| 123 | 'Tham chiếu D5 (Tương đối) từ ô A1'= D5Phạm vi ("a1"). Công thứcR1C1 = "= R [4] C [3]" |
Tài liệu tham khảo hỗn hợp
Tham chiếu ô có thể một phần tương đối và một phần tuyệt đối. Thí dụ:
| 123 | 'Tham chiếu D5 (Hàng Tương đối, Cột Tuyệt đối) từ ô A1'= $ D5Phạm vi ("a1"). Công thứcR1C1 = "= R [4] C4" |
Thuộc tính công thức VBA
Khi thiết lập công thức với.Thuộc tính Công thức bạn sẽ luôn sử dụng ký hiệu kiểu A1. Bạn nhập công thức giống như bạn làm trong một ô Excel, ngoại trừ được bao quanh bởi dấu ngoặc kép:
| 12 | 'Gán một công thức được mã hóa cứng cho một ôPhạm vi ("b3"). Công thức = "= b1 + b2" |
Mẹo công thức VBA
Công thức với biến
Khi làm việc với Công thức trong VBA, rất phổ biến là bạn muốn sử dụng các biến trong công thức ô. Để sử dụng các biến, bạn sử dụng & để kết hợp các biến với phần còn lại của chuỗi công thức. Thí dụ:
| 1234567 | Công thức phụ_Variable ()Dim colNum As LongcolNum = 4Phạm vi ("a1"). Công thứcR1C1 = "= R1C" & colNum & "+ R2C" & colNumKết thúc Sub |
Trích dẫn Công thức
Nếu bạn cần thêm dấu ngoặc kép (“) trong công thức, hãy nhập dấu ngoặc kép hai lần (“ ”):
| 123 | Sub Macro2 ()Phạm vi ("B3"). Công thứcR1C1 = "= TEXT (RC [-1]," "mm / dd / yyyy" ")"Kết thúc Sub |
Một dấu ngoặc kép (“) biểu thị cho VBA phần cuối của một chuỗi văn bản. Trong khi dấu ngoặc kép (“”) được coi như một dấu ngoặc kép trong chuỗi văn bản.
Tương tự, sử dụng 3 dấu ngoặc kép (“” ”) để bao quanh một chuỗi bằng dấu ngoặc kép (“)
| 12 | MsgBox "" "Sử dụng 3 để bao quanh một chuỗi có dấu ngoặc kép" ""'Điều này sẽ in cửa sổ ngay lập tức |
Gán công thức ô cho biến chuỗi
Chúng ta có thể đọc công thức trong một ô hoặc dải ô nhất định và gán nó cho một biến chuỗi:
| 123 | 'Gán công thức ô cho biếnDim strFormula dưới dạng chuỗistrFormula = Range ("B1"). Công thức |
Các cách khác nhau để thêm công thức vào ô
Dưới đây là một số ví dụ khác về cách gán công thức cho một ô:
- Chỉ định trực tiếp công thức
- Xác định một biến chuỗi chứa công thức
- Sử dụng các biến để tạo công thức
| 12345678910111213141516171819202122232425 | Sub MoreFormulaExamples ()'Các cách thay thế để thêm công thức SUM'đến ô B1'Dim strFormula dưới dạng chuỗiLàm mờ ô dưới dạng Phạm vidim fromRow as Range, toRow as RangeĐặt ô = Phạm vi ("B1")'Chỉ định trực tiếp một chuỗicell.Formula = "= SUM (A1: A10)"'Lưu trữ chuỗi vào một biến'và gán cho thuộc tính "Công thức"strFormula = "= SUM (A1: A10)"cell.Formula = strFormula'Sử dụng các biến để xây dựng một chuỗi'và gán nó cho thuộc tính "Công thức"fromRow = 1toRow = 10strFormula = "= SUM (A" & fromValue & ": A" & toValue & ")cell.Formula = strFormulaKết thúc Sub |
Làm mới công thức
Xin nhắc lại, để làm mới công thức, bạn có thể sử dụng lệnh Tính toán:
| 1 | Tính toán |
Để làm mới một công thức, phạm vi hoặc toàn bộ trang tính, hãy sử dụng .Calculate thay thế:
| 1 | Trang tính ("Trang 1"). Phạm vi ("a1: a10"). Tính |
