엑셀 매크로(Excel Macro) 만들기

엑셀을 사용하다 보면 단순 작업을 반복해야 하는 경우가 있습니다. 매크로 기능을 사용하면 여러 번 반복해야 하는 작업을 한꺼번에 수행하여 작업 시간을 단축할 수 있습니다.

엑셀 매크로 기능의 간단한 사용방법을 알아보겠습니다.

보기 -> 매크로 -> 매크로 기록 메뉴를 선택합니다.

매크로 이름과 바로 가기 키를 지정해 준 후 확인 버튼을 누릅니다. 바로 가기 키는 해당 매크로를 실행시켜주는 단축키입니다.(매크로 별 고유 단축키 지정)

매크로 기록 중인 상태에서는 작업하는 모든 내용이 기록됩니다. 폰트의 색상을 빨강으로 변경해보겠습니다. 

(매크로 기록 전 셀을 선택한 이후 매크로 기록을 실행하여야 합니다. 매크로 기록 이후 셀을 선택하면 특정 셀을 선택한 동작까지 매크로에 기록됩니다.)

매크로 기록을 중지하려면 하단에 중지 버튼, 또는 보기 -> 매크로 -> 기록 중지 선택

보기 -> 매크로 -> 매크로 보기 메뉴(Alt + F8)에서 저장된 매크로를 확인할 수 있습니다. 이후 편집 및 삭제 또한 가능합니다.

편집 실행 시 Microsoft Visual Basic가 실행되며 매크로 스크립트를 편집할 수 있습니다.

매크로 동작 테스트입니다. 문자열 선택 후 앞에서 생성한 매크로를 실행시켜보겠습니다.(매크로 단축키 실행)

매크로가 정상 실행되어 폰트와 색상이 변경되었습니다.

찾기 및 바꾸기(Replace) 기능을 매크로에 적용 가능합니다. prefix 항목을 subnet 값으로 변경해보겠습니다. 

What 항목에 찾을 내용, Replacement에 바꿀 내용을 입력하여 매크로를 생성하였습니다.

찾기 및 바꾸기를 여러 번 실행해야 할 작업도, 매크로 실행으로 Replace가 한 번에 완료되었습니다.

*Prefix -> Subnet Replace

Sub 매크로1()

    Cells.Replace What:="/32", Replacement:="255.255.255.255", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="/31", Replacement:="255.255.255.254", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="/30", Replacement:="255.255.255.252", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="/29", Replacement:="255.255.255.248", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="/28", Replacement:="255.255.255.240", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="/27", Replacement:="255.255.255.224", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="/26", Replacement:="255.255.255.192", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="/25", Replacement:="255.255.255.128", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="/24", Replacement:="255.255.255.0", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="/23", Replacement:="255.255.254.0", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="/22", Replacement:="255.255.252.0", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="/21", Replacement:="255.255.248.0", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="/20", Replacement:="255.255.240.0", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="/19", Replacement:="255.255.224.0", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="/18", Replacement:="255.255.192.0", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="/17", Replacement:="255.255.128.0", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="/16", Replacement:="255.255.0.0", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="/15", Replacement:="255.254.0.0", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="/14", Replacement:="255.252.0.0", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="/13", Replacement:="255.248.0.0", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="/12", Replacement:="255.240.0.0", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="/11", Replacement:="255.224.0.0", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="/10", Replacement:="255.192.0.0", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="/9", Replacement:="255.128.0.0", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="/8", Replacement:="255.0.0.0", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

*Subnet -> Prefix Replace

Sub 매크로2()

    Cells.Replace What:="255.255.255.255", Replacement:="/32", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="255.255.255.254", Replacement:="/31", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="255.255.255.252", Replacement:="/30", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="255.255.255.248", Replacement:="/29", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="255.255.255.240", Replacement:="/28", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="255.255.255.224", Replacement:="/27", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="255.255.255.192", Replacement:="/26", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="255.255.255.128", Replacement:="/25", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="255.255.255.0", Replacement:="/24", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="255.255.254.0", Replacement:="/23", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="255.255.252.0", Replacement:="/22", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="255.255.248.0", Replacement:="/21", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="255.255.240.0", Replacement:="/20", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="255.255.224.0", Replacement:="/19", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="255.255.192.0", Replacement:="/18", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="255.255.128.0", Replacement:="/17", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="255.255.0.0", Replacement:="/16", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="255.254.0.0", Replacement:="/15", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="255.252.0.0", Replacement:="/14", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="255.248.0.0", Replacement:="/13", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="255.240.0.0", Replacement:="/12", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="255.224.0.0", Replacement:="/11", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="255.192.0.0", Replacement:="/10", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="255.128.0.0", Replacement:="/9", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Cells.Replace What:="255.0.0.0", Replacement:="/8", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub