좌표를 이용해 엑셀에 도면 위치 표시하기(2)

3. 여러 개의 좌표를 도면에 일괄 표시하기

 

좌표를 이용해서 도면 위치 표시하기2.xlsm

0.02MB

 

이번에는 엑셀에 아래와 같이 가상의 좌표를 표시하고,

(참고 사항) - 도면의 위치는 1000단위로 간격을 두었으며, 숫자는 #, 표시형식을 사용해서 천단위 위만 표시되도록 했습니다. 예를 들어 첫번째 N의 좌표는 723,000입니다. - 도면의 좌표는 왼쪽에서 오른쪽, 위쪽에서 아래로  내림차순으로 표시되어 있습니다.

 

 

그 안에 아래의 좌표에 해당하는 도면의 위치를 표시해보겠습니다.

 

가. 작성 방법

① 왼쪽과, 위쪽, 너비와 높이가 필요한데,

좌표가 정확히 구간과 일치하는 것이 아니므로

좌표보다 큰 값의 위치에 표시하도록 하겠습니다.

 

② 도면 색은 ColorScheme를 사용하는데, 1은 흰색이므로 제외하고 일련번호를 이용해 표시하겠습니다.

 

나. 코드

Dim i As Long, BottomRow As Long, topRow As Long, LeftCol As Long, RightCol As Long
Dim a As Double, b As Double, e As Double, f As Double
Dim c As Range, eastRange As Range, northRange As Range
Dim shp As Shape

Sub 도면표시하기()
    
    Application.ScreenUpdating = False
  
    Sheets(4).Select
    
    Set eastRange = Range("b3:b" & Cells(Rows.Count, "b").End(xlUp).Row)
    Set northRange = Range(Range("c2"), Cells(2, Columns.Count).End(xlToLeft))
    
    For i = 2 To 21
        BottomRow = 작은값행(Sheets(3).Cells(i, 2).Value, eastRange)
        topRow = 작은값행(Sheets(3).Cells(i, 3).Value, eastRange)
        RightCol = 작은값열(Sheets(3).Cells(i, 4).Value, northRange) + 1
        LeftCol = 작은값열(Sheets(3).Cells(i, 5).Value, northRange) - 1
        
        Set shp = ActiveSheet.Shapes.AddShape(msoShapeRectangle, Cells(topRow, LeftCol).Left, Cells(topRow, LeftCol).Top, _
            Cells(topRow, RightCol).Left - Cells(topRow, LeftCol).Left, Cells(BottomRow, RightCol).Top - Cells(topRow, RightCol).Top)
        shp.TextFrame2.TextRange.Text = Sheets(2).Range("a" & i)
        shp.TextFrame2.TextRange.Font.Size = 15
        If i = 9 Then i = i + 1
        shp.Fill.ForeColor.SchemeColor = i
    Next
    Application.ScreenUpdating = True
End Sub

Function 작은값행(longi_value, rngA)
    For Each c In rngA
        If longi_value > c.Value Then
            작은값행 = c.Row
            Exit For
        End If
    Next
End Function

Function 작은값열(lati_value, rngA)
    For Each c In rngA
        If lati_value > c.Value Then
            작은값열 = c.Column
            Exit For
        End If
    Next
End Function

Sub 도형지우기()
    Sheets(4).Select
    For Each shp In ActiveSheet.Shapes
        If Left(shp.Name, 6) <> "Button" Then
            shp.Delete
        End If
    Next
End Sub

 

 

다. 코드에 대한 설명

 

Sub 프로시저가 도면표시하기와 도면지우기 2개가 있고,

Function 프로시저가 작은값행과 작은값열 2개가 있는데,

Funciton은 도면표시하기 프로시저에서 사용하는 것입니다.

 

그리고, 변수는 프로시저마다 공통으로 사용되는 것이 있으므로 위로 빼내서 코드를 간결하게 했습니다.

 

(1) 도면 표시하기

1편에서는 도형 하나만을 그려봤는데,

이번에는 엑셀에 2행부터 21행까지 있는ㄷ 데이터를 가지고 위에서 만든 좌표안에 표시해보겠습니다.

 

따라서, For ~ Next 반복문을 사용해야 하고, 그 안에서 좌표값을 불러들여 좌표에서 어디에 해당하는지 위치를 찾아서 그곳에 사각형을 그려야 합니다.

 

(가) 화면 갱신 제어

Application.ScreenUpdating = False ..... Application.ScreenUpdating = True

 

Application.ScreenUpdating = False는 화면 갱신을 하지 말라는 것이고,

Application.ScreenUpdating = True는 화면 갱신을 하라는 것입니다.

 

(나) 시트 선택

Sheets(4).Select

 

시트4를 선택하는 것입니다.

그런데, 매크로 작성할 때  화면 전환이 일어나서 처리 시간이 길어지게 되므로 가능하면 Select문을 사용하지 말라고 합니다. 그래도 필요할 때는 해야죠.

 

(다) 범위 변수에 범위 저장

Set eastRange = Range("b3:b" & Cells(Rows.Count, "b").End(xlUp).Row)     Set northRange = Range(Range("c2"), Cells(2, Columns.Count).End(xlToLeft))

 

① eastRange 범위 변수 지정

eastRange, 다시 말해 동경 좌표에 해당하는 범위는 B3부터 B열의 맨 아래에서 위로 올라왔을 때 걸리는 행까지로 정했습니다. 범위 변수를 지정할 때는 Set 명령어를 사용합니다.

 

Cells(Rows.Count, "b")에서 Rows.Count는 행수이므로, MIcrosoft 365에서는 맨 아래 행 1048576이고, B열이므로 B1048576셀이 됩니다.

 

End(xlUp)은 위로 끝까지 올라가는 것으로 Ctrl + ↑키를 누르는 것과 같으며,

.Row는 올라갔을 때 만나는 셀의 행수이므로 243이 됩니다.

 

② northRange 범위 변수 지정

Set northRange = Range(Range("c2"), Cells(2, Columns.Count).End(xlToLeft))

 

이번에는 Range안에 Range를 넣었는데, 이렇게 하면 시작 셀과 끝 셀만을 범위로 지정하는 것이 아니라 연속된 범위를 지정합니다.

 

Range("c2")는 C2셀이고,

 

Cells(2, Columns.Count).End(xlToLeft)는 2행, 맨 오른쪽 칼럼에서 Ctrl + ←키를 눌렀을 때 만나는 셀이므로 PJ2셀이 됩니다. 따라서, 위 명령문을 실행하면 northRange 변수에 C2:PJ2셀 범위가 저장됩니다.

 

따라서, 위 명령문을 실행하면 northRange 변수에 C2:PJ2셀 범위가 저장됩니다.

 

For문 왼쪽을 마우스로 클릭해서 중단점을 설정하고, F5키를 눌러서 실행하면 중단점에서 멈추는데,

 

직접 실행창에 ? northrange.address라고 입력하고 엔터키를 누르면

$C$2:$PJ$2라고 반환됩니다.

 

 

글이 길어져서 For ~ Next 반복문부터는 나누서 설명하겠습니다.

 

완성된 파일을 올리니 설명과 무관하게 실행해보실 수 있습니다.

 

좌표를 이용해서 도면 위치 표시하기2(완성).xlsm

0.04MB