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

2018年9月26日

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

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

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

「^」という文字が、「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を使っていてハマッたポイントがあるよ、という場合は問い合わせフォームよりお知らせください。