メインコンテンツへスキップ
見出し画像

VBA応用1：関数/定数を自作：Function

    KIYO

    １．概要

    　以前の記事でExcel機能の関数を紹介しましたが、ほしい機能がないことも多数あります。

    　そこで今回の記事では自分専用の関数を自作します。

    ２．基礎知識

    ２－１．用語

    　VBA使用時に頻繁に出てくる用語の意味は下記の通りです。

    【用語】
    ●VBA(Visual Basic for Applications)：プログラミング言語
    ●VBE(Visual Basic Editor)：コードを記載するエディタ
    ●マクロ：実行機能

    ２－２．Functionプロシージャ

    　VBAで記載する処理をプロシージャと呼び、主に３種類あります。

    【プロシージャ一覧】
    ●Subプロシージャ：戻り値を持たない処理であり「マクロダイアログ」から呼び出して実行可能です。
    ●Private Subプロシージャ：戻り値を持たない処理であり「マクロダイアログ」から呼び出しできません（裏側だけで処理させる用）。
    ●Functionプロシージャ：戻り値を持つ処理であり通常のExcel内でも使用が可能です。

    　Functionプロシージャを使用すれば自作関数も作成可能です。

    [Functionプロシージャ]
Function KIYO_FUNC(入力値) As 戻り値の型
    KIYO_FUNC = n * 2 + 1 '関数名＝処理したい内容と記載する
End Function

    　ポイントは「Functionプロシージャ内で関数を定義することで、津城ではVBAでしか使用できない関数をExcel内でも使用可能」です。例として検索文字が対象とする文字列の後ろから何番目にあるかを確認する"InStrRev"も使用可能になります。

    [Functionプロシージャ]
Function Instr(Path, word) As Long
    Instr = Instrrev(Path, word)
End Function

    ２－３．モジュール作成

    　「Alt+F11」よりVBEを開いて標準モジュールを追加します。自作関数であることが分かるようにプロパティウィンドウ（左下の枠）から名前を変更しておくと便利です。

    画像

    　またExcelのVBA内で頻繁に使用する定数があるなら別モジュール化してもよいと思います。ただし変数の重複管理が必要になります。

    画像

    ２－４．変数の適用範囲

    　VBAでは変数の宣言箇所・方法により適用範囲（使用できるエリア）が異なり、大きく分けて３つあります（詳細は下記記事参照）。

    【変数の適用範囲】
    １．プロシージャ内だけで使用可（Sub()―End Subの間のみ）
    ２．モジュール内だけで使用可（同じモジュール内はどこでも使用可）
    ３．全モジュールで使用可

    　MyConstモジュールで必要な定数を管理する場合は「３．全モジュールで使用可」に該当し、この場合の変数定義方法は下記の通りです。

    [Module]
Public <変数名> As <型式>

Sub プロシージャ名()
 <変数名> = 値
End Sub

    【参考用トライアル　※参考用のため記事では使用しません】
    　サンプルデータは「Qiita：都道府県コード JSON版とJS版」を編集して、下記の通りPythonのコードで作成しました。replace()処理を入れているのは"をエスケープシーケンス処理するためです。

    [IN ※Python]
import json
prefectures = {
"prefectures": [{ "code": 1, "name": "北海道"},
    { "code": 13, "name": "東京都"},
    { "code": 14, "name": "神奈川県"},
    { "code": 23, "name": "愛知県"},
    { "code": 26, "name": "京都府"},
    { "code": 27, "name": "大阪府"},
    { "code": 40, "name": "福岡県"},
    { "code": 47, "name": "沖縄県"}]
}

print(type(prefectures), type(json.dumps(prefectures, ensure_ascii=False))) #型式確認：json.dumpsで文字列に変換
prefectures_json = json.dumps(prefectures, ensure_ascii=False) #ensure_ascii->日本語の文字化け防止
prefectures_jsonesc = prefectures_json.replace('"', '""')

print(prefectures_json)
print(prefectures_jsonesc)

[OUT]
<class 'dict'> <class 'str'>
{"prefectures": [{"code": 1, "name": "北海道"}, {"code": 13, "name": "東京都"}, {"code": 14, "name": "神奈川県"}, {"code": 23, "name": "愛知県"}, {"code": 26, "name": "京都府"}, {"code": 27, "name": "大阪府"}, {"code": 40, "name": "福岡県"}, {"code": 47, "name": "沖縄県"}]}
{""prefectures"": [{""code"": 1, ""name"": ""北海道""}, {""code"": 13, ""name"": ""東京都""}, {""code"": 14, ""name"": ""神奈川県""}, {""code"": 23, ""name"": ""愛知県""}, {""code"": 26, ""name"": ""京都府""}, {""code"": 27, ""name"": ""大阪府""}, {""code"": 40, ""name"": ""福岡県""}, {""code"": 47, ""name"": ""沖縄県""}]}

    　MyConstモジュールで変数を定義して、同モジュール内および別モジュールで実行した結果、両方とも変数を読み込むことが出来ました。
    （一度定義したプロシージャをF5で実行しないと空文字が出力されました）

    [MyConstモジュール]
Public Prefectures As String

Sub パブリック変数定義()
    Prefectures = "{""prefectures"": [{""code"": 1, ""name"": ""北海道""}, {""code"": 13, ""name"": ""東京都""}, {""code"": 14, ""name"": ""神奈川県""}, {""code"": 23, ""name"": ""愛知県""}, {""code"": 26, ""name"": ""京都府""}, {""code"": 27, ""name"": ""大阪府""}, {""code"": 40, ""name"": ""福岡県""}, {""code"": 47, ""name"": ""沖縄県""}]}"
End Sub

Sub B()
    Debug.Print Prefectures
End Sub

[OUT]
{"prefectures": [{"code": 1, "name": "北海道"}, {"code": 13, "name": "東京都"}, {"code": 14, "name": "神奈川県"}, {"code": 23, "name": "愛知県"}, {"code": 26, "name": "京都府"}, {"code": 27, "name": "大阪府"}, {"code": 40, "name": "福岡県"}, {"code": 47, "name": "沖縄県"}]}
    [MyFuncモジュール]
Sub B()
    Debug.Print Prefectures
End Sub

[OUT　※モジュール内で定義していない変数を出力できた]
{"prefectures": [{"code": 1, "name": "北海道"}, {"code": 13, "name": "東京都"}, {"code": 14, "name": "神奈川県"}, {"code": 23, "name": "愛知県"}, {"code": 26, "name": "京都府"}, {"code": 27, "name": "大阪府"}, {"code": 40, "name": "福岡県"}, {"code": 47, "name": "沖縄県"}]}
    画像
    画像

    ２－５．自作定数：シンプルなFunction

    　Function内で入力した数値をそのまま出力とする場合は自作定数も作成可能です。モジュール内でのコード・動作は下記の通りです。

    [MyConstモジュール　※先ほどのPublic変数とFucntion変数名が重複してエラーとなるためPublicは削除]

Function Prefectures() As String
    Prefectures = "{""prefectures"": [{""code"": 1, ""name"": ""北海道""}, {""code"": 13, ""name"": ""東京都""}, {""code"": 14, ""name"": ""神奈川県""}, {""code"": 23, ""name"": ""愛知県""}, {""code"": 26, ""name"": ""京都府""}, {""code"": 27, ""name"": ""大阪府""}, {""code"": 40, ""name"": ""福岡県""}, {""code"": 47, ""name"": ""沖縄県""}]}"
End Function
    [MyFuncモジュール]
Sub B()
 Debug.Print Prefectures
End Sub

[OUT]
{"prefectures": [{"code": 1, "name": "北海道"}, {"code": 13, "name": "東京都"}, {"code": 14, "name": "神奈川県"}, {"code": 23, "name": "愛知県"}, {"code": 26, "name": "京都府"}, {"code": 27, "name": "大阪府"}, {"code": 40, "name": "福岡県"}, {"code": 47, "name": "沖縄県"}]}
    画像

    　Fucntionで定義したので実際にExcel内で呼び出すことが可能になります。

    画像

    ３．まとめ

    ３－１．モジュール構成

    　モジュールの構成は①MyConst(定数用)、②MyFunc(関数用)です。

    画像

    ３－２．全コード

    　全コードを記載したモジュールを（自分用で）添付します。インポートすれば同じ関数が使用可能となります。

    ４．自作関数１：基礎データ取得

    ４－１．行列の最終データ位置取得

    　新規にデータを追加したり最新の情報を取得するために行列端の位置情報を取得する関数を作成しました。まずは行を紹介します。

    [IN]
Function GetEndRow(Column As Variant, Nextcell)
    If Nextcell Then
        addrow = 1 '一番下にあるセルの一つ下の位置
    Else
        addrow = 0 '一番下にあるセルと同じ位置
    End If
    r = ActiveSheet.Rows.Count '使用しているシートの最大行数(1048576)を取得 ※Excel2010
    
'Columnは列の数値または文字列の両方入力出来るようにする。
    If IsNumeric(Column) Then
        GetEndRow = Cells(r, Column).End(xlUp).Row + addrow '最終行から上に移動したときのセルの値
    Else
        Num_Column = Range(Column & "1").Column
        GetEndRow = Cells(r, Num_Column).End(xlUp).Row + addrow '最終行から上に移動したときのセルの値
    End If

End Function
    [セル内での使用方法]
GetEndRow(<列位置(数値or記号)>, True/False)

    　実際に何パターンかで使用した結果は下記の通りです。

    [IN_A8 ※列記号で入力]
=GetEndRow("A", FALSE)
[OUT_A8]
9

[IN_B10　※列を数値で入力]
=GetEndRow(2, TRUE)
[OUT_B10]
12

[IN_C9　※列を関数で入力：関数が自動的にセル情報を取得]
=GetEndRow(COLUMN(), TRUE)
[OUT_C9]
10
    画像

    　上記と同じ形で列情報用の関数も下記の通り作成しました。

    [IN]
Function GetEndColumn(Row As Variant, Nextcell)
    If Nextcell Then
        addrow = 1 '一番下にあるセルの一つ下の位置
    Else
        addrow = 0 '一番下にあるセルと同じ位置
    End If
    col_max = ActiveSheet.Columns.Count '使用しているシートの最大列数(16384)を取得 ※Excel2010
    
    GetEndColumn = Cells(Row, col_max).End(xlToLeft).Column + addrow '最終行から上に移動したときのセルの値

End Function

    ５．自作関数２：文字列処理

    ５－１．JSON解析

    　JSONデータからKEY->VALUEに変換する関数を作成します。

    [Module]
' JSON文字列からキーkeyを取り出す
Function GetJsonKey(JsonStr, key, enc) As String
    Dim d As Object
    If JsonStr = "" Then
        GetJsonKey = ""
        Exit Function
    End If
    Set d = CreateObject("htmlfile")
    d.Write "<meta http-equiv='X-UA-Compatible' content='IE=8' />"
    ' JavaScriptの関数を定義
    d.Write "<script>" & _
            "document.getjson = function(s, key, enc){" & _
            "var vals = eval('('+s+')');" & _
            "if (enc) { return JSON.stringify(vals[key]);}" & _
            "else { return vals[key] }}" & _
            "</script>"
    ' JavaScriptの関数を呼び出す
    GetJsonKey = d.getjson(JsonStr, key, enc)
End Function
    [セル内での使用方法]
GetJsonKey(<JSONデータ>, <KEY>, True/False)

    　２－４節で作成したサンプルJSONデータを使用して入出力を確認します。

    [IN_A1]
=Prefectures()
[OUT_A1]
{"prefectures": [{"code": 1, "name": "北海道"}, {"code": 13, "name": "東京都"}, {"code": 14, "name": "神奈川県"}, {"code": 23, "name": "愛知県"}, {"code": 26, "name": "京都府"}, {"code": 27, "name": "大阪府"}, {"code": 40, "name": "福岡県"}, {"code": 47, "name": "沖縄県"}]}

[IN_A2]
=GetJsonKey(A1,"prefectures",TRUE)
[OUT_A2]
[{"code":1,"name":"北海道"},{"code":13,"name":"東京都"},{"code":14,"name":"神奈川県"},{"code":23,"name":"愛知県"},{"code":26,"name":"京都府"},{"code":27,"name":"大阪府"},{"code":40,"name":"福岡県"},{"code":47,"name":"沖縄県"}]

    ５－２．文字列のJSONリストからデータ抽出

    　前節で出現した下記の(文字列)配列は直接処理できませんでした。

    [サンプル：配列内のJSONデータ]
"[{""code"":1,""name"":""北海道""},{""code"":13,""name"":""東京都""},{""code"":14,""name"":""神奈川県""},{""code"":23,""name"":""愛知県""},{""code"":26,""name"":""京都府""},{""code"":27,""name"":""大阪府""},{""code"":40,""name"":""福岡県""},{""code"":47,""name"":""沖縄県""}]"

    　Pythonのリストのように処理できるよう、下記手順でデータ抽出できる関数を作成します。処理でわかる通り”[<JSONデータ>]"専用関数です。

    【処理イメージ】
    １．リストの[]を削除する。
    ２．"},"で文字列を分割する：最後の値は，がないため別処理が必要
    ３．文字の終わりに}がない文字は追加して、ある場合はそのまま出力

    [Module]
Function GetJsonList(JsonListStr, idx) As String
    tmp = Replace(JsonListStr, "[", "") '[を削除
    tmp = Replace(tmp, "]", "") ']を削除
    myarray = Split(tmp, "},") '}, で文字列を分割する->配列出力
    
    '-1で最後のデータが抽出できるような設定を追加
    If idx = -1 Then
        idx = UBound(myarray) 'UBound(array)で配列の最後のインデックスを抽出
    End If
    
    '最後のデータは}が残るため条件分岐
    If Right(myarray(idx), 1) <> "}" Then
        GetJsonList = myarray(idx) + "}"
    Else
        GetJsonList = myarray(idx)
    End If
    
End Function

    　前節で処理できなかったリストを処理します。

    [IN_A4-6]
=GetJsonList(A3,0)
=GetJsonList(A3,1)
=GetJsonList(A3,-1)

[OUT]
{"code":1,"name":"北海道"}
{"code":13,"name":"東京都"}
{"code":47,"name":"沖縄県"}
    画像

    ６．自作関数３：通信関係

    　API接続のためのサンプルとして下記サイトを利用しました。

    ６－１．HTTPクライアント（GET）

    　PythonのRequestsと同じことが出来るような関数を作成します。

    　各関数は下記の通りです。注意点としてPOSTメソッドはJSONデータを送付するためヘッダー形式は”application/json; charset=UTF-8”にしました。

    [In]※GETメソッド
' HTTPにGETメソッドを送信して結果を得る
Function GetHttp(url) As String
    Dim httpObj As Object, s As String
    ' URLのページを開く
    Set httpObj = CreateObject("MSXML2.XMLHTTP")
    httpObj.Open "GET", url
    httpObj.setRequestHeader "Content-Type", "text/plain"
    httpObj.send
    ' 終了まで待機
    Do While httpObj.readyState <> 4
        DoEvents
    Loop
    ' HTTPのステータスコードが200ならば成功
    If (httpObj.Status = 200) Then
        s = httpObj.responseText
        GetHttp = "" & s
    Else
        GetHttp = ""
        Debug.Print "エラー:" & httpObj.statusCode
    End If
End Function

    　使用方法はGetHttp(<APIのエンドポイントURL>)です。

    [IN_CELL]
=GetHttp("https://jsonplaceholder.typicode.com/posts/1")

[OUT]
{
  "userId": 1,
  "id": 1,
  "title": "sunt aut facere repellat provident occaecati excepturi optio reprehenderit",
  "body": "quia et suscipit\nsuscipit recusandae consequuntur expedita et cum\nreprehenderit molestiae ut ut quas totam\nnostrum rerum est autem sunt rem eveniet architecto"
}

    ６－２．HTTPクライアント（POST）

    　上記と同様にPOSTメソッドも作成します。

    [In]※POSTメソッド
Function PostHttp(url, json) As String
    '--------------
    'POST実行
    '--------------
    Dim xmlhttp As Object
    Set xmlhttp = CreateObject("msxml2.xmlhttp")
    xmlhttp.Open "POST", url, False
    xmlhttp.setRequestHeader "Content-Type", "application/json; charset=UTF-8" '送信形式をJSONに設定
    xmlhttp.send (json) 'JSONデータをPOST

    '--------------
    '応答取得
    '--------------
    Dim retCd As String
    retCd = xmlhttp.Status    '結果コード取得
    
    If retCd <> 200 Then
        Debug.Print "error:" & retCd
    Else
        Dim retHtml As String
        retHtml = StrConv(xmlhttp.responseBody, vbUnicode, 1041)    '結果HTML取得：戻り値JSON
        PostHttp = retHtml
    End If
End Function

    　使用方法はPostHttp(<URL>, <Jsonデータ>)です。

    [IN_CELL]
=PostHttp("https://jsonplaceholder.typicode.com/posts/1", "{""id"":1}")

[OUT]
出力が出ないため追って調整
※使用方法はあってます

    ７．標準モジュールの転用

    　作成したモジュールを別のブックや他の人と共有するためにモジュールを保存(エクスポート)／読み込み(インポート)します。

    ７－１．ファイル保存：エクスポート

    　モジュールを右クリックして「ファイルのエクスポート」を選択するとbas拡張子のモジュールファイルを保存できます。

    画像

    ７－２．モジュール使用１：インポート

    　新規ブックにモジュールをインポートしてみます。新規ブック再生して「Alt+F11」でVBEを開き”VBAProject (<ファイル名>)”を右クリックして「ファイルのインポート」を選択します。
    　ウィンドウが立ち上がるのでエクスポートしたbasファイルを選択するとモジュールが挿入されます。

    画像

    ７－３．モジュール使用２：Excelアドインの利用

    　２つ目のモジュール利用方法としてアドインの利用があります。手順としては下記の通りです。注意点として自分で利用する場合は全ブックで使用できますが、他人とは直接アドインを共有できないため手順2~4を各自で実施してもらう必要があります。

    【アドイン使用の手順】
    １．ほしいマクロを作成(VBAコードを記載)する。
    ２．Excelブックを拡張子「Excel アドイン (*.xlam)」で保存する。
    　ー＞自動で指定箇所に保存されます(下記コードブロック参照)
    ３．「開発」タブー＞「アドイン」から保存したファイル名のアドイン選択
    ４．記載したマクロを使用

    [AddInnのファイルパス]
C:\Users\KIYO\AppData\Roaming\Microsoft\AddIns
    画像

    参考資料・参考記事

    あとがき

    　全部は書ききれないのでゆっくり更新予定

     
     

    KIYO

     
     
    普段は製造業で企画/開発/設計しております。記事はプログラミング・機械学習、IoT関係の記事をメインで作成し、なるべく１つの記事で知りたいことを網羅していきます。内容は学術的より実装・アウトプット（ほしくなるもの）を重視して作成しています。 面白そうな仕事があればやりたいです！

    あなたへのおすすめ