Nhập công thức mảng trong VBA. (5 người xem)

[sealand]

Tại các sheets của Excel ta nhập công thức rồi kết thúc bằng Ctrl+Shilt+Enter là ta có công thức mảng tại ô đó. Trong VBA ta có thể nhập công thức cho 1 ô ví dụ là A1 bằng cách:
Sheet("TH").range("A1").Formula= "=Sum(B1:E1)"
Vậy nếu trong VBA cần gán công thức mảng cho 1 ô thì làm thế nào ? Mình đang cần để gán công thức khi cần xem kết quả.Khi không xem nữa thì xóa toàn bộ công thức để tăng tốc độ tính toán tránh tình trang ngồi xem đồng hồ cát khi làm việc.
Cám ơn các bạn.
SEALAND

 

[Mr Okebab]

Tại các sheets của Excel ta nhập công thức rồi kết thúc bằng Ctrl+Shilt+Enter là ta có công thức mảng tại ô đó. Trong VBA ta có thể nhập công thức cho 1 ô ví dụ là A1 bằng cách:
Sheet("TH").range("A1").Formula= "=Sum(B1:E1)"
Vậy nếu trong VBA cần gán công thức mảng cho 1 ô thì làm thế nào ? Mình đang cần để gán công thức khi cần xem kết quả.Khi không xem nữa thì xóa toàn bộ công thức để tăng tốc độ tính toán tránh tình trang ngồi xem đồng hồ cát khi làm việc.
Cám ơn các bạn.
SEALAND

Để gắn công thức mảng :
Sheet("TH").range("A1").FormulaArray = "=Sum(B1:E1)"

Thân!

 

[vanhai]

Gửi bạn Sealand
Để tăng tốc độ tính toán ( thực ra là tắt chế dộ tính toán trong lúc nhập số liệu) chỉ tính toán khi cần xem kết quả thì bạn có thể làm như sau :
- Sheet này (Sheet1) bạn vẫn để công thức mảng đó bình thường
- Trang VBA bạn viết macro tắt chế độ tính toán sheet này khi open wokbook (Sheet1.EnableCalculation=False).
- Tạo 1 modul với tên TINH() với nội dung
Sheet1.EnableCalculation=True
Sheet1.EnableCalculation=False
- Trong Sheet1 bạn thiết lập 1 Commandbutton và gán macro TINH vào
như vậy bình thường khi khởi động workbook này lên thì Sheet1 không tính toán, khi cần xem kế quả thì bạn nhấp vào nút trên thì sheet1 sẽ tính toán ra kết quả.
Cách này tôi nghĩ sẽ nhanh hơn cách cho macro lập lại công thức mảng
Chào bạn

 

[sealand]

Gửi bạn Sealand
Để tăng tốc độ tính toán ( thực ra là tắt chế dộ tính toán trong lúc nhập số liệu) chỉ tính toán khi cần xem kết quả thì bạn có thể làm như sau :
- Sheet này (Sheet1) bạn vẫn để công thức mảng đó bình thường
- Trang VBA bạn viết macro tắt chế độ tính toán sheet này khi open wokbook (Sheet1.EnableCalculation=False).
- Tạo 1 modul với tên TINH() với nội dung
Sheet1.EnableCalculation=True
Sheet1.EnableCalculation=False
- Trong Sheet1 bạn thiết lập 1 Commandbutton và gán macro TINH vào
như vậy bình thường khi khởi động workbook này lên thì Sheet1 không tính toán, khi cần xem kế quả thì bạn nhấp vào nút trên thì sheet1 sẽ tính toán ra kết quả.
Cách này tôi nghĩ sẽ nhanh hơn cách cho macro lập lại công thức mảng
Chào bạn

Bạn Van Hai thân mến! Rất cám ơn bạn đã có ý kiến giúp mình. Nhưng bạn ơi tôi là dân Kế toán mà. Vậy nên trong sheet PHATSINH ít ra đã có từ 2000 đến 5000 dòng định khoản phát sinh (Đối với đơn vị vừa và nhỏ). Từ 5000 dòng nếu dùng công thức phải link tới tối thiểu các sheet:
-Sổ chi tiết tài khoản.
-Sổ cái tài khoản.
-Nhật ký chung.
Các công thức mảng tập hợp dữ liệu cho bảng cân đối tài khoản, Bảng cân đối kế toán và 1 số báo cáo khác....
Như vậy rất nặng, thực tế tôi đã dùng biện pháp khi nhập dữ liệu thì chuyển bỏ chế độ tính toán tự động, nhưng không ổn. Khi thiết lập lại nó chạy rất lâu thậm chí treo đơ ra.
Chính vì vậy mà trong tệp tôi phải tìm mọi cách để tối ưu tốc độ, vì mình ăn nhờ vào Excel không là chương trình lập phần mềm chính thống.
Có vấn đè gì hướng dẫn thêm nhé.

 

[Mr Okebab]

Bạn Van Hai thân mến! Rất cám ơn bạn đã có ý kiến giúp mình. Nhưng bạn ơi tôi là dân Kế toán mà. Vậy nên trong sheet PHATSINH ít ra đã có từ 2000 đến 5000 dòng định khoản phát sinh (Đối với đơn vị vừa và nhỏ). Từ 5000 dòng nếu dùng công thức phải link tới tối thiểu các sheet:
-Sổ chi tiết tài khoản.
-Sổ cái tài khoản.
-Nhật ký chung.
Các công thức mảng tập hợp dữ liệu cho bảng cân đối tài khoản, Bảng cân đối kế toán và 1 số báo cáo khác....
Như vậy rất nặng, thực tế tôi đã dùng biện pháp khi nhập dữ liệu thì chuyển bỏ chế độ tính toán tự động, nhưng không ổn. Khi thiết lập lại nó chạy rất lâu thậm chí treo đơ ra.
Chính vì vậy mà trong tệp tôi phải tìm mọi cách để tối ưu tốc độ, vì mình ăn nhờ vào Excel không là chương trình lập phần mềm chính thống.
Có vấn đè gì hướng dẫn thêm nhé.

5000 dòng là chuyện nhỏ bạn ạ.
Quan trọng là sự tối ưu về công thức và cách thức tổ chức dữ liệu thôi.


Thân!

P/S : Hãy gửi File lên thì sẽ nhận được các góp ý hay.

 

[vanhai]

Khi thiết lập lại nó chạy rất lâu thậm chí treo đơ ra
.

Vậy thì bại thiết lập chức năng tắt tính toán tất cả sheet khi open worbook như hướng dẩn thì khi nhập vào sheet PHATSINH thì bạn nhập thoải mái vài chục ngàn dòng mà không có sheet được tính toán nên bạn nhập rất nhanh.
Khi cần xem kết quả tại sheet nào thì chỉ cần kích hoạt cho sheet đó ( hoặc các sheet có liên quan) tính toán mà thôi, kết quả cũng rất nhanh. File của tôi cũng dùng phương pháp này rất hiệu quả
Chào bạn