おしゃれにいけてる?

散歩やお買い物のことなどを書いています・・・

Category [EXCEL VBA TIPs ] 記事一覧

 情報がデジタルのゴミにならない様に、昔に書いた「 EXCAL VBA マクロ」に関する記事のリストを作ってみた。参考になれば、って事で。

入力規則範囲名の取得方法
範囲名の扱い方、いろいろ
エラー処理ざんまい
改訂版:デバッグログ、吐きまくりマクロ Ver.0.91
範囲名の活用と、行数・列数を取得。
他のワークブック上のマクロ・関数を実行する。
忘れがちな Range オブジェクトの話。
関数 Function Procedure の作り方。
クリップボードにコピーした画像を、ペーストするために取りだす方法。
ワークシート上の画像を、コピーアンドペーストする方法。
ステートメントのまとめ。
データが入力されている範囲を取得する方法。
配列のまとめ。コピーとかも。
プロシジャーの呼び方と、引数の値渡しと参照渡し。
Function (関数)プロシジャーで、配列とか構造体とか、複数の値を渡す・返す方法。
超初心者の時に必要な技。デバッグ。

※全記事は、こちら、カテゴリー「EXCEL VBA TIPs」で。
 会社で、EXCEL マクロ初心者に最初に教えたのが、

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

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

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

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

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

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

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

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

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

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

ここまで出来れば、後は根性。そんな感じ。
 Function Procedure に、配列を引数として渡す方法。この場合、参照渡ししか許されない。

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

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

func1( ) を呼び出すときは、こんな感じ。

   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

 配列を使わずに、複数のデータを渡す場合は、ユーザー定義型の変数を使う。C言語で言う、構造体のようなもの。ユーザー定義型の変数名を TypeTest とすると、

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

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

   Dim tmp As TypeTest
   ans = TypeTest.a

という感じで、構造体の様にメンバを参照できる。

ユーザ定義型変数 TypeTest を戻り値として返す関数は次のように定義する。

   Function Tfunc(in As Integer) As TypeTest
     Tfunc.a = in
   End Function

こんな感じ。これを、

   Dim ans As TypeTest
   ans = Tfunc(10)

の様にして、受け取る。

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

   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 でここまで使うかは疑問だけど、いつか、使うときのために、メモ、メモ。

PS. もし、この記事が役に立ったら、「拍手」で知らせて下さい。
 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 の仕事で、配列は使わなかったんだけど、ちょっと気になったので調査してみた。

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

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

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

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

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 では、この2つは使えなかった。)

これらを使うときは、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. 参照元:エクセル大辞典バリアント型を配列として使う
 エクセルのワークシートのデータが入力されている範囲を取得する方法。

   Thisworkbook.Worksheets("Sheet1").UsedRange

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

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

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

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

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

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

で取得できる。(これに関しては、EXCEL VBA TIPs. [ 忘れがちな Range オブジェクトの話。]で書いた。)
 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 」でヘルプを検索する、見れる。

※他は、こことかを参考に。
 ワークシート上に、「挿入」→「図」→「ファイルから」で画像を貼り付けると、その画像は 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

PS. もし、この記事が役に立ったら、「拍手」で知らせて下さい。
 クリップボードにコピーした画像を、クリップボードから 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

PS. もし、この記事が役に立ったら、「拍手」で知らせて下さい。
 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の値を取る前に、データ種別を判別しろよ、って言うのは、別の話題って事で。46)

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

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

こんな感じ。

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

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

  値渡し:ByVal 変数名 As データ型(デフォルト)
  参照渡し:ByRef 変数名 As データ型(デフォルト)

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

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

左サイドメニュー

プロフィール

Author:ジョニー

ゆるゆると日々を過ごしてしまう、ダメダメサラリーマンの航海日記。



クリック (・。・)yoro

このブログ内を検索

最近の記事

カテゴリー

QRコード

QRコード

月別アーカイブ

右サイドメニュー

カレンダー

07 | 2017/08 | 09
- - 1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31 - -

その他の話題

どらまらんど
どらま・CM・TV全般・映画の話題はこちらです

デジ太る
プログラミング、EXCEL VBA、PC、Mac の話題です

スポンサードリンク

FC2カウンター

#2977 #1478 #1471 #1467 #1463 #1459 #1431 #1414 #1413 #1411