이 게시판은 아별닷컴 회원만 질문을 올릴 수 있습니다. 회원에게 주어지는 특권인셈이지요. 회원이 아닌 분들은 열람만 가능합니다.
글 수 822
EVALUATE함수에 관해서 질문입니다.
아래의 함수를 매크로 이름 정의가 아니라 VBA함수로 정리할수 있을까요?
몰라서 질문 드립니다.
이름 명 | 이름 내용 | ||||||||||
EVALUATE_F | =EVALUATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!$B29,"G","*0"),"S","*0"),"T","*1"),"F","*0")) | ||||||||||
EVALUATE_G | =EVALUATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!$B30,"G","*1"),"S","*0"),"T","*0"),"F","*0")) | ||||||||||
EVALUATE_NUMBER | =EVALUATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!IV31,"G",""),"S",""),"T",""),"F","")) | ||||||||||
EVALUATE_S | =EVALUATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!$B32,"G","*0"),"S","*1"),"T","*0"),"F","*0")) | ||||||||||
EVALUATE_T | =EVALUATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!$B33,"G","*0"),"S","*0"),"T","*1"),"F","*0")) |
댓글 '2'
테스킨님..
기존 답변에 잘 못된 점이 있어 정정 답변 드립니다.
EVALUATE가 매크로 함수이기 때문에 사용자 정의 함수로 구현할 수 없다는 말씀을 드렸었는데요..
EVALUATE가 매크로 함수 뿐만 아니라 APPLICATION의 메서드로 제공되고 있더라구요.. 쿨럭~
아래와 같이 사용자 정의 서식을 작성하시면 됩니다.
실 적용예는 첨부파일을 참고하세요..
다운 받기 : abyul.com_20100111_BM_EVALUATE-5.xls
'### 문자열 수식을 계산하기 위한 사용자 정의 함수 |
'### Created by Joowon Oh @ abyul.com |
Public Function makeValueFunction(rngTarget As Range, Optional strOption As String) |
Dim strResult As String: strResult = rngTarget.Value |
Dim strArray(): strArray = Array("G", "S", "T", "F") |
Dim intArray() |
If strOption <> "" Then |
Select Case strOption |
Case "G": intArray = Array(1, 0, 0, 0) |
Case "S": intArray = Array(0, 1, 0, 0) |
Case "T": intArray = Array(0, 0, 1, 0) |
Case "F": intArray = Array(0, 0, 0, 1) |
Case "All": intArray = Array(1, 1, 1, 1) |
End Select |
For i = 0 To UBound(strArray) |
strResult = Replace(strResult, strArray(i), "*" & CStr(intArray(i))) |
Next i |
End If |
makeValueFunction = Application.Evaluate(strResult) |
End Function |
하하.. 테스킨님..
어디서 많이 보던 스타일의 엑셀파일이라서 찾아봤더니..
예전에 뮤즈님의 질문에 대한 답변으로 제가 만들었던 파일이군요.. ^^
http://www.abyul.com/zbxe/72773
우선 질문에 답변을 먼저 드리겠습니다.
Q. 아래의 함수를 매크로 이름 정의가 아니라 VBA함수로 정리할수 있을까요?
A. 없습니다.==> 답변 정정합니다(2011.10.04) 있습니다.. 있고요.. 이 바로 아래 댓글을 참고하세요.
매크로 함수를 사용하면 아래와 같은 이유로 안되지만..
대신.. APPLICATION의 EVALUATE 메서드를 사용하면 됩니다. 이름은 같지만 다른 개념입니다.
Because~
you cannot use ExecuteExcel4Macro in a UDF. Its part of the "you can't do anything that changes the structure of the workbook in a UDF" restriction - even if this particular Excel 4 Macro won't change the structure.
(From http://www.mrexcel.com/archive/VBA/12290a.html )
==> 대략 번역하자면..
당신은(ㅎㅎ) Excel4매크로 함수를 UDF(User Defined Function:사용자 정의 함수)에서 사용할 수 없습니다.
왜냐믄~ "UDF를 이용해서는 엑셀 워크북의 구조를 변경하는 작업을 수행할 수 없다"는 UDF의 기본 제한 사항때문입니다.
비록.. Excel4매크로 함수가 엑셀의 구조를 전혀~ 바꾸는 기능이 아닐지라도..
그렇습니다.
예제에서 사용한 함수 중 핵심 함수인 Evaluate라는 함수는 Excel4Macro 함수로..
텍스트로된 수식을 평가(evaluate)해서 결과값을 산출해주는 함수입니다.
만약 매크로 함수인 Evaluate함수가 UDF에서 제대로 작동한다는 가정을 한다면..
우리는 아래의 코드를 작성해서 UDF(사용자 정의 함수 : 테스킨님이 VBA 함수라고 언급하신..)로 정의해서 사용할 수 있을 겁니다.
==> 하지만.. 불행히도 #VALUE!에러가 발생합니다.
'### UDF(사용자 정의 함수)에는 Excel4Macro를 사용할 수 없다.
'### 워크시트에서 호출하면 아래와 같은 에러를 발생시킴.
'### 1004 : 'ExecuteExcel4Macro' 메서드('_Global' 개체의)에서 오류가 발생하였습니다.
Public Function makeValueFunction(rngTarget As Range, strOption As String)
Dim strResult As String: strResult = rngTarget.Value
Dim strArray(): strArray = Array("G", "S", "T", "F")
Dim intArray()
Select Case strOption
Case "G": intArray = Array(1, 0, 0, 0)
Case "S": intArray = Array(0, 1, 0, 0)
Case "T": intArray = Array(0, 0, 1, 0)
Case "F": intArray = Array(0, 0, 0, 1)
Case "All": intArray = Array(1, 1, 1, 1)
End Select
For i = 0 To UBound(strArray)
strResult = Replace(strResult, strArray(i), "*" & CStr(intArray(i)))
Next i
makeValueFunction = ExecuteExcel4Macro("Evaluate(" & strResult & ")")
End Function
그렇다면..
VBA로는 구현할 수 없는건가?
일단 첨부파일을 다운 받으세용~~ 20100111_BM_EVALUATE-4.xls
물론 하고자 한다면 못할게 없는 세상이니... 가능은 합니다만..
수식을 한 자 한자 떼어내서 그게 숫자인지 수식인지 가로인지를 식별하고..
그것을 다시 수식을 재구축해서 사용한다면 굳이 Evaluate매크로 함수를 사용하지 않아도 됩니다만..
그런식의 접근은 조건이 한두개만 달라져도 전체적인 코딩을 다시해야하며.. 쓸데없이 코딩만 길어지는 삽질이 될 수 있습니다.
저는 UDF를 이용하되..
바로 결과값을 구해주는게 아닌 결과값이 나오도록하는 수식 자체를 돌려주는 UDF를 만들었습니다.
그 수식의 결과를 값으로 만든 다음에, 찾아바꾸기(Ctrl+H)를 사용하여 =를 =로 교체해주어서 수식의 결과값을 만들어냈죠..
( 아별툴 사용자라면.. Ctrl+V를 눌러서 선택 영역을 값으로 만들어주면 바로 결과를 보실 수 있습니다. )
( 복사모드가 아닌 상태에서 값으로 만들기 기능이 작동합니다. ESC누른 다음 Ctrl+V를 누르면 됩니다. )
그때 사용한 코드입니다.
'### UDF안에 Excel4Macro 함수를 사용할 수 없으므로.. 대안으로 선택됨.
Public Function makeValue(rngTarget As Range, strOption As String)
Dim strResult As String: strResult = rngTarget.Value
Dim strArray(): strArray = Array("G", "S", "T", "F")
Dim intArray()
Select Case strOption
Case "G": intArray = Array(1, 0, 0, 0)
Case "S": intArray = Array(0, 1, 0, 0)
Case "T": intArray = Array(0, 0, 1, 0)
Case "F": intArray = Array(0, 0, 0, 1)
Case "All": intArray = Array(1, 1, 1, 1)
End Select
For i = 0 To UBound(strArray)
strResult = Replace(strResult, strArray(i), "*" & CStr(intArray(i)))
Next i
makeValue = "=" & strResult
End Function
수식의 결과값을 아래와 같습니다만..
위에서 설명한 것처럼.. 값으로 바꾼 다음에.. 바꾸기 하거나..
아별툴 사용자라면.. ESC후에 Ctrl+Shift+V하고 엔터 치면 결과값이 나옵니다.
짜라란~~
도움이 되셨기를..