엑셀(Excel) VBA – 선택한 셀시트명, 파일명, 위치를 알아오는 사용자정의함수

엑셀(Excel) VBA – 선택한 셀시트명, 파일명, 위치를 알아오는 사용자정의함수

 

환경 : Microsoft Excel 2010

 

이번에는 사용자 정의함수를 만들건데 셀을 선택했을 때 선택한 셀에 해당하는 시트명, 엑셀파일명, 시트의 순서로 시트명을 알아오는 기능 입니다. 이것을 사용자정의 함수로 만든 후 엑셀에서 사용해 보도록 하겠습니다. 이런 정보를 가져오는 개체가 Application.Caller.Parent 입니다.

 

▼ 먼저 아무 셀이나 선택하게 되면 그 선택한 셀이 포함되어있는 시트명을 반환하는 소스입니다. 인수로 Optional 로 한 이유는 인수값에 아무것도 않넣어도 구동이 될수 있도록 한 것입니다. 아무 값이 없을 때는 현재 열려 있는 시트에 명을 가져옵니다. 그 소스가 Application.Caller.Parent.Name 이며 선택한 셀이 있을때는 Parent.Name 으로 가져오면 됩니다. Parent 라는 의미가 자기를 포함하고 있는 개체를 가리킵니다.

 

Function 셀시트명(Optional sel As Range)     Application.Volatile         If sel Is Nothing Then         셀시트명 = Application.Caller.Parent.Name     Else         셀시트명 = sel.Parent.Name     End If End Function

 

▼ 다음은 엑셀 파일명을 가져오는 것인데 위에서 Parent 에 대해 설명했듯이 시트를 포함하고 있는 것이 문서겠죠. 문서개체로 올라갈려면 Parent 한번 더 써주면 되겠죠. 그래서 소스에서 Parent.Parent 가 된 것입니다.

 

Function 셀파일명(Optional sel As Range)     Application.Volatile         If sel Is Nothing Then         셀파일명 = Application.Caller.Parent.Parent.Name     Else         셀파일명 = sel.Parent.Parent.Name     End If End Function

 

▼ 마지막으로 시트명을 가져올 때 시트의 순서값으로 이름을 가져올수 있습니다. 아래에 시트의 순서가 바뀌면 이름도 바뀌겠죠. Application.Caller.Parent.Parent 로 엑셀문서 개체 까지 올라간후 Sheets() 함수를 사용해 인수로 몇번째 인지 넘기면 그에 대한 정보를 얻을수 있습니다. 그리고 함수마다 Application.Volatile 가 상단에 추가 되어 있는 이유는 참조하는 값이 변경되었을 함수가 자동으로 재계산하기 위함입니다.

 

Function 시트위치로시트명(sheetNum As Integer)     Application.Volatile         If sheetNum > Application.Caller.Parent.Parent.Sheets.count Then         시트위치로시트명 = "없음"     Else         시트위치로시트명 = Application.Caller.Parent.Parent.Sheets(sheetNum).Name     End If End Function

 

▼ 3개의 함수를 추가 하였다면 사용자정의 함수 목록에 있을 겁니다. 이것을 사용해 엑셀에서 값을 추출해 보겠습니다.

 

 

▼ 셀을 선택했을 때 시트명을 가져오는 함수를 수행한 모습입니다. 인수로 셀을 선택하라고 대화상자가 뜨죠.

 

 

▼ 파일명을 가져오는 함수도 위와 같이 합니다. 그리고 시트순서값으로 이름을 가져오는 예제는 시트위치값을 셀에 넣어두고 함수에 인수로 넘기게 만들었습니다.

 

 

▼ 이렇게 3개의 함수를 엑셀에서 적용해 보았습니다. 아래 시트명을 보시면 제대로 값을 추출했다는 것을 확인할수 있을 겁니다.