각 산업별..무언가 풀어보고 만들어 보고 싶은
문제의식이 많으신 분들의 질문을 받으면
단발적으로 답이 되는 경우가 있고 몇개의 씨리즈로 풀어 나가야 할 경우가 있다
이 자재관련 모듈도 그런 경우다
자재청구서를 상위부서에 제출하여야 하는데
필요한 자재목록을 선택하면 자동으로 청구서에 옮겨지면 얼마나
편리할까..라는 문제인 것이다
이것을 몇개의 화일로 진행해 보자
질문하신 분은 건설자재분야이지만 대개의 모든 자재관리에
적용될수 있는 범용적으로 응용할수 있는 내용이다
처음에는 질문하신분이 만들었으면 하는 아이디어를 그대로
모듈로 만들어 본후
개선하는 방법을 보도록 하자
물론 개선방법은 본래 한도 끝도 없는 것이지만 조금더
편리하지 않을까 하는 정도까지 가 보도록 하자
질문 하신 분도 계속 문제의식을 갖고 도전을 해주셔야
자꾸 모듈이 추가 되어 나갈 것이다
대개 공략을 하다가 중간에 다른 일에 몰려서 포기하시다 보면
우노도 다른일에 쫓기면 중간에 흐지부지 하게 된다
그러니 질문하신분..해당분야의 자동화를 화끈하게 완성하셔 보시도록!!
STEP_1 ---------------------------------
대개의 현장에서 자재목록을 하나의 통합문서에 놓고 사용한다
시트댑에 자재구분별로 구분하고
한장의 시트에 전체자제의 분류목록을 만들어서
전체의 윤곽을 보는 시트를 갖는다
그리고 청구서양식시트는 각 조직마다 양식이 다르겠지만
청구서양식이 하나 있을 것이고
VBA를 전혀 모른다면 눈으로 각시트의 필요한 자재목록을 찾아보고
손으로 옮겨 입력하는 작업을 하여야 할 것이다
이것을 자동화하는 작업이다
가장 좋은 방법은 전체 자재목록을 DB에 넣고..#@#$@# 그리고
작업을 하는 것이 궁극적 목표이지만..우선 현장에서 사용하는
화일을 중심으로 해본다
각자재분류시트명으로 하이퍼링크로 목록을 만들수 있을 것이다
한장의 시트에 시트명을 [목록표]라고 한후
이 시트를 열때마다 자재분뷰별목록시트의 갯수를 확인하여
보관하고 있는 갯수와 다르면 항상 [목록표]시트를 자동갱신하게 할수도 있겠으나
이것은 활성화할때마다 작업을 시도하게 되므로 번거롭다
모든 시트의 A1셀을 더블크릭하면 어떤 작업을 하게 하는 것이 편리할 것이다
[목록표]시트의 A1셀을 더블크릭하면 목록을 갱신하는 프로시져가 실행되게 하고
각 자재목록시트의 A1셀을 더블크릭하면 [목록표]시트로
이동하게 하는 것이 가볍고 효율적일 것이다
첫번째셀을 더블크릭하였는지 아는 방법은
If Target.Address(False,False)="A1" Then
.....
End If
와 같이 Address(True,True)는 $A$1과 같이 절대주소값을 문자열로 얻고
Address(False,False)는 A1과 같이 상대주소값을 얻는다
그런데 만들다 보면 참으로 경우의 수가 많다
마침 어떤시트의 A1셀이 다른 셀과 병합이 되어있다면
얻어지는 주소는 A1:C3 등과 같이 병합된 셀의 주소를 얻게 된다
그래서 이런 것도 감안하게 한다면
If InStr(Target.Address(False,False),"A1")>0 Then
이라고 하면 두가지 경우를 모두 만족시켜주게 될 것이다
하이퍼링크같은 것을 만들때 이런 것은 자주 사용하지 않지만
종종 필요하다
uno-weekly의 코드라이브러리같은 곳을 평소에 잘 관찰하면
그냥 복사하여다가 상황에 맞게 매개변수나 조건을 조금 수정하여
사용하면 빠르고 편리할 것이다
Sub CreateHyperLinks(rLink As Range)
Dim shtX As Worksheet
rLink.EntireColumn.Clear
For Each shtX In ThisWorkbook.Worksheets
If shtX.Name <> rLink.Parent.Name Then
rLink.Hyperlinks.Add rLink, ThisWorkbook.Name, _
"'" & shtX.Name & "'!A1", shtX.Name, shtX.Name
rLink.Font.Bold = True
rLink.Font.Underline = False
Set rLink = rLink.Offset(1, 0)
End If
Next
End Sub
이렇게
화일내의 시트간 이동의 자동화를 완성했다
***[LOG-IN]***
STEP_2 ---------------------------------
자재청구서양식을 하나 준비하여 놓고
각분류별 자재목록워크시트의 자재를 선택하던가, 더블크릭하면
자재청구서에 자동으로 해당 아이템이 옮겨지게 하고 싶은 것
자재청구서양식을 매번 썼다 지웠다 하면서 사용할까..
아니면 원본은 잘 보관하여 두고 필요할때마다 복사하여 사용할까?
라는 것도 결정하여야할 문제가 될 것이다
원본은 그냥 잘 보관하고 복사하여 사용하도록 해보자
복사하면서 새로운 통합문서로 분리되어 간단하게 분리되게 할수 있다
아래와 같이
Dim oBook As Workbook
Dim oSht As Worksheet
Worksheets("자재청구서").Copy
Set oBook = ActiveWorkbook
Set oSht = oBook.Worksheets("자재청구서")
통합문서를 새로 만들고, 시트를 복사하여 옮기고 어쩌구 할 것없이
그냥 Copy메소드가 통합문서를 새로 만들고 복사된 시트가
새로만들어진 통합문서에 붙여넣어지는 빠른 방법이다
새로만든 통합문서의 이름을 [날자]+[현장명]+[일련번호]+.xls 로 하여두고 싶다
그러면 나중에라도 몇일에서 부터 몇일까지 신청한 자재목록을
주루룩 다시 찾아볼수 있을 것이다..
어떤 규칙에 맞추어 나중에 프로그래밍적으로 찾아보기 쉽게
일종의 DB역할이 되게 보관하는 것이다
약식시트에는 각셀별로 이름을 붙여 두는 것이 관리하기 쉬워지는 것이니
양식을 잘 만들어서 이름을 잘 붙여 두면 되는 것이다
또 한 양식에 공통적을 입력되어야 할 메타데이타가 있다
이것을 어디에서 입력을 하여야 할텐데..어디서 하지..?
[목록]시트의 한 범위를 사용하자
프로그래밍을 한다고 하는 것은 무엇일까?
대단한 것을 하는 것이 아니다
정리정돈를 잘하는 것이 프로그래밍이다
정말 고지식할 정도로 정리정돈을 잘한다면 프로그래머의 소질이 있는 것이다
정리정돈을 생활화할때 이미 프로그래머인 셈이다
위의 그림에 사용자가 기본적으로 입력하는 정보가 있다
이 정보를 받아서
프로그래밍에서 정리정돈의 기본작업은 변수,상수의 정리정돈에서 시작된다
컴퓨터프로그래밍은 정보처리전문가인 것이니
당연히 프로그래밍내에서 사용할 정보를 정리정돈이 필수적임은
두 말 할 필요가 없는 것이다
아래와 같이 정리해보자
Public Const COMPANY_NAME As String = "companyname"
Public Const COMPANY_NAME_ As String = "업체명"
Public Const SITE_NAME As String = "sitename"
Public Const SITE_NAME_ As String = "현장명"
Public Const TEAM_NAME As String = "teamname"
Public Const TEAM_NAME_ As String = "팀명"
Public Const STATUS As String = "status"
Public Const REQUEST_DATE As String = "requestdate"
Public Const REQUEST_DATE_ As String = "발주일"
Public Const SUPPLY_DATE As String = "supplydate"
Public Const SUPPLY_DATE_ As String = "입고일"
Public Const WORK_NAME As String = "workname"
Public Const WORK_NAME_ As String = "공종명"
Public Const HP As String = "hp"
Public Const HP_ As String = "핸드폰"
Public Const REQUESTER As String = "requester"
Public Const REQUESTER_ As String = "청구자"
Public Const MEMO As String = "memo"
Public Const MEMO_ As String = "메모"
Public Const LIST_START_CELL As String = "liststart"
값을 전달하는 쪽과 받는 쪽의 범위에 이름을 만들어주고
위와 같이 해당이름에 접근하기 편한 상수로 분명히 해주면
프로그래밍이 명쾌해진다
Range(COMPANY_NAME).Value=Range(COMPANY_NAME_).Value
라고 하면 간단하게 헷갈리지 않고 정보가 전달된다
그러지 않으면 코딩할때 마다 이름이 헷갈려서..시간을 까먹고
지루해지고 혼돈 스러워진다
내공이 쌓인다고 하는 것은 이런 습관이 점점 어떤 패턴화되어가면
좀더 쉬워지고, 자신만의 철학이 쌓이게 된다
준비작업은 항상 귀찮고 지루하다
하지만 잘 해두면 몇달 후에 다시 딜다 보아도 계속 진행할수 있는
명료한 코딩, 코딩자체가 설명이 되는 셈이다
당연히 에러가 나도 찾기 쉽다
두번째가 정보의 검증이다
아래와 같은 경우는 날짜정보를 전달한다
초보님들은 그냥 전달한다..
Range(SUPPLY_DATE).Value=Range(SUPPLY_DATE_).Value
그러나 사용자가 입력할때, 엉뚱한 문자를 입력하거나
빈셀로 실행하거나..많은 입력상의 통제가 필요하게 될 것이다
이런 검증작업이 또한 지루한 작업이지만 필수적인 작업이다
경험이 있다면 검문소설치가 습관화된다
If IsGood(Range(SUPPLY_DATE_)) Then
Range(SUPPLY_DATE).Value=Range(SUPPLY_DATE_).Value
Else
MsgBox "Sorry!!! Wrong Datas!! Try Again!!"
End If
ThisWorkBook이라는 속성을 잘 활용하는 것이 좋다
예를 들면..
Worksheets("자재청구서").Copy ''''''''''''''''새로운 통합문서가 자동으로 만들어진다
Set oBook = ActiveWorkbook ''''''''''''''''''새로 만들어진 통합문서를 oBook변수에 담는다
Set oSht = oBook.Worksheets("자재청구서") ''''
''''' 이때 모듈시트가 있던 통합문서의 시트를 참조할때 Worksheets("목록표")
''''''라고 하면 에러가 난다..새통합문서에서 "목록표"라는 시트를 찾게 된다
''''''이런 일을 방지하기 위하여 여러개의 통합문서작업을 할때는
''''''항상 모듈시트가 있는 통합문서는 ThisWorkBook속성으로 명시하여 주는 것이 좋은
''''''습관이다
Set oshtList = ThisWorkbook.Worksheets("목록표")
***[LOG-IN]***
STEP_3 ---------------------------------
이제 자재를 청구할 목록을 수집하여 청구서에 어떻게
뿌려줄 것인가?
위의 정도까지는 이것 저것 응용하여 하기는 하겠는데..
이런 떼거리 정보(요청할 아이템이 하나가 아니니까..)
초보님들이 고민이 많이 생기는 부분이다
방법은 여러가지가 있을 것이다
초보에서 가장 쉽게 확장될 수 있는 방법을 생각해 보자
질문하신분의 생각대로 하자면
각 분류별 자재목록시트에 가서 원하는 품목을 더블크릭하면
신청서에 하나씩 추가되게 하고 싶다고 하셨다
이때 각 품목별 신청량은 나중에 신청서에 자동으로 등록된 품목에
입력을 하면 될것이라고 생각한 부분까지 만들어 보자
우선 더블크릭할때 만들어진 신청서에 접근하여 입력을 하여야 하니
신청서개체(워크시트)를 생성할때 전역변수에 잡아넣어두는 것이
좋을 것이고..(STEP_2의 수정의 필요)
이 페이지의 것을 질문하신 분은 지금 온통 머리속에
무언가 직관적으로 사내의 많은 분들이 잘 사용하는 방법이 없을까
라는 생각으로 꽉 차 있을 것이다
시시각각, 이렇게 하면 좋을까..저렇게 하면 좋을까..
그런 생각이 계속되는 것은 사람을 활성화시킨다
아무튼 최초에 질문하였던 것을 우선 만든후..보내오시는
개선방안등을 하나,하나 나중에 반영해 나가도록 하자
목록표에서 분류시트를 크릭하여 해당 시트로 가서
원하는 품목을 더블크릭한다
그러면 [자재신청서]에 하나씩 목록이 추가된다..
그렇게 한다면 문제가 있다..등록했다가 삭제하고 싶다면 어떻게하지??
손작업으로 지운다면 재미없지..
어떤 방법이 좋을까..
임시시트를 하나 만들자
청구하고싶은 품목은 모두 이 시트에 줄줄이 입력된다
그리고 이시트를 최종적으로 살펴보고 삭제하고 싶은 것은
더블크릭하여 삭제하게 한다
그리고 눈으로 확인후 한꺼번에 [자재청구서]양식으로 옮기도록 한다
01)자재목록시트의 원하는 품목을 더블크릭한다
02)[임시시트]가 있는지 확인한다
03)[임시시트]가 없으면 만든다
04)[임시시트]에 더블크릭한 항목을 추가한다
05)계속 반복한다
06)[임시시트]로 가서 눈으로 확인한다
07)[임시시트]의 추가된 항목중 마음에 안드는 것은 또한 더블크릭하여 자동삭제
08)[임시시트]의 모든 내용이 마음에 들면 A1셀을 더블크릭하면
[자재청구서]시트로 옮겨진다
10)이때 [자재청구서]양식에 넘치면 행을 추가 삽입하면서 수행한다
10)[자재청구서]시트의 각항목의 수량을 입력한다
11)[자재청구서]를 닫는다 자동으로 [날짜]+[현장명]+[일련번호]로 화일명을 부여
좀 허접한 씨나리오지만 초보님들이 생각할수 있는 가장 근접한 아이디어
이것이 전략이고, 이것을 구사할 전술이 필요하다
전략은 프로그래머가 아니라도 세울수 있다
그러니 VBA에 대한 상식,엑셀에 대한 상식을 갖춘다면
아래직원들에게 어떻게,어떻게 하라고 지시라도 제대로 할 수 있을 것이다
다른 통합문서가 닫을때 자동으로 이름을 부여할 프로시져를
어디에서 넣지???
새로운 통합문서에는 코드가 없는데..
일련번호를 붙이는데..일련번호의 앞번호는 어디서 찾지?
더블크릭하면 자재가 옮겨지는데 중복되는 것을 멍청히 계속 더블크릭하면
중복되는 것이 계속 추가될텐데..이것도 바보같은 짓이니..
통제를 해야겠는데 어떻게 하지..
시각적으로 이미 신청서에 올린 품목이라는 것으로 표시를 하면 좋겠구나..
라는 생각도 할 수 있을 것이고
거의 더블크릭하면 처리되는 것이니..DoubleClick 이벤트프로시져가
엄청 바쁘겠네..Select Case문을 잘 정리해야겠는데?
좀 불편한 점이 많다..
그런 문제점을 안고 나중에 개선하도록 하고,우선 위의 내용을 구현해 보자
위의 것이 질문한분의 대강의 씨나리오였으니까..
3번째 화일에서는
각 자재시트의 신청하고 싶은 자재명이 있는행은 더블크릭하면
[임시시트]가 없으면 새로만들어지면서
이곳에 차곡차곡 더블크릭하는데로 집계되어 간다
이미 등록한 품목은 색상으로 표시하여 다시 추가하지 못하게 한다
[임시시트]에 가서 보면 주욱 등록이 되어있고
이곳에 버튼이 하나 만들어진다
이 부분 까지..
***[LOG-IN]***
STEP_4 ---------------------------------
[임시시트]에 모여진 신청목록에서 다시 한번 눈으로 확인하고
삭제할 것이 있으면 삭제하고, 이것은 그냥 행머리선택삭제의
엑셀메뉴기능을 사용해도 괜찮을 것이다
이곳에서 수량을 입력한후
[임시시트]가 만들어질때 시트에 만들어진 버튼을 크릭하면
[자재청구서]시트와 새로운 통합문서가 만들어지고
이곳에 옮겨진다
그리고 [날짜]+[현장명]+[일련번호]로 저장하고 닫고
[임시시트]자동삭제되고, 각 분류별자재시트상의 색상표시를
모두 원래대로 원상복구 자동화하고 ..작업끝이 되겠다
특기할 만한 것은 화일을 저장할때 일련번호로 저장하는데
이미 같은 번호가 있는지 확인하고 있으면 다음 번호를 부여하는
작업을 하여야 할 것이다
아래와 같은 함수를 사용한다
' 매개변수 sFilename 을 날짜와 현장명을 조합하여 함수에 보내면
' 함수에서 일련번호 작업을 하여 돌려주게 한다
Function getAfterCheckOlds(sFileName As String)
On Error Resume Next
Dim sPathFile As String, sTemp As String, iNext As Integer
Do
iNext = iNext + 1
sTemp = ""
sPathFile = ThisWorkbook.Path & "\" & sFileName & "_" & iNext & ".xls"
sTemp = Dir(sPathFile)
' sTemp변수에 값이 들어오면 해당 화일명이 존재하는 것
' 그럼 또 다시 순환하면서 일련번호 iNext값을 증가해 나간다
Loop While sTemp <> ""
getAfterCheckOlds = sPathFile
End Function
만들고 보니까..꽤 생산성이 높을 것 같고
학습하는 입장에서도 다른 부분에 많이 확장적으로 응용할 만한
내용들이 많았다
요기까지는 질문하신분의 업무자동화아이디어를 표현한 것이니까..
하다 보면 추가적 기능, 에러의 발생등 다양한 의견이 있을 것이니
질문하신 분이나 다른 관심있는 분들이 메일주시면 또 계속 할 것이고
만약 오랫동안 별 문제가 없다면..
우노가 개선을 하거나 확장할 것임
***[LOG-IN]***