為替レートを取得しワークシートに表示する

	為替レートの情報を取得し、ワークシートに為替ボード風に表示するサンプルです。為替レートはリアルタイム、さらに自動更新します。為替レートのデータは、ヤフーファイナンスから取得するので安心（？）です。 本サンプルはAccess版「為替レートを取得しフォームに表示する」もあります。解説の内容やサンプルの仕様は、ほぼ同じです。 サンプルダウンロード（2014/01/10更新）

	このサンプルのテーマと仕組み
	ウェブ上からの情報取得がテーマのサンプルです。情報の取得元は、ヤフーファイナンスの為替情報（https://fx.yahoo.co.jp/chart/）にある為替ボードです。ウェブ上から為替レートのデータを取得し、ワークシートに貼り付けて、為替ボードとして表示します。 図 1サンプルの概要（為替ボードを作成する） 　ヤフーファイナンスの為替ボードは、約10秒ごとに新しいレートに更新されます。ただし、ページを再読込するのではなく、為替レートのデータを別な場所から取得しページ内の必要な部分を動的に書き換えて表示する仕組みとなっています。これは、ブラウザの開発者ツールで動作を確認すると一目瞭然です。 　そこで、本サンプルでも、同じように為替レートのデータを取得してワークシートに表示します。ウェブ上からデータを取得するにはいくつかの方法がありますが、取得するデータの内容が一定の書式に従って定義・作成されたテキスト形式のデータであることと、一定間隔ごとにデータを取得しなくてはならないため、MSXML2.XMLHTTPというコンポーネントを使って取得します。 図 2開発者ツールでのキャプチャ例

	為替データの取得
	MSXML2.XMLHTTPを使用してウェブ上のデータを取得する処理です。199〜201行目で参照するページのURLを生成、205行目でMSXML2.XMLHTTPオブジェクトを作成し、207〜226行目で同オブジェクトの機能を使用しウェブページを参照する処理を行います。URL生成時に乱数を組み合わせていますが、これはブラウザのキャッシュを取り出さないようにするためのものです。数字そのものに特に意味はなく、MSXML2.XMLHTTPでダウンロードを行うときによく使うテクニックです。 　実際にウェブページを参照するリクエストの実行は217行目のSendメソッドで行いますが、基本的に非同期で実行するものであるため、ページのダウンロードが完了するまで何らかの方法で待機しなくてはなりません。 　サンプルでは、219〜222行目で待機用のループ処理を設け、ダウンロードが完了するのを待っています。ダウンロードが完了したかどうかは、readyStateプロパティの値で判定します。4ならダウンロード完了です。また、ダウンロードしたテキストの情報は、responseTextプロパティから取得できます。 リスト 1クラスのインスタンス生成と取得処理の起動 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 Module1 Private Function getFxRateInfo() As String     Dim sURL  As String     Dim dParm As Long     dParm = 1000000000 + Int(Rnd * 1147483647)     sURL = "https://" & HTTP_Domain & "/rate_data.js?_=" & dParm          getFxRateInfo = vbNullString     Set objHttp = CreateObject("MSXML2.XMLHTTP")     With objHttp         .abort         .Open "GET", sURL, True         .setRequestHeader "Accept", "text/html, application/xhtml+xml, */*"         .setRequestHeader "Accept-Language", "ja-JP"         .setRequestHeader "Accept-Encoding", "gzip, deflate"         .setRequestHeader "User-Agent", "Mozilla/5.0 (compatible; MSIE…(略)…         .setRequestHeader "Host", HTTP_Domain         .setRequestHeader "Connection", "Keep-Alive"         .send          Do Until .readyState = 4             Call Sleep(100)             DoEvents          Loop          getFxRateInfo = .responseText     End With     Set objHttp = Nothing End Function 注：行番号はモジュール全体の中での行数を表します。これ以降のリストも同様です。

	取得した為替データを整える
	為替のデータは、一定の書式で定義・作成されています。このままだと少々扱いにくいので、カンマ区切りのデータに整えます。置き換えの方法は次の図の通りです。 図 3データを加工する 　文字列の置き換えは、RegExp（正規表現）を使って行いました。本サンプルでは、やや複雑な置き換えを行っているため、Replace関数で置き換えを行うよりもプログラムをシンプルかつ明確にできます。 リスト 2データの加工を行う処理 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 Module1 Public Function LoadFxRateData() As String     Dim strData As String     Dim oRegExp As Object     Dim lStart  As Long     LoadFxRateData = ""     Set oRegExp = CreateObject("VBScript.RegExp")     strData = getFxRateInfo()     oRegExp.Global = True     oRegExp.Pattern = "(\:\{)"     strData = oRegExp.Replace(strData, ",")     oRegExp.Pattern = "(\""\:\"")"     strData = oRegExp.Replace(strData, ",")     oRegExp.Pattern = "[\{\}\""]"     strData = oRegExp.Replace(strData, vbNullString)     LoadFxRateData = strData     Set oRegExp = Nothing End Function

	為替データをワークシートに表示する
	為替データをカンマ区切りに加工する処理までが終われば、あとは簡単です。Split関数を使いデータをカンマごとに分解し、データを配列化した中から必要な部分をワークシートに貼り付けます。 　為替レートデータを配列化すると、各通貨ペアの先頭要素が1、27、53…のように26個おきに現れます。そこで、For〜Nextのカウンターの値を各通貨ペアの先頭をたどるようにし、レート情報などの必要な情報は先頭から相対位置で（何個目かで）参照しています。 リスト 3ワークシートに貼り付ける処理 42 ： ： ： 58 59 60 61 62 63 64 65 66 67 68 69 70 71 ： ： ： Module1 Private Sub getFxRate()        :     （各種処理）        :     For i = 0 To UBound(cRateList) - 26 Step 26         '通貨ペア名         objSheet.Cells(lRow, 1).Value = Left(cRateList(i + RATE_PAIR), 3) _                                       & "/" _                                       & Right(cRateList(i + RATE_PAIR), 3)         'Bidとティック記号         Call UpDownStatus(objSheet.Cells(lRow, 2), cRateList(i + RATE_BID))         objSheet.Cells(lRow, 2).Value = cRateList(i + RATE_BID)         'Askとティック記号         Call UpDownStatus(objSheet.Cells(lRow, 4), cRateList(i + RATE_ASK))         objSheet.Cells(lRow, 4).Value = cRateList(i + RATE_ASK)        :     （各種処理）        : 　さらに、Application.OnTimeメソッドを使って、次回の為替レート取得処理を実行します。Application.OnTimeメソッドは、指定した時間に、指定したプロシージャを実行するメソッドです。サンプルでは、現在の時刻から10秒後にプロシージャ「getFxRate」を実行するようにしています。自分自身（getFxRate）を繰り返し実行することで、ワークシート上の為替レートが常に更新されるという仕組みです。 リスト 4次回の為替レート取得（タイマーの設定） 42 ： ： ： 89 90 91 ： ： ： 99 Module1 Private Sub getFxRate()        :     （各種処理）        :     '次回実行タイマーをセット     dteTimer = DateAdd("s", TIMER_SEC, Now())     Application.OnTime dteTimer, TIMER_PROC, , True        :     （各種処理）        : End Sub

	サンプルプログラムの実行例
	プロシージャ「FxRate」を実行、もしくは {Ctrl}+{R} キーを押すと、ワークシート上に為替レートの一覧が表示されます。為替レートは、10秒おきに自動的に最新の情報に更新されます。また、レートに変化があった部分は、いわゆる「ピコピコ」で知らせます。 　為替レートの更新を停止するときは、プロシージャ「TimerStop」を実行するか {Ctrl}+{Q} キーを押してください。 図 4サンプルプログラムの実行結果 注1：画像クリックで拡大します。拡大画像の左右をクリックすると、次や前の画像が表示されます。本サンプルは Excel 2000以降に対応しています。 注2：データ取得元の仕様変更に対応しました（2014/01/10）