엑셀과 VBA의 Offset 함수가 다르다. (2)

나. VBA의 Offset 함수

 

offset 비교1.xlsx

0.01MB

 

 

(1) 떨어진 셀 지정

 

VBA를 실행하기 위해서는

먼저 개발도구 탭이 활성화되어 있어야 합니다.

 ※ 파일 - 옵션을 누른 후 Excel 옵션 창의 리본 사용자 지정에서 오른쪽 아래의 개발 도구 탭 왼쪽에 체크

 

(가) 코드 작성

떨어진 셀을 지정하기 위해서는

① 개발도구 - Visual Basic을 눌러 Visual Basic Editor를 연 다음

삽입 메뉴에서 모듈을 눌러 모듈을 추가하고,

 

② 오른쪽 에디터 창에  sub offset1이라고 입력하고 엔터키를 누릅니다.

그러면 아래와 같이 괄호가 추가되고, 한 줄 다음에 End Sub로 마무리됩니다.

 

③ 이제 코드를 작성하고,

Sub offset1()
    Range("a1") = Range("c9").Offset(3, 1)
End Sub

 

④ 실행 메뉴에서 'Sub / 사용자 정의 폼 실행 F5'를 누르거나,

위쪽 표준 도구 모음에서 오른쪽 세모 모양 아이콘을 눌러 실행합니다.

 

⑤ 그리고, 표준 도구 모음의 엑셀 아이콘을 누르면 엑셀로 돌아가므로

엑셀에서 A1셀의 값을 확인해 보면 C9셀에서 아래로 3칸, 오른쪽으로 한 칸 이동한 셀의 값 92가 구해졌습니다.

그런데 수식이 아니라 값으로 구해졌습니다. 수식으로 입력하는 것은 아래에서 알아보겠습니다.

 

(나) 코드 설명

- Range("a1") : A1셀을 가리킵니다. 엑셀에서는 A1이라고 바로 쓰면 되지만, VBA에서 Range 다음에 셀 주소 또는 범위를 입력해야 합니다.

 

- Range("c9").Offset(3, 1) : C9셀에서 아래로 3칸, 오른쪽으로 1칸 이동하라는 의미입니다. 따라서, D12셀이 되는 것이고, D12셀의 값 92를 반환하는 것입니다.

 

(다) 수식을 입력하도록 코드 수정 1

 

Sub 모듈을 offset2라는 이름으로 하나 만든 다음

아래와 같이 코드를 작성한 후 실행하면

Sub offset2()
    Range("a1") = "=offset(c9,3, 1)"
End Sub

 

A1셀에 수식 =offset(c9,3, 1)이 입력됩니다. 

 

 

=offset(c9,3,1)라고 수식을 입력해야 하므로

큰 따옴표 안에 =offset(c9,3,1)을 입력한 것입니다.

 

(라) 수식을 입력하도록 코드 수정 2

c9 대신에 Range("c9").address라고 Address 속성을 이용해서 C9 셀의 주소를 입력할 수 있으며,

그다음에 ",3,1)"을 입력하면 됩니다. 문자열을 연결해야 하므로 & 연산자를 이용했습니다.

Sub offset3()
    Range("a1") = "=offset(" & Range("c9").Address & ",3, 1)"
End Sub

 

기본 값이 절대 참조 형식이라 $C$9로 구해집니다.

 

절대 참조 형식을 상대 참조 형식으로 바꾸려면 

Address 다음에 괄호를 열고 0,0이라고 입력한 다음 괄호를 닫으면 됩니다.

1이 절대 참조, 0이 상대 참조형식이고, 첫 번째가 행, 두 번째가 열입니다.

 

Address 다음에 (를 입력하면 아래와 같이 인수에 대한 설명이 나옵니다.

 

모두 대괄호 안에 있기 때문에 입력하지 않아도 되며, 입력하지 않으면 기본 값이 적용됩니다.

첫 번째 인수가 RowAbsolute, 두 번째 인수가 ColumnAbsolute입을 알 수 있습니다.

 

수식이 =OFFSET(C9,3, 1)라고 절대 참조 형식을 지정하는 $표시가 사라졌습니다.

 

매크로 사용 통합문서이므로 확장자를 xlsm으로 바꿔서 저장해야 합니다.

 

offset 비교(완성2).xlsm

0.01MB