마이크로소프트의 도움말 사이트 http://support.microsoft.com/?id=322090
[VBA]VBA에서 엑셀함수 사용하기
엑셀 함수는 VBA 함수보다 훨씬 다양한 분야에 많은 함수들을 보유하고 있다.
앞서 살펴본 것처럼, 삼각함수 분야에도 많은 역함수를 보유하고 있다. 더군다나 스프레드시트 프로그램으로서 당연히 갖추어야 하는 함수도 많이 보유하고 있다. 예를 들면 다음과 같다.
|
1 2 3 4 |
area = Application.WorksheetFunction.Pi * radius ^ 2 a = Application.WorksheetFunction.Acos(b) Set myRange = Application.Worksheets("Sheet1").Range("C1:C60") mySum = Application.WorksheetFunction.Sum(myRange) |
첫줄은 원주율을 참조한 것이고, 둘째줄은 코사인 역함수를 호출하여 사용한 것이다.
셋째줄은 워크시트 내의 특정범위를 설정한 것이며, 네째줄에서 이 범위에 대한 합을 엑셀함수를 이용하여 구했다.
또 다른 방법으로는 Evaluate 메소드를 사용하는 것이다.
|
1 |
mySum = Evaluate("=Sum(C1:C60)") |
[VB6, VBA]삼각함수의 역함수(ArcSin, ArcCos) 작성
앞서 기술한 것처럼, 비주얼베이직6.0과 VBA에는 삼각함수의 역함수인 ArcSin()과 ArcCos()이 없다. 대신 Atn()만 있다.
도움말을 보면 다음과 같이 작성하라고 안내하고 있다.
|
1 2 |
역 사인 (ArcSin(x)) = Atn(x / sqr(-x * x + 1)) 역 코사인 (ArcCos(x)) = Atn(-x / sqr(-x * x + 1)) + 2 * Atn(1) |
이대로 하면 분모가 0이 되는 경우에 대한 대책뿐만 아니라, x가 -1<=x<=1 범위를 벗어날 경우에 대한 대책이 없다.
특히, 부동소수계산을 하다보면 정확하게 -1 또는 1이 되지 않고, -1.0000000** 또는 1.000000**가 되는 경우가 발생한다. 이에 대한 대책이 필요하다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Function ArcSin(ByVal x As Double) As Double If Abs(Fix(x)) <> 1 Then ‘-1=2 일때는 오류 발생 ArcSin = Atn(x / Sqr(-x * x + 1)) Else ‘ Abs(Fix(x)) = 1일 때 즉 -2 1 ArcSin = Sgn(x) * Atn(1) * 2 End Function Function ArcCos(ByVal x As Double) As Double If Abs(Fix(x)) <> 1 Then 'If (-1 < x) And (x < 1) Then ArcCos = Atn(-x / Sqr(-x * x + 1)) + 2 * Atn(1) Else If x > 0 Then 'Fix(x)=1일 때 ArcCos = 0: Exit Function ElseIf x < 0 Then 'Fix(x)=-1일 때 ArcCos = Atn(1) * 4: Exit Function End If End If End Function |
FYI
ArcSin, ArcCos을 작성한 이유 : 태양기하학에서는 태양의 방위각과 고도를 계산하는 과정에서 삼각함수를 많이 사용한다. 1보다 작은 값들로 사칙연산을 하다보면, 부동소수가 정상 범위를 벗어나는 경우가 생긴다. 이런 오류를 사전에 방지하기 위해서 작성하였다.
태양의 고도를 계산하는 공식은 다음과 같다.
따라서,
- h (태양의 고도) : -90° ~ +90°
- δ (태양의 적위) : -23.5° ~ +23.5°
- L (위도) : -90° ~ +90° (남극 ~ 북극)
- H (시각) : -180° ~ +180° (00시는 -180°, 06시는 -90°, 12시는 0°, 18시는 +90°, 24시는 +180°)
태양 방위각
삼각함수 비교
삼각함수 비교
| 설명 | Excel | VBA | VB6 | VB.NET | 비고 | |
|---|---|---|---|---|---|---|
| sin | 사인함수 | Sin | Sin | Sin | Sin | |
| cos | 코사인함수 | Cos | Cos | Cos | Cos | |
| tan | 탄젠트함수 | Tan | Tan | Tan | Tan | |
| arcsin | 사인역함수 | Asin | – | – | Asin | |
| arccos | 코사인역함수 | Acos | – | – | Acos | |
| arctan | 탄젠트역함수 | AtanAtan2 | Atn | Atn | AtanAtan2 | |
| sinh | 하이퍼볼릭사인함수 | Sinh | – | – | Sinh | |
| cosh | 하이퍼볼릭코사인함수 | Cosh | – | – | Cosh | |
| tanh | 하이퍼볼릭탄젠트함수 | Tanh | – | – | Tanh | |
| arcsinh | 하이퍼볼릭사인역함수 | Asinh | – | – | – | |
| arccosh | 하이퍼볼릭코사인역함수 | Acosh | – | – | – | |
| arctanh | 하이퍼볼릭탄젠트역함수 | Atanh | – | – | – |
이상에서 보듯 VB6와 VBA로 프로그래밍을 하고자 하면, 삼각함수의 역함수를 작성해야 한다.
만약 VBA에서 새로 역함수를 작성하지 않고, 엑셀함수를 사용하고자 한다면 다음과 같이 하면 된다.
|
1 2 |
area = WorksheetFunction.Pi * radius ^ 2 a = WorksheetFunction.Acos(b) |
VB.NET Tutorial
Visual Basic .NET에 대한 설명서가 잘 정리된 웹페이지를 소개한다.
http://www.java2s.com/Tutorial/VB/CatalogVB.htm
키워드(keyword) 별로 어떻게 쓰이는지 알고 싶다면, 꼭 한 번 들를만 하다.
또한, 비주얼베이직 닷넷 말고 지구상에 존재하는 웬만한 프로그래밍 언어에 대한 설명서도 함께 있다. 여기서는 비주얼 베이직 닷넷만 연결해 놓은 것이다. 왜? 이 홈피의 주관심사여서. ^^
[VBA]엑셀의 객체 – Appication
Application.UserName : 엑셀 사용자 이름
Application.Version : 현재 사용하는 엑셀 버전
Application.Path : 엑셀이 설치되어 있는 폴더(위치)
Application.StartUpPath : 시작 폴더(위치)
Application.DisplayStatusBar : 상태표시줄을 화면에 표시할 것인지 아니면 안 할 것인지를 설정
Application.StatusBar : 상태표시줄에 문자열을 표현
[VBA]IF문의 이해 : 이차방정식 풀기 Solve the quadratic equation using Excel
문제) 엑셀을 이용하여 이차방정식(
출제의도) If문을 이해한다.
해법)
1) 첫행(셀A1부터 셀E1까지)에 방정식에 필요한 계수들을 기록한다.
2) 둘째 행을 계수의 입력과 근에 대한 출력으로 설정한다.
3) 마지막 셀(셀F2)에 프로시저를 실행시킬 ‘단추’를 둔다.
4) VBA로 이차방정식을 푸는 프로시저를 다음과 같이 작성한다.
특징: 실근뿐만 아니라 허근도 구한다. 불능과 부정도 판정한다. 설령 이차항의 계수가 0일지라도, 즉 이차방정식이 아닐지라도 계산한다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
Sub SolveQuadraticEquation() Dim a As Single Dim b As Single Dim c As Single 'Dim x1 As Single '실근만 있을때는 유효하나, 허근이 있으면 좀 곤란하게 되지요. 'Dim x2 As Single Dim d As Single '판별식 discriminant Dim u As Single Dim v As Single Dim output1 As String '허근의 표시 문자인 i를 출력하기 위해서, Dim output2 As String '그리고 부정과 불능을 출력하기 위해서 문자열로 처리함. '1) 셀에서 값을 읽어온다. Read the varibles from the excel cells a = Range("A2") b = Range("B2") c = Range("C2") ' '2) find the roots of equation If a <> 0 Then ' --> quadratic equation d = b ^ 2 - 4 * a * c u = -b / (2 * a) If d > 0 Then '서로 다른 두 실근(實根)을 갖는다. v = Sqr(d) / (2 * a) output1 = u + v output2 = u - v ElseIf d < 0 Then '허근(虛根)을 갖는다. v = Sqr(-d) / (2 * a) output1 = u & "+" & v & "i" output2 = u & "-" & v & "i" Else 'd=0 '중근(重根)을 갖는다. output1 = u output2 = output1 End If Else 'a = 0 If b <> 0 Then '--> linear equation output1 = -c / b output2 = "None" Else 'b= 0 If c <> 0 Then output1 = "不能" output2 = "不能" Else 'c = 0 output1 = "不定" 'indeterminate output2 = "不定" End If End If End If ' '3)출력 Output Range("D2") = output1 Range("E2") = output2 ' End Sub |
[VBA]macro와 vba
매크로 기록은 조작한 명령을 기록하는 것으로 처리를 자동화할 때 편리한 기능이다. 조건에 따라 처리를 분기시키거나 원하는 값을 구할 때까지 계산을 반복시키는 복잡한 처리는 불가능하다. 이것은 VBA의 범주로 넘어간다. VBA를 시작한 사람은 우선 매크로 기록에서 전체를 이해하고, 다음에 Visual Basic Editor를 사용하여 코드를 추가/수정하는 것으로 원하는 매크로로 바꾸는 것이 VBA를 이해하고 실행하는데 효과적인 방법이다.
출처 : Katsuyuki Watanabe et al, 감춰놓고 혼자보는 엑셀비밀노트 970, p.652
파일경로와 파일이름
1. 프로그램이 시작된 경로를 알아내기
– App.Path 함수를 사용한다.
2. App.Path 사용시 주의할 점
– 루트 디렉토리일 경우 “\”를 함께 반환한다.
– 루트 디렉토리가 아닐 경우, 끝에 “\”가 없다.
3. 더 좋은 App.Path (출처 : http://www.freevbcode.com/ShowCode.asp?ID=878)
항상 끝에 “\”가 붙게 한다.
|
1 2 3 4 5 6 |
Public Function AppPath() As String Dim NewPath As String NewPath = App.Path If Right(App.Path, 1) <> "\" Then NewPath = NewPath & "\" AppPath = NewPath End Function |
사용예
|
1 2 |
'Open "C:\IA Program\입력값\FormData_5장.txt" For Input As #1 Open AppPath & "입력값\FormData_5장.txt" For Input As #1 |
4. 프로그램 시작 경로에서 파일명 가져오기
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Function GetFileName(sFileName As String) As String Dim FullFilename As String ' '파일이름 If Right$(Trim$(App.Path), 1) = "\" Then FullFilename = App.Path + sFileName Else FullFilename = App.Path + "\" + sFileName End If ' GetFileName = FullFilename ' End Function |
사용예 : FormData_5장.txt를 프로그램시작경로에서 찾는다.
|
1 |
myFile = GetFileName("FormData_5장.txt") |
5. 전체파일명에서 경로만 뽑아내기
|
1 2 3 4 5 6 7 8 9 10 |
Function GetPath(strFullFilename As String) As String ' Dim strPath As String strPath = Mid(strFullFilename, 1, InStrRev(strFullFilename, "\", , vbTextCompare) - 1) If Len(strPath) = 0 Then strPath = App.Path End If GetPath = strPath ' End Function |
호환성 : Visual Basic 5, 6
3차원 벡터(또는 점)에 대한 구조체(structure)
3차원 벡터(또는 점)에 대한 구조체(structure)를 아래와 같이 작성할 수 있다.
<특이점>
1) 벡터의 성분은 X, Y, Z로 명백하므로 그냥 Public 변수로 설정함
2) 배열로 선언된 벡터 성분과도 호환되도록 함
– VB.NET의 Default Property를 이용함
– 사용예 :
|
1 2 |
Dim v1,v2 As Vector3D Dim innerProduct As Single = v1(0)*v2(0) + v1(1)*v2(1) + v1(2)*v2(2) |
구조체 소스코드
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
Public Structure Vector3D Public X, Y, Z As Single Public Shared ReadOnly ZeroVector As Vector3D = New Vector3D(0, 0, 0) #Region "Constructors" 'Private Sub New() 'Nothing -&gt; No default initialization 'End Sub Public Sub New(ByVal x As Single, ByVal y As Single, ByVal z As Single) Me.X = x Me.Y = y Me.Z = z End Sub Public Sub New(ByVal v As Vector3D) Me.New(v.X, v.Y, v.Z) 'copy constructor End Sub #End Region #Region "Properties" ''' ''' 배열로 선언된 벡터 성분과 호환이 되도록 작성함 ''' '''성분의 위치를 지정하는 값 0:x, 1:y, 2:z ''' 해당성분의 값 ''' 해당성분의 값 ''' ''' 왜 호환되게 했나고요? ''' c/c++로 짠 코드를 변환하다 보면, 배열로 간단하게 해결한 것들이 많아서요. ''' Default Public Property Elements(ByVal index As Short) As Single Get Dim i As Short = index Mod 3'실수를 방지하기 위해서 Select Case i Case 0 Return X Case 1 Return Y Case 2 Return Z End Select End Get Set(ByVal value As Single) Dim i As Short = index Mod 3 Select Case i Case 0 X = value Case 1 Y = value Case 2 Z = value End Select End Set End Property #End Region ''각종 Property와 Operator 등은 생략 ^^ End Class |