Excel에서 Syntax Highlighting SQL
인공지능, 머신러닝 등을 수행하기 위해선 Python, R 등 특화된 도구를 사용해야 하지만
그 전에 데이터 추출, 정리, 보고를 위해서 실제 현업에서는 엑셀을 많이 사용할 수 밖에 없습니다.
엑셀, PPT를 이용해서 보고서를 작성하고, 자료를 정리하는 경우가 많아서 SQL 쿼리를 엑셀에 정리를 하고 있습니다.
다른 IDE에서는 지원하는 Syntax Highlighting 기능이 없어, 실제 쓰기 위해서 간단히 만들어 보았습니다.
글자 색 바꾸기
참고 를 기반으로 수정
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
Sub HighlightStrings(aFnd As Variant, color As Integer) Application.ScreenUpdating = False Dim Rng As Range Dim cFnd As String Dim xTmp As String Dim x As Long Dim m As Long Dim y As Long For i = LBound(aFnd) To UBound(aFnd) cFnd = aFnd(i) y = Len(cFnd) For Each Rng In Selection With Rng m = UBound(Split(Rng.Value, cFnd, -1, vbTextCompare)) If m > 0 Then xTmp = "" For x = 0 To m - 1 xTmp = xTmp & Split(Rng.Value, cFnd, -1, vbTextCompare)(x) With .Characters(Start:=Len(xTmp) + 1, Length:=y) .Font.ColorIndex = color .Font.FontStyle = "bold" End With xTmp = xTmp & cFnd Next End If End With Next Rng Next i Application.ScreenUpdating = TrueEnd Sub |
현재 선택된 엑셀 범위에 대해서,
색, 모양을 바꾸기 위한 글자를 cFnd 라고 하고, 셀 내용을 cFnd 를 기준으로 split 한 다음,
그 위치를 기준으로 cFnd의 글자 개수만큼 글자 형태를 바꿔주는 함수입니다.
여기서 Split 함수를 한번 살펴보면
1 |
Split ( Expression, [Delimiter], [Limit], [Compare] ) |
VBA에서 Split 함수는 기본적으로 대소문자를 구분기 때문에
Compare 파라미터에 vbTextCompare 를 전달해서 대소문자 구분없이 사용할 수 있도록 했습니다.
1 |
Split(Rng.Value, cFnd, -1, vbTextCompare) |
현재는 bold체에 색깔만 함수를 통해서 전달받는 것으로 했는데,
Range.Characters.Font 에 대한 설명을 보시고 수정하실 수 있습니다.
색깔에 RGB(00,00,00) 을 넣으면 RGB를 통해서도 색깔을 지정할 수 있습니다.
Highlight 할 문자들
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Sub highlight_sql() aFnd = Array("select", "from", "where", _ "left outer join", "left join", " on", " in", "not in", "inner join", "union", _ "(", ")", "=", "group by", "order by", "insert ", "update ", _ "truncate table", "drop table", "if exists", "create table", "having") HighlightStrings aFnd, 5 aFnd = Array("sum(", "count(", ".", "'", _ " like", " and", " or", " between", "),", _ "rank()", " over", "partition by", " asc", " desc", _ "min(", "max(", "distinct", "cast(", "to_char(") HighlightStrings aFnd, 13End Sub |
Array를 통해서 SQL에서 자주 쓰는 명령어들을 앞서 설정한 함수에 전달하였습니다.
평소에 많이 쓰는 명령어들만 넣어놔서, 필요하신 명령어들을 추가하면 될 것입니다.
SQL말고 다른 언어에도 충분히 활용 가능할 것 같습니다.
필드명 등에 in, sum, or, and 등이 들어갈 수 있기 때문에 앞이나 뒤에 공백을 넣었습니다.
문자열, 주석 등 처리하기
앞에서 만든 함수는 간단히 String을 찾아서 형태만 치환해 주는 형태인데요,
RegExp을 쓰기에 VBA는 번거로운 점이 많아서
' 이나 “ 으로 묶여있는 문자열 등의 색깔을 구분하고 싶어서 아래처럼 수정해 보았습니다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
Sub HighlightStrings_between(cFnd As String, color As Integer, Optional endFnd As String = "") Application.ScreenUpdating = False Dim Rng As Range Dim xTmp As String Dim x As Long Dim m As Long Dim y As Long isEnd = 0 For Each Rng In Selection With Rng m = UBound(Split(Rng.Value, cFnd, -1, vbTextCompare)) If m > 0 Then xTmp = "" For x = 0 To m - 1 xTmp = xTmp & Split(Rng.Value, cFnd, -1, vbTextCompare)(x) If endFnd = "" Then If isEnd = 0 Then y = Len(Split(Rng.Value, cFnd, -1, vbTextCompare)(x + 1)) + 1 'MsgBox Split(Rng.Value, cFnd)(x + 1) isEnd = 1 Else y = Len(cFnd) isEnd = 0 End If Else If Len(Split(Rng.Value, cFnd, -1, vbTextCompare)(x + 1)) = 0 Then y = Len(cFnd) Else y = Len(Split(Split(Rng.Value, cFnd, -1, vbTextCompare)(x + 1), endFnd, -1, vbTextCompare)(0)) + Len(cFnd) End If 'MsgBox Split(Split(Rng.Value, cFnd)(x + 1), endFnd)(0) End If With .Characters(Start:=Len(xTmp) + 1, Length:=y) .Font.ColorIndex = color '.Font.FontStyle = "bold" End With xTmp = xTmp & cFnd Next End If End With Next Rng Application.ScreenUpdating = TrueEnd Sub |
endFnd 를 Option으로 받아서
endFnd가 존재하면 cFnd에서 endFnd까지 문자형태를 바꾸고,
없으면 cFnd 사이를 바꿔주도록 했습니다.
간단히 isEnd 라는 변수를 사용해서 처음인지, 끝인지 구분하도록 했습니다.
여기에 쓰일 문자열들은 하나씩 설정해줘야 하니까, 파라미터에서 Array 도 제외했습니다.
아래와 같이 사용하면 됩니다.
1 2 3 4 |
HighlightStrings_between "--", 10, Chr(10)HighlightStrings_between "as", 3, Chr(10)HighlightStrings_between "'", 10HighlightStrings_between """", 10 |
Chr(10)은 라인끝을 볼 수 있는 문자열이니까, 주석이나 as 같은 경우는 라인끝까지 형태를 바꿔주게 됩니다.
활용
아래와 같이 Contol 버튼이나 Shape에 Macro를 연결하여 사용하실 수 있습니다.
.xla 로 저장하여 리본메뉴를 통해서 실행하는 방법도 있습니다.
Excel에서 리본 메뉴 만들기 참고하세요
Discussion