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

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

ぷろぐらまもどき

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

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

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

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

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

Private Sub 〜とすると

キャプチャ02

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

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

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

実験してみてください。

VBAは便利なのですが、VBA、マクロで処理したものは「元に戻す」ことができませんのでご注意。

データの消去とか書き換えなど単純な処理だからと安易にマクロ化すると「あ、しまった。Ctrl+Zキーで元に戻そう」なんてことできません。

ファイルを保存したあとのことなら保存せずにファイルを閉じて開き直せば元の状態になりますけどね。なかなか都合よく行かない場合が多くショックを受けることもある、かな。

そもそもそんなことにも備えてマクロ化するべき(実行前に保存するマクロを含める)か?マクロで元に戻す機能を作る?

いや、面倒くさい。やっぱり慎重に実行するのがいいと思います。人間、何事も慎重に、です。私は「本当に実行していいか?元に戻せないよ?」とメッセージボックスを表示するようにしています。




VBA関連で訪れる人ばかりなのにあまりにも記事を書いていないので無理やり書きました。

自己満足な記事で申し訳ない。VBAの記事ってどんなのが求められているんですかね?

計算式を編集できないようにシート保護してあるExcelワークブックの修正をする際、保護シートが複数あるのでそれらを選択して一括で保護解除できるかと思ったらメニューからはできず(「書式」ー「シートの保護・解除」はグレーアウト)、かと言ってそれぞれのシートごとにするのは面倒なのでVBAでやろうと考えた。

ん?でもこのファイル普通のExcelブックでマクロ有効ブックでないのだが?他人に作ってあげたマクロの必要ないユーザーが入力して計算、表示するだけのワークブック。

結論としては普通の「.xlsx」でもマクロ使えました。保存はできませんけど(コメントとして残しても消される)。普通のワークブックでもマクロ実行できるの当たり前と言えば当たり前なんだろうが、こんなことしたことなかったからなんか新鮮な発見でした。

’シート保護解除するコード。パスワードなし。
Sub shtUnprotect()
  Dim sht As Worksheet
  
  For Each sht In Worksheets
    If sht.Name Like "*月売上" Then
      sht.Unprotect
    End If
  Next
End Sub

特定の名前のシートだけ処理対象とするようにしている。「Like」にカーソル(テキスト上で左クリックすると現れる「点滅する縦棒」)を置いて「F1キー」を押すとヘルプでLikeのことやパターンマッチング、ワイルドカード(*月売上の「*」)を説明しているので読むといいです。

保護するときはUnprotectをProtectにする。
sht.Protect

パスワードを設定したい場合はProtectのヘルプで調べてください。


For Each sht In Worksheets

のWorksheetsはワークブックのすべてのワークシートをあらわすオブジェクト(コレクション)で、上のように書くことで各ワークシートをワークシート型変数(ここではsht)に一旦格納して処理の対象とすることができます。

わかりやすい実験としてワークシートの名前をメッセージボックスやセルに出力するといいです。ただし、順番は特定できない(どの順で処理対象となるかわからない)のでご注意。

For Each 〜 In 〜

Next

はよく使うステートメントですのでこれもヘルプで調べてください(説明面倒なので)。


マクロ有効ブックでなくても何かの処理でマクロを使いたい時もあるので、こんなことも知っておくといいですね(みんな知ってた?知らないの自分だけ?)。

思いつきで作ったファイルに同じ項目で同じ金額の入力が頻繁に登場するので楽できないかと考えてみた。

色んな方法あると思うがなにかのヒントになれば。自分のためにもボケ防止になるかと。


VBA定型01

ここではワークシートの上部に利用するデータなどをまとめてある。最初に設定したら後は行非表示やグループ化で見えないようにする。

青く塗りつぶした列は「MyList」、下の集計表のデータ入力部には「MyData」と名前定義している。MyListは摘要列のリスト選択のソース元にも設定している。

で、摘要でクレジット引落、損害保険などと選択すると上部で設定した金額が自動入力されるようにした。

ワークシートモジュールに以下のコードを記述

'摘要入力時に金額入力処理
Sub enterAmount(r As Range)
  'Find()のxlWholeは完全一致で検索
  r.Offset(0, 1).Value = Range("MyList").Find(r.Value, , , xlWhole).Offset(0, 1).Value
  r.Offset(0, 2).Value = Range("MyList").Find(r.Value, , , xlWhole).Offset(0, 2).Value
End Sub

'ワークシートイベント 入力値変更
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo e
  If Target.Column = 3 _
    And Not Intersect(Range("MyData"), Target) Is Nothing _
    And Target.Value <> "" Then
    Call enterAmount(Target)
  End If
e:
End Sub

摘要列に入力があったときに発動するように、いくつか条件を設定。3列目、MyDataのRange内、入力ありの場合に動く。

ワークシートイベントで複数列のデータを消去したときなどエラーが発生するので
On Error GoTo〜
で回避。

入金、出金を判定しないでそれぞれ設定された金額を入力するようにした。このほうがシンプルで分かりやすい?でしょ。

なんてやってみたが、このファイルをこれからどれほど使うかな?

先日ちょっとした実験をVBAでやっていて、For~Nextのネストで内側からExit Forしても外側のFor~Nextから脱出できないことに気づいた。これまでそんな(ネストから脱出する)コードを書いたことが無かったのだろうが、それにしても知らなかった。

で、GoToを使うのが普通かと思ったけど、調べたら「フラグを立てれば」というのがあって具体例が紹介されていなかったのもあってそれをやってみた。

ネスト脱出部分のみ
Dim flag as Boolean
flag = False
For  ~~~
    For ~~~
        ~~~~
        If ~~~~ Then
           flag = True
           Exit For
        End If
    Next
    If flag Then Exit For
Next

「フラグを立てる」はこれでいい(ネストからの脱出の場合)と思うんだけど。

偉そうに記事書いてきましたがレベルがバレますね。記事を書きながら勉強させていただきます。

昨日あたりVBAの記事にけっこうアクセスがあった。ま、どこまで読まれたか分からないので「チッ!」と瞬時に離れていった人ばかりかもしれないがアクセスはあった。

夏休みなので子供がいろいろ調べているのか?なんて考えたけど、子供が勉強するにはScratchを使って学習するパターンが多いようで、VBAを調べているのはやはりオッサン?

でも、VBAはパソコンにExcelが入っていればすぐに始められるので手軽だし、後々Excelも使いこなせるようになると一挙両得だよ!なんて、どこかの回し者のように推薦します。


プログラミングって何だよ?

ってそもそも考えると思うんだけど、例えば1から10の合計をプログラムで取得しなさいと言われて、何も知らないと

合計 = 1+2+3+4・・・

などと考える(数学的にいろんな方法がある)けど、VBAというプログラミング言語を使ってプログラムにすると

For 数 = 1 To 10
    合計 = 合計 + 数
Next 

のようになります。下が難しくややこしいように思われるけど、これが10000まで、1億までなんてなっても下のは「10」のところを書き換えるだけなので断然ラクですよね。

上のように1億まで書いたプログラムでもコンピュータはあっという間に計算してしまいますが、人間の労力として上のように1億まで数字を書き続けようと思う人はいないでしょう。

プログラミングを勉強するとはこんなことなんです(本当?)。イヤ、正確には違う説明になると思うけど、とっかかりとしてはなんとなく分かってもらえるのではないかと。

実は下のように書いても1億までとか桁が大きくなると計算できない場合があります。そんなこともしょっちゅう発生するので根気も必要で、それを乗り越えてプログラムを完成させたときは一回り大きな人間に成長できると・・・。個人の感想です。

もし夏休み中にプログラミングを勉強しようと思っている人はVBAにも挑戦してみてください。簡単なゲームなんかも作れますよ(どうやって勉強しようがおおきなお世話と思いますが、VBA記事のネタが無くて)。

VBAの記事を書こうと、Rnd関数を使った内容にしようと思って調べていてふと思った。

Rnd関数は0から1未満の間の値を返し、それを加工して利用したい範囲の乱数を得るんだけど「本当にゼロの値が返されるの?」と。ま、散々使ってきてVBAの仕様を本当か?と疑うわけでないが確かめてみたくて。

通常は加工のしかたである程度ゼロが発生するんだけど、生身の値(0〜1未満)でゼロは発生するのか?コードはこんなふうにしてみました。

Sub test()
  Dim cnt As Long
  Dim v As Double
  
  Randomize
  Do
    v = Rnd
    cnt = cnt + 1
  Loop Until v = 0
  
  cells(1, 1).Value = cnt
End Sub

最初はRnd関数で取得した値をセルに入力して、その都度ゼロかどうか判定しゼロなら止めるコードを書いたけど大量のセルに入力するのは時間がかかり(シートやセルなどオブジェクトを操作する処理は時間がかかる)、500万ほどのセルに入力する処理でも数分かかってしかもゼロが出現することはなかった。

そこではFor〜Nextで100万行入力したら次の列へ移動して繰り返すようにしていたがとにかく時間がかかってゼロが現れない。

ので、セルに入力しないでDo〜Loop Untilで条件に合致するまで繰り返しRnd関数で値を取得し、何回目でゼロが出現したかセルA1に表示するようにした。セル入力が無いので「え〜〜〜っと」くらいで終了し、かかる時間はぜんぜん違う。

何回かやってみると100万回未満で出現する場合もあるが1千数百万回という場合もけっこうある。そうか、やっぱりちゃんとゼロが出るようになっているんだな。ヨカッタ、ヨカッタ・・・



暑い日が続くのでこんなことをして紛らす?

VBAを始めたときは当然わからないことばかりです。そのために本を買ったり借りたり、誰かに教えてもらったりネットで調べることが多いと思いますが、Excelのヘルプでもかなり勉強になると思います。

私の記事で「VBA基本」なんてカテゴリで記事を書いていますが、説明はとてもいい加減(正確に要点を説明できているか、という点で)。なのでVBAを書いていて困ったときは、手元の「ヘルプ」を利用しましょう。

ヘルプの利用法はいくつかありますが、簡単なのは「F1キー」で呼び出す方法。

たとえば私の変数の記事でろくに説明していない「Dim」という変数宣言時のワードですが、これについて調べたいとき、コード中でDimにカーソルを移動します。

2018-07-05
分かりづらいですがDimの「D」と「i」の間にカーソルがあります。この状態でF1キーを押します。

すると
help02
このようにExcelのヘルプから「Dim」についての説明が表示されます。関連項目や使用例もあり、とても助かることがあります。

これを見れば私なんかが知ったかぶりで説明する必要は無いわけで、どうして怠けたかがわかってもらえるかと・・・

って、これまでの私の知識でも知らないことがDimについてたくさん書かれているな。隅から隅まで勉強する必要は無い(と思う)けど、わからないことは調べる。上達にはそれが必要と思いますのでヘルプも使ってください。

Sub test()

 Debug.Print ThisWorkbook.Name
 Debug.Print ThisWorkbook.Path
 Debug.Print ThisWorkbook.FullName

 Debug.Print ActiveWorkbook.Name
 Debug.Print ActiveWorkbook.Path
 Debug.Print ActiveWorkbook.FullName
 
 Debug.Print ActiveSheet.Name

End Sub


実務的に必要な場面はあまりないでしょうが、自らのファイル名やアドレスを確認したいことがあります・・・。そうか?

ま、こんなこともできるということで、上のコードを記事にする。とりあえずのVBAネタです。

イミディエイトウインドウに書き込まれるのでVBEでイミディエイトウインドウを表示してから実行してくださいね。

先日「プログラムを停止して」云々という記事を書いたけど、それで具体的にどうやってコードの進み具合を確認するのか説明しなかったので続編で。


プログラムが思うように処理できない原因として変数に格納された値が想定外、ということがよくあります。そこで、変数などの値を確認できる「ローカルウィンドウ」という機能がVBE(VisualBasic Editor)にはあります。

VBEの表示メニューからローカルウィンドウをクリック。
ローカルウィンドウ0

すると下図のようなウィンドウが追加されます。
ローカルウィンドウ00
VBE下部の「ローカル」という見出しになっているのがローカルウィンドウです。いまは何もありませんがブレークポイント(については上のリンク先記事を参照)を設定したコードを実行してみます。

ローカルウィンドウ1
ブレークポイントは上図のように複数箇所に設定することもできます。F5キーを押します。

最初のブレークポイントで停止して、ローカルウィンドウに2行表示されました。
ローカルウィンドウ2
1行目の「Module5」は実行しているコードがModule5というモジュールオブジェクトに書かれているからで、ローカルウィンドウはオブジェクトの内容も表示するのですがここでは関係ない(というか説明するのがヘビー)ので説明省略します。興味あれば左の+ボタンをクリックしてどんなことになっているか見てみてください。

2行目の「i」はコードで使われている「変数i」のことで型は宣言どおりIntegerとなっています。そして値は0(ゼロ)。

ここからはF8キーを押してステップ(1行づつ)実行します。
ローカルウィンドウ3
F8キーを2回押すと2行進んでNextまで行きセルに値が入力されました。

さらにF8キーを押していくとローカルウィンドウのiの値が変わります。下図で変数iの値が1になったことが確認できます。
ローカルウィンドウ4

F8キーを押すたびに1行づつ処理が進み(For文の中を繰り返し)、変数iの値が1ずつ増えてそれを使った文字列がセルに入力されます。

ローカルウィンドウ5



2つ目のブレークポイントまで一気に進みたい場合はF5キーを押します。
ローカルウィンドウ6

メッセージボックスを表示する前の、2つ目のブレークポイントまで進みFor〜Nextによるセルへの入力は終了しました。

もう一度F5キーを押すと。
ローカルウィンドウ7
メッセージボックスが表示されます。ここでOKボタンを押してメッセージボックスを閉じるまでプログラムは終了せず(End Subまで到達していない)、ExcelやVBEを操作することができませんので忘れないように。

ローカルウィンドウ8
プログラムが終了するとローカルウィンドウは元のように空白になります。


画像が多くて説明がまとまっていないのですが、ローカルウィンドウのiの最初の値が0(ゼロ)になっているのは「For i = 0 to 9」としているからでなく、宣言時の初期化によるものです。カウンタ変数(ここではi)の値を5から始まるようにする(For i = 5 to 9)など実験して確認してください。

また、ステップ実行でFor文の中でカウンタ変数の値の変わるタイミングとか、For文を抜けた時にカウンタ変数の値がどうなっているかなども確認しておくともしものトラブルに気づくことがあるかも?

いずれにしてもプログラムの経過を確認できるのは大事な「技術?」でもあるので、VBEのこんな機能も使いこなせるとよいです。


あれ、「10つ目のセル」?コードより日本語がおかしかった。

↑このページのトップヘ