Tự động hóa việc viết công thức
Viết công thức có thể là một trong những phần tốn thời gian nhất trong công việc Excel hàng tuần hoặc hàng tháng của bạn. Nếu bạn đang làm việc để tự động hóa quá trình đó bằng macro, thì bạn có thể yêu cầu VBA viết công thức và nhập công thức đó vào các ô cho bạn. Viết công thức trong VBA có thể hơi phức tạp lúc đầu, vì vậy đây là 3 mẹo giúp tiết kiệm thời gian và làm cho quá trình dễ dàng hơn.
Tip #1: Công thức Property
Công thức Property là một thành viên của Range object trong VBA. Chúng ta có thể sử dụng nó để đặt/tạo công thức cho một ô hoặc dải ô.
Có một số yêu cầu đối với giá trị của công thức mà chúng ta đặt với Công thức property:
- Công thức là một chuỗi văn bản được đặt trong dấu ngoặc kép. Giá trị của công thức phải bắt đầu và kết thúc trong dấu ngoặc kép.
- Chuỗi công thức phải bắt đầu bằng dấu bằng “=” sau dấu ngoặc kép đầu tiên.
Đây là một ví dụ đơn giản về công thức trong macro.
Sub Formula_Property()
‘Formula is a string of text wrapped in quotation marks
‘Starts with an = sign
Range(“B10”).Formula = “=SUM(B4:B9)”
End Sub
Công thức property cũng có thể được sử dụng để đọc một công thức hiện có trong một ô.
Tip #2: Sử dụng Macro Recorder
Khi công thức của bạn phức tạp hơn hoặc chứa các ký tự đặc biệt, chúng có thể khó viết hơn trong VBA. May mắn thay, chúng ta có thể sử dụng macro recorder để tạo mã cho chúng ta.
Dưới đây là các bước để tạo mã thuộc tính công thức bằng macro recorder.
- Bật macro recorder (Developer tab > Record Macro)
- Nhập công thức của bạn hoặc chỉnh sửa công thức hiện có.
- Nhấn Enter để nhập công thức.
- Mã được tạo trong macro.
Nếu công thức của bạn chứa dấu ngoặc kép hoặc ký hiệu “&”, thì macro recorder sẽ tính điều này. Nó tạo ra tất cả các chuỗi con và gói mọi thứ trong dấu ngoặc kép một cách chính xác. Đây là một ví dụ.
Sub Macro10()
‘Use the macro recorder to create code for complex formulas with
‘special characters and relative references
ActiveCell.FormulaR1C1 = “=””Total Sales: “” & TEXT(R[-5]C,””$#,###””)”
End Sub
Tip #3: Ký hiệu công thức kiểu R1C1
Nếu bạn sử dụng macro recorder cho các công thức, bạn sẽ nhận thấy rằng nó tạo mã với FormulaR1C1 property.
Ký hiệu kiểu R1C1 cho phép chúng ta tạo cả tham chiếu tương đối (A1), tuyệt đối ($ A $ 1) và hỗn hợp ($ A1, A $ 1) trong mã macro của chúng ta.
R1C1 là viết tắt của Rows and Columns.
Tham chiếu tương đối
Đối với tham chiếu tương đối, chúng tôi chỉ định số hàng và cột mà chúng tôi muốn bù trừ từ ô chứa công thức. Số hàng và cột được tham chiếu trong dấu ngoặc vuông.
Thao tác sau sẽ tạo tham chiếu đến một ô có 3 hàng ở trên và 2 hàng ở bên phải của ô chứa công thức.
R[-3]C[2]
Các số âm đi lên hàng và cột ở bên trái.
Số dương đi xuống hàng và cột bên phải.
Tham chiếu tuyệt đối
Chúng ta cũng có thể sử dụng ký hiệu R1C1 cho các tham chiếu tuyệt đối. Điều này thường trông giống như $A$2.
Đối với các tham chiếu tuyệt đối, chúng tôi KHÔNG sử dụng dấu ngoặc vuông. Phần sau sẽ tạo tham chiếu trực tiếp đến ô $A$2, hàng 2 cột 1
R2C1
Tham chiếu hỗn hợp
với các tham chiếu hỗn hợp, chúng tôi thêm dấu ngoặc vuông cho tham chiếu hàng hoặc cột và không có dấu ngoặc cho tham chiếu khác. Công thức sau trong ô B2 sẽ tạo tham chiếu này đến A$2, trong đó hàng là tuyệt đối và cột là tương đối.
R2C[-1]
Khi tạo tham chiếu hỗn hợp, số hàng hoặc cột tương đối sẽ phụ thuộc vào ô mà công thức đang ở.Thật dễ dàng nhất là chỉ cần sử dụng trình ghi macro để tìm ra những điều này.
FormulaR1C1 property so với Công thức Property
FormulaR1C1 Property đọc ký hiệu R1C1 và tạo các tham chiếu thích hợp trong các ô. Nếu bạn sử dụng Công thức property thông thường với ký hiệu R1C1, thì VBA sẽ cố gắng đưa các chữ cái đó vào công thức và có thể sẽ dẫn đến lỗi công thức.
Do đó, hãy sử dụng Công thức property khi mã của bạn chứa ô tham chiếu ($A$1), FormulaR1C1 property khi bạn cần tham chiếu tương đối được áp dụng cho nhiều ô hoặc phụ thuộc vào vị trí nhập công thức.
Nếu bảng tính của bạn thay đổi dựa trên các điều kiện ngoài tầm kiểm soát của bạn, chẳng hạn như các cột hoặc hàng dữ liệu mới được nhập từ nguồn dữ liệu, thì các tham chiếu tương đối và ký hiệu kiểu R1C1 có thể sẽ là tốt nhất.
Nguồn: Excel Campus
Xem thêm