見出し画像
Photo by golchiki

VBAの基礎について

YUTO

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). 入門者のＥｘｃｅｌ ＶＢＡ 初めての人にベストな学び方. 講談社.

この記事が気に入ったらサポートをしてみませんか？