ユーザー定義関数を活用する
前回はＶＢＡの入門用として、わかりやすい公式を軸に話を展開してみました。今回は、実用にも耐えるコードをひとつ紹介したいと思います。コンピュータの前で電卓を叩いたり、繰り返し行う処理なのに、いつも同じ文献の同じ箇所を参照している。実にばかげた話ですが、無意識のうちにやってしまう事、決して少なくありません。そこで登場するのが、条件分岐を利用した関数のテクニックです。
１）Visual Basic Editorの起動
- エクセルのメニューバーから、［ツール］⇒［マクロ］⇒［Visual Basic Editor］を選択します。
- Visual Basic Editorが起動したら、メニューバーから、［挿入］⇒［標準モジュール］を選択します。ここで、プロジェクトエクスプローラにModule1が作成されたことを確認してください。この部分に、functionプロシージャのコードを書くことによって、ユーザー定義関数が使えるようになります。
２）コーディング
- 下記の例は共通仮設費率を計算するためのコードです。個々の命令文の詳細については、専門書や、Excelのヘルプ等を参照してください。まず、共通仮設費率を算出する際の条件は、２１種類の工種区分と３種類の対象額の範囲による区分があります。そこで、複数の条件判断ができるＩｆステートメントを利用して、工種区分を選択し、対象額の範囲をサブルーチンに委ねるというプログラムにより計算をおこないます。では、そのコードの構成を見ていきます。先頭行の（ ）内に示される引数は、工種区分と対象額の２種類で、この種類や範囲によって共通仮設費率が決まります。ここで、河川工事を例に挙げれば、Ｉｆステートメントの条件の部分、『もし～ならば処理Ａをしなさい』の～の部分が"河川工事"です。処理Ａはそれ以下のステートメントで、kr、a、b、の定数及びサブルーチン『算定式1』になります。Ｉｆステートメントにより、実際に実行されるステートメントは、２０行程度でしかないのは一目瞭然です。コードは長くなりましたが、内容は実に簡単なものとなります。実務において、同一作業の中で工種区分が変更になる事は、それほど多くはありません。しかし、一度作成したファイルの再使用は頻繁にあり、ミスは起こりうる事ですので、煩わしい作業はなるべく省略したいものです。
例 共通仮設費率の計算 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 工種区分 = "Ｐ・Ｃ橋工事" 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 工種区分 = "共同溝等工事（１）" Then krs = 8.86 a = 68.3 b = -0.1267 krl = 4.53 算定式3 p, a, b, kr, krs, krl ElseIf 工種区分 = "共同溝等工事（２）" 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 工種区分 = "下水道工事（１）" Then krs = 12.85 a = 422.4 b = -0.2167 krl = 4.08 算定式3 p, a, b, kr, krs, krl ElseIf 工種区分 = "下水道工事（２）" Then krs = 13.32 a = 485.4 b = -0.2231 krl = 4.08 算定式3 p, a, b, kr, krs, krl ElseIf 工種区分 = "下水道工事（３）" 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をコピーして、コードウィンドウに貼り付けます。
３）関数の実行
- あらかじめセルA1に工種区分、B1に対象額を入力しておいてください。セルC1をアクティブにして、数式バーからｆｘをクリックします。すると、関数の挿入ダイアログボックスが表示され、関数の入力を促します。ここで、関数の分類からユーザー定義が追加されている事を確認してください。その中に、作成した関数ｋｒがあるはずです。選択後、ＯＫをクリックしてください。
- 引数の入力ボックスに変わりましたら、セルA1に工種区分が入力されていますので、引数にセル範囲を指定します。同様に、対象額はセルB1を指定します。その状態で、入力ボックス下欄に数式の結果が返されるのを確認してください。
- 確認後ＯＫをクリックすると、アクティブセルに共通仮設費率が返されます。引数の入力で、工種区分、対象額を直接入力しても何ら問題ありません。
≫ダウンロード
共通仮設費率、現場管理費率、一般管理費等率、イメージアップ経費率がコーディングされた土木工事諸経費率計算関数（ノンプロテクト版）を公開しますのでご利用ください。但し、メールウエアとしますので継続利用される方は、必ず、送信フォームから一言感想をお願いします。プログラムの改変は自由ですが、著作権表示は必ず残してください。