입출고 시 중복 업로드 방지 - 코드 분석

안녕하세요. 오늘은 입출고 업로드 시 중복 업로드 되는 것 방지하기 위한 VBA 코드와 원리에 대해서 알아보겠습니다.
 
먼저, 코드와 파일을 제공했던 게시글 링크입니다.
https://ksm30546.tistory.com/17

입고, 출고시 중복 업로드 억제-코드제공, 파일제공(복붙용)

 
1. 데이터 중복 방지 및 식별방법 - 키속성
본격적인 설명에 앞서 우리는 어떤 원리로 중복 업로드를 방지 시킬지부터 생각해봐야합니다.
 
사실 방법은 우리의 실생활에 많이 사용되고 있습니다. 가장 대중적으로 사용되는 방법이 주민등록번호죠. 주민등록번호는 매우 특별한 케이스가 아닌 이상 얼굴과 같은 신체와 다르게 시간이 지나도 변하지않고, 이름이나 주소와 다르게 중복이 발생하는 경우가 없습니다.
 
때문에 주민등록번호는 우리의 실생활에 많은 부분에서 사용됩니다. 예를들어, 은행 본인확인, 투표에서 중복참정방지 및 참정자 식별, 사이트 중복가입 확인 및 아이디, 비밀번호 분실 시 조회 수단 등으로 말이죠.
 
튜플 중복 확인도 똑같습니다. 튜플마다 다른 값을 가진 속성이 있다면, 데이터가 중복 처리되는 일을 막을 수 있고 더 나아가 데이터 식별도 빠르게 가능하겠죠.
 
그리고 그것을 데이터베이스 분야에서는 키속성이라고 부릅니다.
 
키속성은 특징과 종류는 아래와 같습니다.
 
1) 후보키
후보키는 튜플마다 다른 값을 가진 속성을 의미합니다. 주민등록번호, 상품번호, 운송장번호 등이 있죠.
 
2) 기본키
기본키는 튜플 중 데이터를 식별할 때 사용하기위해 선택된 속입니다. 때문에 널(Null)값이 사용될 수 없다거나 중복이 발생할 수 없다는 점은 후보키와 동일합니다.
 
3) 대체키(보조키)
대체키는 후보키 중 기본키로 선택되지 못한 속성을 말합니다. 때문에 기본키로 튜플을 인식하기 곤란한 상황이 발생하였을 때 대체키를 통해 튜플을 인식할 수 있습니다.
 
4) 슈퍼키
슈퍼키는 다른 속성들을 합쳐서 중복이 되지않도록 만든 키 값입니다. 이름과 생년월일이라는 속성은 동명이인이 있거나 갑자년 갑자월 갑자일에 태어난 사람이 있는 경우가 발생할 확률이 높아 키속성으로 사용되기에는 부적절합니다.
허나, 이름 + 생년월일 + 전화번호를 조합하여 만들면 어떨까요?
 
이름과 생년월일은 같을 수 있지만, 이름과 생년월일 거기다 전화번호까지 같은 사람은 존재할 수 가 없습니다.(중복 가입이 아니고서야)
 
5) 외래키
현재의 릴레이션이 아닌 다른 릴레이션의 기본키를 현재의 릴레이션에서 식별의 용도로 사용되는 키속성을 말합니다.
 
예를들어 어떤 은행 시스템에서 사용자의 주민번호를 사용자라는 릴레이션에서 기본키로 사용하고 있다고 가정해보겠습니다. 사용자가 계좌를 개설할 경우 계좌번호나 금액 같은 것들이 계좌 릴레이션에 저장이 됩니다.
 
허나, 사용자는 하나의 계좌만 개설하는 것이 아니며, 사용자 또한 한명이 아닙니다. 때문에 많은 계좌 튜플이 계좌 릴레이션에 저장될 것이고 계좌번호와 금액만으로는 누구의 계좌인지 식별할 수 없습니다.
 
때문에, 사용자의 기본키인 사용자 주민번호를 계좌 속성에 추가하면 계좌의 주인이 명확해지고 그렇게되면 계좌 조회, 사용, 식별이 용이해집니다.
 
2. 입고견적서입력 코드 뜯어보기
 
- 중복확인 코드로 수정하기 전 입고견적서입력 코드

Sub 입고견적서입력()
    Dim input_data As String
    Dim data As Integer
    data = 3
    Do While (Cells(data, "B").Value <> "")
        data = data + 1
    Loop
    input_data = "B" & Trim(Str(data)) & ":" & "E" & Trim(Str(data + 100))
    Range(input_data).Value = Worksheets("입고견적서").Range("B3:E100").Value
    
    ' 새로 추가된 코드
    Call 입고재고갱신
End Sub

- 중복확인이 가능하도록 수정한 새로운 코드

Sub 입고견적서입력()
    Dim data As Integer
    Dim Transaction As Integer
    Dim overlap As Boolean
    
    overlap = 중복확인("입고")
    
    If (Not overlap) Then
        '입고시트 몇번째 셀부터 데이터를 넣을 수 있는지 확인
        data = 3
        Do While (Not IsEmpty(Cells(data, "B").Value))
            data = data + 1
        Loop
        
        '입고견적서의 데이터가 몇개인지 확인
        Transaction = 3
        Do While (Not IsEmpty(Worksheets("입고견적서").Cells(Transaction, 2).Value))
            Transaction = Transaction + 1
        Loop
        Range("C" & Trim(Str(data) & ":F" & Trim(Str(data + Transaction - 4)))).Value = Worksheets("입고견적서").Range("B3:E" & Trim(Str(Transaction - 1))).Value
        Range("B" & Trim(Str(data) & ":B" & Trim(Str(data + Transaction - 4)))).Value = Worksheets("입고견적서").Cells(3, 7).Value
        
        
        ' 재고갱신
        Call 입고재고갱신
    End If
End Sub

지난번에 올린 코드와 새로 만들어진 코드는 기능적으로는 중복확인이 되냐안되냐 차이지만, 형태로 봤을 때 많은 차이가 있습니다.
 
우선 변수로는 정수형 변수인 "data"와 불린형(True or False의 값으로만 이루어진 자료형) 변수인 "overlap"이 추가되었다는 점을 제일 먼저 보실 수 있습니다.
 
음.. 용도는 잘모르겠지만,
overlap = 중복확인("입고")라는 코드를 보아하니, 중복확인이라는 Function(Sub는 return을 할 수 없으니 Boolean형 변수에 return값을 넣을 수 있는 Function이라는 프로시저를 사용한 것으로 확인)이라는 문자열을 인수로 받는 프로시저에 인수를 "입고"로 넣고 결과(Boolean형 값)를 overlap이라는 변수에 저장하려한다는 것을 알 수 있습니다.
 
그리고 "입고견적서입력"이라는 프로시저안에 변수 선언과 "중복확인"이라는 프로시저를 호출하는 것 외에는 모든 코드가 if문안에 감싸져있고 그에 따른 조건이 "중복확인"의 return값을 담은 overlap의 Not의 결과(반대의 결과)인 것으로 보아 overlap의 값이 False여야 입고견적서의 데이터가 입력된다는 것을 알 수 있습니다.
(영어를 잘하시거나 검색해보신 분들은 눈치채셨겠지만, overlap은 "겹치다"라는 뜻을 가지고 있습니다. 즉, True가 나오면 중복이라는 뜻이고 False가 나오면 중복이 아니라는 뜻으로 사용하기 위해 만든 변수라는 것을 추측할 수 있습니다.)
 
이제 overlap이 False라는 가정하에(중복되지않았다는 가정하에) if문안으로 들어가보겠습니다.
 
1)
'입고시트 몇번째 셀부터 데이터를 넣을 수 있는지 확인
data = 3
Do While (Not IsEmpty(Cells(data, "B").Value))
    data = data + 1
Loop
이라는 코드가 보입니다. 코드를 그대로 직역해보면, data행 B열에 있는 셀의 값이 비어있지않으면 data의 값을 1올려줘라는 코드입니다. 그렇게 data의 값이 올라가다 비어있는 셀을 만나면 do while문이 종료될 것이고 data행의 B열은 비어있는 셀이 되겠군요.
 
아하! 그러면 비어있는 셀 중 가장 높이 위치한 셀의 행번호를 알 수 있겠군요!
 
2)
'입고견적서의 데이터가 몇개인지 확인
Transaction = 3
Do While (Not IsEmpty(Worksheets("입고견적서").Cells(Transaction , 2).Value))
    Transaction = Transaction + 1
Loop
 
해당 코드는 1)번과 다르게 워크시트를 지정해줬다는 점이랑 변수명말곤 똑같은 것 같습니다. 그리고 주석을 보면 입고견적서의 데이터 개수를 확인하는 코드라고 되어있는데요. 그런데 이상합니다. 1)번 코드는 셀의 번호를 저장하고 2)번 코드는 데이터의 개수를 저장하는데 두개의 형태나 시작값이 같습니다. 때문에 Transaction이라는 변수는 입고견적서의 데이터 개수를 구하기 위해 선언되었지만, 입고견적서의 데이터의 개수와 다른 값을 가지고 있으니 별도의 연산이 필요하겠죠?
 
3)
 Range("C" & Trim(Str(data) & ":F" & Trim(Str(data + Transaction - 4)))).Value = Worksheets("입고견적서").Range("B3:E" & Trim(Str(Transaction - 1))).Value
 
Range("B" & Trim(Str(data) & ":B" & Trim(Str(data + Transaction - 4)))).Value = Worksheets("입고견적서").Cells(3, 7).Value

지난번 코드와 비슷하게 생겼지만 다른 두가지가 눈에 띕니다.
 
첫번째는 데이터의 범위가 data + 100에서 data + Transaction - 4로 변경되었다는 건데요. 예전 코드는 입고견적서의 튜플개수를 세지않고 입고시트에 옮기는 바람에 98개의 튜플밖에 넣지 못했는데, 이제는 입고견적서의 길이가 길어져도 입고시트로 옮길 수 있게되었군요.
 
※ data + Transaction - 4 = 입고시트 중 비어있는 셀 번호 + (입고시트 데이터의 개수 + 4) - 4
- 4를 한 이유는 Transaction이 3부터 시작했기 때문에 입고시트의 데이터 개수를 정확하게 구하려면 3을 빼야하고 거기다 2)번의 알고리즘을 보면 비어있지않으면 Transaction 변수의 값을 1 더하므로 셀이 비어있어 Do while문이 종료될 경우 Transaction의 값은 비어있는 셀 위치가 될 수 밖에 없습니다. 때문에 데이터의 개수 + 초기 셀(3) + 비어있는 셀의 위치(1)이 Transaction의 값이므로 4를 빼줘야 합니다.
 
두번째는 새로 추가할 튜플의 B열에 "입고견적서"시트의 3행 7열의 값을 일괄적으로 대입시킨다는 것인데요.

[그림 1] 입고견적서

[그림 1]의 입고견적서를 보아하니 3행 7열인 3행 G열에는 거래번호가 입력되어있네요. 아하 그러면 입고시트의 새로 추가된 B열에는 거래번호가 일괄적으로 추가되겠군요!
 

[그림 2] 입고시트 거래번호 일괄추가

3. 출고견적서 코드 뜯어보기
이제 입고견적서는 이해되었습니다. 출고견적서입력의 코드를 볼까요?

Sub 출고견적서입력()
    Dim data As Integer
    Dim Transaction As Integer
    Dim overlap As Boolean
    
    overlap = 중복확인("출고")
    
    If (Not overlap) Then
        '출고시트 몇번째 셀부터 데이터를 넣을 수 있는지 확인
        data = 3
        Do While (Not IsEmpty(Cells(data, "B").Value))
            data = data + 1
        Loop
        
        '출고견적서의 데이터가 몇개인지 확인
        Transaction = 3
        Do While (Not IsEmpty(Worksheets("출고견적서").Cells(Transaction, 2).Value))
            Transaction = Transaction + 1
        Loop
        Range("C" & Trim(Str(data) & ":F" & Trim(Str(data + Transaction - 4)))).Value = Worksheets("출고견적서").Range("B3:E" & Trim(Str(Transaction - 1))).Value
        Range("B" & Trim(Str(data) & ":B" & Trim(Str(data + Transaction - 4)))).Value = Worksheets("출고견적서").Cells(3, 7).Value
        
        
        ' 재고갱신
        Call 출고재고갱신
    
    End If
End Sub

오잉? 어디서 많이 본 코드입니다.
"입고"라는 글자가 "출고"로 바뀐거말고는 토시하나도 달라지지않았군요.
 
아하! 입고와 출고의 견적서를 입력하고 중복을 확인하는 원리는 동일하다는 것을 알 수 있네요.
 
4. 중복확인 코드 뜯어보기

Function 중복확인(ttype As String)
    Dim overlap As Boolean
    Dim i As Integer
    Dim msg As Integer
    i = 3

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

End Function

대망의 중복확인 코드입니다. 중복확인 코드는 도대체 어떻게 생겼길래. 입고고 출고고 상관없이 데이터가 중복되는 것을 막는 걸까요?
 
1) Do While (Not IsEmpty(Worksheets(ttype).Cells(i, 2).Value))
워크시트 ttype의 셀 i행 2열의 값이 비어있지않으면 반복하라라는 뜻의 코드입니다.
i행 2열의 뜻은 이제 익숙해서 다들 아시죠? Do while문 안에 i = i + 1이 있는거보아 역시입니다. i의 초기값인 3행부터 비어있는 셀을 찾을때까지 반복하는 코드입니다.
 
그렇다면 워크시트 ttype은 뭘까요? 중복확인의 문자열형 인수군요.
2번과 3번에서 overlap = 중복확인("입고") 또는 overlap = 중복확인("출고")로 되어있던거 다들 기억하시죠?
위 코드가 있으면 중복확인이라는 프로시저가 실행될 때 인수로 "입고" 또는 "출고"라는 문자열을 받겠네요.
 
그러면, Worksheets(ttype)은 워크시트 "입고" 또는 워크시트 "출고"라는 것을 알 수 있군요!
 
2)  If (Worksheets(ttype).Cells(i, 2).Value Like Worksheets(ttype & "견적서").Cells(3, 7).Value)
워크시트 ttype의 i행 B열의 값이 ttype & "견적서" 워크시트의 3행 7열(3행 G열)의 값과 같으면 조건문을 실행한다는 의미입니다.
 
여기서 ttype은 아까 말씀드린대로 "입고" 또는 "출고"라는 문자열을 담은 변수이고 "입고"와 "출고" 시트의 B열에는 거래번호를 넣었었죠.
 
그렇다면, "입고" 또는 "출고"의 거래번호가 ttype & "견적서"라는 워크시트의 3행 7열과 같으면 실행을 한다는 뜻인거같은데..
ttype & "견적서"는 무슨 워크시트를 말할까요?
 
아하! ttype이 "입고" 또는 "출고"라는 값을 담고있으니 "입고" & "견적서" 또는 "출고" & "견적서"라는 뜻일 것이고 &는 문자열을 합칠때 사용되니 "입고견적서" 또는 "출고견적서"를 의미하겠네요!
 
그리고 워크시트 "입고견적서"와 "출고견적서"는 둘다 3행 7열(3행 G열)에 거래번호가 들어가있군요.([그림 1] 참조)
 
결론적으로 "입고" 또는 "출고"에 입력된 거래번호가 "입고견적서" 또는 "출고견적서"와 동일하다면 실행이 되겠네요!
 
3)
overlap = True
msg = MsgBox("이미 등록된 견적서입니다", vbYesOnly, "중복경고")
Exit Do
 
이제 if문 조건이 맞으면 어떤 코드가 실행되는지 볼까요?
일단 overlap은 초기값인 False에서 True로 바꼈네요. overlap은 중복의 여부를 의미한다고 하였었죠? 견적서에 있는 거래번호가 "입고"나 "출고" 시트에 이미 입력이 되어있다면 중복이라는 뜻이니 True로 바껴야겠네요.
 
MsgBox는 메세지 박스를 띄우는 함수인데요. 내용, 단추유형, 제목 순으로 인수를 받습니다. 그리고 MsgBox는 본질적으로 사용자의 선택을 받는 함수이므로 사용자의 선택에 대한 return값을 넣을 변수에 결과를 대입하는 것도 잊지마세요(안그러면 에러납니다.)
 
Exit Do는 Do while문의 종료를 의미합니다. 중복을 의미하여 if문안으로 들어왔으니 더이상 중복인지 확인할 필요없이 반복문을 종료하여 연산횟수를 낮추기위해 넣은 코드입니다.
 
4) 중복확인 = overlap
앞서 Function은 Sub와 다르게 반환이 되는 프로시저라고 언급하였는데요. VBA는 Function 프로시저 종료 후 반환을 받기위해 자신의 프로시저명에 반환하고 싶은 값을 대입하는 형태를 사용합니다.
 
if문이 실행이되어쓰면 overlap이 True, 실행이 안됬으면 overlap이 False로 반환을 하게될텐데요.
중복확인이라는 프로시저가 종료가 되면 다시 중복확인이라는 프로시저를 호출한 프로시저로 돌아가서 호출 다음 코드부터 실행되는데, overlap = 중복확인("입고" or "출고")에서 중복확인("입고" or "출고")이 중복이라면 True, 중복이 아니라면 False로 바뀌어 overlap에 대입됩니다.
 
그리고 그 결과에 따라
앞서 설명한 코드(입고견적서입력, 출고견적서입력 프로시저에 모두있는 코드)인
 
If (Not overlap) Then
-> overlap이 True이면 실행안함, False이면 실행함.
 
가 중복여부에 따라 실행을 할지안할지(추가를 할지안할지) 결정되겠네요.
 
이렇게 오늘은 중복방지를 위한 코드에 대해 설명을 드렸는데요.
 
도움이 되셨다면 좋아요 댓글 부탁드리고
 
피드백과 질문은 모두 환영입니다.