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

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

ぷろぐらまもどき

気づくとVBA関連の記事を半年近く書いていない。ブログの訪問者は殆どがVBAの記事を読む人だけど役に立っているのでしょうか?

カテゴリ(「VBA基本」)と関係ない記事だけどそんな状態なので記録として書いておく。



なんか最近Excelで新しいファイルを作ったりすることもなくて。


あ、そうだ、因みに書いたコードの途中でプログラムを停止させたい、なんて場合には停止させたい行で「F9キー」を押してブレークポイントを設定するとそこで停止できます。

停止させたい行にカーソルがある状態でF9キーを押すと下図のように色付きに反転します。ブレークポイントを解除したい場合はもう一度その行でF9キーを押します(下図の丸印の部分を左クリックすることでも設定、解除できます)。
ブレークポイント1

これでプログラムを実行すると

ブレークポイント2
ブレークポイントの前まで進んで停止します。この時点でセルA3には値が入力されていませんね。

この続きを実行したい場合はF8キーでステップ実行したり、F5キーで一気に最後まで実行することができます。
ブレークポイント3


想定通りに動かないようなコードはこうしてどこがおかしいのか、調べることができるのもVBAを使ううえでとても大切なことです。

このサンプルはおかしなところを探すまでしていませんが、プログラムを停止させることはどのようにコードの処理が進んでいるかを確認するのに有効ですので、こんなことも知っておくとよいです。

以前VBAでセルにハイパーリンクを設定することをやったが、こんなこともできるようで。

Addressにフォルダを設定。
ActiveSheet.Hyperlinks.Add Anchor:=Range("A4"), _
    Address:="C:\Users\XXXX\Desktop\test folder", _
    SubAddress:="Sheet1!A1", TextToDisplay:="ハイパーリンク"

ワークシートのハイパーリンクイベントに
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
  MsgBox Target.Address
End Sub
とすると

ハイパーリンク03

指定したフォルダが開く。イベントプロシージャはAddressの値を確認しただけ。

また、Addressにフォルダ内のファイルを設定すると
ActiveSheet.Hyperlinks.Add Anchor:=Range("A4"), _
    Address:="C:\Users\XXXX\Desktop\test folder\test file.txt", _
    SubAddress:="Sheet1!A1", TextToDisplay:="ハイパーリンク"


ハイパーリンク04

ファイルも開いてくれる。

うまく利用すれば便利なツールを作れるかもしれないね。

ま、ファイルやフォルダは何気なく移動したりすることもあるので、そのへんの対策もするとなると大変だけど。

ActiveSheet.Hyperlinks.Add Anchor:=Range("A4"), Address:="", SubAddress:= _
      "Sheet1!A1", TextToDisplay:="ハイパーリンク"

ハイパーリンク02

AddメソッドのAnchorに指定したセル「A4」に、SubAddressで設定した「Sheet1のセルA1」にリンクするハイパーリンクを追加して、Anchorに表示する文字列は「ハイパーリンク」。

Addressは外部のファイルやネットアドレスを指定する時に使う?とりあえずここでは試していないのでわからない。

マクロで追加したシートにジャンプしたい時に、そのハイパーリンクもマクロで追加できるように、って感じでやってみた。

ハイパーリンクでセル、シートの移動のほかマクロも実行できるようだがその場合は
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
  
End Sub
のようにワークシートのハイパーリンクイベント(と言うか分からないが)から実行する。

ハイパーリンクを設定するとフォントサイズや色、下線つきなど勝手にフォント設定されるのだが、これは何かの設定で初期化できるのか?

以前「できない」と書いたVBAでのNikonRawファイル(NEFファイル)からの撮影日時取得。

Microsoftが「カメラ コーデック パック」というのを提供しており、それをダウンロード、インストールしたら取得できるようになった。

テストしたコードは

Sub ShellDateTime()
  Dim objShell As Object
  Dim objFol As Object
  Dim objFolItem As Object
  Dim folPath As Variant
  Dim tarFile As String
  Dim info As String
    
  folPath = "C:\Users\XXXX\XXXX\"
  tarFile = "XXXX.NEF"  'ファイル名だけ
  Set objShell = _
    CreateObject("Shell.Application")
  Set objFol = _
    objShell.Namespace(folPath)  'tarFileのフォルダパス
  Set objFolItem = _
    objFol.ParseName(tarFile)
 
  Dim i As Integer
'どんな情報があるか
  For i = 0 To 40
    info = info & _
      objFol.GetDetailsOf(vbNull, i) & _
      " : " & objFol.GetDetailsOf(objFolItem, i) & _
      vbNewLine
  Next
  MsgBox info
    
  info = ""
'指定した情報を取得
  Dim itm As Variant
  itm = Array("撮影日時", "タイトル")
  For i = 0 To 40
    If objFol.GetDetailsOf(vbNull, i) = itm(0) Then
      info = info & itm(0) & " = " & _
        objFol.GetDetailsOf(objFolItem, i) & vbNewLine
    ElseIf objFol.GetDetailsOf(vbNull, i) = itm(1) Then
      info = info & itm(1) & " = " & _
        objFol.GetDetailsOf(objFolItem, i) & vbNewLine
    End If
  Next
  MsgBox info
  
  Set objFolItem = Nothing
  Set objFol = Nothing
  Set objShell = Nothing
End Sub

で、こんな感じ。

BlogPaint


コードは「VBAでExif?撮影日時?取得」をほんの少し変えた(変数folPathの型をStringからVariantにしてある)もの。「WIA.ImageFile」ではやはり取得できなかった。

発端のコメントをいただいた方はその後何も言ってこないので自分でまだ調べているかもしれないが、これ(カメラコーデックパックのインストール)でできるんじゃないかな?

自分の環境(64bit Win7&Excel2010)でしか実験していないしテストするNEFファイルが少ないので保証はできないけど。

あら、Windows10にはインストールできない?Win10には標準で同様の機能が備わっている?じゃぁ、解決にはならないのかな・・・

/*2017/06/05追記
仮想マシンのWin10 Insider_Preview 32bit ではコーデックパックをインストールしなくても上のコードで取得できた。
*/



/*2018/02/28追記
この記事にコメントをいただいたので、それに関する追記を

このコードで「秒まで取得できれば」ということでしたが、現状の私には上のコードから撮影日時の秒まで取得する知識がありません。

申し訳ないですが、この記事を書くために調べていて見つけたコードが参考になるかもしれないのでご紹介します。

コードはリンク先にあります。

因みに私の環境(Win7 64bit Excel2010)で試すと以下のようになりました。
NEF-TEST

全く別のコードで恐縮ですが、こちらで秒まで取得できるのではと思います。
期待する結果になること祈っております。
*/

VBAの記事に要望を込めたコメント(初めてでないだろうか?)を頂き、面白そうだなと思い調べてみた。

が、ちょっといまの私には時間的に集中する余裕がなく、とりあえず参考になれば、程度です。


NikonのRawファイル(.NEF)から撮影日時を取得したいのだけど、データがメーカー独自の仕様になっていてこれまで記事にしたWIA.ImageFileのライブラリやShell.Applicationを使って取得することはできないようです(私はWin7&Excel2010 64bitで、環境によるかも)。

ということでバイナリを見てみた。すると、
BlogPaint


Exifと同様のタグらしきものはあった。IFD(ImageFileDirectory)の仕様通りなら「90.03」(16進表記 以下同)がDateTimeOriginalのタグで次の2バイトはデータ型、次の4バイトはデータサイズ、次の4バイトはデータまたはオフセット値ということになる。

ということで、「00.00.02.44」は撮影日時データを格納した位置へのオフセット値ではないか?(データサイズが「00.00.00.14」なので)と。

BlogPaint


ファイルの先頭から「244」バイト先に行くと撮影日時らしき文字データがありました。ということはビンゴ?
/*追記 上の撮影日時データはたまたまそこにあるもので、Exifデータの並びではない可能性あるようです。周りのほかのデータがどうもおかしいので*/

因みにこのNEFファイルをJpgにコンバートしたと思われるファイルのExifを調べたら(VBAで撮影日時 for Win7?
NEF-02
一部だけどこんな感じ。

これを元にNEFファイルのどこに、どのように埋めこれているか地道に調べれば・・・

しか無いのかな?バイナリだと大変だな。だれか挑戦した人がすでにいるかも?



です。少しでも参考になれば幸いですが。

Sub ElbowAttack()
  Dim i As Integer
  Dim pizza As String
  
  pizza = ""
  For i = 0 To 9
    pizza = pizza & "ピザ!" & vbNewLine
  Next
  
  MsgBox pizza
End Sub

サンプルFor


わたしが子供の頃にこんな遊び?は無かったと思いますが、VBAの繰り返し構文の例として使ってみました。

何か繰り返し実行したい処理や計算を行う時のVBAの構文に「For〜Next」があります。

上の例で繰り返しているのはForとNextの間に書かれた
pizza = pizza & "ピザ!" & vbNewLine
の部分です。変数「pizza」に10回「ピザ!」という文字列を格納しています。

格納する前に
pizza = ""
として変数の中身を空文字列("")で初期化しています。VBAでは変数を宣言した段階で文字列は空文字列で初期化される(内部で処理される)のですが、変数を宣言してからあちこちで使用した後、あらためて値を格納する場合もあるので、このように初期化することを覚えておくのは大切です。

で、
pizza = pizza & "ピザ!"
として文字列を格納するのですが、ちょっと意味が分かりづらいですね。先頭の「pizza =」はイコールの右辺をpizzaに格納することを意味します。そして右辺は「pizza & "ピザ!"」(& vbNewLineはメッセージボックスで表示用の改行文字なので説明省略)となっています。

pizzaに格納するpizza?ここでは左辺のpizzaに右辺の演算の結果が格納されると考えてください。そして右辺の演算はpizzaに格納されている文字列に「ピザ!」を連結(「&」は文字列連結の演算子)したものになります。繰り返しが始まる前に初期化されているので最初の左辺は
"" & "ピザ!"(""が変数pizaaの中身)
となり、pizzaには"ピザ!"が格納されます。そして2回めは最初に格納された文字列にまたピザ!を連結して
"ピザ!" & "ピザ!"(ひとつめの"ピザ!"が変数pizaaの中身)
が左辺のpizzaに。3回目は
"ピザ!ピザ!" & "ピザ!("ピザ!ピザ!"が変数pizaaの中身)
と元のpizzaの文字列に"ピザ!"を付け加えたものが左辺のpizzaに格納されるということです。説明が下手で分かりづらいかな?

これを10回繰り返してから表示したのがメッセージボックスの画像です。



For i = 0 To 9
の「i」はカウンタ変数と呼ばれ、決められた回数を繰り返すためのカウンタの値を格納します(カウンタ変数「i」はInteger型で宣言しています。以前変数の型の説明で「整数型はLongを使えばよい」と書きましたが、それは初心者のうちは、ということでご容赦ください)。

例では0〜9を格納し10回繰り返すことになります。慣習的に「i」が使われることが多いですがもちろんほかの変数名でも大丈夫です。

ゼロから始まるのは違和感がある、という場合は
For i = 1 to 10
とすることもできます。

また、変数に格納した値だけ繰り返すことも可能
For i = 0 To count 
変数「count」の値まで繰り返す。

セルの指定にカウンタ変数を使用したいような場合は
For iRow = 6 to 15
  'セルA6~A15に値を入力
  Cells(iRow, 1).Value = iRow & "行目です。"
Next
のようにする。

カウンタ変数は指定無ければ1刻みでカウントアップします。2つづつカウントアップしたい場合は
For i = 0 to 50 step 2
とします。カウントダウンしたい場合は
For i = 100 to 0 step -1
とします。小数でも刻めるようですが不具合発生の可能性もあるようなので、その場合は充分に下調べをしてください。

繰り返し処理の途中、検索するセルが見つかったなどそれ以降の繰り返しが不要となる場合は
For i = 1 to 10
  if Cells(i, 1).Value = "ビンゴ" then
    Exit For '繰り返し終了
  End If
Next
のように、Exit ForとすることでFor文から抜け出すことができます。

Sub ifSample()
  Dim res As VbMsgBoxResult
  Dim msg As String
  
  res = MsgBox("あなたはVBAを使いたいですか?", vbQuestion + vbYesNoCancel, "?への道")
  
  If res = vbYes Then
    msg = "Excelの世界が変わりますよ。"
  ElseIf res = vbNo Then
    msg = "Excelの基本機能で充分助かりますよね。"
  Else
    msg = "失礼しました。"
  End If
  
  Range("A1").Value = msg
 
End Sub



マクロ記録に登場することはありませんが、自分でVBAのコードを書くようになると条件によって処理を分けたいことが頻繁にあります。

そんな時に使うのが上のようなコード。メッセージボックスで押したボタンによってセルに入力する値(文字列)を変えています。

ここではメッセージボックスのYesボタン、Noボタン、それ以外(Cancelまたは閉じるボタン)を押した場合で処理を分けていますが、Yesのときだけ処理する(ほかのボタンが押されたときは何もしない)場合は

If res = vbYes then
  ~~~~
End If

のように書きます。

特定の条件のみ処理する場合はElseIf〜Then、Elseは不要、幾つかの条件に対応する場合はIf 〜Thenのあと必要な条件分ElseIf〜Thenを繰り返し、条件に合致しない場合の処理を用意する場合はElseに処理を書く、となります。

If 条件A Then
  実行する処理
(ElseIf 条件B Then
  実行する処理
ElseIf 条件C Then
  実行する処理
ElseIf 条件・・・ 必要な条件に応じてElseIf〜Thenを繰り返す)
(Else
  一致する条件が無かった場合に必要な処理)
End If

ワークシート関数のIf関数と違うので戸惑うかもしれませんが、VBAを使う上で必須の構文ですのでIf、ElseIf、Elseを使って条件によって処理を分けられるようになりましょう。

で、If構文そのものは難しくないのですが条件式の書き方は勉強と経験が必要です。条件式には真偽(TrueかFalse)を返す式を書かなくてはなりません。


変数A >= 100 変数Aが100以上である
変数B = "文字列" 変数Bは"文字列"である

のように数学の等号不等号をつかうのは分かりやすいですね。ただ「ノットイコール」を条件にしたい場合は「<>」を使います。

変数C <> "" 変数Cは""(空白文字列)ではない

また複数の条件を設定したい場合は 
 
変数D > 65 And 変数D<=100 変数Dは65超、かつ100以下である
変数E <= 0 Or 変数F = ”” 変数Eがゼロ以下、または変数Fが空っぽ(空白文字列)である

のようにできます。

当然VBAの関数を条件に使うこともできます

Instr(変数G, "検索文字列") > 0 変数Gに"検索文字列"が含まれている(とInStr関数が1以上の値を返す)

また、真偽値を返すVBAの関数はそのまま使えます。

IsEmpty(Range("A1")) セルA1は空である(何も入力されていない)

IsEmpty()関数が「True」か「False」を返すので「IsEmpty() = True」のように書かなくて大丈夫です。またFalseを条件にしたい場合は

Not IsEmpty(ActiveCell) アクティブセルは空でない(何か入力されている)

のように「Not」を使います。


いくつか簡単な例を紹介しましたが、実際には複雑な条件で苦労することが多いです。なので経験や、自分で関数を作ったりワークシート関数を利用したり工夫も大切です。
※上の例はIf 〜 Then、ElseIf 〜 Thenの「〜」の部分になります。


If構文では、最初のIf構文の中にさらにIf構文を書く場合があります。

If 条件式 Then
    If 条件式 Then
      実行する処理
    ElseIf 条件式 Then
      実行する処理
    End If
Else
    実行する処理
End If

更に二つ目のIf構文の中でもIf構文なんて場合もあります。こういうのをネストと言うのですがあまり深いネストは良いコードではない、と評価されることが多いです。あまりに複雑な条件になると見ただけでどんな評価・判定が行われているか自分でも分からなくなりますからね。

そんなこともあるので、前回VBA基本ネタにしたインデントをすることが大事になりますので参考までに(VBAでもインデント)。

たまにこんなコードを見かけます。
Sub Test()
Dim goukaku As Integer, iRow As Integer
Dim kekka As Integer
goukaku = 60
For iRow = 1 to 10
kekka = Cells(iRow, 1).Value
if kekka >= goukaku Then
Cells(iRow, 2).Value = "合格"
Else kekka < goukaku Then
Cells(iRow, 2).Value = "不合格"
End If
Next
End Sub

私も勉強始めた時はこんなコード書いていた気がしますが、いまはこのようには書きません。「このようには」というのは、各行の開始位置がどの行も同じコード(言語によってこれが普通、というのもありますが。内容はサンプルなのでここでは関係なし)。

これは処理として単純だし短いので何をしているか分かりますが、これが数十行にもなり変数もたくさん登場していろんな処理があるとどうなるでしょう?

一発完成で改良、手直し必要なければそれで終了ですが、処理を追加したい、もっと早く処理が終わるように改良したい、なんて場合にどこを書き直すか見つけるのが大変かも、と思いません?

ま、上のような書き方を全否定するつもりはありませんが、勢いで一気に書けたコードでも行数が多いほど後のことを考えて整理しておくのも重要かと思います。


Sub Test()
    Dim goukaku As Integer, iRow As Integer
    Dim kekka As Integer

    goukaku = 60

    For iRow = 1 to 10
        kekka = Cells(iRow, 1).Value
        if kekka >= goukaku Then
           Cells(iRow, 2).Value = "合格"
        ElseIf kekka < goukaku Then
           Cells(iRow, 2).Value = "不合格"
        End If
    Next
 
End Sub

こうするのがベストというわけではありませんが、行の先頭をインデント(字下げ)したり、空白行で処理部分を区切ってみました。パット見わからないような処理はコメントしておくと良いです。

インデントは行ごとに開始位置を揃えることでIf〜End IfやFor〜Next、Do〜Loopなどをブロック化して見やすくすることができます。また、上のようにFor構文の中If構文やネストしたときもインデントすることによって見やすくなります。

インデントには半角スペースが使われ、VBEメニューの「ツール」ー「オプション」で「編集」タブの「タブ間隔」を設定してTABキーを押すと、その値の半角スペースが挿入されます。

同じ「編集」タブの「自動インデント」をチェックすると、インデントされた行から改行すると同じインデント位置で改行します。そこから更にインデントしたい場合はまたTABキーを押します。


そんなの面倒くせぇ、と思われるかもしれませんが見やすいのは間違いないし上に書いたように改良し続けるのがコードの宿命でもあるので、どこで何をしているか分かりやすくしておくのは自分のためでもあります。

文字ばかりで分かりづらいかもしれませんが、もしこれまでそんなことしていなければコードを書きながら行の先頭でTABキーを押すとどうなるか。長いコードになるほどインデントでブロック化するとやりたいことも整理できてとてもいいと思いますよ。

変数の名前の付け方に決まりがあることを前のVBAカテゴリの記事で書きましたがあらためて(あまりに前回の内容が薄っぺらなのと、とりあえずのVBAネタ)。


使えない変数名にするとエラーが発生しますが、簡単なルールなのでそれさえ頭に入れば変数名なんてどうってことないです。

long という変数を宣言しようとしましたがすでに型を表すワード(予約キーワード。VBAで意味、役割の決まっているワード)として使われているので使えません。Long型の変数として宣言したからではなく、文字列型や小数型でもlongという変数名は使えません。
hensu4
大文字小文字の使い分けはVBAでは意味ない(※注 参照)のですが、読みやすいので自分しか見ないコードでもルールを決めて使い分けるのはいいと思います(oldValue、NewText)。
※注 VBAではHensuとhensuは同じものとして扱います。そもそもVBEが大文字小文字を自動で変えてしまいます。Hensuで宣言するとコード中でhensuと打ってもHensuになります。ということで変数名にはLonGもlONGもlOnGも使えません。


これは変数名の頭に数字がついているので使えません(理由は深く考えない)。すべて数字の変数名もダメです。
hensu5
VBEではコードにエラーがある行を赤に(デフォルト設定の場合)変換してこの様にメッセージが出ます。

「コンパイルエラー:」?

「修正候補:識別子」?

コンパイルエラーはとりあえず大雑把に「エラー」と捉えておきます。修正必要な箇所として識別子が挙げられていて、ここでは「long」とした変数のことになります。

ヘルプボタンを押しても大した情報は無かったような。OKボタンを押してコードを修正しましょう。

以下のような変数名は使えます。
 hensu7

ま、使えるとしても「long」という禁止ワードが含まれるような変数名はできるだけ避けるべきと思います(紛らわしい、間違いのもとになる気がする)。

状況によって「a」「i」「t」のような簡単な変数名を使う場合もありますが、基本はどんな値を格納しているか分かるような変数名にすることです。下手すると数時間後に何の変数か分からない自分がいたりします。

ま、いろいろ考えたり参考書でどんな変数名にしているか見てまねすることでいいと思います(このブログのは行き当たりばったりで必ずしも参考にならないので悪しからず)。




役に立たない記事と思うがこのブログはVBA関連で来る人ばかりなのでこんなことも一応書いておく。

VBAで変数って・・・

変数のことを詳しく、わかりやすく説明するのは私じゃ無理。なので上っ面だけ。

基本は
Dim 変数名 As 変数のデータ型
と宣言してから使います。

実は宣言せずに変数を使うこともできますが、テストのような場合を除いてコードのなかで何度も登場するような変数は宣言して使う習慣をつけるべきです。

なぜか?

そのほうがいいと本に書いてあったから・・・。理由はともかく、宣言しないことのデメリットを防ぐためと理解してください。宣言したほうがいいんです。

変数名は日本語ほか半角のアルファベット、数字、記号など使えますが1文字目に数字は使えない、VBAですでに使われているワードは使えないなど制限があり、VBEが教えてくれるのでそれに従ってください。

変数は値を格納して使い

変数名 = "文字列" 注)データとしての文字列はダブルクォーテーションで囲む

変数名 = 数字 注)数字はそのままでよい

のように値を入力します。そして変数に入力する文字列、数字などで変数のデータ型が決まります。

VBAに用意されているデータ型はたくさんありますがよく使うのは
String型 文字列を格納
Long型 整数を格納
Single型 小数を格納
などです。

整数にはInteger型、小数にはDouble型もありますが上記の型を憶えておけばたいてい間にあうと思います。なんで整数や小数のデータ型が2つ?ほかのデータ型は?含め詳しく知りたい方はそんなサイトが無数にあると思うのでそちらで。

変数名だけ宣言して型を指定しない使い方

Dim 変数名

もできますがこれも「邪道」とまでは言わないまでも、できるだけデータ型を指定するほうが何をどうしたいのかイメージしやすいと思うしデータ型は指定するようにしましょう(省略した場合Variant型になります。万能の便利な型なんですが)。



hensu1

これを実行すると

hensu2

このようになります。

ここでは変数の値をセルに直接入力しただけなので変数のありがたみはありませんが、ほかの演算でも必要な値、ほかの値と入れ替わるまで変数に保存して使いたい値、条件によって変数の文字列を加工したりそのままで出力したい、など使いみちはとても多いです。

サンプルのような単純な使いみちだと苦労はありませんが、使い方によっていろんな値が入力されるのにコード上では同じ文字(変数名)でしかなく、実行してみたら思わぬ値を格納していてプログラムが暴走したり(いつまでも終了しない)、エラーが発生して先に進まないなど苦労も多いです。

ただ、変数を使えるようになることで「マクロ記録」から一段ステップアップができますので、まずは使える(変数を宣言して値を入れ、値を呼び出せる)ようになりましょう。

きっと便利なマクロを作れるようになりますよ。 

↑このページのトップヘ