worksheetfunction에 없는 엑셀함수 사용하기

application.worksheetfuction 을 사용하면, 왠만한 엑셀함수를 VBE에서 사용할수 있다.
문제는 당연히(?) 없는 함수도 있다는 점인데, 그냥 생각해보면 VBA에서 셀에다가 그냥 수식 텍스트가 입력되게 하면 될 것 같다…. 확신은 없지만..

우선, worksheetfuction을 써보자.

Cells(1, 2).Value = Application.WorksheetFunction.Sum(1, 2)



요 식은 이런 텍스트를 쳤을때와 동일한 효과일 것이다.

=sum(1,2)



이런 형태의 수식을 적어보는건 어떨까..?

=HYPERLINK("#’"&“Sheet2”&"’!A1",“GO”)



특수문자는 쌍따옴표 안에 넣어 텍스트로 처리하거나, (=, &, ! 등) chr 함수로 사용할 수 있다. char함수는 엑셀함수에서 아스키코드를 반환하게 되는데, 마찬가지로 vba에서는 chr로 기재를 해줘야 한다.

myimg



수식이 길어지면 헤깔리기 때문에, 나의 경우에는 chr함수를 사용하는 쪽으로 작성했다.
예를 들어, 첫 서두, =HYPERLINK(" 까지를

"=HYPERLINK(""" 이렇게 써도 되고,
Chr(61) & "HYPERLINK(""" 이렇게 써도 된다는 뜻이다.

이런식으로 변환하게 되면 아래와 같다. 하이퍼링크 함수이기 때문에 이런식으로, 미리 txt 변수를 만들어 주었다.

Dim txt As String
txt = Cells(2, 4).Value

myimg



한줄로 쓰면 이런식이 되겠다.

Chr(61) & “HYPERLINK(”"" & Chr(35) & “’”"" & Chr(38) & """" & txt & """" & Chr(38) & “”"’!A1"",““GO””)"



원래 쌍따옴표때문에, 복잡한 수식인데, 더 복잡해 졌다.

Sub test()
Dim txt As String
    txt = Cells(2, 4).Value

    Cells(1, 2).Value = Application.WorksheetFunction.Sum(1, 2)
    Cells(2, 2).Value = _
    "=HYPERLINK(""" & Chr(35) & "'""" & Chr(38) & """" & txt & """" & Chr(38) & """'!A1"",""GO"")"

End Sub


실행해보면, 잘 실행이 되고, 하이퍼링크 기능도 잘 된다.

myimg



엑셀함수를 텍스트로 넣기

엑셀VBA의 사용자 함수는 다른 사람과 공유하기가 어렵다. 이번에 하고자 하는 것은 반복 사용되는 엑셀함수를 매크로로 만들어 두는 것이다.

말이 좀 애매한데… 아래와 같은 표가 있다고 하자

myimg



예를 들어 올해(21년) 실적과 전년(20년) 실적의 성장율을 구한다고 하자.
시트에서 함수를 쓴다면, 아래와 같이 함수를 작성해야 한다.

=(올해실적 - 전년실적) / 전년실적



괄호치기도 그렇고, 전년실적을 두번 찍어주는 것이 귀찮다.

아래 기능이 되었으면 한다.

  • 매크로로, 올해실적과 전년실적 셀을 선택하면, 해당 함수가 기재된다.
  • 자동완성을 써야할 가능성이 높으므로, 상대참조가 먹도록 한다.


마찬가지로, 달성율과 RATE함수를 활용한 CAGR을 구해본다.
(옆 창은 만든 매크로를 모아놓은 사용자폼이다.)

myimg



함수로 작성이 되기 때문에,

myimg



행이 추가되었을 경우, 상대참조가 되어, 자동완성도 가능하다.

myimg



말은 장황하지만 별거 없다.
아래 코드처럼 워크시트 함수를 노가다로 적어주면 된다.

Sub growthRate()

Dim 작년실적 As Range
Dim 올해실적 As Range

On Error GoTo DD:

Set 작년실적 = Application.InputBox("작년실적", , , , , , , 8)
Set 올해실적 = Application.InputBox("올해실적", , , , , , , 8)

ActiveCell = "=(" & 올해실적.Address(RowRelative, ColmnRelative) & "-" & 작년실적.Address(RowRelative, ColmnRelative) & ")/" & 작년실적.Address(RowRelative, ColmnRelative)

DD:

End Sub

Sub achievementRate()

Dim 목표실적 As Range
Dim 올해실적 As Range

On Error GoTo DD:
Set 목표실적 = Application.InputBox("목표실적", , , , , , , 8)
Set 올해실적 = Application.InputBox("올해실적", , , , , , , 8)

ActiveCell = "=" & 올해실적.Address(RowRelative, ColmnRelative) & "/" & 목표실적.Address(RowRelative, ColmnRelative)

DD:

End Sub

Sub cagR()

Dim 시작실적 As Range
Dim 종료실적 As Range

On Error GoTo DD:
Set 시작실적 = Application.InputBox("시작실적", , , , , , , 8)
Set 종료실적 = Application.InputBox("종료실적", , , , , , , 8)

기간 = Application.InputBox("기간을 입력하세요")

ActiveCell = "=rate(" & 기간 & "," & "0" & "," & "-" & 시작실적.Address(RowRelative, ColmnRelative) & "," & 종료실적.Address(RowRelative, ColmnRelative) & ")"

DD:

End Sub


그냥 생각만으로 썼는데, 더 쉬운 방법을 알고 계신다면, 그거 사용하시면 될듯 ㅋㅋ

끝.