이름관리자에서 VBA의 Evaluate 함수 사용하기

텍스트 형식으로 된 수식의 결괏값을 알아내기 위해 사용자 정의 함수를 작성했는데,

이렇게 하지 않고,

VBA의 Evaluate 함수를 이름관리자에서 사용할 수 있는 방법이 있었습니다.

워크시트에는 Evaluate함수가 없으므로 수식에 직접 사용할 수는 없습니다.

 

1. 이름 짓기

공백대체.xlsx

0.01MB

 

 

 

(1) 수식 - 이름관리자를 선택하거나, 수식 이름 정의를 클릭

 B1셀을 선택한 상태에서 수식 - 이름관리자를 선택하거나, 수식 이름 정의의 콤보 상자 버튼이 아닌 왼쪽을 클릭합니다.

 

 

(2) 이름 정의 및 참조 대상 입력

그러면,  이름에 _2_20_1이라고 표시되는데,

적당한 이름을 지정합니다. '계산'이라고 하겠습니다.

 

그리고, 참조대상에 

일반적으로는 =evaluate(까지 입력하고,

A1셀을 클릭하고 괄호를 닫으면 되는데,

 

예제 파일에는 특수한 공백이 있어서

SUBSTITUTE(A1,UNICHAR(160),"")라고,

유니코드 160에 해당하는 공백을 빈 문자로 바꾸는 수식을 넣어줘야 합니다.

 

그러면 참조대상은 

=evaluate(SUBSTITUTE(A1,UNICHAR(160),""))이 됩니다.

 

확인 버튼을 누릅니다.

 

그러나 이름 관리자 명령을 사용한 경우는 아래와 같이 이름 관리자 창이 나타나므로 닫기 버튼을 한번 더 눌러야 합니다.

 

2. 셀에 적용하기

B1셀에 =계산이라고 입력하면 값 40이 구해지고,

 

B1셀 오른쪽 아래 네모 모양의 채우기 핸들을 더블 클릭하면 맨 아랫줄까지 수식이 채워지는데, 왼쪽 셀을 참고해서 결괏값이 구해집니다.

 

3. 파일 형식 *.xlsm으로 지정

파일에서 저장 버튼을 누르면 바로 저장되는 것이 아니고,

아래와 같이 "정의된 이름으로 저장된 Excel 4.0 함수는 매크로 제외 통합 문서에 저장할 수 없다"라고 하면서

예, 아니요 등을 묻는데 아니요를 선택하고,

 

문서 형식 오른쪽 콤보 상자 버튼을 누른 후 매크로 사용 통합 문서(*.xlsm)으로 저장해야 합니다.

 

 

4. 제약 사항

이름관리자에 등록했으므로 다른 파일에 적용할 수 없고,

다른 파일에 다시 이름을 등록해야 합니다.

 

이것은 사용자 정의 함수의 경우는

추가 기능(*.xlam) 파일에 저장해 놓으면 어떤 파일에서나 사용할 수 있는 것과 다른 점입니다.

 

공백대체(이름관리자).xlsm

0.01MB

 

 

 

5. 추가 기능 파일 만들기

이 글에서 공백대체(완성).xlsm 파일을 다운로드한 후 엷니다.

 

그리고, 다른 이름으로 저장 메뉴를 누른 후

추가 기능(*.xlam 또는 *.xla)을 누르고,

저장 버튼을 누릅니다.

 

그러면 "C:\Users\사용자명\AppData\Roaming\Microsoft\AddIns" 폴더에 저장됩니다.

공백대체(완성).xlam

0.02MB

 

 

 

 

6. 추가 기능 파일 등록하기

파일 - 옵션 - 추가 기능에서 이동 버튼을 누르거나 개발도구 - Excel 추가 기능 메뉴를 누르면

 

아래와 같은 추가 기능 창이 표시되는데, 찾아보기 버튼을 누르면

 

AddIns 폴더의 추가 기능 파일이 표시되므로 이것을 클릭한 후 열기 버튼을 누릅니다.

 

그러면 아래와 같이 '공백대체(완성)이란 추가 기능 파일이 추가됩니다. 확인 버튼을 누릅니다.

 

 

7. 추가 기능 파일의 사용자 정의 함수 사용하기

 

이제 위에서 만든 공백대체(이름관리자).xlsm 파일의 C1셀에

=calc_text(SUBSTITUTE(A1,UNICHAR(160),""))라고 입력하면

이 파일에는 calc_text란 사용자 정의 함수가 없지만 추가 기능 파일에 있기 때문에 에러 없이 값이 구해집니다.

 

만약 추가 기능 창에서 '공백대체(완성) 왼쪽의 체크를 지운 다음 확인 버튼을 누르면

='C:\Users\lsw32\AppData\Roaming\Microsoft\AddIns\공백대체(완성).xlam'!calc_text(SUBSTITUTE(A1,UNICHAR(160),"")) 라고

calc_text 사용자 정의 함수가 있는 폴더 및 파일명과 !가 앞에 생기는데 이 부분을 지우면

 

사용자 정의 함수를 찾을 수 없기 때문에 #NAME! 에러가 발생합니다.