개발하는 디자이너의 DIY 일상

안녕하세요. 오늘은 엑셀 리스트로 가지고 있는 웹 이미지 경로를 이용하여 일괄 다운로드 하는 방법을 소개해 드릴까 합니다. 

인터넷에 찾아보면 이런저런 다운로더들이 있는데요. 입맛에 맞는 프로그램을 찾는 것도 일이고 프로그램이 원하는 형식으로 다운로드용 이미지 리스트를 작성하는것도 번거롭더군요.

이미 엑셀로 이미지 리스트의 웹 주소가 있는데 그냥 엑셀만으로 다운로드 할 수 있을까 해서 좀 찾아 보았습니다. 찾아보니 괜찮은 레퍼런스들이 좀 있었는데요. 사용하기 번거로워 보여 직관적으로 손쉽게 사용할 수 있도록 코드를 좀 손보았습니다.

사실 제가 필요했던 상황이기도 해서 당장 급하게 찾았던 것인데 시간도 없고 별도 프로그램 찾는것 보다 레퍼런스를 이용해서 직접 수정해서 코딩하는게 더 빠르더군요.

저의 경우에는 무려 7000 장 이상의 이미지를 로컬로 내려받아서 확인을 해야 하는 상황이었는데요. 개발팀에 요청하니 4일이나 걸린다고 하더군요. 물론 그 분들도 다른 일정이 있었기 때문이었겠지만 저는 그렇게 기다릴 수는 없었습니다.

일단 저의 경우를 기준으로 설명드릴 수 는 없고 일반적인 상황을 가정해 보겠습니다.

엑셀의 A열에 이미지 7000장의 인터넷 경로가 있습니다. 

엑셀의 B 열에는 로컬에 저장되어야 할 이미지 파일명을 준비해 놓았다고 하면 아래의 코드로 간단하게 다운로드 할 수 있습니다.

엑셀을 이용하여 일괄 이미지 다운로드 하는 방법

alt + F11 을 눌러 vba 창을 열어준 뒤 아래 코드를 현재 통합 문서의 스크립트 창에 붙여 넣어 주세요.

그런다음 이미지 주소가 들어있는 열의 첫번째 셀의 이름, 저장할 이미지가 들어있는 열까지의 offset 등을 설정해 준뒤 F5 키를 눌러 실행해 주면 됩니다. 이때 키보드 커서가 Sub 함수 안에 있어야 바로 실행이 되고요, 그렇지 않은 경우 실행할 함수를 한번 선택해주는 창이 뜰텐데 downloadAllImage 를 선택해 주시면 됩니다.

Option Explicit

Sub downloadAllImages()

    Dim asht As Worksheet
    Dim rngA As Range
    Dim c As Range
    Dim sPath As String
    Dim extensions() As String
    
    Set asht = ActiveSheet
    Set rngA = asht.Range("a2") ' 이미지 리스트가 존재하는 첫번째 셀 '
    
    Set rngA = asht.Range(rngA, rngA.End(xlDown))
    
    sPath = "d:\ImageAutoDownload_test\\" ' 저장할 경로를 넣으세요'
    
    For Each c In rngA
        Dim myURL As String
        If Len(c.Value) Then
            Dim imgName As String
            
            imgName = c.Offset(0, 1).Value   ' 이미지별 저장할 이름이 기록된 열 '
            
            myURL = c.Value
            extensions = Split(myURL, ".") ' 확장자가 3글자가 아닐수도 있으므로 확장자를 판별해내기위한 배열 생성
                
            imgName = imgName & "." & extensions(UBound(extensions)) '위에서 구한 배열에서 확장자를 꺼내자
            
            WebFileDownload myURL, imgName, sPath
            
        End If
    
    Next c

End Sub


Public Function WebFileDownload(ByVal strURL As String, ByVal saveFileName As String, ByVal savepath As String) As Boolean
    Dim Buf() As Byte, oWinHttp
On Error GoTo Err_Sub
    Set oWinHttp = CreateObject("WinHttp.WinHttpRequest.5.1")
    With oWinHttp
        .Open "GET", strURL, 0
        .Send
        Buf = .ResponseBody
    End With
    Open savepath & saveFileName For Binary Access Write As #1
    Put #1, , Buf
    Close #1
       
    Set oWinHttp = Nothing
       
Err_Sub:
    If Err Then MsgBox Err.Description
    If Not oWinHttp Is Nothing Then Set oWinHttp = Nothing
    
End Function

어떠신가요? 복잡하지 않죠? 정상적으로 코드를 옮기고 세팅을 하셨다면 아마 잘 동작하실 겁니다.

이미지 7000 장을 하나하나 링크 복사해서 브라우저에 붙여넣고 다른이름으로 저장하고... 또 복사하고 붙여넣고 다른이름으로 저장하고... 했을걸 생각하면 정말 토나오는 일이지요. 만약 정말 일일이 수동으로 위와 같이 진행했다면 몇 일을 해야 했을 지도 모릅니다.

저의 경우엔 7000장 내려받는데 코딩하는 시간을 포함해서 20분만에 끝났습니다. ^^

이런게 바로 디자이너면서 개발하는 맛이지요. 

어쨌든 다운로드할 이미지 리스트를 가지고 계신데 적당한 툴이 없어 고민이셨던 분이 있으시다면 도움이 되었길 바랍니다.

2022-2-24일 수정

확장자가 3글자가 아닌 경우도 있을 수 있어 주소에서 유동적으로 확장자를 가져올 수 있도록 수정하였습니다.

'선택적 인수가 아닙니다.' 라는 오류가 나는 부분이 있어 내용 수정하였습니다. 함수 호출 시 저장할 경로인 sPath 가 누락되어 있었네요. 

2020/08/27 - [DEV/VBA] - 재택근무 필수 엑셀 "자리안비움" - 윈도우 꺼짐 방지

2020/06/22 - [DEV/VBA] - [vba] 초등학교 연산 자동 문제집 - 곱셈 추가

2020/01/08 - [DEV/c#] - Excel Automate, 엑셀 자동화 프로그램

2019/10/01 - [DEV/VBA] - [VBA] 엑셀 VBA로 포토샵 연동이 가능할까?

안녕하세요 주인장입니다.

코로나로 인한 개학연기로 아이들이 집안에서만 딩굴딩굴 거려 답답하신가요?

아이들에게 신나는 연산 문제를 내 주는건 어떨까요?

??

는 아니고 VBA 로 제가 그동안 덧셈, 뺄셈을 자동으로 만드는 방법을 소개해 드렸었는데요, 이번에는 두자릿수 곱셈문제를 추가해 보았습니다. 

아이들의 계산 공간을 위하여 기존의 가로 문제가 아닌 세로 연산 레이아웃이 필요했고요.

문제수를 조금 줄여 보았습니다.

문제는 난이도를 조금 조정할 수 있는 데 계산에 사용되는 최대 숫자의 크기 제한을 서서히 높이는 방식으로 구성하였으므로 엑셀 VBA 를 조금 이해하시는 분이라면 직접 수정하여 사용하시는 것도 가능합니다.

그냥 간단히 설명 드리자면 문제의 난이도를 수정하려면 아래의 순서로 하시면 됩니다.

버튼 --> 마우스 우클릭 --> 매크로 지정 --> 매크로창에서 선택되어 있는 매크로 편집 --> 최대값 변경

그리고...

채첨해보시면서 일일이 계산하는 것도 번거로운 일인듯 하여 정답보기 버튼을 추가로 넣었습니다.

문제를 출제 --> 프린트 --> 정답보기 --> 채점 또는 프린트 하여 채점 하는 식으로 사용하시면 됩니다.

다시 문제를 내버리면 이전에 만들어진 문제는 사라지므로 그렇게 된 경우엔 채첨하실때 직접 계산을 하시는 수 밖에 없답니다.

아들내미가 이걸로 며칠 하더니 연산 실력이 부쩍 올랐습니다. 늘 하던 실수도 많이 줄더군요.

그리고는 하는말이 우리 선생님한테 이런게 들어가지 않았으면 좋겠다고 하더라고요. ㅋㅋ. 매일 쪽지시험보면 어쩌냐고요.. 

선생님들. 어머님들 너무 심하게 시키지는 말아주세요~

본 포스트에 첨부된 엑셀 파일로 덧셈, 곱셈, 뺄셈을 모두 출재하실 수 있으니 기존의 포스트는 참고만 하시면 될 것 같습니다. 

그럼 더운 여름 코로나로 답답한 아이들에게 시원한 연산문제를 선물해줘보세요~

2019/09/10 - [DEV/VBA] - [VBA] 엑셀로 초등학교 저학년 문제집 만들기 (자동버전)

2020/04/17 - [DEV/VBA] - [vba] 초등학교 연산 문제 자동 출제, 이번엔 뺄셈에 도전

스크립트의 원리가 궁금하시다면 아래 포스트를 참고해주세요.

2019/09/03 - [DEV/VBA] - [VBA] 초등학교 저학년 수학 연산 문제 자동으로 만들기

이참에 본격적으로 VBA 도 한번 배워 보고 싶으시다면? 

2019/06/01 - [DEV/VBA] - 엑셀 VBA 시작하기

가끔 모니터 가로세로 크기가 궁금할 때가 있습니다. 휴대폰 구입 전 화면 크기를 정확히 알고 싶은 경우도 있지요.

그런데 이상하게 화면의 가로세로 크기에 대하여 mm 로 설명이 나오지 않는 경우가 많죠. 책상과 모니터를 새로 사기 위하여 크기가 궁금하다면 그런데 어디에도 정보가 없다면 모니터를 구입하여 자로 재는 수밖에 없습니다. -_-;;

픽셀 피치라던가 DPI, 몇인치 인지, 가로세로 최대 해상도 뭐 이런 정보들은 많은데요 막상 실제 모니터 LCD 의 가로, 세로의 길이를 mm 나 cm 로 알려주는 곳은 많지 않죠.

이런 분들을 위해 엑셀로 간단하게 계산 하는 수식을 하나 올려드리겠습니다. 

아래와 같이 셀의 정보를 설정합니다.

B4 : 인치 수 입력 (ex. 21)
E4 : 가로 해상도 입력 (1920)
F4 : 세로 해상도 입력 (1080)

가로크기 mm (아래 수식을 원하는 가로 길이 셀에 넣으세요)
= 25.4*(B4*E4)/SQRT((E4*E4)+(F4*F4))

세로크기 mm (아래 수식을 원하는 세로 길이 셀에 넣으세요)
= 25.4*(B4*F4)/SQRT((E4*E4)+(F4*F4))

저는 C4, D4 셀에 구하고자 하는 위치를 정한뒤 위의 수식을 넣었습니다.

1920 1080 해상도의 24 인치 모니터의 LCD 크기는 아래와 같습니다. 

회색칸으로 표시된 곳에 크기가 잘 나왔죠? 

흰색 칸에 위에 말씀드린 정보만 입력하면 가로 세로 크기를 정확하게 보여줍니다.

VBA 가 아닌 엑셀 함수이므로 값을 입력하는 즉시 크기를 알 수 있습니다.

휴대폰의 액정크기를 확인하는데 사용하셔도 됩니다.

해상도와 인치수만 안다면 어떤 LCD에도 다 적용되니 커스텀 LCD 모니터 제작이나 뭐 다른 궁금한 곳에 사용해도 다 됩니다.

유용하게 사용하시기 바랍니다~ ^^

2020/04/03 - [DIY] - LEGO 블럭을 이용한 전자담배 기기 직접 제작하기

2020/05/17 - [DIY] - [재활용] 고장난 LED 바 수리하기 > 화장대 조명 만들기

2020/01/08 - [DEV/c#] - Excel Automate, 엑셀 자동화 프로그램

오늘 포스트에서는 지난 엑셀 자동화 툴의 뎃글로 문의 주신 헤더에 이미지를 VBA 를 이용하여 자동으로 삽입하는 내용을 소개해 드릴까 합니다.

네 당연히 가능하고 약간 시간이 걸리긴 했지만 유용할 듯 하여 VBA 로 작성하여 보았습니다.

나중에 시간내서 제가 만든 자동화 툴에도 구현 되도록 해보겠습니다.

오늘 내용은 어렵지 않은 내용이므로 차근차근 확인하시며 따라 오시면 될 것 같습니다.

먼저 다음의 객체로 VBA 에서 접근하여 설정을 진행하는 것을 기억해 두시기 바랍니다.

Worksheet.PageSetup.LeftHeaderPicture.Filename = "이미지 파일 경로"

Worksheet.PageSetup.LeftHeader = "헤더에 입력할 텍스트"

위와 같이 worksheet 내에 PageSetup 이라는 개체로 진입한 뒤 PageSetup 개체 내에 각종 헤더 위치에 따라 필요한 설정을 진행할 수 있습니다. 

Worksheet.PageSetup.붙일위치/타입

머릿말 및 꼬릿말의 위치 및 타입은 아래와 같은 대상을 정하여 설정을 진행할 수 있습니다. 

그림을 넣을 경우

글자를 넣을 경우

그 중 한군데에 그림을 적용하는 코드 샘플을 보여드리면 아래와 같습니다.

Sub headerInsertTest()

    Dim aSht As Worksheet    
    Set aSht = ActiveSheet ' 현재 활성화된 시트'
    
    With aSht.PageSetup   ' 반복되는 문장으로 코드가 길어지지 않도록 with 사용'
        With .LeftHeaderPicture
            .Filename = "D:\myTestImage_25.png" ' 이미지 경로'
            .Height = 25    ' 이미지의 크기를 입력'
            .Width = 25     ' 이미지의 크기를 입력'
            .ColorType = msoPictureAutomatic
        End With        
        .LeftHeader = "&G" ' 헤더에 그림을 표시함'
        .Zoom = 100        
    End With

End Sub

역시 동일한 방식으로 글자를 넣게 되면 약간만 수정해 주면 됩니다.

Sub headerInsertTest()

    Dim aSht As Worksheet    
    Set aSht = ActiveSheet ' 현재 활성화된 시트'
    
    With aSht.PageSetup   ' 반복되는 문장으로 코드가 길어지지 않도록 with 사용'
        .LeftHeader = "This Header is writen by VBA"                    
    End With

End Sub

위의 방식으로 하면 VBA 를 이용하여 헤더에 그림이나 글씨를 적용하는 것이 가능합니다.

폴더 내 모든 엑셀 파일에 동일한 머릿글 이미지 적용하려면?!?!

먼저 다른 포스트에서 요청 주신바와 같이 특정 폴더에 엑셀 시트가 수백개가 있다고 하셨는데요. 해당 폴더 내 모든 엑셀 파일에 동일한 헤더 이미지를 적용한다고 하면 약간 코드가 복잡해 지겠지요.

아마 아래와 같은 과정이 필요할 것 같습니다.

• 폴더를 지정하면 폴더내의 엑셀 파일의 리스트를 가져온다.
• 가져온 엑셀 파일에 대하여 하나씩 열어서 다음의 과정을 처리한다
  • 엑셀에 있는 모든 시트에 대하여 각각 작업이 진행되도록 한다.
  • 이미지의 경로를 이용하여 헤더에 그림을 삽입한다.
• 처리가 완료된 엑셀 파일은 저장한다.

어떤가요? 감이 오시나요?

제가 넣기 위하여 준비한 이미지는 아래와 같습니다.

테스트용 엑셀 파일은 아래와 같이 만들어 두었습니다. 하위 폴더가 있는 경우 하위 폴더의 엑셀 파일에도 적용이 되어야 하므로 하위 폴더도 만들어 준비를 했습니다. 

한번 작성된 코드를 보시죠.

Sub insertHeadertoAllXLSfiles()
    Dim fso As Object
    Dim fsoFolder As Object
    Dim rootPath As String
    Dim imagePath As String
    
    rootPath = "D:\test\files"
    imagePath = "D:\test\files\diy_dev_design_01.png"
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fsoFolder = fso.GetFolder(rootPath)
    
    getDataRecursive fsoFolder, imagePath
End Sub

Sub getDataRecursive(ByVal baseFolder As Object, ByVal imgName As String)
    
    Dim fso As Object
    Dim tmpSubFolders As Object
    Dim tmpFiles As Object
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set tmpSubFolders = baseFolder.subFolders
    Set tmpFiles = baseFolder.Files
    
    For Each c In tmpFiles
        '여기서 각 엑셀에 실행해야 할 코드를 수행함'

        If LCase(Right(c.Name, 3)) = "xls" Or LCase(Right(c.Name, 4)) = "xlsx" Then ' xls, 또는 xlsx 인 경우'
            '엑셀 파일 열기'
            Dim aBook As Workbook
            Set aBook = Workbooks.Open(c)
            
            '모든시트에 헤더를 넣는 함수 실행'
            For Each tmpsht In aBook.Worksheets
                headerInsertTest tmpsht, imgName
            Next tmpsht
            
            ' 저장하고 닫기'
            aBook.Save
            aBook.Close
        End If
    Next c
    
    For Each d In tmpSubFolders
        
        Dim tmpSub As Object
        Set tmpSubs = fso.GetFolder(d)
        
        ' 하위폴더가 있는경우 하위폴더까지 계속 탐색함 (재귀함수)'
        getDataRecursive tmpSubs, imgName
    Next d
End Sub

Sub headerInsertTest(ByVal sht As Worksheet, ByVal imgPath As String)

    Dim wia         As Object       '이미지 크기 확인을 위한 개체'
    
    Set wia = CreateObject("WIA.ImageFile")
    wia.LoadFile (imgPath)
    
    With sht.PageSetup
        
        With .CenterHeaderPicture       ' 상단 중앙 머릿글에 이미지 넣기'
            .Filename = imgPath
            .Height = wia.Height        ' 이미지의 세로 크기를 입력'
            .Width = wia.Width          ' 이미지의 가로 크기를 입력'
            .ColorType = msoPictureAutomatic
        End With
        .CenterHeader = "&G" ' 헤더에 그림을 표시함'
        .Zoom = 100
    End With
End Sub

좀 길죠? 

여기에는 총 3개의 함수가 들어있습니다.

1. 실제 코드를 실행 시키는 함수 
2. 하위 폴더를 탐색하며 엑셀 파일을 만나면 열어서 시트별로 3번 함수를 실행
3. 워크시트의 머릿글에 이미지를 넣는 함수

이런 순서로 들어있고요. 

1번 함수에서 원하시는 엑셀 파일이 들어있는 경로와 머릿글에 들어갈 이미지의 경로를 지정해 준 뒤 실행하면 됩니다. 제가 실행해 보니 실행 속도가 다소 느리기는 하지만 정상적으로 동작이 잘 되는 군요.

첫번째 함수에서 

    rootPath = "D:\test\xlss"
    imagePath = "D:\test\xlss\diy_header.png"

부분에 이미지의 경로와 엑셀 파일이 들어있는 경로를 바꾸어 주신뒤 동작 시키면 모든 엑셀파일(xls, xlsx)에 아래와 같이 헤더 위치에 원하시는 이미지가 붙게 됩니다. 

참고로 위 스크립트는 머릿글을 붙여야할 문서에 작성하는 것이 아닙니다. 별도 엑셀 파일에 작성한 뒤 동작을 시켜야만 지정한 폴더에 있는 엑셀 파일들을 열어서 자동화 작업이 가능하겠죠.

스크립트가 길어 직접 작성이 어려우실 수 있을 것 같아 작성된 엑셀 파일을 첨부합니다.

해보시고 어려운 부분이 있으면 뎃글 부탁드립니다.

뎃글과 공감은 블로그 작성자에게 큰 힘이 됩니다. 

감사합니다.

2019/06/01 - [DEV/VBA] - 엑셀 VBA 시작하기

2019/08/12 - [DEV/VBA] - [VBA] 문자열 가지고 놀기

2019/12/06 - [DEV/VBA] - [vba] 하위폴더 내의 모든 파일 정보 가져오기

2020/01/08 - [DEV/c#] - Excel Automate, 엑셀 자동화 프로그램

정말 지긋지긋한 마이크로 소프트 입니다. 저는 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] 셀 속성 조정하기 (넓이, 높이, 숨기기, 테두리 등)

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

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

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

이번 포스트에서는 vba 를 이용하여 셀의 속성을 조정하는 방법을 설명 드리겠습니다.

엑셀은 단순한 표의 형식을 취하고 있지만 셀의 간격이나 테두리 등의 속성을 자유롭게 조정할 수 있어 다양한 서식에 사용하기 적합한 훌륭한 워드 프로세서 입니다.

데이터의 타입이나 종류에 따라 현재 엑셀 시트의 표를 자동으로 설정해주는 것이 필요한 경우가 있습니다. 

예를 들면 csv 로 내려받은 단순한 정보들을 시트에 올리고 필요한 정보와 필요하지 않은 정보의 열을 구분하여 표시해 준다던가 아예 불필요한 열이나 행은 숨김 처리를 할 수도 있고요. 주요 행의 테두리 정보를 구분하여 보여주어야 하는 경우도 있겠습니다. 또한 서실을 vba로 만들어 놓으면 어떤 다른 인원에 의해 조정된 폼을 원래 지정한 형식으로 완벽하고 빠르게 설정하는 것도 가능할 것입니다.

이번 포스트에서는 아래의 내용을 다루려고 합니다.

• 행의 높이를 조절하는 방법
• 열의 넓이를 조정하는 방법
• 행 또는 열을 숨기거나 숨김 취소 하는 방법
• 테두리 선을 지정하는 방법
• 테두리 선의 모양을 지정하는 방법

엑셀 VBA 를 이용하여 행(row) 높이를 지정하는 방법

Sub changeCellStyle()

    Dim mySht As Worksheet
    Dim myCell As Range
    
    Set mySht = ActiveSheet
    Set myCell = mySht.Range("B2") ' 원하는 행의 셀을 지정
    
    
    ' 지정한 셀(행)의 높이를 32로 설정한다.
    myCell.RowHeight = 32
    
    '픽셀 기준으로 입력하고 싶다면 0.75 를 곱하세요.
    myCell.RowHeight = 32 * 0.75


End Sub

행의 높이를 Pixel 을 기준으로 지정해야 하는 경우 pixel size * 0.75 를 해주면 됩니다.

예를 들면 이미지를 붙여넣는 등의 작업을 할때 이미지의 크기가 150 이라면 150 * 0.75 의 값을 셀의 크기에 지정을 하면 됩니다. 아래와 같이 말이죠.

엑셀 VBA 를 이용하여 열(column)의 넓이를 지정하는 방법???

여기서 셀의 폭을 지정하는 방법은 갑자기 이야기가 달라집니다. 저는 그래픽 디자이너 이므로 당연히 픽셀 기준으로 셀의 넓이를 정해주고 싶거든요. 여기서 미치고 팔딱 뛰는 상황이 생겨나게 됩니다.

정말 저는 마이크로소프트라는 회사를 이해할 수가 없습니다. 

이부분은 아래 포스팅을 참고해 주세요. 작성하면서도 맨붕이네요.

2020/02/05 - [DEV/VBA] - [VBA] 셀의 넓이를 픽셀로 지정하기 (set column width by pixels)

행 또는 열을 숨기거나 숨김 취소하는 방법

Sub changeCellStyle()
    
    Dim mySht As Worksheet
    Dim myCell As Range
    
    Set mySht = ActiveSheet
    Set myCell = mySht.Range("B2") ' 원하는 행의 셀을 지정'
    
	
    ' 선택한 셀이 있는 모든 행을 숨김처리할 때'
    myCell.EntireRow.Hidden = True
    
    ' 선택한 셀이 있는 모든 행을 숨김취소 할때'
    myCell.EntireRow.Hidden = False

End Sub

숨기거나 해제 하는 것은 간단하죠?

entireRow 라는 개체를 이용하여 전체 행에 대한 지정을 한뒤 hidden 이라는 프라퍼티로 숨기거나 숨기지 않도록 설정 할 수 있습니다.

열 (column) 을 숨기거나 해제 하는 것도 완전히 동일합니다.

아래와 같이 entireRow 대신 entireColumn 으로 사용하면 됩니다.

    myCell.EntireColumn.Hidden = True
    
    myCell.EntireColumn.Hidden = False

테두리의 선을 지정 (해제) 하는 방법

선택한 셀 또는 행의 모든 테두리를 없애는 방법

Sub changeCellStyle()
    
    Dim mySht As Worksheet
    Dim myCell As Range
    
    Set mySht = ActiveSheet
    Set myCell = mySht.Range("B2") ' 원하는 행의 셀을 지정
    
    ' 선택한 셀의 테두리를 없애는 방법 '
    myCell.Borders.LineStyle = xlNone
    
    '선택한 행전체의 테두리를 없애는 방법
    myCell.EntireRow.Borders.LineStyle = xlNone
    
    
End Sub

선택한 셀의 테두리를 지정하는 방법

Sub changeCellStyle()
    
    Dim mySht As Worksheet
    Dim myCell As Range
    
    Set mySht = ActiveSheet
    Set myCell = mySht.Range("B2") ' 원하는 행의 셀을 지정
    
    '선택한 행전체의 테두리를 없애는 방법
    myCell.EntireRow.Borders.LineStyle = xlNone
    
    
    
    ' 선택한 셀의 왼쪽 테두리만 보통의 실선으로 보여주는 방법 '
    myCell.Borders(xlEdgeLeft).LineStyle = xlContinuous
    
    ' 선택한 셀의 오른쪽 테두리만 보통의 실선으로 보여주는 방법 '
    myCell.Borders(xlEdgeRight).LineStyle = xlContinuous
    
    ' 선택한 셀의 위쪽 테두리만 보통의 실선으로 보여주는 방법 '
    myCell.Borders(xlEdgeTop).LineStyle = xlContinuous
    
    ' 선택한 셀의 아래쪽 테두리만 보통의 실선으로 보여주는 방법 '
    myCell.Borders(xlEdgeBottom).LineStyle = xlContinuous
    
   ' 선택한 셀의 테두리를 보통의 실선으로 보여주는 방법 '
    myCell.Borders.LineStyle = xlContinuous
    
End Sub

제가 방향별로 지정하는 방법을 적어 두었으니 필요한 내용을 추려다가 사용하시면 됩니다.

테두리의 선의 속성을 변경하는 하는 방법

만약 아래쪽 선만 두껍게 지정하고 싶다면?

' 선택한 셀의 아래쪽 테두리만 두꺼운 실선으로 보여주는 방법 '
    myCell.Borders(xlEdgeBottom).Weight = xlThick
    myCell.Borders(xlEdgeBottom).LineStyle = xlContinuous

요렇게 해주시면 됩니다.

참고로 라인 스타일을 xlContinuous 말고 점선이나 다양한 스타일로 적용하는 것도 가능합니다.

• xlContinuous 연속선입니다.
• xlDash 파선입니다.
• xlDashDot 교대로 연결된 파선과 점선입니다.
• xlDashDotDot 파선과 두 개의 점선입니다.
• xlDot 점선입니다.
• xlDouble 이중선입니다.
• xlLineStyleNone 선이 없습니다.
• xlSlantDashDot 기울어진 파선입니다.

위에 소개해드린 여러가지 속성들을 조합하면 하나의 완성된 폼을 자동으로 생성하는 것이 가능합니다.

한번 짜 놓을 때는 귀찮을 수 있지만 수도없이 계속해서 폼을 맞추어주어야 하는 어떤 상황이라면 자동화 개발을 하는것이 정신건강이나 육체건강에 좋겠죠.

셀의 색상을 채워넣는 방법은 이전 포스트를 참고해 주세요.

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

이렇게 간단하게 vba 를 이용하여 셀의 모양을 조정하는 것이 가능합니다.

어렵게 생각치 마시고 위에 설명드릴 방법을 다양하게 응용하여 자신에게 맞는 자동화 툴을 구성하시기 바랍니다.

2020/09/04 - [DEV/VBA] - [vba] 엑셀 이미지 리스트로 일괄 다운로드 받기

2020/06/22 - [DEV/VBA] - [vba] 초등학교 연산 자동 문제집 - 곱셈 추가

2019/12/06 - [DEV/VBA] - [vba] 하위폴더 내의 모든 파일 정보 가져오기

감사합니다.

어떤 엑셀 페이지에 늘 사용하는 스크립트를 작성하였으나 매번 실행하기 위하여 vba 창을 눌러 F5를 누르는 것은 사실 본인이 직접 스크립트 코드를 개발하는 입장이라면 조금도 번거로울 것이 없는 것이지만 누군가에게 스크립트를 작성하여 엑셀 파일을 건내 주는 경우라면, 실제 사용하는 사람이 개발과 거리가 있는 사람이라면 더더욱 이와 같은 방법으로 스크립트를 실행시키는 것은 불편한 방법이 됩니다.

게다가 스크립트가 딱 하나가 아니고 어떤 스크립트는 정렬을 해주고, 어떤 스크립트는 불필요한 값을 숨겨주고, 또 어떤 스크립트는 데이터를 새로 읽어 들여 전체 시트를 리프레쉬 하는 등의 여러개의 기능을 개발한 상태라면 vba 창에서 실제 해당 스크립트 위치에 마우스 커서를 가져다 놓는 등의 추가 행위가 필요합니다.

이럴때 버튼을 이용하여 특정 스크립트 (엑셀에서는 매크로라고 합니다) 를 연결하게 되면 지정된 버튼을 누를때 마다 필요한 스크립트가 동작하게 됩니다. 

말하자면 간단한 툴이 만들어 지는 것이죠. 

사실 엑셀의 각 셀은 함수나 계산식을 포함할 수 있으므로 왠만한 복잡한 것들은 워크시트의 함수와 계산식으로 처리가 가능하지만 이미지를 불러온다거나 어떤 데이터의 속성을 알아내거나 약간은 복잡한 관계의 셀 값들을 이용하여 결과값을 얻는다든가 하는 등의 일은 워크시트 함수로는 조금 어려운 부분이 있습니다. 그래서 매크로 스크립트를 작성하는 것이죠. 이런 기능기능이 모여 마치 어떤 툴처럼 제작이 가능합니다.

영업사원들에게는 견적서 프로그램을 만드는 것이 가능할 것이고 세무, 회계부서에서는 각종 세금 관계 계산을 위한 처리가 가능할것입니다. 

오늘은 그렇게 만들어진 스크립트 들을 버튼을 이용하여 동작하도록 하는 방법을 소개해 드릴까 합니다.

먼저 제가 초등학교 저학년 학생들을 위한 간단한 덧셈 연산을 자동으로 생성하는 엑셀파일을 만든적이 있습니다. 

https://diy-dev-design.tistory.com/41?category=791049

해당 파일을 보면 아래와 같은 화면을 볼 수 있는데요. 우측의 버튼을 누를 때 마다 버튼의 텍스트에 해당하는 기능으로 좌측 값이 자동으로 생성이 됩니다. 위 링크된 페이지에 가면 실제 엑셀 파일을 받으실 수 있으니 해당 파일을 받으셔서 참고해보셔도 좋을 것 같습니다.

우선 단추(버튼)르르 이용한 동작을 확인할 간단한 스크립트를 작성해 보겠습니다.

• 첫번째 스크립트로 셀 A1~C1 번에 숫자 1, 2, 3 을 넣어 보겠습니다.
  • 만약 이미 숫자가 들어있다면 현재 들어있는 숫자에 각각 1, 2, 3을 더하도록 해보죠.
• 두번째 스크립트는 A2 ~ C2 에 A1 + A1, B1 + B1, C1 + C1 까지의 값을 넣는 동작을
• 세번째 스크립트는 A3 ~ C3 에 A1 * A2, B1 * B2, C1 * C2 의 값을 넣는 동작을
• 네번째 스크립트는 A4 ~ C4 에 C1 ~ C3 의 값을 이용하여 셀의 색상을 채워 넣어 보겠습니다.
  • 컬러를 지정하기 위한 숫자는 RGB 로 각각 0~255만 가능하므로 255 보다 크면 255로 설정 하겠습니다.
  • 그리고 255보다 값이 커진다면 첫번째 행의 값을 낮은 값으로 바꾸어 보겠습니다.

스크립트를 작성해 보겠습니다.

먼저 엑셀을 열고 VBA 창 (alt + F11) 을 실행한 뒤 아래와 같이 입력해 보겠습니다.

Sub function_a()
    
    'cells(rowIndex, columnIndex) 와 같은 형식으로 셀을 지정하는 방법'
    If Len(Cells(1, 1)) Then
        Cells(1, 1).Value = Cells(1, 1).Value + 1
    Else
        Cells(1, 1).Value = 1
    End If
    
    If Len(Cells(1, 2)) Then
        Cells(1, 2).Value = Cells(1, 2).Value + 2
    Else
        Cells(1, 2).Value = 2
    End If
    
    If Len(Cells(1, 3)) Then
        Cells(1, 3).Value = Cells(1, 3).Value + 3
    Else
        Cells(1, 3).Value = 3
    End If

End Sub

Sub function_b()
    
    ' Range(name) 의 형태로 셀을 지정하는 방법'
    
    Range("A2").Value = Range("A1").Value * 2
    Range("B2").Value = Range("B1").Value * 2
    Range("C2").Value = Range("C1").Value * 2
    
End Sub

Sub function_c()
    
    ' range 라는 변수를 설정하여 정의하는 방법'
    
    Dim a3, b3, c3
    
    Set a3 = Range("A3")
    Set b3 = Range("B3")
    Set c3 = Range("C3")
    
    a3.Value = a3.Offset(-2, 0).Value * a3.Offset(-1, 0).Value
    b3.Value = b3.Offset(-2, 0).Value * b3.Offset(-1, 0).Value
    c3.Value = c3.Offset(-2, 0).Value * c3.Offset(-1, 0).Value
    
End Sub

Sub function_d()

    Dim a3, b3, c3, rngAll
    
    Set a3 = Range("A3")
    Set b3 = Range("B3")
    Set c3 = Range("C3")
    
    Set rngAll = Range("A4", "C4")
    
    If a3.Value > 255 Then
        a3.Value = 255
        a3.Offset(-2, 0).Value = 1
    End If
    
    If b3.Value > 255 Then
        b3.Value = 255
        b3.Offset(-2, 0).Value = 2
    End If
    
    If c3.Value > 255 Then
        c3.Value = 255
        c3.Offset(-2, 0).Value = 3
    End If
    
    rngAll.Interior.Color = RGB(a3.Value, b3.Value, c3.Value)
    
End Sub

Sub ~ End Sub 까지가 하나의 매크로 스크립트가 되겠습니다. 총 4개의 스크립트가 작성이 되었는데요. 내용은 위에서 기술한 내용데로 작성하였으며 각각의 스크립트에는 셀을 지정하는 방법을 달리하여 작성해 보았습니다. 여러분들도 직접 타이핑 해보시면서 어떤방법으로 셀을 지정하는 것이 편리한지 경험해보시는 것이 도움이 되실 것 같습니다.

끝으로 rngAll 이라는 Range 개체를 만들었는요 현재 시트의 4번째 행의 A~C 열을 한꺼번에 설정해주었습니다. 그리고 한꺼번에 해당 셀의 색상을 변경을 하는 코드를 넣어 봤습니다.

자 스크립트를 모두 작성하였으면 이제 단추를 만들어 보아야 겠죠? 

그런데 엑셀의 기본 기능 중에는 단추가 보이지 않습니다. 리본 메뉴가 만들어 지면서 사용하지 않는 기능들은 꺼내져 있지 않기 때문인데요. 사용하기 위하여 꺼내 보겠습니다. 처음 한번만 하시면 됩니다.

파일 메뉴로 가셔서 아래와 같은 창이 나오면 제일 아래 옵션을 선택합니다.

그럼 옵션창이 팝업으로 뜨게 됩니다.

팝업으로 뜬 창에서 '리본 사용자 지정' 텝으로 이동해보면 아래와 같이 아주 많은 기능이 나열된 것을 볼 수 있습니다.

위 그림에서 좌측은 엑셀이 가지고 있는 모든 기능을 보여주고 있고 우측은 현재 사용자의 리본메뉴에 추가되어 있는 기능들이라고 생각하시면 되겠습니다.

아래 1번의 드롭다운 메뉴에 리본에 없는 메뉴를 선택하신 뒤 리스트에서 2번 '단추(양식컨트롤)' 이 바로 우리가 필요한 단추를 넣는 기능입니다. 단추라고 하면 좀 이상한데요, 익숙한 용어로는 버튼입니다. 버튼.

우측은 해당 기능을 넣을 리본 메뉴라고 했는데요, 저는 삽입 이라는 탭에 넣을 계획 입니다. 이미 있는 그룹에 넣는 것은 불가능하고 새로운 그룹을 만들어서 넣어야 하기 때문에 3번 삽입 을 선택하여 메뉴를 확장 시킨 뒤 아래 '새 그룹' 이라는 버튼을 이용하여 새로운 그룹을 추가해 줍니다.

새로 추가한 그룹의 이름을 바꾸어 볼 차례 입니다. 저는 제 블로그 이름인 "DIY_DEV_DESIGN" 라고 변경하였습니다. 아래 이름 바꾸기라는 버튼을 이용하여 변경하시면 됩니다.

이제 단추를 선택한뒤 가운데에 있는 "추가>>" 를 이용하여 방금 만든 그룹에 추가를 해 보겠습니다.

자 이제 단추를 추가하는 기능은 완료가 되었습니다. 확인 버튼을 눌러 옵션 창을 닫고 엑셀의 리본메뉴를 살펴보면 아래와 같이 단추가 추가되어 있을 것입니다.

이제 해당 버튼을 누르게 되면 엑셀 시트에 단추를 만들수 있게 됩니다.

만드는 방법은 아래와 같습니다. 

1. 단추 버튼을 누른다
2. 원하는 위치에 마우스를 클릭한뒤 드래그 하여 필요한 크기가 되면 마우스 버튼을 뗀다
3. 팝업으로 나오는 매크로 선택창에서 아까 만들어둔 스크립트를 선택해 준다.
4. 이미 만들어진 단추의 크기나 위치, 레이블을 수정하기 위하여는 마우스 우클릭으로 선택해주면 됩니다.

총 4개의 스크립트를 연결할 것이므로 4개의 스크립트를 차례로 연결해 보겠습니다

이제 위의 버튼부터 차례로 눌러 볼까요?

네 잘 동작되네요.

만약 스크립트를 수정해야 하면 해당 버튼을 마우스 우클릭한 뒤 "매크로 지정" 기능으로 이동하여 설정되어 있는 매크로를 다른 메뉴로 변경하거나 선택되어 있는 매크로에서 "편집" 버튼을 눌러주게 되면 해당 매크로 스크립트를 바로 편집할 수 있는 비주얼 베이직에디터가 나타나게 됩니다.

자 이렇게 해서 엑셀에 버튼을 추가하고 스크립트를 연결하는 방법을 알아 보았습니다.

설정창에서 단추 기능을 꺼내는 단계부터 처음하시는 분은 조금 복잡하다고 생각하실 수도 있겠는데요. 막상 해보시면 정말 간단한 작업이고 어렵지 않습니다.

또한 이렇게 단추로 만들어진 스크립트는 매우 실행하기 편리하고 직관적이죠. 

자 여러분도 이제 엑셀을 이용하여 툴을 만들어 보시는 겁니다.

도전해 보시지요.