[엑셀 VBA] 짤막하고 유용한 사용자정의 함수 및 스니핏

짤막하고 유용한 사용자정의 함수 스니핏

• 엑셀을 사용하면서 왠지 있을 것 같은데 없거나 지원되지 않는 기능이 있다.
• 따로 추가기능을 만들거나 프로그램을 짜려니 그정도 노력을 기울일 정도로 필요한 것도 아니고
• 또 그냥 노가다를 하기엔 성가신 경우가 있어서 필요한 기능을 사용자정의 함수로 만들어보았다.
• 앞으로 사용자정의 함수와 관련해서는 본 포스팅에서 계속 업데이트 할 예정이다. (2021. 2. 2.)

1) 여러 범위의 셀내용(문자열) 합치기

 

• 엑셀 함수 Concatenate와 유사한 함수라고 생각하면 된다.
• 기존 Concatenate 함수는 셀 하나씩만 인자로 받을 수 있었던 반면,
• 이 함수는 범위만 지정해주면 모든 셀값을 하나로 연결해준다.

Function constr(ByRef cRng As Range) As String
  Dim r As Range
  Dim str As String
  For Each r In cRng  '입력받은 범위를 순회하면서
    str = str & r.Value '각 셀의 값을 누적 (사칙연산 아님)
  Next
  constr = str
End Function

2) 웹사이트의 HTML Document를 객체화하기

 

• (Updated 2021. 7. 3.)
• WinHttp를 활용하여 얻은 HTML 문자열을 HTML 문서 객체로 반환할 수 있다.
• HTML 문서로 설정하였기 때문에 QuerySelector 메서드 또는
• getElementsBy~ 메서드를 통해 HTML Document의 각 요소에 쉽게 접근이 가능하다.
• String 형식의 HTML에서 필요한 정보를 일일이 찾아서 Split 하는 것보다 훨씬 편하다.
• 아래 움짤은 제시된 코드를 살짝만 수정한 크롤링 예시이다.
• 네이버 테마주 목록 첫 페이지의 40개 테마주 명칭을 가져오도록 했다.

Sub stockTheme()
  '아래 두 개의 라이브러리 추가 필요
  'Microsoft WinHTTP Services, version 5.1 
  'Microsoft HTML Object Library
  
  Dim myUrl As String '웹페이지의 Url을 담을 변수  
  Dim oHttp As WinHttp.WinHttpRequest
  Dim oHTML As New HTMLDocument 'HTML 문서를 담을 객체
  Dim oEleName As IHTMLDOMChildrenCollection
  'querySelector를 활용하여 접근할 요소를 담을 객체(여기서는 네이버 테마주 명칭으로)
  
  myUrl = "https://finance.naver.com/sise/theme.nhn?field=name&ordering=desc&page=1"	
  '네이버 테마주 페이지 Url
  oHTML.body.innerHTML = getHTMLStr(myUrl)	'myUrl에서 가져온 HTMLDocument를 객체로 반환
  Set oEleName = oHTML.querySelectorAll("#contentarea_left table.type_1.theme tbody tr > td.col_type1 > a")
  '네이버 테마주 명칭을 담은 a 태그를 객체로 설정
  
  Sheets(1).Cells(1, "A") = oEleName.Item(0).innerHTML
  '테마 명칭 작성 (.Item(0) 안의 숫자를 바꾸어주면 된다.)
End Sub

Function getHTMLStr(ByVal url As String) As String 'HTML Document를 String 형식으로 반환하는 메서드
  Set oHttp = New WinHttp.WinHttpRequest
  oHttp.Open "GET", url, False
  oHttp.send
  getHTMLStr = oHttp.responseText
End Function

3) 문자열 분리하기(Split 함수 따라하기)

 

• (Updated 2021. 7. 7.)
• VBA 함수 Split을 활용한 함수이다.
• 엑셀 문자열 함수인 LEFT와 FIND의 조합만으로 데이터 파싱이 어려운 경우,
• 즉 엑셀 데이터가 애매하게 규칙(?)적인 경우 효과적인 함수이다.
• 사용방법은 VBA 함수의 Split과 동일하다.

Function csplit(ByVal v As String, ByVal delimiter As String, ByVal mark As Long) As String
  csplit = Split(v, delimiter)(mark)
End Function

 

 Copyright (2021) Ruahneuma. All Rights Reserved.