ExcelVBAの基礎知識

イミディエイトウィンドウの使い方

イミディエイトウィンドウは、変数やプロパティの値や式の結果を出力することができます。

変数などの値の確認に使われることが多いです。
イミディエイトウィンドウに出力するには、Debug.Printメソッドを使います。
Debug.Print 出力したい内容


たとえば、変数でセルを指定している時、自分が取得したいと思っているセルと実際に取得しているセルが違ってマクロがうまく動かない……といったことがあります。
そんな時はセルのアドレスをイミディエイトウィンドウに出力して、ちゃんとセルが取得できているかを調べることができます。

サンプルコード



Public Sub ImmediateWindowsTest1()
  Dim LastRow As Long
  
  With ThisWorkbook.Worksheets(1)
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    Debug.Print .Cells(LastRow, "A").Address
  End With
End Sub


また、コードを書かずにイミディエイトウィンドウに直接出力もできます。
イミディエイトウィンドウにカーソルを合わせ、「?出力したい内容」を書いて、Enterキーを押すと、次の行に結果が表示されます。

VBAでは文字列はダブルクォーテーション(")で囲むのですが、ダブルクォーテーション自体を出力したいときはダブルクォーテーションをダブルクォーテーションで囲む必要があります。
慣れないとダブルクォーテーションが足りずにダブルクォーテーションが出力できていなかったり、逆に多すぎて2個出力されていたりと、これが結構難しい。

そんな時も、イミディエイトウィンドウで出力できたのを確認してから、実際のコードペインにコピー&ペーストすると、うまくいきます。
ImmediateWindow01.jpg

出力したい内容には変数だけではなく、式も入れることができます。
変数のデータ型でエラーが出たときにTypeName関数で変数のデータ型を調べたり、2つのオブジェクト変数が等しいか確認するなんて事もできます。

マクロがうまく動かないときは、ステップインで1行ずつ実行しながらイミディエイトウィンドウで変数の値を確認して、OKだったらまた次の行を実行して……という風に繰り返していくことで、エラーが見つけやすくなります。

ExcelVBAの基礎知識

マクロの確認をする

コンパイルでVBAコードをチェックする


最初のうちは入門書やネットのコードを見よう見真似で書いても、細かいところが間違っていて「マクロが動かない!」ということがよくあります。

VBEのメニューから[デバッグ(D)]→[VBAProjectのコンパイル(L)]を実行すると、
書いたコードがVBAの文法に合っているかをチェックできます。

compile01.jpg

間違っている箇所が反転し、エラー内容がメッセージボックスに表示されます。
compile02.jpg


コンパイルエラーになる例


コンパイルエラーになる例はいろいろありますが、頻度が高いものをあげてみます。

  1. 存在しないメソッド・プロパティを指定する

  2. Public Sub CompileTest1()
      ThisWorkbook.Test
    End Sub


  3. 読み取り専用のプロパティに値を入れる

  4. Public Sub CompileTest2()
      ThisWorkbook.Path = "C:¥Test"
    End Sub


  5. 終了ステートメントがない

  6. ステートメントの中には、「IfステートメントならEnd Ifステートメント」、「WithステートメントならEnd Withステートメント」といったように、開始と終了のステートメントをセットで使うものがあります。

    終了ステートメントが抜けていたり、ステートメントを入れ子にしていて、終了ステートメントの順番が開始ステートメントの順番と違っていると、コンパイルエラーになります。

    下のサンプルでは、End WithとForステートメントのNextの位置が逆になっているので、コンパイルエラーになります。
    Public Sub CompileTest()
      Dim i As Long
      For i = 1 To 5
        With ThisWorkbook.Worksheets(1)
          If i = 1 Then
            '処理
          End If
        Next i
      End With
    End Sub



コラム

ブログ更新

なんとなく寝れなかったので記事を4本書いてみました。

毎日コツコツと書けばいいのでしょうが、いつも一挙更新、
その後更新停止・・・・・・となってしまいます。

それでも続けているのは、やっぱりExcelVBAが好きだから。

なにか物を作るのは好きだけど、芸術はなにが「いいもの」で
なにが「よくないもの」の基準が曖昧すぎて、あまり好きじゃないんですね。

プログラムっていうのは正しければ動くし正しくなかったら動かない。
ほかにも、見やすいとか、処理が早いプログラムが「いいもの」と、基準がハッキリしている。

それでいて物づくりの楽しさを味わえる、というところが
すごく私の性格にマッチしていて楽しいです。

ExcelVBAの場合は作ったマクロをすぐに動かせるので、
「結果が早く出る」というのもいいところですね。

印刷関係サンプルマクロ

すべてのシートに対して同じ印刷設定をする

Excelの改善して欲しい機能に印刷設定があります。

複数のシートを選択した状態で印刷設定すると、
選択したすべてのシートすべてに同じ印刷設定を適用して欲しいのに、
1つのシートしか設定されないんですよね。

そこで、すべてのシートに対して同じ印刷設定をするマクロを作ってみました。

サンプルコード



Sub すべてのシートに対して同じ印刷設定をする()
Dim objWS As Worksheet

For Each objWS In ThisWorkbook.Worksheets
  With objWS.PageSetup
    .RightHeader = "&A"
    .CenterFooter = "&P/&N"
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = False
  End With
Next objWS

End Sub


解説


マクロが記述されているブックにあるすべてのシートに対して、
次の印刷設定を行います。

右上ヘッダー:シート名
中央フッター:ページ番号/総ページ数
拡大縮小印刷:横1ページ、縦指定なし
一括印刷設定

ほかは現在の設定を変えません。


印刷設定のプロパティはとてもいっぱいあって、覚えるのは無理なので
マクロ記録から起こします。

プロパティ名はそのものズバリな名前なので特に説明なくてもわかるはず。
拡大縮小印刷だけ、ZoomプロパティをFalseにしておかないと
FitToPagesWideプロパティやFitToPagesTallプロパティを設定しても反映されないので注意が必要です。

設定を変えなくていい部分はマクロに記述しなくてOKです。

ExcelVBATips

マクロ以外での効率化!

VBAやマクロを勉強している人はもっと仕事を効率化しよう!という方が多いと思います。
マクロを覚えるのもいいのですが、それ以外にも効率化できる部分はあります。

たとえば、ツールバー。
ワークシートの挿入やセルの結合など、意外とよく使うボタンが入っていません。
Excelでは自分のよく使う機能のボタンを追加したりできるので、
いろいろカスタマイズすると使いやすくなります。

1.ツールバーによく使う機能のボタンを追加する


ツールバーの空いているところで右クリックをし、「ユーザー設定」から
ボタンを追加することができます。

ユーザー設定のコマンドタブから、追加したいボタンをドラッグするだけです。
ツールバーのカスタマイズ01

私は以下のような機能を追加しています。
・改ページプレビュー

・ワークシートの挿入
・セルの結合
・セルの結合の解除
・横方向に結合
・オートフィルタ
・電卓

・取り消し線

図形描画では
・グループ化
・グループ解除
・最全面に移動
・最背面に移動

など。
かなりカスタマイズしているので、たまに初期設定のExcelをみると
ボタンの数が少なすぎて「あれっ!?」となります。
ツールバーのカスタマイズ02

2.よく使うファイルのショートカットを追加する


また、ボタンだけではなくメニューも追加できます。
追加方法はボタンと同じ。

メニュー(押すとコマンドが出てくる)を追加したいときは新しいメニュー、
コマンドを追加したいときはマクロの「ユーザー設定メニュー項目」をドラッグします。
ツールバーのカスタマイズ03

追加してみるとこんな感じ。
ツールバーのカスタマイズ04

まだコマンドの動作を設定していないので、押しても何にもなりません。

ユーザー設定が開いている状態でコマンドを右クリックすると、
メニューが表示されます。
ツールバーのカスタマイズ05

ハイパーリンクの設定があり、ここでファイルへのハイパーリンクの設定をしておくと、
ボタンを押すと同時にそのファイルが開きます。

毎日使うファイルは、ショートカットとして登録しています。
使用頻度の高いファイルにすぐアクセスできて便利です。

2007からはインターフェースがガラっと変わってしまっていますが・・・・・・。
私が今働いているところはまだまだExcel2002/2003が現役です。

作成者向けマクロ

ランダムな順で並べ替える

サンプル用のデータで不規則なデータを考えるのが面倒なので、
もう少し楽にできないかなと考えて作ってみました。

A列の値をランダムに並べ替えてB列に表示するマクロです。

サンプルコード



Sub ランダムな順で並べ替え()

Dim LastRow As Long     '全体の行数
Dim lngRnd As Long     '1〜行数までのランダムな数
Dim aryData() As String   '並べ替え後の値
Dim checkAry() As Boolean  'インデックスNoの値がすでに出たかのチェックフラグ
Dim i As Long
Dim ii As Long       'aryDataのインデックス番号

With ThisWorkbook.Worksheets(1)
  ii = 1
  LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
  
  '◆入替用の配列作成
  ReDim aryData(1 To LastRow, 1 To 1)
  ReDim checkAry(1 To LastRow)
  
  '◆行数分だけ繰り返し
  For i = 1 To LastRow
    '◆今までに出てない数値が出るまで繰り返し
    Do
      Randomize
      lngRnd = Int(Rnd * LastRow) + 1
    Loop Until checkAry(lngRnd) = False
    
    '◆値を配列に格納してチェックフラグをTrueにする
    aryData(ii, 1) = .Cells(lngRnd, "A").Value
    checkAry(lngRnd) = True
    ii = ii + 1
  Next i
  
  '◆B列に並べ替え後の値を書き出し
  .Range(.Cells(1, "B"), .Cells(LastRow, "B")).Value = aryData
End With
End Sub


使用例


こんな感じになります。
ランダムな順で並べ替える01

解説


Rnd関数はヘルプの説明をみてもさっぱりわからなかったので
重複しないランダムな数を取得する方法はmorgの即効テクニックを参考に作りました。
重複を許可せず、ランダムな数をセルに代入する

向こうのページにはコードだけで解説はまったくないので、わかる範囲で解説してみます。

すでに出た値かどうかはBoolean型の配列を作成して、その値でチェックしています。
たとえば、このサンプルでいうと配列checkAryです。

1行目〜最終行まで入れ替えを行いたいので、checkAryの要素数は最終行と同じにしています。
ただし、最終行が何行目かは、変数宣言時にはわかりません。

そこで、宣言時には要素数を指定しないでおいて、最終行が決まった段階で、
ReDimステートメントで配列を定義しなおしています。
(こういう配列のことを動的配列といったりします)

最終行が10行目の場合、以下のような配列が作成されます。
checkAry(1)
checkAry(2)
checkAry(3)
・・・
checkAry(10)

値はすべてFalseが入ってます(Boolean型の初期値)

Int(Rnd * LastRow)で0〜LastRow-1までのランダムな値が作られるので、
それに1を加算しています。

その値が1の場合checkAry(1)をTrue、2の場合はcheckAry(2)・・・・・・と
その値が出たらその数値のインデックス番号の値をTrueをにしています。

つまり
checkAry(1) False
checkAry(2) True
checkAry(3) True

なら1はまだ出ていない、2と3は出た、という意味になります。

重複しない数が出るまで繰り返すには、Do...Loopステートメントを使います。
Untilを後に持ってくることで、1回目は無条件に実行、
2回目以降は条件を満たすまで繰り返しています。

ランダムな値が取得できたら、あとはその行番号の値を順番に配列に格納していきます。
値が1つだけなので1次元配列で

aryData(1) 値1
aryData(2) 値2
aryData(3) 値3

としてもいいのですが、あとでセルに書き込むとき、1つ1つ順番に書き込むより
配列から一気にセル範囲に書いたほうが速いのでわざと2次元配列にしています。

あとはB列に2次元配列から値を書き込んで終了です。

ExcelVBAサンプルマクロ

ピボットテーブルの内容をセルに転記

Excelでクロス集計を行うには、ピボットテーブルを使うのが一番簡単なのですが、
ピボットテーブルって結構使いにくかったりしませんか?

データを集計したあと、うっかり関係ないところをドラッグしてしまって
表の形式が崩れたり・・・・・・。

クロス集計したデータさえ見れればOK、というときはピボットテーブルを作成した後、
セルに転記して元のピボットテーブルは削除してしまう、というのも手です。

以下は、ピボットテーブルのデータをセル範囲に転記するサンプルです。

サンプルコード



Sub ピボットテーブルの内容をセルに転記()

Dim strPivotName As String 'ピボットテーブル名
Dim DataArea As Range    'ピボットテーブルのセル範囲
Dim aryData As Variant   'データ範囲から値を取り込む2次元配列

strPivotName = "ピボット1"

With ThisWorkbook.Worksheets(1)
  '◆ピボットテーブルのデータ範囲取得
  Set DataArea = .PivotTables(strPivotName).TableRange1
  
  '◆見出し行の分セル範囲を縮める(データ範囲を1行下へずらし、1行分縮める)
  Set DataArea = DataArea.Offset(1, 0)
  Set DataArea = DataArea.Resize(DataArea.Rows.Count - 1)
  
  '◆セル範囲から2次元配列作成
  aryData = DataArea
  
  '◆セル範囲に転記&罫線設定
  DataArea.Offset(10, 0) = aryData
  DataArea.Offset(10, 0).Borders.LineStyle = True
  
  '◆元のピボットテーブルを削除
  .PivotTables(strPivotName).TableRange1.Clear
End With

End Sub


使用例


先にピボットテーブルを作っておきます。名前は「ピボット1」としました。
ピボットテーブルの内容をセルに転記01

実行後はこんな感じ。
ピボットテーブルの内容をセルに転記02

サンプルということで、転記する表の位置はやっつけですが、
新規シートにピボットテーブルを一時作成し配列に取り込んだあと、
ピボットテーブルのシートを削除としてもいいと思います。
レコードセットからピボットテーブルを作成するなどと組み合わせてもいいですね。

マクロ解説


ピボットテーブルのデータ範囲は、PivotTableオブジェクトの
TableRange1プロパティを参照します。
ちなみに、TableRange2プロパティもあります。
違いは、
・TabelRange1プロパティ→ページフィールドは含まれない。
・TabelRange2プロパティ→ページフィールドも含む。
らしい。(ヘルプより)

Variant型の変数にセル範囲を指定すると、セル範囲から2次元配列を作成します。

TableRange1プロパティはRangeオブジェクトを返すので、そのまま
「Variant型変数 = TableRange1プロパティ」として2次元配列を作成してもいいのですが、
ピボットテーブルの最初の行にある「合計:値」「日付」のプルダウンが邪魔なので
少しセル範囲を変えています。

Offsetプロパティは、指定した行または列分だけセル範囲をずらすプロパティです。
戻り値は変更したあとのセル範囲(Rangeオブジェクト)です。

◆Offsetプロパティ
Offset(ずらす行数、ずらす列数)
正の数を指定すれば行番号・列番号は大きくなります(下/右にずれます)
負の数を指定すれば行番号・列番号は小さくなります(上/左にずれます)

最初の状態ではデータ範囲はE1:I6ですが、Offsetで1行ずらしてE2:I7にします。
◇最初のデータ範囲(E1:I6)
ピボットテーブルの内容をセルに転記03

◇Offset後のデータ範囲(E2:I7)
ピボットテーブルの内容をセルに転記04

ただ、1行ずらすと7行目は必要ないので、1行分セル範囲を縮めないといけません。
そこで、Resizeプロパティを使います。

◆Resizeプロパティ
Resize(変更後の行数、変更後の列数)
引数を省略すると、元の行数と同じ値になります。
元データの行数はデータ範囲.Rows.Countで取得できますので、
DataArea.Resize(DataArea.Rows.Count - 1)
とすれば、元のデータから1行分縮めた範囲がセル範囲となります。(E2:I6)


ExcelVBAの基礎知識

VBA用語集

プログラム全般に関する用語


コード
プログラム言語で書いた命令文のこと。
ソースともいう。

コーディング
プログラムのコードを書くこと。

バグ
プログラムの中にある不具合のこと。

デバッグ
プログラムがきちんと動くか、動作確認すること。
また、バグが発生した場合にコードを修正すること。

デバッガー
デバッグをする人。

VBAの命令に関わる用語


メソッド
VBAの命令のうち、オブジェクトやコレクションに対して特定の操作を行う命令。
操作のオプションとして、引数(ひきすう)を指定することもある。
Openメソッド、Copyメソッド、Closeメソッドなどなど。
マクロ記録でコードを記録できる。

プロパティ
セルの色、シートの名前、ブックのパスなどのオブジェクトの設定値。
プロパティには、値を取得するだけのプロパティと、
値を入れることのできるプロパティがある。

ステートメント
VBAの命令のうち、オブジェクトには関係しない命令。
条件分岐をするIfステートメント、Select...Caseステートメント、
繰り返し処理をするDo...Loopステートメントなど。
ステートメントは「ステートメント名」で始まって、「End ステートメント名」で終了することが多い。
(例)
If 条件式 Then
 '処理
End If


関数
引数をもとに、計算を行い結果を返すプログラム。
Date関数など、引数を指定しない関数もある。
Functionステートメントでオリジナルの関数を作ることもできる(ユーザー定義関数)

引数
メソッドや関数を実行する時に、指定する値。
メソッドでは、処理のオプションを引数で指定することが多い。
(Findメソッドなら検索オプションなど)

戻り値
関数を実行したあとに、返ってくる結果。
VBAでは、関数の戻り値を取得する場合は引数を()で囲む。
Msgbox "Test"・・・・・・メッセージを表示するだけで、戻り値は取得しない。
result = Msgbox("Test")・・・・・・戻り値を取得する。


ExcelVBAの基礎知識

実際にマクロを作りはじめる前に

マクロの対象ブックとマクロ本体はわける



たとえば、ブックからデータを集計するマクロだったら、
集計対象のブックとマクロを書くブックはわけた方が無難です。

データが入力されているブックというのは、間違えてデータいじってしまっても
復元できるように、バックアップを取ることがあります。

マクロをデータのあるブックに直接書いてしまうと、
データのバックアップをとったとき、当然マクロも一緒にバックアップされます。

マクロを修正して問題がないことを確認したあと、
手動操作でミスをしてファイルをバックアップから復元した場合、
マクロが意図せず修正前に戻ってしまいます。

特に、会社で複数人数で使っているファイルだと、いつ誰が復元するか予測がつきません。

いつのまにか修正したところが元に戻っている、というのは
様々なエラーの原因になります。

ですので、データのあるブックとマクロ本体はわけた方がいいのです。

そこで重要になるのが、ブックやシートの指定の仕方です。
他のブックやシートを指定する方法はマクロ記録では得られないうえに
使用頻度が高いので、しっかりと覚える必要があります。

また、ブックやシートをマクロで指定すればアクティブではないブックも操作できます。
いちいちSelectメソッドでアクティブにする必要がなくなるので、
マクロの大幅な高速化にも役にたちます。


マクロはなるべく標準モジュールに書く


マクロは、標準モジュールのほかにもWorkbookオブジェクトやWorksheetオブジェクト内にも書くことができます。
ただし、特別なことがない限り、標準モジュールに書くのがおすすめです。

標準モジュールは、当たり前ですがマクロがないブックには存在しません。
標準モジュールに書けば、プロジェクトエクスプローラでモジュールが何個あるかすぐわかります。
WorkbookオブジェクトやWorksheetオブジェクトはマクロがあろうがなかろうが存在するオブジェクトのため、これらに書いてしまうとマクロが見つけにくくなります。

また、Worksheetオブジェクトに書いてしまうと、シートをコピーしたときにマクロも一緒にコピーされます。
前述の「マクロの対象ブックとマクロ本体はわける」とも共通しますが、
意図せずにマクロのコピーが量産されると、どれが正しい最新版かがわからなくなり、
エラーの原因になります。

VBAの応用知識

クラスとは

Excelのマクロを書くのには、標準モジュールを使いますが、
実は、標準モジュールだけではなく、
クラスモジュールというモジュールもあります。
クラスとは01

私は最初、どういうものなのか、なかなか理解できませんでした。
Excelの本の解説を読んでみても何だかわかったような、わからないような・・・・・・。

最近では「なんとなく、こうじゃないかな?」というのがわかってきたので書いてみます。

クラスとは、オブジェクトのフォーマットようなものです。
「クラスとはなにか」「クラスモジュールで何ができるか」ということを説明します。

前提:オブジェクトの種類が同じなら、メソッド・プロパティの種類も同じ



クラスを説明する前提として、「オブジェクトの種類が同じなら、メソッド・プロパティの種類も同じになる」というものがあります。
たとえば、シート1とシート2の2つのWorkSheetオブジェクトがあるとします。

シート1にはCopyメソッドがあるけどシート2にはCopyメソッドがない、ということはありえません。
また、シート1にはNameプロパティがあるけどシート2にはNameプロパティがない、ということもありえません。

プロパティは共通で、それぞれのプロパティに入っている値が違うだけなのです。
クラスとは02

なぜクラスが必要か


項目が同じなら、毎回いちいち新しく作るよりも、あらかじめフォーマットを作っておいた方が効率的です。

たとえば、「毎月報告書を出す」といった場合も、毎回まっさらなファイルからは作りませんよね。
1つフォーマットを作っておいて、使うときにフォーマットをコピーし、コピーしたものに書くと思います。

クラスもそれと同じです。

毎回、メソッドとプロパティを全部定義するのは大変。
だから、クラスというフォーマットを作っておくわけですね。

クラスには
・オブジェクトのメソッド
・オブジェクトのプロパティ
・プロパティの初期値

などを定義しておきます。


オブジェクトを使うときは、クラス(フォーマット)のプロパティに直接
値を入れてしまうと、初期値がわからなくなってしまいます。

そのため、フォーマットを使うときは必ずコピーし、コピーしたオブジェクトのプロパティに値を入れます。

クラスからコピーされたオブジェクトのことを「インスタンス」といい、
クラスからコピーしてオブジェクトを作ることを「インスタンス化」といいます。
クラスとは03

VBAでインスタンスを作成する


WorkSheetオブジェクトなど、Excelにもともとついている機能に関しては
Excelが自動的にインスタンスを作ってくれるため、特に意識する必要はありません。

どういうときに使うかというと、FileSystemObjectやADODBといった
外部ライブラリのオブジェクトを使うときに使います。

変数の宣言をするとき、データ型の前にNewキーワードをつけると
変数の宣言とともにインスタンスを作成します。

◆サンプルコード
*FileSystemObjectの場合。MicrosoftScriptiongRuntimeの参照設定が必要です。
Dim FSO As New FileSystemObject



変数を宣言した後でインスタンスを作成したい場合は、
Setステートメントを使います。

◆サンプルコード
Set FSO = New FileSystemObject


参照設定をしない場合は、CreateObject関数を使います。
◆サンプルコード
Set FSO = CreateObject("Scripting.FileSystemObject")


クラスモジュールがなにができるか


クラスモジュールとは、クラスを作るためのモジュールです。
クラスとは、オブジェクトのメソッドとプロパティを定義したオブジェクトのフォーマットでした。

つまり、クラスモジュールを使うとオリジナルのメソッドとプロパティをもつオブジェクトが作れるようになるのです。

ただ、事務作業でそれほど高度なことを求められることは、ほとんどないでしょう。

外部ライブラリを利用するときに、Newキーワードでインスタンスを作成する方が、お世話になる頻度が高いです。

  1 2 3 4 5..  次の10件>>


Powered by Seesaa
×

この広告は1年以上新しい記事の投稿がないブログに表示されております。