7월 30일에 7월 한달 간 오빠두엑셀 VBA 4주 완성 스터디를 마무리하는 강의를 진행했습니다.
4주 동안 했던 VBA 수업 내용 중 몇 가지를 다시 한 번 되돌아오면서
마지막에 다뤘던 사용자폼이 예전 버전으로 꾸며졌던 것이라 현재 뜨는 창처럼
꾸며주는 걸 알려주신 뜻깊은 시간을 보냈습니다.
수업 후 나중에 기억하고자 작게나마 적어놓은 스터디 노트를 좋게 봐주셔서
감사하게도 MS MVP 후드집업을 얻었습니다.
XL사이즈인데 L사이즈보다 약간 커서 에어컨 바람에 추울 때 입고 있기에 딱이에요ㅠㅠ
안감도 좋아서 너무 부드러워서 덥지만 않았다면 계속 입고 다녔을 것 같아요ㅎㅎ
우수 수강생으로 뽑아주시고 좋은 말씀해주시고 축하해주신 분들 모두 감사드립니다.
어디다가 올려야할지 몰라서 이렇게라도...
선생님... 후드집업 엄청 좋더라고요. 정말 너무 감사드려요~
마지막 VBA 4주 완성 마무리 강의해주신 내용을 정리해볼게요.
1. 매크로 기록
엑셀에서 [개발도구] - [매크로 기록]이나 왼쪽 하단에 있는 매크로 기록 단추를 눌러
매크로 이름은 매크로 1
저장 위치는 현재 통합 문서로
설정하여 확인 버튼을 눌러 모듈1을 생성해준다.
그 이후 [데이터] - 오름차순/ 내림차순 정렬, 아무셀을 선택하거나 삭제 등의 동작을
하면 모듈1에 모두 기록이 되고 하단에 눌렀던 매크로기록을 다시 눌러 중단시킨다.
모듈 1에 기록된 내용은 나중에 명령문으로 쓸 때 필요한 단어를 알 수 있다.
2. 4가지 코드
4가지 코드만 알아도 왠만한 코딩을 할 수 있다.
해당 코드는 Dim, Set, For, If 이다.
Dim과 Set에서는 데이터 형식인
논리값, 정수, 실수, 문자 , 개체 등등을 사용할 수 있다.
이에 대한 자세한 내용은 VBA 2강에서 다루고 있다.
3. 실습
3-1. 변수 선언 & 할당 기초 실습
( VBA 2강 내용 )
Sub DimTest()
Dim i As Long 'Long은 정수 (0,1,2...)
Dim d As Double 'Double은 실수 (0,1,1.2... 소수 포함 실수) Dim s As String 'String 은 문자
Dim Rng As Range 'Range는 범위
i=1.1 '소수점이 아닌 정수 1이 기록됨
d=1.1 '"사과"로 입력될때는 오류가남 왜냐면 실수값만 쓸 수 있어서
MsgBox i
MsgBox d (메시지박스는 확인 작업 후 '표시를 하여 안 뜨게 함)
Set Rng = Range("A1") 'set 없으면 91타임 개체가 제대로 안되있을때 뜸
MsgBox Rng.value ' 사번이 나옴
End Sub
Sub DimTest()
Dim i As Long
Dim d As Double
Dim s As String
Dim Rng As Range
i=1.1
d=1.1
'MsgBox i
'MsgBox d
Set Rng = Range("A1")
MsgBox Rng.value
End Sub
3-2. CountGender 명령문
( VBA 4강 나만의 CountIf 응용 )
sub CountGender()
Dim Rng As Range
Dim R As Range
Dim a As Long '남자 직원수
Dim b As Long '여자 직원수
( VBA 3강 DynamicRange 다룸 )
Set Rng = DynamicRange(Sheet1, "C", 2)
(해당문서, 열, 시작행) 'z_PrevLesson 모듈에 있음
MsgBox Rng.Address '셀주소 출력
For Each R in Rng '각 각의 셀들을 돌아가면서 보는 범위
If R.value ="남" Then
a= a+1
Else
b = b+1
End If
Next
MsgBox "남자:" & a & "여자:" & b
End Sub
메시지 결과로 추가된 행과 함께면 남자:10 여자:17로 결과값이 출력된다.
sub CountGender()
Dim Rng As Range
Dim R As Range
Dim a As Long
Dim b As Long
Set Rng = DynamicRange(Sheet1, "C", 2)
MsgBox Rng.Address
For Each R in Rng
If R.value ="남" Then
a= a+1
Else
b = b+1
End If
Next
MsgBox "남자:" & a & "여자:" & b
End Sub
(+) DynamicRange 명령문
Function DynamicRange(WS As Worksheet, Column As String, InitRow As Long) As Range
Dim i As Long
Dim Address As String
i = WS.Range(Column & "1048576").End(xlUp).Row
If i < InitRow Then i = InitRow
Address = Column & InitRow & ":" & Column & i
Set DynamicRange = WS.Range(Address)
End Function
3-3. collection vs Array
( VBA 4강 내용 )
비교를 위한 명령문 3-1에서 추가 입력함.
Dim Rng As Range
Dim coll As Collection
Dim arr As Variant
arr '한정된 값만 가져와서 값을 바로 씀
coll ' 속성 4가지만
Rng '속성 여러개
Sub DimTest()
Dim i As Long
Dim d As Double
Dim s As String
Dim Rng As Range
Dim Rng As Range
Dim coll As Collection
Dim arr As Variant
arr
coll
Rng
i=1.1
d=1.1
'MsgBox i
'MsgBox d
Set Rng = Range("A1")
MsgBox Rng.value
End Sub
4. 사용자 유저폼 Win10 시스템 창처럼 만들기
( VBA 4강 내용에서 추가적)
4-1. 사용자 유저폼 구성하기
[ VBA 편집기 ] - [ 삽입 ]- [ 사용자 정의폼 ]에서 유저폼을 만듭니다.
( 도구상자와 속성창 없을 때 [ 보기] - [도구상자 ] / 속성창 F4 키 )
① 유저폼 처음 생성 시 속성창에서
폼의 이름 : frmEmployee
Caption(보이는 폼의 이름): 직원 관리 유저폼
Font - [...] - 맑은 고딕, 보통, 12
BackColor : 창배경 (&H80000005&)
BorderColor : 활성 테두리 (&H8000000A&)
② 도구상자에서 목록상자, 레이블, 텍스트 상자
목록상자 : 아이콘 클릭해 가로로 긴 직사각형으로 만듦
레이블 (A) 와 텍스트 상자(abl) : 하나씩 만든 후 둘을 드래그하여 복사붙여넣기로 4개 만듦
(레이블은 사번, 이름, 성별, 부서 순으로 이름 바꿈)
마지막은 텍스트 상자를 하나 더 추가한다.
!) 사용자 유저폼을 두번 누르면 매크로 명령창이 나오는 데 다시 돌아가려면
왼쪽에 개체보기 아이콘이나 생성된 유저폼을 다시 누르면 돌아감
!) 명령 단추를 사용해야할 마지막을 텍스트 상자로 추가해서 하는 이유는
명령 단추는 예쁘게 꾸미지 못하기 때문이다.
③ 속성창에서 이름 바꾸기
목록상자 이름: lstMain
사번 이름: txtID
이름 : txtName
성별 이름: txtGender
부서 이름: txtDivision
④ 속성창의 속성 바꾸기
먼저 목록상자 선택 후 바꾸고
텍스트 상자를 ctrl를 눌러 모두 한꺼번에 바꾼다.
SpecialEffect - 0 frmSpecialEffectFlat (평평한)
BorderStyle - 1 frmBorderStyleSingle
BorderColor - 활성 테두리 ( &H8000000A& )
마지막 텍스트 상자는 한 번 더 클릭해 [업데이트]라고 입력해주고
속성창에서
TextAlign - frmTextAlignCenter (가운데 정렬)
SelectionMargin -Flase
( 왼쪽 빈 공간인 마진을 없애 가운데가 오른쪽으로 안 치우치게 함)
사번 변경 없이 고정시키고자 할 때는 속성창에서
Locked - True
BackColor -메뉴모음 (&80000004&)을
설정한다.
4-2. 사용자 유저폼 디자인 매크로
z_WindowStyle 모듈에 있는 매크로를 이용해
윈도우 10 스타일 기본 명령문과 윈도우 10 스타일 버튼 명령문을 만든다.
자세한 설명은 오빠두엑셀에서 유저폼 - 윈도우 10 을 찾으면 된다.
빨간 부분은 바꿔줘야했던 부분이다.
① 윈도우 10 스타일 기본 명령문
Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Dim ctl As Control
Dim btnList As String: btnList = "btnSubmit"
Dim vLists As Variant: Dim vList As Variant
If InStr(1, btnList, ",") > 0 Then vLists = Split(btnList, ",") Else vLists = Array(btnList)
For Each ctl In Me.Controls
For Each vList In vLists
If InStr(1, ctl.Name, Trim(vList)) > 0 Then OutHover_Css ctl
Next
Next
End Sub
'커서 이동시 버튼 색깔을 변경하는 보조명령문을 유저폼에 추가합니다.
Private Sub OnHover_Css(lbl As Control): With lbl: .BackColor = RGB(211, 240, 224): .BorderColor = RGB(134, 191, 160): End With: End Sub Private Sub OutHover_Css(lbl As Control): With lbl: .BackColor = &H8000000E: .BorderColor = &H8000000A: End With: End Sub
② 윈도우 10 스타일 버튼 명령문
명령문 범위 드래그 후 ctrl+H 로
찾을 내용 btnXXX, 바꿀내용 btnSubmit 한 후 모두 바꾸기를 눌러
6개 모두 바꾼다.
Private Sub btnSubmit_Exit(ByVal Cancel As MSForms.ReturnBoolean)
OutHover_Css Me.btnSubmit
End Sub
Private Sub btnSubmit_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
OnHover_Css Me.btnSubmit
End Sub
Private Sub btnSubmit_Enter()
OnHover_Css Me.btnSubmit
End Sub
!) 유저폼 실행하면 업데이트 버튼 색깔이 바뀌고
마우스 커서가 편집하는 모양이 나오는 데 바꾸는 방법으로는
텍스트 상자 우클릭 - 속성 - MousePointer - 1 frmMousePointerArrow
4-3. 사용자 유저폼 입력 관련 매크로
① initialize 명령문
유저폼을 두번 클릭해 매크로로 이동 후 이벤트에서 initialize를 선택해
실행되었을 때의 매크로를 입력하여 목록상자에 뜨도록 만들어준다.
Private Sub UserForm_Initialize()
Dim i As Long
i = Sheet1.Range("A1048576").End(xlUp).Row
Me.lstMain.ColumnCount = 4
Me.lstMain.RowSource = Sheet1.Range("A2:D" & i).Address
Me.lstMain.ColumnHeads = True
End Sub
Private Sub UserForm_Initialize()
Dim i As Long
i = Sheet1.Range("A1048576").End(xlUp).Row
(동적 범위 설정으로 마지막셀에서 위로 왔을 때 데이터 있는 마지막 셀을 알려줌)
Me.lstMain.ColumnCount = 4 ( 열의 갯수 )
Me.lstMain.RowSource = Sheet1.Range("A2:D" & i).Address (원본으로 사용할 시트의 범위(머리글 제외))
Me.lstMain.ColumnHeads = True (값이 있는 행 위에 머리글이 있다는 전제 하에 씀)
End Sub
② 목록상자 특정 직원 값 가져오기
Z_ListControls 모듈에 유저폼 관련 매크로들이 담겨있다.
Private Sub lstMain_Click()
Dim i As Long
i = Get_ListIndex(Me.lstMain)
'MsgBox i
'MsgBox Me.lstMain.List(i, 0)
Me.txtID.Value = Me.lstMain.List(i, 0)
Me.txtName.Value = Me.lstMain.List(i, 1)
Me.txtGender.Value = Me.lstMain.List(i, 2)
Me.txtDivision.Value = Me.lstMain.List(i, 3)
End Sub
Private Sub lstMain_Click()
Dim i As Long
i = Get_ListIndex(Me.lstMain) (선택한 값의 순번을 가져옴(받아올 리스트박스))
'MsgBox i ( 순번 출력 )
'MsgBox Me.lstMain.List(i, 0) (행번호, 열번호)
Me.txtID.Value = Me.lstMain.List(i, 0)
Me.txtName.Value = Me.lstMain.List(i, 1)
Me.txtGender.Value = Me.lstMain.List(i, 2)
Me.txtDivision.Value = Me.lstMain.List(i, 3)
End Sub
③ 입력 값 업데이트 매크로
VBA 2강에서 다룬 XLookUp과 관련된 내용이다.
텍스트 상자라서 btnSubmit에서 이벤트 click이 없기 때문에 MouseDown을 선택해야한다.
윈도우 10 스타일 버튼 명령문에서 MouseMove를 썼기 때문에 이 매크로로 써야한다.
Private Sub btnSubmit_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Dim Rng As Range
Dim R As Range
Dim sName As String
Dim sGender As String
Dim sDivisonAs String
Set Rng = DynamicRange(Sheet1, "A", 2)
sName = Me.txtName.Value
sGender = Me.txtGender.Value
sDivision = Me.txtDivison.Value
For Each R In Rng
If R.Value = Me.txtID.Value Then
R.Offset(0, 1).Value = sName
R.Offset(0, 2).Value = sGender
R.Offset(0, 3).Value = sDivision
End If Me.txtName.value
Next
MsgBox "직원정보가 업데이트 되었습니다."
End Sub
Private Sub btnSubmit_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Dim Rng As Range
Dim R As Range
Dim sName As String
Dim sGender As String
Dim sDivisonAs String
Set Rng = DynamicRange(Sheet1, "A", 2) 사번범위 동적으로 가져옴
sName = Me.txtName.Value 변수를 만들어 각 각의 값을 저장해서 바꿔주는 방식 적용
sGender = Me.txtGender.Value
sDivision = Me.txtDivison.Value
For Each R In Rng
If R.Value = Me.txtID.Value Then
R.Offset(0, 1).Value = sName 몇칸 이동 offset 오른쪽만으로만 이동이라 행은 0으로 고정된 것
R.Offset(0, 2).Value = sGender
R.Offset(0, 3).Value = sDivision
End If
Next
MsgBox "직원정보가 업데이트 되었습니다."
End Sub
!) 메시지 박스 여러번 뜨면 디버깅으로 오류인 부분을 잡아야한다.
④ 스크롤을 마우스 퓔로 바꾸는 매크로
z_MouseWheel 모듈에서 사용방법 리스트 박스로 되어있는 부분을 복사하여 유저폼 매크로에 붙여놓는다.
주석 (') 을 일괄적으로 지우기 위해서는 윗 줄에서 우클릭 후 편집을 누르면 뜨는 아이콘 중에 있다.
리스트 박스라고 되어 있는 부분을 lstMain으로 바꾸면 된다.
Private Sub lstMain_Exit(ByVal Cancel As MSForms.ReturnBoolean)
UnhookListBoxScroll
End Sub
Private Sub lstMain_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
HookListBoxScroll Me, Me.lstMain
End Sub
마지막 마무리 강의까지 알차게 필요한 매크로 명령문을 배울 수 있어 좋았습니다.
생각치 못한 좋은 일도 생기고 좋았어요.
10-11월 중에 스터디 진행을 생각하고 계시고 강의내용은 아직 정하지 못하고 계신다고 하네요.
파워쿼리를 얘기하시긴 했는데 확실하면 알려주신다고 하네요.
다음 스터디에도 알찬 내용을 배울 수 있다고 생각하니 좋아요~
'IT > 엑셀' 카테고리의 다른 글
| VBA 스터디 4강_오빠두엑셀 (0) | 2022.07.28 |
|---|---|
| VBA 스터디 3강_오빠두엑셀 (0) | 2022.07.20 |
| VBA스터디2강_오빠두엑셀 (0) | 2022.07.10 |
| VBA스터디 1강_오빠두엑셀 (0) | 2022.07.03 |
