[VBA] 셀의 넓이를 픽셀로 지정하기 (set column width by pixels)

정말 지긋지긋한 마이크로 소프트 입니다. 저는 VBA 를 하면서 이번 포스트 내용만큼 어의가 없는 경우는 없었던거 같습니다. 왜 때문이냐고요? 바로 아주 단순한 셀 넓이를 VBA 를 이용하여 지정하는 방법 때문입니다.

자 제가 왜 이런 반응을 보이는가 하면요.

엑셀 VBA 에서 셀의 넓이 즉 range.columnWidth 를 지정할때 정말 독특한 단위와 방법을 사용하기 때문입니다.

일단 셀의 넓이는 다음의 두가지 방법으로 구할 수 있습니다. 그런데 그 두가지가 반환하는 값의 정체를 보면 정말 당황스럽기 짝이 없죠.

Range.Width [읽기]
 : 셀의 넓이를 point 로 반환한다. 

Range.ColumnWidth [읽기/쓰기]
 : (나름데로 해석한 결과)셀의 넓이를 현재 지정된 폰트의 "Normal" 인 상태를 기준으로 하는 글자 하나의 크기를 1로 하고 마진값을 추가한 값을 설정 또는 반환한다.

 

 

????????????

 

 

저게 뭔가요.. 

물론 글자가 짤리 않도록 셀의 폭을 지정하는 기능 아주 좋습니다. 글자수만 알면 되는거죠. 훌륭합니다. 네.

그런데 왜 Width 는 읽기 만 가능하며 point 나 pixel 같은 값으로는 설정이 안되는 것인가요? 저와 같은 그래픽 디자이너는 이미지를 붙이든 뭘해도 pixel 을 기준으로 값을 정의하는게 일상인데요. 뜬금없이 왠 글자의 크기를 이용하여 margin 까지 덧붙여 크기가 지정되는 걸까요? 게다가 현재 지정된 폰트의 Normal 상태 일때 라니... 만약 글자 크기가 달라지거나 스타일이 달라지면 같은 값을 지정해도 폭이 달라질 수 있다는 이야기가 됩니다.

헐

 

해외 사이트들을 구글링 해보았으나 마땅한 답이 없더군요. 

그래서 그냥 코드를 짜 보았습니다.

VBA function : Pixel to ColumnWidth

Sub setColumnWidth(ByVal mRng As Range, ByVal targetWidth_pixel As Integer)

    'point = pixel * 0.75'
    'pixel = point / 0.75'
    'columnWidth = char Width + margin(fixed)'
    
    Dim cWidth_A As Double
    Dim cWidth_B As Double
    Dim cWidth_margin As Double
    Dim columnWidthRatio As Double
    Dim testColumnWidth As Double
    
    Dim cWidth As Double
    
    testColumnWidth = 10
    
    
    ' 가로 크기 지정시 발생되는 margin 을 알아보기 위하여 1배, 2배 크기일때 유지되는 margin 을 구해보자'
    mRng.ColumnWidth = testColumnWidth
    cWidth_A = mRng.Width   '(point)'
    
    mRng.ColumnWidth = testColumnWidth * 2
    cWidth_B = mRng.Width   '(point)'
    
    ' 항상 고정으로 유지되는 margin 크기 확인 (point)'
    cWidth_margin = cWidth_B - ((cWidth_B - cWidth_A) * 2)
        
    'point 값이 columnWidth 로 변환되기 위한 ratio 를 구한다. 이때 전체 넓이에서 위에서 구한 margin 을 생략하고 계산한다. '
    'columnWidthRatio : point --> columnWidth'
    columnWidthRatio = mRng.ColumnWidth / (cWidth_B - cWidth_margin)
    
    '최종 크기 지정'
    mRng.ColumnWidth = (targetWidth_pixel - (cWidth_margin / 0.75)) * 0.75 * columnWidthRatio
        
        
End Sub

 

약간 복잡해 보이지만 함수 형태로 만들어 놓았으므로 코드를 가져다가 그냥 사용하시면 됩니다. 인자로 크기를 조정해야 하는 셀과 pixel 기준의 크기를 입력해 주시면 되고요. 아래와 같이 사용하시면 됩니다.

Sub test()
    Dim rngA As Range    
    Set rngA = [d3]
    
    setColumnWidth rngA, 256
End Sub

 

이렇게 하면 D3 에 해당하는 column 의 폭이 256 pixel 로 설정이 됩니다.

 

코드를 좀 보시는 분은 금방 아시겠지만 원리는 다음과 같습니다.

columnWidth 는 고정된 단위가 아니므로 point 라는 단위를 이용하여 columnWidth 로 환산하기 위한 ratio 를 구해야 합니다. columnWidth / Width 하면 되는거죠. 그런데 여기에 함정이 있습니다. 

columnWidth 라는 값이 반환하는 값이 글자의 폭 + margin 이기 때문에 위와 같이 단순 계산을 하면 정확한 값으로 설정을 할 수가 없습니다. margin 만큼씩 계속 오차가 있는 것이지요. 그래서 margin 을 구하는 과정이 선행되었던 것입니다.

제가 몇번 테스트 해본 결과 글자을 속성을 중간에 바꾸지 않는다면 margin 의 값은 항상 일정한 것을 확인하였기 때문에 columnWidth 를 10 , 20 으로 두배 차이나게 각각 적용했을때의 Width 를 이용하여 변하지 않는 크기를 찾아내 해당 크기를 margin 으로 지정하였습니다.

결과적으로는 현재 구해진 Width(point) - margin(point) 로 ColumnWidth 를 나누어 주게 되면 point 값을 columnWidth로 환산 하기 위한 비율이 구해지게 되는 것이죠.

끝으로 입력된 픽셀에서 margin 에 해당되는 픽셀 크기를 빼 준뒤 point 로 전환한 다음 위에서 구한 비율을 곱해주게 되면 정확하게 pixel 에 대응되는 columnWidth 를 구할 수 있게 됩니다.

 

정말 저는 미국인들이 inch 를 사용하는 이유도 모르겠고 microSoft 사에서 위와 같이 독특한 단위만 입력하도록 개발해놓은 이유도 알 수가 없습니다. 

이번 기회로 저역시 좋은 공부가 되었기에 포스트로 남깁니다.

도움이 되셨거나 본 포스트가 괜찮으셨으면 공감 부탁드립니다~

 

2020/01/30 - [분류 전체보기] - [vba] 셀 속성 조정하기 (넓이, 높이, 숨기기, 테두리 등)

[vba] 셀 속성 조정하기 (넓이, 높이, 숨기기, 테두리 등)

2019/09/20 - [DEV/VBA] - [VBA] 엑셀에 그림 자동으로 넣기

[VBA] 엑셀에 그림 자동으로 넣기

2019/08/30 - [DEV/VBA] - [VBA]RGB 색상 값이 들어있는 셀에 셀 색상 지정하기

[VBA]RGB 색상 값이 들어있는 셀에 셀 색상 지정하기

2022.12.02 - [DEV/VBA] - [excel vba] 16진수를 10진수로, 10진수를 16진수로 변경

[excel vba] 16진수를 10진수로, 10진수를 16진수로 변경