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に新しいデータが登録されてしまいます。
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
このプログラムを実行すると、下記のように表示されます。
^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を使っていてハマッたポイントがあるよ、という場合は問い合わせフォームよりお知らせください。