Viết hàm tự tạo theo kiểu Excel 365

Ngô Hải Đăng · 23/10/20

Nghiên cứu trên diễn đàn thì phát hiện được cái Application.Caller và sau đây là ý tưởng của mình:
1. Code trên ThisWorkbook

Mã:

Option Explicit

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    If IsUDF Then
        SetResult
        rCaller.Formula = sFormula
    End If
End Sub

Private Sub SetResult()
    Dim r0&, c0&
    On Error Resume Next
    r0 = UBound(aResult, 1) - LBound(aResult, 1)
    c0 = UBound(aResult, 2) - LBound(aResult, 2)
    On Error GoTo 0
    If c0 = 0 Then
        rCaller.Resize(1, r0 + 1) = aResult
    Else
        rCaller.Resize(r0 + 1, c0 + 1) = aResult
    End If
End Sub

2. Code trên Module

Mã:

Option Explicit

Public IsUDF As Boolean
Public rCaller As Range
Public aResult As Variant
Public sFormula As String

Function MyUDF()
    If IsUDF Then
        MyUDF = aResult
        IsUDF = False
        Set rCaller = Nothing
        If IsArray(aResult) Then Erase aResult Else aResult = Empty
    Else
        IsUDF = True
        Set rCaller = Application.Caller
        sFormula = rCaller.Formula
        
        'Dim tmp As String: tmp = "1 GIA TRI"
        'Dim tmp: tmp = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9)
        Dim tmp(10, 15) As Long
        aResult = tmp
    End If
End Function

3. Gõ =MyUDF() trên Sheet để test.

Có thể thử với kết quả là 1 giá trị, mảng 1 chiều và mảng 2 chiều. Mong được học hỏi thêm kinh nghiệm từ mọi người.

batman1 · 26/10/20

VetMini đã viết:
Ở trong môi trường macro, Function muốn làm gì thì làm.
Khi Function được đưa lên bảng tính thì mới phải tuân thủ theo cái giới hạn kia. Theo tôi thì đây là luật có từ đời Excel 4, hoặc cổ lắm thì cũng là Excel 95/97. Hồi ấy kiểm soát bộ nhớ rất khó (điển hình, Windows muốn đưa lên mấy cái cửa sổ debug cũng không dễ) cho nên giới sử dụng Excel cho đó là điều cần thiết.

Đại khái thì khi một Function được dùng ở bảng tính thì nó phải đi qua một lớp bảo vệ. Trước mắt thì lớp bảo vệ này có nhiệm vụ biến Function thành công cụ làm việc in hệt như hàm thông thường của bảng tính. Và vì vậy nó có hai công việc nhiển nhiên:
1. không cho phép sửa đổi bảng tính.
2. không cho phép crash. Nói cách khác, lỗi sẽ bị bẫy hết.

Những tay nghề xịn thì có cách đi vòng qua lớp bảo vệ này. Có lẽ vì vậy mà MS cứ để mặc câu chuyện diễn tiến.

Tôi không đi vào chi tiết. Chỉ thống nhất với nhau là bác Bill cấm. Còn tại sao, lý do lý trấu không bàn.
Ý tôi là bác Bill là tác giả code nên nếu bác ta muốn thì bác ta có quyền (ai trên bác để cấm?), bác biết phải sửa gì, ở đâu, sửa thế nào, và tôi tin là bác có đủ trình độ để chỉnh sửa code của chính mình. Thế thôi.

HieuCD · 26/10/20

batman1 đã viết:
Khó so sánh thì đúng rồi vì nhiều khi quan điểm khác nhau.
Việc đẩy dữ liệu đang có xuống dưới có khó không? Không khó, và trên GPE chốc chốc lại có yêu cầu như thế. Không khó, mà chỉ là không được phép. Tức bạn tự làm được nhưng anh Bill chỉ cho phép bạn làm trong SUB. Khi bạn làm trong FUNCTION thì anh Bill tuýt còi. Như vậy việc "đẩy dữ liệu xuống dưới" không khó, chỉ là anh Bill không cho phép làm trong FUNCTION. Chắc chắn việc làm trên đã "xung đột" với cái gì đấy trong việc quản lý, xử lý dữ liệu. Là người tạo ra bảng tính chắc chắn anh Bill biết "xung đột" đó nằm ở đâu, mặt mũi thế nào. Là người viết code của bảng tính anh Bill biết dữ liệu được đọc vào bộ nhớ như thế nào, chúng được tổ chức trong bộ nhớ ra sao, được phát hiện thay đổi và chỉnh sửa thế nào. Anh Bill biết từng đường đi lối lại trong code của mình mà không biết chỉnh sửa code đó để cho phép "đẩy dữ liệu xuống dưới"? Phải chăng ở giai đoạn thời gian đó anh Bill có quan điểm khác? Là không chấp nhận việc "đẩy dữ liệu xuống dưới" vì nó phá vỡ cấu trúc dữ liệu, sự tương quan giữa chúng?

Cứ thấy người khác không làm cái việc mình làm thì nghĩ là người ta không biết làm? Tôi không biết làm, bạn không biết làm, nhưng các ông lớn công nghệ, các đầu óc lập trình siêu đẳng của nhân loại không biết làm? Chắc ai đó định đùa thôi. Thay vào đó nên mừng thầm là người ta không làm hết những việc đó nên mình mới có cơ hội kiếm tiền.

Tôi có thể tiếp tục tham gia tranh luận với các bạn khác, còn tranh luận với Tuân tôi đã dừng rồi. Những cái trên tôi không viết cho Tuân. Nói rõ thế để tránh hiểu lầm.

Với Ngài Bill giải quyết xung đột (nếu có) là chuyện nhỏ, function trên bảng tính là thao tác thủ công trên vùng chọn, muốn đẩy xuống đẩy ngang tới đâu đã có các công cụ khác, chỉ cần thêm vài thao tác copy là xong, hoặc chọn tool Table để tự điền công thức xuống. Nếu cài sẵn chức năng nầy vào function cấu trúc lệnh sẽ phức tạp, quan trọng là tốc độ xử lý giảm đáng kể, khách hàng la khắp làng xóm "Excel quá tệ chạy như rùa bò" rủ nhau chạy sang anh Google.
Túm lại lợi nhỏ xíu hại to đùng

Ngô Hải Đăng · 26/10/20

Trong quá trình viết code thì mình chợt nhận ra là thay vì viết nhiều hàm tự tạo thì chỉ cần viết 1 hàm hỗ trợ cho các hàm mảng là được rồi. Tận dụng luôn các hàm mảng của Excel. Một số hình ảnh sau khi sử dụng hàm hỗ trợ này:
1.

2.

3.

Một số hàm mảng khác chỉ cần nhập công thức vô 1 ô rồi CTR+SHIFT+ENTER là nó tự nhảy kết quả mà không cần phải chọn hết nguyên 1 vùng.

Kiều Mạnh · 26/10/20

Ngô Hải Đăng đã viết:
Trong quá trình viết code thì mình chợt nhận ra là thay vì viết nhiều hàm tự tạo thì chỉ cần viết 1 hàm hỗ trợ cho các hàm mảng là được rồi. Tận dụng luôn các hàm mảng của Excel. Một số hình ảnh sau khi sử dụng hàm hỗ trợ này:
1.
View attachment 248060
View attachment 248061
2.
View attachment 248062
View attachment 248064
3.
View attachment 248065
View attachment 248066
Một số hàm mảng khác chỉ cần nhập công thức vô 1 ô rồi CTR+SHIFT+ENTER là nó tự nhảy kết quả mà không cần phải chọn hết nguyên 1 vùng.

Ra rồi he ... chúc mừng nha

Kiều Mạnh · 26/10/20

Cách đây khoảng 1 tháng chi đó có ai đó keo viết rể thôi mà ... thế mà tới hôm nay vẫn chưa thấy úp phim buồn thế nhỉ ...

Ngô Hải Đăng · 26/10/20

Mình chỉ viết dựa trên những gì bác @Kiều Mạnh gợi ý, chưa đụng tới hàm API. Cách thức hoạt động hàm cũng tương tự như mấy hàm trong 365. Có thể bác Bill làm sẵn từ trước mà bác Bill ... quên. Cảm ơn bác Kiều Mạnh rất nhiều.

befaint · 26/10/20

Ngô Hải Đăng đã viết:
Cách thức hoạt động hàm cũng tương tự như mấy hàm trong 365

Trên Excel 365, khi xóa một phần kết quả thì có tự động điền lại phần đã xóa không bạn?

Kiều Mạnh · 26/10/20

Ngô Hải Đăng đã viết:
Mình chỉ viết dựa trên những gì bác @Kiều Mạnh gợi ý, chưa đụng tới hàm API. Cách thức hoạt động hàm cũng tương tự như mấy hàm trong 365. Có thể bác Bill làm sẵn từ trước mà bác Bill ... quên. Cảm ơn bác Kiều Mạnh rất nhiều.

Mạnh hỏi tư duy logic + trắc nghiệm chút he ... sau đó mạnh sẻ đánh giá cho điểm he
1/ Nhưng cái mạnh nói bạn thấy có đúng ko ... loanh quanh trên VBA thui
2/ Khi bạn viết ra như vậy bạn có xem thường bill là người đi sau và ko có tầm nhìn không !!!???
3/ bạn cảm thấy thế nào khi viết xong nó ... cảm giác thôi

quan trọng nhất câu thứ 2 ấy là mình đánh giá được bạn ngay và luôn

Nguyễn Duy Tuân · 26/10/20

Ngô Hải Đăng đã viết:
Mình chỉ viết dựa trên những gì bác @Kiều Mạnh gợi ý, chưa đụng tới hàm API. Cách thức hoạt động hàm cũng tương tự như mấy hàm trong 365. Có thể bác Bill làm sẵn từ trước mà bác Bill ... quên. Cảm ơn bác Kiều Mạnh rất nhiều.

Không giống gì Excel 365 cả. Mảng Excel 365 tạo ra bởi công thức ta không xóa được phần tử trong mảng, cũng như mảng tạo bởi CSTRL+SHIFT+ENTER. Những ai tìm hiểu mảng sẽ nhận thấy điều này ngay. Dù sao bạn cũng đã làm được chút xíu và tự thấy hứng thú là được rồi.

befaint · 26/10/20

Nguyễn Duy Tuân đã viết:
Mảng Excel 365 tạo ra bởi công thức ta không xóa được phần tử trong mảng, cũng như mảng tạo bởi CSTRL+SHIFT+ENTER.

Có chuẩn chỉ như vậy không anh?

Nguyễn Duy Tuân đã viết:
Dù sao bạn cũng đã làm được chút xíu và tự thấy hứng thú là được rồi.

Câu này nghĩa cụ thể là sao anh?

Kiều Mạnh · 26/10/20

Ngô Hải Đăng đã viết:
Mình chỉ viết dựa trên những gì bác @Kiều Mạnh gợi ý, chưa đụng tới hàm API. Cách thức hoạt động hàm cũng tương tự như mấy hàm trong 365. Có thể bác Bill làm sẵn từ trước mà bác Bill ... quên. Cảm ơn bác Kiều Mạnh rất nhiều.

eo ôi mới xem kỹ lại vẫn còn xài 2 cái dấu to sau đó sao ???
{ ............ }

Nguyễn Duy Tuân · 26/10/20

befaint đã viết:
Có chuẩn chỉ như vậy không anh?

Câu này nghĩa cụ thể là sao anh?

Đó là đặc điểm của Excel 365 cũng như mảng được điền vào vùng có nhiều ô bởi CTRL + SHIFT + ENTET. Vì bạn đó bảo như Excel 365 thì mình nói đặc điểm có nó mà thôi.

Kiều Mạnh · 26/10/20

bỏ 2 cái đấu to ấy đi chọn gọn he

Ngô Hải Đăng · 26/10/20

befaint đã viết:
Trên Excel 365, khi xóa một phần kết quả thì có tự động điền lại phần đã xóa không bạn?

Excel 365 thì em chỉ mới xem qua phần giới thiệu trên diễn đàn chứ chưa từng sử dụng bao giờ. Ở đây ý của em là giống cách thể hiện công thức trên 1 ô, các ô còn lại không có công thức. Còn vụ xóa rồi tự động điền lại phần đã xóa thì em không biết là trên Excel 365 có hay không nữa, chắc bác đã xài rồi nên mới hỏi đúng không?

Kiều Mạnh đã viết:
Mạnh hỏi tư duy logic + trắc nghiệm chút he ... sau đó mạnh sẻ đánh giá cho điểm he
1/ Nhưng cái mạnh nói bạn thấy có đúng ko ... loanh quanh trên VBA thui
2/ Khi bạn viết ra như vậy bạn có xem thường bill là người đi sau và ko có tầm nhìn không !!!???
3/ bạn cảm thấy thế nào khi viết xong nó ... cảm giác thôi

quan trọng nhất câu thứ 2 ấy là mình đánh giá được bạn ngay và luôn

1/ Những cái bác nói em thấy đúng ý thì em có like. Mà nói thật là trong chủ đề này em chỉ để ý những phần nào giúp cho em đạt được kết quả của mình, còn những chuyện tranh luận em chỉ đọc lướt qua. Nhiều khi em còn không hiểu là mọi người tranh luận cái gì nữa. Em chỉ nghĩ như vậy, Bill làm ra cái Excel + VBA, mọi người tùy theo khả năng của mình mà khai thác sức mạnh của nó thôi.
2/ Em đang viết bằng hàm của ổng thì sao dám coi thường ổng.
3/ Cảm thấy vui. Nhưng còn chưa ưng ý lắm cái xử lý mảng lúc gán kết quả xuống sheet.

Kiều Mạnh · 26/10/20

Ngô Hải Đăng đã viết:
Excel 365 thì em chỉ mới xem qua phần giới thiệu trên diễn đàn chứ chưa từng sử dụng bao giờ. Ở đây ý của em là giống cách thể hiện công thức trên 1 ô, các ô còn lại không có công thức. Còn vụ xóa rồi tự động điền lại phần đã xóa thì em không biết là trên Excel 365 có hay không nữa, chắc bác đã xài rồi nên mới hỏi đúng không?

1/ Những cái bác nói em thấy đúng ý thì em có like. Mà nói thật là trong chủ đề này em chỉ để ý những phần nào giúp cho em đạt được kết quả của mình, còn những chuyện tranh luận em chỉ đọc lướt qua. Nhiều khi em còn không hiểu là mọi người tranh luận cái gì nữa. Em chỉ nghĩ như vậy, Bill làm ra cái Excel + VBA, mọi người tùy theo khả năng của mình mà khai thác sức mạnh của nó thôi.
2/ Em đang viết bằng hàm của ổng thì sao dám coi thường ổng.
3/ Cảm thấy vui. Nhưng còn chưa ưng ý lắm cái xử lý mảng lúc gán kết quả xuống sheet.

9,5 điểm cho bạn .... xài đồ của bill ... chạy ké trên Ứng dụng của bill mà chê bill nữa thì chỉ là đồ bỏ đi thôi
và tư duy cũng chỉ dùng lại ở đó mà thôi .... trừ khi bạn viết chạy độc lập mọi thứ thì có thể chê bill cũng ko sao cả

Bạn rất tuyệt Vời

Ngô Hải Đăng · 26/10/20

Kiều Mạnh đã viết:
eo ôi mới xem kỹ lại vẫn còn xài 2 cái dấu to sau đó sao ???
{ ............ }

Còn nhưng chỉ có ở ô đầu tiên thôi. Các ô còn lại xóa thoải mái. Là vì em đang tận dụng các hàm trả về mảng của excel.

Nguyễn Duy Tuân · 26/10/20

Kiều Mạnh đã viết:
9,5 điểm cho bạn .... xài đồ của bill ... chạy ké trên Ứng dụng của bill mà chê bill nữa thì chỉ là đồ bỏ đi thôi
và tư duy cũng chỉ dùng lại ở đó mà thôi .... trừ khi bạn viết chạy độc lập mọi thứ thì có thể chê bill cũng ko sao cả

Bạn rất tuyệt Vời

Tôi có chút tò mò nên tôi hỏi riêng bạn:
Bạn ấy nói gì động chạm đến cái chữ màu đỏ đâu mà bạn nói gì ghê vậy? Không biết mọi người nghĩ sao chứ tôi thấy nghe thấy lạ lạ.
Nếu là tôi mà bị bạn nói vậy thì tôi cho rằng bạn ăn nói bừa bãi. Ở đây không ai chê đồ của Bill, có tôi nói là "chớm nở", còn vài đặc tính mà chưa thỏa mãn thì cũng là ý riêng của tôi thấy vậy. Trong việc sử dụng đánh giá sản phẩm, việc người ta nói tính năng này mới nhưng chưa được như ý tôi ý bạn là bình thường chứ không phải chê bai cả sản phẩm là đồ bỏ đi. Kẻ vô ơn nới ăn nói vậy.

Bạn có thể trả lời câu hỏi của tôi hoặc không. Tôi mong ý của bạn không liên quan đến tôi. Nếu có thì bạn muốn nói thêm gì tôi vẫn nghe và phản hồi trên tinh thần trao đổi cho rõ ràng.

Kiều Mạnh · 26/10/20

Nguyễn Duy Tuân đã viết:
Tôi có chút tò mò nên tôi hỏi riêng bạn:
Bạn ấy nói gì động chạm đến cái chữ màu đỏ đâu mà bạn nói gì ghê vậy? Không biết mọi người nghĩ sao chứ tôi thấy nghe thấy lạ lạ.
Nếu là tôi mà bị bạn nói vậy thì tôi cho rằng bạn ăn nói bừa bãi. Ở đây không ai chê đồ của Bill, có tôi nói là "chớm nở", còn vài đặc tính mà chưa thỏa mãn thì cũng là ý riêng của tôi thấy vậy. Trong việc sử dụng đánh giá sản phẩm, việc người ta nói tính năng này mới nhưng chưa được như ý tôi ý bạn là bình thường chứ không phải chê bai cả sản phẩm là đồ bỏ đi. Kẻ vô ơn nới ăn nói vậy.

Bạn có thể trả lời câu hỏi của tôi hoặc không. Tôi mong ý của bạn không liên quan đến tôi. Nếu có thì bạn muốn nói thêm gì tôi vẫn nghe và phản hồi trên tinh thần trao đổi cho rõ ràng.

ồ mà mình có nói chi tới bạn đâu he .... ta dừng lại ở đây sẻ rất tốt đấy
Mình ko nói gì thêm ... và để đấy .. thế thôi

Kiều Mạnh · 26/10/20

tới đó thôi ... thế là vừa tới đủ để hiểu và nhận ra vấn đề ... Mạnh dừng mọi cái ở đây
Còn ai thích bàn cứ bàn he ... -0-0-0-

ptm0412 · 26/10/20

Ngô Hải Đăng đã viết:
Excel 365 thì em chỉ mới xem qua phần giới thiệu trên diễn đàn chứ chưa từng sử dụng bao giờ. Ở đây ý của em là giống cách thể hiện công thức trên 1 ô, các ô còn lại không có công thức. Còn vụ xóa rồi tự động điền lại phần đã xóa thì em không biết là trên Excel 365 có hay không nữa, chắc bác đã xài rồi nên mới hỏi đúng không?

Hàm mảng 365 cho phép chèn dòng, xoá dòng ngang qua giữa mảng kết quả, Excel tự điền lại mảng nguyên vẹn như cũ
Trong các ô còn lại của mảng kết quả nhìn thấy công thức mờ hơn ô đầu tiên, nhưng khi click vào thì trên thanh công thức trống trơn. Cố tình xoá cũng được phục hồi. Tuy nhiên khi sửa lại thành dữ liệu khác thì nguyên mảng biến mất và bị lỗi #SPILL
Tôi có 1 bài nói về Tính chất động của hàm mảng, công thức mảng trong Excel 365 trong đó có đoạn cuối là:

Viết hàm tự tạo theo kiểu Excel 365

Thành viên hoạt động

Thành viên gạo cội

Chuyên gia GPE

Thành viên hoạt động

I don't program, I beat code into submission!!!

I don't program, I beat code into submission!!!

Thành viên hoạt động

|||||||||||||

I don't program, I beat code into submission!!!

Nghị Hách

|||||||||||||

I don't program, I beat code into submission!!!

Nghị Hách

I don't program, I beat code into submission!!!

Thành viên hoạt động

I don't program, I beat code into submission!!!

Thành viên hoạt động

Nghị Hách

I don't program, I beat code into submission!!!

I don't program, I beat code into submission!!!

Bad Excel Member

Facebook

Group