상대주소와 절대주소 맘대로 바꾸기

Step 1: 어떤 결과를 원하는가

기본적으로 "수작업으로 가능한 것이면 자동화 할 수 있다"라고 생각하시면 아마도 틀림이 없을 것입니다. 버튼을 누르시면 대화상자가 나타나는데 원하는 주소형태를 지정해 보세요.

아래와 같은 표의 수식(예: =$C24*1.03)의 주소 형태를 선택한 범위 전체에서 한꺼번에 바꿔 보겠습니다.

이번 강좌에서는 이것을 만들어 보도록 하지요.

Step 2: 유저폼 만들기

(1) VB Editor 상태에서 아래의 그림과 같은 유저폼을 하나 삽입합니다. 아시겠지만, 1개의 RefEdit 컨트롤과 Lagel, 4개의 캡션버튼, 1개의 Frame, 2개의 Command Button 등이 사용되었습니다.

아이엑셀러

Step 3: 초기값 정의하기

(2) 컨트롤이 없는 빈 공간을 더블클릭 한 다음, 이벤트 선택 목록상자에서 Initialize 이벤트를 선택하고 코드를 작성합니다.

Private Sub UserForm_Initialize()
    Range("Start").Select
    RefEdit1.Text = Selection.CurrentRegion.Address
    '//이것은 유저폼이 호출될 때, RefEdit1 컨트롤의 초기값을 설정하기 위한 것입니다.
    '//즉, 현재 셀이 위치한 주변 영역(CurrentRegion)의 주소를 RefEdit 컨트롤에 표시합니다.
End Sub

Step 4: OK 버튼 코드 작성하기

(3) Shift + <F7>키를 눌러 대화상자를 다시 불러온 다음, OK 버튼을 더블클릭하고 해당 코드를 작성합니다.

Private Sub btnOK_Click()
    Dim i As Integer
    Dim Msg As String
    Dim rngCell As Range
    Dim rngTarget As Range
    On Error Resume Next
    Set rngTarget = Range(RefEdit1.Text)
    '//변수들을 지정하고, RefEdit1 컨트롤에 들어있는 (영역)값을 rngTarget 오브젝트 변수에
    '//담습니다.

    If Err <> 0 Then
        Msg = "유효하지 않은 범위입니다." & vbCr
        Msg = Msg & "범위를 다시 선택하세요"
        MsgBox Msg, , "범위 선택 오류//Exceller"
        RefEdit1.SetFocus
        Exit Sub
        '//Range 오브젝트가 아닌 것을 선택하거나 하는 등의 에러가 발생했을 경우, 범위를
        '//다시 지정하도록 합니다.

    End If

    On Error GoTo 0
    Select Case True
    '//4개의 옵션 버튼 중에서 어떤 것이 선택되었는지 확인해서 셀의 주소형태를 그에 맞게
    '//바꾸어 줍니다.

        Case optRel
        '//선택된 영역의 주소를 상대주소로 바꾸어 주는 부분입니다. ConvertFormula 메서드를
        '//사용합니다.

            For Each rngCell In rngTarget
                If rngCell.HasFormula Then rngCell.Formula = Application.ConvertFormula(Formula:=rngCell.Formula, _
                    fromreferencestyle:=xlA1, toreferencestyle:=xlA1, toabsolute:=xlRelative)
            Next rngCell
            MsgBox "모두 상대주소 형태로 바꾸었습니다", , "작업 완료//Exceller"

        Case optAbs
        '//절대주소로 바꾸어 주는 부분입니다. Toabsolute 요소를 xlAbsolute로 지정해 주면
        '//됩니다.
            For Each rngCell In rngTarget
                If rngCell.HasFormula Then rngCell.Formula = Application.ConvertFormula(Formula:=rngCell.Formula, _
                    fromreferencestyle:=xlA1, toreferencestyle:=xlA1, toabsolute:=xlAbsolute)
            Next rngCell
            MsgBox "모두 절대주소 형태로 바꾸었습니다", , "작업 완료//Exceller"

        Case optCol
        '//혼합주소(행방향은 절대주소, 열방향은 상대주소)로 바꾸어 주기 위한 부분입니다.

            For Each rngCell In rngTarget
                If rngCell.HasFormula Then rngCell.Formula = Application.ConvertFormula(Formula:=rngCell.Formula, _
                    fromreferencestyle:=xlA1, toreferencestyle:=xlA1, toabsolute:=xlAbsRowRelColumn)
            Next rngCell
            MsgBox "모두 혼합주소 형태(A$1)로 바꾸었습니다", , "작업 완료//Exceller"

        Case optRow
        '//혼합주소(행방향은 상대주소, 열방향은 절대주소)로 바꾸어 주기 위한 부분입니다.

            For Each rngCell In rngTarget
                If rngCell.HasFormula Then rngCell.Formula = Application.ConvertFormula(Formula:=rngCell.Formula, _
                    fromreferencestyle:=xlA1, toreferencestyle:=xlA1, toabsolute:=xlRelRowAbsColumn)
            Next rngCell
            MsgBox "모두 혼합주소 형태($A1)로 바꾸었습니다", , "작업 완료//Exceller"
    End Select

End Sub

Close 버튼에는 유저폼을 닫는 코드를 연결합니다.

Private Sub btnClose_Click()
    Unload Me
End Sub

마무리

생활하다 보면, "혹시 이러이러한 일이 Excel 또는 VBA를 통해서 가능할까요?" 이러한 질문을 자주 받습니다.

Exceller에게 있어 작업 가능여부 판단기준은 "손으로 작업이 가능한가" 여부입니다. 그래서 Exceller가 주로 하는 답변은, "그러면 그것을 수작업을 통해서는 어떤 식으로 해결하고 계십니까?" 라고 제일 먼저 물어봅니다.

명확하고, 구체화된 문제점은 (특별한 경우를 제외하고는) 반드시 해결가능합니다. 마찬가지로 아무리 쉬운 문제라도 이를 구체화시키지 않으면(또는 못하면) 그만큼 해결이 어려워 집니다. 막연한 문제는 막연한 해결책을 낳을 뿐이지요.

다음 시간에 또…

정리 — 주소 형태 변환 핵심

자주 묻는 질문 (FAQ)

마치며

명확하고 구체화된 문제는 반드시 해결할 수 있으며, 수작업 과정이 곧 자동화의 출발점입니다.