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

[Ngô Hải Đăng]

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

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

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.

 

[ptm0412]

@ptm0412
Bác cho em xem demo ấy ra sao?

Cái demo ấy là tôi xem anh @Nguyễn Duy Tuân trình bày trên máy tính và chiếu trực tiếp, nhân dịp sinh nhật GPE 14 ở Hà nội

 

[batman1]

Add-in A-Tools tạo ra hàm mảng cơ động từ cách đây hơn 10 năm, từ cái thời Excel 97 thì đương nhiên Bill chưa hề có ý tưởng về hàm mảng, mãi gần đây GoogleSheet và Excel 365 mới có đưa ra cách làm việc hàm mảng. Tuy nhiên các cách hiện nay cả trên Excel 365 và GoogleSheets đều chỉ mới chớm nở, làm giản đơn, thô chứ chưa phải linh hoạt.

Thực tế là 1 triệu người thì có 2 triệu nhu cầu, mỗi người có những yêu cầu khác nhau. Có những cái mình tưởng là quan trọng thì người khác không cho là vậy và ngược lại. Tại sao thế giới không có Add-in A-Tools mà người ta phát triển ầm ầm? Tôi không nghĩ là người Ba Lan hàng ngày khóc ròng vì khổ quá do không có Add-in A-Tools. Mà Ba Lan chưa phải là nhất.
Không thể làm mọi cái để thỏa mãn nhu cầu của tất cả triệu, tỷ người được. Và có thể ở giai đoạn nào đó thì người ta bỏ qua vấn đề A, mãi tới những năm sau sau họ mới xét vấn đề A. Đó là chuyện thường. Mình cho vấn đề A là nhất nhưng ở một giai đoạn nào đó họ không cho là thế. Và còn điều nữa. Nếu người ta làm từ A tới Z, từ Z tới A và nhiều hơn nữa thì làm sao còn đường sống cho những công ty như công ty của bạn? Tôi thấy riêng ở Ba Lan người ta không có sản phẩm của bạn mà người ta vẫn làm việc được, chả ai kêu khổ. Không phải họ chịu được khổ. Rất có thể họ có những thứ khác không kém gì Add-in A-Tools?

Mà chả riêng gì Ba Lan. Tôi không nghĩ là mọi công ty ở Việt Nam đều dùng Add-in A-Tools. Nếu đúng thế thì tại sao họ không kêu ca nhỉ? Tại sao họ khổ thế mà vẫn cam chịu nhỉ?

 

[batman1]

- Khi giãn dòng hoặc cột, hàm mảng của 365 nếu đụng dữ liệu khác bên dưới hoặc bên phải sẽ bị lỗi Spill, ATool thì không. Đây là niềm tự hào của anh Tuân và đó là chính đáng.

Tùy theo nhu cầu, tiêu chí của mỗi người thôi anh. Có thể 365 nếu đụng dữ liệu khác bên dưới hoặc bên phải sẽ bị lỗi Spill vì nó KHÔNG CHẤP NHẬN cách hành xử đó. Nếu đẩy dữ liệu đang có xuống dưới thì sẽ làm hỏng cấu trúc, tương quan giữa các dữ liệu. Là tôi tôi cũng không chấp nhận cách hành xử như thế. Đừng thấy người ta không làm như mình mà nghĩ là người ta không nghĩ được dài như mình, không có năng lực xử lý những cái khó như mình. Rất có thể nguyên nhân cực kỳ đơn giản: người ta có "phương châm" khác mình?

 

[Kiều Mạnh]

Tùy theo nhu cầu, tiêu chí của mỗi người thôi anh. Có thể 365 nếu đụng dữ liệu khác bên dưới hoặc bên phải sẽ bị lỗi Spill vì nó KHÔNG CHẤP NHẬN cách hành xử đó. Nếu đẩy dữ liệu đang có xưuống dưới thì sẽ làm hỏng cấu trúc, tương quan giữa các dữ liệu. Là tôi tôi cũng không chấp nhận cách hành xử như thế. Đừng thấy người ta không làm như mình mà nghĩ là người ta không nghĩ được dài như mình, không có năng lực xử lý những cái khó như mình. Rất có thể nguyên nhân cực kỳ đơn giản: người ta có "phương châm" khác mình?

Oh đọc bài này em mới bất chợt nhận ra
VD : cái vùng dữ liệu đó đang làm tham số Or đối số của hàm của 1 sheet khác mà nó link tới ... khi co và dãn nó đẩy xuống thì điều gì xẩy ra ???????????!!!!!!!!!!

 

[tigertiger]

Oh đọc bài này em mới bất chợt nhận ra
VD : cái vùng dữ liệu đó đang làm tham số Or đối số của hàm của 1 sheet khác mà nó link tới ... khi co và dãn nó đẩy xuống thì điều gì xẩy ra ???????????!!!!!!!!!!

Chỉ ảnh hưởng các hàm dạng Indirect hay kiểu lấy địa chỉ làm tham số

Tuy thế đúng là người dùng hay thích làm tắt, đi lên lề, chèn ngang chèn dọc, cuối cùng tan ở đâu không biết- Nếu hàm mà ta cũng làm thế thì khác chi người sử dụng dạng vậy - thiếu chỉnh chu, đặc biệt là chèn ô thì rất dễ phá cấu trúc chung của Sheet, đến lúc rối , Hàm tự động làm việc này thì còn nguy hiểm hơn nhiều, vì người dùng sẽ không biết đâu mà lần

 

[Kiều Mạnh]

Chỉ ảnh hưởng các hàm dạng Indirect hay kiểu lấy địa chỉ làm tham số

Tuy thế đúng là người dùng hay thích làm tắt, đi lên lề, chèn ngang chèn dọc, cuối cùng tan ở đâu không biết- Nếu hàm mà ta cũng làm thế thì khác chi người sử dụng dạng vậy - thiếu chỉnh chu, đặc biệt là chèn ô thì rất dễ phá cấu trúc chung của Sheet, đến lúc rối , Hàm tự động làm việc này thì còn nguy hiểm hơn nhiều, vì người dùng sẽ không biết đâu mà lần

Bài số 38 link sau có lẻ câu phán đó đúng đấy ... không sai tí nào

Làm Sao Gán Kết Qủa Xuống Từng Dòng Bằng Hàm Tự Tạo

Mình có 1 hàm unique để lọc dữ liệu duy nhất. Làm cách để khi ta gõ hàm unique vào ô b1 , kết qủa unique là 10 giá trị thì tự động hàm unique gán giá trị xuống ô b1 đến b10.

www.giaiphapexcel.com

 

[ptm0412]

Tùy theo nhu cầu, tiêu chí của mỗi người thôi anh. Có thể 365 nếu đụng dữ liệu khác bên dưới hoặc bên phải sẽ bị lỗi Spill vì nó KHÔNG CHẤP NHẬN cách hành xử đó. Nếu đẩy dữ liệu đang có xuống dưới thì sẽ làm hỏng cấu trúc, tương quan giữa các dữ liệu. Là tôi tôi cũng không chấp nhận cách hành xử như thế.

Tôi cũng đồng ý vậy, và trong bài tôi cũng viết rằng (chỉ) cần khi sử dụng trong Report có nhiều sub report và muốn giới hạn trang in, không sử dụng trong các sheet dữ liệu gốc. Thêm 1 điều kiện là: "đẩy xuống" mang nghĩa là chèn nguyên dòng chứ không chèn 1 vài cells, để không làm mất tham số công thức bên dưới. (Indirect vẫn bị mất trong 1 số trường hợp)

 

[Tuanba1992]

Em tìm được addins gửi moi người tham khảo:

 

[Ba mười ba]

So sánh A-Tool với cả Excel365 khác nào nói "Đèn khoe đèn tỏ hơn Trăng"

 

[Nguyễn Duy Tuân]

...Tôi không nghĩ là mọi công ty ở Việt Nam đều dùng Add-in A-Tools. Nếu đúng thế thì tại sao họ không kêu ca nhỉ? Tại sao họ khổ thế mà vẫn cam chịu nhỉ?

Em mà được nhiều người dùng Excel biết đến A-Tools là em đã giàu lắm rồi đấy. Excel là môi trường phục vụ đa dạng nhu cầu, A-Tools là phục vụ cho một nhóm nhu cầu báo cáo và chia sẻ mạng vậy thôi anh. Nói vui chút về chuyện không có cái A ta vẫn sông ổn là trước đây công việc đâu có máy tính thì người ta vẫn làm việc được đó thôi.

Với anh Mỹ thì anh có cách nhìn khác, cái anh nói em có thể tự hào về A-Tools là em đã làm cố gắng hết sức để đáp ứng một nhóm nhu cầu và khá triệt để chứ không đơn giản chỉ như game. Em chia sẻ lại một phần nội dung mà em chinh phục một nhóm nhu cầu làm báo cáo để ai đó biết vì sao có người lại cần động đậy. Video tại đây.

 

[dangkhoiLK]

trà trong đây toàn nhân tài không.

So sánh A-Tool với cả Excel365 khác nào nói "Đèn khoe đèn tỏ hơn Trăng"

theo tớ nghĩ ai làm ra sản phẩm điều có quyền Pro sản phẩm của mình cũng là điều dễ hiểu (tớ mà làm ra được thì tới cũng nổ banh xác luôn đó chứ). Cũng giống như vụ Bphone thôi.
Các bác bớt ném gạch đá đi. Tập trung vào chỉ Code cho chủ thớt hay hơn

 

[Nguyễn Duy Tuân]

So sánh A-Tool với cả Excel365 khác nào nói "Đèn khoe đèn tỏ hơn Trăng"

Người hiểu biết ai lại đi so sánh một tính năng của một add-in với cả một phần mềm lớn chứ. Nếu muốn so sánh thì cũng chỉ giới hạn đối tượng cụ thể chứ.

 

[dangkhoiLK]

Em tìm được addins gửi moi người tham khảo:

Cái cách viết code của bác này sao tớ thấy quen quen

 

[Ba mười ba]

trà trong đây toàn nhân tài không.


theo tớ nghĩ ai làm ra sản phẩm điều có quyền Pro sản phẩm của mình cũng là điều dễ hiểu (tớ mà làm ra được thì tới cũng nổ banh xác luôn đó chứ). Cũng giống như vụ Bphone thôi.
Các bác bớt ném gạch đá đi. Tập trung vào chỉ Code cho chủ thớt hay hơn

Bạn nói "các bác" mà bạn lại trích dẫn mỗi bải của tôi? Tôi ném gạch? Nếu chỉ so sánh một khía cạnh nào đó thôi tôi chẳng nói làm gì.. đằng này tự nhiên lôi Bill vào đây để so sánh.
Mỗi người khi tạo ra sản phẩm của mình đều hướng đến một mục đích riêng theo ý muốn của họ, chứ không phải là họ không đủ trình độ để làm tại thời điểm đó hay là một thời điểm nào cả.
Tóm lại tôi chỉ nói thế thôi,để cùng nhìn vào nhu cầu và vấn đề thực tế.,còn nói nữa lại cãi nhau, nên xin phép dừng ở đây.

 

[dangkhoiLK]

Bạn nói "các bác" mà bạn lại trích dẫn mỗi bải của tôi? Tôi ném gạch? Nếu chỉ so sánh một khía cạnh nào đó thôi tôi chẳng nói làm gì.. đằng này tự nhiên lôi Bill vào đây để so sánh.
Mỗi người khi tạo ra sản phẩm của mình đều hướng đến một mục đích riêng theo ý muốn của họ, chứ không phải là họ không đủ trình độ để làm tại thời điểm đó hay là một thời điểm nào cả.
Tóm lại tôi chỉ nói thế thôi,để cùng nhìn vào nhu cầu và vấn đề thực tế.,còn nói nữa lại cãi nhau, nên xin phép dừng ở đây.

Bài của cậu là câu trên, không thấy tớ chấm xuống hàng hả.
Tớ cũng dừng tại đây nhà tớ 2 tầng rồi không muốn lên lầu nữa đâu

 

[Kiều Mạnh]

Tổng kết lại trên GPE này có mấy cái khá độc

1/ cái *.OCX ..... CustomTaskPane có trước đây 10 năm ... sau 10 năm Mạnh cũng đã làm ok
2/ cái lấy dữ liệu qua Internet cũng thế ... mà Mạnh còn làm lấy được rất nhiều Files trên 1 lần kết nối tới Server đấy
3/ cái hàm kiểu Office 365 thì cũng thế code loanh quanh trên VBA thui ( cái đó như ăn kẹo mút vậy ... mò đi )
4/ còn cái co và dãn Mạnh chỉ xem nó như bài 46 mà thôi ... cái khoanh màu đỏ ý
....
...
đi quá xa ròi đấy ... nên quay lại với cái tiêu đề của thớt này sẻ tốt cho ai đó hay bạn đó

Tranh cải nếu mang lại trí tuệ thì nên ... còn làm mất lòng thì nên thui ... ko đi tới đâu cả

bất cứ một kết quả nào thì cũng điều có nguyên nhân sinh ra nó ... và tại sao lại thế ???!!!!
Nếu hiểu được câu nói trên thì tự biết điều chỉnh lại hành vi của mình cho phù hợp ...
đừng có để cái tôi của mình lên trên tất cả thì cũng chỉ dừng lại khả năng trí tuệ có thế mà thôi

 

[dangkhoiLK]

Cậu này đúng là mạnh. Mạnh hơn cả Nga, Mỹ cộng lại . Trên đây tớ thấy dân nghề code két cũng nhiều, bớt bùng lại đi

 

[Kiều Mạnh]

Cậu này đúng là mạnh. Mạnh hơn cả Nga, Mỹ cộng lại . Trên đây tớ thấy dân nghề code két cũng nhiều, bớt bùng lại đi

tớ biết câu là ai rồi đó he ... khi nào qua bình dương alo mạnh làm vài lon

 

[dangkhoiLK]

tớ biết câu là ai rồi đó he ... khi nào qua bình dương alo mạnh làm vài lon

Cậu cũng tài thật tớ sài đồ mẽo mà cũng dò ra được. Ok có dịp ghé bình dương làm vài chai

 

[Kiều Mạnh]

Cậu cũng tài thật tớ sài đồ mẽo mà cũng dò ra được. Ok có dịp ghé bình dương làm vài chai

xem thường mạnh ròi đấy ... mạnh ko cần dò mà nhìn là biết ngay à .... ngày xưa rất xưa mạnh đi theo mấy tay A21 và B40 nó chỉ cho đấy
còn A21 và B40 là gì thì dò đi he ... thì biết mạnh là ai