엑셀 파일을 이용하여 정말 다양한 작업이 가능합니다. 계산이며 표만들기며 각종 정보를 분석하거나 데이터 화 시키는 작업 등 정말 할 수 있는 일이 무궁무진 하죠. 그런데 가끔 엑셀을 이용하여 특정 정보를 별도로 텍스트 등으로 저장해야 하는 경우가 있습니다. 개발 쪽에서 어떤 데이터 리스트를 csv 형태로 요구하였는데 그러기 위하여는 굳이 정보를 재 가공 해야 하는 경우라던가 자료의 특성상 데이터를 가공하는 위험 부담이 있는 경우에는 좀 부담스럽죠.
필요한 필드만 또는 특정 필드의 값들을 특정 형태로 가공해서 저장하고 싶을 때 스크립트를 이용하여 파일 쓰기를 해 줄 수 있다면 큰 도움이 될 겁니다.
아니면 HTML 이나 CSS 처럼 엑셀에 있는 정보들을 특정한 포멧에 맞게 뽑고 싶은데 엑셀의 저장 기능에는 그런것은 없기 때문에 코딩을 할 수 있게 되면 활용 범위가 더욱 커지고 불필요한 노동을 줄일 수 있습니다.
오늘은 엑셀에서 텍스트 파일 형태로 파일을 추출하는 간단한 방법을 알아보도록 하겠습니다.
코드는 대충 아래와 같습니다.
Open FILE_NAME For Output As #1
Print #1, YOUR_STRING
Close #1
아주 간단하죠? 간단해서 제가 자주 사용하는 코드 입니다.
FILE_NAME 부분에 실제 저장되어야 할 파일 명을 경로를 포함하여 적어주시고요 저장하고 싶은 텍스트를 YOUR_STRING 부분에 넣으면 해당 파일을 기록하고 닫는 코드 입니다.
손쉽게 사용하기 위하여 함수 형태로 만들면 아래와 같이 되겠습니다. 물론 너무 짧아서 굳이 함수로 만들 필요도 없기는 하지만요.
Sub writeMyFile (byVal strPath as string, byVal currentText as string)
Open strPath For Output As #1
Print #1, currentText
Close #1
End Sub
'사용할때 '
sub test()
dim myString as string
myString = "hello" & vbNewLine
myString = myString & "DIY" & vbNewLine
myString = myString & "DEV" & vbNewLine
myString = myString & "DESIGN" & vbNewLine
writeMyFile "d:\test.ini", myString
end sub
아래 test 라는 함수에서 실제 텍스트를 써주는 부분을 호출하고 아래와 같은 파일이 만들어 지게 됩니다.
보시는 것처럼 vbNewLine 과 같은 줄바꿈 기호 등을 줄의 맨뒤에 추가하여 여러줄을 동시에 기록하는 것도 가능합니다.
제가 만든 test 부분에서 기록하고 싶은 여러 내용을 포멧에 맞게 변수에 저장을 한 뒤 마지막에 파일을 써주는 함수에 보내면 원하는 형식으로 텍스트 파일이 만들어 지는 것이죠.
자 손쉽게 엑셀의 자료를 텍스트 파일로 기록해 주는 함수를 만들어 보았습니다. 스트링 형식으로 저장된 내용을 그냥 기록만 해주면 되기 때문에 해당 기능을 이용하여 자바스크립트, CSS, HTML 등의 형식으로 손쉽게 저장하 실 수 있습니다.
참고로 저는 이미지를 붙여 넣는 기능과 함께 해당 이미지를 웹 브라우저를 통해 미리 볼 수 있는 html 을 뽑는 스크립트를 자주 사용합니다. 웹 브라우저에서 이미지를 보게 되면 파일이 많아도 화면 이동 속도가 거의 느려지지 않고 이미지와 함께 전달하게 되면 사용자가 간단하게 이미지 리스트를 확인 할 수 있기 때문입니다. 또한 이미지 수정 시 별도로 엑셀등을 수정하지 않고 저장된 이미지만 교체하면 바로 수정된 결과를 볼수 있는 장점도 있기 때문입니다.
사용하시는 분마다 용도는 다르겠지만 텍스트를 추출하는 기능만으로도 아주 커다란 효과를 낼 수 있을지 모릅니다.
어떤 엑셀 페이지에 늘 사용하는 스크립트를 작성하였으나 매번 실행하기 위하여 vba 창을 눌러 F5를 누르는 것은 사실 본인이 직접 스크립트 코드를 개발하는 입장이라면 조금도 번거로울 것이 없는 것이지만 누군가에게 스크립트를 작성하여 엑셀 파일을 건내 주는 경우라면, 실제 사용하는 사람이 개발과 거리가 있는 사람이라면 더더욱 이와 같은 방법으로 스크립트를 실행시키는 것은 불편한 방법이 됩니다.
게다가 스크립트가 딱 하나가 아니고 어떤 스크립트는 정렬을 해주고, 어떤 스크립트는 불필요한 값을 숨겨주고, 또 어떤 스크립트는 데이터를 새로 읽어 들여 전체 시트를 리프레쉬 하는 등의 여러개의 기능을 개발한 상태라면 vba 창에서 실제 해당 스크립트 위치에 마우스 커서를 가져다 놓는 등의 추가 행위가 필요합니다.
이럴때 버튼을 이용하여 특정 스크립트 (엑셀에서는 매크로라고 합니다) 를 연결하게 되면 지정된 버튼을 누를때 마다 필요한 스크립트가 동작하게 됩니다.
말하자면 간단한 툴이 만들어 지는 것이죠.
사실 엑셀의 각 셀은 함수나 계산식을 포함할 수 있으므로 왠만한 복잡한 것들은 워크시트의 함수와 계산식으로 처리가 가능하지만 이미지를 불러온다거나 어떤 데이터의 속성을 알아내거나 약간은 복잡한 관계의 셀 값들을 이용하여 결과값을 얻는다든가 하는 등의 일은 워크시트 함수로는 조금 어려운 부분이 있습니다. 그래서 매크로 스크립트를 작성하는 것이죠. 이런 기능기능이 모여 마치 어떤 툴처럼 제작이 가능합니다.
영업사원들에게는 견적서 프로그램을 만드는 것이 가능할 것이고 세무, 회계부서에서는 각종 세금 관계 계산을 위한 처리가 가능할것입니다.
오늘은 그렇게 만들어진 스크립트 들을 버튼을 이용하여 동작하도록 하는 방법을 소개해 드릴까 합니다.
먼저 제가 초등학교 저학년 학생들을 위한 간단한 덧셈 연산을 자동으로 생성하는 엑셀파일을 만든적이 있습니다.
해당 파일을 보면 아래와 같은 화면을 볼 수 있는데요. 우측의 버튼을 누를 때 마다 버튼의 텍스트에 해당하는 기능으로 좌측 값이 자동으로 생성이 됩니다. 위 링크된 페이지에 가면 실제 엑셀 파일을 받으실 수 있으니 해당 파일을 받으셔서 참고해보셔도 좋을 것 같습니다.
버튼을 이용하여 다양한 매크로를 동작하도록 만든 엑셀 시트
우선 단추(버튼)르르 이용한 동작을 확인할 간단한 스크립트를 작성해 보겠습니다.
첫번째 스크립트로 셀 A1~C1 번에 숫자 1, 2, 3 을 넣어 보겠습니다.
만약 이미 숫자가 들어있다면 현재 들어있는 숫자에 각각 1, 2, 3을 더하도록 해보죠.
두번째 스크립트는 A2 ~ C2 에 A1 + A1, B1 + B1, C1 + C1 까지의 값을 넣는 동작을
세번째 스크립트는 A3 ~ C3 에 A1 * A2, B1 * B2, C1 * C2 의 값을 넣는 동작을
네번째 스크립트는 A4 ~ C4 에 C1 ~ C3 의 값을 이용하여 셀의 색상을 채워 넣어 보겠습니다.
컬러를 지정하기 위한 숫자는 RGB 로 각각 0~255만 가능하므로 255 보다 크면 255로 설정 하겠습니다.
그리고 255보다 값이 커진다면 첫번째 행의 값을 낮은 값으로 바꾸어 보겠습니다.
스크립트를 작성해 보겠습니다.
먼저 엑셀을 열고 VBA 창 (alt + F11) 을 실행한 뒤 아래와 같이 입력해 보겠습니다.
Sub function_a()
'cells(rowIndex, columnIndex) 와 같은 형식으로 셀을 지정하는 방법'
If Len(Cells(1, 1)) Then
Cells(1, 1).Value = Cells(1, 1).Value + 1
Else
Cells(1, 1).Value = 1
End If
If Len(Cells(1, 2)) Then
Cells(1, 2).Value = Cells(1, 2).Value + 2
Else
Cells(1, 2).Value = 2
End If
If Len(Cells(1, 3)) Then
Cells(1, 3).Value = Cells(1, 3).Value + 3
Else
Cells(1, 3).Value = 3
End If
End Sub
Sub function_b()
' Range(name) 의 형태로 셀을 지정하는 방법'
Range("A2").Value = Range("A1").Value * 2
Range("B2").Value = Range("B1").Value * 2
Range("C2").Value = Range("C1").Value * 2
End Sub
Sub function_c()
' range 라는 변수를 설정하여 정의하는 방법'
Dim a3, b3, c3
Set a3 = Range("A3")
Set b3 = Range("B3")
Set c3 = Range("C3")
a3.Value = a3.Offset(-2, 0).Value * a3.Offset(-1, 0).Value
b3.Value = b3.Offset(-2, 0).Value * b3.Offset(-1, 0).Value
c3.Value = c3.Offset(-2, 0).Value * c3.Offset(-1, 0).Value
End Sub
Sub function_d()
Dim a3, b3, c3, rngAll
Set a3 = Range("A3")
Set b3 = Range("B3")
Set c3 = Range("C3")
Set rngAll = Range("A4", "C4")
If a3.Value > 255 Then
a3.Value = 255
a3.Offset(-2, 0).Value = 1
End If
If b3.Value > 255 Then
b3.Value = 255
b3.Offset(-2, 0).Value = 2
End If
If c3.Value > 255 Then
c3.Value = 255
c3.Offset(-2, 0).Value = 3
End If
rngAll.Interior.Color = RGB(a3.Value, b3.Value, c3.Value)
End Sub
Sub ~ End Sub 까지가 하나의 매크로 스크립트가 되겠습니다. 총 4개의 스크립트가 작성이 되었는데요. 내용은 위에서 기술한 내용데로 작성하였으며 각각의 스크립트에는 셀을 지정하는 방법을 달리하여 작성해 보았습니다. 여러분들도 직접 타이핑 해보시면서 어떤방법으로 셀을 지정하는 것이 편리한지 경험해보시는 것이 도움이 되실 것 같습니다.
끝으로 rngAll 이라는 Range 개체를 만들었는요 현재 시트의 4번째 행의 A~C 열을 한꺼번에 설정해주었습니다. 그리고 한꺼번에 해당 셀의 색상을 변경을 하는 코드를 넣어 봤습니다.
자 스크립트를 모두 작성하였으면 이제 단추를 만들어 보아야 겠죠?
그런데 엑셀의 기본 기능 중에는 단추가 보이지 않습니다. 리본 메뉴가 만들어 지면서 사용하지 않는 기능들은 꺼내져 있지 않기 때문인데요. 사용하기 위하여 꺼내 보겠습니다. 처음 한번만 하시면 됩니다.
파일 메뉴로 가셔서 아래와 같은 창이 나오면 제일 아래 옵션을 선택합니다.
엑셀에서 파일 메뉴를 진입한 화면
그럼 옵션창이 팝업으로 뜨게 됩니다.
팝업으로 뜬 창에서 '리본 사용자 지정' 텝으로 이동해보면 아래와 같이 아주 많은 기능이 나열된 것을 볼 수 있습니다.
위 그림에서 좌측은 엑셀이 가지고 있는 모든 기능을 보여주고 있고 우측은 현재 사용자의 리본메뉴에 추가되어 있는 기능들이라고 생각하시면 되겠습니다.
아래 1번의 드롭다운 메뉴에 리본에 없는 메뉴를 선택하신 뒤 리스트에서 2번 '단추(양식컨트롤)' 이 바로 우리가 필요한 단추를 넣는 기능입니다. 단추라고 하면 좀 이상한데요, 익숙한 용어로는 버튼입니다. 버튼.
우측은 해당 기능을 넣을 리본 메뉴라고 했는데요, 저는 삽입 이라는 탭에 넣을 계획 입니다. 이미 있는 그룹에 넣는 것은 불가능하고 새로운 그룹을 만들어서 넣어야 하기 때문에 3번 삽입 을 선택하여 메뉴를 확장 시킨 뒤 아래 '새 그룹' 이라는 버튼을 이용하여 새로운 그룹을 추가해 줍니다.
엑셀의 옵션창 버전마다 약간의 차이는 있다
새로 추가한 그룹의 이름을 바꾸어 볼 차례 입니다. 저는 제 블로그 이름인 "DIY_DEV_DESIGN" 라고 변경하였습니다. 아래 이름 바꾸기라는 버튼을 이용하여 변경하시면 됩니다.
추가한 그룹의 이름을 바꾸어준 상태
이제 단추를 선택한뒤 가운데에 있는 "추가>>" 를 이용하여 방금 만든 그룹에 추가를 해 보겠습니다.
새로 추가한 그룹에 단추 명령을 넣은 상태
자 이제 단추를 추가하는 기능은 완료가 되었습니다. 확인 버튼을 눌러 옵션 창을 닫고 엑셀의 리본메뉴를 살펴보면 아래와 같이 단추가 추가되어 있을 것입니다.
이제 해당 버튼을 누르게 되면 엑셀 시트에 단추를 만들수 있게 됩니다.
만드는 방법은 아래와 같습니다.
단추 버튼을 누른다
원하는 위치에 마우스를 클릭한뒤 드래그 하여 필요한 크기가 되면 마우스 버튼을 뗀다
팝업으로 나오는 매크로 선택창에서 아까 만들어둔 스크립트를 선택해 준다.
이미 만들어진 단추의 크기나 위치, 레이블을 수정하기 위하여는 마우스 우클릭으로 선택해주면 됩니다.
첫번째 단추를 추가하였다.
총 4개의 스크립트를 연결할 것이므로 4개의 스크립트를 차례로 연결해 보겠습니다
이제 위의 버튼부터 차례로 눌러 볼까요?
버튼을 누를때마다 해당 스크립트가 동작한다
네 잘 동작되네요.
만약 스크립트를 수정해야 하면 해당 버튼을 마우스 우클릭한 뒤 "매크로 지정" 기능으로 이동하여 설정되어 있는 매크로를 다른 메뉴로 변경하거나 선택되어 있는 매크로에서 "편집" 버튼을 눌러주게 되면 해당 매크로 스크립트를 바로 편집할 수 있는 비주얼 베이직에디터가 나타나게 됩니다.
자 이렇게 해서 엑셀에 버튼을 추가하고 스크립트를 연결하는 방법을 알아 보았습니다.
설정창에서 단추 기능을 꺼내는 단계부터 처음하시는 분은 조금 복잡하다고 생각하실 수도 있겠는데요. 막상 해보시면 정말 간단한 작업이고 어렵지 않습니다.
Sub makeMath_under10()
Dim aSht As Worksheet
Dim rngA As Range
Dim numA As Integer
Dim numB As Integer
Set aSht = ActiveSheet
Set rngA = [B3]
Randomize
For i = 0 To 8
numA = Int(Rnd * 9) + 1
Do
numB = Int(Rnd * 9) + 1
Loop While (numA + numB > 10)
rngA.Offset(i * 2, 0).Value = numA
rngA.Offset(i * 2, 1).Value = "+"
rngA.Offset(i * 2, 2).Value = numB
rngA.Offset(i * 2, 3).Value = "="
Next i
For i = 0 To 8
numA = Int(Rnd * 9) + 1
Do
numB = Int(Rnd * 9) + 1
Loop While (numA + numB > 10)
rngA.Offset(i * 2, 6).Value = numA
rngA.Offset(i * 2, 7).Value = "+"
rngA.Offset(i * 2, 8).Value = numB
rngA.Offset(i * 2, 9).Value = "="
Next i
End Sub
Sub makeMath_over10()
Dim aSht As Worksheet
Dim rngA As Range
Dim numA As Integer
Dim numB As Integer
Set aSht = ActiveSheet
Set rngA = [B3]
Randomize
aSht.Cells(1, 12).Value = Date
For i = 0 To 8
numA = Int(Rnd * 9) + 1
Do
numB = Int(Rnd * 9) + 1
Loop While (numA + numB < 10)
rngA.Offset(i * 2, 0).Value = numA
rngA.Offset(i * 2, 1).Value = "+"
rngA.Offset(i * 2, 2).Value = numB
rngA.Offset(i * 2, 3).Value = "="
Next i
For i = 0 To 8
numA = Int(Rnd * 9) + 1
Do
numB = Int(Rnd * 9) + 1
Loop While (numA + numB < 10)
rngA.Offset(i * 2, 6).Value = numA
rngA.Offset(i * 2, 7).Value = "+"
rngA.Offset(i * 2, 8).Value = numB
rngA.Offset(i * 2, 9).Value = "="
Next i
End Sub
Sub makeMath_under100_easy()
Dim aSht As Worksheet
Dim rngA As Range
Dim numA As Integer
Dim numB As Integer
Set aSht = ActiveSheet
Set rngA = [B3]
Randomize
aSht.Cells(1, 12).Value = Date
For i = 0 To 8
numA = Int(Rnd * 20) + 1
Do
numB = Int(Rnd * 10) + 1
Loop While (numA + numB > 100)
rngA.Offset(i * 2, 0).Value = numA
rngA.Offset(i * 2, 1).Value = "+"
rngA.Offset(i * 2, 2).Value = numB
rngA.Offset(i * 2, 3).Value = "="
Next i
For i = 0 To 8
numA = Int(Rnd * 20) + 1
Do
numB = Int(Rnd * 10) + 1
Loop While (numA + numB > 100)
rngA.Offset(i * 2, 6).Value = numA
rngA.Offset(i * 2, 7).Value = "+"
rngA.Offset(i * 2, 8).Value = numB
rngA.Offset(i * 2, 9).Value = "="
Next i
End Sub
Sub makeMath_under100_normal()
Dim aSht As Worksheet
Dim rngA As Range
Dim numA As Integer
Dim numB As Integer
Set aSht = ActiveSheet
Set rngA = [B3]
Randomize
aSht.Cells(1, 12).Value = Date
For i = 0 To 8
numA = Int(Rnd * 90) + 1
Do
numB = Int(Rnd * 10) + 1
Loop While (numA + numB > 100)
rngA.Offset(i * 2, 0).Value = numA
rngA.Offset(i * 2, 1).Value = "+"
rngA.Offset(i * 2, 2).Value = numB
rngA.Offset(i * 2, 3).Value = "="
Next i
For i = 0 To 8
numA = Int(Rnd * 90) + 1
Do
numB = Int(Rnd * 10) + 1
Loop While (numA + numB > 100)
rngA.Offset(i * 2, 6).Value = numA
rngA.Offset(i * 2, 7).Value = "+"
rngA.Offset(i * 2, 8).Value = numB
rngA.Offset(i * 2, 9).Value = "="
Next i
End Sub
Sub makeMath_under100_hard()
Dim aSht As Worksheet
Dim rngA As Range
Dim numA As Integer
Dim numB As Integer
Set aSht = ActiveSheet
Set rngA = [B3]
Randomize
aSht.Cells(1, 12).Value = Date
For i = 0 To 8
numA = Int(Rnd * 90) + 1
Do
numB = Int(Rnd * 90) + 1
Loop While (numA + numB > 100)
rngA.Offset(i * 2, 0).Value = numA
rngA.Offset(i * 2, 1).Value = "+"
rngA.Offset(i * 2, 2).Value = numB
rngA.Offset(i * 2, 3).Value = "="
Next i
For i = 0 To 8
numA = Int(Rnd * 90) + 1
Do
numB = Int(Rnd * 90) + 1
Loop While (numA + numB > 100)
rngA.Offset(i * 2, 6).Value = numA
rngA.Offset(i * 2, 7).Value = "+"
rngA.Offset(i * 2, 8).Value = numB
rngA.Offset(i * 2, 9).Value = "="
Next i
End Sub
요렇게 코드가 모두 작성이 되셨다면
단추마다 매크로를 연결해 줍니다.
단추에서 마우스 우클릭 후 매크로 지정자신이 만든 매크로함수 중 연결할 대상을 선택해 준다.
이런식으로 함수를 연결해 주면 됩니다.
어렵지 않죠?
뎃글, 공감 은 블로그 작성자에게 큰 힘이 된답니다. 도움이 되었다 생각되시면클릭!! 부탁드려요~
안녕하세요. 이번 강좌에서는 VBA 를 이용하여 초등학교 저학년 아이를 위한 연산문제를 자동으로 내주는 스크립트를 작성해 보겠습니다. VBA 를 좀 해봤다 하면서 막상 일상 생활에 사용하려면 사용할데가 마땅히 떠오르지 않죠? 초등학교 저학년 자녀가 있으신 분은 수학 연산 학습지를 한번 살펴보세요. 별거 없습니다. 간단한 문제 들이 그저 나열되어 있을 뿐이죠.
이제 우리는 VBA 를 조금 다룰 줄 알게 되었기 때문에 VBA 를 이용하여 사랑하는 아이를 위한 연산 문제를 만들어주는 스크립트를 작성해 보려고 합니다.
현재 활성화된 시트와 특정 영역을 설정하는 방법은 앞선 포스트에서 다루었으니 해당 포스트를 참고하시면 되고요.
만약 두개의 숫자의 합이 10이 넘는다면 10이 넘지 않는 두번째 숫자를 구한다. (쉬운버전)
첫번째 숫자 뒤에는 "+" 기호를 넣고 두번재 숫자 뒤에는 "=" 기호를 넣는다.
자 먼저 함수를 정의하고 입력할 시트와 칸을 지정, 그리고 숫자 두개를 지정하겠습니다. 감이 오시나요?
Sub makeMath_under10()
Dim aSht As Worksheet
Dim rngA As Range
Dim numA As Integer
Dim numB As Integer
Set aSht = ActiveSheet
Set rngA = [B3]
End sub
요렇게요.
아직 아무런 동작도 실행되지는 않지만 필요한 변수는 다 설정했고 입력할 시트와 셀도 지정을 했습니다.
자 이제 한번에 갑니다. 조금 복잡할수도 있지만 잘 보세요~ ( '로 시작하는 문장은 주석입니다. )
Sub makeMath_under10()
Dim aSht As Worksheet
Dim rngA As Range
Dim numA As Integer
Dim numB As Integer
Set aSht = ActiveSheet
Set rngA = [B3]
' 이 함수에서 난수를 사용할 것임을 선언함'
Randomize
'Rnd 라는 함수에서 난수 0.00001 ~ 0.99999 사이의 숫자를 생성'
'만들어진 9 이하의 숫자를 정수로 변경'
'최종 만들어진 숫자에 1 을 더함 : 0이 나올수 있기 때문에'
'만들어진 숫자를 numA 에 적용함'
numA = Int(Rnd * 9) + 1
'Do 와 Loop 사이의 수식을 While 뒤에 나오는 조건이 만족하는 동안 계속 반복함'
Do
numB = Int(Rnd * 9) + 1
Loop While (numA + numB > 10)
'rngA 를 기준으로 우측으로 각 칸마다 필요한 값을 입력'
rngA.Offset(0, 0).Value = numA
rngA.Offset(0, 1).Value = "+"
rngA.Offset(0, 2).Value = numB
rngA.Offset(0, 3).Value = "="
End sub
주석으로 설명을 했습니다만 어려울 수 있는 부분만 추가 설명을 하겠습니다.
Int( 숫자 ) 라는 함수는 '숫자' 에 해당하는 어떤 수 도 정수형으로 변경해 주는 함수 입니다.
Rnd 를 통해 만들어진 소수형 숫자에 9를 곱해 최대 9가 나올수 있도록 범위를 확장해 준 뒤 Int 라는 함수를 이용하여 정수로 변경해주는 문장입니다. 만약 Rnd 에서 0.00001 이런 값이 나오는 경우 1 미만의 값이 나올 수 있기 때문에 제일 마지막에 + 1 을 해주어 절대로 0은 나오지 않도록 작성하였습니다.
그 뒤에 나오는 Do ... Loop While 부분이 조금 어려우실 수도 있겠는데요. 조금 생각해보면 그다지 어렵지는 않습니다.
Do 와 Loop 사이에 어떤 문장을 계속 반복하라는 뜻인데요. 이 반복은 어떤 조건이 만족할때만 반복시키게 됩니다. 여기에서는 (numA + numB > 10) 이라는 조건을 걸었죠. 말하자면 Do Loop 사이에서 구해진 numB 라는 값과 그 전에 구해진 numA 라는 값을 더했을 때 10이 넘는다면 numB 를 다시 구하도록 하는 역할을 합니다. 만약 두 숫자를 더했을 때 10이 넘지 않는 다면 반복문은 종료 되고 그 다음 줄로 빠져나오게 됩니다. 이렇게 하면 numA 와 numB를 더했을 때 절대로 10이 넘지 않게 됩니다. 저희 둘째가 아직 어려서 10 넘는 더하기는 무리이기 때문에 10이 넘지 않도록 하였지만 만약 10이 넘는 덧셈이 필요하시면 해당 부분을 제거하시면 됩니다.
조금 응용을 해보시면 반드시 10이 넘는 계산만 만드시는 것도 가능하겠죠?
위의 스크립트를 실행하면 아래와 같이 작성이 됩니다.
전 한 페이지에 문제를 좀더 내려고 합니다. For 문을 이용해서 반복적으로 문제를 계속해서 만들어 나갈 겁니다.
Sub makeMath_under10()
Dim aSht As Worksheet
Dim rngA As Range
Dim numA As Integer
Dim numB As Integer
Set aSht = ActiveSheet
Set rngA = [B3]
Randomize
For i = 0 To 8
numA = Int(Rnd * 9) + 1
Do
numB = Int(Rnd * 9) + 1
Loop While (numA + numB > 10)
rngA.Offset(i * 2, 0).Value = numA
rngA.Offset(i * 2, 1).Value = "+"
rngA.Offset(i * 2, 2).Value = numB
rngA.Offset(i * 2, 3).Value = "="
Next i
For i = 0 To 8
numA = Int(Rnd * 9) + 1
Do
numB = Int(Rnd * 9) + 1
Loop While (numA + numB > 10)
rngA.Offset(i * 2, 6).Value = numA
rngA.Offset(i * 2, 7).Value = "+"
rngA.Offset(i * 2, 8).Value = numB
rngA.Offset(i * 2, 9).Value = "="
Next i
End Sub
요렇게 작성했습니다. 이렇게 만들고 코드를 실행하면 아래와 같은 한페이지에 총 16 문제가 들어있는 문제집이 만들어 집니다.
그런데 모양이 보기에 좋지 않죠? 음...
이제 함수를 실행할때 마다 문제가 새로 만들어 질 건데요. 그 전에 화면을 좀 다듬어 봐야 겠네요.
출력할 용지도 고려하고 아이가 볼 문제니 글자 크기도 좀 키우고 해야 겠죠?
최종으로 만들어진 화면입니다.
스크립트를 실행할 때 마다 문제가 계속 바뀌니 항상 새로운 문제를 낼 수 있게 되었습니다.
물론 아이가 좋아할지는 미지수입니다.
숫자를 쓴걸 보니 그다지 재미는 없나 보네요. ( 0 이 아니고 10을 쓴거네요. 1이 아주 작게 ㅋ)
뎃글, 공감 은 블로그 작성자에게 큰 힘이 된답니다. 도움이 되었다 생각되시면클릭!! 부탁드려요~
오늘은 엑셀 시트에 있는 모든 그림을 한번에 삭제하는 스크립트를 만들어 보겠습니다. 엑셀 시트에 첨부된 그림을 일일이 또는 어떤 조건에 맞는 모든 그림을 삭제 하는 것은 매우 귀찮은 일입니다. 일단 다중 선택을 위하여 하나하나 그림 개체를 선택해 주어야 하는데 대상을 잘못 클릭하거나 그 대상이 수백개 이상일 때는 정말 난처한 상황이 발생되죠.
그래서 이번에는 엑셀 시트에 있는 모든 그림을 삭제하는 방법 또는 특정 영역에 있는 모든 그림을 삭제하는 방법을 포스팅 하려고 합니다.
스크립트 내용은 아주 짧으니 참고하시면 됩니다.
Sub delPic() '영역안의 사진 지우기
Dim cwkbook As Workbook
Dim cwkSht As Worksheet
Dim shpC As Shape
Dim rngShp As Range
Dim rngAll As Range
Dim rngStartValue As String
Dim rngEndValue As String
Set cwkbook = ActiveWorkbook
Set cwkSht = cwkbook.ActiveSheet
rngStartValue = "A1"
rngEndValue = "z9999"
Set rngAll = cwkSht.Range(rngStartValue, rngEndValue)
For Each shpC In cwkSht.Shapes
Set rngShp = shpC.TopLeftCell
If Not Intersect(rngAll, rngShp) Is Nothing Then
shpC.Delete
End If
Next shpC
Set rngAll = Nothing
Set rngShp = Nothing
End Sub
스크립트 상단에 각종 변수를 정의해 주게 되는데요. 워크북 (엑셀 파일) 과 워크시트 (시트) 를 지정해주는 부분이 있습니다. 이곳에서는 일단 현재 활성화된 파일의 활성화된 워크시트를 선택해 주었습니다. 만약 특정 엑셀 파일을 지정할 계획이고 해당 엑셀 파일에 특정 시트에 대해 작업을 해주어야 한다면 해당 부분만 아래와 같이 작성해주면 됩니다.
Set cwkbook = Excel.Workbooks("엑셀파일이름.xlsx")
Set cwkSht = cwkbook.Worksheets("해당워크시트이름")
그 아래에 rngStartValue 와 rngEndValue 를 이용하여 영역의 시작부분과 끝부분의 셀 이름을 정해주면 해당 셀안에 있는 모든 그림은 삭제가 됩니다.
만약 세로로 같은 열에 있는 그림만 삭제를 해야 한다면 시작칸과 종료칸의 열 번호를 동일하게 지정해 주면 됩니다.
뎃글, 공감 은 블로그 작성자에게 큰 힘이 된답니다. 도움이 되었다 생각되시면 클릭!! 부탁드려요~
vba를 이용하여 자동화를 하기 위하여는 어떤 범위에 있는 대상에 대하여 작업이 이루어 지는지를 설정 해주어야 합니다. 보통 range 라는 개체를 이용하여 설정을 해주게 되는데 이 range 를 현재 엑셀 시트에서 사용자가 선택한 영역으로 하고 싶은 경우가 있습니다.
선택을 필요로 하는 대상 셀이 한정 적이거나 자동화 대상이 필요에 따라 자주 변경되는 경우에는 사용자가 선택한 영역을 이용하여 range 를 설정해주는 것이 편리하기도 합니다.
엑셀 시트에서 특정 영역을 선택한 다음 vba 에디터 창으로 가서 아래와 같이 입력한 후 F5 를 눌러 실행해 봅시다.
Sub selectiontest()
Dim currentSelection As Range
Dim row_Count As Integer
Dim column_count As Integer
Set currentSelection = Selection.Cells()
row_Count = currentSelection.Rows.Count
column_count = currentSelection.Columns.Count
MsgBox (row_Count) '현재선택한 셀의 세로 줄 수
MsgBox (column_count) '현재선택한 셀의 가로 줄 수
End Sub
현재 선택한 셀의 가로 줄 수와 세로 줄 수가 팝창으로 안내가 됩니다.
선택한 모든 셀에 대하여 무엇인가를 해야 한다면 For 구분을 통하여 실행이 가능합니다.
여기서 한번 응용을 해보도록 하겠습니다.
특정 셀안에 색상을 표기하는 16진수 값이 들어있을 때 해당 값을 이용하여 섹의 색상을 칠한다고 하면 아래와 같이 하면 되겠습니다.
먼저 필요한 명령들을 보면 아래와 같습니다.
선택 영역을 지정하는 함수 : Selection.Cells()
문자열의 일부 영역을 잘라내는 함수 : LEFT(string, number), MID(string,from, number), RIGHT(string, number)
16진수 값을 10진수로 변경해주는 함수 : WorksheetFunction.Hex2Dec(value)
셀의 배경색상을 지정하는 함수 .Interior.Color = RGB(255,255,255)
엑셀의 특정 영역에 16진수 색상 값을 적어 보겠습니다.
해당 셀을 영역으로 선택한 뒤 아래의 코드를 vba 에디터 창에 입력한 후 F5 를 눌러봅시다.
위에 설명했던 코드들이므로 한번 훓어 보시면 되겠습니다.
Sub selectiontest_setCellColor()
Dim currentSelection As Range
Dim row_Count As Integer
Dim column_count As Integer
Set currentSelection = Selection.Cells()
row_Count = currentSelection.Rows.Count
column_count = currentSelection.Columns.Count
For Each c In currentSelection
Dim clr_red As Integer
Dim clr_green As Integer
Dim clr_blue As Integer
clr_red = WorksheetFunction.Hex2Dec(Mid(c.Value, 2, 2))
clr_green = WorksheetFunction.Hex2Dec(Mid(c.Value, 4, 2))
clr_blue = WorksheetFunction.Hex2Dec(Right(c.Value, 2))
c.Interior.Color = RGB(clr_red, clr_green, clr_blue)
Next
End Sub
짜잔. 요렇게 색상이 칠해졌습니다.
물론 당연히 선택한 셀이 아닌 기존 방식대로 특정 영역의 모든 셀에 대하여도 동일하게 적용이 가능합니다.
코딩을 하는 이유는 많은 작업을 쉽고 빠르며 정확하게 하기 위함이므로 위와 같은 칸이 수천개가 있다면 당연히 코딩을 하는 것이 좋은 방법이 되겠습니다.
디자이너분들이라고 겁먹지 마시고 과감히 코딩을 배워봅시다.
뎃글, 공감 은 블로그 작성자에게 큰 힘이 된답니다. 도움이 되었다 생각되시면클릭!! 부탁드려요~
Sub checkError()
Dim asht As Worksheet
Dim rngA As Range
Set asht = ActiveSheet
Set rngA = asht.Range("a3")
Set rngA = asht.Range(rngA, rngA.End(xlDown))
End Sub
요렇게요. 그럼 a3 부터 a 열의 제일 마지막 행까지 설정이 됩니다.
반복문으로 무었을 할거냐면요.
a 열에 있는 단어를 모두 소문자로 바꿔서 b 열에 넣을 겁니다.
엑셀 기본 제공 함수에는 왜인지 모르겠지만 이 기능을 지원하지 않는것 같습니다. 그러면 vba 로 하면 되는거죠.
위에 작성한 함수 아래 부분에 for 구문을 이용해서 위에서 말했던 소문자로 바꾸눈 기능을 개발해보겠습니다.
Sub checkError()
Dim asht As Worksheet
Dim rngA As Range
Set asht = ActiveSheet
Set rngA = asht.Range("a3")
Set rngA = asht.Range(rngA, rngA.End(xlDown))
'반복문 추가
For Each c in rngA
c.offset(0,1).value = LCase(c.value)
next c
End Sub
요렇게 하면 됩니다.
For 뒤에 Each c 라는 부분이 있는데요. rngA 라는 Range의 각각의 셀을 c 로 하겠다는 의미 입니다. 모든 셀이 각각 턴마다 c 에 대응되고 나면 종료가 됩니다.
아래쪽에 Next c 라는 부분을 만나면 다음 셀로 넘어가게 되죠. 즉 For ~ Next 사이의 코드가 계속해서 반복됩니다. 언제까지요? 네. 모든 셀이 한번씩 지나가는 동안요.
그 아랫줄에 오늘 설명하기로 하였던 하나의 명령어가 나왔습니다.
바로 offset 입니다.
문장을 그래도 해석해 보면 c 라는 셀로 부터 아래쪽으로 0칸, 오른 쪽으로 1칸 위치에 있는 셀의 value, 즉 값에다가 c 의 값을 이용하여 LCase - 소문자로 변경하는 함수 - 로 세팅하라는 의미 입니다.
문장이 좀 이상한데요. 어쨌든, LCase 는 소문자로, 반대로 UCase 는 뒤의 괄호안의 문자를 모두대분자로 변경하라는 의미입니다. 즉 소문자로 변경한 c 의 값을 c 의 오른쪽 첫번째 칸에 입력하라는 의미가 되겠습니다.
offset 은 특정 셀에 대하여 사용할 수 있는 명령어 인데요. offset은 두개의 인자를 입력받는데 첫번째 인자는 세로 방향, 두번째 인자는 가로방향입니다. 0 이면 제자리이고요. 가로에 대하여 - 는 왼쪽, + 는 오른쪽입니다. 세로방향에 대하여 -는 위쪽, + 는 아래쪽입니다.
특정 셀에 대하여 오른쪽으로 두칸, 아래로 한칸이면 c.offset(1,2) 가 됩니다.
특정 셀에 대하여 왼쪽으로 세칸, 위로 두칸이면 c.offset(-2, -3) 이라고 작성하면 됩니다.
offset 은 아주 자주 사용되고 유용한 명령어 이므로 꼭 기억해두시길 바랍니다.
다음 강좌에서는 셀의 색상을 칠하는 방법, 셀 안의 글자 색을 변경하는 방법을 알아보겠습니다.
또한 색상의 경우 16진수 값으로 FF143F 와 같이 작성되는 경우가 많은데요. 이런식의 색상 코드를 이용하여 셀의 색상을 채우는 방법을 알아보겠습니다.
마이크로 소프트 엑셀은 수많은 기능과 자동화된 연산 처리, 편리한 템플릿 가공, 다양한 그래프 드을 이용한 데이터의 시각화 외에도 아주 많은 유용한 기능을 제공하여 사무 업무의 표준 프로그램이 되었다. 나는 디자이너이지만 후배들에게 포토샵이나 일러스트만큼이나 중요한 프로그램이라고 자신있게 말할 수 있는 것이 바로 엑셀이다. 회사에서 작성되고 확인되는 거의 모든 수치를 다루는 문서는 바로 엑셀이기 때문이다.
견적서도, WBS 같은 일정표도, 데이터 분석 결과도 모두 엑셀이며 지금은 전자문서로 대체되긴 했지만 각종 결재 서식이나 양식역시 워드보다 엑셀을 많이 사용했었다.
본 포스트를 보신 후 도움이 되었다 생각되시면 공감클릭!! 부탁드려요~
엑셀 전문가 하면 다양한 기능을 사용하는 것도 있지만 무엇보다 엑셀에서 지원하는 강력한 기능인 함수의 사용 여부로 판가름 할 수 있겠다. 엑셀에서 각종 지원 함수를 이용하면 엑셀이 더이상 문서가 아닌 하나의 프로그램이 되기 때문이다. 엑셀 지원 함수를 이용한 기능은 너무나도 다양하기 때문에 여기서 다루지는 않을 것이고 따로 찾아보는 것이 좋을 것이다. 관심이 있다면 학원을 잠시 다니는 것도 추천한다.
그럼 여기서 이야기 할 주제는 무엇인가 하니, 바로 VBA 라고 하는 프로그래밍 언어에 대하여 작성할 예정이다.
VBA 란 Visual Basic for Application 의 약어로 마이크로 소프트에서 제공하는 office 제품군에서 사용할 수 있는 visual basic 언어를 말한다.
visual basic 은 하나의 프로그래밍 언어로 문법이 다른 언어와 약간 차이가 있지만 막상 배우려고 보면 아주 쉽게 습득할 수 있는 언어이며 인터넷에 아주 많은 레퍼런스가 널려 있어 새로 개발을 할때에도 참고할 만한 자료가 아주 많은 괜찮은 개발 언어이다.
이 포스트를 보는 분이 이런 글 보자고 들어온 것은 아닐 것 이므로 바로 본론으로 들어가자.
엑셀( 다른 office 제품군 포함)에서 VBA 를 작성하기 위하여 먼저 개발창을 열어야 한다.
alt + F11키를 누르면 아래와 같은 창이 나타난다.
F11 키를 눌러 VBA 에디터 창이 표시된 화면 - 현재 좌측 tree 의 sheet1을 더블클릭한 상태
좌측 상단에는 현재 엑셀에 열려있는 문서와 각 문서가 포함하고 있는 워크시트 (엑셀 창 하단에 보이는 탭) 이름이 보여지는 창이 하나 있고 아래는 복잡한 테이블... 별로 알필요 없음. 우측은 회색 공간이 있다. 회색 공간에 창이 하나 떠 있는데 이는 필자가 좌측 트리뷰에서 Sheet1(Sheet1) 로 표시된 부분을 더블클릭한 상태이다.
이 하얀색 텍스트 에디터에 VBA 를 작성하여 각종 자동화 작업을 할 수 있다.
VBA 에서는 어떤 목적을 위하여 수행하는 기능을 묶어서 실행을 시키게 되는데 바로 아래와 같은 형태로 정의한다.
Sub setText()
수행할 명령어
End Sub
여기서 setText 는 사용자가 원하는 기능의 명칭을 적으면 되며 실제 수행하는 기능은 "수행할 명령어" 부분에 입력하면 된다.
Sub setText()
Cells(1, 1).Value = "DIY"
Cells(2, 1).Value = "Dev."
Cells(3, 1).Value = "Design"
End Sub
요렇게 입력하고 F5 키를 누르면 엑셀 창의 Sheet1 에 아래와 같이 자동으로 작성이 된다.
위의 코드가 의미하는 것을 살펴보면
Cells 는 엑셀 화면의 칸들을 의미한다. Cells( rowIndex, columnIndex) 의 순서로 셀을 지정하게 되는데 rowIndex 는 행번호 즉 좌측에 세로로 보이는 숫자를 말하고 columnIndex 는 열을 의미하며 위에 보이는 알파벳에 해당되는 번호다. 물론 VBA 에서는 알파벳으로 기재를 하지 않고 숫자로 기재를 해야 한다. VBA 에서 가로 세로 열을 앞에 말한바와 같이 지칭할때는 항상 앞자리가 행, 뒷자리가 열 임을 기억하자.
참고로 코드 실행을 위하여 F5 를 누를 때 키보드의 커서가 Sub 와 End Sub 사이에 이어야 해당 기능이 실행이 된다.
엑셀 VBA 를 공부하면서 알아가야 할 것이 많겠지만 우선 다음과 같은 것들을 고민해보아야 한다.
무엇을 위해 VBA 를 사용할 것인가?
VBA 를 배우면 내가 하는 실무 (또는 생활) 에 당장 도움이 되는 것은 어떤 것일까?
엑셀을 이용하면서 가장 시간 낭비라고 생각했던 것은 무엇이었는가?
막연히 공부해야 겠다는 생각으로 시작한 스크립팅 공부는 생각보다 진도가 빠르게 나가지 않는다. 당장 1분이라도 내 시간을 아껴 줄 수 있는 부분을 빨리 찾아 직접 만들어 보는 것이 가장 빠른 학습방법이 된다. 아래 몇가지 예를 들어 볼테니 본인에게 해당되는 사항이 있다면 엑셀을 열고 Alt + F11 을 눌러 바로 코드를 작성해 보자.
위에 예시를 든 케이스 외에도 VBA 로 할 수 있는 작업은 무궁무진하다. 하지만 어려운 것부터 시작한다면 첫걸음을 떼기 어려우니 간단한 작업부터 도전해 보자.
VBA 관련 첫번째 포스트는 여기서 마무리를 할텐데 이 주제에서 다룰 내용은 아래와 같다.
1. 각종 VBA 명령어와 사용법
2. 알아두면 유용한 코드들
3. 완성된 코드 샘플
필자는 디자이너지만 프로그래밍을 접하면서 업무의 질이 많이 달라졌다. 개발직군이 아니라고 개발을 등한시 하지 말고 적극적인 자세로 접하도록 해보자. 어느 순간 개발자가 와서 이것 좀 해줄 수 있을까 하고 개발 의뢰를 할 것이다. 그럴때가 우리에게 채무자가 아닌 채권자의 입장이 될 수 있는 절호의 찬스일 것이다.
세상에 VBA 만큼 쉬운 프로그래밍이 없으니 걱정말고 시작하자. 개발자 아닌 디자이너, 기획자, 일반 사무직 분들.
내가 작성하는 내용만 다 따라와도 엑셀 붙잡고 머리 터지게 숫자 계산하는 업무시간이 1/10 으로 줄어들 것이다.
궁금한 내용이나 질문은 뎃글을 남겨주면 답변해 드리겠습니다.
뎃글, 공감 은 블로그 작성자에게 큰 힘이 된답니다. 도움이 되었다 생각되시면 클릭!! 부탁드려요~
