엑셀과 VBA

시트에 기록될 필드명을 작성하였으면 이제부터 유저폼을 만들어  폼에서 데이터를 입력하는  단계입니다.

1. ALT+F11키를 누르면 VBA편집기창이 열립니다.

오랜만에 글을 올립니다.
오래전에 시트에 입력하는 VBA를 올렸는데 많은 도움이 되었나 모르겠내요.
이번에는  가장 많이 사용하고 다양한 기능이 있는 유저폼에 대해 공부(?) 하도록 하겠습니다.
유저폼이란 사용자가 원하는 형식을 구상하여 입력시스템을 사용자의 취향에 맞게 또는 업무형식에 맞게 제작하여 사용하는 사용자 폼을 말합니다. 업무용 프로그램을 보면 입력상자가 나오고 그 입력상자에 어떤 데이터를 넣고, 값을 넣고 버튼을 누르면 계산하고 정렬하고. 기록하고 하는 걸 많이 보셨을 겁니다. 엑셀도 이에 못지않게 다양한 기능을 활용하여 만들수 있습니다. 그렇지만 각 프로그램마다 특성이 있는 것이므로 엑셀의 유저폼으로 만든다고 해서 같은 형식은 만들 수 없습니다. 대신 어느정도 근접한 프로그램은 제작이 가능하므로 잘 만 만들면 다른 프로그램 몾지않는 훌륭한 프료그램이 될 수도 있습니다.

********************************
이것도 초보자분들이 가장 많은 질문을 올렸던 것이라 이번에도 초급자을 위한 유저폼과
VBA에 대해 소개 해 드리겠습니다.
이 유저폼을 만들어 데이터를 입력하고 시트에 기록하는 것도 거의 전에 소개했던 시트입력방식과 거의 같은 형식입니다. 다만 입력하는 형식이 시트입력이냐, 유저폼입력이냐 의 차이일 뿐 거의 같은 형식이므로 코드도 비슷하므로 그리 어려운 건 없을 겁니다.
전에 <현재시트에 입력하기>에 대해 잘 숙지를 하셧으면 코드작성은 아주 쉬울 겁니다.
그럼 본론으로 들어가서.
시트에 입력되는 형식은 예전에 올렸던 <현재시트에 기록하기> 예제로 하겠습니다.
아래 그림은 시트1에 기록할 양식입니다.
예전에는 시트에 입력상자를 만들어 노란색 부분에 값을 넣으면 9행에 입력값이 기록되는 방벙이엇는데 이번에는  입력셀 대신 유저폼으로 입력하는 방식입니다.
위 그림은 입력상자의 범위때문에 8행에 필드명을 작성했지만 유저폼으로 하면 1행부터 7행까지의 공간이 필요없으므로 아래 그림처럼 2행에 필드명을 작성합니다.


1. 시트1에 위와깉이 필드명을 만들고 적당한 간격을 조정하여 기록될 서식을 만듭니다.
2. NO, 서울,부산...울산 까지 모두8개의 필드명을 입력합니다.

이와같이 모두 작성하였으면 이제 유저폼을 만드는 과정을 말씀 드리겠습니다.
유저폼 만드는건 다음 시간에....

지난번에 이어 이번에는 시트1에서 값을 입력하고 이 입력된 값을 시트2에 저장하는 방법에 대해 말씀 드리겠습니다. 이거 역시 초보자님들의 잦은 질문 이어서 간략히 그 방법에 대해 적어 보겠습니다.

전번의 예제를 보면 시트1에서 값을 입력하고 메크로를 실행하면 현재시트(시트1)에 데이터가 기록되는 방법이었습니다.
이제부터는 이 기록을 시트2에 해 보도록 하겠습니다.

위 그림은 시트2에 저장할 화면입니다. 2행에 레코드명을 작성하고 3행부터 차곡차곡 기록하는 코드 입니다. 코드 형식은 지난번과 거의 같은 형식입니다.

Sub 다른시트에저장()

With Sheets("Sheet2")
  K = .Range("A65536").End(xlUp).Row + 1
  .Cells(K, 1) = K - 2
  .Cells(K, 2) = Range("C2")
  .Cells(K, 3) = Range("C3")
  .Cells(K, 4) = Range("C4")
  .Cells(K, 5) = Range("C5")
End With
End Sub

위 구문을 보면 지난번 코드와 조금 다른 것이 보일겁니다.
With Sheets("Sheet2")
이게 한줄이 추가되어있죠? 지난번에 코드작성엔 없었는데....
그럼 이게 무슨역할을 하는걸까요?
With는 ~와 함께,~에, 라는 뜻으로서 '시트2에'라는 구문입니다.
즉 시트2에 어떤 지정한 셀에 시트1의 셀값을 기록하라는 의미입니다/

이 구문은 원래 아래와 같이 작성할 수도 있습니다.

K = Sheets("Sheet2").Range("A65536").End(xlUp).Row + 1

  Sheets("Sheet2").Cells(K, 1) = K - 2
  Sheets("Sheet2").Cells(K, 2) = Range("C2")
  Sheets("Sheet2").Cells(K, 3) = Range("C3")
  Sheets("Sheet2").Cells(K, 4) = Range("C4")
  Sheets("Sheet2").Cells(K, 5) = Range("C5")

위 코드와 아래코드의 차이점은 하나도 다를게 없이 시트1의 C2부터 C5의 값이 시트2에 차곡차곡 기록됩니다.
그렇지만 두번쩨 코드는 같은 구문이 반복적으로 6개의 시트명이 붙어있죠. 지금은 간단히 몇줄만 작성되어있지만 만약 수십개정도의 입력값이 있다면 시트명도 수십개를 일일이 붙여주어야 하겠죠. 그럼 보통 불편한 일이 아닐겁니다. 물론 시트명만 복사하여 앞부분에 붙여주면 되지않냐 하지만 그것도 일일이 하나씩 줄을 바꾸어 가면 붙여주어야 합니다.

이거 이러면 보통일이 아니죠, 아마 하다가 싫증나서 포기할 수도 있을것이고 또 겨우겨우 어떻게 완성했는데 나중에 치명적인 오류로 인해 엑셀이 다운되어버라면 미처 저장도 하지 못하고 애쓰게 작성한 코드도 첨부터 다시 작성해야 합니다.(물론 자동저장으로 해 놓았다면 어느정도는 커버가 될 수도 있겠지요)
이런식으로 코드를 작성하고나면 나중에 본인도 그 코드를 들여다 볼 때 순 영문으로 도배(?)가 되어있어 보는 보인도 했갈릴 수도 있을 것이고요.

그래서 이걸 좀 더 간편하게 작성할 수 없을까? 하는 의문이 있겠지요.  간단합니다.
위 첫번째 구문처럼 With문을 하나 추가하면 쉽게 해결할 수 있죠.
With Sheets("Sheet2") ----> 여기에 시트2가 아닌 다른 원하는 시트명을 넣으면 됩니다.
 K=.Range("A65536")~~~  를 보면 Range앞에 .이 하나 보이죠?
왜 점을 사용했을까? 그것은 이렇게 이해하시면  됩니다.
With Sheets("Sheet2")
 K=.Range("A65536:)~~ 은  Sheets("Sheet2").Range("A65536")와 같은 형식이라고 이미애기 했듯이  With문을 사용했기 때문에 다음에 나오는 구문에는 시트명을 생략한 것입니다.
즉 시트2의 A열에서 값이 있는 행까지 카운터 하여라는 뜻이죠...
다음 은  시트2의 A열 3행에는 순번이. B열 3행에는 서울값(C3)을 기록하는 코드입니다.
이것도 역시 앞에 시트명을 생략하고 아래와 같이 하심 됩니다.
.Cells(K,1)=K-2   --->여기서는 왜 2를 빼주는지 그 이유는 이제 아시겠죠?
.Cells(K,2)=Range("C3")
그런데 왜 뒤에 나오는 Range("C3")은 점이 없을까? 여기에도 점을 넣어주어야 하는게 아닌가 하는 의문이 있을 겁니다.
앞에 점이 없는것은 현재 활성화된 시트를 의미합니다.
한 예를 들어보겠습니다.(With 문을 사용하지 않고)

 Sheets("Sheet2").Cells(K, 2) = Range("C2")

은 현재시트(활성화된 시트) 는 시트1에서 실행하므로 시트명을 붙여주지 않아도 됩니다.

만약 시트2의 C2셀값을 시트3의 A1셀에 기록하는 경우는 이렇게 작성합니다.

  Sheets("Sheet3").Cells(1, 1) =Sheets("Sheet2").Range("C2")

위 그림은 시트1에서 메크로를 실행하는 것이므로, 현재 활성화된 시트이라면 앞에 시트명을 생략해도 됩니다.

이렇게   Witn문 뒤에 어떤 시트명,또는 어떤 개체(나중에 유저폼에서 많이 사용함)를 지정하면 결과값은 지정한 시트나  지정한 장소로 실행하는  것 입니다.

With문을 선언했으면 반드시 마지막에는 End With로 마감해야 합니다.

하지 않으면 구문오류라는 메세지창을 상봉하게 될 것 입니다.
아래 그림은 시트1에서 데이터를 입력하고 메크로를 실행한 후 시트2에 그 결과값을 표시한 모습입니다.

충분히 이해가 되셨나요? 아래 코드는 최종작성된 코드입니다. 이것을 복사하여 직접 실습을 해 보세요, 본인이 직접 실습을 많이 해보면서 셀값의 위치. 결과값표시위치등등을 수정해서 연습하면 자연적으로 VBA에 대한 흥미를 가지시게 될 것 입니다.


Sub 다른시트에저장()
Dim K As Integer

With Sheets("Sheet2")
  K = .Range("A65536").End(xlUp).Row + 1
  .Cells(K, 1) = K - 2
  .Cells(K, 2) = Range("C2")
  .Cells(K, 3) = Range("C3")
  .Cells(K, 4) = Range("C4")
  .Cells(K, 5) = Range("C5")
End With

  Range("C2:C5") = ""
  Range("C2").Activate
 
End Sub

 

이 내용은 이제 막 엑셀의 VBA를 배우는 초보자에게 조금이나 도움이 되길 바라면서 가장 기초적인 VBA르를 하나 소개합니다.
오피스튜터에 보면 입력된 데이터를 다른 셀,또는 다른시트에 차곡차곡 순차적으로 기록하는 방법에 대한 문의가 많이있어 혹 이 글을 보시는 초보자에게 도움이 되었으면 합니다.
아래는 다른시트가 아닌 현재 시트에 값을 차곡차곡 기록하는 예 입니다.

사트1에 위와같이 입력셀, 기록할 셀의 위치를 작성합니다.
노란셀은 입력된 값을 9행부터 순차적으로 기록될 값의 위치입니다.
예를 들면 서울의 값이 들어갈 위치는 C2셀 입니다. 여기에 어떤 값을 넣고 메크로를 실행하면  서울에 입력된 값이 B9셀에 기록됩니다. 나머지도 위와같이 지정한 셀의 위치에 있는 값을 열방향으로 서울에서 울산까지 한행에 기록하는 VBA입니다.

1. 양식도구의 명령단추를 클릭하여 단추를 하나 만듭니다.
   그러면 '메크로 지정' 대화상자가 나타납니다.
2. 대화상자에서 '새로만들기'를 선택합니다.
3. VBA편집기 창이 열리면서 아래와 같이 자동으로 프로세스명이 생성됩니다.
 Sub Button1_Click()

 End Sub
 4. 위 사이에 지금부터  앞에 설명한 것처럼 지정된 셀의 값을 행열방향으로 차곡차곡 기록하는 코드를 작성하겠습니다.
5. VBA편집기창에 보면 멘위에 아래와 같은 글이 보일겁니다.
   Option Explicit
이것은 사용자가 VBA를 작성할 때 '나는 지금부터 모든 코드를 작성할 때 변수를 사용하겠으니 엑셀은 모든 코드에 적용하도록 하라"는 의미 입니다.(물론 이것을 지정하지 않아도 상관은 없지만 나중에 중급이상 되었을 경우 이것이 가장 중요하다는 것을 느끼실 겁니다,)
처음 배우시는 분들도 항상 이것을 사용하도록 하십시오.
모듈이나 시트모듈이나 어느 모듈이든 새로 생성하면 자동으로 이 옵션이 지정됩니다.
자동으로 생성되지 않으면 다음과 같이 지정하면 됩니다.
VBA편집기창의 도구-옵션에서 '변수 선언 요구'에 체크하면 됩니다.
지금부터 코드를 작성해 보겠습니다.

Sub Button1_Click()
 Dim K As Integer
프로세스명 아래 위와같이  변수 K를 지정합니다.(변수명은 아무거나 해도 되자만 가능하면 영문으로 하세요)
Dim -->K라는 변수를 대입하겠다는 약정어 입니다.

K = Range("A65536").End(xlUp).Row + 1
==> K는 A열의 마지작행부터 위로  값이 있는 행까지 읽어서 마지막에 입력된 값이 있으면 마지막 입력된  행의 값을 카운트 합니다.
위의 예제를 보면 A행의 마지막행에 있는 값의 셀은 A8입니다. 즉 마지막으로 입렫된 행은 8행입니다.
그럼 K는 8행을 기억합니다.

Range("A65536")
-->이것는 A열을 의미합니다.
이것은 K값을 부여할 데이터가 있는 행의 갯수를 인식하도록 하는 것입니다. A열을 지정한 것은 데이터가 기록될때마다 빈셀없이 차곡차곡 기록되므로 K값을 인식하도록 하는 겁니다. 중간에 빈셀이 있는 경우라면 이 A열대신 B열,C열 등등 기록될때마다 빈셀이 없는 열을 지정해 주면 됩니다. 중간에 빈셀이 들어가는 열을 선택했다면  다음행에 추가기록이 안되므로  지정할때는  반드시 빈셀이 없이 기록되는 열을 선택해야 합니다.

.Row + 1---->마지막값이 있는 행에 하나추가하여 그다음 행에....
본문에서는 8행에 값이 있으므로 한행 추가하여 그다음행(9행)에 다음 값을 기록하라는 뜻 입니다.
&&& 코드를 작성할때 셀주소는 2가지 방법을 씁니다.&&&
Range문과 Cell문 입니다.
둘다 같은 의미인데요, 셀의 위치가 변하는 경우앤 Cell을 사용하고 고정적인 경우엔 Range문을 사용합니다. 물론 Cell대신 Range를 사용해도 되지만 이건 좀 코드작성할 때 보기도 좋지 않고 또한 코드줄이 길어지는 경우가 있습니다. 간편하고 간단한 작성앤 Range 대신 Cell 문을 사용하는게 더 편리하고 알아보기 쉬울 겁니다.
참고로 Range문을 사용할 경우 셀 주소가 A2셀이라면 Ragne("A2")f라고 표시하며,
Cell문은 Range문과 반대로 되어 있습니다.
A2셀을 Cell문으로 표시하면 Cells(2,1)라고 표시합니다, 숫자2는 2행,1은 A열을 의미합니다, Range문처럼 생각하고   Cell문을 사용할 때 이런 혼돈으로 작성하고 실행하면 엉뚱한 위치에 값이 표시되는 경우가 있습니다.  이 점만 주의하시면 되겠습니다.

다음은 C2셀에 입력된 값을 B9행에 기록하는 코드를 작성해 보겠습니다.
먼저 순번(No)을 표시해주는 코드부터 작성합니다.

Cells(K, 1) = K - 8
위 뜻은 A열 K행에 K-8값을 넣어라는 뜻 입니다. 그런데 뒤에 왜 K-8을 해 줄까요?
K 는 이미 9를 기억하고 있으므로 순번은 1이 아닌 9를 표시합니다.
서울~울산까지 값을 입력하고 메크로를 실행하면 9행에 값이 기록됩니다. 그럼 9행이 첫 데이터기록이므로 당연히 첫번째를 인식하는 1이 표시되어야 하는데 K-8이 아닌 K만 표시하면 9가 표시되는 겁니다.그래서 이미 9를 기억하고 잇는 K값에서 8을 빼주어야 1로 인식하는 겁니다. 2를 표시할때는 다음 K값은 10(한행의 데이터가 입력되었다면)을 기억하고 있으므로 10-8을 해주면 다시 2로 인식합니다. 이와같이 데이터가 기록될때마다 K값은 하나씩 증분되므로 증분된 값에서 항상 8을 빼주는 겁니다.

다음은 서울(10000)의 값을 타이틀이 있는 8행 아래 값을 기록하는 코드에 대해 설명드리겠습ㄴ다. 아래 그림은 C2의 값이 B9셀에 기록된 결과 입니다.

위 C2값을 B9헹에 기록하는 코드입니다.

Cells(K, 2) = Range("C2")
위 코드는 설명하지 않아도 잘 아시리라 믿습니다.
Cells(K, 2) 는 Range("B9")와 같은 의미입니다. 이와같이  기록될 값이 항상 변할때는 Range문대신 Cell문을 사용한다는 것 입니다.
나머지 부산, 대구,....도 모두 이와같이 열 위치와 입력될 셀주소만  바꾸어주면 됩니다.

최종적으로 작성한 코드 입니다 아래 코드를 복사하여 모듈에 붙여넣고 실습을 해 보세요.

Sub Button1_Click()
Dim K As Integer
 
K = Range("A65536").End(xlUp).Row + 1
Cells(K, 1) = K - 8
Cells(K, 2) = Range("C2")
Cells(K, 3) = Range("C3")
Cells(K, 4) = Range("C4")
Cells(K, 5) = Range("C5")
Cells(K, 6) = Range("E2")
Cells(K, 7) = Range("E3")
Cells(K, 8) = Range("E4")
End Sub

두서없이 작성하다 보니 제대로 전달이 되었느지 모르겠네요, 저도 막상 이 글을 작성하기전에 몇번이고 망설엿습니다. 먼 말인지 무슨내용인지 이해가 안가는 글을 올렸다가 괜히 망신(?)만 당하는 거 아닌가 싶어서요.
초보자가 아닌 다른 고수님들이 보시기엔 별 문제가 없겟으나 이제 막 VBA를 배우시려는 분들에겐 이해가 가지 않는 부분도 있을 겁니다. 그런 의문이 있으시면 댓글 남겨주시고 아니면 메일로 보내주셔도 좋겠네요(메일은 수시확인이 불가하므로 다소 늦는 경우도 있음)

메일: bada2420@hanmail.net

제가 아주 유용하게 쓰고있는컴퓨터자동종료프로그램 입니다.
웹에 보면 자동종료 프로그램이 많이 있지만 대부분 종료시간을 매일 원하는 시간을 지정해 주는 기능밖에 없더군요, 한번 지정한 시간에 종료를 실행하면 그 다음날 다시 종료할 시간을 지정해 주어야만이 실행이 되므로 사용할때마다 시간을 설정을 해야하는 불편이 있습니다.  그러나 이 프로그램은 매일 같은 시간(예; 오후 8시에 종료)에 한번만 지정해 주면 언제든지 사용자가 다시 변경하지 않는 이상, 매일 지정한 시간 오후 8시가 되면 어김없이 자동종료가 되어 밤에 영화를 다운받는다거나 아니면 음악을 듣다가 잠이 드시는분들에게 아주 유용할 겁니다.
저도 아주 편리하게 사용하고 있어서 혹시나 이 프로그램을 원하는 분이 계실거 같아 소개합니다. 어느 사이트에서 공개용으로 올리는 것을  퍼온 것 입니다.

아름다운 폭포이죠?
처음으로 내 블러그에 사진을 올려보니 참으로 신기(?)하네요, 늘 다른사람들의 블로그를 보면서  사잔이나 동영상을 어떻게 올린가 싶었는데 막상 저도 이렇게  올려보니 참으로 신기하기만 하네요....^^^^^