쓸모없는 짓거리 금융공학

옵션전략과 관련한 파일을 구했는데, 사용한 함수중 Gamma에 문제가 있었다. 최근 버전의 엑셀 워크시트함수중 GAMMA함수가 있어 두 함수의 이름이 중복되어 충돌하는 것이다. 친절하게도 엑셀이 이를 알려주어 사용자정의 함수 Gamma의 이름을 하나씩 바꾸다 보니 gamma 관련 차트(행사가격별 감마의 추이)가 애니메니트되는 효과가 생각났다. 그래서 gif 파일을 하나 만들어 보았다. 에구 의미없다~

데이터를 워크시트가 아닌 곳에 숨겨두기 EXCEL/VBA

보통 배열의 수명은 해당 애플리케이션이 종료되는 순간까지이다. 엑셀의 경우 배열의 내용을 워크시트에 저장해둘 수 있는데, 워크시트말고 배열의 내용을 저장할 만한 공간이 없을 까? 이 방법은 워크시트가 아닌 워크북에 저장하는 것이다. 다음의 코드는 배열을 만들어 값을 채운 후 이름을 붙여 names 컬렉션에 저장하는 것이다(굳이 배열일 필요는 없다. 여기서는 names컬렉션에 데이터를 저장하는 것을 보여주려는 것일 뿐이다)
Public Sub ArrayToName()
Dim MyArray(1 To 20, 1 To 3) As Long
Dim i As Long, j As Long

For i = 1 To 20
For j = 1 To 3
MyArray(i, j) = i + j
Next
Next

Names.Add Name:="MyName", RefersTo:=MyArray

End Sub
만든 이름은 [이름관리자]에서 다음과 같이 확인해볼 수 있다.

그런데 이렇게 만든 이름을 VBA에서는 어떻게 읽어 들여야 하는 가 하는 문제가 하나 있다. 다음은 저장한 name을 불러오는 예이다.
Sub demoReferName()
Dim a, b

a = ThisWorkbook.Names("MyName")
b = Application.Evaluate(a)

Dim i As Long, j As Long

For i = LBound(b, 1) To UBound(b, 1)
For j = LBound(b, 2) To UBound(b, 2)
Debug.Print "(" & i & "," & j & ")= ", b(i, j)
Next
Next
End Sub
이 방법은 매크로 실행에 필요한 설정값을 넣어두는 용도로 사용하기도 한다. 물론 레지스트리를 사용할 수 도 있지만 말이다. (참고로 너무 많은 데이터를 넣어두면 Application.Evaluate의 한계가 있어 에러를 낼 수도 있다)


Range와 Cells EXCEL/VBA

VBA 초보시절에는 Range보다 Cells속성을 사용하여 출력이나 입력좌표를 참조하는 걸 선호하였는데, 초보딱지를 떼었다고 판단되는 순간이 Range를 편안하게 받아 들이는 시점이 아닌 가 싶다(Cells가 속성이라는 걸 모르는 시절)

VBA의 경우 간혹 같은 결과를 가져오는 것이지만 여러 가지 방법이 있고, 그걸 다 알아야 하는 가 싶은 부담감이 있다. 가령 현재의 워크북의 Range개체를 참조하는 방법이 다음과 같이 있다.
Range(“B2”)
Range(“A1:B7”)
Range(“A1:B3,E1:O9”)

Cells의 경우도 마찬가지이다. 다음은 A1:E5 를 참조하는 경우이다.
Range (Cells(1,1), Cells (5,5))

Range 범위에 한정하여 셀을 참조하는 것도 가능한데, 자주 쓰지는 않는다. 다음은 F9를 참조하는 셈이 된다.
Range (“D10:G20”).Cells (0,3)

이걸 줄여서 쓰면 다음과 같다.
Range (“D10:G20”) (0,3)





Application.OnTime을 사용할 때 주의할 점 EXCEL/VBA

Application.OnTime NextBlink, "'" & ThisWorkbook.Name & "'!StartBlinking", , True

이번에 시그널에 따라 매매알람을 알려주는 프로그램을 만들면서 Application.OnTime을 사용하게 되었는데,엑셀매크로파일을 닫으면 좀비처럼 다시 열려 실행되는 버그 아니 버그를 알게 되었다.

매초마다 셀서식을 번갈아 바꾸어 마치 반짝반짝 거리게 만들기 위해 사용하였는데, 문제는 OnTime에 걸어둔 스케줄을 해지하지 않았기 때문이다. 비록 파일은 잠시 닫혔지만 OnTime에 걸어둔 프로시저를 호출하느라 다시 열리는 것이다.

그래서


워크북의 이벤트를 다음과 같이 해두어야 한다.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call Module1.StopBlinking
DeletePopupMenu2
End Sub

Private Sub Workbook_Open()
Call Module1.StartBlinking
GeneratePopupMenu2
End Sub
위의 StopBlinking()프로시저에서는 다음과 같이 마지막 매개변수를 False로 주어 스케줄을 취소한다.

Application.OnTime NextBlink, "'" & ThisWorkbook.Name & "'!StartBlinking", , False



'28' 스택 공간이 부족합니다(Out of Stack Space) 에러 EXCEL/VBA



워크시트의 Change이벤트 프로시저를 만들어 두고, 셀에 값을 입력하면 28 런타임 에러, 스택공간이 부족합니다 라는 에러와 함께 엑셀이 죽어버리는 문제가 생겼다. 아래의 코드는 소문자를 입력하면 대문자로 바꿔주는 간단한 예인데, 자주 보는 에러가 아니어서 어디가 문제인지 감이 오질 않았다. 보통 스택관련한 오류는 재귀호출시 만나게 되는 데 사실 이 문제도 재귀호출의 문제이기는 하다.

결론적으론 Target.Value = UCase$(Target.Value)에서 에러가 나는 것이다. 워크시트내 셀값을 변경하여 _Change 이벤트가 일어나고 다시 Target.Value = UCase$(Target.Value) 때문에 셀 값이 바뀌어 다시 _Change 이벤트가 계속 일어나는 것이다. 이러니 스택오버플로에러가 나는 것이다.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

Set rng = Application.Intersect(Target, [D8:D30])
If rng Is Nothing Then
Else
Target.Value = UCase$(Target.Value)
End If

End Sub
해결책Application.EnableEvents = False를 이벤트 프로시저 처음에 추가하여 추가이벤트 발생을 막아버리는 것이다. 물론 프로시저 마지막에는 원상복구를 해야 한다(Application.EnableEvents = True)
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

Application.EnableEvents = False

Set rng = Application.Intersect(Target, [D8:D30])
If rng Is Nothing Then
Else
Target.Value = UCase$(Target.Value)
End If

Application.EnableEvents = True
End Sub

1 2 3 4 5 6 7 8 9 10 다음