UDF tính công nợ tại một thời điểm bất kỳ

[BNTT]

Tương tự bài UDF tính tồn kho theo số lượng tại một thời điểm bất kỳ, tôi cũng muốn đưa luôn vấn đề lập hàm để tính công nợ tại một thời điểm bất kỳ.
Thiết nghĩ những hàm tự tạo này sẽ cần thiết cho nhiều người.

Ví dụ, có 1 Database Khách hàng, gồm có các cột như sau:

1. Ngày tháng năm (xếp theo thứ tự nhỏ đến lớn)
2. Mã khách hàng
3. Số tiền phát sinh tăng (do mua hàng...)
4. Số tiền phát sinh giảm (do trả tiền...)
​

Ngoài ra còn có thể có thêm các cột khác (như địa chỉ, điện thoại, hàng mua, v.v...)
Công nợ đầu kỳ coi như bằng không.
Các tiêu chí chưa được đặt name. Vì để dễ dàng truy xuất từ một Database bất kỳ, miễn là có đủ 4 cột như ở trên.

Xin nhờ các bác làm giúp một cái UDF (có thể chạy trong Excel và cả trong VBA) theo dạng sau:

CONGNO(NGAY,KHACHHANG,PSTANG,PSGIAM)​

Diễn giải: Dùng để kiểm tra công nợ của (KHACHHANG) tại thời điểm (NGAY) dựa vào số tiền PSTANG và PSGIAM.

Ghi chú: Ở đây không bàn đến việc dùng hàm SUMPRODUCT.

 

[BNTT]

Từ bài UDF tính tồn kho tại một thời điểm bất kỳ, xin tạm đưa ra giải pháp cho topic này. Mong tác giả là Mr OkBap xem và góp ý hộ.

Function CongNo(MaKhach As String, Ngay As Date, _
                MangNgay As Range, MangMaKH As Range, MangTienTang As Range, MangTienGiam As Range) As Double
    Application.Volatile (False)
    Dim i As Integer
    If Len(MaKhach) = 0 Then Exit Function
    If MangNgay.Columns.Count > 1 Then Exit Function
    If MangMaKH.Columns.Count > 1 Then Exit Function
    If MangTienTang.Columns.Count > 1 Then Exit Function
    If MangTienGiam.Columns.Count > 1 Then Exit Function
    If Not (MangNgay.Rows.Count = MangMaKH.Rows.Count And MangNgay.Rows.Count = MangTienTang.Rows.Count _
            And MangNgay.Rows.Count = MangTienGiam.Rows.Count) Then Exit Function
    For i = 1 To MangNgay.Rows.Count
        If MangNgay(i) <= Ngay Then
            If MangMaKH(i) = MaKhach Then CongNo = CongNo + MangTienTang(i) - MangTienGiam(i)
        Else: Exit For: End If
    Next
    Set MangNgay = Nothing
    Set MangMaKH = Nothing
    Set MangTienTang = Nothing
    Set MangTienGiam = Nothing
End Function

Trong đó:

• MaKhach: Mã khách hàng cần xem công nợ
• Ngay: Ngày cần tính công nợ (kể từ ngày đầu tiên phát sinh quan hệ mua bán với khách hàng này)
• MangNgay: Cột chứa danh sách Ngày Tháng Năm
• MangMaKH: Cột chứa danh sách Mã Khách Hàng
• MangTienTang: Cột chứa số tiền phát sinh tăng
• MangTienGiam: Cột chứa số tiền phát sinh giảm

 

[levanduyet]

To: BNTT,

Thông thường một hàm cần có bẩy lỗi để trả về một giá trị để người sử dụng có thể biết là lỗi gì mà khắc phục.
Ví dụ:
_Nếu lỗi là số hàng của các mảng khác nhau, thì trả về là -1
_Các lỗi khác, thì trả về là -2, chẳng hạn.

Lê Văn Duyệt

 

[Mr Okebab]

To: BNTT,

Thông thường một hàm cần có bẩy lỗi để trả về một giá trị để người sử dụng có thể biết là lỗi gì mà khắc phục.
Ví dụ:
_Nếu lỗi là số hàng của các mảng khác nhau, thì trả về là -1
_Các lỗi khác, thì trả về là -2, chẳng hạn.

Lê Văn Duyệt

Hơi cầu toàn bác ạ. Hàm của bác Bill nhiều khi cũng chung chung lắm, có lỗi là bác ấy cứ đưa hết vào NA hoặc #Value
Tính Công nợ mà khi lỗi trả về số thì . . chết vì có thể lại nfghĩ rằng là không bị lỗi.
VD : KQ = -1, khi đó lại nghĩ rằng mình nợ khách hàng là 1.

Tuy nhiên đây cũng là một phương pháp để tụi em chuyên nghiệp hơn.

Cảm ơn bác nhiều.

Thân!

 

[hai2hai]

Hơi cầu toàn bác ạ. Hàm của bác Bill nhiều khi cũng chung chung lắm, có lỗi là bác ấy cứ đưa hết vào NA hoặc #Value
Tính Công nợ mà khi lỗi trả về số thì . . chết vì có thể lại nfghĩ rằng là không bị lỗi.
VD : KQ = -1, khi đó lại nghĩ rằng mình nợ khách hàng là 1.

Tuy nhiên đây cũng là một phương pháp để tụi em chuyên nghiệp hơn.

Cảm ơn bác nhiều.

Thân!

Anh Duyệt viết thế là chuyên nghiệp rồi.

Mình muốn nói thêm 1 chút: "Hàm", "Thủ tục", "Phương thức" là thể hiện 1 hành động nào đó của một đối tượng. Vì thế, tên của chúng sẽ phải có dạng là 1 ĐỘNG TỪ.

Ví dụ:

Dim dblReturnBalance As Double

dblReturnBalance = 0

objCustomer.ID = 5
objCustomer.BalanceDate = '12/08/2007'
iRet = objCustomer.getBalance(dblReturnBalance)

Select Case iRet
Case 0 '// No Error found
'// Return Customer Balace
Msgbox "Số dư hiện thời của khách hàng " & objCustomer.Name & " là: " & dblReturnBalance, vbInformation, "Thông báo"
Case -1
Case -2
Case Else
End Select

 

[Mr Okebab]

Bác yêu quí ơi !

Anh Duyệt viết thế là chuyên nghiệp rồi.
Em biết là thế mà, và em công nhận, nhưng có những cái phải liệu cơm mà gắp mắm, và phải nhìn sang hàng xóm nữa. Và dĩ nhiên làm được thế là tốt nhất, luôn luôn là vậy.
Mình muốn nói thêm 1 chút: "Hàm", "Thủ tục", "Phương thức" là thể hiện 1 hành động nào đó của một đối tượng. Vì thế, tên của chúng sẽ phải có dạng là 1 ĐỘNG TỪ.
Cái này thì em lại nghĩ khác, nó có thể là 1 ĐỘNG TỪ, 1 DANH TỪ, 1 TÍNH TỪ, thậm chí là TRẠNG TỪ . . . .
vd :
- ĐộngTừ : ROUND, CHOOSE, CLEAN
- Danh từ :ADDRESS, COLUMN, ROW
- Tính từ : MIN, MAX, SUM, LEN, ISERROR . . .
-Trạng từ : DAY, DATE, TIME, NOW . .
............................................

Cảm ơn bác nhiều.

Thân!

 

[BNTT]

Thú thật, làm kiểu của Bap, với em như thế là đạt lắm rồi các bác à.
Còn khi sử dụng, em sẽ chế ra thêm vài cái message, ví dụ:
- Số dư > 0: người ta đang nợ mình
- Số dư < 0: mình còn nợ người ta (người ta đã đặt cọc mà chưa lấy hàng chẳng hạn...)
Đó là với khách hàng, còn với bạn hàng thì ngược lại.

Bên hàm tồn kho, hiện tại thì em chưa khám phá ra có số dư nào < 0, vì đó là chuyện không thể có được, nếu có, mà em sửa không nổi, lại phải cầu cứu Bap thôi... Còn hàm tính công nợ, chuyện < 0 là bình thường.

Cảm ơn các bác đã quan tâm. Mong nhận được thêm nhiều góp ý nữa.

 

[hai2hai]

Bên hàm tồn kho, hiện tại thì em chưa khám phá ra có số dư nào < 0, vì đó là chuyện không thể có được.

Hàng âm trong trường hợp hàng mới về đem xuất bán ngay mà chưa kịp nhập chứng từ nhập hàng. Mình cũng tưởng chuyện này ko thể xảy ra nhưng trên thực tế (khách hàng là người nước ngoài, một số khách chứ ko phải 1 khách nhé) và một số phần mềm nước ngoài vẫn cho phép xuất âm kho. Cuối cùng chính mình cùng phải cho xuất âm kho và sau đó cho nhập sau (hàm tính tốn kho vẫn phải đúng, tính giá vốn vẫn phải đúng chứ)

Còn hàm tính công nợ, chuyện < 0 là bình thường.

Còn khái niệm số dư công nợ khách hàng, nhà cung cấp âm là chuyện đương nhiên (tài khoản 131, 331 là lưỡng tính mà). Nhưng có lẽ các bạn chưa hiểu cách mình viết code ở trên. Không phải giá trị trở về của hàm là giá trị công nợ đâu mà là mã lỗi hàm trả về. Còn giá trị công nợ trả về là cái đối số dblReturnBalance cơ.

Đây, tớ viết cho các cậu xem nhé:

...
Public Function getBalance(ByRef dblReturnBalance As Double) As Long
On Error Goto Proc_Error
....

'// Assumes function failed
getBalance = -1

....
'// Check some validation here...
if Not ... Then
getBalance = -2
Goto Proc_Done
End if

if Not ... Then
getBalance = -3
Goto Proc_Done
End if

'// Get Balance code here
dblReturnBalance = ..... '// Đây mới là giá trị số dư Công nợ được trả về này . Vì thế trên đối số của method mới khai báo là ByRef chứ ko phải ByVal nhé.


'// All well done, return 0 <-- Nếu hàm cứ chạy 1 lèo qua hết các validation ở trên thì coi như ko có lỗi
getBalance = 0

Proc_Done:
'// Release all resources here...
...
Exit Function

Proc_Error:
...
Goto Proc_Done
End Function

Cách sử dụng method này như ở bài trên mà mình post.


Còn mọi người đang hiểu theo kiểu cứ hàm CongNo là phải trả về số dư công nợ. Nhưng ko phải thế đâu nhé. Cách của anh Duyệt là đúng rồi đó.

Tóm lại là: Cái -1, -2, -3 đó hoàn toàn trả liên quan gì tới việc công nợ là -1, -2, -3 đồng .... cả. Hoàn toàn độc lập nhau.

Tuy nhiên, cách viết trên là các chuyên gia hay dùng (các bạn hãy nhìn các hàm APIs ấy, giá trị trở về toàn là kiểu Long thôi).
Ở những hàm đơn giản thì chỉ cần trả về giá trị kiểu Boolean. Return True tức là ko có lỗi, còn False tức là có lỗi. Còn một khi hàm có thể trả về nhiều loại validation khác nhau cho nhiều trường hợp khác nhau (Validation check rất nhiều trong hàm) thì cách viết trên là CỰC CHUẨN.

Đấy là mình chỉ giải thích cái giá trị 0, -1, -2,... đó là khác với giá trị công nợ trả về thôi. Còn nếu các bạn thấy các hàm như trên ổn rồi thì tốt rồi. Mình chỉ định giải thích về cách mà anh Duyệt nói mà thôi, ko có ý định gì sâu sa ở đây nữa đâu.

 

[BNTT]

Cảm ơn bác Hai2hai nhiều.
Nhưng thú thật, hôm qua mày mò để hiểu được cái hàm của OkBap, em đã muốn điên cái đầu. Bi giờ đọc đi đọc lại cái bài của bác viết, em ... như lạc vào mê hồn trận.
Em thì, chỉ biết bẫy bằng On Error Go To Next hoặc là cho nó Exit Function luôn mà thôi, hihi...
Nhưng dù sao cũng xin cảm ơn các bác. Mong nhận thêm được nhiều chỉ giáo.