VBA excel 삽질중 알게 된 것...

잘만 활용한다면 정말 좋은.....그러나 현실은..ㅎㅎ

업무상 해야 하는 일중..(역시나..) vLookUp을 이용해서 들고오는 스트링 중에 일부만 폰트색상이 적용된 스트링이 있다.

이를 지금까지는 그냥 손노가다로 하였는데... 물론 참조스트링을 이용해서 한번에 색상적용된 셀까지 복사해서 오는 스크립트는 작성하기 그리 어렵지 않다.

오늘은 이를 vLOOKUP 함수를 이용하듯이 나만의 myLOOKUP 함수를 만들어서 스트링을 들고온 후 여기에 폰트색상까지(스트링의 일부만 색상이 적용되어야 한다.) 자동으로 적용되도록 해보고 싶었다.

아래는 삽질의 흔적이다...ㅠㅠ

Function myVLOOKUP(value, range, offset)
    Dim ret As String
    Dim targetRange() As Variant
    Dim i As Long, j As Long, pos As Long
    Dim cr As Long, cc As Long
    cr = Application.ActiveCell.row
    cc = Application.ActiveCell.Column
    
    
    targetRange = range.value
    
    For i = LBound(targetRange, 1) To UBound(targetRange, 1)
        If StrComp(targetRange(i, 1), value) = 0 Then
            ret = targetRange(i, offset)
            Exit For
        End If
    Next

    myVLOOKUP = ret

    For j = 13 To Len(range.Cells(i, 3).value)
        If range.Cells(i, 3).Characters(Start:=j, Length:=1).Font.Color <> vbRed Then
            pos = j
            Exit For
        End If
    Next
    
    'Debug.Print Cells(Application.ActiveCell.row, Application.ActiveCell.Column).value
    Cells(cr, cc).Characters(13, pos - 13).Font.Color = vbRed
    'range(Application.ThisCell.Address).Characters(13, pos - 13).Font.Color = vbRed
        
    'Selection.Cells().Characters(13, pos - 13).Font.Color = vbRed
    'Application.Caller.Worksheet.Cells().Characters(13, pos - 13).Font.Color = vbRed

End Function

일단 리턴값을 지정한 후에도 MsgBox 를 이용해서 테스트를 해보니 이후로도 코드가 돌아간다.

따라서 리턴값 지정 후에 폰트색상을 적용해주면 되겠다 싶어서 오만가지 방법으로 해보았으나.... 되지 않는다.

폰트색상 적용하는 부분만 따로 빼서 함수로 만들어서도 해보았는데 되지 않는다. ㅎㅎ

대부분 error가 발생하거나 혹은 자가참조셀이 있다는 에러가 종종 나기도 한다.

이런저런 내용으로 구글링을 겁나 하다 보니 아래와 같은 stackoverflow의 유사한 글의 답변을 보게 되었다...

https://stackoverflow.com/questions/25022575/excel-vba-how-can-function-return-string-with-a-variety-of-colored-characters

Excel VBA: How can function return string with a variety of colored characters?

 

해당 글의 답글에 달린 Microsoft의 사용자지원 문서 링크는 아래와 같다..

https://support.microsoft.com/ko-kr/topic/excel%EC%9D%98-%EC%82%AC%EC%9A%A9%EC%9E%90-%EC%A7%80%EC%A0%95-%ED%95%A8%EC%88%98-%EC%A0%9C%ED%95%9C%EC%97%90-%EB%8C%80%ED%95%9C-%EC%84%A4%EB%AA%85-f2f0ce5d-8ea5-6ce7-fddc-79d36192b7a1

Excel의 사용자 지정 함수 제한에 대한 설명 - Microsoft 지원

 

즉 사용자 지정함수를 셀의 수식에서 호출한 경우(내가 하고 싶은 경우이다.) 단순히 값만을 리턴하는 것만은 할 수 있으나 셀의 서식을 지정하는 등의 동작은 불가하다는.... 내용의 문서이다. ㅠㅠ;;

뭐 기존에 하던대로 문서작업후 특정 프로시져를 한번 돌려서 적용되도록 하면 되겠으나... 좀 기깔나게? 수식으로 사용자 함수로 처리하면 편리할텐데... 아쉬운 기분이 들었다.

 

위의 이야기와는 별도로... 오늘 알게 된 것 중 또 하나는... UTF-8 형식의 문서로 텍스트 파일을 저장할 때 엑셀 셀에서 들고온 스트링 중에 줄바꿈 문자를 \n 으로 변환하여 넣어야 하는 경우는 아래와 같이 하면 된다.

Replace(Sheets("TargetSheetName").Cells(row, column).Value, vbLf, "\n")

visual basic의 여러 상수중 carriage return은 vbCr이고 line feed는 vbLf 그리고 둘을 한꺼번에 표현할 때는 vbCrLf 인데...

셀에서 들고온 스트링중에 alt-enter 를 통해서 줄바꿈한 것은 vbLf로 찾으면 된다는... 의미이다.(처음에 vbCrLf 로 한참 찾다가 안되어서...삽질을 좀 했다 ㅎㅎ)