『Excel VBA WEB連携術』

土屋 和人 (著)：Excel VBA WEB連携術―2007/2003対応 ，ソシム ，2009/5 ExcelでWebサービスを利用する方法が書かれた本。 ExcelでWebから情報を取得して加工やストックしておきたい人に 役立つと思います。

WEBから情報を取得する方法として

１．ハイパーリンクを使った方法
２．Webクエリを使った方法
３．RESTリクエストをして返ってきたXMLから情報を取得する方法
４．WebBrowserコントロールを使った方法

などが紹介されています。
中でも詳しく書かれているのが

２．Webクエリを使った方法
３．RESTリクエストをして返ってきたXMLから情報を取得する方法

です。

まず２．について。
Webクエリで
為替レートをExcelで取得する方法が紹介されています。

Webクエリを一定間隔で更新させ、
Webクエリが更新されたときにあるマクロを実行するための
クラスモジュールを使った実例が載っています。

３．については、
YahooやAmazonのWebサービスを利用した実例が載っています。

Amazonは2009年8月あたりから
署名付きのRESTしか受け付けなくなったので
この本に載っているままでは
AmazonのWebサービスは使えません。

Yahooのほうは、
Webサービスを使ったことがないので
現状がどうなっているかはわかりません。

しかし、楽天市場のWebサービスは
この本に載っている方法を応用すれば現時点で利用可能です。

そのほかにも署名のいらないWebサービスなら
この本に載っている方法で利用可能だと思います。

改訂版がでるなら
署名付きのAmazonのWebサービスを利用する方法を載せてほしいです。

その他は、
Webサービスを使うために必須の
URLエンコードをVBAでする方法も載ってます。

一応「Excel VBAの基礎知識」といった章がありますが、
全く知識がないところからこの本を見ると
XMLやらマクロの文字の羅列で
難しそうに見えると思います。

VBA初心者は別の入門書があったほうがいいと思います。
XMLはHTMLがわかる知識でOKでした。

VBAで何ができるか？具体例１

VBAに限らず、
プログラミングを始める一番の抵抗は
何ができるかわからない事だと思います。

そこでVBAで何ができるか？の具体例です。

Amazonの「価格.com」最安商品

上のリンク先のWebページは
VBAで価格.comから情報を取得して、
VBAでHTMLファイルを生成したものです。

な～んて
単なる宣伝でした。

右のメニューの 最安一覧を見る からも行けます。

Amazonのお買い物のお供に
ヨロシクどうぞー

簡易メール送信 ２

記事「 簡易メール送信 」内で、
メールの件名だけの簡易メールを送信する方法を紹介しました。

ケータイの料金プランをEプランに変更してみたので
とりあえず一ヶ月ガンガンメールを使っていこう！
ということで、
今度はパケットを気にしないでよいので
本文も付けて送信します。

といっても所詮簡易メールなので
前の方法に毛が生えたようなものです。

上述の記事の「メールのリンクを作るコード」で作るリンクを使用しますので、
まず「メールのリンクを作るコード」を実行してください。

Subject（件名）まではプロパティで指定できますが
本文のプロパティはないようなので
SendKeysを使って本文を入力します。

文字型の変数Honbunに
送信したい本文を入れます。

このHonbunをSendKeysでメールのウィンドウに送ります。

メールのリンクをクリックして立ち上がったメールのウィンドウは
一番最初は「宛先」のところにフォーカスがあり、
カーソルが点滅しています。

手動の場合大抵はマウスで本文のところをクリックして
カーソルを移動させます。

マウスを使わなくても、
TABキーを使えばカーソルを順番に移動させることができます。

この場合は
TABキーを3回押すと
メールの本文のところにカーソルが移動します。

TABキーを3回をSendKeysで送ることは

SendKeys "{TAB 3}"

でできます。

メールの本文のところにカーソルが移動したところで

SendKeys Honbun

で送信したい本文をメールのウィンドウに送ります。

他の部分は上記記事を参照してください。

コードはこちら

Sub macro100526a()
'SendMail2の使用例

    Call SendMail2("件名", "本文")
   
End Sub

Sub SendMail2(Subj As String, Honbun As String)
'Subjをメールの件名で
'Honbunをメールの本文で送る

    With Sheets("mail").Range("A1").Hyperlinks(1)
        .EmailSubject = Subj
        .Follow NewWindow:=False, AddHistory:=True
        Application.Wait Time:=Now + TimeValue("00:00:05")
        SendKeys "{TAB 3}", True
        SendKeys Honbun, True
        SendKeys "%{s}", True
    End With

End Sub

実行すると出てくるウィンドウ

エラーが発生した時
マクロの実行が終了した時などに
お知らせメールが便利です。

マクロ実行中に一定時間ごとにブックを保存

マクロ実行中に一定時間ごとにブックを保存

時間の掛かるマクロを実行中は
ブックを定期的に
キリのいいところで保存していきたいです。

途中で強制終了して
また初めからなんてことがあります。

されど、ブックの保存は時間が掛かるし、
経験的にブックの開閉や保存のときには
強制終了が起こる確率が高いので
あまり頻繁にはブックを保存したくありません。

そこで自分がちょうど良いと思える頻度で
ブックを保存するプロシージャを作りました。

一定時間ごとにブックを保存する方法は
幾通りもあると思います。

ここでは、
現在の日時とブックの最終保存日時を比較して
差が○○分以上のとき
ブックを保存します。

時間の掛かるマクロの中に組み込んで
使ってください。

コードはこちら

Sub SaveBy(M As Integer)
'M分ごとに保存する
'ワークブックの最終保存日時とNowを比較して
'差がM以上なら保存する

    If (Now() - FileDateTime(Workbooks(wb).Path)) * 24 * 60 > M Then
        ActiveWorkbook.Save
    End If
   
End Sub
Sub macro100525b()
'SaveBy使用例

    '時間の掛かるマクロの途中で
    Call SaveBy(20)
   
End Sub

SaveBy(20)で
ブックを２０分毎に保存します。

SaveBy(30)で
ブックを３０分毎に保存します。

ある文字列を含むかで条件分岐

ある文字列が含まれているかを調べるのに
文字列操作系InStr関数を使います。

InStr関数は、
指定した文字列の中に探したい文字列がある場合は、
探したい文字列の先頭の文字が
最初からの何文字目かを返します。

探したい文字列がない場合は0を返します。

つまり、探したい文字列がある場合は0以外なので
これを使って条件分岐します。

簡単な使い方：

InStr(string1, string2)

string2に探したい文字列を、
string１に探したい文字列を検索したい文字列を指定します。

詳細はVBAヘルプにあります。

コードはこちら

Sub macro100524a()
'ある文字列が含まれているかで条件分岐
    Dim Str1 As String, Str2 As String
    Str1 = "やむえむのExcel VBAメモ"
    Str2 = "VBA"
   
    If InStr(Str1, Str2) <> 0 Then
        '文字列が含まれていた場合の処理をいれる
        MsgBox "「" & Str1 & "」 に 「" & Str2 & "」 は含まれます。"
    End If
   
End Sub

上記の場合は
InStr(Str1, Str2) = 12
Str1の12文字目にStr2の1文字目があります。

逆に、
ある文字列が含まれないときに処理したい場合は、
InStr(string1, string2) = 0
を条件にします。

セル内の改行を削除する

セル内の改行を削除するには、
記事「 ダブルクォーテーションをシングルクォーテーションに変換する 」
内で、
ダブルクォーテーションをシングルクォーテーションに変換したときにも使った
Replace関数を使います。

コードはこちら

Sub macro100523a()
'セル内の改行を削除する

    ActiveSheet.UsedRange.Replace _
        What:=Chr(10), Replacement:=""

End Sub

実行前：

実行後：

セル内の改行を削除すると
セルの高さと幅を調整したくなります。

そこで
下のコードを実行してみます。

Sub macro100523b()
'セルの高さ幅を自動調整

    With ActiveSheet.UsedRange
        .Rows.AutoFit
        .Columns.AutoFit
    End With
   
End Sub

実行後：

変化なしです。

改行を削除してから
セルの高さ幅を自動調整しても調整されません。

そこでいったんセルの高さ幅を最大にしてから
調整します。

下のコードはセル内の改行を削除した後、
セルの高さと幅を自動調整します。

Sub macro100523c()
'セル内の改行を削除してから
'セルの高さ幅を自動調整する

    With ActiveSheet.UsedRange
        .Replace What:=Chr(10), Replacement:=""
        .Rows.RowHeight = 409
        .Columns.ColumnWidth = 255
        .Rows.AutoFit
        .Columns.AutoFit
    End With
   
End Sub

実行後：

今度はきちんと調整されました。

オブジェクトがNothingか判別する

定義したオブジェクト型の変数に何も代入していない状態では
ウォッチで調べると変数の値はNothingになります。

変数objがNothingかどうかを判別するために
If文を使って条件判断します。

そこで次のコードを実行してみます。

Sub macro20100513a()
'オブジェクトがNothingか判別する
'エラーになるコード
   
    Dim obj As Object
   
    If obj = "Nothing" Then
        MsgBox ("変数objはNothingです。")
    End If

End Sub

実行結果：実行時エラー'438'になります。

If文の条件式　obj = "Nothing"は
Object型の変数objと文字列Nothingを比較しようとして
エラーになります。

次のコードはエラーが起こるでしょうか？
実行してみます。

Sub macro20100513b()
'オブジェクトがNothingか判別する
'エラーになるコード
   
    Dim obj As Object
   
    If obj = Nothing Then
        MsgBox ("変数objはNothingです。")
    End If

End Sub

実行結果：エラーになります。

Nothingはオブジェクトです。
=演算子ではオブジェクト同士の比較はできません。
2つのオブジェクトの比較は
Is演算子を使用します。

先ほどのコードは次のようにすると
エラーが発生しないで実行できます。

Sub macro20100513c()
'オブジェクトがNothingか判別する
   
    Dim obj As Object
   
    If obj Is Nothing Then
        MsgBox ("変数objはNothingです。")
    End If

End Sub

次のコードは
オブジェクトがNothingではない場合に
If文の中を実行します。

Sub macro20100513d()
'オブジェクトがNothingでないか判別する

    Dim obj As Object
    Set obj = ActiveSheet
   
    If Not (obj Is Nothing) Then
        'ここに変数objがNothingでない場合に
        '実行したいコードを書く
        MsgBox ("変数objはNothingではありません。" & _
        TypeName(obj) & "です。")
    End If

End Sub

上記コードのIf文の条件式
Not (obj Is Nothing)は
変数objがNothingでない場合にTrueになります。

Nothingの判別は
TypeName関数を使用することでも可能です。
TypeName関数は
オブジェクトの情報を文字列で返します。

Sub macro20100513e()
'オブジェクトがNothingか判別する
   
    Dim obj As Object
   
    If TypeName(obj) = "Nothing" Then
        MsgBox ("変数objはNothingです。")
    End If

End Sub

Microsoftサイト参考Web「比較演算子」

行と列を入れ替える

記事「数式を値に変換する 」では
[編集]メニューの[形式を選択して貼り付け]を使いました。

行と列を入れ替えるのにも[形式を選択して貼り付け]が使えそうです。

[編集]メニューの[形式を選択して貼り付け]を選択すると
下の画像のダイアログが表示されます。

[行列を入れ替える]にチェックを入れて、
OKボタンを押すと行と列が入れ替えられてペーストされます。

ペーストする際、
コピー元の範囲とペーストする範囲が
一部でも重なるとペーストできません。

ですのでVBAでこれを使ってシート内のすべてのセルの行と列を入れ替えるのに、
新しいシートを追加して
その新しいシートに行と列を入れ替えたものをペーストすることにします。
コピー元のシートは削除します。

コードはこちら

Sub macro100512a()
'アクティブシートの使用している範囲の
'行と列を入れ替える

    Dim shname As String
    shname = ActiveSheet.Name
   
    '使用範囲をコピー
    ActiveSheet.UsedRange.Select
    Selection.Copy
   
    '新しいシートを名前を付け追加し、行と列を入れ替えペースト
    Sheets.Add.Name = shname & "tmp"
    Range("A1").Select
    Selection.PasteSpecial Transpose:=True
   
    'コピー元シートを削除
    Application.DisplayAlerts = False
        Sheets(shname).Delete
    Application.DisplayAlerts = True
   
    'ペーストしたシートの名前をコピー元シートと同じ名前に変更
    Sheets(shname & "tmp").Name = shname
   
End Sub

次は、引数付きのプロシージャでやってみます。

コードはこちら

変数Range1にコピー元の範囲を
変数Range2にペースト先の左上のセルを指定します。

Sub ExchangeRC(Range1 As Range, Range2 As Range)
'指定した範囲の行列を入れ替える
'Range1 = コピー元の範囲
'Range2 = ペースト先の範囲の左上のセル
   
    'Range1で指定した範囲をコピー
    Range1.Copy
   
    'Range2で指定した範囲に行と列を入れ替えペースト
    Range2.Select
    On Error GoTo ErrHandle
    Selection.PasteSpecial Transpose:=True
    On Error GoTo 0
    Application.CutCopyMode = False
   
Exit Sub
ErrHandle:
    If Err = 1004 Then
        MsgBox "選択範囲が正しくありません。" & Chr(10) & _
            "コピー元の範囲とペースト先の範囲が重なっていないか" & _
            "確認してください｡"
    Else
        MsgBox "実行時エラー" & Err & ":" & Err.Description
    End If
   
End Sub

Sub macro100512c()
'ExchangeRC使用例1
'アクティブシート内でのコピペ

    Call ExchangeRC(Range("F14:G19"), Range("G19"))
   
End Sub

Sub macro100512d()
'ExchangeRC使用例2
'異なるシート間のコピペ

    Call ExchangeRC(Sheets("Sheet1").Range("F14:G19"), _
        Sheets("Sheet2").Range("G20"))
   
End Sub

コピー元とペースト先の範囲が重なっていた場合、
エラーになり
このようなMsgBoxを表示します。

このエラーが発生した時は
ペースト先の範囲をコピー元の範囲と重ならないように変更してください。

数式を値に変換する

数式が入っているセルは
その数式で参照しているセルの値を変更すると
数式が入っているセルの値まで変わります。

このとき数式が入っているセルの値を変化させたくない場合、
数式を値に変換します。

「変換」といっても
要するにコピペです。

手動でコピーすると
[編集]メニューに[形式を選択して貼り付け]
という項目が現れます。

これを選択した画面が下の画像です。
この中の[値]にチェックを入れてOKボタンを押すと
値だけがペーストされます。

あるセルでコピーして
同じセルに[形式を選択して貼り付け]れば
数式を値に変換できます。

これをVBAでするコードはこちら

Sub macro100508a()
'選択している範囲の数式を値にする

    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues
    Application.CutCopyMode = False
   
End Sub

上の画像を見ると
まだ使ったことのない[形式を選択して貼り付け]が
たくさんありますね～

[書式設定]をクリアするときの不便を解消

日付けが入っているセルを、
メニューバーの[編集]-[クリア]-[書式設定]で
書式設定をクリアすると
フォントの色だけをなくしたいだけでも
「表示形式」までクリアされて数字になってしまいます。

そこで、また表示形式を設定しなおす…
なんてことをしたことありませんか？

エクセルにとっては、「表示形式」も「装飾」も
「表面的なこと」で括られているようです。

人間にとっては、「表示形式」が設定されてないと
理解できない場合が多いと思います。
逆に「装飾」はなくても情報は伝わります。
（ここでいう「装飾」とは、
フォントの色・大きさ・太さやセルの枠線、背景の色など。）

人間の感覚的に「表示形式」と「装飾」は違うと思いますが、
それを「書式設定」とひとつにまとめてクリアするところに不便を感じます。

この不便を解消するために、
[編集]-[クリア]で現れる項目に
[表示形式]と[装飾]を追加します。

コードはこちら

まず下のコードを標準モジュールにコピペします。

Sub ClearNumberformat()
'書式の中の「表示形式」をクリアする

    Selection.NumberFormatLocal = "G/標準"
   
End Sub

Sub ClearDecoration()
'書式の中の「装飾」をクリアする

    With Selection
        .Interior.ColorIndex = xlNone
        .Borders.LineStyle = xlNone
        .Interior.Pattern = xlPatternNone
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
    With Selection.Font
        .ColorIndex = xlAutomatic
        .Bold = False
        .Italic = False
        .Name = Application.StandardFont
        .OutlineFont = False
        .Shadow = False
        .Size = Application.StandardFontSize
        .Strikethrough = False
        .Subscript = False
        .Superscript = False
        .Underline = xlUnderlineStyleNone
    End With
End Sub

これら２つのコードは、
選択している範囲をそれぞれ「表示形式」と「装飾」をクリアします。

次のコードで
[編集]-[クリア]で現れる項目に
[表示形式]と[装飾]を追加します。

VBAでメニューをカスタマイズする方法については、
マイクロソフトサポートオンラインの次の頁が詳しいです。
『Excel のメニューおよびメニュー バーをカスタマイズする方法』

Sub macro100501a()
'メニューバーの[編集]-[クリア]の項目に追加
'[表示形式]…書式の中の「表示形式」をクリアする
'[装飾]…書式の中の「装飾」をクリアする

   With CommandBars("Worksheet menu bar").Controls("編集(&E)"). _
    Controls("クリア(&A)")
      .Controls.Add(Type:=msoControlButton, Before:=1).Caption _
        = "表示形式"
      .Controls("表示形式").OnAction = "ClearNumberformat"
      .Controls.Add(Type:=msoControlButton, Before:=1).Caption _
        = "装飾"
      .Controls("装飾").OnAction = "ClearDecoration"
   End With
End Sub

実行後のメニュー

最後に、追加したメニューを削除するコードです。

Sub macro100501b()
'[表示形式]と[装飾]をメニューから削除する

    With CommandBars("Worksheet menu bar"). _
        Controls("編集(&E)").Controls("クリア(&A)")
        .Controls("表示形式").Delete
        .Controls("装飾").Delete
    End With
End Sub

Excel2007からはメニューがなくなって
「リボン」なるものになったようですので
メニューの追加はできないと思います。