Chuyển công thức thành hàm UDF Vba

[hongphuong1997]

Cháu có công thức như sau:
(=MOD($E$24-$D$24;5)&MOD($E$24-$C$24;5)&","&MOD($D$24+$F$24;5)&MOD($C$24-$F$24;5)&","&MOD($D$24-$G$24;5)&MOD($C$24+$G$24;5)&","&MOD($D$24-$G$24;5)&MOD($C$24-$F$24;5)&","&MOD($D$24+$F$24;5)&MOD($C$24+$G$24;5))
Cháu đã mầy mò viết macro như sau mừ nó không chạy, vậy khính mong các bác cùng các anh chị giúp đỡ ạ.
Cháu xin cảm ơn ạ.

Function MyCustomFunction(D24 As Range, E24 As Range, C24 As Range, F24 As Range, G24 As Range) As String
    Dim part1 As String, part2 As String, part3 As String, part4 As String, part5 As String
    
    ' Tính từng phần của công thức MOD và ghép chúng lại
    part1 = WorksheetFunction.Mod(E24.Value - D24.Value, 5) & WorksheetFunction.Mod(E24.Value - C24.Value, 5)
    part2 = WorksheetFunction.Mod(D24.Value + F24.Value, 5) & WorksheetFunction.Mod(C24.Value - F24.Value, 5)
    part3 = WorksheetFunction.Mod(D24.Value - G24.Value, 5) & WorksheetFunction.Mod(C24.Value + G24.Value, 5)
    part4 = WorksheetFunction.Mod(D24.Value - G24.Value, 5) & WorksheetFunction.Mod(C24.Value - F24.Value, 5)
    part5 = WorksheetFunction.Mod(D24.Value + F24.Value, 5) & WorksheetFunction.Mod(C24.Value + G24.Value, 5)
    
    ' Ghép các phần lại thành chuỗi kết quả
    MyCustomFunction = part1 & "," & part2 & "," & part3 & "," & part4 & "," & part5
End Function

 

[huuthang_bd]

Thử sửa WorksheetFunction.mod(E24.Value - D24.Value, 5) thành ((E24.Value - D24.Value) mod 5) và sửa tương tự cho những chỗ khác.

 

[hongphuong1997]

Thử sửa WorksheetFunction.mod(E24.Value - D24.Value, 5) thành ((E24.Value - D24.Value) mod 5) và sửa tương tự cho những chỗ khác.

Cháu cảm ơn bác ạ, đúng rùi bác oi, thế mừ cháu nghĩ mãi không ra.

 

[huhumalu]

Function MyCustomFunction(D24 As Range, E24 As Range, C24 As Range, F24 As Range, G24 As Range) As String
Dim part1 As String, part2 As String, part3 As String, part4 As String, part5 As String

Mà kết quả xuất ra string để làm gì nhỉ ?

 

[hongphuong1997]

Mà kết quả xuất ra string để làm gì nhỉ ?

Vì cháu nối thành chuỗi mà bác, vậy như nào thì đúng bác oi?

 

[huhumalu]

Vì cháu nối thành chuỗi mà bác, vậy như nào thì đúng bác oi?

À, mình nhầm, do mải nhìn cái MOD, nghĩ là kết quả trả về số.
Ghép chuỗi thì vậy đúng rồi.

 

[bebo021999]

Công thức này có thể rút gọn lại như sau:

=MyCustomFunction(C24:G24)

Option Explicit
Function MyCustomFunction(rng As Range) As String
Dim st As String, C, D, E, F, G
With rng
    C = .Cells(1, 1): D = .Cells(1, 2)
    E = .Cells(1, 3): F = .Cells(1, 4)
    G = .Cells(1, 5)
End With
st = (E - D) Mod 5 & ",": st = st & (E - C) Mod 5 & ","
st = st & (D + F) Mod 5 & ",": st = st & (C - F) Mod 5 & ","
st = st & (D - G) Mod 5 & ",": st = st & (C + G) Mod 5 & ","
st = st & (D - G) Mod 5 & ",": st = st & (C - F) Mod 5 & ","
st = st & (D + F) Mod 5 & ",": st = st & (C + G) Mod 5
MyCustomFunction = st
End Function

 

[hongphuong1997]

Công thức này có thể rút gọn lại như sau:

=MyCustomFunction(C24:G24)

Option Explicit
Function MyCustomFunction(rng As Range) As String
Dim st As String, C, D, E, F, G
With rng
    C = .Cells(1, 1): D = .Cells(1, 2)
    E = .Cells(1, 3): F = .Cells(1, 4)
    G = .Cells(1, 5)
End With
st = (E - D) Mod 5 & ",": st = st & (E - C) Mod 5 & ","
st = st & (D + F) Mod 5 & ",": st = st & (C - F) Mod 5 & ","
st = st & (D - G) Mod 5 & ",": st = st & (C + G) Mod 5 & ","
st = st & (D - G) Mod 5 & ",": st = st & (C - F) Mod 5 & ","
st = st & (D + F) Mod 5 & ",": st = st & (C + G) Mod 5
MyCustomFunction = st
End Function

Cháu cảm ơn bác ạ, quá tuyệt vời bác oi.

 

[VetMini]

Hình như trong WorksheetFunction không có hàm MOD. Tôi chưa tìm ra nguyên do, nhưng thử thì thấy cái bảng xổ xuống (list) không có Mod.
Để tạm thời khắc phục điểm này ta có thể dùng Evaluate (hơi chậm)
Thay WorksheetFunction.MOD(a, b) bằng Evaluate("MOD(a, b)")

MyCustomFunction = Evaluate("=MOD($E$24-$D$24;5)&MOD($E$24-$C$24;5)&","&MOD($D$24+$F$24;5)&MOD($C$24-$F$24;5)&","&MOD($D$24-$G$24;5)&MOD($C$24+$G$24;5)&","&MOD($D$24-$G$24;5)&MOD($C$24-$F$24;5)&","&MOD($D$24+$F$24;5)&MOD($C$24+$G$24;5)")

----------------------

' Tính từng phần của công thức MOD và ghép chúng lại
part1 = Evaluate("=Mod(" & E24.Value - D24.Value & ", 5)") _
& Evaluate("=Mod(" & E24.Value - C24.Value & ", 5)")
part2 = tương tự

 

[hongphuong1997]

Hình như trong WorksheetFunction không có hàm MOD. Tôi chưa tìm ra nguyên do, nhưng thử thì thấy cái bảng xổ xuống (list) không có Mod.
Để tạm thời khắc phục điểm này ta có thể dùng Evaluate (hơi chậm)
Thay WorksheetFunction.MOD(a, b) bằng Evaluate("MOD(a, b)")

MyCustomFunction = Evaluate("=MOD($E$24-$D$24;5)&MOD($E$24-$C$24;5)&","&MOD($D$24+$F$24;5)&MOD($C$24-$F$24;5)&","&MOD($D$24-$G$24;5)&MOD($C$24+$G$24;5)&","&MOD($D$24-$G$24;5)&MOD($C$24-$F$24;5)&","&MOD($D$24+$F$24;5)&MOD($C$24+$G$24;5)")

----------------------

' Tính từng phần của công thức MOD và ghép chúng lại
part1 = Evaluate("=Mod(" & E24.Value - D24.Value & ", 5)") _
& Evaluate("=Mod(" & E24.Value - C24.Value & ", 5)")
part2 = tương tự

Cháu cảm ơn bác rất nhiều ạ.
Chậm cũng không sao cháu dùng công thức quen rồi.
Cách viết này giống như viết hàm trên bảng tinh vì vậy rất phù hợp đối với cháu.
Nhưng bác oi, có phải khai báo biến và các tham số không hở bác?

Bài đã được tự động gộp: 6/9/24

@VetMini bác ơi cháu nhìn lại rồi, nếu dùng udf trên thì sẽ không làm được tổng quát khi địa chỉ các ô thay đổi.
Vì vậy cháu thấy không khả thi.

 

[bebo021999]

Nhưng bác oi, có phải khai báo biến và các tham số không hở bác?

Cũng ngộ thiệt, thường dùng hàm khi cần truyền tham số, hiểu nôm na là tham số sẽ thay đổi (biến động) và hàm để tính toán ra kết quả.
Ở đây lại cố định luôn dòng 24, cột C,D,E... thế thì cần gì hàm nữa

 

[hongphuong1997]

Cũng ngộ thiệt, thường dùng hàm khi cần truyền tham số, hiểu nôm na là tham số sẽ thay đổi (biến động) và hàm để tính toán ra kết quả.
Ở đây lại cố định luôn dòng 24, cột C,D,E... thế thì cần gì hàm nữa

Vâng ạ, vì vậy cháu mới đăng hỏi các bác và các anh chị mà.
Nếu như cháu để công thức thì cũng được và vác đi đâu cũng được.
Nhưng tội là cồng kềnh quá bác oi.
Bác xem cháu làm như này đã đúng chưa và góp ý cho cháu với bác nhé.

Bài đã được tự động gộp: 6/9/24

@bebo021999 bác oi bác xem giúp cháu có sai sót chỗ nào bác hướng dẫn cháu với bác nhé.

Function MyCustomFunction(a1 As Range, a2 As Range, a3 As Range, a4 As Range, a5 As Range) As String
    Dim part1, part2, part3, part4, part5
    
    part1 = (a3.Value - a2.Value + 5) Mod 5 & (a3.Value - a1.Value + 5) Mod 5
    part2 = (a2.Value + a4.Value + 5) Mod 5 & (a1.Value - a4.Value + 5) Mod 5
    part3 = (a2.Value - a5.Value + 5) Mod 5 & (a1.Value + a5.Value + 5) Mod 5
    part4 = (a2.Value - a5.Value + 5) Mod 5 & (a1.Value - a4.Value + 5) Mod 5
    part5 = (a2.Value + a4.Value + 5) Mod 5 & (a1.Value + a5.Value + 5) Mod 5
    
    MyCustomFunction = part1 & "," & part2 & "," & part3 & "," & part4 & "," & part5
End Function

 

[VetMini]

Cháu cảm ơn bác rất nhiều ạ.
Chậm cũng không sao cháu dùng công thức quen rồi.
Cách viết này giống như viết hàm trên bảng tinh vì vậy rất phù hợp đối với cháu.
Nhưng bác oi, có phải khai báo biến và các tham số không hở bác?

Tôi cóp cả cái công thức của bạn, không sửa 1 ký tự.
Vì vậy, với cách 1, không lý do gì phải khai báo biến và tham số.
Với cách 2, phải khai tham số đầy đủ như code ở bài #1 của bạn.

@VetMini bác ơi cháu nhìn lại rồi, nếu dùng udf trên thì sẽ không làm được tổng quát khi địa chỉ các ô thay đổi.
Vì vậy cháu thấy không khả thi.

Nhìn cái gì vậy?
Ví dụ, tham thứ nhất bạn đặt tên là D24. Bên trong hàm, D24 là một biến, nó là cái range bạn nạp cho tham thứ nhất chứ không phải là ô dòng 24, cột D - Cells(24, "D"). Nếu bạn nạp $D$24 lúc gọi hàm thì nó là Cells(24, "D"), nhưng nếu bạn nạp $A$1 thì nó là Cells(1, "A")

' Tính từng phần của công thức MOD và ghép chúng lại
part1 = Evaluate("=Mod(" & E24.Value - D24.Value & ", 5)") _
& Evaluate("=Mod(" & E24.Value - C24.Value & ", 5)")

Ở biểu thức trên, bạn dùng Value của biến (range) ấy cho nên bạn nạp ô nào nó lấy giá trị ô nấy thôi.
Nếu không dùng .Value thì bạn phải dùng .Address. Như ví dụ trên, nếu bạn nạp tham 1 là $A$1 thì bên trong hàm, D24.Address = $A$1

' Tính từng phần của công thức MOD và ghép chúng lại
part1 = Evaluate("=Mod(" & E24.Address & " - " & D24.Address & ", 5)") _
& Evaluate("=Mod(" & E24.Address & " - " & C24.Address & ", 5)")

Biểu thức trên cuối cùng sẽ tương đương với:
=Evaluate("=Mod($B$1 - $A$1, 5") & Mod($B$1 - $X$1, 5")
$B$1 là trị ở tham thứ 2, $X$1 là trị ở tham thứ 3

 

[ptm0412]

@bebo021999 bác oi bác xem giúp cháu có sai sót chỗ nào bác hướng dẫn cháu với bác nhé.

Vấn đề là khi có hàm rồi sử dụng trên sheet như thế nào:
- Gõ công thức = MyCustomFunction(...) với cái gì bên trong?
- Gõ công thức cho chỉ 1ô hay nhiều ô? Có kéo công thức xuống hay sang ngang không?
- Các tham số là các ô liền kề hay các ô rời rạc?

Sai sót chỗ nào thì cứ thử rồi so sánh với công thức thường hoặc tính tay.

 

[VetMini]

À, mình nhầm, do mải nhìn cái MOD, nghĩ là kết quả trả về số.
Ghép chuỗi thì vậy đúng rồi.

Ghép chuỗi thì kết quả đúng. Nhưng lập trình thì ghép nhiều như vậy cần khác đi một chút
Thay vì Dim part1, part2,...
Thì Dim prt(1 To 5)
prt(1) = ...
prt(2) = ...
...
GopLai = Join(prt, ",")

Code như bài #7 lại càng dễ hơn:
GopLai = Join(Array(bieuthuc1&bieuthuc11, bieuthuc2&bieuthuc21, bieuthuc3&bieuthuc31, bieuthuc4&bieuthuc41, bieuthuc5&bieuthuc51), ",")

 

[hongphuong1997]

Ghép chuỗi thì kết quả đúng. Nhưng lập trình thì ghép nhiều như vậy cần khác đi một chút
Thay vì Dim part1, part2,...
Thì Dim prt(1 To 5)
prt(1) = ...
prt(2) = ...
...
GopLai = Join(prt, ",")

Code như bài #7 lại càng dễ hơn:
GopLai = Join(Array(bieuthuc1&bieuthuc11, bieuthuc2&bieuthuc21, bieuthuc3&bieuthuc31, bieuthuc4&bieuthuc41, bieuthuc5&bieuthuc51), ",")

Trời ạ, viết được như bác thì cháu thành cao thủ roài bác oi.
Cháu chưa viết được như vậy.
Bác làm ơn hoàn chỉnh cho cháu bài này theo hướng làm như này của bác với ạ.

 

[huhumalu]

Trời ạ, viết được như bác thì cháu thành cao thủ roài bác oi.
Cháu chưa viết được như vậy.
Bác làm ơn hoàn chỉnh cho cháu bài này theo hướng làm như này của bác với ạ.

Từ gợi ý của anh VetMini thì bạn làm theo đi, cũng là tốt để bạn tìm hiểu về việc khai báo biến.
Thay vì Thay vì Dim part1, part2,... là khai báo không tường minh.
Đúng thì bạn định gán chuỗi cho biến thì nên chỉ thị là Dim part1 as String
Nhưng ví dụ bạn có 100 cái part, thì chả lẽ ngồi dim hoài ;Do vậy nên gán nó vào mảng 1 chiều Thì Dim prt(1 To 5) rồi sau trích xuất ra.
Trong bài này thì có thể chưa thấy hết, nhưng sau đối với "dự án" lớn hơn, thì sau việc quản lý biến sao cho hợp lý thì sẽ thấy hiệu quả. Viết xong vài năm sau quay lại kg hiểu tại sao luôn

 

[ptm0412]

Cháu chưa viết được như vậy.

Khoan nói đến việc viết với thủ thuật nào, trước tiên phải xác định các mục như bài 14 để khai báo tham số, là dòng đầu tiên của hàm.

- Gõ công thức = MyCustomFunction(...) với cái gì bên trong?
Gõ công thức với giá trị số hay tham chiếu ô? Việc này để khai báo kiểu của tham số.

- Gõ công thức cho chỉ 1 ô hay nhiều ô? Có kéo công thức xuống hay sang ngang không?
Nếu chỉ 1 ô thì không cần hàm VBA. Có kéo công thức xuống hoặc sang ngang thì tham số hàm cần phải là các ô liền kề, hoặc nếu rời rạc phải đúng quy luật dòng hoặc cột

- Các tham số là các ô liền kề hay các ô rời rạc?
Nếu các ô tham chiếu là liền kề thì chỉ khai báo 1 tham số là range như bài 7. Nếu là các ô rời rạc thì mới phải khai báo từng tham số (theo đầu bài là 5). Hãy tưởng tượng người dùng gõ tên hàm mở ngoặc xong phải click chọn 5 ô, gõ dấu phảy 4 lần.

Tên hàm cũng phải ngắn gọn và có ý nghĩa theo nhu cầu tính toán. Đặt tên MyCustomFunction thì biết hàm dùng làm gì? Rồi khi có nhu cầu viết hàm mới chắc lại đặt tên hàm là MyCustomFunction2, MyCustomFunction3 hay sao?

 

[hongphuong1997]

Khoan nói đến việc viết với thủ thuật nào, trước tiên phải xác định các mục như bài 14 để khai báo tham số, là dòng đầu tiên của hàm.

- Gõ công thức = MyCustomFunction(...) với cái gì bên trong?
Gõ công thức với giá trị số hay tham chiếu ô? Việc này để khai báo kiểu của tham số.

- Gõ công thức cho chỉ 1 ô hay nhiều ô? Có kéo công thức xuống hay sang ngang không?
Nếu chỉ 1 ô thì không cần hàm VBA. Có kéo công thức xuống hoặc sang ngang thì tham số hàm cần phải là các ô liền kề, hoặc nếu rời rạc phải đúng quy luật dòng hoặc cột

- Các tham số là các ô liền kề hay các ô rời rạc?
Nếu các ô tham chiếu là liền kề thì chỉ khai báo 1 tham số là range như bài 7. Nếu là các ô rời rạc thì mới phải khai báo từng tham số (theo đầu bài là 5). Hãy tưởng tượng người dùng gõ tên hàm mở ngoặc xong phải click chọn 5 ô, gõ dấu phảy 5 lần.

Tên hàm cũng phải ngắn gọn và có ý nghĩa theo nhu cầu tính toán. Đặt tên MyCustomFunction thì biết hàm dùng làm gì? Rồi khi có nhu cầu viết hàm mới chắc lại đặt tên hàm là MyCustomFunction2, MyCustomFunction3 hay sao?

Cú pháp của hàm như này bác oi
MyCustomFunction=(a1;b1;c1;d1;e1)
Hoặc là các ô rời rạc đều được hết
"Tên hàm cũng phải ngắn gọn và có ý nghĩa theo nhu cầu tính toán. Đặt tên MyCustomFunction thì biết hàm dùng làm gì? Rồi khi có nhu cầu viết hàm mới chắc lại đặt tên hàm là MyCustomFunction2, MyCustomFunction3 hay sao?"
Đúng roài bác oi.

 

[ptm0412]

Cú pháp của hàm như này bác oi

Bạn cần đọc hết và đọc kỹ.