初心者オサムシがExcelのマクロと戦う戦闘ブログである
by オサムシ
カテゴリ:試験メモ/VBAエキスパート( 9 )
試験メモ【イベントプロシージャ】/VBAエキスパート

イベントプロシージャ



イベントプロシージャとはExcel上で何か操作が行われたときに、それをマクロが感知してプロシージャを起動するという事

Excel上で変化が起こる事をイベントという訳だけど、もちろん変化が起こっただけじゃ、別に何の意味もない

そこに仕掛けをしておくからこそ効果があるんだよね

例えば、どこかのセルを書き換えたとか、右クリックをしたとか、Excelファイルを開いた(つまりWorkbookを開いた)とか

それを全て「イベントが起こった」という訳

いろいろ使い道はあるけど、シートにマクロボタンを設けて、それをクリックすれば別にイベントで反応させなくても良いんだけど、例えば、絶対に書き換えてはいけない書類ファイルがあったとする

それを上書き保存しちゃったらマズいから、「上書き保存をしようとしたら別名保存させるようにする」なんてのはどうだろう

上書き保存はマクロではSaveだけど、Saveというイベントが起こったら、BeforeSaveつまり「Saveの前に」別名保存させるのでオリジナルは上書きされないで済む

こんな風に「あったらマズイ」ときと「あったら便利」なときにも使える

イベントには以下リンク先のモノがある(全てではありません)

ワークシートに対するイベント/Excel マクロ
https://docs.google.com/spreadsheet/ccc?key=0Aq3SC7WlgWhMdHg2TGxGcnAzVXZ0cV9YSUtoVWRtWUE&usp=sharing

ブックに対するイベント/Excel マクロ
https://docs.google.com/spreadsheet/ccc?key=0Aq3SC7WlgWhMdEVBM3JaRGhiZlRjVWRaUHdCdjVZN2c&usp=sharing

書き方は、まずコードウィンドウでオブジェクトモジュールをダブルクリックで選択する

すると下画像の様なウィンドウになる
d0285354_2254519.jpg

左のセレクトボタンでWorksheetを選択すると、右のセレクトボタンに候補がでるので、そこから選択すればベースを打ち込んでくれる

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub


この書式がデフォで、最初から引数Targetまで用意してくれてる

Targetはともかく、書式に関してはこれじゃないとダメみたいですね

ちなみにイベントプロシージャ内で、イベントを起こさせたくない部分にはEnableEvensを使います

Application.EnableEvents = False
命令
Application.EnableEvents = True


イベントに関してはもう少し裏がありそうだけど、そんなに追及するとこじゃないのでこの程度でよし

あと一つ言うなら、イベントはオブジェクトモジュールでないと使えない

更にブックのイベントはオブジェクトモジュール内の「ThisWorkbook」モジュールでしか使えない

イベントプロシージャは乱用するととんでもない事になりかねないから、ほどほどにしようね。


オサムシじゃーなる
[PR]
by 03musi | 2013-02-11 22:55 | 試験メモ/VBAエキスパート
試験メモ【ユーザー定義型変数と変数の範囲(スコープ)】/VBAエキスパート

ユーザー定義型変数と変数の範囲(スコープ)



ユーザー定義型変数

複数の定義型をセットにして使いたい場合には「ユーザー定義型」を使います

例えば下記のようなシートをマクロで扱う場合に使えます
d0285354_16361226.jpg


それではマクロを書いてみましょう
Option Explicit
Type dlist
namae As String
tosu As Long
End Type
Sub sample1()
Dim dobutu(3) As dlist, i As Long
For i = 2 To 4
dobutu(i - 1).namae = Cells(i, 1)
dobutu(i - 1).tosu = Cells(i, 2)
Next i
MsgBox dobutu(2).namae & vbCrLf & dobutu(2).tosu
End Sub


このようにユーザー定義型は違う型をセットにすることもできますし、同じ型をセットにすることもできます

ちなみにユーザー定義型はオブジェクトモジュール内では使えません
標準モジュールなどを使いましょう

変数の範囲(スコープ)

通常、プロシージャ内で定義される変数は、プロシージャ内でしか通用しません

例えば、全く同じ変数名であっても、違うプロシージャであれば、全く別のものです

これはメリットであると同時に、用途によっては混乱を招くということと、一度定義した変数名でも、違うプロシージャでは使えないというデメリットがあります

このような場合は、「モジュール変数」を使います

モジュール変数は、モジュールシートの先頭の、プロシージャより上に書きます
d0285354_1646524.jpg

左の赤枠はプロジェクトで、「Microsoft Excel Objects」がオブジェクトモジュール、選択されているところが「標準モジュール」です

実行してみましょう
d0285354_16493340.jpg


モジュール変数は、モジュール内でのみ有効です

この場合、Dimで宣言してもPrivateで宣言しても結果は同じです

Publicで宣言すると、プロジェクト内(赤枠内)の他のモジュールからも参照できます

ちなみにこの変数の適用範囲の事を「スコープ」といいます

モジュール変数ならば「変数のスコープがモジュール内である」と使います

定数 Const

「Const」は定数を宣言するときに使います

当然、定数ですからプロシージャ内では変更できません

尚、「Microsoft Excel Objects」モジュールでは何かと規制が多いので、汎用的に使うマクロは標準モジュールに書きましょう。osamushi

オサムシじゃーなる
[PR]
by 03musi | 2013-02-11 16:39 | 試験メモ/VBAエキスパート
試験メモ【静的変数とオブジェクト変数】/VBAエキスパート

静的変数とオブジェクト変数



静的変数

プロシージャ内で宣言した変数は、通常そのプロシージャが終了するとリセットされます

静的変数とは、プロシージャが終了しても消えない変数です

ブックが開いている限り、変数は保持されます

ブックを閉じると、上書き保存しても変数はリセットされます

変数を宣言するときDimではなくStaticをつかいます

Sub sample5()
Static hensu5 As Long
hensu5 = hensu5 + 1
MsgBox hensu5
End Sub


これで、マクロを実行する度に変数に1が加算されていきます

オブジェクト変数

読んで字のごとし、オブジェクトを格納した変数をいいます

通常の変数は値が格納されていますが、オブジェクト変数は、オブジェクトそのものが格納されている為、格納されたオブジェクトと同じ性質を持ちます
つまりValue・Formulaなどのプロパティや、Insert・Deleteなどのメソッドが使えるということです

当然、オブジェクト型変数の宣言をしなければなりません

よく使うオブジェクト変数を宣言してみます
①セル Dim hensu As Range
②シート Dim hensu As Worksheet
③ブック Dim hensu As Workbook

もっと大雑把に宣言する方法もあります
Dim hensu As Object
これですべてのオブジェクトを格納できます
また、万能型であるVariant型は、あらゆるオブジェクトも格納できます

オブジェクト変数にオブジェクトを代入するときは「Set」命令を使います

Set hensu = Renge("A1")

検証してみましょう

Sub sample6()
Dim hensu6 As Object '変数にオブジェクトを定義
Set hensu6 = Range("A1") 'A1セルを代入
hensu6 = "テスト"
Set hensu6 = Nothing '格納したオブジェクトを破棄
MsgBox hensu6
End Sub

d0285354_150413.jpg

d0285354_1503692.jpg

Nothingでhensu6を破棄していますので、最後のMsgBoxはエラーとなります
このように、明示的にオブジェクト変数を破棄することができます

オブジェクト変数が参照している参照元オブジェクトを取得したい場合ですが、下記の方法を考えてみました
Sub sample7()
Dim hensu7 As Object
Set hensu7 = Range("A1")
'hensu7 = "テスト"
htype = TypeName(hensu6) '変数のタイプを取得
MsgBox htype '取得した変数のタイプを表示
Select Case htype '取得した変数タイプによって処理を変える
Case "Worksheet"
a = hensu7.Name
Case "Workbook"
a = hensu7.Name
Case "Range"
a = hensu7.Address
End Select
MsgBox a 'Select Caseで判定した内容を表示
End Sub

d0285354_151683.jpg

d0285354_1511314.jpg

一応、使いそうな3つのオブジェクトタイプを想定してSelect Caseで判定・選択して表示というのを書いてみましたが、どんなオブジェクトにも対応できるもっとスッキリした良い方法あったらぜひコメントお願いします。osamushi

オサムシじゃーなる
[PR]
by 03musi | 2013-02-11 15:18 | 試験メモ/VBAエキスパート
試験メモ【動的変数と配列のお話し】/VBAエキスパート

動的変数と配列のお話し



配列

変数宣言時に配列であることを指定すると、一つの変数に配列として複数の値を格納できます

配列の要素には、インデックス番号がつけられます

Sub sample1()
Dim zoo(2) As String
zoo(0) = "ゴリラ"
zoo(1) = "ペンギン"
zoo(2) = "カバ"
MsgBox zoo(0)
End Sub


上記の構文ではzoo(2)と指定していますが正式には下記となります

Dim zoo(0 To 2) As String
範囲を指定しない場合は「0」を指定したとみなされます

0が扱いにくい場合は
Dim zoo(1 To 3) As String
のように指定しても要素が3つであることは同じです

動的配列

マクロで要素数を取得するようなときは、マクロを書く時点では配列の要素数がわかりませんよね

そんなときは、動的配列を使います
Dim zoo() As String
要素数を指定しないで、動的配列である事を()で表します

要素数がわかった時点でReDimを使って要素数を指定します

ただし、ReDimをする度に格納されていた値は全てリセットされてしまいます

これを避けるためには、ReDimの後にPreserveを付加します

Sub sample2()
Dim a As Variant
Dim n As Variant
Dim i As Integer
Dim zoo() As String 'zooの後に()をつけて動的配列に指定
n = 1
ReDim Preserve zoo(n)
a = Cells(n, 1) 'aにセル値を代入
Do Until IsEmpty(a) 'aが空でなかったら処理を続ける
zoo(n) = Cells(n, 1) '現在インデックス配列にセル値を代入
n = n + 1 '1つ下のセルへ
a = Cells(n, 1) 'aにセル値を代入
ReDim Preserve zoo(n)
Loop 'Loopここまで
dobutu = InputBox("動物の名前を入力")
zoo(n) = dobutu
For i = 1 To n
hyouji = hyouji & zoo(i) & vbCrLf
Next i
MsgBox hyouji
End Sub


最後にFor…Nextステートメントで、配列を取り出し、Msgboxで表示しています

Preserveを指定したおかげで、Loop内で配列に格納した値が保持されています

ReDimステートメント

配列の宣言で要素数を指定した場合、動的配列にならないので、ReDimで要素数を変更できません

また、動的配列では要素数の開始番号を指定できないようです
つまり「0」から始めるしかないようです。

例の様にセル番号と関連性を持たせたいような場合は、「0」は欠番にするか、配列のときだけ「n - 1」のように1を引いて扱うしかありません。たぶん。。

上記例を「n - 1」として「0」から使えば、最後のFor文はFor…Eachにすることで、インデックス番号を気にすることなく、配列のすべての値を取り出せます

Sub sample2()
Dim a As Variant
Dim n As Variant
Dim i As Integer
Dim zoo() As String
n = 1
ReDim Preserve zoo(n)
a = Cells(n, 1)
Do Until IsEmpty(a)
zoo(n - 1) = Cells(n, 1)
n = n + 1
a = Cells(n, 1)
ReDim Preserve zoo(n - 1)
Loop
dobutu = InputBox("動物の名前を入力")
zoo(n - 1) = dobutu
For Each hairetu In zoo
hyouji = hyouji & hairetu & vbCrLf
Next hairetu
MsgBox hyouji
End Sub


実際やってみますかぁ

まずはシートに下準備
d0285354_23344550.jpg


実行するとセルの内容を配列に格納して、空白セルに到達するとLoopをやめてInputBoxを開きます
「ライオン」と入力しました
d0285354_23345723.jpg


最後にFor…Eachで配列を取り出しMsgBoxで表示
d0285354_2335795.jpg

ちなみにこの方法で配列を抽出する場合、Variant型で宣言しないとダメです

つまり型を省略すればVariant型宣言とみなされるから、それでOK

上記例の程度ならセルで扱っても問題ないね

配列のメリットを体感するような出来事があったら実例で紹介します。osamushi

オサムシじゃーなる
[PR]
by 03musi | 2013-02-10 23:47 | 試験メモ/VBAエキスパート
試験メモ【引数渡しをもう少し詳しく】/VBAエキスパート

引数渡しをもう少し詳しく



複数の引数を渡す

Sub sample8()
Call sample9("渡します", 5)
End Sub

Sub sample9(msg As String, n As Long)
Dim i As Long, a As String
For i = 1 To n
a = a & msg & i & vbCrLf
Next
MsgBox a
End Sub

d0285354_16155013.jpg



引数を省略する

省略可能な引数前にOptionalを付ける
Sub sample9(msg As String, Optional n As Long)


省略した場合の対処

①あらかじめ初期値を設定しておく
Sub sample9(msg As String, Optional n As Long = 5)


②省略された事を調べる/IsMissing関数

Sub sample8()
Call sample9("渡します") 'nに渡す引数を省略
End Sub

Sub sample9(msg As String, Optional n) '変数nはバリアントなので型省略
Dim i As Long, a As String
If IsMissing(n) = True Then n = 5
For i = 1 To n
a = a & msg & i & vbCrLf
Next
MsgBox a
End Sub

気を付けなきゃいけないのが、IsMissing関数で判定する引数はバリアント型でなければダメ
型を宣言しないとバリアント型になるので、宣言しなければOK

オサムシじゃーなる
[PR]
by 03musi | 2013-02-10 16:15 | 試験メモ/VBAエキスパート
試験メモ【プロシージャ】/VBAエキスパート

プロシージャ



プロシージャ名の右に付く()はプロシージャ同士で値を渡す時に使う
詳しくは後の記事で。。

他のプロシージャを呼び出す
Call

Callは省略できますが明示的にした方が良いと思う

Sub sample1()
Range("A1").Value = "山田"
Call sample2
End Sub
Sub sample2()
Range("B1").Value = Range("A1").Value & "太郎"
End Sub

d0285354_0523532.jpg



Functionプロシージャ

SubではじまるプロシージャをSubプロシージャと言いますが、End Subで終わった後は何もしません

Functionプロシージャは、End Functionで終わった後、実行結果を呼び出し元に返します

Function プロシージャ名(受け取る値) As 返すデータ型

Sub sample3()
Dim i As Long
For i = 2 To 6
Cells(i, 2).Value = baika(Cells(i, 1).Value)
Next
End Sub

Function baika(genka As Single) As Long
baika = genka * 1.3
End Function

d0285354_17131365.jpg



オサムシじゃーなる
[PR]
by 03musi | 2013-02-09 17:14 | 試験メモ/VBAエキスパート
試験メモ【デバッグ】/VBAエキスパート

デバッグ



デバッグモード

VBEのデバッグモードとは、実行中のマクロを一時停止した状態を指します
デバッグモード中に、一行ずつ命令を実行していく時はF8(デバッグメニューの「ステップイン」でも可)を押下
デバッグモードを終了する場合は、実行メニューから「リセット」を選択します

イミディエイトウィンドウ

①マクロ実行中に変数の値やプロパティの設定値を出力できる
Debug.Print
②ブレークポイントやStopステートメントで、マクロ停止中に任意の命令を実行できる
③値を取得し表示させたいときは、行頭に?をつける
④イミディエイトウィンドウでDimは使えない(変数宣言しなくてよい)

変数testの左から3文字だけ表示させる
a = Left(test,3)
MsgBox a

ウォッチウィンドウ

ウォッチウィンドウはあらかじめ仕掛けておいた式に従って、マクロを中断することができる
「式のウォッチ」を選択すると表示するだけですので、通常は「式がTrueのときに中断」もしくは「式の内容が変化したときに中断」を使う
d0285354_15505445.jpg


呼び出し履歴

エラー発生時、デバッグモードにして「表示」メニューから「呼び出し履歴」を選択すると、呼び出し履歴ウィンドウが開き、エラーが発生しているプロシージャを選んで「表示」ボタンを押すと、コードウィンドウに呼び出し元を表すマークが表示されます


オサムシじゃーなる
[PR]
by 03musi | 2013-02-09 15:51 | 試験メモ/VBAエキスパート
試験メモ【シートとブック操作】/VBAエキスパート

シートとブック操作



ワークシート

シートの挿入
Worksheets.Add

挿入されたシートはアクティブになります

名前の定義
Worksheets("sheet1").Name = "名前"

削除
Worksheets("名前").Delete

確認メッセージ無しで削除
Application.Displayalerts = False
Worksheets("sheet1").Delete
Applecation.Displayalerts = True

アクティブでないシートのセル操作
Worksheets("sheet1").Range("A1").Copy Worksheets("sheet2").Range("B2")

ワークブック

ブックを開く
Workbooks.Open Filename:=("C:\Users\ユーザー名\Desktop\test.xlsm")

ブックの挿入
Workbooks.Add

ブックの上書き保存
Workbooks("Book1.xlsx").Save

別名保存
ActiveWorkbook.SaveAs "C:\test1.xlsx"

ブックを閉じる
Activebooks.Close

アクティブでないブックのセル操作(ファイルは開いている必要があります)
Sub test()
a = Workbooks("test.xlsm").Worksheets("Sheet6").Range("A1")
MsgBox a
End Sub


オサムシじゃーなる
[PR]
by 03musi | 2013-02-09 14:25 | 試験メモ/VBAエキスパート
試験メモ【VBA基礎構文】/VBAエキスパート

VBA基礎構文



①オブジェクト式
(A)対象.様子 = 値
(B)対象.命令 オプション(引数名) := 値
②ステートメント
③関数

様子、状態=プロパティ
命令=メソッド
オプション=引数

変数

①Dim 変数名 As 変数の型 プロシージャ内で使用
②Private 変数名 As 変数の型 モジュール内で使用
③Public 変数名 As 変数の型 すべてのモジュールで使用

複数の宣言をする場合は「Dim a As String, b As String」の様に変数名をカンマで区切る

セルの操作

Range("A1") 'A1セル
Cells(1,2) 'B1セル

コピー
Range("A1").Copy Destination:=Range("B3") '「Destination:=」は省略可
Range("A1").Copy Range("B3")

ステートメント

If 条件1 Then
処理1
ElseIf 条件2 Then
処理2
Else
処理3
End If

For 変数 初期値 To 終了値 Step 2
処理
Next 変数

オサムシじゃーなる
[PR]
by 03musi | 2013-02-09 13:35 | 試験メモ/VBAエキスパート


カテゴリ
試験メモ/VBAエキスパート
マクロ 基礎
マクロ 応用
Excel 基礎
Excel 応用
Office
日記
もくじ

ファン

フォロー中のブログ
初心者のためのOffic...
七里ヶ浜だより Lett...
はじめてのMac-Sup...
初心者のためのOffic...

検索

タグ

その他のジャンル