Bạn muốn Excel tự động tính ra kết quả mà không để lại công thức tính? Việc này được thực hiện một cách rất đơn giản thông qua VBA. Hãy cùng Học Excel Online tìm hiểu cách làm ngay nhé.

Tự động gán công thức tính vào ô bằng VBA

Để có thể gán công thức vào trong 1 ô, chúng ta sử dụng thuộc tính Formula. Cách viết như sau:

Sub GanCongThuc()

‘Gan cong thuc vao o A7

Sheet1.Range(“A7”).Formula = “=SUM(A2:A6)”

End Sub

Công macro trên, chúng ta gán hàm SUM tính tổng các ô trong vùng A2:A6 vào ô A7

Khi thực hiện Macro này, Excel sẽ tự động tạo ra công thức =SUM(A2:A6) vào ô A7 mà chúng ta không cần phải viết bằng tay trực tiếp vào ô A7

Hình 1: gán công thức thông thường vào trong 1 ô

Khi sử dụng thuộc tính Formula, chúng ta chú ý những vấn đề sau:

• Áp dụng được cho cả đối tượng là Cells hoặc Range
• Công thức được gán phải bao gồm cả dấu bằng và đặt trong cặp dấu nháy kép
• Nếu trong công thức có chứa sẵn dấu nháy kép thì phải viết 2 lần dấu nháy kép

Hình 2: Gán công thức có sẵn dấu nháy kép trong VBA

Trong hàm IF ở ví dụ trên, cặp từ Yes, No là ký tự text nên được đặt trong cặp dấu nháy kép. Nhưng khi gán công thức này trong VBA, tại mỗi vị trí có sử dụng dấu nháy kép cần đặt trong 1 cặp dấu nháy kép nữa (tức là 2 cặp dấu nháy kép liền nhau).

Mở rộng:

Ngoài ra chúng ta có thể kết hợp thêm phương thức FillDown để có thể fill công thức cho nhiều ô cùng 1 lúc.

Câu lệnh: Sheet1.Range(“B2:B6”).FillDown

Filldown là việc sao chép công thức xuống các dòng phía dưới.

Việc này giúp chúng ta không phải gán công thức vào từng ô nếu công thức tương tự nhau. Khi làm thao tác này sẽ tiết kiệm được thời gian nếu số lượng ô cần gán công thức nhiều.

Tự động lấy kết quả của công thức trong ô bằng VBA

Khi đã gán công thức vào trong ô, chúng ta thu được kết quả của công thức đó. Nhưng làm thế nào để chỉ giữ lại kết quả mà không còn công thức nữa? Cách thực hiện như sau:

Hình 3: Cách lấy kết quả của công thức trong macro VBA

Câu lệnh khá đơn giản: Chỉ cần lấy đúng giá trị của ô chứa công thức và bằng chính nó.

Sheet1.Range(“A7”).value là giá trị trong ô A7, thuộc Sheet1 (code name của sheet)

Như vậy chỉ với 2 bước đơn giản, chúng ta đã có thể tự động thu được kết quả trong ô mà không để lại công thức tính. Việc này thực hiện rất đơn giản và dễ làm đúng không. Nếu bạn chưa giỏi VBA thì hoàn toàn có thể sử dụng cách này.

Ứng dụng của cách làm này:

• Trong những trường hợp công thức đơn giản, dễ viết.
• Hạn chế công thức động, giúp file nhẹ hơn, chỉ lấy kết quả trong những thời điểm nhất định.
• Giấu công thức tính không cho người khác biết (tốt hơn cách dùng Protect/ Hidden)
• Tiết kiệm thời gian nếu cần tính nhiều công thức cùng lúc.

Xem thêm

Cách ẩn và khóa công thức trong Excel 2010, 2013, 2016

Cách ẩn công thức trong Excel

Chuyển công thức Excel sang hàm Vba sử dụng WorkSheetFunction