VBAHaskellはMS Office上のVBAで動作するライブラリで、特にアプリケーションを限定していないため、WordやOutlookでも使えます。しかし実際にはExcelで使うことが多いですし、ワークシートとのデータのやり取りが欲しくなります。
それを含めてI/O関係のユーティリティ関数はライブラリ本体とは違うところで作って使ってましたが、ひとつのモジュールに集めることにし、これから中身を増やしていこうと考えてます。今回は以下の5つの機能について書きます。
1. ワークシートから配列変数を取得
2. 配列変数からワークシートにペースト
3. Rangeオブジェクトの配列を取得
4. テキストファイルから配列変数にデータを取り込む
5. WEB上のテキストデータから配列変数にデータを取り込む
モジュールはY_IO_utiliy.basまたはY_IO_utiliy.basにあります。
1. sheet2m 関数
ワークシートのデータから配列変数を取得する関数です。
Function sheet2m(ByVal r As Object, Optional ByVal vec As Boolean = False)
ExcelではDim m As Variant : m = セル範囲.Value とするだけでワークシートから2次元配列mを取得できます1。この関数がやっているのは基本的にはそれだけですが、結果の配列のLBoundの値を0に揃えています。また、第2引数にTrueを指定すると1次元配列が得られます。
サンプルとしては以下の通りです。
m = sheet2m([A1:D100])
printS m
[Dim1]: 0 -> 99 [Dim2]: 0 -> 3 : Total Size = 400
m = sheet2m([A1:D100], True)
printS m
[Dim1]: 0 -> 399 : Total Size = 400
2. Sub m2sheet
配列をExcelシートの任意の位置にペーストします。
Sub m2sheet(ByRef matrix As Variant, _
ByVal r As Object, _
Optional ByVal vertical As Boolean = False)
第1引数が対象配列、第2引数が範囲で左上のセルが起点になります。
第3引数はOptionalになっていて、Trueにすると1次元配列が縦にペーストされます。（2次元配列の場合は無視）
以下のサンプルでは乱数を入れた3行4列の配列をセル[B4]に貼り付けています。
m = makeM(3, 4, uniform_int_dist(12, -10, 10))
printM m
-2 -10 4 2
5 1 -9 -10
3 4 -8 -5
m2sheet m, [B2]
3. getRangeMatrix関数
ワークシートのセル範囲からRangeオブジェクトの配列を取得します。
Function getRangeMatrix(ByRef r As Object) As Variant
sheet2m 関数はセルの値だけを取得して配列にしますが、この関数は各セルをRangeオブジェクトとして配列化します。個々の要素がプロパティやメソッドを呼び出せます。
これとオブジェクトのInteriorプロパティを取得するgetInteriorを組み合わせて、任意のセル範囲にランダムに色を塗るサンプルを示します。
' オブジェクトに色を付けるよ
Function colorX(ByRef a As Variant, ByRef b As Variant) As Variant
a.Color = b
colorX = b
End Function
Function p_colorX(Optional ByRef firstParam As Variant, Optional ByRef secondParam As Variant) As Variant
p_colorX = make_funPointer(AddressOf colorX, firstParam, secondParam)
End Function
そして、セル範囲と同じ形をしたランダム整数(RGB値)をzipWithすると色が塗られます。
a = getRangeMatrix([A1:G5]) ' セル範囲
b = makeM(5, 7, uniform_int_dist(35, 0, 256&*256*256 - 1)) ' ランダム値
call zipWith(p_colorX, mapF(p_getInterior, a), b)
4. getTextFile関数
テキストファイルから配列変数にデータを取り込みます。
Function getTextFile( _
ByVal fileName As String, _
Optional ByVal line_end As String = vbCrLf, _
Optional ByVal Charset As String = "_autodetect_all") _
As Variant
引数のfileNameはその通りファイル名で、フルパスで指定します。
line_endは改行文字のオプションで、vbLF、vbCR、vbCRLF（デフォルト）が使えます。
Charsetは使用されているキャラクターセットで、UTF-8 や EUC-JP 、 shift-jis 等が選べます。デフォルトでうまくいく場合もありますが、 shift-jis のときは明示的に指定しなければ文字化けする場合が多いような気がします。
テキストの一行に対してひとつのString型変数が割り当てられ、返り値は行数分の配列となります。
使い方は次のgetURLTextと同じなので、サンプルもそちらに書きます。
5. getURLText関数
テキストファイルの場所をURLで指定して、その内容を配列に読み込みます。
Function getURLText( _
ByVal url As String, _
Optional ByVal line_end As String = vbCrLf, _
Optional ByVal Charset As String = "_autodetect_all") _
As Variant
urlはその通りURLで、line_endとCharsetの意味は上と同じです。
サンプルとしてはジャパンネット銀行のサンプルデータ（CSVファイル）を取り、ついでにCSVとして要素に分解してみます。
s = "http://www.japannetbank.co.jp/service/payment/web_all/sample_zengin.csv"
txt = getURLText(s)
printS txt
[Dim1]: 0 -> 13 : Total Size = 14
printM_ txt
1,21,0,,ﾆﾎﾝｼﾖｳｶｲ(ｶ,1031,33,,1,,1,1234567
2,33,,1,,,1,1111111,ｶ)ﾆﾎﾝｼﾖｳｼﾞ,1000,0,
2,33,,1,,,1,2222222,ｶ)ﾔﾏﾓﾄｼﾖｳﾃﾝ,1000,0,
2,33,,1,,,1,3333333,ﾆﾎﾝｺｳｷﾞﾖｳ(ｶ,1000,0,
2,33,,1,,,1,4444444,ｻｻｷｼﾖｳｶｲ(ｶ,1000,0,
2,33,,1,,,1,5555555,ｶﾄｳﾊﾅｺ,1000,0,
2,33,,1,,,1,6666666,ｻﾄｳﾀﾛｳ,1000,0,
2,33,,1,,,1,7777777,ｻﾞｲ)ﾏﾙﾏﾙｷﾖｳｶｲ,1000,0,
2,33,,1,,,1,8888888,ｲ-.ﾄﾞﾂﾄｺﾑ(ｶ,1000,0,
2,33,,1,,,1,9999999,ｲｹﾀﾞ(ｶ)ﾆﾎﾝｼﾞﾑｼﾖ,1000,0,
2,33,,1,,,1,1234567,ﾐﾀﾌﾞﾝｸﾞﾃﾝ(ｶ,1000,0,
8,10,10000,,,,,,,,,
9,,,,,,,,,,,
printM_ は本来はジャグ配列を表示するものですが、1次元配列を縦に表示するのにも使えるため、Stringの配列を見るときはprintMより都合がいいです。
結果、コンマで区切られた普通のCSVだとわかったので、csvファイルの1行を配列に分割する関数csv2Vectorを使用して全体を分解してみます。
m = mapF(p_csv2Vector, txt) ' 各行に csv2Vector 関数をマップ
printS m
[Dim1]: 0 -> 13 : Total Size = 14
printM m
[0] [1] [2] [3] [4] [5] [6] [7] [8] [9] [10] [11] [12] [13]
printM_ m
1 21 0 ﾆﾎﾝｼﾖｳｶｲ(ｶ 1031 33 1 1 1234567
2 33 1 1 1111111 ｶ)ﾆﾎﾝｼﾖｳｼﾞ 1000 0
2 33 1 1 2222222 ｶ)ﾔﾏﾓﾄｼﾖｳﾃﾝ 1000 0
2 33 1 1 3333333 ﾆﾎﾝｺｳｷﾞﾖｳ(ｶ 1000 0
2 33 1 1 4444444 ｻｻｷｼﾖｳｶｲ(ｶ 1000 0
2 33 1 1 5555555 ｶﾄｳﾊﾅｺ 1000 0
2 33 1 1 6666666 ｻﾄｳﾀﾛｳ 1000 0
2 33 1 1 7777777 ｻﾞｲ)ﾏﾙﾏﾙｷﾖｳｶｲ 1000 0
2 33 1 1 8888888 ｲ-.ﾄﾞﾂﾄｺﾑ(ｶ 1000 0
2 33 1 1 9999999 ｲｹﾀﾞ(ｶ)ﾆﾎﾝｼﾞﾑｼﾖ 1000 0
2 33 1 1 1234567 ﾐﾀﾌﾞﾝｸﾞﾃﾝ(ｶ 1000 0
8 10 10000
9
printMの結果が[0] [1] [2] [3] [4] ... となっているのは各行を配列に分解したためmが配列の配列（ジャグ配列）になっているからで、これをunZip関数を使って2次元配列に展開することができます。
m2 = unzip(m, 2)
printM m2
1 21 0 ﾆﾎﾝｼﾖｳｶｲ(ｶ 1031 33 1 1 1234567
2 33 1 1 1111111 ｶ)ﾆﾎﾝｼﾖｳｼﾞ 1000 0
2 33 1 1 2222222 ｶ)ﾔﾏﾓﾄｼﾖｳﾃﾝ 1000 0
2 33 1 1 3333333 ﾆﾎﾝｺｳｷﾞﾖｳ(ｶ 1000 0
2 33 1 1 4444444 ｻｻｷｼﾖｳｶｲ(ｶ 1000 0
2 33 1 1 5555555 ｶﾄｳﾊﾅｺ 1000 0
2 33 1 1 6666666 ｻﾄｳﾀﾛｳ 1000 0
2 33 1 1 7777777 ｻﾞｲ)ﾏﾙﾏﾙｷﾖｳｶｲ 1000 0
2 33 1 1 8888888 ｲ-.ﾄﾞﾂﾄｺﾑ(ｶ 1000 0
2 33 1 1 9999999 ｲｹﾀﾞ(ｶ)ﾆﾎﾝｼﾞﾑｼﾖ 1000 0
2 33 1 1 1234567 ﾐﾀﾌﾞﾝｸﾞﾃﾝ(ｶ 1000 0
8 10 10000
9
6. その他
日本語URLなど、そのまま指定してもgetURLTextでテキストが落とせない場合はURLエンコードが必要になります。以下の2つの関数はそのためのユーティリティです。
' URLエンコード
Function urlEncode(ByVal s As String) As String
' URLデコード
Function urlDecode(ByVal s As String) As String
?urlEncode("http://www.テスト.txt")
http://www.%E3%83%86%E3%82%B9%E3%83%88.txt
?urlDecode("http://www.%E3%83%86%E3%82%B9%E3%83%88.txt")
http://www.テスト.txt
（2016/12/12追記）
この二つの関数の中でADODB.Streamを使っていますが、カナ漢字の判定や文字コード⇔%nn表記のマッピングはベタなプログラムを書いています。その後CreateObject("ScriptControl")を使えば簡単に実現できることを知り、書き換えようと思ったのですが、ScriptControlが64bitのOffice環境には対応していないことに気づきました。
-
ずっと昔は取得できるセルの数に制約があり、範囲が大きい時は3000個ぐらいずつ数回に分けて取得して結合する手間がかかりました。 ↩