[컴퓨터활용능력 1급 실기] 프로시저 작성 (액셀/스프레드시트) ✨

프로시저 작성 (액셀/스프레드시트)

개념

 

• 프로시저(Procedure) : 한 개 이상의 기능을 수행하기 위한 명령문의 집합
• 액셀에서의 프로시저
  • VBA를 이용하여 액셀 작업을 자동화시킬 수 있는 도구로서 유용하게 활용됨.
• 프로시저의 종류
  • Sub 프로시저 : 정해진 명령대로 작업을 수행하고, 결과를 반환하지 않는 프로시저
  • Function 프로시저 : 결과를 반환하는 프로시저
  • Property 프로시저 : 개체의 속성을 정의할 때 사용하는 프로시저
• 모드
  • 실행 모드 : 삽입된 컨트롤을 실행할 수 있는 모드
  • 디자인 모드 : 컨트롤에 기능을 부여하기 위해 코드를 연결하거나 컨트롤의 모양을 변경할 수 있는 모드
• 코드의 실행
  • [F5]를 누르거나 '표준' 도구 모음의 실행 아이콘(▶)을 누르면 현재 입력한 코드의 실행 결과를 확인할 수 있음.

 

매크로 사용 예 1

매크로 사용 예 2

 

참고

• 컴퓨터활용능력 1급 시험에서는 각 5점씩 총 3문제가 출제됨.
  • 매회 출제되는 3문제 모두가 기출문제와 다르게 출제된 적은 한 번도 없음.
  • 최소한 5점 이상은 맞힌다는 생각으로 공부해야 함.
• VBE에서는 개체명을 입력하고 마침표(.)를 누르면 해당 개체에서 사용할 수 있는 메서드 및 속성이 표시되고, 첫 글자만 누르면 해당 메서드나 속성으로 이동함.
  • 해당 메서드나 속성이 선택되었을 때 [Ctrl] + [Enter]를 누르면, 선택된 메서드나 개체가 프로시저에 입력되고 같은 줄에서 계속 자료를 입력할 수 있음.
• 컨트롤의 이름은 사용자가 임의로 만드는 것이지만, 실제 문제에 주어진 폼에는 이미 각 컨트롤에 이름이 정의되어 있음.
• 'txt판매일자.Value = Date'
  • 'txt판매일자 = Date'와 같이 '.Value'를 생략해도 됨.
  • 하지만 반드시 Value를 붙여야 하는 경우가 있음.
    • 'txt판매일자'의 값을 날짜 데이터로, 'txt수량'과 'txt단가'의 값을 숫자로 워크시트에 입력하려면 반드시 'txt판매일자.Value', 'txt수량.Value', 'txt단가.Value'와 같이 'Value' 속성을 붙여야 함.
      • 'Value' 속성을 생략하면 데이터가 텍스트로 입력됨.
• Range는 워크시트의 셀이나 셀 범위를 선택하는 속성으로, 셀 주소에 대괄호([])를 붙일 경우 생략이 가능함.
  • 예) 다음은 같은 표현임.
    • range("b3").CurrentRegion.Rows.Count
    • [b3].CurrentRegion.Rows.Count

 

VB 사용 예

워크시트의 단추를 클릭하면 폼이 열리도록 설정하기

 

• 코드 예
  • 워크시트의 '판매입력' 단추를 클릭하면 폼이 열리도록 설정하기

Private Sub 판매입력_Click()
    판매자료입력.Show
End Sub

 

 

폼이 실행되면 자동으로 기능 실행하기

 

• 코드 예
  • 폼 실행 시, 자동으로 TextBox, ListBox, ComboBox에 항목 넣기(초기화 하기)

Private Sub UserForm_Initialize()
    txt판매일자.Value = Date                     '판매일자 폼에 현재 날짜 표시하기
    lst제품목록.RowSource = "I4:I13"             '제품목록 리스트에 항목 넣기 (워크시트 참고)
    cmb결재형태.AddItem "현금"                   '결재형태 콤보박스에 항목 넣기
    cmb결재형태.AddItem "카드"                   '결재형태 콤보박스에 항목 넣기
    cmb결재형태.AddItem "어음"                   '결재형태 콤보박스에 항목 넣기
    
    'cmb결재형태.List = Array("현금", "카드", "어음")을 입력해도 동일한 결과가 출력됨.
    '만약 콤보 상자가 결재코드와 결재형태를 나타내는 2개의 열로 구성된다면,
    '다음과 ColumnCount 속성을 2로 지정해야 두 열이 표시됨.
    'cmb결재형태.ColumnCount = 2
End Sub

 

 

폼의 자료를 워크시트에 입력하기

• 코드 예

Private Sub cmd등록_Click()
    If txt제품명.Value = "" Then
        MsgBox "제품명을 입력하시오."
    ElseIf txt수량.Value = "" Then
        MsgBox "수량을 입력하시오."
    ElseIf txt단가.Value = "" Then
        MsgBox "단가를 입력하시오."
    ElseIf cmb결재형태.Value = "" Then
        MsgBox "결재형태를 입력하시오."
    Else
        기준행위치 = [b3].Row           'Row : 셀의 행
        기준범위행수 = [b3].CurrentRegion.Rows.Count      'CurrentRegion : 지정된 셀과 연결된 범위, Rows : 범위에 있는 데이터의 행들, Count : 개수
        입력행 = 기준행위치 + 기준범위행수
        
        Cells(입력행, 2) = txt판매일자.Value
        Cells(입력행, 3) = txt제품명.Value
        Cells(입력행, 4) = txt수량.Value
        Cells(입력행, 5) = txt단가.Value
        Cells(입력행, 6) = Format(txt수량.Value * txt단가.Value, "currency")   'Format() : 숫자에 서식을 지정하는 함수, "currency" 통화기호를 붙임.
        Cells(입력행, 7) = cmb결재형태.Value
        
        txt제품명.Value = ""    '새로운 데이터를 입력받기 전에 컨트롤들에 현재 입력된 값을 지움.
        txt수량.Value = ""
        txt단가.Value = ""
        cmb결재형태.Value = ""
    End If
        
End Sub

 

 

워크시트의 자료를 폼에 표시하기

• 코드 예
  • 시트의 표에 입력된 제일 마지막의 '판매날짜', '제품명', '수량', '단가', '결재구분'을 폼에 표시하기

Private Sub cmd조회_Click()
    기준행위치 = [b3].Row
    기준범위행수 = [b3].CurrentRegion.Rows.Count - 1  '-1은 기준행(3행)을 제외하고, 순수하게 데이터 범위에 대한 행 수를 구하기 위해 사용됨.
    입력행 = 기준행위치 + 기준범위행수
    
    txt판매일자.Value = Cells(입력행, 2)
    txt제품명.Value = Cells(입력행, 3)
    txt수량.Value = Cells(입력행, 4)
    txt단가.Value = Cells(입력행, 5)
    cmb결재형태.Value = Cells(입력행, 7)
End Sub

 

 

폼 종료하기

• 코드 예

Private Sub cmd종료_Click()
    Unload Me
    '현재 작업중인 폼을 화면과 메모리에서 제거함.
    'Me : 현재 작업중인 폼 (다른 폼을 지정하려면 폼의 이름을 정확하게 기록해야 함.)
    '"Unload 판매자료입력" 이라고 입력해도 같은 결과가 나옴.
End Sub

 

 

셀의 위치나 데이터가 변경(Change)되면 해당 셀의 특징을 변경시키기 (이벤트)

 

• 코드 예

Private Sub Worksheet_Change(ByVal Target As Range)
    Target.Activate	    	  '해당 셀로 셀 포인터를 이동시키기
    Target.Font.Name = "바탕체"    '폰트를 "바탕체"로 변경
    Target.Font.Size = "14"        '폰트 크기를 14로 변경
End Sub

 

 

워크시트의 Activate 이벤트에 기능 설정하기

 

• 코드 예

Private Sub Worksheet_Activate()
    [b1] = "컴활합격"
End Sub

 

 

체크박스 기능 구현하기

 

• 코드 예
  • '성별'의 체크박스(chk성별)를 체크하면 '남학생', 체크를 해제하면 '여학생'이 '성별(cmd성별)'에 표시되도록 하기

 

Private Sub chk성별_Click()
    If chk성별.Value = True Then       '체크박스가 체크될 경우
        cmd성별.Caption = "남학생"
    Else                               '체크박스가 체크되지 않을 경우
        cmd성별.Caption = "여학생"
    End If
End Sub

 

 

스핀 단추 기능 구현하기

 

• 코드 예
  • '국어(spin국어)'의 스핀 단추를 누르면 5씩 증감된 숫자가 '국어(txt국어)'에 표시되도록 하기

Private Sub spin국어_Change()
    txt국어.Value = spin국어.Value * 5  '"spin국어" 컨트롤의 값에 5를 곱해 'txt국어' 컨트롤에 표시
End Sub

 

 

콤보 박스에 항목 넣기

 

• 코드 예 (방법1)
  
  • '결재형태(cmd결재형태)' 에 "현금", "카드", "어음" 항목 넣기
  • AddItem 이용

Private Sub UserForm_Initialize()
    cmb결재형태.AddItem "현금"        
    cmb결재형태.AddItem "카드"            
    cmb결재형태.AddItem "어음"          
End Sub

 

• 코드 예 (방법2)
  
  • '결재형태(cmd결재형태)' 에 "현금", "카드", "어음" 항목 넣기
  • List, Array 이용

Private Sub UserForm_Initialize()
    cmb결재형태.List = Array("현금", "카드", "어음")
End Sub

 

• 코드 예 (방법3)
  
  • '결재형태(cmd결재형태)' 에 "현금", "카드", "어음" 항목 넣기
  • With, AddItem 이용

Private Sub UserForm_Initialize()
	With cmb결재형태
            .AddItem "현금"
            .AddItem "카드"
            .AddItem "어음"
	End With
End Sub

 

 

리스트에 항목 넣기

 

• 코드 예
  
  • '제품목록(lst제품목록)' 에 워크시트의 항목(I4:I13) 넣기 

Private Sub UserForm_Initialize()
	lst제품목록.RowSource = "I4:I13"         
End Sub

 

 

컨트롤로 포커스 설정하기

 

• 코드 예
  
  • 폼이 실행되면 '판매일자(txt판매일자)' 컨트롤로 포커스가 옮겨가도록 하기

Private Sub UserForm_Initialize()
	txt판매일자.SetFocus    
End Sub

 

 

메시지 상자 출력하기

 

• 코드 예
  
  • '작업종료(Cmd종료)' 버튼을 누르면 현재 날짜와 시간을 표시하는 메시지 박스를 표시하고, 폼을 종료시키기
  • MsgBox (메시지 내용) (박스 유형) (메시지 박스 제목)

Private Sub Cmd종료_Click()
    MsgBox Now, vbOKOnly, "작업을 종료합니다."  'vbOKOnly : <확인> 단추만 표시
    Unload Me
End Sub

 

 

기출 문제 예시

Q. <성적입력폼>에 데이터를 입력하고 '등록(cmd등록)' 단추를 클릭하면 폼에 입력된 '이름(txt이름)', '성별(cmd성별)', '국어(txt국어)', '수학(txt수학)'과 '평균', '평가' 가 계산되어 '기출01' 시트의 표에 입력되도록 프로시저를 작성하시오.

 

• '이름' 앞에 입력되는 순서를 나타내는 번호를 입력하시오.
• '평균'은 입력받은 '국어', '영어', '수학' 값의 평균이고, 소수 2자리까지 표시하시오.
• '평가'는 '평균'과 [평가표]를 참조하되, Select ~ Case 명령문을 이용하여 산출하시오.
• 폼의 '등록(cmd등록)' 단추를 클릭하면 폼의 '이름(txt이름)', '국어(txt국어)', '영어(txt영어)', '수학(txt수학)'에는 새로운 값이 입력될 수 있도록 설정하시오.

※ 데이터를 추가하거나 삭제하여도 항상 마지막 데이터 다음에 입력되어야 함.

• 코드

Private Sub cmd등록_Click()
    기준행위치 = [b3].Row
    기준범위행수 = [b3].CurrentRegion.Rows.Count
    입력행 = 기준행위치 + 기준범위행수
    
    Cells(입력행, 2) = 입력행 - 3 & txt이름.Value
    Cells(입력행, 3) = txt국어.Value
    Cells(입력행, 4) = txt영어.Value
    Cells(입력행, 5) = txt수학.Value
    Cells(입력행, 6) = Format((Val(txt국어) + Val(txt영어) + Val(txt수학)) / 3, "0.00")  
    '소수 2자리까지 표시
    'Val() : 숫자 형식의 문자를 숫자로 변환하는 함수
    
    Select Case Cells(입력행, 6)
        Case Is < 60
            Cells(입력행, 7) = "가"
        Case Is < 70
            Cells(입력행, 7) = "양"
        Case Is < 80
            Cells(입력행, 7) = "미"
        Case Is < 90
            Cells(입력행, 7) = "우"
        Case Else
            Cells(입력행, 7) = "수"
    End Select
    
    Cells(입력행, 8) = cmd성별.Caption
    
    txt이름.Value = ""
    txt국어.Value = ""
    txt영어.Value = ""
    txt수학.Value = ""
End Sub

 

 

Q. <고객관리> 폼의 '고객명(txt고객명)'에 조회할 '고객명'을 입력하고 '고객조회(cmd고객조회)' 단추를 클릭하면 워크시트의 [표1]에서 해당 데이터를 찾아 각각의 컨트롤에 표시하고 해당 고객의 정보가 없는 경우에는 "조건에 일치하는 자료가 없습니다."라는 메시지 박스가 표시되도록 프로시저를 작성하시오.

 

 

• For Each ~ Next문을 이용하시오.

• 코드

Private Sub cmd고객조회_Click()
    스위치 = 0
    참조행 = 3
    For Each aa In Range("D4:D7")
        참조행 = 참조행 + 1
        If aa.Value = txt고객명.Value Then
            txt고객등급.Value = Cells(참조행, 5)
            txt매출금액.Value = Cells(참조행, 6)
            txt결제방식.Value = Cells(참조행, 7)
            스위치 = 1
            Exit For
        End If
    Next
    If 스위치 = 0 Then
        MsgBox "조건에 일치하는 자료가 없습니다."
    End If
End Sub

 

 

Q. '보험종류(Cmb종류)'와 '가입지점(Cmb지점)'을 선택한 후 '성명(Txt성명)' 을 입력하고 '신청확인(Cmb확인)' 단추를 클릭하면 폼에 입력된 데이터가 [표1]에 입력되어 있는 마지막 행 다음에 연속하여 추가입력 되도록 작성하시오.

 

 

• 폼에서 선택된 보험종류에 해당하는 '월납부액', '납부총액', '이자총액'을 [참조표]에서 찾아 [표1]에 표시하시오. (LastIndex 속성 이용)

※ 데이터를 추가하거나 삭제하여도 항상 마지막 데이터 다음에 입력되어야 함.

 

• 코드

Private Sub Cmd확인_Click()
    기준행위치 = [A1].Row	'[a1] 셀에 있는 행이 데이터 표의 맨 첫 째 줄로 인식됨. 엑셀에서는 내용에 관계없이 빈 행이 나올 때까지를 하나의 연결된 표로 봄.
    기준범위행수 = [A1].CurrentRegion.Rows.Count
    입력행 = 기준행위치 + 기준범위행수
    
    참조행 = Cmb종류.ListIndex + 7
    'ListIndex : 콤보 상자 컨트롤의 목록 부분에서 선택한 항목의 인덱스 번호를 반환하거나 설정하는 속성
    '0부터 시작하므로, 콤보 상자에서 첫 번째 값을 선택하면 0, 두 번째 값을 선택하면 1이 반환됨.
    
    Cells(입력행, 1) = Cmb종류.Value
    Cells(입력행, 2) = Cmb지점.Value
    Cells(입력행, 3) = Txt성명.Value
    Cells(입력행, 4) = Cells(참조행, 9)     '참조행의 9열 값 표시
    Cells(입력행, 5) = Cells(참조행, 10)    '참조행의 10열 값 표시
    Cells(입력행, 6) = Cells(참조행, 11)    '참조행의 11열 값 표시
End Sub

 

 

Q. <온라인수강신청> 폼의 '입력(Cmd입력)' 단추를 클릭하면 폼에 입력된 데이터를 시트의 안에 추가하되, List, Listindex를 사용하여 프로시저를 작성하시오.

 

 

• 목록 상자(lst과목)에서 과목을 선택했을 때에만 폼의 데이터를 워크시트에 입력되도록 설정하시오.
• 목록 상자(lst과목)에서 과목을 선택하지 않았으면 'ID(txtID)' 컨트롤에 '선택안함'을 표시한 후, 목록 상자(lst과목)의 첫 번째 항목을 선택하시오.
• 'ID(txtID)' 컨트롤에 소문자를 입력해도 워크시트에는 대문자로 입력되도록 설정하시오. (Ucase 이용)
• '할인수강료'는 '수강료-(수강료*0.1)'로 계산하여 입력하시오.
• 폼의 '입력' 단추를 클릭하면 새로운 값이 입력될 수 있도록 폼의 'ID(txtID)', '날짜(txt날짜)', '이름(txt이름)'에 입력된 데이터는 삭제하고, 목록 상자(lst과목)는 아무것도 선택되지 않은 상태로 설정하시오.

 

• 코드

Private Sub cmd입력_Click()
    If IsNull(lst과목.Value) Then
        lst과목.ListIndex = 0
        txtID = "선택안함"
    Else
        기준행 = [a3].Row
        행수 = [a3].CurrentRegion.Rows.Count
        입력행 = 기준행 + 행수
        참조행 = lst과목.ListIndex
        
        Cells(입력행, 1) = UCase(txtID.Value)     'ID (대문자로 변환)
        Cells(입력행, 2) = txt이름.Value   '이름
        Cells(입력행, 3) = lst과목.List(참조행, 0)  '과목코드
        Cells(입력행, 4) = lst과목.List(참조행, 1)  '과목명
        Cells(입력행, 5) = lst과목.List(참조행, 2)  '담당강사
        Cells(입력행, 6) = lst과목.List(참조행, 3) - (lst과목.List(참조행, 3) * 0.1)    '할인수강료
        
        txtID.Value = ""
        txt이름.Value = ""
        txt날짜.Value = ""
        lst과목.Value = ""
    End If
End Sub