EXCEL VBA マクロ TIPs. [ 超初心者の時に必要な技。デバッグ。 ]

　会社で、EXCEL マクロ初心者に最初に教えたのが、

　　　「セルが Range オブジェクトだという事。」

そして、その扱い方。それと、ちょっと難しいけど、オブジェクトのコレクションの考え方。

これを把握してしまえば、あとは、自分のやりたい事が、どのオブジェクトのなんというプロパティーやメソッドを使えばいいか、ネットで調べるだけ。

　これを説明するときに、一緒に教えたのが、デバッグの方法。エクセルマクロは、ぜんぶ書き上げてから動かすよりも、チョットずつ書いて試す方がいい。

実行できるのは Sub プロシジャー。Function プロシジャーを試したいときは、Sub test みたいに、テスト用の Sub プロシジャーを用意する。

その Sub プロシジャーの中をクリックしてカーソルを移動させて、プレイボタンで実行する。（これをしないと、実行可能なプロシジャーの一覧が出てしまい、とまどう。）
　いよいよデバッグ。写真のように（クリックで拡大）、見たい変数のある「次の行」の左側の欄外をクリックして、茶色のブレークポイントを設定する。（もう一度クリックで解除）

その状態で実行すると、ブレークポイントで実行が止まるので、見たい変数のところにカーソルを持って行くと、写真のように、ツールチップみたいな感じで変数の値を見る事が出来る。

配列や Type のように直接中身が見れない変数は、その変数を選択して反転表示にし、右クリックで「ウォッチ式の追加」をやると、画面下の様に追加される。

それを展開していくと、配列の各要素や Type のメンバが見れる。

忘れていけないのは、マクロが完全に終了してしまうと、メモリがクリアされてしまうのこと。前もって「 End Sub 」のところにブレークポイントを張っておくと安心。

ここまで出来れば、後は根性。そんな感じ。

EXCEL VBA マクロ TIPs. [ Function （関数）プロシジャーで、配列とか構造体とか、複数の値を渡す・返す方法。 ]

　Function Procedure に、配列を引数として渡す方法。この場合、参照渡ししか許されない。

　　　Function func1(ByRef a() As Integer)
　　　　　
　　　End Function

また、配列は、静的配列か動的配列で、Variant 配列は渡せない。

呼び出すときは、こんな感じ。

　　　Dim h() As Integer
　　　h = Array(1, 2, 3)
　　　ans = func1(h)

　Function プロシジャーで、配列を返すこともできる。
　　　Function func1(ByRef a() As Integer) As Integer()
　　　　　func1 = a
　　　End Function

これを、

　　　Dim h() As Integer
　　　Dim ans() As Integer
　　　h = Array(1, 2, 3)
　　　ans = func1(h)

こんな感じで受け取る。データは、値渡しで、渡される。

ただし、受け取る配列 ans は、動的配列または Variant じゃないといけない。

EXCEL'97では、この記述はできなかった。その場合は、戻り値の型を Variant 型にすれば OK。

　　　Function func1(ByRef a() As Integer) As Variant
　　　　　func1 = a
　　　End Function

　配列を使わずに、複数のデータを渡す場合は、ユーザー定義型の変数を使う。変数名を TypeTest とすると、

　　　Type TypeTest
　　　　　a As Integer
　　　　　b As String
　　　　　h() As Integer
　　　End Type

のような形で定義できる。使い方は、

　　　Dim tmp As TypeTest
　　　ans = TypeTest.a

と、構造体のようにメンバを参照できる。

ユーザー定義型は、配列としても使える。

　　　Dim t() As TypeTest

ここで、注意。先の例では、メンバ変数として動的配列 h() を定義したけど、これを使うためには、

　　　ReDim t(1)
　　　ReDim t(0).h(1)
　　　ReDim t(1).h(1)

の様に、各インスタンスごとに、動的配列の領域を再確保しなくてはいけない。これ、けっこう面倒。使いたくない。

ユーザ定義型変数の配列を、引数・戻り値に持つ関数の定義方法。(引数をそのまま返す例。)

　　　Function Tfunc(t() As TypeTest) As TypeTest()
　　　　　Tfunc = t
　　　End Function

この関数の値は、

　　　Dim ans() As TypeTest
　　　ans = Tfunc(t)

と、TypeTest 型の[動的]配列で受け取らないといけない。（静的配列や、Variant 型では受けられないので、注意が必要。）

　ま、EXCEL VBA でここまで使うかは疑問だけど、いつか、使うときのために、メモ、メモ。

EXCEL VBA マクロ TIPs. [ プロシジャー呼び方と、引数の値渡しと参照渡し。]

　Sub プロシジャーや Function プロシジャーの使い方を間違っていた。っていうっか知らなかった。

それは、呼び出し方。Sub プロシジャー sub1 は、

　　　call sub1(引数)

Function（関数）プロシジャー func1 は、

　　　ans = func1(引数)

という形で使うものだとばかり思ってた。

でも、実際は、

　　　call sub1(引数)
　　　sub1(引数)
　　　call func1(引数)
　　　func1(引数)
　　　ans = func1(引数)

の、どの呼び方もできる。

違うのは、

　　　call で呼ぶと、引数は参照渡しになり
　　　そのまま実行すると、引数は値渡しになる

って事。

　　　ans = func1(引数)

の形で、普通の関数の様に呼んだときは、引数は値渡しになる。（ただし、配列を引数に渡す場合は、参照渡しのみ。）

　　　「知らんかった・・・」

って言うっか、MS のソフト仕様って、「余計なお世話」を押しつけすぎ。この仕様の、意図が分からない。

　で、今後は、引数を付けるときは、

　　　ByVal a As Integer
　　　ByRef a As Integer

の様に、明示的に指定しようかと思う。間違ってたら、エラーになるし。

EXCEL VBA マクロ TIPs. [ 配列のまとめ。コピーとかも。 ]

　EXCEL の仕事で、配列は使わなかったんだけど、ちょっと気になったので調査してみた。

配列には、静的配列、動的配列、Variant 配列、の 3つがある。

　配列を定義すると、０始まりで、添え字と同じ番号までメモリが確保される。

　　　Dim h(1) As Integer
　　　Dim h(0 to 1) As Integer

これは、二つとも、h(0), h(1) のメモリエリアを確保する。

１始まりにしたい場合は、プロシジャーの外の宣言部（モジュールの先頭）に、

　　　Option Base 1

と記述。

　静的配列は、いわゆる、普通の配列。

　　　Dim sh(3) As Integer

動的配列は、

　　　Dim dh() As Integer

と添え字を付けずに定義する。これは、このままではメモリ領域を確保しない。

　　　ReDim dh(3)

みたいな感じで再定義して、初めてメモリ領域が確保されて、使えるようになる。動的配列なので、再度、

　　　ReDim dh(5)

とやると、要素数を増やすことができる。ただし、この際、既に入力済みのデータはクリアされる。

入力済みデータをクリアしたくない場合は Preserve を使う。

　　　ReDim Preserve dh(5)

　動的配列を使うと、以下の形で、配列のコピーができる。

　　　dh = sh

この左辺に書けるのは、動的配列のみ。

この場合、配列の値がコピーされる。配列への参照がコピーされるのではないので、注意が必要。

これを使うと、

　　　dh = Array(1, 2, 3)

とか、

　　　dh = Split（"A B C", " "）

のように、配列を返す関数の値を受け取ることができる。（EXCEL'97 では、この２つは使えなかった。）

これらを使うときは、ReDim で領域を確保しなくても、自動的に確保される。

また、要素数の違うものを再度代入しても、自動的にサイズが調整される。

　配列要素の、下限と上限を知る方法。

　　　LBound（配列名）　'下限番号
　　　UBound（配列名）　'上限番号

上記を使えば、ループ処理が簡単になる、カモ。ただし、動的配列の場合は、一度でも ReDim をして領域を確保しないと、エラーになる。

配列の要素数を知りたければ、

　　　UBound（配列名） - LBound（配列名） + 1

で OK 。

　配列のメモリ領域をクリアしたい場合は、

　　　Erase 配列名（カッコなし）

とする。静的配列は値のみクリア（メモリ領域は保持）、動的配列はメモリ領域ごとクリアされる。

　Variant 配列は、普通に Variant 型の変数を定義すると、それが動的配列と同じ様に使える、というしろもの。

　　　Dim v As Variant
　　　s = "A B C"
　　　v = Split(s, " ")
　　　ans = v(2)　　　　　' "C" が入ってる。
　　　ReDim Preserve v(3)
　　　ans = LBound(v3)　' 0を返す
　　　ans = UBound(v3)　' 3を返す

Variant 型は、普通の変数型だけじゃなく、動的配列も含む、って考えるべきなのかも。これ、あえて使う、意味が分からない。

PS. 参照元：エクセル大辞典、バリアント型を配列として使う

EXCEL VBA TIPs. [ データが入力されている範囲を取得する方法。 ]

　エクセルのワークシートのデータが入力されている範囲を取得する方法。

　　　Thisworkbook.Worksheets("Sheet1").UsedRange

これが、その、データが入力されている範囲の Range オブジェクト。

左上のセルの Range オブジェクトは、

　　　Thisworkbook.Worksheets("Sheet1") _
　　　　　　　　　　　　　　　　　.UsedRange.Cells(1, 1)

で取得できる。他のスマートな方法を探したんだけど、見つからず。

右下の最終セルの Range オブジェクトは、

　　　Thisworkbook.Worksheets("Sheet1") _
　　　　　　　.Cells.SpecialCells(xlCellTypeLastCell)

で取得できる。（これに関しては、EXCEL VBA TIPs. [ 忘れがちな Range オブジェクトの話。]で書いた。）

EXCEL VBA TIPs. [ ステートメントのまとめ。]

　VBA で、何を忘れてしまうかって言うと、

　　　「ステートメント。」

これ、VBA に限らず、プログラム言語ごとに違ってて、

　　　「わざとだろう！」

って言いたくなる。case とか End Case とか esac とか・・・

という事で、VBA の仕事も一段落したので、またいつか再開するときのために、 MEMO MEMO。

・IF 文
　　If 式 Then
　　ElseIf 式 Then
　　Else
　　End If

　　比較は
　　If x = 1 Then
　　If x <> 1 Then
　　If x = 1 And y = 1 Then
　　If x = 1 Or y = 1 Then

・Swich case 文
　　Select Case 変数
　　　Case 1 To 5
　　　Case "abc"
　　　Case Else　　　' その他の値の場合。
　　End Select

　※ Case 文に、文字列も使える。
　※ VBA の Swich case 文は、全く違うので注意。

・For ループ
　　For n = 1 To 20
　　　Exit For
　　Next n

・For Each ループ
　　For Each c In コレクション
　　Next

・do ループ
　　Do While 条件式
　　Loop

　　Do
　　Loop While 条件式

　　Do Until 条件式
　　Loop

　　Do
　　　Exit Do
　　Loop

・データ型
　　「 Data Type Summary 」でヘルプを検索する、見れる。

※他は、こことかを参考に。

EXCEL VBA TIPs. [ ワークシート上の画像を、コピーアンドペーストする方法。]

　ワークシート上に、「挿入」→「図」→「ファイルから」で画像を貼り付けると、その画像は Shape オブジェクトとして扱われる。

マクロで、ワークシート上の画像を、ダイアログボックスの Picture コントロールに貼り付けたいという、無茶な要望に答えたもの。

　まず、Shape オブジェクトを抽出する方法。ワークシート上の、画像、線、図形は、まとめて Shapes コレクションとして取得できる。

　　Dim shps As Shapes
　　Set shps = ThisWorkbook.Worksheets("Sheet1").Shapes

この shps の中から、目的の画像の含まれる Shape を取り出すわけだけど、画像を検索するキーがない。

そこで、画像が置かれているセルの情報を使う。例えば、画像が "A1" セルに置かれているとして、

　　Dim shp As Shape
　　For Each shp In shps
　　　'Shape が Picture オブジェクト（画像）か？
　　　If shp.Type = msoPicture Then
　　　　 '範囲 A1 にある Shape を捜す
　　　　　Set area _
　　　　　　　　= Intersect(Range(shp.TopLeftCell, _
　　　　　　　　　　　　　　shp.BottomRightCell), _
　　　　　　　　　　　　　　Worksheets("Sheet1").Range("A1"))
　　　　　'重なる範囲がある → その範囲に画像がある
　　　　　If Not (area Is Nothing) Then
　　　　　　'範囲 A1 に含まれる画像 Shape がある
　　　　　　shp.Copy '画像をクリップボードにコピー
　　　　　　Exit For
　　　　　End If
　　　End If
　　Next

これで、クリップボードに画像がコピーされる。

Intersect は二つのレンジオブジェクトの、重なっている範囲を返す関数。Shape と "A1" が重なっていなければ Nothing を返す。

これは、逆に、"A1"に一部でも重なっている Shape があるとその範囲を返してしまう。

画像を特定したいのなら、そのセルには、確実にその画像だけを置くようにしなければならない。それぐらいの制限はしないと、VBA マクロは成立しない。

　ここで、この前の記事で紹介した関数 LoadPictureFromCB() を使って、画像を抜き出して利用する。

フォームの Image コントロールに貼り付けるなら、

　　Set Form1.Image1.Picture = LoadPictureFromCB()

こんな感じ。

※ 実際に使う場合は、クリップボードに確実に画像がある状態でペーストすること。
※ クリップボードに画像があるかどうかチェックする方法の例。

　　CBContents = Application.ClipboardFormats
　　For Each fmt In CBContents
　　　If fmt = xlClipboardFormatPICT Then
　　　　MsgBox "クリップボードにあるのは、PICT です。"
　　　End If
　　Next

EXCEL VBA TIPs. [ クリップボードにコピーした画像を、ペーストするために取りだす方法。]

　クリップボードにコピーした画像を、クリップボードから Picture オブジェクトとして取りだしてペーストに利用する方法。（コピペに利用）

これは、ネット上の掲示板にあった物を、ちょっとだけ修正したもの。仕組みが理解できてない。情報が無くなると困るので、ここにコピー。

いつかは、これが理解できるようになりたいと思ってる。

'----------------------- by shira
'モジュールの先頭に書く
Option Explicit
Private Type GUID
　　Data1 As Long
　　Data2 As Integer
　　Data3 As Integer
　　Data4(0 To 7) As Byte
End Type
Private Type PICTDESC
　　cbSizeofstruct As Long
　　picType As Long
　　hemf As Long
　　Padding(0 To 1) As Long
End Type
Const PICTYPE_ENHMETAFILE = 4

Private Declare Function OleCreatePictureIndirect _
　　　　Lib "olepro32.dll" _
　　　　(lpPictDesc As PICTDESC, riid As GUID, _
　　　　ByVal fOwn As Long, lplpvObj As Object) As Long
Private Declare Function OpenClipboard Lib "user32" _
　　　　(ByVal hWndNewOwner As Long) As Long
Private Declare Function CloseClipboard Lib "user32" () As Long
Private Declare Function GetClipboardData Lib "user32" _
　　　　(ByVal uFormat As Long) As Long

Const CF_ENHMETAFILE = 14
Private Declare Function CopyEnhMetaFile Lib "gdi32" _
　　　　Alias "CopyEnhMetaFileA" _
　　　　(ByVal hemfSrc As Long, ByVal lpszFile As String) As Long
Private Declare Function DeleteEnhMetaFile Lib "gdi32" _
　　　　(ByVal hemf As Long) As Long


'クリップボードから Picture オブジェクトを取り出す関数
'※画像がない場合は nothing を返す

Public Function LoadPictureFromCB() As Object

　　Dim IID_IDispatch As GUID
　　Dim pd As PICTDESC
　　Dim objResult As Object
　　Dim hemf As Long

　　If OpenClipboard(0) Then
　　　hemf = GetClipboardData(CF_ENHMETAFILE)
　　　' ハンドルを複製してから使用する
　　　hemf = CopyEnhMetaFile(hemf, vbNullString)
　　　CloseClipboard
　　End If
　　If hemf = 0 Then
　　　Set LoadPictureFromCB = Nothing
　　　Exit Function ' 失敗
　　End If
　　With IID_IDispatch
　　　.Data1 = &H20400
　　　.Data4(0) = &HC0
　　　.Data4(7) = &H46
　　End With
　　With pd
　　　.cbSizeofstruct = Len(pd)
　　　.picType = PICTYPE_ENHMETAFILE
　　　.hemf = hemf
　　 End With

　　If OleCreatePictureIndirect(pd, IID_IDispatch, _
　　　　　　　　　　　　　　　　1, objResult) >= 0 Then
　　　' 成功時
　　　Set LoadPictureFromCB = objResult
　　Else
　　　' 失敗時
　　　DeleteEnhMetaFile hemf
　　　Set LoadPictureFromCB = Nothing
　　End If

End Function

EXCEL VBA TIPs. [ 関数 Function Procedure の作り方。]

　VBA マクロで、 Sub プロシジャーだけでプログラムを書いてると、見通しが悪く、ワケが分かんなくなってくる。

直接実行する以外は、Function Procedure （以下、関数と呼ぶ。）だけで書くぐらいの気持ちでやると、ソースコードも読みやすく整理される。関数定義のとこに、詳しいコメントも書いておけるし。

　関数の値は、関数と同じ名前の変数に値を設定することで、戻り値を返すことが出来る。

　　Public Funcion test()
　　　　test = Range("A1").Value
　　End Fucntion

ここで注意しないといけないのは、この関数の書き方だと、関数の戻り値が、

　　Variant Valiant 型

になってしまうこと。

関数の型がハッキリ決まらないと、思いがけないエラーの原因になるので、次の様にして、戻り値の型をハッキリ決める様にした方がいい。

たとえ、Variant Valiant 型の方が、ソースコードが短くなろうとも、ゼッタイ！

　　'ここに関数の情報を書いておくのがベスト。
　　Public Function getNumber()
　　　getNumber = Range("A1").Value
　　End Function

　　Sub Macro1()
　　　Dim ans As Integer
　　　ans = getNumber() + 1
　　End Sub

この例で、Macro1 を実行すると、セル A1 の値が 1とかの数字の場合は問題ない。ところが、"AAA"とかだと、ans = の行で、

　　　「型が一致しません。」

とエラーになってしまう。

セル A1 の値を取ってるのは getNumber() なので、そこがしっかり Integer を返さないと、至る所にエラー処理を入れなくてはならなくなる。

で、こんな感じで、関数の戻り値の型を返すようにする。

　　Public Function getNumber() As Integer
　　　getNumber = Range("A1").Value
　　End Function

（実は、この、Function Procedure の戻り値の型を指定する方法、「 EXCEL VBA TIPs. （エラー処理ざんまい）」の記事を書いた時点では知らなかった。）

このままだと、getNumber() の中でエラーが起こるので、次のようにエラー対策をする。GOTO分を使っても、短い関数の中だと、見通しが良くなってバグになりにくい。

　　Public Function getNumber() As Integer
　　　On Error GoTo ERROR
　　　getNumber = Range("A1").Value
　　　Exit Function
　　ERROR:
　　　getNumber test = 0
　　End Function

（ A1の値を取る前に、データ種別を判別しろよ、って言うのは、別の話題って事で。４６）

　関数は、数値を返すだけかと思ってたら、オブジェクトも返してくれる。

　　Public Function getArea() As Range
　　　getArea = Range("A1:B2")
　　End Function

これ、他のワークブックをオープンする関数で Workbook オブジェクトを返すようにするとか、かなり便利。

　Funcion Procedure の引数で、忘れやすい TIPs が、

　　値渡し：ByVal 変数名 As データ型（デフォルト）
　　参照渡し：ByRef 変数名 As データ型（デフォルト）

自分でも手を抜いてやりがちなのが、バッファとか配列、そのアクセスインデックス、をグローバルで定義して、それを関数で処理してしまうこと。

それだと、いろいろな関数から勝手に呼ばれてしまうのでバグの原因になる。ローカル変数として定義して、参照渡しするのがいい。はず。

EXCEL VBA TIPs. [ 忘れがちな Range オブジェクトの話。]

　EXCEL の仕事も、一段落したので、忘れてもすぐ思い出せるように、ブログにメモ。（自分用なので、他の人にはわかりにくいかも。）

エクセルの VBA って、ほんと、ちょっと間を開けると、すぐに忘れてしまう。これ、不思議。で、一番忘れてしまうのが、

　　　「 Range オブジェクトの扱い。」

　エクセルの値のある最終セルを見つけて、データの入力されている範囲の Range オブジェクトを取得する方法。（最大セル？最終行？最後のセル？）

Dim WS As Worksheet
Dim LastCell As Range

Set WS = Thisworkbook.Worksheets("Sheet1")
Set LastCell = WS.Cells.SpecialCells(xlCellTypeLastCell)

SpecialCells() は引数に常数を入れると、いろいろな条件でセルをグループ選択できる。 xlCellTypeLastCell を入れると、使われたセル範囲内の最後のセルの Range オブジェクトを返してくれる。

ただし、

10	20	30
11	21	31
12	22	32

この表の[12]と[30]のセルにだけ値が入っていても、LastCell には、[32]のセルの Range オブジェクトが代入されるので注意が必要。

で、LastCell の行列番号を知りたいなら、

　　　列番号 = LastCell.Column
　　　行番号 = LastCell.Row

で取得できる。

　データが入力されている範囲の Range オブジェクト DataArea を取得したいのなら、例えば、左上のセルに「TopLeftCell」とかの範囲名を付けて、

　　　Dim TopCell As Range
　　　Dim DataArea As Range

　　　Set TopCell = WS.Range("TopLeftCell")
　　　Set DataArea = WS.Range(TopCell, LastCell)

の様に、データのある範囲のレンジを取得してもいい。

この DataArea の n行目と m列目までが見出しの場合、データの入っている範囲の Range オブジェクトを取得したいなら、

　　With DataArea
　　　Set DataArea = .Range(Cells(n,m), LastCell)
　　End With

って感じ。

DataArea の n 行目の Range オブジェクトを取得したいなら、

　　DataArea.Rows(n)

n 列目のオブジェクトを取得したいなら、

　　DataArea.Columns(n)

メモメモ。

EXCEL VBA TIPs. （他のワークブック上のマクロ・関数を実行する。）

　全体で一元的に管理したいデータを一つのワークブックに入れて、サーバーに置いて、その値を別のワークブックのマクロ上で使いたいと思った。

共通データをメンテするとき、サーバー上のワークブックを変更すればそれだけで済むから。

その際、サーバー上のデータを別のワークブックのマクロで直接参照してしまうと、データー構造が変わったときに、全てのワークブックのマクロを変えないといけなくなる。

それだとまずいので、サーバー上のデータを書いたワークブックに、データを抽出する関数を書いておく事にした。他のワークブックのマクロでは、その関数を呼びだして値を取得する。

　ワークシート DB.xls の関数 getValue を指定する記述は、

　　　"DB.xls" & "!" & "getValue"

実際の呼び出しは、Application.Run を使う。記述はこんな感じ。

　　　f_getValue = "DB.xls" & "!" & "getValue"
　　　Application.Run(f_getValue, 10)

Run の最後の引数は、関数 getValue の引数。なければ省略、もっとあれば、[カンマ＋引数] の形で付け加える。

使用例を、メモメモ。

◎サーバー上のワークブックの関数
'ワークシート DB の範囲 Area の row 行目の値を返す。
Public Function getValue( row As Integer)
　 getValue = ThisWorkbook.Worksheets("DB"). _
　　　　　　　　　　Range(Area).Cells(row, 1).Value
　If IsError(getValue) Then getValue = Empty
End Function

◎別のワークブックのマクロで、上の関数を呼びだして値を取得する。
Public Sub test()
　Dim ans As String
　Workbooks.Open ("C:\EXCEL\DB.xls")
　f_getValue = "DB.xls" & "!" & "getValue"
　ans = Application.Run(f_getValue, 10)
　If IsEmpty(ans) Then MsgBox ("No Value!")
End Sub

関数名を一度 f_getValue に代入したのは、見やすくするため。直接、Application.Run() の中に書いても、もちろん OK。

　これを読んで、

　　　「 Access とかのデータベースソフト使えばいいジャン！」

って言うかもしれないけど、そうすると、他のソフトの使い方もマスターしなくてはいけない。最大のネックは、

　　　「他の人に引き継ぐのが大変になる。」

そんな感じ。

EXCEL VBA TIPs. （範囲名の活用と、行数・列数を取得。）

　エクセルのマクロで、ある範囲を扱うときは、その範囲に範囲名を付けとくと便利なのは常識。

で、ループを使ってその範囲を操作するとき、その範囲内の行数・列数が知りたくなる。

範囲名「Area」内の行数は、

　　　Worksheets("Sheet1").Range("Area").Rows.Count

列数は、

　　　Worksheets("Sheet1").Range("Area").Columns.Count

みたいな感じで取得できる。

あ、でも、普通、「 For Each...Next ステートメント」使うのか？

ちなみに、Range を指定しないで、ただ単に、

　　　Rows.Count
　　　Columns.Count

ってやると、アクティブなワークシートの最大行数・最大列数が返ってくる。

　逆に、範囲名を使わないで、特定のセルの、先頭セルからの行数（行番号）・列数（列番号）も、

セル「"F5"」の行番号は、

　　　Worksheets("Sheet1").Range("F5").Row

列番号は、

　　　Worksheets("Sheet1").Range("F5").Column

みたいな感じで取得できる。

　これは、どういう風に使えるか分からないけど、あるセルに設定されている範囲名を取得（抽出）する事も出来る。

たとえば "A1" に範囲名「Hani」が設定されているとすると、

　　　Range("A1").Name.Name

は「Hani」を返してくる。

これ、

　　　Range("A1").Name

ってやっちゃうと、

　　　=Sheet1!$A$1

ってのが返ってくる。最後の Name はメソッドなのか？意味不明。

　役に立つかどうかは分からないけど、とりあえず、

　　　「忘れたときの為の、メモメモ。」

な感じ。

PS. VBA の仕事、一段落したと思ったら、とんでもなくディープな調査依頼が来てしまった。

　　　「いくらなんでも、ムリ！」

つらい・・・

EXCEL VBA TIPs. （改訂版：デバッグログ、吐きまくりマクロ Ver.0.91 ）

　前回紹介した、「デバッグログ、吐きまくりマクロ Ver.0.90 」の改訂版、Ver.0.91（前の Ver.0.10にしときゃ良かった・・・）。

今回追加した機能は、

　　　「ログの数を返すオプションを追加。」

何に使うかというと、エラーやワーニングを吐き出した後で、

　　　ERROR: 128
　　　WARNING: 512

とかって、メッセージボックスとかで表示させるのに使える。

　以下、ソース。EXCEL に直接コピペして使える。（空白は２バイト文字だけど、 EXCEL VBA ではエラーにならないみたい。）

'以下は、プロシジャーの外に書くこと。（どのモジュールに書いても可。）
Public Const LOG_SW　　= True　　　'ログ出力の有効・無効
Public Const LOG_SHEET = "Sheet2"　'ログの出力先シート名
Public Const MAX_LOG　 = 2 　　　　'ログの本数（何本でも）
Public Const LOG_START_ROW = 2 　　'ログ出力開始行
Public Const INIT_LOG　 = "_INIT" '初期化指定
Public Const LOG_COUNT　= "_COUNT" 'ログ数を返す、指定
Public Const ERROR_LOG　= 1 　　　 'ERROR 用ログ
Public Const OUTPUT_LOG = 2 　　　 'データ吐き出しログ

'以下は、ログ吐き出しまくりマクロ本体。
Public Function debugLOG( log As String, opt As Integer )
　Static last_line(MAX_LOG) As Integer　　'次にログを書く行
　If Not LOG_SW Then Exit Function
　If log = INIT_LOG Then
　　For n = 1 To MAX_LOG
　　　last_line(n) = LOG_START_ROW
　　　'ログ領域のクリア
　　　With ThisWorkbook.Worksheets(LOG_SHEET)
　　　　.Range( .Cells(LOG_START_ROW, n), _
　　　　　　　　　 .Cells(Rows.Count, n)).ClearContents
　　　End With
　　Next
　　Exit Function
　ElseIf log = LOG_COUNT Then
　　debugLOG = last_line(opt) - LOG_START_ROW
　　Exit Function
　End If
　Worksheets(LOG_SHEET).Cells(last_line(opt), opt).Value = log
　last_line(opt) = last_line(opt) + 1
End Function

　戻り値を返すために Function 形式に変更。戻り値が必要ないときは値を返してない。戻り値が必要ないときは、

　　　Call debugLOG("おしゃいけ")

の形で呼ぶある。

INIT_LOG の定義とか、その debugLOG() 内での参照とか、ビミョウに変えてるので、丸ごと差し替えるのが、よろしある。

当然、ログ文字列として、

　　　_INIT
　　　_COUNT

は使えない（対応策として、先頭に"_"を付加したある。）

　使い方は、こんな感じ。

　　　'LOG 領域の初期化。マクロ開始直後、必ず一度は実行。
　　　Call debugLOG(INIT_LOG, 0)

　　　'実際のログの吐き出し。→ エラーログへ
　　　Call debugLOG("ダメだこりゃ！", ERROR_LOG)
　　　'実際のログの吐き出し。→ 出力結果ログへ
　　　n = 1
　　　Call debugLOG("答えは：" + CStr(n) + "だす", OUTPUT_LOG)

　　　n1 = debugLOG(LOG_COUNT, ERROR_LOG)
　　　n2 = debugLOG(LOG_COUNT, OUTPUT_LOG)
　　　MsgBox("ERROR:"+CStr(n1)+Chr(13)+"OUTPUT:"+CStr(n2))

LOG_START_ROW = 2 の設定だと、LOG_SHEET の２行目から書き始めるので、１行目に、ログ名を書いとくのがいいある。

MAX_LOG の値を増やせば、区別して保存できるログ本数が増える。本数を増やしたら、ERROR_LOG = 1, OUTPUT_LOG = 2, 3, 4, ・・・って感じで、定義を増やしていくだけで、OK ある。

マクロを別モジュールに書いてもいい。LOG_SW を False にすれば、ログは出力されない。マクロのあるワークブックと、参照・処理するワークブックが別になってても、OK どす。

PS. カテゴリーまで作って始めたこのシリーズだけど、VBA の仕事が落ち着いちゃったんで、もう QUEST ネタがない、かも。

EXCEL VBA TIPs. （エラー処理ざんまい）

　エクセル・マスターなら知ってる事も、ジョニーの様な初心者は知らない、エクセルマクロのちょっとした TIPs。今日のは、

　　　「ジョウシキテキ。」

かも。

　ワークシート関数を使うと、セルの状態によっては、思いもかけずエラーが出てしまうことがある。エラー処理は必須。対処方法はいくつかある。

　まずは、エラーを無視する方法。エラーが出ても問題ないのなら、無視していい。エラーが出る関数の直前で以下のステートメントを書く。

　　　On Error Resume Next

これを書いとくと、エラーの出た次の行から、何事もなかったかのように、実行が続けられる。

この後発生したエラーはすべて無視されるので、エラーが出る関数の後に、

　　　On Error Goto 0

を書いて、エラー処理を無効化しておくのを忘れずに。毎回やっといた方が、バグが少なくなる気がする。

　次は、エラーが発生したら、エラー処理部分にジャンプする方法。エラーが発生する関数の前に、On Error Goto LABEL を書いておくと、エラーが発生した瞬間、LABEL 行にジャンプしてくれる。

　　　Dim retI As Integer
　　　On Error GoTo ERROR
　　　retvI = Application.WorksheetFunction. _
　　　　　　　　　Match("おしゃいけ", Range("BLOGName"), 0)
　　　正常に取得できた時の処理を書く
　　　　　・
　　　　　・
　　　Exit Sub
　　ERROR:
　　　エラー処理を書く
　　End Sub

この方法を使うと、ラベル行にジャンプしてしまうので、プログラムの見通しが悪くなる。

　そこで、エラーが発生するワークシート関数を使う場合は、必ず Function を使ってラップしてしまうってのが、グー。

　　　Public Fuction wrapMatch(param As String)
　　　　Dim retI As Integer
　　　　On Error GoTo ERROR
　　　　　retI = Application.WorksheetFunction. _
　　　　　　　　　Match( param, Range("BLOGName"), 0)
　　　　　wrapMatch = retI
　　　　Exit Function
　　　ERROR:
　　　　　wrapMatch = -1
　　　End Function

これを見て、

　　　「 retI 使わないで、直接 wrapMatch に代入しろよ！」

って思うかもしれない。

　　　wrapMatch = Application.WorksheetFunction. _
　　　　　　　　　Match( param,Range("BLOGName"), 0)

って感じで。

でも、それだと、wrapMatch は Variant 型なのでエラーにならない。で、呼び出し元に「＃エラー＃」が返ってしまって、そこで止まってしまう。

　これを回避する方法が、次。

　　　Public Fuction wrapMatch(param As String)
　　　　wrapMatch = Application.WorksheetFunction. _
　　　　　　　　　Match( param, Range("BLOGName"), 0)
　　　　If IsError(wrapMatch) Then
　　　　　wrapMatch = -1
　　　　End If
　　　End Function

IsError() は Variant 型の変数の値がエラーかどうか判断してくれる。これが一番すっきりしてるかも。

　って事で、おすすめのワークシート関数のエラー処理は、

　　　・Fuction で wrap する。
　　　・IsError 関数でエラーを検出する

かな？

EXCEL VBA TIPs. （デバッグログ、吐きまくりマクロ）

　エクセルのマクロを作ってて困るのは、

　　　「出力結果が見えねぇ・・・」

って事。適当なセルに書き出したり、デバッカでブレークポイントを設定したり、めんどくさいのなんのって。

って事で、デバッグ用のエラーログを吐き出すマクロ Ver.0.90（1.00 って、ゼッタイ書けない ＾＾） を紹介。

これに、アクティブでないシートのセルに値を書く方法を使ってるので、それを知りたい人もチェキ。

以下、ソース。

'以下は、メインモジュールのトップ、プロシジャーの外に書くこと。
Public Const LOG_SW = True　　　'ログ出力の有効・無効
Public Const LOG_SHEET = "Sheet2"　'ログの出力先シート名
Public Const MAX_LOG = 2 　　　　'ログの本数（何本でも）
Public Const LOG_START_ROW = 2 　　'ログ出力開始行
Public Const INIT_LOG = "INIT"　　'初期化指定
Public Const ERROR_LOG = 1 　　　　'ERROR 用ログ
Public Const OUTPUT_LOG = 2 　　　　'データ吐き出しログ

'以下は、ログ吐き出しまくりマクロ本体。（どのモジュールに書いても可。）
Public Sub debugLOG( log As String, opt As Integer )
　Static last_line(MAX_LOG) As Integer　　'次にログを書く行
　If Not LOG_SW Then Exit Sub
　If log = "INIT" Then
　　For n = 1 To MAX_LOG
　　　last_line(n) = LOG_START_ROW
　　　'ログ領域のクリア
　　　With ThisWorkbook.Worksheets("Sheet2" LOG_SHEET )
　　　　.Range( .Cells(LOG_START_ROW, n), _
　　　　　　　　　 .Cells(Rows.Count, n)).Value = ""
　　　End With
　　Next
　　Exit Sub
　End If
　Worksheets(LOG_SHEET).Cells(last_line(opt), opt).Value = log
　last_line(opt) = last_line(opt) + 1
End Sub

　使い方は、こんな感じ。

　　　'LOG 領域の初期化。マクロ開始直後、必ず一度は実行。
　　　Call debugLOG（INIT_LOG, 0）

　　　'実際のログの吐き出し。→ エラーログへ
　　　Call debugLOG（"ダメだこりゃ！", ERROR_LOG）
　　　'実際のログの吐き出し。→ 出力結果ログへ
　　　Call debugLOG（"答えは：" + CStr(n) + "だす", OUTPUT_LOG）

LOG_START_ROW = 2 の設定だと、LOG_SHEET の２行目から書き始めるので、１行目に、ログ名を書いとくのがいいある。

MAX_LOG の値を増やせば、区別して保存できるログ本数が増える。本数を増やしたら、ERROR_LOG = 1, OUTPUT_LOG = 2, 3, 4, ・・・って感じで、定義を増やしていくだけで、OK ある。

マクロを別モジュールに書いてもいい。LOG_SW を False にすれば、ログは出力されない。マクロのあるワークブックと、参照・処理するワークブックが別になってても、OK どす。

　課題項目は、

　　　・範囲クリアに "" を代入してるのがかっこわるい。

かなり調べたんだけど、ClearContents を使うと、どうしてもエラーになってしまう。

　このマクロの中に、重要なチップスが隠れてる。それは、

　　　「アクティブでないワークシートに値を書く方法。」

これ、WEB 上で調べても、なかなかうまく行かなかった。ポイントは、With ではなく、

　　　.Range( .Cells(2, n), .Cells(Rows.Count, n)).Value

の所。Cells の前に「 ．」が必要なんて、わかんねぇって・・・

あと、Rows.Count は最大行数（≒最終行の行番号）を返してくれる。

※改訂版もあるよ。

EXCEL VBA TIPs. （範囲名の扱い方、いろいろ）

　見てる人がいるのかいないのか、役に立つのか立たないのかは分からないけど、自分がマクロを忘れた時のために、モメモメ。

　エクセルのセル範囲に、範囲名を付けて利用するのは常套手段。その扱い方。

仮に、以下のセル範囲の範囲名を［ hani ］とする。

10	20	30
11	21	31
12	22	32

「 10, 11, 12 」の列範囲は、

　　　Range( "hani" ).Columns(1)

で取得できる。指定できる。「 10, 20, 30 」の行範囲は、

　　　Range( "hani" ).Rows(1)

で取得できる。

この切り出した範囲を Match( ) とかの関数の引数に入れて使うことが出来る。

　それでは、この範囲内の特定のセルの Range オブジェクトを取得するにはどうするか。例えば、「 22 」の入っているセルは、

　　　Range( "hani" ).Cells( 3, 2 )

が、このセルの Range オブジェクトを返してくれる。

ここで思った。

　　　Cells って、数値で座標を指定するためにあるんじゃないんだ・・・

最初、Range は「 A1 」形式でセルの Range オブジェクトを取得でき、Cells は（行番号、列番号）で取得するための物だと思ってた。

でも、さっきのを見ると、Cells って、ある Range 範囲内の特定の座標の Range オブジェクトを返すって感じ。

　　　Cells( column, row )

は、

　　　Range(ワークシートの全セル).Cells( column, row )

の Range の部分を省略した形、って感じ。この考えが合ってるかどうか分かんないけど、なんかしっくり来る。覚えやすい。

この考え方、本とか、WEB 上に載ってない、もしくは、見つけられなかった。よって、合っているかどうかは不明。

EXCEL VBA TIPs. （入力規則範囲名の取得方法）

　ひょんな事から、仕事でエクセルのマクロをやらなくてはいけなくなってしまった。もうだいぶ忘れてて、ネットで検索しまくり。

EXCEL のマクロ・VBA に関しては、星の数ほどの情報ページがあるので、ここでは、そこで見つからなかったり、探しずらかったりした物だけをピックアップ。

　仕事の中で、セルに設定した「入力規則」の範囲名を知る必要があったんだけど、

　　　「ネットに、みつかんねぇ・・・」

って事で、ここで情報発信。

セルの入力規則に設定した範囲名は、TCell が Range オブジェクトだとして、

　　　TCell.Validation.Formula1

で取得できる。

このとき、セルの入力規則に設定された範囲名が [ hani ] だとすると、セルに入力したときのように、

　　　=hani

とイコール付きで返ってくる。そこで、これを、

　　　Mid( TCell.Validation.Formula1, 2 )

のようにすると、よろしある。

PS. どうやってこれを見つけたか知りたい場合は、コメしてちょ。