VBAに慣れてきた頃にハマるポイント

VBAも随分使いこなせるようになってきたと思って、プログラムを組んでみると、妙な仕様にハマッてしまうことがよくあります。

そこで、このページでは、私がVBAを使っていて、ハマッてしまった仕様について、忘れないように自分用にメモをしておきます。



VBA本体

「End」プロパティで最終行が取得できない

Endプロパティはを使う方法は、エクセルのワークシートで「Ctrl+矢印」を使う操作と等価です。
そのため、非表示行・列や、オートフィルタで表示されない行がある場合には、最終行を取得することができません。

UsedRangeで最終行が取得できない

UsedRangeプロパティは、下記のように正しく動作しない場合があります。

  • セルの表示形式の変更をしたセルなども対象に入ってしまう
  • いったんセルに値を入力したあとに、そのデータを消しても、UsedRangeが更新されないことがある

Microsoft Scripting Runtime

Dictionaryオブジェクトを使うときに、参照設定して使うことが多いライブラリです。

Dictonaryで「存在しないキー」を参照すると「キーが作成される」

プログラム例:

Sub DictionaryTest()
  Dim Dic As Dictionary
  Set Dic = New Dictionary
  
  Debug.Print Dic.Count  '0と表示される
  
  Dic("IndexA") = "A"
  Debug.Print Dic.Count  '1と表示される
  
  Debug.Print Dic("IndexB")  '読み込んでいるだけなのに キーIndexBが作られる!
  Debug.Print Dic.Count      '2と表示される!
End Sub

上記コードの最後のほうの2行(10行目~11行目)に注目してください。
Dicから存在しないキー「IndexB」のデータを読み出そうとしているだけなのに、新たに「IndexB」のキーが作られてしまうのです。

もっとも、実際のプログラムでは、存在しないキーでデータを読み出すことはしませんので、このケースが問題になることはほとんどありません。

注意しないといけないのは、デバッグ中のイミディエイトウィンドウでの入力です。

何気なく、

? Dic("IndexC")

と入れて中身を確認しようとすると、それだけで、Dicに新しいデータが登録されてしまいます。

Microsoft VBScript Regular Expressions 5.5

VBAで正規表現を使うためのライブラリなのですが、思わぬ落とし穴があります。

改行コードがCR+LF、Multilineモードのとき「^」「$」がCR、LF両方にマッチする

正規表現を使うときに「Multiline」というオプションがあり、このオプションを使うと、

  • 「^」→文字列の先頭だけでなく行頭にもマッチする
  • 「$」→文字列の末尾だけでなく行末にもマッチする

という説明がされています。

ところが、通常のWindows環境では、そのまま使うと正常に動作しません。

たとえば、行頭にマッチする「^」を例に挙げます。

Sub regexTest()
  Dim Reg As RegExp
  Set Reg = New RegExp
  
  Reg.MultiLine = True
  Reg.Global = True
  
  Reg.Pattern = "^"
  
  Dim StrCR As String
  StrCR = "1" & vbCr & "2" & vbCr & "3"
  Debug.Print "CRのみ"
  Debug.Print Reg.Replace(StrCR, "^")  ' → 期待通り
  
  Dim strLF As String
  strLF = "1" & vbLf & "2" & vbLf & "3"
  Debug.Print "LFのみ"
  Debug.Print Reg.Replace(strLF, "^")  ' → 期待通り
  
  Dim strCRLF As String
  strCRLF = "1" & vbCrLf & "2" & vbCrLf & "3"
  Debug.Print "CRLF"
  Debug.Print Reg.Replace(strCRLF, "^")  ' → ???

End Sub

このプログラムを実行すると、下記のように表示されます。

CRのみ
^1
^2
^3
LFのみ
^1
^2
^3
CRLF
^1
^
^2
^
^3

強調部分が、直感に反する部分です。

「^」という文字が、「CR」「LF」の両方(正確にいうと、それらの文字コードの直後)にマッチしてしまっています。

解決策としては、次のような方法をケースバイケースで選択しましょう。

  • Multilineモードを使わず、自力で行頭、行末にマッチする正規表現を書く
  • 改行ごとに配列に格納し、配列の要素ごとに正規表現を書く
  • いったん改行コードを「LF」に変換し、正規表現による処理後に「CRLF」に戻す

Microsoft XML, V6.0

VBAから直接インターネットにアクセスするときに使われるライブラリです。

XMLHTTP60を使って「302リダイレクト」ページにアクセスするとエラーになる

試しに、次のリンクをクリックしてみてください。
https://sem.excelspeedup.com/302redirecttest/302redirecttest.php

上記URLには「302リダイレクト」と呼ばれる仕組みを組み込んでいます。
そのため、上記リンクをクリックすると、URLが書き換わり「https://www.excelspeedup.com/」(=このブログのトップページ)の内容が表示されます。

さて、上記URLに対して次のプログラムでアクセスをしてみようと思います。

Sub xmlhttpTest()
  Dim Req As XMLHTTP60
  Set Req = New XMLHTTP60
  
  Req.Open "GET", "https://sem.excelspeedup.com/302redirecttest/302redirecttest.php"
  
  Req.send 'ここでエラー発生
  
  Debug.Print Req.responseText 
End Sub

ところが、このようにXMLHTTP60でアクセスしようとすると、
実行時エラー'-2147024891(80070005)':アクセスが拒否されました
というエラーが表示されてしまいます。

この不具合を解決するためには、別のライブラリ「Microsoft WinHTTP Services, version X.X」を参照設定したうえで、次のように「WinHttpRequest」オブジェクトを使いましょう。

Sub winhttpTest()
  Dim Req As WinHttpRequest
  Set Req = New WinHttpRequest
  
  Req.Open "GET", "https://sem.excelspeedup.com/302redirecttest/302redirecttest.php"
  
  Req.send '今度はエラーが出ない
  
  Debug.Print Req.responseText
End Sub

これで、正しくデータが取得できます。

ハマリポイント募集中!

もし、ほかにもVBAを使っていてハマッたポイントがあるよ、という場合は問い合わせフォームよりお知らせください。

エクセル基礎講座 「無料」動画マニュアル

「経理事務のためのエクセル基礎講座(初級編)」(動画マニュアル 総収録時間162分)を無料プレゼント中です!

このマニュアルで解説していることを一通り学べば、経理事務を行う上で最低限必要となる知識が得られます。

ご登録者の方には、合わせて、公認会計士が実体験を通して身に付けたエクセルを使う技をメールにてお伝えしていきます!

無料動画講座 登録フォーム

※ご登録頂いたメールアドレスに、エクセルを使いこなすための情報を配信するメールセミナー「エクセル倍速講座」も合わせて配信させていただきます。