11本目「結合セルのみコメント挿入」を学ぶ
お題
ファイルはこちらから
基礎知識
MergeCellsとMergeArea
セルが結合されているかどうかの判定にはMergeCellsを用いる。
今回のお題では、コメントで結合セルに警告を表示するため、
その結合セルの判定に使用しています。
if Range.MergeCells then ' そのセルが結合されているなら
〜〜
Range.Comment / .ClearComments / .AddComment
Range.Comment 対象セルにコメントがあるかないか
Range.ClearComments 対象セルのコメント全消し
Range.AddComment "a" 対象セルにaとコメント挿入
Excel 2016では、コメントのTextFrame(およびTextFrame2)プロパティは読み取り専用です。
***回答
Sub odai11() Dim ws As Worksheet Set ws = ActiveSheet Dim rng As Range For Each rng In ws.Range("A1").CurrentRegion If rng.MergeCells Then If rng.Address = rng.MergeArea.Item(1).Address Then If Not rng.Comment Is Nothing Then rng.ClearComments End If rng.AddComment " セル結合ダメ" 'rng.Comment.Shape.TextFrame.AutoSize = True rng.Comment.Visible = True End If End If Next End Sub||<
10本目「条件付きでの行削除」を学ぶ
基礎知識
行削除 For ~~ Step
今回のお題では、行の削除を最下段から行います。その理由を説明します。
たとえば
1〜6行の表があったとします。そこで2(i)行目、3( i + 1 )行目・・を削除としていきます。
するとエクセルは、消された2行目を詰めてこんな感じになります。
1、2、3、4、5、6 の並びが、
↓ 2( i ) 行目削除
1、3、4、5、6、空 の並びに ここまではOK
↓ 3( i +1) 行目削除
1、3、5、6、空、空 の並びに 3より先に4が消えてしまった!
つまり、For文で上から行をインクリメントしていくと、とびとびで行が削除されてしまうのです。
そのため、削除に関しては、表の下からFor文で回します。iをデクリメントしていきます。
For i = 9999 To 1 Step -1
Next
↑デクリメントはStepを用いてこのように記述します。
プロシージャとモジュールについて
これに関しては今回特別にでてきたものではありませんが。一度、早い段階で知識を整頓しておいたほうが良いとかんじたのでまとめます。
まずは、プロシージャです。
これはSub () ~~~End Subなどのマクロを実行できる最小の実行単位です。
そして、モジュールはこのプロシージャを記述するための場所のことです。
モジュールには様々種類があります。
シートモジュール、ブックモジュール、標準モジュール、Windowsならフォームモジュールがあります。
これまで全てのお題は、この標準モジュールに記述しています。その中にプロシージャを作成し、マクロを実行しています。
プロシージャの中で使える変数
例えば、標準モジュールのモジュール1にtest1,test2二つのプロシージャを記述します。
モジュール1はマクロ処理単位を2つもっている状態です。
test1のプロシージャ内で宣言した変数(文字列型のa)は、test1のプロシージャ内でのみ有効です。
この場合、test2では「文字列a ?そんな変数宣言されていないよ?」とエラーが出てしまいます。
Sub test1() Dim a as String a="macro" End Sub Sub test2() Msgbox a //実行できずエラーが返されます End Sub
では、複数のプロシージャで同じ変数を共有したい場合はどうするかというと、プロシージャ外の宣言セクションで宣言すればいいんです。
実際に記述してみるとこんな感じ
Option Explicit Dim a As String. '宣言セクション モジュールレベル変数(以下モジュール内のプロシージャでどこでも使える) '-------------------------------- Sub test1() a="macro" End Sub Sub test2() Msgbox a //実行可能 End Sub
これでtest1→test2と実行していけば、メッセージボックスがでて「macro」と一言つぶやくマクロができました。
では他のtest3というプロシージャがあったとしたとき、そこからこのtest1,test2を呼び出すには、つまり二つの小っちゃなマクロ処理を合体して、test3プロシージャーから呼び出すにはどうすればよいか、考えます。
call
文字通り、マクロ名を指定してマクロを呼び出す機能がCallです。
実際に使ってみると
Option Explicit Dim a As String. '宣言セクション '-------------------------------- Sub test1() a="macro" End Sub Sub test2() Msgbox a //実行可能 End Sub ’ 以下を追加 Sub test3() Call test1 'マクロ名はSub ○()の○の文字列 Call test2 End Sub
これでtest1,test2 の順にマクロが実行されます。
今回はどちらも短い処理なので有り難みがほぼないですが、このtest1,test2のマクロ処理がそれぞれ複雑な場合、
Callは非常に便利です。どんだけ小分けしたマクロの機能が複雑になろうとも、test3のマクロの内容はずっとこのままです。
あくせく働かされるのはtest1,2で、test3は「まずtest1やってー、おわったらtest2も頼むねー」と中身も見ずに命令するだけです。test3がいやらしい上司に見えてきました、まるで社会の構図のようです笑
要は極端な話、test3で必要なマクロを全て呼び出せば、「test3マクロを一つだけ実行して全て済む」ということです。業務ぽちっと自動化計画の道がまた一歩大きく切り開かれました^^
Callは革命的に便利だと思います。ですが、ただやみくもに機能を小分けしてもよくありません。あくまで何らかの大きな機能を実現するために、全体の流れを掌握しやすくするために意図、目的をもって必要処理をまとめます。(そういう意味でこの例に挙げた処理は、Callするに相応しくは無いです。)
マクロ実行後の元に戻る
※マクロは実行後にCtr+Z(元に戻る)機能が使えません(超大事)。そのため、処理前シートを別シートにペーストして一旦逃しておくことは大変重要かと思います。ということで、お題にはありませんでしたが、Call及び宣言セクションでの定義の復習の意味も込めて、今回は処理前のバックアップの機能を作成し、呼び出します。
回答
Option Explicit Dim ws As Worksheet ’宣言セクションでモジュール変数を定義 ’ーーーーーーーーーーーーーーーーーーーーー Sub 行削除() Application.ScreenUpdating = False Set ws = Worksheets("受注") Call バックアップ Dim i As Long For i = ws.Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1 If ws.Cells(i, 3) = "" Then If ws.Cells(i, 4).Value Like "*不要*" Or _ ws.Cells(i, 4).Value Like "*削除*" Then ws.Rows(i).Delete End If End If Next Application.ScreenUpdating = True End Sub ’ーーーーーーーーーーーーーーーーーーーーーー Sub バックアップ() Dim wsBak As Worksheet Set wsBak = Worksheets.Add(after:=ws) On Error Resume Next Application.DisplayAlerts = False Worksheets(ws.Name & "(bak)").Delete Application.DisplayAlerts = True On Error GoTo 0 wsBak.Name = ws.Name & "(bak)" ws.Range("A1").CurrentRegion.Copy wsBak.Range("A1") End Sub
バックアップ機能が別枠で下に持ってきた為、本題のFor文の処理が読みやすく、可読性が少しよくなった気がします。
今後もこの視点は忘れず持ちながらプログラムを組みたいです。
学んだこと総括
行の削除は下から For 〜〜 Step -1
処理前シートを別シートに逃しておく バックアップ作成(マクロ実行後は戻れない為)
モジュールで共有する変数は、宣言セクションで宣言
Call マクロ名 で好きなタイミングで別プロシージャ処理を呼べる
9本目「フィルターコピーとシートの作成」を学ぶ
基礎知識
AutoFilter
オートフィルターをVBAで記述できると、ボタンぽちっと一手間で、絞り込み結果全てを別シートに移したり、
絞り込み結果のみを別シートに移したりできるようになります。絶対に押さえておきたいテクニックです!
Range.AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown)
引数について
Field
フィールド番号は、リストの一番左側から、1、2、3・・・と番号付与されています。
今回の例で例えると、仮にセル領域の7列目合格でフィルターをかけたければ
〜〜.AutoDilter Field:=1 Criteria1:="合格"
というように使用します。
空白で絞り込み の結果 Criteria1=”=”
Sub filter実験1空白() Dim ws As Worksheet Set ws = Worksheets("成績表") ws.Range("A1").AutoFilter field:=7, Criteria1:="=" '空白で絞り込み End Sub
合格(完全一致)で絞り込み→ フィルターモード解除 の結果
Sub filter実験2合格() Dim ws As Worksheet Set ws = Worksheets("成績表") ws.Range("A1").AutoFilter field:=7, Criteria1:="合格" '合格で絞り込み End Sub
「三」を含む文字列のみ絞り込み
Sub filter実験4三含む() Dim ws As Worksheet Set ws = Worksheets("成績表") ws.Range("A1").AutoFilter field:=1, Criteria1:="*三*" '文字列内に三を含む End Sub
「三」で始まる文字列のみ絞り込み
Sub filter実験4三で始め() Dim ws As Worksheet Set ws = Worksheets("成績表") ws.Range("A1").AutoFilter field:=1, Criteria1:="三*" '文字列が三で始まる End Sub
Worksheets.Add Name
ワークシートを追加する処理、シート名を変更する処理です。
Worksheets.Add Before, After(, Count, Type)
例題1:シートの最初にシート追加
Set ws = Sheets.Add(Before:=Sheets(1)) ws.Name="○○"
例題2:シートの最後にシート追加
Set ws = Sheets.Add(After:=Sheets(Sheets.Count)) ws.Name="○○"
Application.DisplayAlerts
シートを削除する際などに、エクセルから警告がなされます。
その表示のたびに毎回「はい」を選択させられては、マクロで自動化する意味がありません。そのため今回のお題では、シートを削除するときだけ、 Application.DisplayAlertsを一時的に切って、
エラーが想定される処置が終わった後に、Application.DisplayAlertsを復元する(復元しておかないと、マクロ実行した後も、エクセル自体の設定でアラートがオフ設定のままになる)。
Application.DisplayAlerts=false //アラートOFF
シートを消す処理
Application.DisplayAlerts=true //アラートON
そのため、実際にはこのように挟み込んで使うことになることが多いです。
On Error Resume Next
こちらは、過去のお題で一度使用しました。前回はSpecialCellsで「定数値のみClearContents」する際に定数セルでないとエラーがでて処理が停止します。その際にエラー出てもとばしちゃって、というこの命令を使いました。参考に
beginners.hatenablog.jp
今回のお題で、仮にこのエラー処理をコメントアウトし、かつ削除対象の合格シートがない、というエラー項目を残して敢えて実行してみると。。
「ないものは消せませんよ」とちゃんと怒られました!そりゃそうだ。^^;
エクセル君は本当にいつも優秀です。ただ今回のエラー内容に関しては許容していますので、「合格シートがもともと無いんやったら、これは無視して次の処理から再開してちょうだい(Resume Next)」と伝えています。
過去のお題ではプロシージャの終了(End Sub)までこれを有効にしていました。しかし、今回はその必要がないため、シート削除後にエラーの設定を元に戻しておきましょう。
On Error GoTo 0
こちらで戻せます。つまりここまでをまとめると
//ないものは消せませんって言って止まらないで
On Error Resume Next
//削除しますかってメッセージボックスで確認しないで
Application.DisplayAlerts = False
//削除して
Worksheets("合格者").Delete
//この行以降は、メッセージボックスで確認してきて
Application.DisplayAlerts = True
//これ行以降は、ないものがあったらエラー出して
On Error GoTo 0
言葉で書くとくどいですけど、こんな感じなのかなと解釈しました。
回答
回答はこちら
Sub お題9() Dim wsIn As Worksheet Dim wsOut As Worksheet On Error Resume Next Application.DisplayAlerts = False Worksheets("合格者").Delete Application.DisplayAlerts = True On Error GoTo 0 Set wsIn = Worksheets("成績表") Set wsOut = Worksheets.Add(after:=wsIn) wsOut.Name = "合格者" wsIn.AutoFilterMode = False With wsIn.Range("A1").CurrentRegion .AutoFilter field:=7, Criteria1:="合格" .Columns(1).Copy wsOut.Range("A1") End With wsIn.AutoFilterMode = False End Sub
学んだこと総括
AutoFilterの使い方(基礎の基礎)
Field:=領域左から1
Criteria1:="完全一致" "*含む*" "始め!*" "="(空白セル) ”<>”(空白で無いセル)
マクロのエラー飛ばし On Error Resume Next ーOn Error GoTo 0
エクセルの警告切り Application.DisplayAlerts = False(※シート削除時にDeleteとセットで忘れないようにする)
8本目 「点数の合否判定」を学ぶ
必要な基礎知識(マクロ)
intersect
複数のセル範囲の共有セル範囲を表すRangeオブジェクトを返します。
[Application.]Intersect(Arg1, Arg2[, Arg3, ・・・, Arg30])
[]内省略可能
Resize
Range.Resize(RowSize, ColumnSize)
サイズ変更後のRangeオブジェクトが返されます。
RowSize,ColumnSizeは省略すると、変更前のサイズが入ります。
略記の仕方
例1)行を3行に、列数はそのままにしたい処理
Resize(3)
例2)行数はそのままに、列数を5にしたい処理
Resize(,5)
For each ~ In - .Rows
行ごとに処理を実行する
With ~ End With
~で書いた内容は
With内で省略して記述できる
例えば、繰り返した記述が必要な際にWithを用いて
.を書くと、With の後の内容が自動挿入される。
With Worksheet("成績表")
a= .Range("A1")
b= .Range("A2")
.
.
.
End With
WorksheetFunction
エクセル関数をVBに使用できる。
今回使用したエクセル関数は以下の二つ
必要な基礎知識(エクセル関数)
Sum
セル範囲の数値を合計する
Countif
セル範囲から条件に合致したセル個数を取得
回答
回答結果(全文)
まずは回答結果から
Dim ws As Worksheet Set ws = Worksheets("成績表") Dim rng As Range Set rng = ws.Range("A1").CurrentRegion '表範囲 Set rng = Intersect(rng, rng.Offset(1, 1)) ' 表データ範囲取得 rng.Columns(6).ClearContents '合否欄リセット Dim r As Range For Each r In rng.Rows With WorksheetFunction 'Resize で合否列(G)を範囲から出す If .Sum(r.Resize(, 5)) >= 350 And _ .CountIf(r.Resize.Resize(, 5), ">=50") = 5 Then Cells(r.Row, 7) = "合格" ce End If End With Next
ワークシート指定
今回はまずワークシート名の指定があるので、「成績表」シートに対して
のみ実行されるマクロを作成します。
成績表のワークシートオブジェクトを作ります。
Dim ws As Worksheet Set ws = Worksheets("成績表")
表のデータ部のみの抽出
次に見出し行列を除き表データだけとってくるテクです。数値演算の条件式に、文字列の氏名は邪魔になるので。
これは今後もおそらく頻出、大事な箇所だと思います。
まず、この緑範囲を取得するにはどうすればよいか、と考えてみてください。
・
・
・
この緑範囲は、
「A1のCurrentRegion」(表全体範囲)と
「A1のCurrentRegionをoffset(1,1)」(ずらした範囲)
の交わった重複範囲として捉えられます。
重複範囲は、この上の基礎知識で学びました、「intersect」でしたね!ではまずここまでをコード化します。
Dim rng As Range Set rng = ws.Range("A1").CurrentRegion '表範囲 Set rng = Intersect(rng, rng.Offset(1, 1)) ' 表データ範囲取得
前回の合否判定入力リセット
rng.Columns(6).ClearContents
私はこれを書いている時、「あれ?これだとG列じゃなくてF列が消えるんじゃないの?大丈夫?」と思いました。しかし、これで問題ありません。
rngの範囲はRange("B2:G12")となるため、6列目というのは「Rangeオブジェクトの範囲内での」6列目です。
For each ー In ー Rows で行ごと判定&処理
For r In rng.Rows
でrセルが行ごとに処理されます。
ですが、今のままだと処理範囲rngに、合否欄の空白をスコアーに加算しても意味がないので、範囲を削りましょう。
→ Resize((そのまま),5列に修正)
お題の要 複数条件式とエクセル関数の適用
WorksheetFunction.Sum(範囲)>= 350
And _
(And:「かつ」という意味です。 スペースアンダーバー:処理がまだ行途中の場合、通常改行するとエラーがでますが、これを使えば改行できます。)
WorksheetFunction.Countif(リサイズ後の範囲,">=50")=5
50点以上という条件を満たすセルが5個だったら、とかいています。
指定範囲がそもそも教科の5セルなので、要は全科目50点以上だったら、という条件と等価です。
いずれの判定も、いつものi,j as longからのForNext二重ループでぶんぶんまわしでもできるのですが、今回は新たな知識導入のため敢えてWorksheet関数で解いてみました。
With ーEnd With
WorksheetFunction~がくどいですね。Withで略記に変えます。
With WorksheetFunction
・
・
・
End With
実行結果
無事に合否判定ができました。
学んだこと総括
- 行ごとのForーIn ーrows文を使えば i,j2重ループと同じ処理ができた
- マクロでもエクセル関数は使える!忘れた時はエクセルシートに打ってみて調べて貼り付けたら早い
- With で略記し、コードを読みやすく整形するテク
- CurrentRegion /offset/intersect/Resize 組み合わせれば大抵の表範囲は取得できる
初心者が最初に覚えるべきテク「Debug.Print」を学ぶ
今回は今までのデバグのやり方を一度見つめ直してみました。
従来の自分の非効率なデバグ 効率✖️
前回まで、自分はデバグ時に変数に格納されているデータを確認する為に、
Msgbox を使っていました。これを使うと、その都度でメッセージボックスのウインドウが開いてしまってました・・
しかし、VBEにはもともとデバグをするための、
「イミディエイトウインドウ」というデバグスペースがありました^^;
さっそくつかってみましょう。
Debug.Printを使おう 効率○
VBEの画面で以下のショートカットキー入力してみましょう。
画面下にイミディエイトウィンドウが開きます。
Mac なら Ctr+⌘(コマンド)+G
Windows なら Ctr + G
ここで様々な計算結果を確認できます。
実際に、次のコードを実行してテストしてみます。
例題:文字列の表示、数値計算、セルの値取得
Dim messa As String messa = " テスト" Debug.Print messa Debug.Print 1 + 2 Debug.Print Range("A1")
実行結果
内容削除
→イミディエイトウィンドウの内容を削除する Ctr+a+DEL
学んだこと総括
Debug.Print を積極的に活用することで、今後のマクロ開発作業の効率が
かなり向上しそうな気がしました。
「あれ、この変数にちゃんと指定した数値格納されてるのかな?」
と思った時は、ここで確認すればいいことを学びました。