以下を調査します。
エクセルが何をしているのか？。特に、入力と表示結果の違いはマクロ記録も見るとよくわかる？
しかし、
マクロ記録は、VBAのコードを書くときは以下の内容を確認するのがほとんでしょう。
転記となるとマクロ？。危なっかしいというかSubプロシージャを書くのにパワーがいる。何が難しいのかというとセルの位置の指定ですよね。一方、ワークシート関数は、セルに埋め込むので目的のセルにデータを入れる。確実に指定のセルに入れる？表示することができる。
エクセルのワークシート関数を使うならINDEX関数？なんかな。
ここではユーザー定義関数/カスタム関数/自作関数であるmyIndex関数を作ります。INDEX関数とは関係ありません。
例題、テスト結果の表と受講台帳がある。この２つの表から合格者の表を作る。表の項目は、クラス番号、出席番号、名前、各科目の点数とする。
これがすぐできる人は天才です。私は、「困難は分割せよ」、「数が多い時は少なくして、データの桁が大きい時は小さくして考える」ということを心がけます。「困難は分割せよ」の方は有名かな？。２つ目は経験から。プログラムではこの考えで複数の関数を作る。
ここでは、合格者の配列を返す関数myGoukakuを作る。これは、myTemplateを参考にしてます。
'とにかく合の人だけを返す
Function myGoukaku(rTest As Range)
Dim ar() 'Rangeの範囲のデータを入れる動的配列
ar = rTest 'セルの値が入る
Dim i As Integer, j As Integer
j = 1 'For文の中で使い始めで0だと飛ぶため。
ReDim arr(1 To UBound(ar, 1), 1 To UBound(ar, 2)) 'データ以上になることは無い
Dim ii As Integer 'arrの行番号用、列はjを使う
For i = 1 To UBound(ar, 1) '２次元配列の１番目、行。１から始めるのはｒがRangeだから
If "合" = ar(i, 6) Then '６列目に合か否かがある
ii = ii + 1 'スタートが１なので先に１にしておく
For j = 1 To UBound(ar, 2) '２次元配列の２番目、列
arr(ii, j) = ar(i, j)
Next
End If
Next
myGoukaku = arr
End Functionその２では、合格者名と一致する受講台帳の行の配列を返す関数です。
Sub テスト表（）プロシージャを実行する前に、以下の関数を先に標準モジュールに貼り付けます。
Function hanntei(setu, tori, hou)
If setu + tori + hou ＜180 Then
hanntei = "不"
Else
hanntei = "合" '以下の判定前にとりあえず合
If setu ＜ 40 Then hanntei = "不" 'Thenの内容が簡素なら一行の方が見やすい
If tori ＜40 Then hanntei = "不"
If hou ＜ 40 Then hanntei = "不"
End If
End Functionテスト表作成のマクロ。上の関数を使うので先に上の関数を貼り付けて下さい。以下のマクロも標準モジュールに貼り付け実行します。
Sub テスト表()
If VBA.MsgBox("選択しているワークシートに上書きします！！！", vbOKCancel) = VBA.vbCancel Then Exit Sub
If ActiveSheet.ListObjects.Count = 1 Then ActiveSheet.ListObjects(1).Delete
Range("A1") = "名前"
ActiveCell.Characters(1, 2).PhoneticCharacters = "ナマエ"
Range("B1") = "設備"
ActiveCell.Characters(1, 2).PhoneticCharacters = "セツビ"
Range("C1") = "取扱"
ActiveCell.Characters(1, 2).PhoneticCharacters = "トリアツカイ"
Range("D1") = "法規"
ActiveCell.Characters(1, 2).PhoneticCharacters = "ホウレイ"
Range("E1") = "判定"
ActiveCell.Characters(1, 2).PhoneticCharacters = "ハンテイ"
Range("A2") = "名前１"
ActiveCell.Characters(1, 2).PhoneticCharacters = "ナマエ"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A８"), Type:=xlFillDefault
Range("A2:A10").Select
Range("B2") = "56"
Range("C2") = "87"
Range("D2") = "87"
Range("B3") = "76"
Range("C3") = "76"
Range("D3") = "89"
Range("B4") = "54"
Range("C4") = "67"
Range("D4") = "34"
Range("B4") = "90"
Range("B5") = "89"
Range("C5") = "78"
Range("D5") = "67"
Range("B6") = "56"
Range("C6") = "45"
Range("D6") = "67"
Range("B7") = "48"
Range("C7") = "68"
Range("D7") = "43"
Range("B8") = "23"
Range("C8") = "67"
Range("D8") = "87"
Range("C8") = "98"
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1") = "No"
Range("A2") = "1"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A8"), Type:=xlFillSeries
Range("A2:A8").Select
Range("D5").Select
Application.CutCopyMode = False
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$F$8"), , xlYes).Name = _
"テーブル3"
Range("F2") = "=hanntei([@設備],[@取扱],[@法規])"
End Sub受講生台帳の作成
Sub 受講生台帳()
If VBA.MsgBox("選択しているワークシートに上書きします！！！", vbOKCancel) = VBA.vbCancel Then Exit Sub
If ActiveSheet.ListObjects.Count = 1 Then ActiveSheet.ListObjects(1).Delete
Range("A1") = "No"
Range("A2") = "1"
Range("A2").AutoFill Destination:=Range("A2:A8"), Type:=xlFillSeries
Range("B1") = "クラス"
Range("B2") = "1"
Range("B3") = "2"
Range("B4") = "1"
Range("B5") = "1"
Range("B6") = "2"
Range("B7") = "1"
Range("B8") = "2"
Range("C1") = "出席番号"
ActiveCell.Characters(1, 4).PhoneticCharacters = "シュ"
Range("C2") = "1"
Range("C3") = "1"
Range("C4") = "3"
Range("C5") = "2"
Range("C6") = "2"
Range("C7") = "4"
Range("C8") = "3"
Range("d1") = "名前"
Range("d2") = "名前１"
ActiveCell.Characters(1, 2).PhoneticCharacters = "ナマエ"
Range("d2").Select
Selection.AutoFill Destination:=Range("d2:d８"), Type:=xlFillDefault
Range("E1") = "住所"
ActiveCell.Characters(1, 2).PhoneticCharacters = "ジュウショ"
Range("E2") = "住所１"
ActiveCell.Characters(1, 2).PhoneticCharacters = "ジュウショ"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E8")
Range("E2:E8").Select
Range("D3").Select
Application.CutCopyMode = False
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$E$8"), , xlYes).Name = _
"テーブル12"
Range("テーブル12[#All]").Select
End Sub現在のExcelは、ファイルの拡張子がxlsmでないとマクロが消えてしまう。うっかりxlsxで保存して作ったマクロが無くならないように保存するファイルの種類を常に「Excelマクロ有効ブック(*.xlsm)」にする。Excel本体のメニュー/ファイル/オプションで設定する。
この設置で新規にExcelを起動して保存すると、拡張子がxlsmに変わる。なお、Excel97-2003のブックは同じ拡張子でマクロが保存でる。
昔は良かった。ファイルを見ただけではマクロを書いているのがわからない。「だから危険だったんよね」
ワークシート関数IFが使えるということは「条件分岐」OKです。ならVBAでIf Thenも書けます。
優良可の第二弾。IF関数と比較もします。
問題：設備、取扱、法規の３つの問題があり全体の合計が６割以上で合格。ただし、３つのどれか40より低い点数があると不合格になる。
以下は数式バーに書いたIF関数の計算式です。テーブルを使っているのでセル番地ではなく項目名になります。それにしてもセルの中に入れる時に何度もセルの指定が必要ですね。
=IF(SUM(テーブル[@[設備]:[法規]])<180,”不”,IF([@設備]<40,”不”,IF([@取扱]<40,”不”,IF([@法規]<40,”不”,”合”))))
以下は、標準モジュールに書いたユーザー定義関数/カスタム関数/自作関数を使っている状態、数式バーの状態です。セルの指定は３つですから使いやすい。
=hanntei([@設備],[@取扱],[@法規])
以下を標準モジュールに書いています。文書になっているので読みやすい。コメントも簡単に書けます。
Function hanntei(setu, tori, hou)
If setu + tori + hou < 180 Then
hanntei = "不"
Else
hanntei = "合" '以下の判定前にとりあえず合
If setu < 40 Then hanntei = "不" 'Thenの内容が簡素なら一行の方が見やすい
If tori < 40 Then hanntei = "不"
If hou < 40 Then hanntei = "不"
End If
End Function書いたユーザー定義関数がなくならないように、マクロが有効なブックで保存が必要です。
以下は、サンプルの表の作成とIF関数の入力をするマクロ
Sub 表作成()
If VBA.MsgBox("選択しているワークシートに上書きします！！！", vbOKCancel) = VBA.vbCancel Then Exit Sub
If ActiveSheet.ListObjects.Count = 1 Then ActiveSheet.ListObjects(1).Delete
Range("A1").Select
ActiveCell.FormulaR1C1 = "名前"
ActiveCell.Characters(1, 2).PhoneticCharacters = "ナマエ"
Range("B1").Select
ActiveCell.FormulaR1C1 = "設備"
ActiveCell.Characters(1, 2).PhoneticCharacters = "セツビ"
Range("C1").Select
ActiveCell.FormulaR1C1 = "取扱"
ActiveCell.Characters(1, 2).PhoneticCharacters = "トリアツカイ"
Range("D1").Select
ActiveCell.FormulaR1C1 = "法規"
ActiveCell.Characters(1, 2).PhoneticCharacters = "ホウレイ"
Range("E1").Select
ActiveCell.FormulaR1C1 = "判定"
ActiveCell.Characters(1, 2).PhoneticCharacters = "ハンテイ"
Range("A2").Select
ActiveCell.FormulaR1C1 = "名前１"
ActiveCell.Characters(1, 2).PhoneticCharacters = "ナマエ"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A８"), Type:=xlFillDefault
Range("A2:A10").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = "56"
Range("C2").Select
ActiveCell.FormulaR1C1 = "87"
Range("D2").Select
ActiveCell.FormulaR1C1 = "87"
Range("B3").Select
ActiveCell.FormulaR1C1 = "76"
Range("C3").Select
ActiveCell.FormulaR1C1 = "76"
Range("D3").Select
ActiveCell.FormulaR1C1 = "89"
Range("B4").Select
ActiveCell.FormulaR1C1 = "54"
Range("C4").Select
ActiveCell.FormulaR1C1 = "67"
Range("D4").Select
ActiveCell.FormulaR1C1 = "34"
Range("B4").Select
ActiveCell.FormulaR1C1 = "90"
Range("B5").Select
ActiveCell.FormulaR1C1 = "89"
Range("C5").Select
ActiveCell.FormulaR1C1 = "78"
Range("D5").Select
ActiveCell.FormulaR1C1 = "67"
Range("B6").Select
ActiveCell.FormulaR1C1 = "56"
Range("C6").Select
ActiveCell.FormulaR1C1 = "45"
Range("D6").Select
ActiveCell.FormulaR1C1 = "67"
Range("B7").Select
ActiveCell.FormulaR1C1 = "48"
Range("C7").Select
ActiveCell.FormulaR1C1 = "68"
Range("D7").Select
ActiveCell.FormulaR1C1 = "43"
Range("B8").Select
ActiveCell.FormulaR1C1 = "23"
Range("C8").Select
ActiveCell.FormulaR1C1 = "67"
Range("D8").Select
ActiveCell.FormulaR1C1 = "87"
Range("C8").Select
ActiveCell.FormulaR1C1 = "98"
Range("B2").Select
Application.CutCopyMode = False
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$e$８"), , xlYes).Name = _
"テーブル"
Range("e2").Select
ActiveCell.FormulaR1C1 = _
"=IF(SUM(テーブル[@[設備]:[法規]])<180,""不"",IF([@設備]<40,""不"",IF([@取扱]<40,""不"",IF([@法規]<40,""不"",""合""))))"
'エラーになったら＜の4箇所を半角文字にして下さい。
End Sub配列数式で扱う２次元配列の処理は、For Eachで処理することが多いが初めから配列に入れて処理する方法もある。
'配列にRangeのデータを入れる
Function aa(r As Range)
Dim ar()
ar = r 'セルの値が入る
Dim i As Integer, j As Integer
For i = 1 To UBound(ar, 1) '２次元配列の１番目、行。１から始めるのはｒがRangeだから
For j = 1 To UBound(ar, 2) '２次元配列の２番目、列
ar(i, j) = i & "," & j & "," & ar(i, j)
Next
Next
aa = ar
End FunctionFor i=1から始まることに注意。配列の要素番号は0から始まるのが普通だが、動的配列ar()にRangeを入れると要素番号/インデックス番号は1から始まる。
データベースのような表からデータの取り出しは行単位になる。ある条件で取り出して別の配列arrに入れる。行単位に処理するのでFor文の列の処理の前にIf条件文を入れることになる。
'配列にRangeのデータを入れる
Function myTenplate(r As Range)
Dim ar() 'Rangeの範囲のデータを入れる動的配列
ar = r 'セルの値が入る
Dim i As Integer, j As Integer
j = 1 'For文の中で使い始めで0だと飛ぶため。
Dim arr(1 To 100, 1 To 10) '関数値用。スタートを１にして列のjをそのまま使う。1000くらいにやると重く、飛ぶことも。
Dim ii As Integer 'arrの行番号用、列はjを使う
For i = 1 To UBound(ar, 1) '２次元配列の１番目、行。１から始めるのはｒがRangeだから
If i = 1 Or i = 3 Then '条件、１と３の行だけ
ii = ii + 1 'スタートが１なので先に１にしておく
For j = 1 To UBound(ar, 2) '２次元配列の２番目、列
arr(ii, j) = i & "," & j & "," & ar(i, j)
Next
End If
Next
myTenplate = arr
End Function２次元配列の処理は頭の中で処理できるほど簡単では無い。難しくしているのがi,jなどの変数とそれが位置を意味しており、ar(i,j)で一つのデータ。２次元配列の音からデータが２つあると勘違いしてはいけない。私だけかもしれないが。
時間はかかってもワークシート関数を駆使して「やっぱできない」ってことはプログラムの世界ではありませんし、使った時間はスキルアップになります。