엑셀 VBA 기본기능 정리 - VBA 함수, 워크시트함수, 메서드 불러오기

여는 글

여러가지 라이브러리와 기능이 지속적으로 제공되는 다른 언어들과 달리,

 

일정 수준 이상으로 발전하지 않고 호환성을 위해 멈추어있는 VBA는 군더더기라고 할만한 기능은 딱히 없는것 같습니다.

 

배워두면 결국은 대부분 언제 어디서든 쓰게 되어있는 것 같더군요.

 

그 중에서도 가장 많이 사용되는 핵심 기능들이 Applicaition / Worksheetfuntion 이하의 함수/메서드인것 같습니다.

 

물론 Range 등 숨쉬듯이 사용하게되는 여러 객체도 있지만, 엑셀에 기본적으로 존재하는 특정 기능을 불러오는 함수들이 대부분 여기에 속하는 것 같네요.

 

자주 사용해보았던 함수들을 한 번 정리해보았습니다.

 

 

 

---

엑셀 VBA 기본기능 정리 - VBA 함수, 워크시트함수, 메서드 불러오기

• VBA 함수, 워크시트함수, 메서드
• 사용자 정의함수
  [Fuction 함수명(인수1 as 자료형, 인수2 as 자료형 ...) As 자료형 ~ 함수기능 삽입 ~ End Fuction]
  * 엑셀의 사용자 정의 함수는 매우 느리므로, 권장할만한 것이 못된다고 함.
• 워크시트함수
• 기본형 : Application.WorksheetFunction.기능명(기능변수 삽입)
• 활용예시
• Match함수의 예시 : Application.WorksheetFunction.Match("찾을 String", 찾을 범위, 0(정확도))
  * 주의 : Match함수는 중복된 값이 있다면 가장 먼저 찾은 값을 반환한다
• 사용상 주의사항

Application 함수와 워크시트 함수의 차이

• 출력 : Print/MsgBox
• Debug.Print() : 일반적인 출력기능으로 직접실행창으로 결과물이 나타남.
• MsgBox("") : 메시지박스형 출력기능
• "" : 일반적인 문자열은 ""안에 넣는다.
• 문자열이 아닌 변수등은 ""바깥에 두며, 각 개체간 연결시 &를 사용한다.
• 특수개체 VbCrLf : C++의 endl에 해당하는 줄바꾸기 기능이다.
• VbCr : CarriageReturn문. 현재 줄의 처음으로 커서나 헤드를 이동한다.
• VbLf : LineFeed문. 커서나 헤드가 현재 위치에서 다음줄로 이동한다.
• VbCrLf : Cr+Lf. 위의 2기능은 과거 프린터의 제어를 위하여 존재하던 기능으로, 현대의 Enter키 기능은 위의 2가지가 혼합된 형태. 셀 내부에서 줄을 바꾸려면 이 기능을 써야한다.
• 기타 간결한 함수
• IsNumeric(내용물) : 내용물이 숫자인지 판별
• Mid(개체, 위치, 추출할 문자 수) : 엑셀에서의 mid함수와 동일
• Range.Interior.Color = RGB(R, G, B) // 여기서 RGB는 각각 0~255의 숫자
• InStr / InStrRev(문자열, 찾을 문자) : 찾을 문자가 처음으로 나타나는 위치를 반환한다. InStr은 정순으로, InStrRev는 역순으로 문자를 찾음

 

• 특정 글자 찾기 : Find
• 주요 필드/메서드 - rng.Find(what / After / LookIn / LookAt)
• what : 찾을 내용 (string)
• After : 보통생략. 생략시, 선택셀 이후부터(해당셀 제외), 찾을 내용을 수색함을 의미.
• Lookin : 찾는 위치 (수식, 값, 메모). 보통생략. 생략시 Formula
• LookAt : 찾는 내용이 부분적/전체 일치하는지 여부 (xlPart / xlWhole)
• 활용예 : 선택된 셀을 기준으로 아래쪽에 위치한 특정 글자를 찾기 

Public Sub FindingFx()
Dim uWanted As String
    uWanted = "찾을 글씨"
Dim uRange As Range                     			'글씨 찾기 시작 위치를 저장할 변수 Range 선언
    Set uRange = ActiveCell
Dim uColor As Long                      			'찾은 글씨가 위치하는 셀의 색상값을 저장할 변수 선언
    uColor = RGB(0, 255, 100)
Dim rng As Range, cf As Range
Dim ad As String
    Set rng = Range(uRange, Cells(Rows.Count, uRange.Column).End(xlUp)) '찾을 범위 설정Set cf = rng.Find(uWanted, , , xlWhole)  'Find 적용(uWanted의 내용이 전체 일치하는 셀을 찾는다)
If Not cf Is Nothing Then               			'If문 적용. 조건 : cf가 Nothing이 아님
    ad = cf.Address                     			'ad변수에 cf의 주소를 넣는다.
    Do                                  			'Do ~ Loop 적용.
        cf.Address = ad                 			'조건 : cf.Address = ad가 될때까지
        cf.Interior.Color = uColor      			'Find에 걸린셀에 uColor색상 적용
        Set cf = rng.FindNext(cf)       			'rng 범위에서 다음 항목 Find(검색)
        Loop Until cf.Address = ad      			'rng.FindNext가 모든 범위를 순환 후, 첫 셀로 귀환시 종료
End If
End Sub

 

• 특정 글자 바꾸기 : Replace
• 기본형 : rng.Replace 기존문자, 대체할문자
• 주로 대체되는 문자 : ChrW(160) - 투명 반각문, ChrW(13) - Carriage Return문, ChrW(10) - LineFeed문 * ChrW(13) = VbCr // ChrW(10) = VbLf
• 주의사항 : 위 3가지가 아님에도 불구하고 셀 내에 알 수 없는 문자가 있는 것으로 인식되어, 빈칸이 아니라고 나오는 경우가 있다. 이 경우, 워크시트에서의 isblank()에선 False로 나오나, VBA에서의 [=""]에서는 True로 나오므로, [if c.value ="" then c.clearcontents]를 이용하여 시트 내 셀들을 정리해주는 것이 좋다.
• 활용형 : 특정 문자 지우기 / 특정 문자 입력받아서 지우기

Public Sub 특정문자지우기()
Dim rng As Range
Dim c, target, i
    target = Array(ChrW(160), ChrW(13), ChrW(10))               '지워버릴 문자지정
Set rng = Application.InputBox("삭제할 범위 선택", Type:=8)     '범위를 입력받아 변수에 저장
For Each c In rng
    For i = 0 To UBound(target)
        c.Replace target(i), ""
    Next
Next
End Sub

 

 

 

Public Sub 특정문자지우기2()                		'삭제할 문자를 입력받는 경우
Dim rng As Range
Dim c, i
    i = 0
Dim target()
    Do While 1
    ReDim Preserve target(i)
    target(i) = Application.InputBox("삭제할 문자를 입력하시오. exit를 입력시 종료함", Type:=2)
    If target(i) = "exit" Then Exit Do      		'삭제할 문자를 입력받아 배열로 지정함
    i = i + 1
    Loop
Set rng = Selection.CurrentRegion           		'범위기준 : 선택셀이 포함된 표
For Each c In rng
    For i = 0 To UBound(target)
        c.Replace target(i), ""
    Next
Next
End Sub

 

• 중복값제거 / 고유값추출 : RemoveDuplicates
• 기본형 : RemoveDuplicates [범위 중 실제 중복값을 검출할 행], [머리말이 있는지 여부 (xlNo/xlYes)]
• 활용예

Public Sub 사본삭제()
Dim 삭제할열 As Long
    삭제할열 = 1
Selection.CurrentRegion.RemoveDuplicates 삭제할열, xlYes     '선택영역을 기준으로 삭제할열의 중복값 삭제 / 머릿글있음
End Sub

 

 

• 특정 문자열로 나누기 : Split
• 기본형 : Split(나눌 셀 1개, 나눌 문자, 반환받을 substring의 최대 숫자(생략가능))
• 활용시 주의사항 : Split은 배열을 반환하므로 첫 번째 열에서 s를 string으로 명시하거나, 동적배열로 나타내지 않아도 자동으로 배열로 바뀌게 된다. (Dim s() as string ☞ Dim s, s() = Split(...) ☞ s = Split(...)
• 활용예

Public Sub 나누기기능()
Dim s() As String               			'변수 s를 선언한다.
Dim i As Long
s() = Split(Selection, "  ")   				'공백을 기준으로 문자를 나눈 후 s에 대입한다
For i = 0 To UBound(s)         				's의 크기만큼 for문을 반복한다.
Cells(i + Selection.Row, Selection.Column).Value = s(i)
Next
End Sub