ブログを始めてみたけれど・・・

ただのオッサンのひとり言が多いです

VBA基本

分かりづらいタイトルですが、前回のダブルクリックでプロシージャ、マクロを実行するのと同じでダブルクリックイベントではなく、セルへの入力で実行する方法です。
キャプチャ1

前回のサンプルに書き足しているのでA1〜3が入力済みですが今回に関係ありません。A5のセルに入力規則を設定してリスト選択で操作1〜3を選択・入力できるようにしています。

で、コードは前回のものをチェンジイベントにコピペしただけ。
キャプチャ2
ただ、チェンジイベントはワークシートのすべてのセルでの入力や消去で発生して、どこかのセルに入力するようなマクロだとそれでまたチェンジイベントが発生してマクロが実行されて、という無限ループに陥る可能性があります。予め限定したセルのときだけ実行されるようにしたり、延々とマクロが実行されないように注意しましょう。

で、今回はIf文で入力変更したセルのアドレスが「$A$5」(入力規則を設定したセルA5)のときだけ処理を開始するようにしています。セル(Range)のAddressプロパティでは絶対参照表記のアドレスが返されるのでドル記号をつけた値で判定しています。

If Target.Column =1 And Target.Row = 5 Then
としても同様になりますので分かりやすい方法で判定してください。で、セルA5の値を選択すると最初の画像のようにマクロが実行されます。

この場合、セルA5の値が操作1となっていてあらためて操作1を選択すると操作1の処理が実行されますので、それを避けたい場合はどんな方法があるか・・・(すいません、検証していないです)



あまり需要のないイベント処理ですかね?それとも誰でもやっている?

よかったら参考にしてみてください(ネタがないので似たような記事が多いな)。

こんなことしたい人はあまりいないかな?

マクロを実行するのにボタンをワークシートに用意したりメニューから呼び出せるようにすることが多いと思うけど、ダブルクリックしたセルの内容で呼び出すプロシージャを決めたい。


たとえばこんな事できます。
キャプチャ2

操作2と入力されているセルをダブルクリックすると操作2が実行される。ここではメッセージボックスを表示するだけですが、それを別の作業に書き換えればそのように実行されます。


キャプチャ1
仕組みは簡単で、ダブルクリックしたセルの内容によってSelect Caseで分岐しているだけ。

シートモジュール(上の「操作1」などを入力したワークシート)に
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Select Case Target.Value
    Case "操作1"
      MsgBox "操作1を実行します"
    Case "操作2"
      MsgBox "操作2を実行します"
    Case "操作3"
      MsgBox "操作3を実行します"
    Case Else
      Exit Sub
  End Select
  Cancel = True
End Sub
とします。この場合セルの内容とCase "XXXX"が一致するか判定するので「Case "操作1"」などの数字はセルに入力したものと同様に全角、半角を揃えないと実行されないので注意。

Case Else はセルの入力が操作1〜3以外の場合に実行されここではプロシージャを終了(Exit Sub End Subではない)し最後の Cancel = Trueを実行しないようにしています。

なぜそんなことをするのか?Exit Sub、またはCancel = Trueをコメントアウト(行頭にシングルクォーテーション)してどのように変わるか確認してください。

なお、この方法だと意図しないセルに「操作1」などと入力してダブルクリックすると実行されてしまうので、それを回避したい場合は特定の列でダブルクリックしたときなど他の条件設定が必要です。




もしかしたら前に似たような内容を記事にしているだろうか?

VBAの記事を読んでくれる(読まずにすぐ立ち去っているかもしれないが)人ばかりなんだけどネタが思い浮かびません。

ネタがなく、思いつきの記事で面白いものでないし深く説明するのも面倒なのでコードを紹介するだけです。


このコードを実行してみてください。
Sub sample1()
 MsgBox MsgBox("テストです")
End Sub
メッセージボックスを使用するコードですが、MsgBoxが2つ続いています。どうなると予想しますか?

次は
Sub sample2()
 MsgBox MsgBox("テストです", vbYesNo)
End Sub
として、表示されるメッセージボックスの「はい」ボタン、「いいえ」ボタンを押して結果を確認してください。

最後に
Sub sample3()
 MsgBox MsgBox("テストです", vbYesNo) = vbYes
End Sub

ま、いずれにしてもどうってことない内容ですが、MsgBoxはよく知っておくと便利なので勉強しておいたほうがいいですよ。

なんて、説明らしい説明をしていないのでVBEでMsgBoxにカーソルを置いてF1キーで表示されるヘルプで確認してください。

MsgBoxはメッセージを表示するために
MsgBox ”メッセージ〜”
のように使われることが多いと思いますが、
MsgBox(”メッセージ〜”)
とする(メッセージほか引数をカッコでくくる)と関数として戻り値を返すようなり、表示されたメッセージボックスのどのボタンを押したかを返します。

なので
MsgBox("テストです", vbYesNo)
とすると「はい」「いいえ」のどちらを押したか返します。が、値としては「6」や「7」になります(sample2では2つ目のメッセージボックスで6か7が表示される)。

と、それでは使う(コードを書く)人間にとって分かりづらいので「vbYes = 6 」「vbNo = 7」のようなシステム定数が設定されています。
MsgBox("テストです", vbYesNo) = vbYes
とすれば「はい」ボタンを押すとTrue、「いいえ」ボタンを押せばFalseになります。

これらもF1キーのヘルプで表示されますから確認してください。

VBAの「癖がすごい」までは行きませんけどね、興味を持ってくれそうなタイトルにしてみました。


なんて、今どきはYouTubeでVBAを教えている人がいるからピンポイントな内容でないと読まれないと思われ、そんなネタがぜんぜん思いつきません。

前回パチンコをシミュレーションするような記事を紹介しましたが、大した行数でないのに2つのプロシージャを用意してメインのプロシージャから別のプロシージャを呼び出すようにしました。

あれしきの処理を2つのプロシージャに分けることに賛否・好き嫌いがあると思いますが、プロシージャからプロシージャを呼び出すのはいつか必要になると思うので簡単に説明しておきます。

Sub Proc1()
  Call Proc2
End Sub

Sub Proc2()
  何らかの処理
End Sub

Proc1からProc2を呼び出す場合はこのようにCallステートメントを使います。実際はCallを使わず「Proc2」とだけしても呼び出すことはできますが、プロシージャ名だけだと何だコレ?となることがある(耄碌のせい?)のでわたしはほぼ、Callステートメントを使います。

ただし、Functionプロシージャを呼び出す場合は戻り値を受取る変数などとセットで使う事が多いのでCallは使いません。

Sub Proc1()
  Dim val As Integer

  val = Proc3
End Sub

Function Proc3() As Integer
  何らかの処理
  Proc3=何らかの値
End Function

なお上記はシンプルな例で、引数を使うプロシージャ
  Sub Proc4(arg As String)
を呼び出す場合は記述法が違いますので
  Call Proc4("String arg")や
  Proc4 "String arg" のように 結果は同じ
ヘルプで調べてください。



いきあたりばったりなので説明が前後して申し訳ないです。

わからないことはネットで調べるのも有効ですがVBEでF1キーを使ってヘルプを表示するのも助けになりますので、分からないものにカーソルをセットして(たとえばCallのどこかにカーソル)F1キーを押してみてください。

学校もう始まる?まだ行けない人もいるんだろうけど、VBA勉強している人いる?

そんな学生さんはあまりいないかもしれないが、ゲームとかはハードルが高いので(私にとって)小学生でも興味を持ってくれそうな座席表づくりをやってみた。

いきなり始まるのでVBAやったことない人はチンプンカンプンと思うけど、このブログの「VBA はじめてみる?」を参考にしてください。試してみるだけなら普通のワークブックで大丈夫だけど保存したい場合はマクロ有効ブックに保存してくださいね(それも「VBA はじめてみる?」を参照)。



簡単にするためシンプルな座席表です。まずワークシートに9名の名前を入力してください。コードが「G列1行目から」を想定しているので図のように入力してください。
座席表01
せっかくなので憧れの有名人が同級生の設定です。


コードは以下のようになります(座席表を作るワークシートモジュールに記述しています)。

Option Explicit

Sub 座席表作り()
  Dim iRow As Integer, iCol As Integer
  
  'Application.ScreenUpdating = False
  
  Range("A1:E7").Value = ""
  cells(1, 3).Value = "先生"
  
  '作業用に名前リストを別列にコピー
  Range("G1:G9").Copy Range("I1")
  
  'ワークシートの3,5,7行目に
  For iRow = 3 To 7 Step 2
    'ワークシートの1,3,5列目に
    For iCol = 1 To 5 Step 2
      cells(iRow, iCol).Value = 名前
    Next
  Next
  
  'Application.ScreenUpdating = True
  
End Sub

Function 名前() As String
  Dim iRow As Integer, rowCnt As Integer
  
  'I(アイ)列の行数を変数に格納
  rowCnt = Range("I1").CurrentRegion.rows.count
  '乱数の初期化
  Randomize

  '名前リストの行数から任意の行を取得
  iRow = Int(Rnd() * rowCnt) + 1
  '取得した行の名前を返す
  名前 = cells(iRow, 9).Value
  
  '最終行の名前を返した名前の位置に入替え
  cells(iRow, 9).Value = cells(rowCnt, 9).Value
  '最終行に入った名前はもう使わないので消去
  cells(rowCnt, 9).Value = ""
  
End Function


で、Subプロシージャの座席表づくり()を実行(実行法も「VBAはじめてみる?」を参照)すると下図のようになります。

座席表02

座席表作りというか、名前のリストからランダムに出力しただけですけどね、自分の気持ちいい座席になるまで何度でも試行できます・・・(架空の座席表が楽しいかはともかく)

3密を避けるために?席を一列、一段おきに設定しています(Forステートメントの Step 2)。リストからランダムに名前を取得する部分はFunctionプロシージャ名前()を用意してそこから取得するようにしています。

名前()ではコメントで説明していますが、リストをコピーしたデータからランダムに名前を取得し、一旦取得した名前は消去して次は選択されないようにしています。

どうなっているか分かりづらい場合はステップ実行(これも「VBAはじめて・・・」参照)してコピーしたデータがどうなるか確認してください。

なお9名の設定なのでForステートメントの数値や名前リストの位置が固定されていますし、その他も思いつきで書いたため加工しづらいコードになっているかな。

名前データコピーのあとCurrentRegionを使っていて、貼り付け先でほかのデータと接触していたら正しいデータ数を取得できない場合があるので注意してください。

座席表づくり()の2箇所でApplicastioon.ScreenUpdating〜がコメントアウトされていますがコメントを外して実行する(F5キー等で)とどう変わるか、確認してください。

初心者の方でも、と思って記事にしたけどちょっとヘビーだったかな?ま、そもそもこの記事が読まれる可能性が低いのだけど。

説明不足でわかりづらいかもしれませんが、休校でヒマ、VBAやってみたい人はどんなもんかやってみてください。

数年前、会計が使う帳簿ファイルをマクロ入りで作った。小さな団体の会計なので複雑な処理などないがパソコン不慣れな人も使うし、少しでもラクできるようにと。

で、数人の会計係を経てこんど自分が会計をすることになり久々に戻ってきた(引き継いだ)ファイルを見たら、マクロは思ったほど使われていないことがわかった。

一応説明として、どうしたらどんなことができるのかメモとしてテキストファイルやエクセルのシートに書き込んでおいたがそれをそもそも読んでいない可能性があり、自分流に手を加えて使っていた模様。

ま、しょうがないな。もっと使いやすい、わかりやすいマクロを、と考えても同じかもしれないし余計なことはしないでおこう。

VBAでいろんなことができるけど、それをだれでも使いやすく、わかりやすいものにするのが一番難しいかもしれない(私の作り方がマズイ可能性ももちろんあるけど)。

最近の記事で図らずも「オーバーフロー」というのが出てきました。

オーバーフローが起きるとプログラムは止まってしまうのですが、そもそもオーバーフローって何?

コンピュータはデータを扱うのにメモリに保存したりしますが、その際にデータ種類によって使用するメモリ容量を予め決めています。VBAの場合Integer型は2バイト、Long型は4バイトなど(プログラミング言語によってデータ種類とデータサイズは異なります)。

データサイズ(メモリの使用量)が決まっていることで読み込みが高速にできるとか、貴重なメモリ領域を無駄使いしないように(昔はメモリが高価で搭載量が少なかった。最初に買ったノートPCは8Mバイト!)といった理由でそんな事になっていたと思います。

ということで、データサイズが2バイトだと16ビット(1バイト=8ビット)しか桁を使えないので自ずと扱える数値も限界が生じるということです。

2進数の 1111 1111 1111 1111 は 10進数で65535

のようになり、さらに正負の使い分けするために先頭の1ビット使うので実際に数値で使えるのはのこりの15ビットで

2進数の 0111 1111 1111 1111 は 10進数の32767
 
2進数の 1000 0000 0000 0000 は 10進数の−32768

となります(10進数の値は2バイトで整数を表す場合)。Windows付属の電卓(Windowsキー&Rキーで「calc」と入力)で電卓の種類を「プログラマ」にすればこんなことも確認できますのでやってみてください。

VBAのプログラムでもInteger型の変数を宣言して(Dim i As Integer)32767を格納(i = 32767)し、さらに1を足す(i = i  + 1)コードを実行してみてください。オーバーフローしました?

プログラムで数値を扱う際、こんなことがあること、知っておいてください。
(注)独学で勉強した私の自己流解釈なので間違っている説明あったらご容赦ください。

やはり私のブログはVBAの記事が圧倒的に読まれている(他のが読まれない)のでたまに書いておく。
家にこもって勉強している人いる?


初心者向けです。以下のコードの実行法もわからない方は「VBA はじめてみる?」を参照してください。

Sub Sample()
  Dim i As Integer

  For i = 1 To 10
    cells(i, 1).Value = " i の値は" & i & "です。"
  Next
  
 MsgBox ("入力終了" & vbNewLine & "次は消去します") 
  i = 1
  
  Do
    cells(i, 1).Value = ""
    i = i + 1
  Loop Until i = 11
  
  MsgBox ("実験終わり")
End Sub


上のコードをVBEにコピペして、「VBAでセルに入力」の最後の方でステップ実行について説明しているので、ステップ実行でどのように動くか確認してみてください。

ま、説明はしませんが動きを見て「ここはこういうことか」と考えながら理解するのも大事なので(実は説明が面倒)。

Do~Loopの例はカウンタ変数(上では「i」)を使っているので適切な例ではないですが、「i = i + 1」「Until i = 11」がどんな役割を果たしているのか考えましょう。

また「i = i + 2」や「Until i = 5」などと書き換えて実験してみてください。

ただし、「i = i + 0」」や「Until i = -1」などとすると「無限ループ」と言っていつまでも終わらないプログラムになってしまうのでご注意!(そうなった時は、Functionキー + Breakキーを押す またはCtrl キー + Altキー + Delキーでタスクマネージャーからエクセルを強制終了)
/*追記 Until = -1 とすると「オーバーフロー」で停止しますね。検証してませんでした。削除します*/

なぜ終わらないのかも考えてください。

全然知らなかった。VBAってバージョンアップしていたのね。

さっきなんの気なしにVBAの記事を検索していたら「Office2013からVBA7」というような表記を発見。

ずーっとVBAのバージョンは6だと思っていた私は「エーッ、聞いてないよぉ!」と驚いたけど調べたらOffice2010から7.0で2013から7.1ということのよう。

今使っているのはOffice2010なのですでに7.0に変わっていて自分が知らなかっただけ。最近マクロも書かないのでVBAを調べることも無いし影響もなかった。VBAの記事書く資格ないな。バージョンによる差し障りのないものを書くように気をつけます。

ところでバージョンアップしてどこが変わったんですかね?(そのうち調べます)

VBAで書いたマクロを実行するとき、ワークシートに用意したボタンなどから実行することが多いと思いますが、マクロを表示するダイアログボックスから実行することもできます。

注意が必要なのは、全然想定してない場面でダイアログボックスから実行してしまいプログラムが暴走したり大事なデータを消したり表のレイアウトを変えてしまったり、ワークシートを削除したり。

自分だけが使うファイルなら対処できますが、マクロのことなんか知らないユーザーがたまたまマクロダイアログから実行してしまい目の前のデータが消えてしまったらパニックになるでしょうね。

そうならないよう色々方法あると思いますが、私はプロシージャに「Private」をつけてマクロダイアログに表示されないようにしています。

普通にSub 〜 とするプロシージャはマクロダイアログに表示(Alt + F8キー)されますが、
キャプチャ01

Private Sub 〜とすると

キャプチャ02

ダイアログに表示されなくなります。

「Private」はこのためのステートメントではないのですがこんなことできるよ、ということで知っておいてください。

ちなみに、ダイアログに表示されていませんがマクロ名に「Sample」と入力して実行すると・・・

実験してみてください。

↑このページのトップヘ