初心者オサムシがExcelのマクロと戦う戦闘ブログである
by オサムシ
試験メモ【イベントプロシージャ】/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エキスパート
マクロ 基礎
マクロ 応用
Excel 基礎
Excel 応用
Office
日記
もくじ

ファン

お気に入りブログ
初心者のためのOffic...
七里ヶ浜だより Lett...
はじめてのMac-Sup...
初心者のためのOffic...

検索

タグ

人気ジャンル