입출고관리 데이터 코드 단순화 + 코드 캡슐화 - 코드제공, 파일제공

※ 제 코드와 파일은 상업적 이용, 개인적 이용이 모두 가능합니다. 다만, 게시판이나 블로그에 업로드할 목적이라면 꼭 출처를 남겨주시기바랍니다.

재고관리프로그램-Ver.6.xlsm

0.12MB

안녕하세요.

오늘은 코드를 유지보수하는데 있어 더 수월하도록 캡슐화하고 단순화시킨 코드를 공개하겠습니다.

1. 견적서 추가 & 재고 갱신

Sub add_inventory(kind As String)
Dim count_inven As Integer '재고 품목 개수
Dim count_estimate As Integer '견적서의 품목 개수
Dim data As Integer
Dim cal As Integer
Dim overlap As Boolean
overlap = reduplication_check(kind)

If (Not overlap) Then
    Call save_past(kind, "input", Worksheets(kind & "견적서").Cells(3, "G").Value)
    Worksheets("Redo " & kind & "데이터").Range("A:Y").delete xlToLeft
    
    cal = calculation(kind)
    count_inven = 3 '재고는 3행부터 존재
    Do While (Not IsEmpty(Worksheets("재고관리").Cells(count_inven, 2).Value)) '재고 품목 개수를 카운트하는 반복문
        count_inven = count_inven + 1
    Loop
    
    count_estimate = 3 '견적서는 3행부터 존재
    
    Do While (Not IsEmpty(Worksheets(kind & "견적서").Cells(count_estimate, 3).Value)) '견적서 품목 개수를 카운트하는 반복문
        count_estimate = count_estimate + 1
    Loop
    
    data = 3 '데이터는 3행부터 존재
    
    Do While (Not IsEmpty(Worksheets(kind).Cells(data, "B").Value)) '데이터의 개수를 카운트하는 반복문
        data = data + 1
    Loop
    
    
    For i = 3 To count_estimate - 1 '재고의 품목과 견적서의 품목이 일치하면 재고 갱신
        For j = 3 To count_inven - 1
            If (Worksheets("재고관리").Cells(j, 2).Value Like Worksheets(kind & "견적서").Cells(i, 3).Value) Then
                Worksheets("재고관리").Cells(j, 3).Value = Worksheets("재고관리").Cells(j, 3).Value + (Worksheets(kind & "견적서").Cells(i, 5).Value * cal)
                Exit For
            End If
        Next j
    Next i
    
    '데이터 갱신
    Range("C" & Trim(Str(data) & ":F" & Trim(Str(data + count_estimate - 4)))).Value = Worksheets(kind & "견적서").Range("B3:E" & Trim(Str(count_estimate - 1))).Value
    Range("B" & Trim(Str(data) & ":B" & Trim(Str(data + count_estimate - 4)))).Value = Worksheets(kind & "견적서").Cells(3, 7).Value
End If
End Sub

 

2. 삭제 & 재고갱신

Sub delete(kind As String)
    Dim i As Integer
    Dim j As Integer
    Dim delete_number As String
    Dim success_delete As Boolean

    cal = -calculation(kind)
    
    success_delete = False
    delete_number = Application.InputBox("삭제하실 거래번호를 입력하세요", "거래삭제", , , , , , 2)
    
    i = 3
    Do While (Not IsEmpty(Cells(i, 2).Value))
        If (Cells(i, 2).Value Like delete_number) Then
            If (Not success_delete) Then
                Call save_past(kind, "delete", delete_number)
                Worksheets("Redo " & kind & "데이터").Range("A:Y").delete xlToLeft
            End If
            success_delete = True
            j = 3
            Do While (Not IsEmpty(Worksheets("재고관리").Cells(j, 2)))
                If (Cells(i, 4).Value Like Worksheets("재고관리").Cells(j, 2).Value) Then
                    Worksheets("재고관리").Cells(j, 3).Value = Worksheets("재고관리").Cells(j, 3).Value + (Worksheets(kind).Cells(i, 6).Value * cal)
                    Exit Do
                End If
                j = j + 1
            Loop
            Range("B" & Trim(Str(i)) & ":F" & Trim(Str(i))).Select
            Selection.delete Shift:=xlUp
            i = 3
        Else
            i = i + 1
        End If
    Loop
    If (Not success_delete) Then
        msg = MsgBox("이미 삭제되었거나 존재하지않는 번호입니다.", vbYesOnly, "삭제실패")
    Else
        Range("B3:F500").Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
            With Selection.Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        Range("G2").Select
    End If
    
End Sub

3. 중복확인

Function reduplication_check(kind As String)
    Dim overlap As Boolean
    Dim i As Integer
    Dim msg As Integer
    i = 3

    overlap = False
    
    Do While (Not IsEmpty(Worksheets(kind).Cells(i, 2).Value))
        If (Worksheets(ttype).Cells(i, 2).Value Like Worksheets(kind & "견적서").Cells(3, 7).Value) Then
            overlap = True
            msg = MsgBox("이미 등록된 견적서입니다", vbYesOnly, "중복경고")
            Exit Do
        End If
        i = i + 1
     Loop
reduplication_check = overlap

End Function

4. 유형확인

Function calculation(kind As String)
    Dim plus As Boolean
    Dim minus As Boolean
    Dim i As Integer
    plus = False
    minus = False
    i = 3
    Do While (Not Worksheets("재고관리").Cells(i, "I").Value Like "")
        If (kind Like Worksheets("재고관리").Cells(i, "I").Value) Then
            calculation = 1
            plus = True
            Exit Do
        End If
        i = i + 1
    Loop
    If (Not plus) Then
        i = 3
        Do While (Not Worksheets("재고관리").Cells(i, "J").Value Like "")
            If (kind Like Worksheets("재고관리").Cells(i, "J").Value) Then
                calculation = -1
                minus = True
                Exit Do
            End If
                i = i + 1
        Loop
        If (Not minus) Then
            msg = MsgBox("입력되지 않은 유형입니다.", vbYesOnly, "실행실패")
            calculation = 0
        End If
    End If
End Function

5. Undo

Sub undo(kind As String)
    Dim cal As Integer
    cal = calculation(kind)
    If (IsEmpty(Worksheets("Undo " & kind & "데이터").Cells(2, "U").Value)) Then
        msg = MsgBox("Undo하실 데이터가 없습니다.", vbYesOnly, "Undo 실패")
    Else
        Call save_future(kind)
        
        '재고 갱신
        If (Worksheets("Undo " & kind & "데이터").Cells(1, "V") Like "input") Then
            Dim i As Integer
            Dim j As Integer
            
            cal = cal * -1
            i = 3
            Do While (Not IsEmpty(Cells(i, 2).Value))
                If (Cells(i, 2).Value Like Worksheets("Undo " & kind & "데이터").Cells(1, "U").Value) Then
                    j = 3
                    Do While (Not IsEmpty(Worksheets("재고관리").Cells(j, 2)))
                        If (Cells(i, 4).Value Like Worksheets("재고관리").Cells(j, 2).Value) Then
                            Worksheets("재고관리").Cells(j, 3).Value = Worksheets("재고관리").Cells(j, 3).Value + (Worksheets(kind).Cells(i, 6).Value * cal)
                            Exit Do
                        End If
                        j = j + 1
                    Loop
                End If
                i = i + 1
            Loop
        Else
            Dim count_inven As Integer
            count_inven = 3
                
            Do While (Not IsEmpty(Worksheets("재고관리").Cells(count_inven, 2).Value))
                count_inven = count_inven + 1
            Loop
            
            For i = 3 To 100
                For j = 3 To count_inven - 1
                    If (Worksheets("Undo " & kind & "데이터").Cells(1, "U").Value Like Worksheets("Undo " & kind & "데이터").Cells(i, "U").Value _
                    And Worksheets("재고관리").Cells(j, 2).Value Like Worksheets("Undo " & kind & "데이터").Cells(i, "W").Value) Then
                        Worksheets("재고관리").Cells(j, 3).Value = Worksheets("재고관리").Cells(j, 3).Value + (Worksheets("Undo " & kind & "데이터").Cells(i, "Y").Value * cal)
                        Exit For
                    End If
                Next j
            Next i
        End If
        
        '데이터 갱신
        Worksheets("Undo " & kind & "데이터").Range("U:Y").Cut Worksheets(kind).Range("B:F")
        Range("B1:C1").Value = ""
        Cells(1, "D").Value = kind
        Worksheets("Undo " & kind & "데이터").Range("A:E").Insert
        End If
End Sub

6. Redo

Sub Redo(kind As String)
    Dim cal As Integer
    cal = calculation(kind)
    If (IsEmpty(Worksheets("Redo " & kind & "데이터").Cells(2, "U").Value)) Then
        msg = MsgBox("Redo하실 데이터가 없습니다.", vbYesOnly, "Redo 실패")
    Else
        Call save_past(kind, Worksheets("Redo " & kind & "데이터").Cells(1, "V"), Worksheets("Redo " & kind & "데이터").Cells(1, "U"))
    
    '재고 갱신
    If (Worksheets("Redo " & kind & "데이터").Cells(1, "V") Like "delete") Then
        Dim i As Integer
        Dim j As Integer
        
        cal = cal * -1
        i = 3
        Do While (Not IsEmpty(Cells(i, 2).Value))
            If (Cells(i, 2).Value Like Worksheets("Redo " & kind & "데이터").Cells(1, "U").Value) Then
                j = 3
                Do While (Not IsEmpty(Worksheets("재고관리").Cells(j, 2)))
                    If (Cells(i, 4).Value Like Worksheets("재고관리").Cells(j, 2).Value) Then
                            Worksheets("재고관리").Cells(j, 3).Value = Worksheets("재고관리").Cells(j, 3).Value + (Worksheets(kind).Cells(i, 6).Value * cal)
                        Exit Do
                    End If
                    j = j + 1
                Loop
            End If
            i = i + 1
        Loop
    Else
        Dim count_inven As Integer
        count_inven = 3
            
        Do While (Not IsEmpty(Worksheets("재고관리").Cells(count_inven, 2).Value))
            count_inven = count_inven + 1
        Loop
        For i = 3 To 100
            For j = 3 To count_inven - 1
                If (Worksheets("Redo " & kind & "데이터").Cells(1, "U").Value Like Worksheets("Redo " & kind & "데이터").Cells(i, "U").Value _
                And Worksheets("재고관리").Cells(j, 2).Value Like Worksheets("Redo " & kind & "데이터").Cells(i, "W").Value) Then
                    Worksheets("재고관리").Cells(j, 3).Value = Worksheets("재고관리").Cells(j, 3).Value + (Worksheets("Redo " & kind & "데이터").Cells(i, "Y").Value * cal)
                    Exit For
                End If
            Next j
        Next i
    End If
    
        '데이터 갱신
        Worksheets("Redo " & kind & "데이터").Range("U:Y").Cut Worksheets(kind).Range("B:F")
        Range("B1:C1").Value = ""
        Cells(1, "D").Value = kind
        Worksheets("Redo " & kind & "데이터").Range("A:E").Insert
    End If
End Sub

7. 과거저장(Undo)

Sub save_past(kind As String, reverse_order As String, number As String)
    Worksheets("Undo " & kind & "데이터").Range("A:E").delete xlToLeft
    Worksheets(kind).Range("B:F").Copy Worksheets("Undo " & kind & "데이터").Range("U:Y")
    Worksheets("Undo " & kind & "데이터").Cells(1, "U").Value = number
    Worksheets("Undo " & kind & "데이터").Cells(1, "V").Value = reverse_order
End Sub

8. 미래저장(Redo)

Sub save_future(kind As String)
    Worksheets("Redo " & kind & "데이터").Range("A:E").delete xlToLeft
    Worksheets(kind).Range("B:F").Copy Worksheets("Redo " & kind & "데이터").Range("U:Y")
    Worksheets("Redo " & kind & "데이터").Range("U1:V1").Value = Worksheets("Undo " & kind & "데이터").Range("U1:V1").Value
End Sub

9. 통합실행(실제 매크로 지정 프로시저)

Dim row As Integer
Dim column As String
Sub point()
    row = 1
    column = "D"
End Sub
Sub add_data()
    Call point
    If (Not calculation(Cells(row, column)) Like 0) Then
        add_inventory (Cells(row, column))
    End If
End Sub
Sub delete_data()
    Call point
    If (Not calculation(Cells(row, column)) Like 0) Then
        delete (Cells(row, column))
    End If
End Sub
Sub undo_data()
    Call point
    If (Not calculation(Cells(row, column)) Like 0) Then
        undo (Cells(row, column))
    End If
End Sub
Sub redo_data()
    Call point
    If (Not calculation(Cells(row, column)) Like 0) Then
        Redo (Cells(row, column))
    End If
End Sub

+ 새로운 기능을 추가했습니다.

입고와 출고만 있었던 기존과 다르게 불량이라는 시트가 만들어졌죠?

 

사실 불량이 만들어진게 다가아닙니다.

 

영사을 보시면 "재고관리"시트에서 감소에 불량을 넣으니 마치 출고처럼 추가시 -, 삭제시 +, Undo시 반대연산, Redo시 정연산을 합니다.

 

마치 새로 만든 것처럼 작동합니다.

 

그러면 반품, 이벤트, 증정 등 많은 유형의 견적서를 만들 수 있겠죠?

 

하지만, 시트 복붙하고 이름 바꾸는게 귀찮을 겁니다.

 

걱정하지마세요.

 

6월 7일 일요일 00시전까지 복사와 이름변경 자동화, 폼을 통한 증가와 감소 입력, 실제 견적서 도입 예시 등을 공개하겠습니다. 즉, 이번 입출고 프로젝트는 6월 6일 토요일 업로드를 통해 마무리하고 더 좋은 프로그램으로 찾아뵙겠습니다.

 

- 입출고관리 데이터 코드 단순화 + 코드 캡슐화 - 코드제공, 파일제공

6월 6일 자정전 공개