
VBAの基礎について
EXCELの操作を自動にする機能をマクロといい、それはVBA(Visual Basic for Applications)と呼ばれるプログラミング言語を用いて記述されます。今回は、その事について説明します。
VBAの始め方と保存方法
VBAを始めるには、タブに開発をいれてください。
(この方法はEXECLのバージョンによって少し異なるので各自調べてください)

入れたら、Visual Basicをクリックすれば、開発画面が表示されます。

Sheet1をダブルクリックすると、開発画面が表示されます。

以下が開発画面です。ここにマクロおよびVBAを記述します。

VBAで書いたプログラムを実行する際は、赤い四角で囲んだところをクリックするとできます。

なお、VBAを保存するときは、拡張子 .xlsm で保存してください。

マクロの書式について
マクロを記述する時、一番初めは以下のように記述します。
Sub マクロ名 ()
マクロの処理を記述
End Sub
マクロ名は自由に設定しても構いません。
メッセージボックスについて
メッセージボックスを表示させる時、処理内容に以下の事を記述します。 MsgBox "文字列"
Sub Message ()
MsgBox "Hello World!!"
End Sub
変数について
変数とは値を保持する箱のようなものです。その箱に数字もくしは文字列のどれかを入れるとき、箱には型と言われるものを以下のように宣言します。
Dim 変数名 As 型名
変数名は自由に設定し、型は目的に応じて使い分けます。
Dim a As Integer #これは変数aには整数が入る事の宣言です
Dim b As String #これは変数bには文字列が入る事の宣言です
型の種類について
型名 型指定 文字格納できるデータ
ブール型 Boolean TrueまたはFalse
バイト型 Byte 0~255までの整数
整数型 Integer -32,768~32,767の整数
長整数型 Long -2,147,483,648~2,147,483,647の整数
通貨型 Currency -922,337,203,685,477.5808 ~ 922,337,203,685,477.5807の固定小数点数
単精度浮動小数点数型 Single 負の値:約-3.4×10(38乗)~-1.4×10(-45乗)正の値:約1.4×10(-45乗)~1.8×10(38乗)
倍精度浮動小数点数型 Double 負の値:約-1.8×10(308乗)~-4.0×10(-324乗)正の値:約4.9×10(-324乗)~1.8×10(308乗)
日付型 Date 日付:西暦100年1月1日~西暦9999年12月31日時刻:0:00:00 ~ 23:59:59
文字列型 String 任意の長さの文字列
オブジェクト型 Object オブジェクト
バリアント型 Variant すべてのデータ
まずは、整数型と文字列から覚えるといいです。
変数を宣言したら、変数に数字が文字列を「=」を用いて代入します。ここで、「=」とは等号という意味ではなく、右辺を左辺に代入するという意味です。
a = 1
b = "あい"
ここで、文字列の場合はダブルクオテーションを付けてください、
Sub Message ()
Dim b As String
b = "数字"
MsgBox b
End Sub
制御文について
・if構文
条件式の真偽で処理を分け、書式は以下のようになります。
if 条件式 Then
真の時の処理
Else
偽の時の処理
Endif
ここで、条件式の比較記号は = ,> ,>= ,< ,<= ,<>を利用します。
(ここで条件式の中では=は等号という意味になります)
以下の例文は a+bが4より大きいのかを調べるものになります。
Sub Message ()
Dim a As Integer
Dim b As Integer
a=2
b=4
if a+b > 4 then
MsgBox “True”
else
MsgBox “False”
endif
End Sub
次の例文はaとbの結合が"福岡県北九州市"か調べるものになります。
Sub Message ()
Dim a As String
Dim b As String
a=“福岡県”
b=“久留米市”
if a & b = “福岡県北九州市” then
MsgBox “一致”
else
MsgBox “不一致”
endif
End Sub
・FOR構文
最終値になるまで、条件を繰り返す構文です。
For 変数名 = 初期値 to 最終値 step 変化量
処理内容
Next 変数
Sub Message ()
Dim a As Integer
a=0
For i=1 to 10
a=a+1
Next i
MsgBox a
End Sub
Sub Message ()
Dim b As Integer
b=5
For i=0 to 10 step 2
b=b+2
Next i
MsgBox b
End Sub
VBAでセルを操作する方法
オブジェクトについて
オブジェクトとは、セルやワークシートなどのExcel上にあるものの総称です。
そのオブジェクトには一例として以下の名前があります。
オブジェクトの種類 オブジェクト名
アプリケーション Application
ブック Workbook
ワークシート Worksheet
セル(その1) Range
セル(その2) Cells
もし、Cellsオブジェクトを使う場合は書式として
Cells(行,列)
と書きます。セル番地が3行2列の場合は
Cells(3,2)
と書きます。
プロパティについて
プロパティはオブジェクトの情報を取得・設定する命令文であり、セルに入力されているデータや文字の色、フォント、サイズ等といったセルの状態を設定します。
そのプロパティは一例として以下のものがあります。
プロパティの種類 プロパティの名前
データ Value
色 Color
罫線 Borders
このプロパティを使うと、オブジェクトの情報を取得することができ、以下の書式になります。
オブジェクト名.プロパティ名
MsgBox Cells(6,5).Value #E6セルの値をメッセージ表示
また、オブジェクトの情報を設定することもできます。
オブジェクト名.プロパティ名 = 設定値
Cells(5,3).Value = “abc” #C5セルの値をabcにする
また、収得と設定を組み合わせることもできます。
オブジェクト名1.プロパティ名1 = オブジェクト名2.プロパティ名2
Cells(4,1).Value = Cells(2,2).Value #A4セルにB2セルの値を代入する。
メソッドについて
メソッドとはオブジェクトを操作する命令文のことでオブジェクトの動作になります。
オブジェクトを操作することで、セルに対して削除・コピー・貼り付けなどを行えます。
そのメソッドは一例として以下のものがあります。
メソッドの種類 メソッドの名前
コピー Copy
切り取り Cut
貼り付け Paste
データの削除 ClearContents
このメソッドを使うと、オブジェクトを操作することができ、以下の書式になります。
オブジェクト名.プロパティ名
Cells(4,1).ClearContents #A4セルにあるデータを削除する
VBA関数について
VBA関数とは、設定した値から結果を吐き出す機能を持つものです。入力値を引数、出力値を戻り値といいます。書式は以下のようになります。
VBA関数名(引数)
また、引数を必要としないVBA関数もあり、その時は書式に引数を書く必要はありません。
VBA関数はたくさんありますが、一例として以下があります。他は各自調べてください。
VBA関数名 機能
Date 現在の日付を取得する
Year シリアル値から年のみを取得する
Len 文字数の文字数を取得する
Int 数値の整数部分のみ取得する
例文(文字数を返してみる)
Sub sample()
Dim a As String
a = "VBAのIE制御入門"
MsgBox "文字数:" & Len(a)
End Sub
例文(日付を返してみる)
Sub sample()
Dim a As Date
a = Date
MsgBox a
End Sub
また、VBA関数は自作することができ、それをユーザー定義関数といいます。
それは以下の書式で表され、関数名のほかに引数や戻り値のデータ型を指定し、戻り値の指定は関数名に続けて戻り値をイコールで結びます。
Function 関数名(引数名 As データ型) As 戻り値のデータ型
処理内容1
処理内容2
処理内容3
…
関数名 = 戻り値
End Function
ここで使った関数の戻り値を使いたい場合は、Subの方のマクロの中にて
変数 = 関数名
として記述します。
以下の例文はセルB2に記載された価格を関数へ渡し、その値を1.08で割ったものをPriceへ代入したものを表示したプログラムになります。ここで、PがセルB2に記載された価格が入る変数となります。
Function Offtax(P As Integer) As Integer
Offtax = P / 1.08
End Function
Sub 本体価格()
Dim Price As Integer
Price = Offtax(Range("B2").Value)
MsgBox "税抜きの本体価格は" & Price & "円です"
End Sub
簡単なNEMのエクスプローラを作る
NEMとはブロックチェーンの名称であり、ネイティブ通貨としてXEMが使われています。ここでは、EXCELを用いて、簡単なNEMのエクスプローラを作ります。
完成品は、以下のようになります。

これは、HeightボタンをクリックするとNIS1のブロック高がB3セルに表示され、アドレスをB6セルに入力しBalanceボタンをクリックするとアドレスの残高がB7セルに表示されます。
これはVBAを記述することで実行でき、全体的なコードは以下になります。
Sub nis1_height()
Dim JsonHeight As Object
Set JsonHeight = JsonConverter.ParseJson(urlaccess("http://go.nem.ninja:7890/chain/height"))
Cells(3, 2).Value = JsonHeight("height")
End Sub
Sub nis1_balance()
Dim jsonBalance As Object
Set jsonBalance = JsonConverter.ParseJson(urlaccess("http://go.nem.ninja:7890/account/get?address=" + Cells(6, 2).Value))
Cells(7, 2).Value = jsonBalance("account")("balance") / 1000000 & "xem"
End Sub
Function urlaccess(url As String) As String
Dim result As String
Set request = CreateObject("MSXML2.XMLHTTP")
request.Open "GET", url request.Send
Do While request.readyState < 4
DoEvents
Loop
result = request.responseText
Set request = Nothing
urlaccess = result
End Function
このコードについて説明をします。
その前に、EXCEL-VBAをダウンロードしてJSONconverterをEXCELに持ってくる必要がありますので、その方法を紹介します。
VBA-JSONを導入する
まず、VBA-JSONをGithubからダウンロードするために、まず以下のページに入ってください、
それから、Codeをクリックします。

それから「Download ZIP」をクリックすればダウンロードできます。

ZIPファイルとしてダウンロードされるので、そのファイルをクリックすれば、ファイルが解凍されて使えるようになります。

VBEのプロジェクトエクスプローラーにはいったら、この写真の部分にマウスを持ってきて右クリックをしてください。

次に、モジュールにマウスを持ってきて、右クリックし、ファイルのインポートをクリックしてください。

そしたら、[JsonConverter.bas]を選択して開いてください。

以下の状態になれば導入完了です。

この設定は、EXCELブック毎に必要になります
ここから、コードを書いていきます。
コードの内容について
まず、B2セルに「現在のNIS1ブロックHeightは?」、B5セルに「アドレスの残高は?」を入力します。
それから開発の方へ進んでVBAを記述します。
以下はブロック高を表示させるマクロであり、マクロ1とします。
Sub nis1_height()
Dim JsonHeight As Object
Set JsonHeight = JsonConverter.ParseJson(urlaccess("http://go.nem.ninja:7890/chain/height"))
Cells(3, 2).Value = JsonHeight("height")
End Sub
http://go.nem.ninja:7890/chain/height
は、NEMが提供してくれているAPIのアドレスであり、これを叩くと以下のようにNEMのブロック高が返ってきます。

以上のようなデータをJSONデータといいます。JSONとはJavaScript Object Notationの略称です。
具体的に、どんなJSONがあるのかは、以下のページで調べる事ができます。
Dim JsonHeight As Object
これは、Hightを求めるJSONのObject型として変数JsonHeightを定義することを意味します。
Set JsonHeight = JsonConverter.ParseJson(urlaccess("http://go.nem.ninja:7890/chain/height"))
これは、JSONconverterに入っている関数であるJsonConverter.ParseJsonを使い、引数としてurlaccess("http://go.nem.ninja:7890/chain/height")と書く事で、「http://go.nem.ninja:7890/chain/height」から収得できるJSONを分解して表示させたものを変数JsonHeightに代入することを意味します。ParseJsonとはJSONを分解して分かりやすくするという意味です。また、urlaccess()とはURLからJSONを返すことができる自作関数です。
Cells(3, 2).Value = JsonHeight("height")
これは変数JsonHeightにあるheightをCells(3, 2).Valueに表示させるというものになります。JSONでは以下のようになっているので、3行列目にブロック高3059695が表示されます。
{"height":3059695}
以下は残高を表示させるマクロであり、マクロ2とします。
Sub nis1_balance()
Dim jsonBalance As Object
Set jsonBalance = JsonConverter.ParseJson(urlaccess("http://go.nem.ninja:7890/account/get?address=" + Cells(6, 2).Value))
Cells(7, 2).Value = jsonBalance("account")("balance") / 1000000 & "xem"
End Sub
Dim jsonBalance As Object
これは、Balance(残高)を求めるJSONのObject型として変数JsonHeightを定義することを意味します。
Set jsonBalance = JsonConverter.ParseJson(urlaccess("http://go.nem.ninja:7890/account/get?address=" + Cells(6, 2).Value))
これは、「http://go.nem.ninja:7890/account/get?address=" + Cells(6, 2).Value」と書く事で、URLは「http://go.nem.ninja:7890/account/get?address=セル6.2に記載されたアドレス」となります。もし、セル6.2にコインチェックのウォレットアドレス「NA3MZM72LRCVRJYXLIXWHQDKNUMLHY6KFO253ICC」を記入した場合のURLは「http://go.nem.ninja:7890/account/get?address=NA3MZM72LRCVRJYXLIXWHQDKNUMLHY6KFO253ICC」となります。
それをJSONとして分解して表示させたものを変数jsonBalanceに代入することを意味します。
Cells(7, 2).Value = jsonBalance("account")("balance") / 1000000 & "xem"
今度は、このコードについて説明します。
実際に先ほどのアドレスをFirefoxを用いて見てみます。

このJSONデータを見ると、accountの中にbalanceと言う項目があるので、変数jsonBalanceの中身を参照するために「jsonBalance("account")("balance")」と書きます。ここで、XEMはμXEM単位として表記されるので、XEM単位にするために、1000000で割ることになります。そして、単位XEMを表記させたいので、最後に「& "xem"」と書きます。
もし、JSONデータをわかりやすく表示させたい人はFirefoxはオススメです。
生データの場合は以下のようになっていて、JSONの階層構造がわかりにくいです。

ここでは、トップ階層はmetaとaccountであり、その下の階層にadressやbalance等があり、それを視覚的にわかりやすく表示させてくれます。
ここで以下の自作関数であるurlaccessを説明します。
このコードはurlaccess("http://go.nem.ninja:7890/chain/height")が処理している事を記述したものになります。これを使う事で、URLからJSONを返すことができます。くわしい処理内容は難しいので省略します。このコードは汎用的に使っても構いません。
Function urlaccess(url As String) As String
Dim result As String
Set request = CreateObject("MSXML2.XMLHTTP")
request.Open "GET", url request.Send
Do While request.readyState < 4
DoEvents
Loop
result = request.responseText
Set request = Nothing
urlaccess = result
End Function
これはNEMだけでなく、webAPI全般に対して使えます。
例として、図書館に関するAPIを提供している以下のページを眺めてみてください。
https://calil.jp/doc/api.html
ここでは、NEMが提供しているAPIを「http://go.nem.ninja:7890/chain/height」から参照しています。
ここまで完成したら、ボタンを設置します。
ボタンを設置する
まず、開発タブへ進み、ボタンをクリックします。

それから、使うマクロを選択します。

すると、ボタンが設置されます。
そのボタンをクリックすれば、VBAが実行されます。
マクロが実行できない場合
もし、このEXCELが実行できない場合はVBEに進んで「ツール→参照設定」に進み、Microsoft scriping Runtimeがあるのかを確認してください。
(私が使っているのはMACなので、以下のように欠落と表示されています。これは、JSONがEXCELで使えないことを意味し、実行はMAC環境では残念ながら使えない事になります)

読んで欲しい本について
この記事を読んだら、以下の本を読んでさらに理解を深めて欲しいです。
立山秀利. (2012). 入門者のExcel VBA 初めての人にベストな学び方. 講談社.