ユーザー定義関数を活用する
前回はVBAの入門用として、わかりやすい公式を軸に話を展開してみました。今回は、実用にも耐えるコードをひとつ紹介したいと思います。コンピュータの前で電卓を叩いたり、繰り返し行う処理なのに、いつも同じ文献の同じ箇所を参照している。実にばかげた話ですが、無意識のうちにやってしまう事、決して少なくありません。そこで登場するのが、条件分岐を利用した関数のテクニックです。
1)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をクリックすると、アクティブセルに共通仮設費率が返されます。引数の入力で、工種区分、対象額を直接入力しても何ら問題ありません。
≫ダウンロード
共通仮設費率、現場管理費率、一般管理費等率、イメージアップ経費率がコーディングされた土木工事諸経費率計算関数(ノンプロテクト版)を公開しますのでご利用ください。但し、メールウエアとしますので継続利用される方は、必ず、送信フォームから一言感想をお願いします。プログラムの改変は自由ですが、著作権表示は必ず残してください。