[110] Excel 기타작업 - 프로시저 작업 중급 (열 설정하기, ListIndex 명령)

바로 앞에서 배운 2010년 컴활 1급 프로시저 문제를 가져오겠습니다.

[문제]

<수강인원등록화면> 폼의 '등록(cmd등록)' 버튼을 클릭하면 

분류, 강좌명, 강사명, 강좌요일, 수강료와 수강인원(Text정원), 총수강료를 계산하여

[표1]에 입력되도록 프로시저를 작성하시오.

단, [총수강료 = 수강등록인원 * 수강료] 이고

강좌명, 강사명, 강좌요일, 수강료는 'List강좌'의 왼쪽부터 나오는 필드들임

밑에 사진을 보겠습니다.

앞선 포스팅에서 

프로시저 명령을 이용하여 빨간 막대기 부분을

행으로 지정했습니다.

이제는 열을 지정할 차례입니다.

엑셀에서 열은 A, B, C, D, E, ... 이렇게 알파벳 순으로 올라갑니다.

비쥬얼베이직에서는 각 알파벳으로 설정된 열을 숫자로 인식하는데

A열은 1, B열은 2, C열은 3, ... 이 됩니다.

앞에서 위 사진 빨간 막대기 행 부분을 r로 정의하겠다고 했습니다. 

즉, 빨간막대기는 r행인 것입니다.

6행 위에 빨간막대기가 있으니까 6행 아닌가요?

라는 의문을 가질 수도 있는데

r행은정확히 말하면 6행이아니라

Range("b5") . CurrentRegion . Rows . Count + 4 행입니다.

즉, 첫 행에서 +4를 한 행이 r행이라는 소리입니다.(단, 첫 행을 +1로 봅니다.)

만약에 현재 6행에 위치하고 있는 r행에서 

어떤 값이 입력된다면

그 순간

6행은 필드로 인식됩니다.

6행은 이미 필드로 인식되어있는 4,5행과 붙어있기 때문입니다.

앞에서 Count + 4를 할 때 필드 부분은 무시한다고 했으므로

6행에 값이 입력되어 필드로 간주된다면

Range("b5") . CurrentRegion . Rows . Count + 4 한 r행은

6행이 아닌 7행(빨간막대기)이 되는 것입니다.

만약에 7행에도 값이 입력된다면 그 순간

이미 필드로 인식되어있는 4,5,6행과 붙어있는 7행도 역시

필드로 인식되게됩니다.

그럼 Range("b5") . CurrentRegion . Rows . Count + 4 를 한 r행의 위치는 

8행으로 바뀌게 되는 것입니다.

다시 위 사진으로 돌아와서 빨간 막대기는 r행이라고 했고

A열은 1, B열은 2, C열은 3, ... 이라고 했습니다.

그러므로 

파란색 화살표 부분의 값은 r행, 1열 

주황색 화살표 부분의 값은 r행, 2열 

노란색 화살표 부분의 값은 r행, 3열 

보라색 화살표 부분의 값은 r행, 4열이 될 것입니다.

이런 엑셀의 위치를 프로시저 명령으로 나타내면

파란색 화살표는 Cells(r,1)

주황색 화살표는 Cells(r,2)

노란색 화살표는 Cells(r,3)

보라색 화살표는 Cells(r,4)가 됩니다.

이 프로시저 명령을 이용하여

위 사진처럼 내가 위치시키고자 하는

정확한 셀의 좌표를 선택할 수 있습니다.

다시 문제를 봅시다.

[문제]

<수강인원등록화면> 폼의 '등록(cmd등록)' 버튼을 클릭하면 

분류, 강좌명, 강사명, 강좌요일, 수강료와 수강인원(Text정원), 총수강료를 계산하여

[표1]에 입력되도록 프로시저를 작성하시오.

라고 작성되어 있습니다.

이제 각 셀에 대응하는 폼 항목들을 끼워 맞추면 작업이 완료됩니다.

해보겠습니다.

Cells(r,2) = Cob분류

라고 작성했습니다.

이 뜻은 곧 Cmd등록 버튼을 클릭했을 때

Cell(r,2)[빨간박스]에는 

'Cob분류'에 있는 항목[초록박스]이 적힌다는 뜻으로

위 사진처럼 되어 있는 폼 상태에서

등록 버튼을 누른다면

B6셀에 외국어 라는 글자가 적힐 것[갈색박스]입니다.    

그런데 Cells(r,3)부터 문제가 생겼습니다.

Cells(r,3) 즉 C6셀은 외국어명을 적는 곳인데

폼 안에서 외국어명 목록을 클릭하면

외국어명, 강사명, 강좌요일, 수강료 전부가 클릭되기 때문입니다[파란막대기]

이 때, ListIndex를 사용하면 문제가 해결됩니다.

ListIndex는 말 그대로

List(표 목록)에 있는 어떤 값을 Index(찾기, 색인)해준다는 의미를 가지고 있습니다.

현재 폼의 'List강좌' 에 강좌명[빨간박스]부분은 엑셀시트의 강좌명[빨간박스]이 폼 속으로 들어가 있는 것입니다. 

폼에서는 강좌명만 따로 선택할 수 없기 때문에

폼 안에서 선택한 부분과 

엑셀시트에 있는 표[초록박스] 부분을 연동한 후

왼쪽 [표 1]에 나타나게 해야 합니다.

그렇게 하기 위해서는

표[초록박스]의 위치를 프로시저 명령으로 지정해야 합니다.

그러려면 우선적으로 

앞선 포스팅에서 했던 것 처럼 표의 행과 열을 지정해야  합니다.

강좌명은 L열, 강사명은 M열, 강좌요일은 N열, 수강료는 O열 입니다.

숫자로 변환하면 강좌명은 12열, 강사명은 13열, 강좌요일은 14열, 수강료는 15열이 됩니다.

다시 비주얼베이직 화면으로 돌아가보겠습니다.

 

표[초록박스]의 열 부분은 프로시저 명령에서 빨간박스로 정해졌습니다.

그럼 행 부분[파란박스]은 어떻게 정해야 할까요?

ListIndex 명령을 사용해야 합니다.

예를 들어

rr = List강좌 . ListIndex + 8

이라는 명령을 작성했다고 합시다.

참고로 'List강좌'는 폼에서 목록을 나타내주는 흰색 판의 실제이름입니다. 

명령을 뜯어보면

 List강좌      -     List강좌(는)

.      -     는

List      -     리스트[초록박스 속 표]에서 찾을 수 있는데

Index     -    그 행이 시작하는 지점을 Index해 본 결과

+ 8      -     엑셀 시트 첫 행으로부터 + 8 행 만큼 떨어져 있다.

(첫 행 포함. 즉, 1행은 +1, 2행은 +2, 3행은 +3...)

=rr      -     그리고 그 행 지점을 rr이라고 정의하겠다.

이렇게 뜯어집니다.

위에서 정의된 rr은 제가 지정한 임의의 알파벳으로 

앞에 이미 r이라는 행을 지정해 놓았기 때문에 r을 한 번 더 붙인 것입니다.

다른 알파벳으로 바꿔도 됩니다.

위 명령에서 +8 은 데이터의 첫 부분을 찾기 위한 것입니다.

표[오른쪽 표] 필드의 바로 밑부분[빨간막대기]이 데이터의 첫 부분이므로 +8을 했습니다.

ListIndex는 행 계산을 할 때 필드도 행으로 포함해서 계산하기 때문에 +8을 하게 됩니다.

주의합시다.

이렇게 ListIndex 프로시저 명령을 작성하면

보라색으로 묶어진 부분끼리 서로 연동이 됩니다.

문제는 보라색으로 묶어지지 않은 부분입니다.

만약 'Cob분류[노란박스]'항목이 바뀌게 되면

'List강좌'의 목록도 위 사진[노란박스]처럼 바뀌게 되는데

이 바뀌게 되는 부분도 ListIndex 프로시저를 이용하여 설정해주어야 합니다.

Cob분류가 취미인 경우에 ListIndex 프로시저의 행은 + 13[초록막대기]

Cob분류가 컴퓨터인 경우에 ListIndex 프로시저의 행은 + 23[파란막대기]이 됩니다.

위 설정을 한 번에 통으로 해주기 위해

앞서 배웠던 Select 명령이나 If 명령을 사용합니다.

if 명령을 사용하여 ListIndex 명령을 완료해보겠습니다.

If Cob분류 = "외국어" Then

rr = List강좌.ListIndex + 8

ElseIf Cob분류 = "취미" Then

rr = List강좌.ListIndex + 13

Else

rr = List강좌.ListIndex + 23

End If

[빨간박스]

이렇게 작성하여 완료할 수 있습니다.

드디어 행 부분을 완성한 것입니다.

이제 위 사진의 파란박스 안에 방금 설정한 rr행을 집어넣어주면 됩니다.

rr행을 집어넣은 모습입니다.[파란박스]

이제 분홍박스 부분만 설정하면 모든 작업이 완료됩니다.

다시 문제를 보겠습니다. 

[문제]

<수강인원등록화면> 폼의 '등록(cmd등록)' 버튼을 클릭하면 

분류, 강좌명, 강사명, 강좌요일, 수강료와 수강인원(Text정원), 총수강료를 계산하여

[표1]에 입력되도록 프로시저를 작성하시오.

단, [총수강료 = 수강등록인원 * 수강료] 이고

강좌명, 강사명, 강좌요일, 수강료는 'List강좌'의 왼쪽부터 나오는 필드들임

위 문제를 보면 Cells(r,7)은 수강인원을 의미함을 쉽게 파악할 수 있을 것입니다.

수강인원의 실제 폼 개체(자재)의 이름은 'Text정원'이므로

프로시저는 Cells(r,7) = Text정원.value 로 작성해주면 됩니다.

여기서 value란 해당 값(Text정원)을 숫자로 인식하라는 명령인데 그 이유는

Cells(r,8)에서 'Text정원'을 이용하여 연산식을 수행해야 하기 때문입니다.

'Text정원'이 문자로 인식되면 연산식을 수행할 수 없습니다.

Cells(r,8)을 봅시다.

이곳은 총 수강료가 찍히는 자리로 

수강등록인원(즉, Text정원)에 수강료를 곱한 값입니다.

수강료 부분은 이미 cells() 명령으로 변환된 상태이기 때문에 

따로 value를 적을 필요는 없습니다.

이렇게 하면 전체 명령이 완성됩니다.

식이 완성되었습니다.

제대로 실행되는지 확인해보겠습니다.

위와같이 설정을 하고 등록 버튼을 눌러보았습니다.

결과가 제대로 나온 모습[빨간박스]입니다.