ユーザー定義関数を活用する

前回はVBAの入門用として、わかりやすい公式を軸に話を展開してみました。今回は、実用にも耐えるコードをひとつ紹介したいと思います。コンピュータの前で電卓を叩いたり、繰り返し行う処理なのに、いつも同じ文献の同じ箇所を参照している。実にばかげた話ですが、無意識のうちにやってしまう事、決して少なくありません。そこで登場するのが、条件分岐を利用した関数のテクニックです。

1)Visual Basic Editorの起動

  • エクセルのメニューバーから、[ツール]⇒[マクロ]⇒[Visual Basic Editor]を選択します。[Visual Basic Editor]を選択
  • Visual Basic Editorが起動したら、メニューバーから、[挿入]⇒[標準モジュール]を選択します。ここで、プロジェクトエクスプローラにModule1が作成されたことを確認してください。この部分に、functionプロシージャのコードを書くことによって、ユーザー定義関数が使えるようになります。[挿入]⇒[標準モジュール]を選択

2)コーディング

  • 下記の例は共通仮設費率を計算するためのコードです。個々の命令文の詳細については、専門書や、Excelのヘルプ等を参照してください。まず、共通仮設費率を算出する際の条件は、21種類の工種区分と3種類の対象額の範囲による区分があります。そこで、複数の条件判断ができるIfステートメントを利用して、工種区分を選択し、対象額の範囲をサブルーチンに委ねるというプログラムにより計算をおこないます。では、そのコードの構成を見ていきます。先頭行の( )内に示される引数は、工種区分と対象額の2種類で、この種類や範囲によって共通仮設費率が決まります。ここで、河川工事を例に挙げれば、Ifステートメントの条件の部分、『もし~ならば処理Aをしなさい』の~の部分が"河川工事"です。処理Aはそれ以下のステートメントで、kr、a、b、の定数及びサブルーチン『算定式1』になります。Ifステートメントにより、実際に実行されるステートメントは、20行程度でしかないのは一目瞭然です。コードは長くなりましたが、内容は実に簡単なものとなります。実務において、同一作業の中で工種区分が変更になる事は、それほど多くはありません。しかし、一度作成したファイルの再使用は頻繁にあり、ミスは起こりうる事ですので、煩わしい作業はなるべく省略したいものです。
    例 共通仮設費率の計算
    
    Function kr(工種区分 As String, 対象額 As Currency) As Double
    'copyright(c)2011 三浦プロジェクト
    Dim p As Currency
    Dim a As Single, b As Single
    Dim krs As Double, krl As Double
    
    p = 対象額
    If 工種区分 = "河川工事" Then
        krs = 12.53
        a = 238.6
        b = -0.1888
        krl = 4.77
        算定式1 p, a, b, kr, krs, krl
    ElseIf 工種区分 = "河川・道路構造物工事" Then
        krs = 26.94
        a = 6907.7
        b = -0.3554
        krl = 4.37
        算定式1 p, a, b, kr, krs, krl
    ElseIf 工種区分 = "海岸工事" Then
        krs = 13.08
        a = 407.9
        b = -0.2204
        krl = 4.24
        算定式1 p, a, b, kr, krs, krl
    ElseIf 工種区分 = "道路改良工事" Then
        krs = 12.78
        a = 57#
        b = -0.0958
        krl = 7.83
        算定式1 p, a, b, kr, krs, krl
    ElseIf 工種区分 = "鋼橋架設工事" Then
        krs = 26.1
        a = 633#
        b = -0.2043
        krl = 9.18
        算定式1 p, a, b, kr, krs, krl
    ElseIf 工種区分 = "P・C橋工事" Then
        krs = 27.04
        a = 1636.8
        b = -0.2629
        krl = 7.05
        算定式1 p, a, b, kr, krs, krl
    ElseIf 工種区分 = "舗装工事" Then
        krs = 17.09
        a = 435.1
        b = -0.2074
        krl = 5.92
        算定式1 p, a, b, kr, krs, krl
    ElseIf 工種区分 = "砂防・地すべり等工事" Then
        krs = 15.19
        a = 624.5
        b = -0.2381
        krl = 4.49
        算定式1 p, a, b, kr, krs, krl
    ElseIf 工種区分 = "公園工事" Then
        krs = 10.8
        a = 48#
        b = -0.0956
        krl = 6.62
        算定式1 p, a, b, kr, krs, krl
    ElseIf 工種区分 = "電線共同溝工事" Then
        krs = 9.96
        a = 40#
        b = -0.0891
        krl = 6.31
        算定式1 p, a, b, kr, krs, krl
    ElseIf 工種区分 = "情報ボックス工事" Then
        krs = 18.93
        a = 494.9
        b = -0.2091
        krl = 6.5
        算定式1 p, a, b, kr, krs, krl
    ElseIf 工種区分 = "道路維持工事" Then
        krs = 16.64
        a = 34596.3
        b = -0.4895
        krl = 4.2
        算定式2 p, a, b, kr, krs, krl
    ElseIf 工種区分 = "河川維持工事" Then
        krs = 8.34
        a = 26.8
        b = -0.0748
        krl = 6.76
        算定式2 p, a, b, kr, krs, krl
    ElseIf 工種区分 = "共同溝等工事(1)" Then
        krs = 8.86
        a = 68.3
        b = -0.1267
        krl = 4.53
        算定式3 p, a, b, kr, krs, krl
    ElseIf 工種区分 = "共同溝等工事(2)" Then
        krs = 13.79
        a = 92.5
        b = -0.1181
        krl = 7.37
        算定式3 p, a, b, kr, krs, krl
    ElseIf 工種区分 = "トンネル工事" Then
        krs = 31.87
        a = 5388.7
        b = -0.3183
        krl = 5.9
        算定式3 p, a, b, kr, krs, krl
    ElseIf 工種区分 = "下水道工事(1)" Then
        krs = 12.85
        a = 422.4
        b = -0.2167
        krl = 4.08
        算定式3 p, a, b, kr, krs, krl
    ElseIf 工種区分 = "下水道工事(2)" Then
        krs = 13.32
        a = 485.4
        b = -0.2231
        krl = 4.08
        算定式3 p, a, b, kr, krs, krl
    ElseIf 工種区分 = "下水道工事(3)" Then
        krs = 7.64
        a = 13.5
        b = -0.0353
        krl = 6.34
        算定式3 p, a, b, kr, krs, krl
    ElseIf 工種区分 = "コンクリートダム" Then
        krs = 12.29
        a = 105.2
        b = -0.11
        krl = 9.02
        算定式4 p, a, b, kr, krs, krl
    ElseIf 工種区分 = "フィルダム" Then
        krs = 7.57
        a = 43.7
        b = -0.0898
        krl = 5.88
        算定式4 p, a, b, kr, krs, krl
    Else
        kr = 0
    End If
    End Function
    Sub 算定式1(p As Currency, a As Single, b As Single, kr As Double _
                , krs As Double, krl As Double)
    If p <= 6000000 Then
        kr = krs
    ElseIf p > 6000000 And p <= 1000000000 Then
        kr = Round(a * p ^ b, 2)
    ElseIf p > 1000000000 Then
        kr = krl
    End If
    End Sub
    Sub 算定式2(p As Currency, a As Single, b As Single, kr As Double _
                , krs As Double, krl As Double)
    If p <= 6000000 Then
        kr = krs
    ElseIf p > 6000000 And p <= 100000000 Then
        kr = Round(a * p ^ b, 2)
    ElseIf p > 100000000 Then
        kr = krl
    End If
    End Sub
    Sub 算定式3(p As Currency, a As Single, b As Single, kr As Double _
                , krs As Double, krl As Double)
    If p <= 10000000 Then
        kr = krs
    ElseIf p > 10000000 And p <= 2000000000 Then
        kr = Round(a * p ^ b, 2)
    ElseIf p > 2000000000 Then
        kr = krl
    End If
    End Sub
    Sub 算定式4(p As Currency, a As Single, b As Single, kr As Double _
                , krs As Double, krl As Double)
    If p <= 300000000 Then
        kr = krs
    ElseIf p > 300000000 And p <= 5000000000# Then
        kr = Round(a * p ^ b, 2)
    ElseIf p > 5000000000# Then
        kr = krl
    End If
    End Sub
    
    
  • FunctionからEnd Subをコピーして、コードウィンドウに貼り付けます。コードウィンドウに貼り付けます

3)関数の実行

  • あらかじめセルA1に工種区分、B1に対象額を入力しておいてください。セルC1をアクティブにして、数式バーからfxをクリックします。すると、関数の挿入ダイアログボックスが表示され、関数の入力を促します。ここで、関数の分類からユーザー定義が追加されている事を確認してください。その中に、作成した関数krがあるはずです。選択後、OKをクリックしてください。ユーザー定義が追加されている
  • 引数の入力ボックスに変わりましたら、セルA1に工種区分が入力されていますので、引数にセル範囲を指定します。同様に、対象額はセルB1を指定します。その状態で、入力ボックス下欄に数式の結果が返されるのを確認してください。引数の入力ボックス
  • 確認後OKをクリックすると、アクティブセルに共通仮設費率が返されます。引数の入力で、工種区分、対象額を直接入力しても何ら問題ありません。アクティブセルに共通仮設費率

≫ダウンロード

共通仮設費率、現場管理費率、一般管理費等率、イメージアップ経費率がコーディングされた土木工事諸経費率計算関数(ノンプロテクト版)を公開しますのでご利用ください。但し、メールウエアとしますので継続利用される方は、必ず、送信フォームから一言感想をお願いします。プログラムの改変は自由ですが、著作権表示は必ず残してください。

土木工事諸経費率計算関数(keihi.zip)

EXCEL活用テクニック