====== Excel에서 Syntax Highlighting SQL ======

{{tag>blog Excel 엑셀 매크로 vba sql 개발}}

인공지능, 머신러닝 등을 수행하기 위해선 Python, R 등 특화된 도구를 사용해야 하지만
그 전에 데이터 추출, 정리, 보고를 위해서 실제 현업에서는 엑셀을 많이 사용할 수 밖에 없습니다.\\
엑셀, PPT를 이용해서 보고서를 작성하고, 자료를 정리하는 경우가 많아서 SQL 쿼리를 엑셀에 정리를 하고 있습니다.
\\
\\
다른 IDE에서는 지원하는 Syntax Highlighting 기능이 없어, 실제 쓰기 위해서 간단히 만들어 보았습니다.
\\
{{blog:pasted:vba_sql_gif.gif}}
\\
\\

==== 글자 색 바꾸기 ====
[[https://www.extendoffice.com/documents/excel/3775-excel-highlight-part-of-text-in-cell.html|참고]] 를 기반으로 수정
<sxh vb>
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 = True
End Sub
</sxh>

현재 선택된 엑셀 범위에 대해서,
색, 모양을 바꾸기 위한 글자를 cFnd 라고 하고, 셀 내용을 cFnd 를 기준으로 split 한 다음,
그 위치를 기준으로 cFnd의 글자 개수만큼 글자 형태를 바꿔주는 함수입니다.

여기서 Split 함수를 한번 살펴보면
<code> Split ( Expression, [Delimiter], [Limit], [Compare] ) </code>
VBA에서 Split 함수는 기본적으로 대소문자를 구분기 때문에
Compare 파라미터에 vbTextCompare 를 전달해서 대소문자 구분없이 사용할 수 있도록 했습니다.
<sxh vb> Split(Rng.Value, cFnd, -1, vbTextCompare) </sxh>

현재는 bold체에 색깔만 함수를 통해서 전달받는 것으로 했는데,
[[https://docs.microsoft.com/en-us/office/vba/api/excel.font(object)|Range.Characters.Font]] 에 대한 설명을 보시고 수정하실 수 있습니다.
색깔에 RGB(00,00,00) 을 넣으면 RGB를 통해서도 색깔을 지정할 수 있습니다.

\\
\\

==== Highlight 할 문자들 ====
<sxh vb>
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, 13
End Sub
</sxh>

Array를 통해서 SQL에서 자주 쓰는 명령어들을 앞서 설정한 함수에 전달하였습니다.
평소에 많이 쓰는 명령어들만 넣어놔서, 필요하신 명령어들을 추가하면 될 것입니다.
SQL말고 다른 언어에도 충분히 활용 가능할 것 같습니다.

<WRAP center round important 60%>
필드명 등에 in, sum, or, and 등이 들어갈 수 있기 때문에 앞이나 뒤에 공백을 넣었습니다.
</WRAP>

\\
\\

==== 문자열, 주석 등 처리하기 ====
앞에서 만든 함수는 간단히 String을 찾아서 형태만 치환해 주는 형태인데요,\\
RegExp을 쓰기에 VBA는 번거로운 점이 많아서\\
' 이나 " 으로 묶여있는 문자열 등의 색깔을 구분하고 싶어서 아래처럼 수정해 보았습니다.\\

<sxh vb>
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 = True
End Sub
</sxh>

endFnd 를 Option으로 받아서
endFnd가 존재하면 cFnd에서 endFnd까지 문자형태를 바꾸고,
없으면 cFnd 사이를 바꿔주도록 했습니다.

간단히 isEnd 라는 변수를 사용해서 처음인지, 끝인지 구분하도록 했습니다.
여기에 쓰일 문자열들은 하나씩 설정해줘야 하니까, 파라미터에서 Array 도 제외했습니다.

아래와 같이 사용하면 됩니다.

<sxh vb>
    HighlightStrings_between "--", 10, Chr(10)
    HighlightStrings_between "as", 3, Chr(10)
    HighlightStrings_between "'", 10
    HighlightStrings_between """", 10
</sxh>

Chr(10)은 라인끝을 볼 수 있는 문자열이니까, 주석이나 as 같은 경우는 라인끝까지 형태를 바꿔주게 됩니다.

\\
\\

==== 활용 ====
아래와 같이 Contol 버튼이나 Shape에 Macro를 연결하여 사용하실 수 있습니다.

{{blog:pasted:20200215-130327.png?300}}

.xla 로 저장하여 리본메뉴를 통해서 실행하는 방법도 있습니다.
[[blog:excel_vba_ribbon_menu|Excel에서 리본 메뉴 만들기]] 참고하세요

\\
\\

==== Add-in 다운 ====

<WRAP center round download 60%>\\
{{blog:vba_add-in_example.zip|다운로드}} \\
</WRAP>\\


~~DISCUSSION~~