오빠두엑셀 라이브 특강 VBA 강의 7월 2일-7월 23일 매주 토요일 9시에 시작합니다.
스터디 신청한 사람만 라이브 강의 일주일 다시 듣기 가능합니다.
엑셀 파일은 제가 직접 창작으로 제공한게 아니다보니 그냥 여기다 올리기 애매해서
오빠두엑셀에서 작성한 엑셀 완성본이 있는 스터디 노트 링크 올립니다.
그김에 오빠두엑셀 홈페이지에 있는 선생님이 올리신
유익한 엑셀 내용들도 보실 수 있으세요.
https://www.oppadu.com/study-note/?mod=document&uid=39406
[2주차] VBA 스터디 2강 스터디 노트
1. 열심히 공부한 흔적이 담긴 사진을 남겨주세요! (위쪽 카메라 버튼을 클릭해서 이미지를 삽입할 수 있습니다) 2. 이번 스터디에서 새롭게 알게되거나 유익했던 내용을 3가지로 요약해주세
www.oppadu.com
1주차와 2주차 강의 듣는 것만으로 80%는 배운 것이라고 합니다.
1주차 난이도가 50이라면 2주차 강의는 70 정도라고 합니다.
[ 2강 강의 내용 ]
본격 업무 자동화의 시작!
VBA 핵심 구문 3가지
1. VBA 변수란 무엇인가요?
2. 범위, 시트 개체만 알아도 충분합니다.
3. 업무 자동화의 시작 -For, IF문
4. 미션 매크로 만들기!
미션 1: 시트 목차 생성 매크로 만들기
미션2: 찾기 및 바꾸기 매크로 만들기
보너스 미션: XLOOKUP함수 만들기
VBA 로 작성되는 대부분 코드는 이 4가지로 시작된다!
첫 번째: Dim-변수선언!
Declare In Memory
이 변수가 어디있고, 얼마인지 알면 준비가 쉬워진다!
이해를 위해 설명한 문제) 효율적인 쇼핑을 하려면, 쇼핑리스트를 작성해야합니다.
Sub 장보기()
Dim 달걀 As String
Dim 두부 AS Integer
Dim 양파 As Long
...
End Sub
무엇을 사야할지, 어디에서 사야할지, 돈을 얼마 챙겨야지 알면 준비가 쉬워 효율적이다
변수 선언이라는 건 명령작성시 무엇을 쓸건지 미리 알려주는 것이다.
엑셀에서의 숫자, 문자, True/False 쓰이는 것처럼 같다.
1. 논리값:Boolean(2byte) (True/False)
2.정수(0): Long(4byte) (-20억~20억)
3.실수(0.0) :Double(8byte) (10^300...무한대)
4.문자: String(16byte) (20억개까지 가능)
5.개체, 등등: Object, Variant(4-22byte)(기타 등등...)
※ Long과 String만이라도 알아야함.
변수 꼭 선언해야하는가?
정답은 NO!
다만 필수는 아니지만 변수 선언 안하면
가장 큰 용량을 선택하고
속도 느리고
오탈자 방지가 안된다.
두번째: Set - 변수 할당!
Set은 왜 필요할까?
1. 논리값:Boolean(2byte) (True/False)
2.정수(0): Long(4byte) (-20억~20억)
3.실수(0.0) :Double(8byte) (10^300...무한대)
4.문자: String(16byte) (20억개까지 가능)
위는 값
.................................................................................
아래는 개체 (값X)
5.개체, 등등: Object, Variant(4-22byte)(기타 등등...)
예) Range("A1")는 여러가지 속성이 들어가서 값이 아니다.
값이 있고
범위 글씨,색상
범위 행높이, 글자크기 등이 있다.
1. [ 목차 시트 ]
1-1. 변수 선언 & 할당 기초 실습
개발도구-visual basic( alt+F11) - 삽입 - 모듈 만들기.
Sub 장보기()
Dim i As Long ( i라는 변수는 숫자데이터로 만들어주는 것(선언))
Dim s As String ( s라는 변수는 문자데이터로 만들어는 것(선언))
Dim Rng As Range ( Rng라는 변수는 범위데이터로 만들어주는 것(선언))
i = 1
s = "사과"
Rng =Range("A1")
위에 내용을 쓴 후 실행을 누르면 ' 91런타임' 개체 변수 설정되지 않았다고 뜸
주의!) Rng은 개체라 앞에 set을 써야함.
sub 장보기 ()
Dim i As Long
Dim s As String
Dim Rng As range
i = 1
s = "사과"
Set Rng = Range ("A1")
End Sub
1-2 명령문 단계별 디버깅하기
F8키 (실행(▶)) 누르면 노란색 화살표 보이면서 한단계씩 실행 가능함.
(사전에 [보기] - 직접실행, 지역, 조사식 창을 보이도록 설정해야함. )
F8키를 누르며 지역창을 보면서 다음 단계로 가면 이전 단계에 있던 적용한 값이 적용되는 걸 볼 수 있다.
rng의 경우는 범위 개체가 추가 되어 오른쪽에 보이는 것 같이 많은 속성을 보여준다.
추가로 Msgbox Rng.Value (범위의 값에 대한 메시지 박스)로
End Sub에서 노란색 화살표 드래그 해서 옮겨 F8키로 실행함
Msgbox Rng.Font.Size (범위의 글꼴 크기)도 동일하게 실행함.
sub 장보기 ()
Dim i As Long
Dim s As String
Dim Rng As range
i = 1
s = "사과"
Set Rng = Range ("A1")
Msgbox Rng.Value
Msgbox Rng. Font. Size
End Sub
1-3. 디버깅하는 또 다른 방법
1. F8키로 봄
2.중지 버튼 (■)누름
3. 커서 두고 F9키누르면 중단됨
(다시 실행하려할때 다시 F8키를 눌러야함)
4. 왼쪽 회색 부분을 직접 누르면 중단점 생김
5. Stop을 중간에 적으면 중단할 수 있다.
VBA 초보자가 가장 자주 하는 실수
Set 누락!
많이 작성하면 저절로 해결됨
!) VBA 편집기에서 커서 아무데나 두고 F2키 누르면 VBA에 쓰이는 모든 개체볼 수 있다.
( F7키 누르면 다시 나갈 수 있음 )
실무 VBA에서는 파일, 시트, 범위만 알면 충분합니다!
Dim 통합문서 AS Workbook ( 어떤 통합문서를 볼지)
Dim 시트 AS Worksheet (문서안에 어떤 시트를 볼지)
Dim 범위 As Range (시트 안에서 어떤 범위를 볼지)
Dim 통합문서 AS WorkBook
① Set 통합문서=ThisWorkbook
( 현재 통합문서를 볼 거라는 )
② Set 통합문서=ActiveWorkbook
( 다른 실행 중인 활성화된 통합문서 활성화 )
③ Set 통합문서=Application.Workbooks.open(경로)
( 경로에 있는 특정파일을 실행해서 통합문서로 만들어준다는 )
이중 1번만 알아도 됨
여러문서 걸치거나 배포 명령문 쓸 때는 2번을 주로 쓰임
Dim 시트 As Worksheet
① Set 시트=Activesheet
(현재 실행중인 시트를 볼거라는)
② Set시트=통합문서.Worksheet(시트이름이나 순번)
( 통합문서 시트를 보는 데 시트이름이나 순번으로 할당할 수 있다는 )
③ Set 시트= Sheet1
( 매크로 편집기에 현재통합문서의 목록에서의 값은 실제 쓰이는 시트 고유코드 )
실무에서는 2번째만 알아도 됨
Dim 범위 As Range
① Set 범위=시트.Range("A1:A2")
( 셀주소를 직접써서 범위를 지정함 )
② Set 범위= 시트.Cells(행번호, 열번호)
( 행번호, 열번호로 범위를 만듦 )
③ Set 범위= Selection
(선택된 범위를 직접 받아옴)
세가지 모두 중요함.
어려울때는 1번만 알아도 된다.
2. 파일, 시트, 범위 사용법 실습
2-1. 통합문서 실습
Sub Test()
'1) 통합문서 '2) 시트 '3) 범위
Dim WB As Workbook
Dim WS As Worksheet
Dim Rng As range
Set WB = ThisWorkbook
MsgBox WB.Name ( 현 통합문서의 이름 메시지 창)
Msgbox WB.path ( 현 통합문서의 경로 메시지 창)
End Sub
2-2. 시트 실습
Set Ws = WB. Worksheets ( "1월 매출")
Ws. Activate
( 현재 실행중인 통합문서 중에서 시트인 1월 매출을 보고 활성화함(할당X, 선택O) )
※ 띄어쓰기 주의 !!
2-3. 범위 실습
Set Rng =WS.Range("C5")
Msgbox Rng.Value
(C5셀의 값에 대한 메시지 창 뜸)
Sub Test()
Dim WB As Workbook
Dim WS As Worksheet
Dim Rng As Range
Set WB = ThisWorkbook
MsgBox WB.Name
MsgBox WB.Path
Set WS = WB.Worksheets("1월매출")
WS.Activate
Set Rng = WS.Range("C5")
MsgBox Rng.Value
End Sub
[ 이해를 위한 명령문 예시]
Sub 테스트()
Dim s As String
Dim Rng As Range
Set Rng = Range("A1").Font.Color
s = Rng.Value
End Sub
!) Set은 문자나 숫자가 아닌 다른 값(개체)으로 만들어줄때씀 (할당할 때)
- Rng에는 Set이 붙고 S에는 안 붙는지에 대한 설명
!) 개체에 속성을 보기 위해 점을 찍는다. (한 단계씩 내려갈때는 점을 찍는다 )
세번째: For - 반복문!
자동화의 핵심! - 4가지 예제
# 예제1
For i (숫자 변수) =1 To 10 (1-10까지 반복)
MsgBox i (안내창에 숫자 출력됨)
Next i <-생략가능
# 예제2
For i=1 To 10
WS.Range("A"&i).value = i
(A1 =1 부터 A10= 10 까지 반복)
Next i <-생략가능
# 예제3
For Each WS In WB.WorkSheets
( 특정 통합문서 안에 묶음이 있을 때 하나씩 돌아가면서 보는 것)
Msgbox Ws.Name( 시트 이름을 안내창으로 출력함)
Next
# 예제4
For Each Rng In Ws. Rang("A1:A10")
Rng.value = Rng. Address
(각각의 셀의 값을 참조해서 셀의 주소 만들어줌)
Next
4. 네번째: If - 조건문
#예제1
If WS.Range("A1").value >0 Then
WS.Range("A2").Value = "양수"
Else
WS.Range("A2").Aalue = "음수"
End if
(0보다 클때 양수, 그외에는 음수로 표시)
# 예제2
If WS.Range("A1").value >0 Then
WS.Range("A2").Value = "양수"
Else if WS.Range("A1").value =0 Then
WS.Range("A2").Aalue = "-"
Else
WS.Range("A2").Aalue = "음수"
End if
(0보다 크면 양수, 그 외에서 만약에 0 이면 -로 표시하고 그외에는 음수로 표시)
# 예제3
For Each Ws In WB.WorkSheets
If Len(WS.Name)>5 Then (시트의 이름의 길이가 5개이상이라면)
MsgBox Ws.name
Exit For (5개 이상일때는 1번만 출력함)
End if
Next
# 예제4
For Each Rng In WS.Range("A1:A10")
If Rng.value >80 Then
Rng.value ="Pass"
Else
Rng.Value = "Fall"
Next
그 외에 조건이 많을 때에는
Select Case 문을 사용하면 편리합니다!
Select Case 조건
Case 조건1
....
Case Else
,,,,
End Select
미션 1 : 시트 목차 작성 매트로
1-1. For문으로 바꾸기
Sub CreatetoC()
'변수 선언
Dim WB As Workbook
Dim WS As Worksheet
Dim i As Long
Set WB(개체) = ThisWorkbook (현재 실행중인 통합문서)
Set WS = WB.Worksheets("목차") (현재 실행 중인 통합문서 중에 대상 시트가 목차)
MsgBox WB.Worksheets.Count
( 현재 실행 중인 통합문서에서 시트들의 갯수를 안내창으로 출력)
End Sub
Sub CreatetoC()
Dim WB As Workbook
Dim WS As Worksheet
Dim i As Long
Set WB = ThisWorkbook
Set WS = WB.Worksheets("목차")
MsgBox WB.Worksheets.Count
End Sub
For문으로 바꾸면
Sub CreatetoC()
Dim WB As Workbook
Dim WS As Worksheet
Dim i As Long
Set WB = ThisWorkbook
Set WS = WB.Worksheets("목차")
For i =1 To WB.Worksheets.Count (실행 버튼 누를 시 1-8까지 직접 실행 창에 나옴)
Debug.print i
Debug. Print WB.Worksheets(i).Name
(시트의 이름을 출력해 실행함)
WS.Range("C"&i).Value = WB.Worksheets(i).Name
(위에 디버그들 지우고)
(C1~C8까지의 셀에 시트의 이름 나옴)
Next
End sub
1-2. 매크로 기록으로 하이퍼링크 코드 살펴보기
매크로 녹화를 눌러 매크로 2를 만들고 아무셀 클릭 후 우클릭해서 링크 - 현재 문서-선택한 셀의 이름인 특정 시트 누름
'Anchor= 링크를 추가할 셀
'Address = 웹페이지주소 or 파일경로
'SubAddress =[선택 인수] 내부 시트로 이동할 때 주소
WS.Hyperlinks.Add Ws.Range("C"&i),"", WB.Worksheets(i).Name &"!A1" (시트이름! A1)
(링크를 추가할 셀은 선택한 시트의 셀에 적용)
("" : 내부에서 이동하는 시트라 빈칸으로 함)
(WB.Worksheets(i).Name &"!A1" (시트이름! A1) :SubAddress (이동할 주소) )
Sub CreatetoC()
'변수 선언
Dim WB As Workbook
Dim WS As Worksheet
Dim i As Long
Set WB = ThisWorkbook
Set WS = WB.Worksheets("목차")
For i = 1 To WB.Worksheets.Count
Debug.Print i
Debug.Print WB.Worksheets(i).Name
WS.Range("C" & i).Value = WB.Worksheets(i).Name
WS.Hyperlinks.Add WS.Range("C" & i), "", WB.Worksheets(i).Name & "!A1"
Next
End Sub
!) 쉼표와 마침표 언제 사용하나?
엑셀에서 VLookup(찾을 값, 범위, 열번호...) 처럼 씀
WS. Hyperlinks. Add 앵커, 링크 .....
미션2: 찾기 및 바꾸기 매크로
Sub FindReplace()
'변수 선언
Dim WS As Worksheet
Dim FindValue As String
Dim ReplaceValue As String
Dim Rng As Range
Dim R As Range
Set WS = ThisWorkbook.Worksheets("확진자경로")
FindValue = WS.Range("J4").Value
ReplaceValue = WS.Range("J5").Value
Set Rng = Selection
'For each 안에 있는 R는 할당하는 것이 아니라 돌아가면서 보므로 set을 안 씀
For Each R In Rng
If R.Value = FindValue Then (찾을값)
R.Value = ReplaceValue (바꿀값)
R.Interior.Color = 65535 (색 채우기 색)
End If
Next
End Sub
Sub FindReplace()
Dim WS As Worksheet
Dim FindValue As String
Dim ReplaceValue As String
Dim Rng As Range
Dim R As Range
Set WS = ThisWorkbook.Worksheets("확진자경로")
FindValue = WS.Range("J4").Value
ReplaceValue = WS.Range("J5").Value
Set Rng = Selection
For Each R In Rng
If R.Value = FindValue Then
R.Value = ReplaceValue
R.Interior.Color = 65535
End If
Next
End Sub
보너스 미션: XLookup함수 만들기
Function MyXLookUp(lookup_value, lookup_range As Range, return_range As Range)
'=MyXLookup(찾을 값, 찾을 범위, 출력 범위)
' 변수 선언
' i = 찾을범위 반복할 순번
Dim i As Long
' 찾을범위의 시작부터 끝까지 돌아가면서
For i = 1 To lookup_range.Rows.Count
'찾을범위의 i번째 값이 찾을값과 동일하면
'※힌트 : If ○○○.Cells(○).value = ○○ Then ... End If
If lookup_range.Cells(i).Value = lookup_value Then
MyXLookUp = return_range.Cells(i).Value
Exit Function
End If
'MyXLookup 함수의 결과값으로 반환범위의 i번째 값을 출력한 후 명령문을 강제로 종료한다.
'※힌트 : MyXLookup = ○○○.Cells(○).Value
'※힌트 : Exit Function
Next
End Function
Function MyXLookUp(lookup_value, lookup_range As Range, return_range As Range)
Dim i As Long
For i = 1 To lookup_range.Rows.Count
If lookup_range.Cells(i).Value = lookup_value Then
MyXLookUp = return_range.Cells(i).Value
Exit Function
End If
Next
End Function
강의를 마치며
VBA강의를 1,2주차에 80%를 배울 수 있다는 게 좋은 데 넘 어렵네요. 쉽지는 않지만 여러번 반복하면서 공부가 필요한 걸 느끼네요:: 다음 진도도 잘 따라갈 수 있도록 지속적으로 해야겠네요. Dim, Set, For, If문을 이용한 방법을 이용하는 데 엑셀에서 쓰는 함수도 포함되어 있다보니 뭔가 익숙해서 빨리 익히겠다 싶다가도 응용 들어가면 어렵네요::
'IT > 엑셀' 카테고리의 다른 글
| VBA 4주 완성 마무리_오빠두엑셀 (0) | 2022.08.05 |
|---|---|
| VBA 스터디 4강_오빠두엑셀 (0) | 2022.07.28 |
| VBA 스터디 3강_오빠두엑셀 (0) | 2022.07.20 |
| VBA스터디 1강_오빠두엑셀 (0) | 2022.07.03 |
