VBAのコードを書かなくてもテーブルの入力用フォームが作れる。やることは以下の2つがある。
- テーブル化する
- クイックアクセルツールバーにフォームを追加する。
次図は、テーブル化して「金額」の列に計算式を入れる。
クイックアクセスツールバーにフォームを追加する。
VBAのコードを書かなくてもテーブルの入力用フォームが作れる。やることは以下の2つがある。
次図は、テーブル化して「金額」の列に計算式を入れる。
クイックアクセスツールバーにフォームを追加する。
コードを書いていて、間違いがあるとカーソルを奪われるため「自動構文チェック」のチェックを外す。入力中に間違い承知で書いてから後で正しく修正することもある。「自動構文チェック」が有効だとキーボード操作からマウス操作に変更をさせられる。一瞬だが、思考停止とマウスを戻す操作の繰り返しで余計な時間になる。
次図の例は、「自動構文チェック」が☑になっている状態で、Subプロシージャの名前は数値から始まる名前は使えないためエラーになり、マウス操作が必要になる。
このチェックがなくても、マクロの実行の直前に構文のチェック(コンパイル)をするため「自動構文チェック」は必要ない。
コンパイルは、VBEの「メニュー/デバッグ」でもできる。
コンパイルは、マクロ実行前に必ずするのでコンパイルを意識する必要は無い。
練習:「sub dim」でエラーを確認する。また、「自動構文チェック」を外して確認する。
また、「変数の宣言を強制する」をチェックの状態でも確認する。
設定以降は、追加するモジュールに「Option Explicit」が入る。すでにあるモジュールにも変数の宣言を強制したい場合は、Option ExplicitをGeneral-Declarationsに書き込めばいい。
セルの中にIF文がいくつも入れ子になっていると見にくいし、メンテナンスも難い。次図の例は、英語の点数で優、良、可、不可の評価をしている。優は80点以上、良は70点以上、可は60点以上、不可は60点より少ない。
ワークシート関数は、同じセル番地を何度も指定することがある。この例では、セルB2の値を何度も使っている。
=IF(B2=””,””,IF(B2>=80,”優”,IF(B2>=70,”良”,IF(B2>=60,”可”,”不可”))))
一方、標準モジュールに書く関数は、=優良可(B2)で答えが出る。引数xにB2のセルを1つを渡す。
ワークシート関数のIF関数に慣れているならVBAではIf Then とElseIfを使う。優先する条件を先に書くのはワークシート関数のIFと同じです。
Function 優良可if(x)
If "" = x Then
優良可if = ""
ElseIf x >= 80 Then
優良可if = "優"
ElseIf x >= 70 Then
優良可if = "良"
ElseIf x >= 60 Then
優良可if = "可"
Else
優良可if = "不可"
End If
End Function
Select Caseなら引数xの変化だけをCase 条件で書けるのでシンプルだ。Ifに慣れていると条件の書き方が違うので初めは戸惑います。
Function 優良可(x)
Select Case x 'Select CaseはCaseの右の値だけを判断して上から順に処理する
Case "" 'xが空白文字の場合
優良可 = ""
Case Is >= 80 'xが80以上の場合
優良可 = "優"
Case Is >= 70 'xが70以上の場合
優良可 = "良"
Case Is >= 60 'xが60以上の場合
優良可 = "可"
Case Else 'その他
優良可 = "不可"
End Select
End Function
関数名が漢字なのでセルの中で入力する手間があるので「yrk」などの半角文字の関数名にするといい。
DateSerial関数は引数に(西暦の年,月,日)を入れる。入力が平成20年とか令和2年となると、西暦に変更する必要がある。時図はセルB2に年号を入れる。令和か平成かはセルB1(リストにしている)の内容で分岐処理する。令和は入力の数値に2018を、平成は1988を加算すると西暦になる。得られるデータは1900年1月1日を起点とするシリアル値(連番)です。
IF関数は1つしか使っていないので読みやすい。ユーザー定義関数にすると読みやすさとコメントなどで修正や拡張がしやすい。
やっていることは、DateSerial関数を使ってシリアル値を得る。
Function reiwaDate(y, m, d, 平成令和セル As Range)
If InStr(平成令和セル, "平成") Then '平成令和セルの文字列に平成はあれば
reiwaDate = VBA.DateSerial(y + 1988, m, d)
Else '令和
reiwaDate = VBA.DateSerial(y + 2018, m, d)
End If
End Function
「学問のすすめ」みないな。
Excelの標準モジュールに作った関数は、セルの中で使える。
ユーザー定義関数(自分で作る関数)を作るためには以下の知識が必要です。
SUMIFやVLOOKUPなどのワークシート関数(Excel/エクセルに組み込まれている関数)は、何かやりたいことがあって使うのだが、自分が作ったわけではないので処理内容を確認しながら進める。以下の手順でワークシート関数を使う。
一方、自分で関数を作ることができれば以下の手順で関数を作る。
「???ができるワークシート関数はどれかな?」って探すのではなく、自分で作るので自作関数とかカスタム関数、ユーザー定義関数とか言う。
(プログラムする)利点
(プログラムする)欠点としては、プログラムやVBA関数を知らない人には読めない、修正、引き継ぎが出来ない点。「他の人が修正できないが!」というクレーム。
再計算の対象にするためユーザー定義関数内にApplication.Volatileを挿入する必要がある。一般的に動作が遅くなると言われるが更新されないクレーム回避策になる。
一般的なExcel使いの人の意見でスキルを止めてはいけない。使いやすい関数を提供してあげえればいい。
一般的に、INDEXやVLOOKUPなどを組み合わせるような式は読みにくい。メンテナンスする点からも、「引き継ぎ」に関して大抵は引き継げるほど簡素では無いはずだ。Excelのセルの中のコードを読むのって難しい。じゃない?
比較的簡単でセルの中を見れば処理内容が分かる程度ならExcelのワークシート関数を使う。どちらを使うかの判断は、「どちらが読みやすいか」だが、練習の場合は単純でもユーザー定義関数を作ればいい。
SUMというワークシート関数があるが、あえて自作のmySUM関数を作る。
セルの範囲を引数で受け取る場合のユーザー定義関数の基本的な書き方は以下のようになる。
Function 関数名(引数名 As Range)
Dim v
For Each v In 引数名 '引数に複数のセルが入っていることを前提にしている。
処理コード
Next
End Function
ワークシート関数にSum関数がある。これと同じような関数をユーザー定義関数で書く。
Function mySUM(範囲 As Range)
Dim v As Variant ’As Variantは省略しても同じ意味
For Each v In 範囲
mySUM = mySUM + v
Next
End Function
範囲のセルの値を変えたらmySUMの値も変わることを確認する。次のコードは合計数と最大値を文字列にして返す。
Function mySUM2(範囲 As Range)
Dim s As String, i As Integer
ReDim ar(範囲.Count) As Double 'ワークシート関数のMaxを使うための配列
For i = 1 To 範囲.Count
mySUM2 = mySUM2 + 範囲(i)
ar(i - 1) = 範囲(i) '配列にも入れる
Next
s = "合計は" & mySUM2 & "です。最大値は" & WorksheetFunction.Max(ar) & "です。"
mySUM2 = s
End Function
練習:myKake関数を作る。掛け算をする関数。ただし、セルの中の””は計算しないが0が入っている場合は計算する。
まず、下記のようなコードを思い付くと思う。myKakeの初期値がEmptyというバリアント型特有の値でたいていは0として扱われ永遠に0の結果を返す関数になる。
Function myKake(r As Range)
Dim v
For Each v In r
myKake = myKake * v
Next
End Function
対応が必要なのはセルが空白文字や文字列、myKakeがEmptyの時。ElseIfまで必要になる。
Function myKake(r As Range)
Dim v
For Each v In r
If "" = v Then '""の空白は何もしない
ElseIf VBA.IsNumeric(v) Then '数値の場合
If VBA.IsEmpty(myKake) Then 'まだmyKakeがEmptyの場合は1で掛ける
myKake = 1 * v
Else
myKake = myKake * v '通常の掛け算
End If
End If
Next
End Function
条件分岐が複数になると試行錯誤、条件が5,6個程度ならコードを先に書いて確認する方が早い。