2019/11/23

自分専用の関数を作る



0.はじめに

別項目「計算や検索を行うサイトからデータを取得する」で、他のサイトにデータを送って結果を受け取る、というプログラムを作りましたが、その時は、INPUTボックスに1回1回データをタイプ入力し、結果もMSGBOXに表示される、というものでした。
1個や2個のデータでしたら気になりませんし、そもそもExcelで作らなくてもそのサイトに直接行った方が速そうです。Excelで作る、ということは大量のデジタルデータを一気に処理できる、という所に魅力があるわけです。
ということで、大量データに対応した手法を紹介します。

1.(おさらい)計算や検索を行うサイトからデータを取得する方法

INPUTボックスに入れたデータ(文字列)を結果表示ページに直接送り、そのHTMLソースから結果を探し出しMSGBOXで表示する、というものです。このプログラムは「マクロ実行」などの方法で読み出して実行するものです。
計算や検索を行うサイトからデータを取得する」を参照下さい
  1. Sub main()
  2.  Dim st As String   '入力する文字列
  3.  Dim paramStr As String   'ページにデータを渡すPOSTパラメータ
  4.  Dim xmlhttp As Object   'サーバとのHTTP通信を行うための組込みオブジェクト(XMLHttpRequestオブジェクト)
  5.  Dim retCd As String    '結果コード(コード200であれば正常)
  6.  Dim retHtml As String   '結果HTML
  7.  Dim retStr As String    '結果データ
  8.  Dim Position1 As Integer 'ソース中での位置(結果データの手前側)
  9.  Dim Position2 As Integer 'ソース中での位置(結果データの後側)
  10.  Const url As String = "http://atsumitm.iobb.net/it/test_keisan1.php"
  11.  Const StartStr1 As String = "MD5ハッシュ値</td>"  '目的ワードの手前で特徴的な言葉・記号
  12.  Const StartStr2 As String = "<td>"   '目的のワードの直前の記号
  13.  Const EndStr1 As String = "</td>"     '目的のワードの直後の記号
  14.  st=inputbox("文字列を入力して下さい")
  15.   If st = "" Then
  16.    Exit Sub
  17.   End if
  18.  paramStr = "&moji=" & st
  19.  Set xmlhttp = CreateObject("msxml2.xmlhttp")
  20.  xmlhttp.Open "POST", url, False   'url2との接続
  21.  xmlhttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"  'HTTPリクエストヘッダ値を設定
  22.  xmlhttp.send (paramStr)   'パラメータを送付
  23.  retCd = xmlhttp.Status   '結果コードを取得
  24.  If retCd <> 200 Then
  25.   retStr= "Error番号:" & retCd
  26.  Else
  27.   retHtml = StrConv(xmlhttp.responseBody, vbUnicode) '結果HTML取得
  28.   Position1 = InStr(retHtml, StartStr1) + Len(StartStr1)
  29.   Position1 = InStr(Position1, retHtml, StartStr2) + Len(StartStr2)
  30.   Position2 = InStr(Position1, retHtml, EndStr1)
  31.   retStr = Mid(retHtml, Position1, Position2 - Position1)
  32.    if retStr = "" then retStr = "データがありません"
  33.  End If
  34.  MsgBox (st & vbCrLf & retStr)
  35.  Set xmlhttp = Nothing
  36. End Sub
このプログラムですと初めに説明した通り、INPUTボックスを使用している為、結果は1つずつしか得られません。

2.自分用関数の作り方

Excelには、組み込みワークシート関数が400種類以上が存在します。例えば「=SUM(A1:A5)」などが真っ先に出てくる関数でしょうが、「自分のやりたい事を表現してくれる関数が、どうしても見つからない」という事もあると思います。
そういう時に、自分専用の関数(カスタム関数)を作る方法を覚えておくと便利です。

カスタム関数はVBAで作成し、標準モジュールに記入します。まずは、以下のコードを標準モジュールに貼って下さい。
  1. Function zeikomi(kakaku As Long, keigen As Boolean, hizuke As Date)
  2.  If hizuke >= DateSerial(2019, 10, 1) Then  '2019/10/1の10%開始より後か
  3.   If keigen = True Then         '軽減税率の対象か
  4.    zeikomi = kakaku * 1.08       '軽減税率対象での税込み価格
  5.   Else
  6.    zeikomi = kakaku * 1.1        '軽減税率対象以外での税込み価格
  7.   End If
  8.  Else
  9.   zeikomi = kakaku * 1.08        '9/30までの8%税込み価格
  10.  End If
  11.  zeikomi = Int(zeikomi)          '1円未満を切り捨て
  12. End Function

貼り付けた後、下図のようにワークシート上で「日付」「価格」「軽減税率」の列を作り、別の列で数式として、
   =zeikomi(価格 , 軽減税率対象有無 , 買った日付)   を入力します。

ちゃんと税込み価格が計算できていますよね。
但し、この位の計算であれば、以下の数式で表現できてしまいます。
   =INT(IF(A2>=DATE(2019,10,1),(IF(C2,B2*1.08,B2*1.1)),B2*1.08))

3.SUBプロシージャとFunctionプロシージャの違い

では、下のような「ハッシュ値」の一覧を作りたい場合はどうでしょう。

一番上のマクロは使えますが、1つ1つ文字列を入力しなくてはならず多くの文字列を処理するには大変です。また、残念ながらExcelの組み込み関数には無さそうです。
こういう時こそカスタム関数の出番です。

まずワークシート関数として使えるように改造するに当たり、SUBプロシージャとFunctionプロシージャの違いを整理してみましょう。
(尚、各プロシージャとも色々な使い方が出来ます。とりあえず一番上のマクロに限っての表です)
 SUBプロシージャ  Functionプロシージャ 
 プロシージャの始まり  SUB+プロシージャ名  Function+プロシージャ名 
 入力方法  inputboxなど  プロシージャの引数 
 出力方法  msgboxなど  プロシージャ名に戻り値を渡す 
 プロシージャの終わり  END SUB  END Function 

4.マクロのFunction改造

上の表の「SUBプロシージャとFunctionプロシージャの違い」を確認しながら改造をしていきましょう。
まず、プロシージャの始まりと終わりを変更します。途中で抜ける「Exit・・」も忘れずに変更します。
変更するに当たり、プロシージャ名はワークシート関数として登録されていないものであれば問題ないのですが、後から見て分かり易い名前が良いので、ここではハッシュ名を使った「hash_md5」にしました。
(ちなみに、単純に「md5」としてしまうとセルの「MD5」番地を指してしまいますのでプロシージャ名には使えません)
  1. Function hash_md5()
  2. ・・・・
  3.   If st = "" Then
  4.    Exit Sub 
  5.    Exit Function
  6.   End if
  7. ・・・・
  8. End Function

次に、入力としてはSUBプロシージャのinputbox の戻り値を入れていた関数「st」を使いましょう。型はString です。
  1. Function md5(st As String)
  2.  Dim st As String   '入力する文字列
  3. ・・・・
  4.  st=inputbox("文字列を入力して下さい")
  5. ・・・・
  6. End Function

最後に、出力として「md5=〇〇」を加えます。
  1. Function md5(st As String)
  2. ・・・・
  3.   If st = "" Then
  4.    hash_md5 = ""  ←追加行
  5.    Exit Function
  6.   End If
  7. ・・・・
  8.  MsgBox (st & vbCrLf & retStr)
  9.  hash_md5 = retStr  ←追加行
  10. ・・・・
  11. End Function

以上です。結構簡単ですね。変更を加えたFunctionプロシージャを全て書いてみます。
  1. Function hash_md5(st As String)
  2.  Dim paramStr As String   'ページにデータを渡すPOSTパラメータ
  3.  Dim xmlhttp As Object   'サーバとのHTTP通信を行うための組込みオブジェクト(XMLHttpRequestオブジェクト)
  4.  Dim retCd As String    '結果コード(コード200であれば正常)
  5.  Dim retHtml As String   '結果HTML
  6.  Dim retStr As String    '結果データ
  7.  Dim Position1 As Integer 'ソース中での位置(結果データの手前側)
  8.  Dim Position2 As Integer 'ソース中での位置(結果データの後側)
  9.  Const url As String = "http://atsumitm.iobb.net/it/test_keisan1.php"
  10.  Const StartStr1 As String = "MD5ハッシュ値</td>"  '目的ワードの手前で特徴的な言葉・記号
  11.  Const StartStr2 As String = "<td>"   '目的のワードの直前の記号
  12.  Const EndStr1 As String = "</td>"     '目的のワードの直後の記号
  13.   If st = "" Then
  14.    hash_md5 = ""
  15.    Exit Function
  16.   End If
  17.  paramStr = "&moji=" & st
  18.  Set xmlhttp = CreateObject("msxml2.xmlhttp")
  19.  xmlhttp.Open "POST", url, False   'url2との接続
  20.  xmlhttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"  'HTTPリクエストヘッダ値を設定
  21.  xmlhttp.send (paramStr)   'パラメータを送付
  22.  retCd = xmlhttp.Status   '結果コードを取得
  23.  If retCd <> 200 Then
  24.   retStr= "Error番号:" & retCd
  25.  Else
  26.   retHtml = StrConv(xmlhttp.responseBody, vbUnicode) '結果HTML取得
  27.   Position1 = InStr(retHtml, StartStr1) + Len(StartStr1)
  28.   Position1 = InStr(Position1, retHtml, StartStr2) + Len(StartStr2)
  29.   Position2 = InStr(Position1, retHtml, EndStr1)
  30.   retStr = Mid(retHtml, Position1, Position2 - Position1)
  31.    if retStr = "" then retStr = "データがありません"
  32.  End If
  33.  hash_md5 = retStr
  34.  Set xmlhttp = Nothing
  35. End Function

この自作関数「hash_md5(文字列)」を使って、ワークシートの計算をさせましょう。無事にMD5ハッシュ値が算出されました。 また、空セルの場合(下の例の10行目)は空("")が戻されるようになっています。



5.他人のサイトを計算に使用する場合の注意点

なお、この例のように他の人のサイトを使って計算をする場合は、注意が必要です。計算の都度、そのサイトにアクセスを繰り返すため、悪くすると「サイトが攻撃されている」と受け取られるからです。(アクセスを停止される可能性もあり、最悪では攻撃者とみなされて訴えられる可能性もあります)
ハッシュ値計算なら、Excelのマクロだけでもできます(「Excel ハッシュ 計算」で検索すると、一杯出てきます)のでトライして下さい。