エクセルの関数を自分で作る (tp0501)

エクセルの関数を自分で作成する方法を説明します

エクセル VBA ユーザー関数入門

ここでは、セルの [塗り潰しの色] を返すユーザー関数などを作ります

(1) ユーザー関数を作るということ

VBAの世界では [Functionプロシジャ] と呼ばれます
エクセルに標準で組み込まれていない機能を独自に作成することができます、ただしエクセルの標準関数でできることは、わざわざ作らずに標準関数を使いましょう
ワークシート上から自分が作成したユーザー関数を呼び出すことができます、数式では複雑すぎる場合などにもユーザー関数は便利です
マクロはマクロを実行することで起動しますが、ユーザー関数はワークシート上から関数の形で呼び出し、呼び出したセルに答えを返す だけです、従って隣のセルに何かするといったことはできません

(2) 開発タブを表示させる

エクセルは標準のままでは VBA 開発に必要な [開発] タブが表示されません、まずリボンに [開発] タブを表示させる方法を説明します

[ファイル]メニュー → 一番下の右側にある [オプション] を選択します
左側下の方の [リボンのユーザー設定] をクリックします
右側にある[リボンのユーザー設定(B)] → □[開発] の左側の □ にチェックを入れます
右下にある[OK]ボタンを押します
[開発] タブが表示されます

Excel2007の場合：

[Officeボタン] → 一番下[Excelのオプション] → [基本設定]→[Excelの利用に関するオプション] → [開発タブをリボンに表示]にチェックを入れる

(3) VBE の世界へ

[開発]タブ → [コード]グループ → [Visual Basic] をクリックします
これでVBEの世界に入ります
[挿入(I)]メニュー → [標準モジュール(M)] を選択します
これで新しい [標準モジュール] が準備できました、右側の白い部分にコードを書いてゆきます

(4) 標準モジュール側にコードを書く

標準モジュールに下のコードを入力します

Function cc(adr)
	cc = adr.Interior.ColorIndex
End Function

作成上の注意：
- 基本的に半角英文字で入力します
- 間が空いている部分は半角スペースです
- ccの前は Tabキーです
- 上のコードを選択してコピーして、標準モジュール側に貼り付けて構いません
完成イメージ
解説：
- 1行目と3行目：cc という名前の Function を宣言しています
- 2行目：cc = adr.Interior.ColorIndex では adrという場所(セル)のセルの塗り潰しの色 を関数の答えとして渡しています

(5) ワークシート側

作った関数を呼び出すワークシート側を準備します

セルA1 の [セルの塗り潰しの色] を薄い緑に
セルB1 に
```
=cc(A1)
```
と入力します
これでワークシートからユーザー関数 cc が呼び出され、セルB1 に 43 が表示されます。← (セルA1の [塗り潰しの色] が薄い緑なので 43)
[標準の色]の色番号
- 濃い赤：3
- 赤：3
- オレンジ：44
- 黄：6
- 薄い緑：43
- 緑：14
- 薄い青：33
- 濃い青：49
- 紫：47

(6) ワークブック保存

ブックを保存すると書類のアイコンはびっくりマークのついた「マクロ有効ブック xlsm」になります

(7) 応用編・赤い文字のセルだけ合計する [font-red]

この例では、ユーザー関数 fc に引数を1つ渡しています、ユーザー関数に複数セル範囲(adrs) を渡してその範囲の文字の色が赤いもののを合計を求めます

標準モジュール側

Function fc(adrs)
	sm = 0
	For Each ad In adrs
		If ad.Font.ColorIndex = 3 Then
			sm = sm + ad.Value
		End If
	Next
	fc = sm
End Function

解説：
For Each ad In adrs
...
処理1 ← 複数セル範囲を対象に処理1 を行います
...
Next

If ad.Font.ColorIndex = 3 Then
...
処理2 ← 文字色が赤の時のみ処理2 を実行します
...
End If
ワークシート側
セルA1 に100、セルA2 に200、セルA2 の文字を赤色に、セルA3 に300、
セルA4 に
```
=fc(A1:A3)
```
と入力します
結果：セルA4 に赤い色の合計 200 が表示されます

補足説明：

自分で作らないで使うColor関数・アドインもあります → Color関数 (関数、アドイン)

重要な注意：

この関数ではセルの塗り潰しの色をあとから変更しても、再計算してくれません
エクセルが再計算が必要と判断しないからです
対策として、セル B1の関数式 =cc(A1) を数式バーに表示させて、クリックしてから、[return]キーを押してやる必要があります

(8) 応用編・税金の額を求める [zeikomi]

この例では、ユーザー関数 zaikomi に引数を2つ渡しています、ユーザー関数に金額(king)と税率(ritu) の2つを渡して税込の計算を行います

標準モジュール側

Function zeikomi(king , ritu)
	zeikomi = king * (1 + ritu)
End Function

ワークシート側
セルA1 に 5000 と入力し、セルA2 に 10% の 10 ではなく 0.1 と入力し、
セルA3 に
```
=zeikomi(A1,A2)
```
と入力します
結果：セルA3 に税込の金額 5500 が表示されます

(9) 名前や変数名の付け方

上の例で、zeikomi は勝手に決めた関数の名前で、実は何でも良いわけです
関数名を下↓のように fzc に変更してもOK です、但し king * 1.05 の左側も変えます。これはこの関数の返す値をセットする意味があります
関数を使うワークシート側も =fzc(A1) に変更する必要があります

Function fzc(king,ritu)
	
	fzc = king * (1 + ritu)

End Function

king はこの関数にデータを渡すとき(引数といいます)、そのデータをこれ以降king という名前で使いますよという意味です

(10) インデント・字下げで見た目を整える

コードを見やすくするため、階層が一致しているコードの先頭位置を揃えます
下↓の例ではFunction のF とEnd Function のE If のI とElse のE とEnd If のE を揃えています

Function fzc(king)
|   If king >10000 Then
|   |   fzc = king * 1.05
|   Else
|   |   fzc = king * 1.03
|   End If
End Function

絶対にこうでなくてはいけないわけではなく
参考ページ
[[20040628191821]] 『マクロの体裁について』（川野鮎太郎）
　↑[川野鮎太郎][KAMIYA][INA]さんいつもありがとう

(11) 応用編

VBA サブプロシジャからユーザー定義関数を呼び出して使うこともできます

   Sub test0629()
      kin = 11000
      If fzc(kin) > 11540 Then
         ...
      End If
   End Sub

(12) ユーザー定義関数 (UDF)

このように作って来たのが ユーザー定義関数 です
ユーザー定義関数はプログラマーの間でUDF (User Defined Function) と呼ばれますがその単語はIT用語辞典やMSDNには出てきません
参考ページ
[[20090527094350]] 『UDFとは？』（uuu）

移動更新の経緯

このページは
- 旧ライブラリ赤い文字のセルだけ合計(e1k)
を整理、統合、再編集したものです[2020/07/25]

2024/04/27：更新 2020/07/25：登録訪問者：カウンタ