プログラミングの世界では
ある物を作るとき、全く無の状態から作成されていきます。
そんな時に、何をどう作っていくのか、細かく書かれている説明書のようなものを
仕様書といいます。
大きなソフト開発プロジェクトでは、必ずそういった仕様書がありますが
個人的にExcelのデータ操作を行う場合に記述するVBAでは
そこまで大げさなものもありません。
そもそもどうしてVBAのプログラムまで覚えて、コーディングして、マクロを実行するのでしょう。
と、同時に現在VBAの勉強に行き詰っている人がいたとすると、
行き着く答えの出口は意外と同じなんてこともあります。
つまり、目的です。
どういう動作をエクセルにさせたいか。
VBAのプログラミングを行うときに
一番重要な箇所、それが仕様です。
表題の件に関してですが
VBAのコードを記述した方がいい時と、記述しない方がいい時
こればかりは、ギブアンドテイクの天秤で測るしかないのですが
A1からA10までに数値が入っていて、その合計をA11に表示させるのであれば
VBAでコードを記述するよりA11のセルに=SUM(A1:A10)と関数を入れてしまった方が
早いわけなんです。
では、こんな内容はどうでしょう。
A1からA10までのセルに1～100まででランダムの数値が入っていたとします。
A11には合計を入力し、B1～B10の間でそのランダムの数値が
1～50までは黒い文字、51～100までであれば赤い文字にする。
なお、ブック内に同様のシートが5000シート分あります。
そうなれば、手作業で行うより、VBAを記述した方が早いでしょう。
手で動かして動作させても、VBAコードを記述して
処理を自動化させても、結果は同じことですが
どのように進めていくかが仕様になります。
なにもVBAは作業の効率化だけではありません。
確かに、膨大なデータをボタン1クリックで
あっという間に処理を行ってくれるという機能は
誰しもご存知かと思います。
しかし、その裏で作業を行うのはコンピューターであり
仕様さえ間違いがなければ
決定的なエラーが1つなくなります。
それがヒューマンエラーです。
どんなに頑張っても、
ミスの少ない人はいても、ミスを絶対にしない完璧な人はいません。
ヒューマンエラーは人間が引き起こす
仕様書の欠落です。
単純作業ほど回数を重ねれば
人間は精度を落とし、いつかエラーを起こします。
しかし、コンピューターはそういったことは絶対にしません。
私が、仕事中で悩む最初の分岐は
自分で処理を行うか、コンピューターに処理を行うかの
選択になります。
簡単にIf文で例えてみます。
-------------------------
If me =>-5 Then
VBAコード入力を開始
Else
自分で処理を行う
-------------------------
という文になります。
これは勝手な印象値になりますが
僅かに処理結果にマイナスが発生したとしても
大幅なマイナスにならないようであれば、
VBAでコーディングして処理を実行させるようにします。
もう少し、具体的にいうと
手作業の場合は3時間かかる、
VBA入力の場合はコーディングから実行まで4時間かかるとしたとき
私はVBAのコーディングを選択するようにしてます。
理由は2つあります。
■先述した通り、コンピューターは指示通りの作業をこなすので
記述したコードが間違いなければ処理ミスはありえない。
■手作業で行った処理で処理ミスが発生した場合
最悪もう一度最初からやり直す必要があり、結果倍の6時間を要する。
メリットは他にもあります。
VBAに対する知識や発想力は、たくさんのテーマをクリアすればするほど豊富になり、
さらに次への対応の幅が広くなります。
もちろんデメリットもあります。
VBAの仕様コーディングに誤りがあり
予想していた以上にコーディングに時間がかかってしまうこともあります。
ただし、ここでのデメリットはメリットの経験を重ねれば重ねるほど
どんどん小さくなってきます。
やはり、やるに越したことはないわけです。
少々のロスは承知の上で、果敢にVBAコーディングに挑んだ方が
返ってくるメリットは大きいといえるでしょう。
2016年6月26日日曜日
2016年6月19日日曜日
【VBAリファレンス】ワークシートの作成、任意の名前のワークシートの作成、任意の名前のワークシートを作成し移動させる。の3本立てです。
たくさんのデータを取り扱っていると
ワークシートを追加した上で
新たな処理を行う必要性が出てきます。
ワークシートを追加する場合の
基本コードは以下の通りです。
-------------------------------------------
Sub ワークシートを追加()
Worksheets.Add
End Sub
以前はSheet1～3までとなっています。
上記のコードを実行すると
以降のシート番号が自動で割り振られ
シートが生成されます。
※処理結果
ただし、VBAコードで処理を実行するとき
シートを指定したうえで、作業を行うことがあり
時に、Sheet番号を指定してするにはちょいと面倒だったりします。
そんなのいちいち覚えてられませんし。
余談ですが、Exceブックlを開いたままシートを生成すると
生成した数だけシート番号が上がっていきます。
しかし、ブックを開きなおすと、またそのシート番号は最初のSheet1から生成され始めます。
といっても、ブック内にSheet1があると飛ばされSheet2から生成されます。
(ここは覚えなくていです)
さらに横道にそれますが
シートを指定して処理を行う場合、
以下のように記述してワークシートを指定します。
-------------------------------------------
Sub ワークシート処理()
Sheets("指定したいシート名").Select
End Sub
-------------------------------------------
赤文字の箇所を指定したいシート名に変えて実行すると
そのシートを指定することが出来ます。
さて、ではシートを作成した時に任意のシート名を記述しておけば
希望通りの処理を行うことが出来ますね。
一連の処理を記述します。
・【新しいシート】という名称のワークシートを作る
・そのシートを指定する
・そのシートに算数の九九の結果をFor文を使って記述する
上記3つの処理を1つのプロシージャで作成してみます。
なお、シート名をつけたい時は
Worksheets.Addの次の行に
ActiveSheet.Name = "指定したいシート名"と記述するだけでOKです。
まずは、実行前のブックの画像です。
なんてことはない普通のシートです。
記述するコード画像です。
記述している内容は以下の通りです。
-------------------------------------------
Sub test()
Dim i, j, x As Long
'新しいシートを生成し、シート名を新しいシートに変更する
Worksheets.Add
ActiveSheet.Name = "新しいシート"
'新しいシートを選択する
Sheets("新しいシート").Select
'新しいシートに九九の表示を行う
For i = 1 To 9
For j = 1 To 9
x = j * i
Cells(j, i) = x
Next j
Next i
End Sub
-------------------------------------------
実行後の画像
新しいシートが作成され、九九の結果が記述されています。
では、もう少し詳しく説明させてください。
作成時シートを任意の場所にあらかじめ移動しておきたい場合は
以下のように記述します。
-------------------------------------------
Sub ワークシートを追加()
Worksheets.Add after:=Worksheets(Worksheets.Count)
End Sub
まず、ブック内にあるシートの枚数をしらべます※赤文字の部分です。
で、そのシートの後(Afterの部分)に移動をさせるという記述です。
カッコ内のWorksheets.Countの部分はシート名で指定することが出来ます。
例)：新しいシートというシートの後に移動させたい場合は
Worksheets.Add after:=Worksheets("新しいシート")となります。
シート名を指定する場合は、ダブルクォーテーションで囲ってあげてください。
また、指定しているシートの前に生成したい場合は
afterをbeforeに変えてあげれば、指定したシートの左隣に移動されます。
右隣がafterで左隣がbeforeと覚えておきましょう。
※シートの作成時に特に指定をしない場合は、
一番左側に新しいシートが生成されますので合わせて覚えておいてください。
ワークシートを追加した上で
新たな処理を行う必要性が出てきます。
ワークシートを追加する場合の
基本コードは以下の通りです。
-------------------------------------------
Sub ワークシートを追加()
Worksheets.Add
End Sub
-------------------------------------------
この1行を記述するだけで
ワークシートを追加できます。
Excelのバージョンによりますが
2013以降はデフォルトのワークシート数がSheet1のみとなっており以前はSheet1～3までとなっています。
上記のコードを実行すると
以降のシート番号が自動で割り振られ
シートが生成されます。
※処理結果
ただし、VBAコードで処理を実行するとき
シートを指定したうえで、作業を行うことがあり
時に、Sheet番号を指定してするにはちょいと面倒だったりします。
そんなのいちいち覚えてられませんし。
余談ですが、Exceブックlを開いたままシートを生成すると
生成した数だけシート番号が上がっていきます。
しかし、ブックを開きなおすと、またそのシート番号は最初のSheet1から生成され始めます。
といっても、ブック内にSheet1があると飛ばされSheet2から生成されます。
(ここは覚えなくていです)
さらに横道にそれますが
シートを指定して処理を行う場合、
以下のように記述してワークシートを指定します。
-------------------------------------------
Sub ワークシート処理()
Sheets("指定したいシート名").Select
End Sub
-------------------------------------------
赤文字の箇所を指定したいシート名に変えて実行すると
そのシートを指定することが出来ます。
さて、ではシートを作成した時に任意のシート名を記述しておけば
希望通りの処理を行うことが出来ますね。
一連の処理を記述します。
・【新しいシート】という名称のワークシートを作る
・そのシートを指定する
・そのシートに算数の九九の結果をFor文を使って記述する
上記3つの処理を1つのプロシージャで作成してみます。
なお、シート名をつけたい時は
Worksheets.Addの次の行に
ActiveSheet.Name = "指定したいシート名"と記述するだけでOKです。
まずは、実行前のブックの画像です。
なんてことはない普通のシートです。
記述するコード画像です。
記述している内容は以下の通りです。
-------------------------------------------
Sub test()
Dim i, j, x As Long
'新しいシートを生成し、シート名を新しいシートに変更する
Worksheets.Add
ActiveSheet.Name = "新しいシート"
'新しいシートを選択する
Sheets("新しいシート").Select
'新しいシートに九九の表示を行う
For i = 1 To 9
For j = 1 To 9
x = j * i
Cells(j, i) = x
Next j
Next i
End Sub
-------------------------------------------
実行後の画像
新しいシートが作成され、九九の結果が記述されています。
では、もう少し詳しく説明させてください。
作成時シートを任意の場所にあらかじめ移動しておきたい場合は
以下のように記述します。
-------------------------------------------
Sub ワークシートを追加()
Worksheets.Add after:=Worksheets(Worksheets.Count)
End Sub
-------------------------------------------
これは作成したシートをまず、ブック内にあるシートの枚数をしらべます※赤文字の部分です。
で、そのシートの後(Afterの部分)に移動をさせるという記述です。
カッコ内のWorksheets.Countの部分はシート名で指定することが出来ます。
例)：新しいシートというシートの後に移動させたい場合は
Worksheets.Add after:=Worksheets("新しいシート")となります。
シート名を指定する場合は、ダブルクォーテーションで囲ってあげてください。
また、指定しているシートの前に生成したい場合は
afterをbeforeに変えてあげれば、指定したシートの左隣に移動されます。
右隣がafterで左隣がbeforeと覚えておきましょう。
※シートの作成時に特に指定をしない場合は、
一番左側に新しいシートが生成されますので合わせて覚えておいてください。
2016年6月16日木曜日
【VBAリファレンス】InputBoxは他の誰かのために使いましょう。
今日はInputBoxについて記事を書いていきたいと思います。
さて、世の中にたくさんあるツールの中で
何かの処理を行う場合、
とある入力を求められることがあります。
こういうやつです。
インプットボックスっていって
使用するユーザーに任意の情報を入力するための
テキストボックスになります。
記述自体はとても簡単です。
-------------------------------------------------
Sub test()
'ダブルクォーテーションの間のメッセージを変えるだけで
'表示するメッセージを変えられます
Application.InputBox ("名前を入力してください")
End Sub
さて、世の中にたくさんあるツールの中で
何かの処理を行う場合、
とある入力を求められることがあります。
こういうやつです。
インプットボックスっていって
使用するユーザーに任意の情報を入力するための
テキストボックスになります。
記述自体はとても簡単です。
-------------------------------------------------
Sub test()
'ダブルクォーテーションの間のメッセージを変えるだけで
'表示するメッセージを変えられます
Application.InputBox ("名前を入力してください")
End Sub
-------------------------------------------------
上記、ウィンドウを表示させるにはこのコードを記述するだけで
出現させることが出来ます。
しかし、これだけではウィンドウが出るだけで
文字を入力してもなーんの意味もありません。
InputBoxって、文字を入力して
その入力した文字を何かしらの処理の材料として使いたいわけですよね。
なので、右辺を左辺に代入するということで
変数を使用します。
Application.InputBoxの前に、宣言済みの変数をイコールで
つなげてあげましょう。
-------------------------------------------------
Sub test()
Dim ipt_b As String
ipt_b = Application.InputBox("名前を入力してください")
End Sub
-------------------------------------------------
変数名ipt_bとします。
で、今回は入力する値が名前なのでテキスト型(String)を宣言します。
※当然ですが値が数値の場合は、Long型を宣言してあげてください。
これを実行すると、InputBoxで入力した文字列が
変数ipt_bに代入されます。
さて、これだけでも意味がありません。
あとは、変数に代入した値をどう使うかは皆様次第です。
例えば、列の処理をする場合は
String型として、直接列の値を入力して見るのもいいかもしれません。
任意の列を削除したい時
以下のような使い方が出来ます。
-------------------------------------------------
Sub test()
Dim ipt_b As String
ipt_b = Application.InputBox("削除したい列を入力してください")
Columns(ipt_b).Delete
End Sub
-------------------------------------------------
サンプル画像(C列を削除したいと思います)
先ほどのコードを実行します。
InputBoxにCと入力しOKを押すとコードが実行されます。
ここでは文字列の【C】が
変数ipt_bに代入されます。
そして、Columns(ipt_b).Deleteに充てられるので
Columns(C).Deleteと同じ意味になりますね。
処理結果
C列にあった【この列は消す】という列が消えています。
InputBoxはおもに、他の誰かから情報を取得する場合、
あるいは、都度変わる任意の情報を直接入力する場合
に大変役立つ機能です。
この使用例はほんの一部ですので、色々と試してみてください。
2016年6月14日火曜日
【VBA実践】特定の列を一括挿入する。
別の記事で
特定の列を削除する内容をご案内しました。
【VBA実践】特定の列を一括削除する。
今回は作業を行うため、空の列を挿入するコードを記述します。
基本は列を削除する内容とあまり変わりません。
For文を駆使して行っていきます。
サンプル画像
1から10(AからJ列)までデータが入っており
その各列に空の列を挿入します。
コードは以下の通り。
削除するコードと基本は同じ逆打ちです。
-------------------------------------------------
Sub 特定の列を挿入するコード()
Dim col As Long
For col = 10 To 2 Step -1
Columns(col).Insert
Next col
End Sub
特定の列を削除する内容をご案内しました。
【VBA実践】特定の列を一括削除する。
今回は作業を行うため、空の列を挿入するコードを記述します。
基本は列を削除する内容とあまり変わりません。
For文を駆使して行っていきます。
サンプル画像
その各列に空の列を挿入します。
コードは以下の通り。
削除するコードと基本は同じ逆打ちです。
-------------------------------------------------
Sub 特定の列を挿入するコード()
Dim col As Long
For col = 10 To 2 Step -1
Columns(col).Insert
Next col
End Sub
-------------------------------------------------
1列目までは挿入する必要はないので
カウンタは10 To 2 になります。
また、Stepは-1になります。
処理分は挿入のInsertです。
実行後画面
上手く実行できたことが確認できました。
逆打ちではなく、通常カウントで実行できなくもないのですが
やはりそのやり方はおすすめできません。
試しにサンプル実行してみます。
※Excelのスタート画面は同じレイアウトとします。
コードは以下の通り。
カウント値を逆にしStepは 2とします。
結果は以下の通りです。
上手くいっているように見えますが
よく見てみると、様子が変です。
右方向にスクロールしてみます。
6以降が挿入されていません。
勘のいい方はすぐにお分かりいただけたかと思いますが
カウンタが10となっていることとStep 2となっているところがポイントです。
2列目から挿入されステップで2飛ばしとなって
最後は10で終了しているカウントということは
J列が最後の列になります。
この場合の対応方法は通常の倍のカウント数にしなければいけないということです。
なので、直感的に処理を行うのであれば
やはり前者にご覧いただいた
カウント逆打ちが一番いいですね。
また、データが入っている最終列の値を出すコードを
合わせて組み込み、Forの逆打ちの最初のカウントに代入しておくだけで
後はVBAが勝手に処理してくれます。
コードはただ書くだけではなく
無駄なく処理が実行されることも重要です。
ぜひ活用してみてください。
最後は10で終了しているカウントということは
J列が最後の列になります。
この場合の対応方法は通常の倍のカウント数にしなければいけないということです。
なので、直感的に処理を行うのであれば
やはり前者にご覧いただいた
カウント逆打ちが一番いいですね。
また、データが入っている最終列の値を出すコードを
合わせて組み込み、Forの逆打ちの最初のカウントに代入しておくだけで
後はVBAが勝手に処理してくれます。
コードはただ書くだけではなく
無駄なく処理が実行されることも重要です。
ぜひ活用してみてください。
【VBA実践】特定の列を一括削除する。
今回は不要となった特定の列を削除するコードをご紹介します。
列を挿入するコードは別の記事にてご案内していますので
是非参考にしてみて下さい。
【VBA実践】特定の列を一括挿入する。
大量のデータを処理をしていると
A列に大元のデータがありB列にA列のデータを処理した結果を入れたりします。
また、それが、C列以降、同様の列が続いたりします。
例えば、データ処理が終わった場合
不要となった列を削除したりすることがあります。
間が飛び飛びになっている場合は、
一つ一つの列を手作業で削除していくのは大変骨が折れる作業です。
また、誤って消すつもりのない列を消してしまったりする
大変なことになります。
そんな作業も、ちょっとコードを入力するだけで
正確かつ一瞬で終了します。
サンプル画像
まずは偶数(赤文字)が記載されている列を一括で削除するコードです。
上記コードを実行すると以下の結果がでます。
-------------------------------------------------
Sub 特定の列を削除するコード()
Dim col As Long
For col = 100 To 1 Step -2
Columns(col).Delete
Next col
End Sub
-------------------------------------------------
赤くなっている文字の列を削除する場合のサンプルコードは以下の通りです。
削除する列は2列に増えるので
Columns(col).Delete(指定している列)と
Columns(col -1).Delete(指定した左隣の列)を合わせて記述し削除します。
(col -1)というのは、変数colに代入されている値をマイナス1にするという記述です。
※結構こういった使い方することがあるのでぜひ覚えておいてください。
なお、2列消して、3列戻るので、Step -3としてください。
列を挿入するコードは別の記事にてご案内していますので
是非参考にしてみて下さい。
【VBA実践】特定の列を一括挿入する。
大量のデータを処理をしていると
A列に大元のデータがありB列にA列のデータを処理した結果を入れたりします。
また、それが、C列以降、同様の列が続いたりします。
例えば、データ処理が終わった場合
不要となった列を削除したりすることがあります。
間が飛び飛びになっている場合は、
一つ一つの列を手作業で削除していくのは大変骨が折れる作業です。
また、誤って消すつもりのない列を消してしまったりする
大変なことになります。
そんな作業も、ちょっとコードを入力するだけで
正確かつ一瞬で終了します。
サンプル画像
まずは偶数(赤文字)が記載されている列を一括で削除するコードです。
上記コードを実行すると以下の結果がでます。
-------------------------------------------------
Sub 特定の列を削除するコード()
Dim col As Long
For col = 100 To 1 Step -2
Columns(col).Delete
Next col
End Sub
-------------------------------------------------
まず変数は、列を意味するcolumnの最初の3文字を取って
変数名colにします。
colはFor文のカウンタの役割を行うので
値を代入しておく必要はありません。
次にFor文では逆打ちカウンタでStepは-2となります。
まず、なぜ逆打ちにするかということですが
通常の2..4..6..8..10とカウントしていった場合
削除する列がおかしくなってしまうんです。
例えば、最初に2があるB列を消すと、隣のC列にあったデータがB列になります。
ということは、次のカウント4列目つまりD列には
E列に入っていたデータを消してしまうことになります。
サンプル画像1
上記コードを実行すると以下の結果となります。
手前から消すと先のデータは前にずれてくるので
全ての結果がずれてきます。
その為、データの最後から実行していくことで
ターゲットの列データがずれることはなくなります。
こういった逆打ちでデータを操作する方法は、VBAの処理ではよく使用されるので
頭の片隅に置いとていただけると幸いです。
ちなみに、Columns(col).Deleteは削除です。
ClearContentsやClearにすると、列の削除はされず、値や値と書式のみが削除されます。
サンプルコードの画像
実行後の画像
という結果となります。
A、B、Cを1ブロックとして、そのB、C列を削除したいという
2列消しをする場合も要領は同じとなります。
サンプル画像
削除する列は2列に増えるので
Columns(col).Delete(指定している列)と
Columns(col -1).Delete(指定した左隣の列)を合わせて記述し削除します。
(col -1)というのは、変数colに代入されている値をマイナス1にするという記述です。
※結構こういった使い方することがあるのでぜひ覚えておいてください。
なお、2列消して、3列戻るので、Step -3としてください。
実行後の画面
上手く実行できたことが確認できました。
さて、別の記事でも今後記載予定ですが
先日、データが入っている最終行を取得するというコードを案内しましたが
最終列を取得するコードも当然あります。
column = Cells(1, Columns.Count).End(xlToLeft).Column
変数名columnとして、そこにデータが入っている最終列の値を代入するようにします。
Cells(1,～～の1は1行目を指定する箇所であり、
データが入っている行をここで入力します。
なので、1列目にデータが入っていないときは、データが最終列まで入っている行を
ここで入力してあげてください。
そして、その変数columnをカウンタに設定してあげます。
-------------------------------------------------
Sub 特定の列を削除するコード()
Dim col As Long
Dim column As Long
column = Cells(1, Columns.Count).End(xlToLeft).Column
For col = column To 1 Step -2
Columns(col).Delete
Next col
End Sub
-------------------------------------------------
Sub 特定の列を削除するコード()
Dim col As Long
Dim column As Long
column = Cells(1, Columns.Count).End(xlToLeft).Column
For col = column To 1 Step -2
Columns(col).Delete
Next col
End Sub
-------------------------------------------------
最終列が100行まで入っている場合、
変数columnには100が代入されるので
For col = 100 To 1 Step -2と同じ意味になります。
変数の宣言も忘れず行ってください。
ちょっと長い記事になってしまいましたが、
状況に応じてぜひ活用してみてください。
2016年6月12日日曜日
【VBAリファレンス】プロシージャってなんでしょう。Callステートメントで呼び出すやつです。
VBAコードを記述を行う時はVBE(Visual Basic Editor)に行います。
VBEを表示させるには
Excelの画面のリボンの【開発】からVisual Basicをクリックし
挿入よりモジュールをクリックするとエディターが起動します。
そこにぱちぱち入力していくのです。
もし、【開発】のタブがない場合は、
スタートメニュー、オプション、リボンのユーザー設定で開くと
リボンの設定画面が開くので
開発のところにチェックを入れて下さい。
さて、表題の件ですが
マクロの記述をしたあと、そのコードを確認しに行くと
コードが書かれています。
マクロの記述を行った場合、自動的に
【プロシージャ】というものが作成され、そこにコードが記載されます。
その時、何も設定しない場合Macro1という名前で登録されるのですが、
その名称をプロシージャ名といいます。
つまり、1括りの記述の集まりをプロシージャといいます。
基本形ですが、1つのプロシージャは
SubからEnd Subまでの間に処理内容を記述します。
そして、モジュールの中には
複数のプロシージャを記述することが可能です。
複雑な処理を行う場合、
どうしても記述するコードが長くなります。
もちろん、全体把握がしっかり出来ている頭のいい方であれば
1つのプロシージャに全ての処理内容を記述してもかまいません。
でも、私のようなポンコツおじさんは限界があります。
そんな時は、全体処理を部分化させて全体把握をしやすくします。
つまり、プロシージャごとに処理内容を分けて記述します。
しかし、そんなことをすると、
全処理を実行するにはプロシージャ1つ1つを実行しなければなりません。
それはそれで面倒です。
そんな時は、Callステートメントを使います。
Callステートメントは
あるプロシージャ内で、別のプロシージャ(つまり別の処理)を
呼び出すことができます。
記述の仕方は
Call プロシージャ名
だけです。
サンプル画像
サンプル画像のVBE内、一番上のプロシージャがメインプロシージャで
以降は具体的な処理を記述したプロシージャです。
一番最初の全処理工程を記述のプロシージャを実行すると
2番目から4番目までのプロシージャが順番に実行されます。
さらにサンプル画像2
このCallステートメントを使用することで
全体を把握しやすくなったり、
各パーツごとでデータを実行したりと、
後々編集もしやすくなりますし、エラーが発生した場合でも
メンテナンスがしやすくなります。
VBAの記述量が少ないうちは
あまり使用することはないですが、
多くの処理内容があるコードを記述する場合は
こまめにプロシージャを分けることをおすすめします。
VBEを表示させるには
Excelの画面のリボンの【開発】からVisual Basicをクリックし
挿入よりモジュールをクリックするとエディターが起動します。
そこにぱちぱち入力していくのです。
もし、【開発】のタブがない場合は、
スタートメニュー、オプション、リボンのユーザー設定で開くと
リボンの設定画面が開くので
開発のところにチェックを入れて下さい。
さて、表題の件ですが
マクロの記述をしたあと、そのコードを確認しに行くと
コードが書かれています。
マクロの記述を行った場合、自動的に
【プロシージャ】というものが作成され、そこにコードが記載されます。
その時、何も設定しない場合Macro1という名前で登録されるのですが、
その名称をプロシージャ名といいます。
つまり、1括りの記述の集まりをプロシージャといいます。
基本形ですが、1つのプロシージャは
SubからEnd Subまでの間に処理内容を記述します。
そして、モジュールの中には
複数のプロシージャを記述することが可能です。
複雑な処理を行う場合、
どうしても記述するコードが長くなります。
もちろん、全体把握がしっかり出来ている頭のいい方であれば
1つのプロシージャに全ての処理内容を記述してもかまいません。
でも、私のようなポンコツおじさんは限界があります。
そんな時は、全体処理を部分化させて全体把握をしやすくします。
つまり、プロシージャごとに処理内容を分けて記述します。
しかし、そんなことをすると、
全処理を実行するにはプロシージャ1つ1つを実行しなければなりません。
それはそれで面倒です。
そんな時は、Callステートメントを使います。
Callステートメントは
あるプロシージャ内で、別のプロシージャ(つまり別の処理)を
呼び出すことができます。
記述の仕方は
Call プロシージャ名
だけです。
サンプル画像
サンプル画像のVBE内、一番上のプロシージャがメインプロシージャで
以降は具体的な処理を記述したプロシージャです。
一番最初の全処理工程を記述のプロシージャを実行すると
2番目から4番目までのプロシージャが順番に実行されます。
さらにサンプル画像2
このCallステートメントを使用することで
全体を把握しやすくなったり、
各パーツごとでデータを実行したりと、
後々編集もしやすくなりますし、エラーが発生した場合でも
メンテナンスがしやすくなります。
VBAの記述量が少ないうちは
あまり使用することはないですが、
多くの処理内容があるコードを記述する場合は
こまめにプロシージャを分けることをおすすめします。
2016年6月11日土曜日
【VBAコラム】VBAの技術を身に付けたいのであれば、是非読んでみてください。
さて、このブログも2016年5月31日に開設し、
早10日が経過しようとしてます。
今日はここで少し重要なお話をしようと思っています。
こんな私でも、今まで素人に羽毛が生えた程度の柔らかさで
よくも偉そうにしゃあしゃあと記事を書いてきたものです。
■変数の宣言
【VBAリファレンス】変数は宣言してください。お願いします。
■Rangeや行列の選択
【VBAリファレンス】セルの値を指定する方法は主に2種類あるのです。
【VBAリファレンス】行や列を指定する
■Forステートメント
【VBAリファレンス】For文を理解してフォアフォアしてみよう。
■Ifステートメント
【VBAリファレンス】もし、If文を使うなら…True、使わないならFalse
などなど
しかし、ForやIfなんか覚えただけでは
何の役にも立ちません！！
ズコーーっ！！！
はい、皆さんも一緒に
ズコーーーっ！！！！！
ただ、一部の方の中には
そうだね～と共感していただける方もいるはずです。
誤解を与えるような書き方をしてしまい申し訳ありません。
ここまで、記事でご案内したForやIf、変数の宣言から
Range、Cellsプロパティを思い出してください。
・変数の宣言をして、値を変数に代入したからどうするのん？
・セルを選択したあとどうするのん？
・Forを使ってカウンタが上がっていくからなんなん？
・Ifで分岐させた後、どうなるん？
勉強を始めようとしたとき、
こういった疑問持つ人の方が圧倒的に多いと思います。
それが自然なんです。
私もそうでした。
何故なら、使いたい使い方を知らないからなんです。
家の台所に真っ白い金属製の白い箱があったとします。
その箱は、煮る、焼く、茹でる、揚げる、炒めるをすべて1台でこなす
超ハイパー家電だったとします。
でも、説明書はありませんでした。
ハイそこー！
しかも、料理経験が全くありません。
さらにそこー！
先ほど疑問を抱いた方は、
説明書がない状態、もしくは料理経験がない状態なだけです。
説明書や、経験がなければ何もできないのは当然です。
For文もIf文も組み合わせて、
さらに複数の変数を宣言したうえで、
注釈のコメント書きながらやるからこそ、
手作業で10時間かかる作業がボタン1クリックするとものの数分で処理が完了する
コードが本当に役に立つコードです。
書籍やWebサイトを見ても、自分がやりたい処理の
具体的なコードが見つからなくて、嘆いた経験はありませんか？
私は、ありました。
特にVBAの勉強を本気で使用と思ってすぐの時でした。
しかし、今同じ条件でWebで検索すると
答えはごろごろ転がっています。
ど～れ～に～し～よぅ～か～な～って感じです。
その差は何かというと、
最初はWebなどで記述されてるコードの意味が分からないだけなのです。
だから、使い方はおろか、正解か不正解かの前に
見つからないと結論付けるのです。
でもそれは慣れなんですね。
毎日何かしらコードを記述したりすることで
自然と身に付く、自転車運転方法でしかありません。
基本が乗っている書籍なんて本屋さんや
AMAZONでいくらでも売っているので、本来ここに書くべきものでもないのですが
私自身、ずいぶん遠回りしながらも、どうにか身に付けてきた知識を
皆様は遠回りしないようショートカットで身に付けていただければと考えました。
書籍には使用方法は書かれていますが
例や目的は書かれていないことがとても多いです。
どのリファレンス(教材)本にも共通することですが
評価が高い本は、実用的な使用方法がたくさん書かれていることが多いです。
あるいは、本当に使用方法だけがギッチリぱんぱんに詰め込まれているかのどちらかです。
しかし、実用的な使用方法が書かれている本は
やはり少ないのです。
おこがましい話ですが
私も1から始めたことで、たくさんの壁や挫折を経験してきたからこそ
まだ、初心者の目線は忘れてはいません。
だからこそお伝えできる例え方や、文章が書けるだろうと調子に乗ってます。
適度なリファレンスと、オーソドックスで変態的な使用例を
うまく合わせて、お伝えできればと考えます。
まだまだ、お伝えしたいことがたくさんあります。
なので、じっくりお付き合いください。
少しでも、誰かのお役に立てれば幸いです。
P.S 最終回ではありません
早10日が経過しようとしてます。
今日はここで少し重要なお話をしようと思っています。
こんな私でも、今まで素人に羽毛が生えた程度の柔らかさで
よくも偉そうにしゃあしゃあと記事を書いてきたものです。
■変数の宣言
【VBAリファレンス】変数は宣言してください。お願いします。
■Rangeや行列の選択
【VBAリファレンス】セルの値を指定する方法は主に2種類あるのです。
【VBAリファレンス】行や列を指定する
■Forステートメント
【VBAリファレンス】For文を理解してフォアフォアしてみよう。
■Ifステートメント
【VBAリファレンス】もし、If文を使うなら…True、使わないならFalse
などなど
しかし、ForやIfなんか覚えただけでは
何の役にも立ちません！！
ズコーーっ！！！
はい、皆さんも一緒に
ズコーーーっ！！！！！
ただ、一部の方の中には
そうだね～と共感していただける方もいるはずです。
誤解を与えるような書き方をしてしまい申し訳ありません。
ここまで、記事でご案内したForやIf、変数の宣言から
Range、Cellsプロパティを思い出してください。
・変数の宣言をして、値を変数に代入したからどうするのん？
・セルを選択したあとどうするのん？
・Forを使ってカウンタが上がっていくからなんなん？
・Ifで分岐させた後、どうなるん？
勉強を始めようとしたとき、
こういった疑問持つ人の方が圧倒的に多いと思います。
それが自然なんです。
私もそうでした。
何故なら、使いたい使い方を知らないからなんです。
家の台所に真っ白い金属製の白い箱があったとします。
その箱は、煮る、焼く、茹でる、揚げる、炒めるをすべて1台でこなす
超ハイパー家電だったとします。
でも、説明書はありませんでした。
ハイそこー！
しかも、料理経験が全くありません。
さらにそこー！
先ほど疑問を抱いた方は、
説明書がない状態、もしくは料理経験がない状態なだけです。
説明書や、経験がなければ何もできないのは当然です。
For文もIf文も組み合わせて、
さらに複数の変数を宣言したうえで、
注釈のコメント書きながらやるからこそ、
手作業で10時間かかる作業がボタン1クリックするとものの数分で処理が完了する
コードが本当に役に立つコードです。
書籍やWebサイトを見ても、自分がやりたい処理の
具体的なコードが見つからなくて、嘆いた経験はありませんか？
私は、ありました。
特にVBAの勉強を本気で使用と思ってすぐの時でした。
しかし、今同じ条件でWebで検索すると
答えはごろごろ転がっています。
ど～れ～に～し～よぅ～か～な～って感じです。
その差は何かというと、
最初はWebなどで記述されてるコードの意味が分からないだけなのです。
だから、使い方はおろか、正解か不正解かの前に
見つからないと結論付けるのです。
でもそれは慣れなんですね。
毎日何かしらコードを記述したりすることで
自然と身に付く、自転車運転方法でしかありません。
基本が乗っている書籍なんて本屋さんや
AMAZONでいくらでも売っているので、本来ここに書くべきものでもないのですが
私自身、ずいぶん遠回りしながらも、どうにか身に付けてきた知識を
皆様は遠回りしないようショートカットで身に付けていただければと考えました。
書籍には使用方法は書かれていますが
例や目的は書かれていないことがとても多いです。
どのリファレンス(教材)本にも共通することですが
評価が高い本は、実用的な使用方法がたくさん書かれていることが多いです。
あるいは、本当に使用方法だけがギッチリぱんぱんに詰め込まれているかのどちらかです。
しかし、実用的な使用方法が書かれている本は
やはり少ないのです。
おこがましい話ですが
私も1から始めたことで、たくさんの壁や挫折を経験してきたからこそ
まだ、初心者の目線は忘れてはいません。
だからこそお伝えできる例え方や、文章が書けるだろうと調子に乗ってます。
適度なリファレンスと、オーソドックスで変態的な使用例を
うまく合わせて、お伝えできればと考えます。
まだまだ、お伝えしたいことがたくさんあります。
なので、じっくりお付き合いください。
少しでも、誰かのお役に立てれば幸いです。
P.S 最終回ではありません
2016年6月10日金曜日
【VBAリファレンス】記載するコードは長くても短くてもコメントはたっぷりお願いします。
VBAのコード入力も波に乗ってくると
調子にものって、ガツガツたくさんのコードを1つのプロシージャに
書いてしまいがちです。
VBAのコードというのは皆様もご存知の通り
英字と数字と記号を主に記述します。
日本語が入る時は、大体If文の条件分岐とかで
特定のワードに対して使ったり
ワークシートに対する処理を行うときなどに日本語が入り混じります。
さて、ひとまず以下のコードをご覧ください(ちょっと長いです)
---------------------------------------------------------------------------------
Sub Maxif()
Dim datavalue, cntvalue, d_one, d_two, Boobyvalue, Lastvalue, Maxvalue As Long
Maxvalue = WorksheetFunction.Max(Range("B1:B40"))
For cntvalue = 1 To Maxvalue
Lastvalue = 0
For datavalue = 1 To 40
Boobyvalue = Lastvalue
If Cells(datavalue, 2) = cntvalue Then
d_one = Cells(datavalue, 3)
Else
d_one = 0
End If
If Cells(datavalue + 1, 2) = cntvalue Then
d_two = Cells(datavalue + 1, 3)
Else
d_two = 0
End If
If d_one > d_two Then
Lastvalue = d_one
ElseIf d_one < d_two Then
Lastvalue = d_two
Else
End If
If Lastvalue > Boobyvalue Then
Lastvalue = Lastvalue
ElseIf Lastvalue < Boobyvalue Then
Lastvalue = Boobyvalue
Else
End If
Cells(cntvalue, 7).Value = Lastvalue
Next datavalue
Next cntvalue
End Sub
---------------------------------------------------------------------------------
これは実際に数日前に私が記述した
Maxifの代用で試しに起こしてみたコードです。
これをみて、若干イヤんなちゃった人は少なくないはずです。
これでも、そこまで長くはない記述ですが
こんなコードがたくさんあって、プロシージャがさらに連なってってなると
もう、見るだけで嫌になります。
当然のことです。
VBAではコードを記述する際
どういった処理のコードか、メモ書きのようにしておける機能があります。
人はそれをコメントといいます。
シングルクォーテーションを最初に入れて
そのあとに入れたい文字を入れます。
-----------------------------------
Sub test()
'セルのA1にこんにちはと入れる
Range("A1").Value = "こんにちは"
End Sub
調子にものって、ガツガツたくさんのコードを1つのプロシージャに
書いてしまいがちです。
VBAのコードというのは皆様もご存知の通り
英字と数字と記号を主に記述します。
日本語が入る時は、大体If文の条件分岐とかで
特定のワードに対して使ったり
ワークシートに対する処理を行うときなどに日本語が入り混じります。
さて、ひとまず以下のコードをご覧ください(ちょっと長いです)
---------------------------------------------------------------------------------
Sub Maxif()
Dim datavalue, cntvalue, d_one, d_two, Boobyvalue, Lastvalue, Maxvalue As Long
Maxvalue = WorksheetFunction.Max(Range("B1:B40"))
For cntvalue = 1 To Maxvalue
Lastvalue = 0
For datavalue = 1 To 40
Boobyvalue = Lastvalue
If Cells(datavalue, 2) = cntvalue Then
d_one = Cells(datavalue, 3)
Else
d_one = 0
End If
If Cells(datavalue + 1, 2) = cntvalue Then
d_two = Cells(datavalue + 1, 3)
Else
d_two = 0
End If
If d_one > d_two Then
Lastvalue = d_one
ElseIf d_one < d_two Then
Lastvalue = d_two
Else
End If
If Lastvalue > Boobyvalue Then
Lastvalue = Lastvalue
ElseIf Lastvalue < Boobyvalue Then
Lastvalue = Boobyvalue
Else
End If
Cells(cntvalue, 7).Value = Lastvalue
Next datavalue
Next cntvalue
End Sub
---------------------------------------------------------------------------------
これは実際に数日前に私が記述した
Maxifの代用で試しに起こしてみたコードです。
これをみて、若干イヤんなちゃった人は少なくないはずです。
これでも、そこまで長くはない記述ですが
こんなコードがたくさんあって、プロシージャがさらに連なってってなると
もう、見るだけで嫌になります。
当然のことです。
VBAではコードを記述する際
どういった処理のコードか、メモ書きのようにしておける機能があります。
人はそれをコメントといいます。
シングルクォーテーションを最初に入れて
そのあとに入れたい文字を入れます。
-----------------------------------
Sub test()
'セルのA1にこんにちはと入れる
Range("A1").Value = "こんにちは"
End Sub
-----------------------------------
VBAに記述する際もシングルクォーテーションを入れた後
文字は緑色になり
その行の最後までは処理に関係のないものとされます。
つまり、メモを入れるということになります。
改行をする場合は新たにシングルクォーテーションをつける必要があります。
記述したコードは
何も自分ばかりが見るものではなく、他の方が見たり
手入れしたりする可能性もあります。
その際、どの部分でどういった処理が起こるか、
その簡易説明があるかないかで
確認作業や修正作業、
さらには、人に説明をしなければいけない場合など、
作業が円滑に進むものです。
おそらく、こういった作業は個人差(性格)が出るものですが
出来る限り分かりやすく、簡潔に書いておくことに越したことはありません。
今日お仕事で、私はそのコメントの記述を怠ったばかりに
どういった処理が行われているか、すぐに説明できませんでした。
戒めの意味で、自分のメガネをヤスリでサッ！ってやっておきます。
意外と軽視されがちですが
やはりコメントというのは重要です。
それは、たくさんのコードを書けば書くほど身に染みるものです。
だからといってやたらめったら書いていいものではないです。
先ほども申し上げましたが
出来る限り分かりやすく、簡潔に書くというのが前提です。
さて、先ほどのコード。
コードが付いているものを改めて乗せてみたいと思います。
---------------------------------------------------------------------------------
Sub Maxif()
Dim datavalue, cntvalue, d_one, d_two, Boobyvalue, Lastvalue, Maxvalue As Long
'B1～40までの最大値をMaxvalueに代入
Maxvalue = WorksheetFunction.Max(Range("B1:B40"))
For cntvalue = 1 To Maxvalue 'Maxvalueの数だけループ
Lastvalue = 0 'Lastvalueを初期化
For datavalue = 1 To 40 '元データの末端行の値
Boobyvalue = Lastvalue 'LastvalueデータをBoobyvalueデータに移行
'd_oneに1個目のデータを代入 なければ0で初期化
If Cells(datavalue, 2) = cntvalue Then
d_one = Cells(datavalue, 3)
Else
d_one = 0
End If
'd_twoに2個目のデータを代入 なければ初期化
If Cells(datavalue + 1, 2) = cntvalue Then
d_two = Cells(datavalue + 1, 3)
Else
d_two = 0
End If
'd_oneとd_twoを比較し、大きい方をLastvalueに代入
If d_one > d_two Then
Lastvalue = d_one
ElseIf d_one < d_two Then
Lastvalue = d_two
Else
End If
'この前にLastvalueからBoobyvalueに代入していた値と最新のLastvalueを比較し、
'大きい方をLastvalueに代入
If Lastvalue > Boobyvalue Then
Lastvalue = Lastvalue
ElseIf Lastvalue < Boobyvalue Then
Lastvalue = Boobyvalue
Else
End If
Cells(cntvalue, 7).Value = Lastvalue
Next datavalue
Next cntvalue
End Sub
Dim datavalue, cntvalue, d_one, d_two, Boobyvalue, Lastvalue, Maxvalue As Long
'B1～40までの最大値をMaxvalueに代入
Maxvalue = WorksheetFunction.Max(Range("B1:B40"))
For cntvalue = 1 To Maxvalue 'Maxvalueの数だけループ
Lastvalue = 0 'Lastvalueを初期化
For datavalue = 1 To 40 '元データの末端行の値
Boobyvalue = Lastvalue 'LastvalueデータをBoobyvalueデータに移行
'd_oneに1個目のデータを代入 なければ0で初期化
If Cells(datavalue, 2) = cntvalue Then
d_one = Cells(datavalue, 3)
Else
d_one = 0
End If
'd_twoに2個目のデータを代入 なければ初期化
If Cells(datavalue + 1, 2) = cntvalue Then
d_two = Cells(datavalue + 1, 3)
Else
d_two = 0
End If
'd_oneとd_twoを比較し、大きい方をLastvalueに代入
If d_one > d_two Then
Lastvalue = d_one
ElseIf d_one < d_two Then
Lastvalue = d_two
Else
End If
'この前にLastvalueからBoobyvalueに代入していた値と最新のLastvalueを比較し、
'大きい方をLastvalueに代入
If Lastvalue > Boobyvalue Then
Lastvalue = Lastvalue
ElseIf Lastvalue < Boobyvalue Then
Lastvalue = Boobyvalue
Else
End If
Cells(cntvalue, 7).Value = Lastvalue
Next datavalue
Next cntvalue
End Sub
---------------------------------------------------------------------------------
このようにコメントの記載があるかないかで
読みにいく気持ちも変わってきます。
他人のためでもあり、自分のためのコメントなので
長い、短いにかかわらず、コメントはたっぷりしっかり記述するようにしましょう。
2016年6月8日水曜日
【VBAリファレンス】もし、If文を使うなら…True、使わないならFalse
さて、前回のForステートメントに続き
今回はIfステートメントをご紹介します。
Forステートメントの記事はこちら。
【VBAリファレンス】For文を理解してフォアフォアしてみよう。
一般的な事務や、データ解析、操作系の仕事をされているのであれば
For文と、If文をマスターしていれば
大体のことは出来るのではないでしょうか。
ちょっと大げさかもしれませんが
そこから派生した疑問などは
大体Webを見ると解決したりするものです。
つまり、基本をしっかり!マスターしていれば
あとはGoogle先生が教えてくれるものです。
実際に私も、Ifは理屈的に理解していましたが
For文の理解度を高めた事で、二段飛ばしくらいあれをあれしたものです。
それではIf文の基本的な説明ですが
原理はこうです。
もし○○が□□だったら、▲▲する
そうじゃなければ△△する。
または
もし○○が□□だったら、▲▲する
それか、◇◇だったら、☆☆する。※1
そうじゃなければ△△する。
※1 それか、◇◇じゃなければ、☆☆するは何回も重ねられます。
If文はFor文と違って上から下に流れて終わります。
その際にとある条件に一致しているか、していないか、のどちらかで
処理内容が変わる、条件分岐、分かれ道方式になります。
またその条件は柔軟に変動が可能です。
その時の答えとして
そうだ！って時はTrue、違う！って時は、Falseに置き換えたうえで
処理が進められます。
それでは、さっそく基本文を書いてみます。
-------------------------------
Sub test()
If Range("A1") = "1" Then
Range("B1").Value = "男"
End If
End Sub
-------------------------------
これは、セルのA1に1が入力されていた場合
その隣のB1のセルに男と入れるIf文です。
1以外の文字列、もしくは何も入ってなかったら
何も起きません。
If Range("A1") = "1" Then → もしセルのA1に1が入っていたら
Range("B1").Value = "男" → B1に男と入れてね。
ですね。
つまり、1以外の時は End Ifに飛んで、このコードは終わります。
次に、1以外のデータが入っていたときは
B1に女と入れるコードを書きたい時、
Else(エルス)を使用します。
-------------------------------
Sub test()
If Range("A1") = "1" Then
Range("B1").Value = "男"
Else
Range("B1").Value = "女"
End If
End Sub
-------------------------------
セルのA1に1以外のデータ(空欄含む)が入っている場合は
セルのB1に女が入力されます。
つまり、1以外どんな状態でも女と入るわけです。
これではちょっと雑だな～って時は
さらにElse If(エルスイフ)を使用します。
-------------------------------
Sub test()
If Range("A1") = "1" Then
Range("B1").Value = "男"
ElseIf Range("A1") = "2" Then
Range("B1").Value = "女"
Else
Range("B1").Value = "エラーです"
End If
End Sub
-------------------------------
これは、セルのA1に1と入っていた場合はB1に男と入力され
2と入力されていた場合はB1に女と入力され
それ以外のデータだった場合は、B1にエラーですと返すコードです。
この時点で、条件分岐は3つに分かれるわけです。
このElseIfはいくつも連結が可能ですが
最後は必ずElseをつけて終わりに導いてください。
Elseで特に指定したい条件がない場合は
Else以降何も書かなくてよいです。
そして、条件は必ずしもイコール(一致)だけというわけではありません。
○○より大きい
○○以上
○○と等しい(今まで使っていたイコールです)
○○と等しくない
の主に6種類あり、
それを比較演算子といいます。
意味さえ知っていれば、特に言葉を覚えなくてもいいです。
比較演算子の記号は以下の通りです。
○○＜ →○○より大きい
○○≦ →○○以上
○○＝ →○○と等しい(今まで使っていたイコールです)
○○＜＞ →○○と等しくない
となります。
やはり使う頻度が多いのは、＜、＞、＝、＜＞の4つが多いでしょうか。
＜や＞は使い方を誤ると、えらい方向にはまりがちなので
ぜひぜひご注意ください。
先ほど【条件は柔軟に変動が可能です】と記載をしましたが
私が普段使用しているIfの条件サンプルを記述してみたいと思いますので
ご参考頂ければ幸いです。
その1
-------------------------------
Sub test1()
If Range("A1") = "" Then
Range("A1").Value = "○"
ElseIf Range("A1") = "○" Then
Range("A1").ClearContents
Else
End If
End Sub
-------------------------------
その1は完全な2択です。
何も入っていなければ○を入れる、○が入っていたら消すの繰り返しです。
これをたくさん作って、それぞれコントロールフォームのボタンに
当て込めば、チェック表のようなものが作れます。
※コントロールフォームの使用方法は後日ご案内いたします。
その2
-------------------------------
Sub test()
Dim i, a, b As Long
For i = 1 To 10
If Cells(i, 1) <> "" Then
a = Cells(i, 1)
b = a * 1.08
Cells(i, 1).Value = b
Else
End If
Next i
End Sub
一旦、指定したセルに値が入っていないかを確認するため
If文を使い、ダブルクォーテーション2つ""(何も入っていない)という条件を記載し
その条件に当てはまらない<>をクリアした場合、
選択しているセルの値を一旦変数aの箱に代入します。
その変数aに消費税1.08をかけた値をさらに変数bの箱に代入します。
その変数bの値を選択中のセルに上書きすることで
自動で課税金額に上書きすることが出来ます。
例えばとなりのセルに課税した金額を記載したい場合は
Cells(i, 1).Value = bをCells(i, 2).Value = bに変えればOKです。
ちょっとここでは書ききれないので、
色々なサンプルは後日ご案内しますね。
今回はIfステートメントをご紹介します。
Forステートメントの記事はこちら。
【VBAリファレンス】For文を理解してフォアフォアしてみよう。
一般的な事務や、データ解析、操作系の仕事をされているのであれば
For文と、If文をマスターしていれば
大体のことは出来るのではないでしょうか。
ちょっと大げさかもしれませんが
そこから派生した疑問などは
大体Webを見ると解決したりするものです。
つまり、基本をしっかり!マスターしていれば
あとはGoogle先生が教えてくれるものです。
実際に私も、Ifは理屈的に理解していましたが
For文の理解度を高めた事で、二段飛ばしくらいあれをあれしたものです。
それではIf文の基本的な説明ですが
原理はこうです。
もし○○が□□だったら、▲▲する
そうじゃなければ△△する。
または
もし○○が□□だったら、▲▲する
それか、◇◇だったら、☆☆する。※1
そうじゃなければ△△する。
※1 それか、◇◇じゃなければ、☆☆するは何回も重ねられます。
If文はFor文と違って上から下に流れて終わります。
その際にとある条件に一致しているか、していないか、のどちらかで
処理内容が変わる、条件分岐、分かれ道方式になります。
またその条件は柔軟に変動が可能です。
その時の答えとして
そうだ！って時はTrue、違う！って時は、Falseに置き換えたうえで
処理が進められます。
それでは、さっそく基本文を書いてみます。
-------------------------------
Sub test()
If Range("A1") = "1" Then
Range("B1").Value = "男"
End If
End Sub
-------------------------------
これは、セルのA1に1が入力されていた場合
その隣のB1のセルに男と入れるIf文です。
1以外の文字列、もしくは何も入ってなかったら
何も起きません。
If Range("A1") = "1" Then → もしセルのA1に1が入っていたら
Range("B1").Value = "男" → B1に男と入れてね。
ですね。
つまり、1以外の時は End Ifに飛んで、このコードは終わります。
次に、1以外のデータが入っていたときは
B1に女と入れるコードを書きたい時、
Else(エルス)を使用します。
-------------------------------
Sub test()
If Range("A1") = "1" Then
Range("B1").Value = "男"
Else
Range("B1").Value = "女"
End If
End Sub
-------------------------------
セルのA1に1以外のデータ(空欄含む)が入っている場合は
セルのB1に女が入力されます。
つまり、1以外どんな状態でも女と入るわけです。
これではちょっと雑だな～って時は
さらにElse If(エルスイフ)を使用します。
-------------------------------
Sub test()
If Range("A1") = "1" Then
Range("B1").Value = "男"
ElseIf Range("A1") = "2" Then
Range("B1").Value = "女"
Else
Range("B1").Value = "エラーです"
End If
End Sub
-------------------------------
これは、セルのA1に1と入っていた場合はB1に男と入力され
2と入力されていた場合はB1に女と入力され
それ以外のデータだった場合は、B1にエラーですと返すコードです。
この時点で、条件分岐は3つに分かれるわけです。
このElseIfはいくつも連結が可能ですが
最後は必ずElseをつけて終わりに導いてください。
Elseで特に指定したい条件がない場合は
Else以降何も書かなくてよいです。
そして、条件は必ずしもイコール(一致)だけというわけではありません。
○○より大きい
○○以上
○○より小さい
○○以下○○と等しい(今まで使っていたイコールです)
○○と等しくない
の主に6種類あり、
それを比較演算子といいます。
意味さえ知っていれば、特に言葉を覚えなくてもいいです。
比較演算子の記号は以下の通りです。
○○＜ →○○より大きい
○○≦ →○○以上
○○＞ →○○より小さい
○○≧ →○○以下○○＝ →○○と等しい(今まで使っていたイコールです)
○○＜＞ →○○と等しくない
となります。
やはり使う頻度が多いのは、＜、＞、＝、＜＞の4つが多いでしょうか。
＜や＞は使い方を誤ると、えらい方向にはまりがちなので
ぜひぜひご注意ください。
先ほど【条件は柔軟に変動が可能です】と記載をしましたが
私が普段使用しているIfの条件サンプルを記述してみたいと思いますので
ご参考頂ければ幸いです。
その1
-------------------------------
Sub test1()
If Range("A1") = "" Then
Range("A1").Value = "○"
ElseIf Range("A1") = "○" Then
Range("A1").ClearContents
Else
End If
End Sub
-------------------------------
その1は完全な2択です。
何も入っていなければ○を入れる、○が入っていたら消すの繰り返しです。
これをたくさん作って、それぞれコントロールフォームのボタンに
当て込めば、チェック表のようなものが作れます。
※コントロールフォームの使用方法は後日ご案内いたします。
その2
-------------------------------
Sub test()
Dim i, a, b As Long
For i = 1 To 10
If Cells(i, 1) <> "" Then
a = Cells(i, 1)
b = a * 1.08
Cells(i, 1).Value = b
Else
End If
Next i
End Sub
-------------------------------
その2はA列の1から10に
税抜きの金額が入っていると仮定してください。
ただし、その中には何も入っていないセルもある場合、
そのセルは飛ばします。
先日の記事のFor文を使用します。
カウントの変数はiになります。
で、そのカウントをCellsプロパティに当て込みながら
セルのA1からA10までを指示するFor文を作ります。
一旦、指定したセルに値が入っていないかを確認するため
If文を使い、ダブルクォーテーション2つ""(何も入っていない)という条件を記載し
その条件に当てはまらない<>をクリアした場合、
選択しているセルの値を一旦変数aの箱に代入します。
その変数aに消費税1.08をかけた値をさらに変数bの箱に代入します。
その変数bの値を選択中のセルに上書きすることで
自動で課税金額に上書きすることが出来ます。
例えばとなりのセルに課税した金額を記載したい場合は
Cells(i, 1).Value = bをCells(i, 2).Value = bに変えればOKです。
ちょっとここでは書ききれないので、
色々なサンプルは後日ご案内しますね。
2016年6月7日火曜日
【VBAリファレンス】For文を理解してフォアフォアしてみよう。
大量のデータ処理を行う上で、必要なステートメント(述べること)が
いくつかあります。
その中でも私が現在とても重きを置いているのが
Forステートメント(以下For文って言います)と
Iｆステートメント(以下If文って言います)があります。
ちなみにIf文は後程ご紹介します。
For文とは
For～Nextという基本構文の～の部分に処理内容を記述します。
では、肝心のFor～Nextとは一体何をするものなのかですが、
まず、ざっくりいうと
カウントするんです。
まず、カウントするという言葉を頭の中に置いておいてください。
記述サンプルは以下の通りです。
-----------------------------------------
Sub test()
Dim i As Long 'ロング型の変数を宣言します
For i = 1 To 10
'ここに処理内容を書く
Next i
End Sub
-----------------------------------------
For i =の後がポイントです。
ここで記述している 1 To 10というのは
1から10までカウントするという意味です。
最初のiには1が入り、処理が行われます。
そして、処理が終わるとNext iに到着。
しかし、カウントはまだ1回目なので、このiは2回目のi、つまり
上に戻り、カウントは2にアップし、2回目の処理が行われます。
そして、それが10回まで繰り返されます。
もちろん、数字の入力は自由です。
2 To 5
これは、2～5カウントします。
1 To 1000
これは、1～1000までカウントします。
さて、ここまでカウントといってはぐらかしてきましたが
このカウントこそが最大のポイントです。
カウントとして使用するこの変数i(もちろん変数名はiじゃなくてもいいです)は
列の値、行の値、処理を行う回数など、
様々な使い方が出来るんです。
だって変数だから
その変数iを直接処理内容のCellsプロパティやRowsプロパティに
当て込んであげれば、もうエクセル内縦横無尽に
行ったり来たり出来ます。
あっちこっちに処理が出来ます。
ただ、For文というのは
掴むまで個人差があり、私は開花するまで数か月かかりました。
そのかわり、一度掴むと結構複雑な動作が出来るようになります。
具体例をコードで記述してみます。
-----------------------------------------
Sub test()
Dim i As Long
For i = 1 To 10
Cells(i, 1).Value = "カウント" & i
Next i
End Sub
-----------------------------------------
これはCellsプロパティを使って、Rowの値を1つずつ増やし、
尚且つ、指定したセルにカウントというコメントと変数を組み合わせて入れてます。
その結果は以下の通りです。
先ほどのCells(i, 1).Valueのiと1入れ替えると
RowとColumnが入れ替わるので
横向きに入力されていきます。
ただし、これは基本的な使用になります。
例えばAとB列に数値が入っていて
その数値に対してC列へ処理を実行したい場合、
また、そのC列の処理が終了したら、
隣の列に移動して別の処理を行いたいなど、
様々な処理が可能になります。
なんだかワクワクしませんか？
また、このカウントは段飛ばしにカウントすることも出来ます。
For i = 1 To 10の後に Step 2とつけると1段飛ばし
For i = 1 To 10の後に Step 3とつけると2段飛ばしが出来ます。
例)：For i = 1 To 10 Step 2
※下の図はStep 2を実行したものです。
もちろん、カウントの逆打ちも出来ます。
逆打ちの場合はToを挟む数字も逆になります。
-----------------------------------------
Sub test()
Dim i As Long
For i = 10 To 1 Step -2
Cells(i, 1).Value = "カウント" & i
Next i
End Sub
-----------------------------------------
逆打ちでStepを使用する場合はマイナスをつけてください。
この、逆打ちは不要な列の削除をする際に
大活躍します。
具体的には後々ご案内しますね。
さて、ここまでカウントの数値を具体数で記述していましたが
ここのカウント値に変数を使用することが出来ます。
ここで？？と思う方もいるかもしれません。
→For i = 1 To count (Countが変数とします)
変数には値を代入しなければならないので
結局2度手間じゃないの？と思うかもしれません。
ここで魔法の言葉です。
先日紹介しました記事にも記載しています。
Cells(Rows.Count, 1).End(xlUp).row
Cells(1, Columns.Count).End(xlToLeft).Column
それぞれCellsの後に記述しているの
Cells(Rows.Count, 1).End(xlUp).rowはA列
Cells(1, Columns.Count).End(xlToLeft).Columnは1行目の意味です。
その1の部分はデータ最終行、もしくは列まで入っているところを記述します。
※こちらも詳しくは後日ご案内します。
そして、その左辺に変数を入力してあげれば
最終行が入っている行が変数に代入されます。
例):A列に10行目までデータが入ってた場合、
count = Cells(Rows.Count, 1).End(xlUp).rowと記述すると
変数countには10という値が代入されます。
さて、もうお判りでしょうか。
あとはその変数countをForのカウントとして設定してあげればいいわけですね。
つまり、データが入っている最終までVBAで調べ
その値をForのカウンタに設定するわけです。
なので、Forは慣れればなれるほど
拡張性が増し、よりパフォーマンスの幅が広がってきます。
慣れてくると、エディター内はフォアフォアしまくるようになってくるでしょう。
最後に、先日私がプライベートで調査のために書いた
For文だらけのサンプルコードを乗っけておきます。
参考にはしないでください。
-----------------------------------------
Sub Sample()
Dim cc, re, sc, colcount, rocount, i As Long
cc = Cells(1, Columns.Count).End(xlToLeft).Column
rc = Cells(Rows.Count, 1).End(xlUp).Row
For sc = 1 To Sheets.Count
Sheets.count(sc)
For colcount = 1 To cc
For rocount = 1 To rc
For i = 1 To 30
'処理実行
Next i
Next rocount
Next colcount
Next sc
End Sub
Dim cc, re, sc, colcount, rocount, i As Long
cc = Cells(1, Columns.Count).End(xlToLeft).Column
rc = Cells(Rows.Count, 1).End(xlUp).Row
For sc = 1 To Sheets.Count
Sheets.count(sc)
For colcount = 1 To cc
For rocount = 1 To rc
For i = 1 To 30
'処理実行
Next i
Next rocount
Next colcount
Next sc
End Sub
-----------------------------------------
2016年6月6日月曜日
【VBAリファレンス】行や列を指定する
以前、セルの指定方法について記事を書きました。
詳しくはこちら:【VBAリファレンス】セルの値を指定する方法は主に2種類あるのです
今回は行や列を指定したり、選択したりする記述方法についてご案内します。
その前にCellsプロパティについて、少し復習してみます。
Cellsプロパティ
------------------------------------------
Cells(1, 1).Select ’A1を選択
Cells(3, 2).Select ’B3を選択
------------------------------------------
詳しくはこちら:【VBAリファレンス】セルの値を指定する方法は主に2種類あるのです
今回は行や列を指定したり、選択したりする記述方法についてご案内します。
その前にCellsプロパティについて、少し復習してみます。
Cellsプロパティ
------------------------------------------
Cells(1, 1).Select ’A1を選択
Cells(3, 2).Select ’B3を選択
------------------------------------------
列と行を逆に記述するわけですが、
内容は変数、または数字で指定をすることがルールでした。
行や、列を表示する場合も基本的には同じ考え方で大丈夫です。
でも、実は列の場合AやBなどで指定することも可能です。
基本的には
行はRow(ロウ)
列はColumn(カラム)です。
では、さっそく基本的な記述をやってみます。
行の選択
------------------------------------------
'1行目だけを選択
Rows(1).Select
'1～5行目をまとめて選択
Rows("1:5").Select
------------------------------------------
Rows(1)のカッコの中は整数を代入した変数名を指定することも可能です。
なお、複数指定する場合はダブルクォーテーションで囲ったうえで
数値の間をコロンで挟んでください。
変数名を指定する場合の記述はちょっとややこしいですが以下の通りです。
Rows(a & ":" & b).Select (aに1を代入し、bに5を代入した時、1～5行目まで選択されます)
変数と組み合わせてセルの値や指定箇所を記述する詳しい方法は
後日ご紹介します。
列の選択
------------------------------------------
'A列を選択
Columns(1).Select
'同じくA列を選択
Columns("A").Select
'A～D列を選択
Columns("A:D").Select
------------------------------------------
列の場合は、整数での指定や
単独列の時はアルファベットを1文字入力して指定することが可能です。
その場合同じくダブルクォーテーションで囲うようにしてください。
私の場合、行、列を指定したうえで処理を行うことが多く、
For文を使用して特定間隔に処理を実行していくときに使用することが多いです。
その為、カッコの中は変数名が入ることが多いです。
また、お分かりかと思いますが
Select(選択する)だけではないので、Selectの箇所をDeleteに置き換えると
その列が削除されます。
もちろん、Copy&Pasteも可能なので
セルの操作と行列の操作を覚えるだけで、
データの操作性が拡張します。
是非色々と試してみてください。
2016年6月5日日曜日
【VBAリファレンス】初心者だからこそ、MsgBox(メッセージボックス)、結構慣れてきたからこそMsgBox(メッセージボックス)。
メッセージボックス(MsgBox)はVBAをやり始めたときに
メッセージが表示できるので、よく遊んで使っていました。
しかし、簡易的にプログラムの内容を確認するときや
実行コードの処理完了を報告する際など、
さまざま利用用途があります。
なので、初心者だからこそ、結構慣れてきた頃だからこそ、
利用用途を掴んで、活用してみてください。
基本使用例1
------------------------------
Sub test()
MsgBox "こんにちわ"
End Sub
メッセージが表示できるので、よく遊んで使っていました。
しかし、簡易的にプログラムの内容を確認するときや
実行コードの処理完了を報告する際など、
さまざま利用用途があります。
なので、初心者だからこそ、結構慣れてきた頃だからこそ、
利用用途を掴んで、活用してみてください。
基本使用例1
------------------------------
Sub test()
MsgBox "こんにちわ"
End Sub
------------------------------
上記コードを実行すると
基本使用例2
------------------------------
Sub test()
MsgBox 123456789
End Sub
------------------------------
直接、文字列を入力する場合はダブルクォーテーションで
囲んであげる必要があります。
しかし、数値や変数の結果を表示させる場合は
そのまま入力をしてあげれば大丈夫です。
基本使用例3
------------------------------
Sub test()
Dim a, b
a = 2 '変数aの箱に2を代入
b = 3 '変数bの箱に3を代入
MsgBox a + b 'aとbを足した結果をメッセージボックスで表示
End Sub
------------------------------
ここまで、基本使用例1～3まで記述してきましたが
主に使用頻度が多いのは1と3です。
1は長いコードを書いて、処理的にも時間がかかる場合、
全ての処理が完了した時、コードの最後に
MsbBox "完了しました"
と記載をすれば、そのメッセージが出たときに作業が完了したと判断できます。
3はForをお試しで使用したとき、カウンターの変動を確認したり
変数の結果を表示してみたりすることが出来ます。
初心者であれば、最初はそのような使い方をして全然良いと思います。
※慣れてきたころには【Debug.Print 変数】を使用して
イミディエイトウィンドウで表示し、確認してください。
Debug.Printとイミディエイトウィンドウの使用方法は後程ご紹介します。
【Excel】小数点の取り扱いにはご注意を…(関連:CSV、インポート、取り込み)
こないだ、とある事象にぶつかりました。
VBAでとかる箇所のデータをもとに
別の箇所からデータを引っ張ってくるコードを書いて
いざ実行してみると、上手く値が返ってこなかったのです。
エクセルの関数でいうとのことの
VLOOKUP関数の要領でやっていたのですが。
あれっ？って思い
今度は個別にMATCH関数を使用して
データの整合性を確認した見たところ、
やはりエラー値が返ってきます。
見た目には同じ値が入っているので、
結果は返ってくるはずなのに、エラー値が出る。
…ということはそれぞれのデータは別ものということです。
さて、何が理由か調べてみました。
まず、データの内容は数字、片方は0から1000くらいまで整数、
もう片方は、小数点以降2桁までの数値がある値で、
その値に100倍掛けして、結果的に整数に変えてます。
例): 0.02×100 =2 / 10.35×100 =1035
元々のデータはCSVから取り込んだデータです。
さて、まず原因について、判明した限りをお話しします。
どうやら、小数点以下の数値というかExcelの機能が
悪さをしていたようです。
図込みで、ちょっと説明します。
まず、メモ帳(Text)に0.123456789123456789と入力します。
小数点以下、18桁ですね。
そのテキストをそのままExcelに貼り付けます。
セルの書式は標準です。
セルの幅を自動でワイドにすると、そこで表示される文字は【0.123456789】となります。
しかし、セルの書式を数値にして、小数点以下30桁まで表示とします。
すると、出てきた表示は【0.123456789123456000000000000000】となってました。
最初にテキストで作成した文字が【0.123456789123456789】で
最終的にExcel内で処理されているのは【0.123456789123456】まで
そして、6の次の7は四捨五入されず、0に置き換えられています。
既にこの段階でユーザーの思いは儚く打ち砕かれているわけですね。
もう、ソッコーパーン!ですよ。
そして、このファイルを一旦CSVファイルとして保存してみます。
そのファイルを開きなおしてみると…
どうでしょうか。のっけから嫌な予感しかしません。
セルのA1にカーソルを合わせ、数式バーを確認したところ
【0.123456789】と表示が出ています。
そこで、その下にMATCH関数を入れてみます。
VBAでとかる箇所のデータをもとに
別の箇所からデータを引っ張ってくるコードを書いて
いざ実行してみると、上手く値が返ってこなかったのです。
エクセルの関数でいうとのことの
VLOOKUP関数の要領でやっていたのですが。
あれっ？って思い
今度は個別にMATCH関数を使用して
データの整合性を確認した見たところ、
やはりエラー値が返ってきます。
見た目には同じ値が入っているので、
結果は返ってくるはずなのに、エラー値が出る。
…ということはそれぞれのデータは別ものということです。
さて、何が理由か調べてみました。
まず、データの内容は数字、片方は0から1000くらいまで整数、
もう片方は、小数点以降2桁までの数値がある値で、
その値に100倍掛けして、結果的に整数に変えてます。
例): 0.02×100 =2 / 10.35×100 =1035
元々のデータはCSVから取り込んだデータです。
さて、まず原因について、判明した限りをお話しします。
どうやら、小数点以下の数値というかExcelの機能が
悪さをしていたようです。
図込みで、ちょっと説明します。
まず、メモ帳(Text)に0.123456789123456789と入力します。
小数点以下、18桁ですね。
そのテキストをそのままExcelに貼り付けます。
セルの書式は標準です。
セルの幅を自動でワイドにすると、そこで表示される文字は【0.123456789】となります。
しかし、セルの書式を数値にして、小数点以下30桁まで表示とします。
すると、出てきた表示は【0.123456789123456000000000000000】となってました。
最初にテキストで作成した文字が【0.123456789123456789】で
最終的にExcel内で処理されているのは【0.123456789123456】まで
そして、6の次の7は四捨五入されず、0に置き換えられています。
既にこの段階でユーザーの思いは儚く打ち砕かれているわけですね。
もう、ソッコーパーン!ですよ。
そして、このファイルを一旦CSVファイルとして保存してみます。
そのファイルを開きなおしてみると…
どうでしょうか。のっけから嫌な予感しかしません。
セルのA1にカーソルを合わせ、数式バーを確認したところ
【0.123456789】と表示が出ています。
そこで、その下にMATCH関数を入れてみます。
B6にはテキストに一番最初に起こした【0.123456789123456789】を入れて
その下のB7にはCSVファイルのA1の値をコピーし、メモ帳に貼り付け、
それを再度コピーし、CSVファイルのB7に貼ったもの、
それに対して=MATCH(B6,$A$1,0)の式を当て込んでみたところ、
何と、元々の値は一致しないというエラー値が返ってきたのです。
ぶべらっ～!! (-3*)
もう、この時点で、元の【0.123456789123456789】は存在しないことになっています。
ところが、A1とA2、同じ値が入っているように見えますが
実際に見直してみると
値が違うことが分かります。
CSV化することで、その時のデータの書式により
保存される内容が変わってきます。
初めてこのファイルを見た人は
すでに真実は夢の彼方なのです。
さてさて、ここまで余計なことを含め、お話してきましたが
そもそもはデータが一致しないというお話でした。
その為、100倍掛けした数値を一旦Int(Integer)に置き換えてみたところ
きっちりとかみ合い、結果希望するデータを導き出すことが出来ました。
原因はおそらく元々のCSVファイルデータにおいて
小数点以下でも取り切れない箇所に、余計なデータが隠れてたとしか
推測することが出来ません。
プログラムの世界ではFloatやDoubleといって
この浮動小数点に対する細かい型が決められており
計算もきっちり行われるのですが、
こと、Excelに関しては、まず書式で小数点以下30桁まで設定できるにも拘らず
小数点15桁までしか表示がされません。
また、関数の一致に関しても同じ扱いになります。
というか入れられないものは入れられないよのスタンスみたいです。
(IEEE 754 仕様に準拠らしい)
結構いい加減。
なお、15桁以上の数字を表示するためには
書式を文字列にすればいらしいのですが
これもいったんCSVファイルにしてしまうと、15桁まで扱いになってしまいます。
関数をかけた結果、あっているはずの答えがエラー値で返ってきてしまうようであれば
まず、この辺りを疑い、
しかるべきデータ型(Int,Double)などに置き換えてみてください。
あと、Rondなどで四捨五入する方法もあります。
2016年6月2日木曜日
【VBA実践】別のシートでコピーしたデータを1つのシートに集約させて、蓄積させていく。
私がVBAをマスターしたいと思った技術の1つです。
誰しもがブック内にあるたくさんのシートにあるデータを
1つのシートにまとめたうえで管理したいと考えたことがあると思います。
もちろん、シートだけでなく他のブックからデータを引っ張ってくることも出来ます。
VBAの勉強を始めようとした私が最初にぶち当たった壁なんですが
これは、マクロの記録だけでは対処のしようがないんです。
マクロの記録とVBAは同じようで同じではないです。
マクロは処理の自動化で
Excel内で作業をした内容を記録させて
同じ動きをさせることが出来る機能なんです。
厳密にいうと、さらに意味は全く違いますが。
VBAはそんな自動化処理をするための文章を
直接書き込む事なんです。
似て非なるもの。
【白い恋人】と【面白い恋人】くらい差があるかもしれません。
そして、VBAには出来て、マクロの記録には出来ないことがあります。
条件により作業内容を変える!ということ。
マクロはやったことを記録して、その通り繰り返すだけ。
VBAは条件に応じてその先の処理内容を変えていくことが出来ます。
すみません、横道にそれましたが
今回のテーマもデータを蓄積させるさせるためには
データが入っている一番下の次のセルに別のデータを張るという作業の繰り返しになります。
ここで必要な大きな作業内容を説明しますと…
・蓄積させたいデータコピーする。
・貼り付けたいシートを選択し、現段階でシート内に
データが何行目まで入っているかを確認する。
・そのデータの1つ下のセルにデータを貼り付ける。
・蓄積させたいデータが入っているシートの数だけ
その作業をループする。
大きく分けてその4つ分のコードを書けば、希望の処理を行うことが出来ます。
まず、答えは以下の通りです。
※ブック内一番左側にAll_dataという名前のシートがあり、
以降そのシートにデータを蓄積させる元データのシートがあることを前提としてます。
-------------------------------------------------
Sub test()
Dim scount, row As Long
For scount = 2 To Sheets.Count
Sheets(scount).Select
Range("A1").Copy
Sheets("All_data").Select
row = Cells(Rows.Count, 1).End(xlUp).row + 1
Cells(row, 1).Select
ActiveSheet.Paste
Next scount
End Sub
この中に上の4つの条件が書かれています。
順序としては以下の通りです。
・蓄積させたいデータが入っているシートの数だけ
その作業をループする。
For scount = 2 To Sheets.Count
～～処理内容～～
Next scount
ここでFor文というのを使います。
ここでの使い方はシート2シート目から
最後のシートまでの枚数を割り出し、順に処理をしていくという
ループ処理指示になります。
なお、For文に関しては
後日別の記事で詳しくご案内しますね。
・蓄積させたいデータコピーする。
For文で選択されたシートのコピーしたいセルを指示して
コピーします。
Sheets(scount).Select
Range("A1").Copy
この2行です。
なお、RangeプロパティでもCellsプロパティでも、
Rangeプロパティで広い範囲を指定してコピーしてもOKですよ。
・貼り付けたいシートを選択し、現段階でシート内に
データが何行目まで入っているかを確認する。
Sheets("All_data").Select
row = Cells(Rows.Count, 1).End(xlUp).row + 1
Cells(row, 1).Select
貼り付けるシート、つまりAll_dataというシートを指示してあげます。(1行目)
ここからが魔法の言葉なんですが
Cells(Rows.Count, 1).End(xlUp).rowという記述が
選択しているシートで何行目までデータが入っているかというのを
調べる記述です。
Cells(Rows.Count, 1)の1はA列を示してるので
B列の末端を調べるときはここを2に変えてください。
それを変数rowに代入して、Cellsプロパティに当て込んであげればいいわけです。
しかし、End(xlUp).rowの後には必ず【+1】を付け加えてあげてください。
そうじゃないと、ずっと同じ場所にデータが毎回貼り付けられてしまい
蓄積もクソもない、鬼畜の所業になってしまいます。
もっちょいかみ砕いていうと、
データが入っている最終行を割り出したその下にデータを貼り付けたいので
最終行の1つ下、つまり【+1】を付与して1つしたにずらしてあげるわけです。
・そのデータの1つ下のセルにデータを貼り付ける。
ActiveSheet.Paste
あとは選択した箇所にペーストしてあげればOKです。
こんな時、特にCellsプロパティの便利さが実感できます。
たかだか260文字程度を入力するだけで
たくさんのシートにあるデータをかき集めることが出来るのです。
しかも一瞬で。
もちろん、これが全世界の正解ではありません。
VBAはたくさんのやり方が存在します。
これはそんな方法の1つだと思っていただければ思います。
誰しもがブック内にあるたくさんのシートにあるデータを
1つのシートにまとめたうえで管理したいと考えたことがあると思います。
もちろん、シートだけでなく他のブックからデータを引っ張ってくることも出来ます。
VBAの勉強を始めようとした私が最初にぶち当たった壁なんですが
これは、マクロの記録だけでは対処のしようがないんです。
マクロの記録とVBAは同じようで同じではないです。
マクロは処理の自動化で
Excel内で作業をした内容を記録させて
同じ動きをさせることが出来る機能なんです。
厳密にいうと、さらに意味は全く違いますが。
VBAはそんな自動化処理をするための文章を
直接書き込む事なんです。
似て非なるもの。
【白い恋人】と【面白い恋人】くらい差があるかもしれません。
そして、VBAには出来て、マクロの記録には出来ないことがあります。
条件により作業内容を変える!ということ。
マクロはやったことを記録して、その通り繰り返すだけ。
VBAは条件に応じてその先の処理内容を変えていくことが出来ます。
すみません、横道にそれましたが
今回のテーマもデータを蓄積させるさせるためには
データが入っている一番下の次のセルに別のデータを張るという作業の繰り返しになります。
ここで必要な大きな作業内容を説明しますと…
・蓄積させたいデータコピーする。
・貼り付けたいシートを選択し、現段階でシート内に
データが何行目まで入っているかを確認する。
・そのデータの1つ下のセルにデータを貼り付ける。
・蓄積させたいデータが入っているシートの数だけ
その作業をループする。
大きく分けてその4つ分のコードを書けば、希望の処理を行うことが出来ます。
まず、答えは以下の通りです。
※ブック内一番左側にAll_dataという名前のシートがあり、
以降そのシートにデータを蓄積させる元データのシートがあることを前提としてます。
-------------------------------------------------
Sub test()
Dim scount, row As Long
For scount = 2 To Sheets.Count
Sheets(scount).Select
Range("A1").Copy
Sheets("All_data").Select
row = Cells(Rows.Count, 1).End(xlUp).row + 1
Cells(row, 1).Select
ActiveSheet.Paste
Next scount
End Sub
-------------------------------------------------
※変数は赤文字で表示してます。この中に上の4つの条件が書かれています。
順序としては以下の通りです。
・蓄積させたいデータが入っているシートの数だけ
その作業をループする。
For scount = 2 To Sheets.Count
～～処理内容～～
Next scount
ここでFor文というのを使います。
ここでの使い方はシート2シート目から
最後のシートまでの枚数を割り出し、順に処理をしていくという
ループ処理指示になります。
なお、For文に関しては
後日別の記事で詳しくご案内しますね。
・蓄積させたいデータコピーする。
For文で選択されたシートのコピーしたいセルを指示して
コピーします。
Sheets(scount).Select
Range("A1").Copy
この2行です。
なお、RangeプロパティでもCellsプロパティでも、
Rangeプロパティで広い範囲を指定してコピーしてもOKですよ。
・貼り付けたいシートを選択し、現段階でシート内に
データが何行目まで入っているかを確認する。
Sheets("All_data").Select
row = Cells(Rows.Count, 1).End(xlUp).row + 1
Cells(row, 1).Select
貼り付けるシート、つまりAll_dataというシートを指示してあげます。(1行目)
ここからが魔法の言葉なんですが
Cells(Rows.Count, 1).End(xlUp).rowという記述が
選択しているシートで何行目までデータが入っているかというのを
調べる記述です。
Cells(Rows.Count, 1)の1はA列を示してるので
B列の末端を調べるときはここを2に変えてください。
それを変数rowに代入して、Cellsプロパティに当て込んであげればいいわけです。
しかし、End(xlUp).rowの後には必ず【+1】を付け加えてあげてください。
そうじゃないと、ずっと同じ場所にデータが毎回貼り付けられてしまい
蓄積もクソもない、鬼畜の所業になってしまいます。
もっちょいかみ砕いていうと、
データが入っている最終行を割り出したその下にデータを貼り付けたいので
最終行の1つ下、つまり【+1】を付与して1つしたにずらしてあげるわけです。
・そのデータの1つ下のセルにデータを貼り付ける。
ActiveSheet.Paste
あとは選択した箇所にペーストしてあげればOKです。
こんな時、特にCellsプロパティの便利さが実感できます。
たかだか260文字程度を入力するだけで
たくさんのシートにあるデータをかき集めることが出来るのです。
しかも一瞬で。
もちろん、これが全世界の正解ではありません。
VBAはたくさんのやり方が存在します。
これはそんな方法の1つだと思っていただければ思います。
2016年6月1日水曜日
【Excel】オーフィルと親父の小言は後で効く…
オートフィル。
オートフィルタと似てますが、一文字違いで全く別のものです。
オートフィル
Excelで連続的にデータがあり
その隣のセルにそのデータに対して関数を設定し
上から下までその関数を同じ条件で入れるときに
関数を入れて、そのセルの右下のあたりに
ポインタを持っていったら、白い太めの十字から
黒い細めの十字に変わります。
その瞬間に、マウスをダブルクリックすると
隣のデータが入っている下のセルまで
ザイーン！！って関数が引き継がれる、なんとも便利なExcelの機能です。
さて、勘のいい人はここでピンと来るはずです。
隣のセルのデータが入っているところ。
そうです。
つまり、A1からA10000までデータが入っていて
そのとなりのB1からB10000まで関数をザイーン！ってさせたい時。
縦10000行まで結構な長さがあります。
これ、私みたいなめんどくさがり屋さんは
ろくに下の10000行までデータが入っていることを確認せず
次の作業に進めようとします。
だって、オートフィルじゃん！
下まで行ってんじゃん！
でも。
もし。
A6859行目にデータが何も入ってなくて
A6860行目から、続きのデータが入っていたら…
はぅあ～！！
オートフィルはA6859で止まってしまっているのです。
はい～、処理ミス発生!
慣れてきたころ、このオートフィルの機能を軽視して
ろくに最後まで確認せず次の作業に移行しようとする輩が
多くなるものです。私のように。
今日、このオートフィルの機能を軽視した私は
爆死しました。
本当にすみません。
私が無意識でも調子に乗り始めようものなら
オートフィル先輩は
調子に乗るなよ!!
と、常に戒めてくれました。
私の長所でもあり短所でもある、
すぐ調子に乗ること。
過去どんだけあるんだって話ですが。
人間としてまだまだなのであります。
VBAのオーフィルはいつかご紹介させて頂きますね。
オートフィルタと似てますが、一文字違いで全く別のものです。
オートフィル
Excelで連続的にデータがあり
その隣のセルにそのデータに対して関数を設定し
上から下までその関数を同じ条件で入れるときに
関数を入れて、そのセルの右下のあたりに
ポインタを持っていったら、白い太めの十字から
黒い細めの十字に変わります。
その瞬間に、マウスをダブルクリックすると
隣のデータが入っている下のセルまで
ザイーン！！って関数が引き継がれる、なんとも便利なExcelの機能です。
さて、勘のいい人はここでピンと来るはずです。
隣のセルのデータが入っているところ。
そうです。
つまり、A1からA10000までデータが入っていて
そのとなりのB1からB10000まで関数をザイーン！ってさせたい時。
縦10000行まで結構な長さがあります。
これ、私みたいなめんどくさがり屋さんは
ろくに下の10000行までデータが入っていることを確認せず
次の作業に進めようとします。
だって、オートフィルじゃん！
下まで行ってんじゃん！
でも。
もし。
A6859行目にデータが何も入ってなくて
A6860行目から、続きのデータが入っていたら…
はぅあ～！！
オートフィルはA6859で止まってしまっているのです。
はい～、処理ミス発生!
慣れてきたころ、このオートフィルの機能を軽視して
ろくに最後まで確認せず次の作業に移行しようとする輩が
多くなるものです。私のように。
今日、このオートフィルの機能を軽視した私は
爆死しました。
本当にすみません。
私が無意識でも調子に乗り始めようものなら
オートフィル先輩は
調子に乗るなよ!!
と、常に戒めてくれました。
私の長所でもあり短所でもある、
すぐ調子に乗ること。
過去どんだけあるんだって話ですが。
人間としてまだまだなのであります。
VBAのオーフィルはいつかご紹介させて頂きますね。
登録:
投稿 (Atom)