Excel ứng dụng trong Thiết kế xây dựng | phần 2

phần 2 | Lập trình VBA trong Excel

Khi gặp phải vấn đề mà các chức năng sẵn có như trong phần 1 bó tay, hoặc có thể làm nhưng quá phức tạp. Đó là lúc nghĩ đến một công cụ mở rộng của Excel, là lập trình bằng ngôn ngữ VBA (Visual Basic for Application).

Đừng vội bỏ qua khi nghe đến “lập trình”. Tui không định bảo các bạn học một khoá IT 3-5 năm đâu! Hơn nữa cũng không cần thiết tìm hiểu mọi ngóc ngách của lập trình VBA. Công việc cần cái gì ta mò cái đó, một cách thực dụng. Các vấn đề đề cập ở đây đều cơ bản, đúng như tên gọi của nó (Basic). Chỉ cần chút kỹ năng lập trình Pascal đã học ở đại học và chút kiên nhẫn. Cố lên, thời buổi này các cháu tiểu học đã biết lập trình rồi huống gì cầm bằng kỹ sư trong tay 🥰

Lùng sục Google

Đúng tinh thần đi từ dễ đến khó, cách này thậm chí chả cần biết lập trình.

• Bước 1: Google những hàm Excel đã được chia sẻ sẵn trên mạng. Cách làm quen thuộc là nếu tiếng Việt không ra thì dùng tiếng Anh.

💎Ví dụ Hàm anh em kỹ sư hay dùng để tra bảng là Nội suy một chiều, hai chiều.

Google “Excel hàm nội suy 2 chiều”... hay dịch ra “Excel VBA interpolate 2 dimensions”…

Kết quả tìm được nhiều lắm. Ta cần dùng thử từng cái, thấy cái nào xài ổn ít lỗi thì OK.


Tiếp theo là thêm các hàm này vào trong file XLS đang mở.

• Bước 2: Lôi tab “Developer” lên thanh công cụ của Excel:

• Bước 3: Vào tab Developer, nhấn nút “Visual Basic” (hoặc phím tắt Alt-F11) để mở cửa sổ Microsoft Visual Basic.

chọn menu Insert/Module để hiện ra cửa sổ con Code. Đây là nơi để gõ các lệnh của VBA.

• Bước 4: paste phần code của hàm từ trang web vào cửa sổ Code này. Ctrl-S để lưu và đóng cửa sổ Microsoft Visual Basic.

• Bước 5: Xài hàm mới thôi🍺

Quay lại bảng tính, gõ “=” + tên hàm mới (cú pháp). Ví dụ hàm nội suy 2 chiều ta vừa thêm vào:

= INTER2D (vùng bảng tra, giá trị theo hàng, giá trị theo cột)

Khi mở bảng tính nhớ bấm nút “Enable Macros” nhé.


🎁 mấy hàm lùng Google được hay dùng trong thực tế nghề:

• Nội suy 1 chiều: https://wellsr.com/vba/2016/excel/powerful-excel-linear-interpolation-function-vba/
• 
  
• Nội suy 2 chiều: https://thuthuatxaydung.blogspot.com/2015/07/noi-suy-2-chieu-trong-excel.html
• 
  
• Hàm đọc số thành chữ: https://exceleasy1.blogspot.com/2019/03/code-oc-chu-thanh-so-trong-excel-vba.html

rất hữu ích cho việc lập dự toán, kế toán, báo giá…

Lập trình

Khi không thể tìm được đồ ăn sẵn nữa. Đã đến lúc bắt tay vào nội dung chính. Như đã nói, ta chỉ cần những thứ cơ bản công việc cần thôi. Một nhu cầu hay gặp là vòng lặp 2 chiều

💎Ví dụ bảng tính rung của sàn

Lúc này buộc phải dùng đến VBA. Ta sẽ tập trung vào cách để làm vòng lặp thôi hen.

Để đánh giá độ rung của sàn, ta cần tính được gia tốc phản ứng

- trường hợp dao động Liên tục, tổ hợp theo phương pháp Căn bậc hai bình phương (SRSS)

$$a_{w,rms,e,r}=\frac1{\sqrt2}\sqrt{\sum_{h=1}^H\left(\sum_{n=1}^N\left(\mu_{e,n}\mu_{r,n}\frac{F_h}{M_n}D_{n,h}W_h\right)\right)^2}$$

Công thức khá rối mắt vì tính hai tổng theo hai biến: đầu tiên lấy tổng theo dạng dao động n, sau đó lấy tổng theo hoạ âm thứ h.

🔍For… Next là câu lệnh VBA để tính vòng lặp 2 chiều này. Vòng ở trong là theo n (số dạng dao động là $n_1$), vòng ở ngoài theo h (số hoạ âm H=4)

SRSS = 0
For h = 1 To 4
 SoHang(h) = 0
 For n = 1 To n1
   SoHang(h) = SoHang(h) + muy_e(n, 1) * muy_r(n, 1) * Fh(h) / Mn(n, 1) * D_nh(n, h) * Wh(h)
 Next
 SRSS = SRSS + SoHang(h) ^ 2
Next

a_rms = 1 / Sqr(2) * Sqr(SRSS)

Lưu ý các tổng (SRSS, SoHang (h)) gán bằng 0 trước khi bắt đầu mỗi vòng lặp cộng dồn vào tổng.

📜Dịch nôm:

với mỗi giá trị h chạy từ 1 đến H=4, lấy bình phương tổng $\sum_{n=1}^N\left(\mu_{e,n}\mu_{r,n}\frac{F_h}{M_n}D_{n,h}W_h\right)$, được 4 số hạng bình phương đó với mỗi h. Kết quả cuối cùng $a_{w,rms,e,r}$ bằng căn bậc hai của tổng 4 cái bình phương đó.


Trong đó số hạng $D_{n,h}$ cũng phụ thuộc 2 biến n, h, ta sẽ tính toán trước đưa nó thành 1 mảng hai chiều cho nhanh (xem phần dưới). Theo công thức

$$D_{n,h}=\frac{h^2\beta_n^2}{\sqrt{(1-h^2\beta_n^2)^2+(2h\zeta\beta_n^2)^2}}$$

Trong đó: $\beta_n= f_p/f_n $

For n = 1 To n1
 beta_n(n) = fp / fn(n, 1)
 For h = 1 To 4
  D_nh(n, h) = h ^ 2 * beta_n(n) ^ 2 / Sqr((1 - h ^ 2 * beta_n(n) ^ 2) ^ 2 + (2 * h * Damping * beta_n(n)) ^ 2)
 Next
Next

- Trường hợp dao động Tạm thời, đơn giản hơn vì chỉ có 1 vòng lặp theo dạng dao động n

$$a_{w,peak,e,r,n}=2\pi\sqrt{1-\zeta^2}\sum_{n=1}^{n_2}\mu_{e,n}\mu_{r,n}f_n\frac{F_I}{M_n}W_n$$

cách code cũng tương tự như trên.


Các lệnh còn lại cơ bản, đỡ phức tạp hơn:

🔍Tạo nút bấm “Tính toán”

Mục đích: vì vòng lặp khá nhiều, tốn bộ nhớ và thời gian của máy tính xử lý. Nên chỉ khi nào ta đã nhập xong hết dữ liệu đầu vào (vốn gồm nhiều dòng theo từng mode dao động xuất ra từ ETABS). Khi đó người dùng bấm vào nút thì Excel mới thực hiện tính toán theo chương trình con Sub Button_Click().

Cách làm: vào tab “Developer”> Button. Vẽ nút hình chữ nhật vào trong bảng tính, nhấn sửa text hiển thị trong nút này. Ta thấy nó giống như nút bấm của các cửa sổ.

Sau đó chuột phải, Assign Macro, chọn macro name có đuôi “_Click()” đại diện cho lệnh thực hiện khi bấm nút bằng chuột.


🔍If… Then… Else

Câu lệnh điều kiện, ý nghĩa giống hệt lệnh If của Pascal: Nếu…Thì…Ngược lại thì… Cú pháp VBA chỉ thêm End If để kết thúc câu lệnh

🔍Dim…As

Khai báo tên biến. Kiểu dữ liệu của biến gồm bao gồm:

🔍Integer: số nguyên

🔍Double: số thực

🔍Variant: kiểu bất kỳ

Muốn khai báo mảng, chỉ việc thêm () vào sau tên biến.


🔍ReDim

Reset khai báo lại các biến và mảng. Câu lệnh này rất lợi hại so với Pascal để thay đổi kích thước mảng.

Ví dụ:

ReDim D_nh(n1, 4): thay đổi kích thước mảng D_nh thành n1 hàng và 4 cột. n1 tuỳ thuộc vào kết quả tính toán.


🔍Const: Khai báo hằng số

trong ví dụ ta dùng cho số $\pi$. Thật khó hiểu tại sao VBA không có sẵn hằng số này😔


🔍Function: tạo hàm bất kỳ

Ý nghĩa cũng giống trong Pascal. Cú pháp cơ bản:


Function tên hàm (danh sách biến) As kiểu dữ liệu

<Các câu lệnh>…

tên hàm = công thức

End Function


Trong ví dụ, một số công thức chia thành nhiều trường hợp khác nhau, ta sẽ đưa thành hàm. Như $W_g, W_b, W_d$ sẽ là các hàm theo tần số f (f là biến)

$$W_g=\begin{cases}

0,5\sqrt{f} &\text{nếu 1Hz < $f$ < 4Hz} \\[2ex]

1,0 &\text{nếu 4Hz ≤ $f$ ≤ 8Hz} \\[2ex]

8/f &\text{nếu $f$ > 8Hz}

\end{cases}$$


$$W_d=\begin{cases}

0,4 &\text{nếu 1Hz < $f$ < 2Hz} \\[2ex]

\frac{f}5 &\text{nếu 2Hz ≤ $f$ < 5Hz} \\[2ex]

1,0 &\text{nếu 5Hz ≤ $f$ ≤ 16Hz } \\[2ex]

\frac{16}f &\text{nếu $f$ > 16Hz}

\end{cases}$$


$$W_d=\begin{cases}

1,0 &\text{nếu 1Hz < $f$ < 2Hz} \\[2ex]

2/f &\text{nếu $f$ ≥ 2Hz}

\end{cases}$$

Trong các hàm này sử dụng lệnh If… Then… Else để chia theo các trường hợp của f như trên.

Một số công thức đơn giản hơn, như $f_n,M_n,\alpha_h$, ta đưa vào mảng cho nhanh.


🔍Lấy input từ các ô dữ liệu nhập vào các biến, sử dụng lệnh:

Range (“tên ô”).Value

Tên ô lấy theo ký hiệu ô của Excel, ví dụ như “B2”, hoặc “B21”,”B60” cho 1 vùng các ô.

Cần lưu ý kiểm tra kỹ nếu có sửa chèn dòng, cột trong bảng tính, dữ liệu có thể bị nhảy. Vì Tên ô là tuyệt đối, có thể giá trị của ô này đã bị nhảy từ ô khác sang rồi.


🔍= lệnh gán, giống “:=” của Pascal


🔍Sqr: hàm tính căn bậc hai


🔍Xuất kết quả trở lại bảng Excel:

Cách làm đơn giản là gán Range theo hướng ngược lại (ở ví dụ là lấy lại giá trị $a_{rms}$ tính được vào ô I4 với trường hợp liên tục, I6 với trường hợp tạm thời)


…

Nếu chưa rõ cú pháp lệnh nào, các bạn google “VBA syntax” + tên lệnh để tra nha🥸

Bẫy lỗi

Với người mới lập trình, chắc cú không bao giờ chỉ viết code liền tù tì 1 lần là chạy ngon lành đúng kết quả. Sẽ luôn có lỗi xảy ra, như là đi đường phải có ổ gà vậy.

Kinh nghiệm của tôi là viết được 1 vài dòng là chạy thử để kiểm tra lỗi.

Ngoài các lỗi cú pháp, lỗi công thức không đúng làm VBA chạy được nhưng kết quả nhiều khi không đúng với tính tay (nên tính tay độc lập để kiểm tra, chứ sai công thức khéo sập nhà đó😵).

Trong cửa sổ Microsoft Visual Basic ta sẽ dùng đến menu “Debug”:

- Toggle Breakpoint: bắt VBA dừng ở dòng đang nghi ngờ cần kiểm tra

- Add Watch: kiểm tra giá trị tính được của một biến ta nhập vào, hoặc

- Quick Watch: xem giá trị của biến tại vị trí con trỏ text trong cửa sổ code

Nói chung là muôn hình vạn trạng lỗi, chỉ có bắt tay làm trực tiếp và cần thời gian bẫy lỗi thôi. Phần thưởng cho sự kiên trì là càng làm nhiều bạn sẽ ít gặp lỗi hơn.


Một số lỗi thông thường vấp phải, các bạn có thể google “VBA các lỗi thường gặp”.

Kết

Khi đã lập trình được cái đầu tiên rồi, ta thấy để đáp ứng nhu cầu kỹ thuật của nghề xây dựng cũng không có gì cao siêu cả. Dần dần sẽ làm được tự động hoá những thứ trong nghề như:

- Vẽ biểu đồ tương tác cột bê tông cốt thép chịu uốn lệch tâm xiên theo TCVN 5574:2018

- Lập trình LISP thống kê cốt thép tự động trong CAD, cho những cấu kiện nhàm chán nhất là dầm, sàn, …

- Lập trình add in cho Revit ra bản vẽ cốt thép rồi thống kê schedule tự động

- Lập trình add in cho Etabs để tính toán các cấu kiện,…

Nếu bạn hứng thú với nội dung nào và muốn tôi viết bài hướng dẫn lập trình xin để lại comment ha👍


🎁 đính kèm là file excel theo ví dụ, vô Alt-F11 để xem phần code VBA nhé. Ví dụ này không chú trọng vào phần trình bày bảng tính, các bạn xem phần 1 về cách trình bày bảng tính in ra nhe.

Một ví dụ nữa trong blog là lập biểu đồ tương tác 1 phương tính cột lệch tâm xiên. Phần code VBA nằm trong file excel đính kèm rồi.