複雑度とファンクションポイントを計算するエクセルマクロ

DET/RET/FTRから複雑度やファンクションポイントを計算するエクセルのマクロである．Excel2002では動いている．

使い方は以下の通り．

1. メニューバーの「ツール」→「マクロ」→「Visual Basic Editor」
2. メニューバーの「挿入」→「標準モジュール」
3. 下記コードの“'---begin---”から“'---end---”までをコピー
4. Visual Basic Editorを閉じる
5. DET/RET/FTRをシート上に記述
6. 結果を表示したいセルでメニューバーの「挿入」→「関数」
7. ダイアログの「関数の分類」から「ユーザ定義」を選択
8. 関数名に４個の関数（DataFunctionComplexity/DataFunctionPoint/TransactionalFunctionComplexity/TransactionalFunctionPoint）が出てくるので，適当なものを選ぶ．
9. 関数を設定したセルに結果を表示

'---begin---
Option Explicit

Type DataFunction
    det As Integer
    ret As Integer
    complexity As String
End Type

Type TransactionalFunction
    det As Integer
    ftr As Integer
    complexity As String
End Type

Const DET_MAX = 32767 '整数型の最大値
Const RET_MAX = 32767
Const FTR_MAX = 32767

Function DataFunctionComplexity(det As Integer, ret As Integer) As String
    Dim df(6) As DataFunction
    df(0).det = 50
    df(0).ret = 1
    df(0).complexity = "low"
    
    df(1).det = DET_MAX
    df(1).ret = 1
    df(1).complexity = "average"
    
    df(2).det = 19
    df(2).ret = 5
    df(2).complexity = "low"
    
    df(3).det = 50
    df(3).ret = 5
    df(3).complexity = "average"
    
    df(4).det = DET_MAX
    df(4).ret = 5
    df(4).complexity = "high"
    
    df(5).det = 19
    df(5).ret = 6
    df(5).complexity = "average"
    
    df(6).det = DET_MAX
    df(6).ret = RET_MAX
    df(6).complexity = "high"
    
    Dim i As Integer
    For i = LBound(df) To UBound(df)
        If det <= df(i).det And ret <= df(i).ret Then
            DataFunctionComplexity = df(i).complexity
            Exit For
        End If
    Next
End Function

Function DataFunctionPoint(filetype As String, complexity As String) As Integer
    Dim dic As Object
    
    Set dic = CreateObject("Scripting.Dictionary")
    dic.Add "EIF,low", 5
    dic.Add "EIF,average", 7
    dic.Add "EIF,high", 10
    dic.Add "ILF,low", 7
    dic.Add "ILF,average", 10
    dic.Add "ILF,high", 15
    
    DataFunctionPoint = dic.Item(filetype + "," + complexity)
End Function

Function TransactionalFunctionComplexity(trantype As String, det As Integer, ftr As Integer) As String
    Dim ei(6) As TransactionalFunction
    ei(0).det = 15
    ei(0).ftr = 1
    ei(0).complexity = "low"
    
    ei(1).det = DET_MAX
    ei(1).ftr = 1
    ei(1).complexity = "average"
    
    ei(2).det = 4
    ei(2).ftr = 2
    ei(2).complexity = "low"
    
    ei(3).det = 15
    ei(3).ftr = 2
    ei(3).complexity = "average"
    
    ei(4).det = DET_MAX
    ei(4).ftr = 2
    ei(4).complexity = "high"
    
    ei(5).det = 4
    ei(5).ftr = FTR_MAX
    ei(5).complexity = "average"
    
    ei(6).det = DET_MAX
    ei(6).ftr = FTR_MAX
    ei(6).complexity = "high"
    
    Dim eo_eq(6) As TransactionalFunction
    eo_eq(0).det = 19
    eo_eq(0).ftr = 1
    eo_eq(0).complexity = "low"
    
    eo_eq(1).det = DET_MAX
    eo_eq(1).ftr = 1
    eo_eq(1).complexity = "average"
    
    eo_eq(2).det = 5
    eo_eq(2).ftr = 3
    eo_eq(2).complexity = "low"
    
    eo_eq(3).det = 19
    eo_eq(3).ftr = 3
    eo_eq(3).complexity = "average"
    
    eo_eq(4).det = DET_MAX
    eo_eq(4).ftr = 3
    eo_eq(4).complexity = "high"
    
    eo_eq(5).det = 5
    eo_eq(5).ftr = FTR_MAX
    eo_eq(5).complexity = "average"
       
    eo_eq(6).det = DET_MAX
    eo_eq(6).ftr = FTR_MAX
    eo_eq(6).complexity = "high"

    Dim tf() As TransactionalFunction
    If trantype = "EI" Then
        tf = ei
    Else
        tf = eo_eq
    End If
    Dim i As Integer
    For i = LBound(tf) To UBound(tf)
        If det <= tf(i).det And ftr <= tf(i).ftr Then
            TransactionalFunctionComplexity = tf(i).complexity
            Exit For
        End If
    Next
End Function

Function TransactionalFunctionPoint(trantype As String, complexity As String) As Integer
    Dim dic As Object
    
    Set dic = CreateObject("Scripting.Dictionary")
    dic.Add "EI,low", 3
    dic.Add "EI,average", 4
    dic.Add "EI,high", 6
    dic.Add "EO,low", 4
    dic.Add "EO,average", 5
    dic.Add "EO,high", 7
    dic.Add "EQ,low", 3
    dic.Add "EQ,average", 4
    dic.Add "EQ,high", 6
    
    TransactionalFunctionPoint = dic.Item(trantype + "," + complexity)
End Function
'---end---