green

Lotusnut >> Soft >> Visual Basic for Application >> VBA

インタプリンタ言語「EXCEL_VBA」とは・・・

  1. ■　なぜＶＢＡなのか
  2. ■　フォームを作る
  3. ■　二つのプロシージャ
  4. ■　再利用可能なプロシージャの勧め
  5. ■　具体的なロジックの実装
  6. ■　程度の良いVBAフォームの設計

■　なぜＶＢＡなのか

前章「エクセル」の項で、標準関数（エクセルのセル上で利用できる予め用意された関数）の例を示しました。

セル範囲の合計を計算する関数　=sum(範囲)を例にとりましょう。例の「E列」のような記述によって、セルE２に表示される合計額は３００点になり、平均は７５点になります。

      A B C D E
    １ 国語 算数 理科 社会 合計
    ２ ８０ ８５ ６５ ７０ =SUM(A2:D2)
    ３ ８０ ８５ ６５ ７０ =AVERAGE(A2:D2)

標準関数はとっても便利で、標準関数の範囲内で作成できるプログラムなら、シート上にうまく配置したフォームで見栄えの良い作品ができるでしょう？そして、それを作者自身のみが使うというのであれば、とても魅力的で簡単な操作で思ったような仕事ができるとおもいます。また、とてもよくできた作品は、ぜひとも他人にも使ってもらって評価を得たいものです。また他人に使ってもらうことによって、勉強にもなり、プログラムの腕も上がるというものです。しかしながら、第三者に使ってもらうとなると、いろいろ問題が出てくることも確かです。その一つの例が上記の標準関数を消されない（上書き）ように守ってあげる必要性が出てくるということです。つまり、関数というものを知らない人が関数の入ったセルを侵す危険があるということです。確かにそのためにEXCELには、セルを保護する仕組みもちゃんと整っています。普通の人は、この保護の仕組みを利用して、大事な関数を守ってあげるシート上の標準関数を中心としたプログラムを作ることを心がけていけばよいと思います。

しかしながら、なぜそれでもVBAを使うプログラムの必要性や必然性があるのでしょうか？

一つは上記のような他人に使ってもらう時、未然にエラーを防ぎたい。一つは上級？の段階に進みプログラムを勉強したい。一つは関数を裏で（VBA）つくり、セルに値として返す有利さ。・・・などなど有利な点は、数多くあると思います。すこし、その辺のことを記述していきたいと思います。

皆さんは、マクロとＶＢＡは何が違うと感じているでしょう？コードを見た上では、どちらも同じではないかと思われるでしょう？一言で言うと、マクロはあくまでエクセルの用意された標準的なロジック（コード）といえます。それに引き換えＶＢＡを使うということは、コードを一から自分で考え、自分で作れる所に違いがあると思います。マクロは操作の記録によってエクセルが自動でコードを書いてくれ、それを理解しようがしまいが、動くのです。つまり、自分で作ったという感覚が薄れると感じるのです。

ここで、自作という意味で、上記の見本に出したＳＵＭ関数を自分なりにロジック（コード）で表わすと、別表・ロジックのようになります。標準のＳＵＭ関数とは、引数（＝（）で囲んだロジックに渡す値）の数に違いがありますが、ＶＢＡを勉強することで、標準関数の動き方が大体自分なりに理解できるようになります。（このことは、コンピュータという言う広域の意味を理解する上でもとても勉強になることです。）　また、下段のロジックで示したとおり、シート上のセルを変化させた時のイベントのコードでは、もちろん、標準関数でセルに書き込んだ関数を他人に消されても、まったく問題のない裏（自分なりの表現では見えないところという意味）で動かし、結果を表に出すというＶＢＡの行為は、明らかに有利なエクセルの設計になると思っています。

    Private Sub Worksheet_Change(ByVal Target As Range)

    　　' 作　　者(Writer) : lotusnut
    　　' 目　　的(Aim) : シート上のあるセルの値が変化したときのイベント
    　　' 履　　歴(History): Ver 0.0.0 2005 Original
    　　'
    　　On Error GoTo ErrTrap

    　　'変数の宣言
    　　Dim rng as Range

    　　'変数のインスタンス化
    　　Set rng = Range("B2:E2")

    　　'/* 処理の実行 */

    　　'セルの値を変更した場所がターゲットアドレスなら合計額を計算
    　　Select Case Target.Address
    　　　　Case "$B$2" To "$E$2"
    　　　　　　Range("F2").Value = dfSum(rng)
    　　End Select

    　　'インスタンスの開放
    　　Set rng = Nothing

    　　Exit Sub

    'ｴﾗｰﾄﾗｯﾌﾟ
    ErrTrap:
    　　Debug.Print ”dfSum ｴﾗｰ : ”＆ Err.Description
    　　On Error GoTo 0

    End Sub

■　フォームを作る

ＶＢＡを使う上で、二人三脚の役割を果たすのが、「ユーザーフォーム（form）」です。エクセルメニューの「ツール」「マクロ」「Visial Basic Editor」を開いて見てください。「挿入」「ユーザーフォーム」でフォームの基が表示されます。そして、下図のような「ツールボックス」内のコントロールを使って、フォームを設計します。

具体的に、あるフォームを見て見ましょう。

  • オプションボタン
    フレームの中に入れることによって、複数のオプションボタンが一つのみ選択可能になります。
  • テキストボックス
    基本的なコントロールで、数字･文字列・日付、なんでも入る変数の箱の役割を担います。
  • コンボボックス
    名称や番号記号等をリスト化し、ユーザーインターフェースを楽にさせます。
  • リストボックス
    コンボボックスが表示部分が一行に対し、数行が見えるリスト構造になります。
  • コマンドボタン
    実行やキャンセルのきっかけになるボタンコントロール
  • チェックボタン
    チェック付のラベル判定に利用します。
  • マルチページ
    タブインデックスを切り替えとして、数ページの項目を管理できます。
  • その他ファイル管理や画像・映像･音楽など数々の種類、形態が用意されています。
    		•

それでは、具体的にコントロールの利用の仕方を提示しましょう・・・

    １）　コンボボックスにリストを作り選択された項目をテキストに表示するフォームを作ってみます。

    まず各コントロールの説明と名前を付けます。

  • フォームのオブジェクト名をプロパティで'frmMusic'とします。またCaption = '音楽ジャンルの選択'と表題を付けます。
  • ジャンルを入れるコンボボックスを作ります。プロパティでオブジェクト名（name） = 'cboGenre' とします。
  • ジャンルで選択された項目を決定として、テキストに代入します。 name = 'txtRet'とします。
  • 決定された項目をセルに出力します。ここがフォームとシート（セル）との連絡です。name = 'cmdOk'とします。
  • このフォーム自体を閉じるボタンを作ります。　name = 'cmdCancel'とします。
    		•

    Private Sub UserForm_Initialize( )
    　　'
    　　'// 汎用・システム関数の処理 //
    　　'// lotusnut Ver 0.0.0 2006 Original //
    　　'
    　　On Error GoTo ErrTrap

    　　'変数の宣言
    　　Dim vntList As Variant

    　　'変数への代入
    　　vntList = Array("クラシック", "ジャズ", "ロック", "バロック", "ヒーリング")

    　　'/* 処理の実行 */

    　　'ジャンルリスト化
    　　For i = 0 To 4
    　　　　cboGenre.AddItem vntList(i)
    　　Next i

    　　'リスト初期値
    　　cboGenre.ListIndex = 0

    　　Exit Sub

    　　'ｴﾗｰﾄﾗｯﾌﾟ

    ErrTrap:
    　Debug.Print "UserForm_Initialize ｴﾗｰ : " ＆ Err.Description
    　On Error GoTo 0

    End Sub
  • フォームの起動
    フォームの起動前のイベントは’UserForm_Initialize(　)’を使います。ここで、リストにジャンルを結びつけ初期表示のリストを決めます。
    		•

今度は、リストで選択した項目をテキストボックスに表示します。

    Private Sub cboGenre_Change()
    　　'
    　　'// 汎用・システム関数の処理 //
    　　'// lotusnut Ver 0.0.0 2006 Original //
    　　'
    　　On Error GoTo ErrTrap

    　　'/* 処理の実行 */

    　　'選択されたリスト項目をテキストに表示
    　　txtRet.Value = cboGenre.Value

    　　Exit Sub

    　　'ｴﾗｰﾄﾗｯﾌﾟ
    　ErrTrap:
    　Debug.Print ”cboGenre_Change ｴﾗｰ : ”＆ Err.Description

    On Error GoTo 0 End Sub

そして、最後にテキストに入力された項目をセルに出力するロジックとフォームを閉じるロジックを提示します。

    Private Sub cmdOk_Click()
    　　'
    　　'// 汎用・システム関数の処理 //
    　　'// lotusnut Ver 0.0.0 2006 Original //
    　　'
    　　On Error GoTo ErrTrap

    　　'/* 処理の実行 */

    　　'セルにテキスト内容を出力
    　　Range(”A1”).Value = txtRet.Value

    　　Exit Sub

    　'ｴﾗｰﾄﾗｯﾌﾟ
    　ErrTrap:
    　Debug.Print ”cmdOk_Click ｴﾗｰ : ”＆ Err.Description
    　On Error GoTo 0

    End Sub

    Private Sub cmdCancel_Click()
    　　'
    　　'// 汎用・システム関数の処理 //
    　　'// lotusnut Ver 0.0.0 2006 Original //
    　　'
    　　On Error GoTo ErrTrap

    　　'/* 処理の実行 */

    　　'フォームを閉じる
    　　Unload Me

    　　Exit Sub

    'ｴﾗｰﾄﾗｯﾌﾟ
    ErrTrap:
    Debug.Print ”cmdCancel_Click ｴﾗｰ : ”＆Err.Description
    On Error GoTo 0

    End Sub

当然ながら、上記のような冗長なフォームの設計をするプログラマーはいないと思うのですが、リストの使い方。コントロールからコントロールに値を渡す流れ、セルに出力する方法等、初心者の方には、コードをなぞってみてほしいと思います。このような簡単な設計が基本となって複雑なロジックも生まれていくということを、感じてください。

■　二つのプロシージャ

ＶＢＡ（ＶＢ）には、プロシージャという定義された二つの「かたち」がある。

ひとつは Private sub test(ByVal intCcount As Integer )　・・・・・End Subでｓｕｂプロシージャと呼ぶ。

もひとつは、Public Function test(ByVal intCcount As Integer) As Boolean　・・・・・　End FunctionでＦｕｎｃｔｉｏｎプロシージャという。

ともに（　）のなかは、引数と呼ばれ、値渡しや参照渡しで　〜Ｅｎｄまでのコードの中で、必要に応じて利用する。そして、この二つのプロシージャのなかでも、Ｆｕｎｃｔｉｏｎプロシージャは　（引数）の後に　As ・・・（Booleanは型のひとつ）　のかたちで、値を返すこともできる。つまり、Functionプロシージャのことを特に関数と呼んでいる。もうお気づきのことかと思いますが、標準関数の「関数」とは、引数を渡し、値を返す　（たとえばSUM(範囲)の結果が合計）Functionプロシージャのことであるといえます。

二つのどちらのプロシージャも利用されるわけですが、自分で作るプロシージャは、できるだけ関数（Function）になるように作ることを心がけたいものです。しかも、特に何かの結果を返すものの他では、正常に関数が動いたかどうかの検証を伴った関数にすることによって、より強固なロジックができると思っています。それらの例をできるだけこの後で、示せたらなと思っています。

ページＴＯＰへ

■　再利用可能なプロシージャの勧め

プロシージャ（自分は好んで「ロジック」という）を作成する上で、個人的に考えて作っていく信条がある。それは、汎用性があり後々再利用できるように、一つ一つのロジックを検証しながら積み上げていくということである。そのためにも引数をうまく利用することになる。そして、最初の解説の項でも述べてあるが、「安全なエラーコーディングの実践」である。エラーを未然に防ぎつつエラー箇所がわかり易くなるような書き方である。エラーが埋もれて表面のみが、なんとなく動くという作り方は、どうも自分のスタイルには合っていなし。以下の項では、大胆にも・・・恥ずかしくも･･・小生のロジックを提示したいと思うのだが、あくまでもプログラムロジックというものは、個人の趣味性が強く出るもので、個性が出るものです。その手の先輩諸氏には、なんか変でない？というようなロジックも度々目にしてしまうかも知れませんが、そこは、まさに大目に見て、通り過ぎてください。

ページＴＯＰへ

■　具体的なロジックの実装

というわけで、趣味的ロジックにお付き合いください。

[ 基本形はくずさない ]

  • 大敵はエラー処理

    • 下記のロジックを見てください。3行目でいきなりエラー処理の宣言をします。・・・以下にエラーが発生したらErrTrap（23行目）に飛ぶ。つまり予期しないロジック中のエラーはすべて、実行することなくエラーとして処理し、デバックの手伝いをします。もちろん実行途中で、ユーザーによる予期できるエラーをメッセージなどで、知らしめることは、懸命な処理ですが、実は、エラーの大敵は、予期できぬエラーであったり、予期できても、完璧なロジックへの道は険しいのが現実であることを考えると、少なくても、たとえばエラー処理に「On Error Resume Next（エラーが発生しても無視して次の処理に進む）」を使うようなロジック構成にすべきではないと思うのです。9行目と18行目の処理も重要です。自作のプロシージャは必ず関数を使っていただきたいことは、先に説明しました。False（偽）で初期化し、ロジックが正常に進めば、True（真）でロジックを完成させます。ロジックの終了（Exit Function）のすぐ前に書くことで、予期せぬエラー以外のエラーでの終了処理を受けて側の関数へ真偽を渡すことができます。

    1. Private Function dfTest(ByVal strpath As String) As Boolean
    3. 　On Error GoTo ErrTrap
    5. 　'ﾛｰｶﾙ変数の宣言
    6. 　Dim test as String
    7. 　'戻り値の初期化
    8. 　dfTest = False
    9. 　　・
    10. 　'***** ﾌﾟﾛｼｰｼﾞｬ処理の実行 *****
    11. 　　・
    12. 　'正常な戻り値
    13. 　dfTest = True
    14. 　
    15. 　Exit Function
    17. 'ｴﾗｰﾄﾗｯﾌﾟ
    18. ErrTrap:
    19. 　Debug.Print "dfTestｴﾗｰ : " & Err.Description
    20. 　On Error GoTo 0
    21. End Function

Excel VBA Logic written by lotusnut ・・・・・

次にExcelで使うロジックをいくつか紹介したいと思います。

[ エクセルファイル処理関連ロジック ]

ページＴＯＰへ

■　程度の良いVBAフォームの設計

エクセルで、ＶＢＡを利用する要素に欠かせないのが、フォームの設計でしょう。前節までに少しさわりを述べてきましたが、ここでは具体的にフォームとエクセルの連携について述べて生きたいと思います。また、エクセルの頁でセル上のコントロールを説明しました。それらとは、動作的には似ているのですが、Visual Basic Editorで作成するフォーム設計は、もうほとんどVBやCの世界で、エクセルとの連携では、ボタンや右クリックをイベントとして表示させるもので、ここまでくれば、ローカルプログラムの設計となんら違いの無く引けをとらない「プログラム」になります。しかもEXCEL−VBAということですから、主要な表処理や表示はEXCELのセルや印刷機能に任せて、ただ、EXCELとのインターフェースプログラムに集中できるという利点が出てきます。皆さんも実際に作成を始められれば、それを実感できると思います。

十人十色とは言いますが、プログラムの設計もやはり、人の個性が出てきます。読者諸氏の皆さんには、気に入らない点等多々あることをご了解の上、お付き合いください。

エクセルとの連携

  1. 「イベント処理」・・・右クリックの例
    1. Private Sub Worksheet_BeforeRightClick
      　 　　　　　　　　　　　　(ByVal Target As Range, Cancel As Boolean)
    2. 'システム関数の右クリック後の処理フォームを表示
    4. 　On Error GoTo ErrTrap
    6. 　'ﾛｰｶﾙ変数の宣言
    7. 　Dim ｓｔｒAdd As String
    8. 　'変数の初期化
    9. 　ｓｔｒAdd = Target.Address
    11. 　'***** ﾌﾟﾛｼｰｼﾞｬ処理の実行 *****
    13. 　'ターゲットセルでフォームを表示
    14. 　Select Case ｓｔｒAdd
    15. 　　Case "$A$1" To "$A$10"
    16. 　　　'フォームの表示
    17. 　　　frmInput.Show
    18. 　　　Cancel = True
    19. 　　Case Else
    20. 　　　'キャンセル後の処理
    21. 　　　Cancel = False
    22. 　End Select
    24. 　Exit Sub
    26. 'ｴﾗｰﾄﾗｯﾌﾟ
    27. ErrTrap:
    28. 　Debug.Print "Worksheet_BeforeRightClick ｴﾗｰ : " & Err.Description
    29. 　On Error GoTo 0
    30. End Sub

    ポイント：エクセルを理解されている方には、すぐお分かりになると思うのですが、通常右クリックは、使用頻度の高いエクセルのメニューが割り当てられています。右クリックの制御を強制的に自作フォームに割り込む形になるので、18行目でその辺との兼ね合いを取ってあります。（何を言っているかわからない方は、18行と21行をはずして、試しても理解できると思います。）

  2. フォームからセルへに出力

      3. 右に図のように、右クリックで、フォームを表示した後、必要なテキストボックスに項目を入れ、「セル出力」ボタンでセルに出力しています。見てのとおり、テキストボックス、スピンボタン（日付変更）、オプションボタン（収入・支出の別）、各種ボタン等適材適所に配置することが重要です。

      1. Private Sub cmdok_Click()
      2. '// 汎用・システム関数の処理 //
      3. '// lotusnut Ver 0.0.0 2005 Original //
      4. '
      5. 　On Error GoTo ErrTrap
      7. 　'変数の宣言
      8. 　Dim myDate As String
      9. 　Dim Ws As Worksheet
      10. 　Dim lngRow As Long
      11. 　Dim vntData As Variant
      12. 　Dim strInpay As String
      13. 　Dim strOutpay As String
      14. 　Dim intPage As Integer
      16. 　'変数への代入
      17. 　myDate = txtdate.Value
      18. 　Set Ws = Worksheets("Data")
      19. 　intPage = Range("page").Value
      21. 　'/* 処理の実行 */
      22. 　
      23. 　'データ部対象セルの検索
      24. 　lngRow = (intPage - 1) * 35 + ActiveCell.Row
      26. 　'収入OR支出の確認
      27. 　If optuke Then
      28. 　　strInpay = txtkin.Value
      29. 　　strOutpay =""
      30. 　Else
      31. 　　strInpay = ""
      32. 　　strOutpay = txtkin.Value
      33. 　End If
      35. 　'各セルに該当項目を入力
      36. 　vntData = Array(Month(myDate), Day(myDate), txtteki.Value, _
      37. 　txtRyo.Value, strInpay, strOutpay, cboItem.Text)
      39. 　'一レコード分データの取得
      40. 　Ws.Range("B" & lngRow & ":H" & lngRow).Value = vntData
      42. 　'インスタンスの開放
      43. 　Set Ws = Nothing
      45. 　'次の行に移動
      46. 　If lngRow = 38 Then
      47. 　　Range("page").Value = intPage + 1
      48. 　　'先頭セルに移動
      49. 　　ActiveCell.Offset(-35, 0).Select
      50. 　　lngRow = (intPage) * 35 + ActiveCell.Row
      51. 　Else
      52. 　　'下のセルに移動
      53. 　　ActiveCell.Offset(1, 0).Select
      54. 　　lngRow = lngRow + 1
      55. 　End If
      57. 　'表示
      58. 　If Not dfShowCellData(lngRow) Then Debug.Print "表示失敗"
      60. 　'フォーカス移動
      61. 　txtteki.SetFocus
      63. 　Exit Sub
      65. 'ｴﾗｰﾄﾗｯﾌﾟ
      66. ErrTrap:
      67. 　Debug.Print "cmdok_Click ｴﾗｰ : " & Err.Description
      68. 　On Error GoTo 0
      70. End Sub

    上記のセルへの出力の例は、勿論あくまで一例であって、いろいろと出力の書き方はあります。36行〜40行目のコントロールの値を配列変数に格納し、セルに結び付けることです。この例でも一つずつコントロールの値をセルに出力しても、ＯＫなのですが、セルに出力する時が一番速さに影響するので、出力するときは、一気に。その前準備として変数にできるだけ格納しておくことを心がけたいものです。

ページＴＯＰへ