わさっきhb

大学（教育研究）とか ，親馬鹿とか，和歌山とか，とか，とか．

VBAについて，知っておくとよいこと2

授業 computing

　VBAの書き方について，知っておくとよいことを挙げていきます．「変数」「関数」「引数」などの用語は他のプログラミング言語で既習とします．
　「なぜVBAでプログラムを書くのか」というと，「Excelの上で動かしたいから」というのがシンプルな答えです．「Excelで操作するのでは不十分か」という問いには，「あらかじめプログラムを書いておけば，マクロから簡単に実行できる（他の人にも実行してもらえる）」というのが基本となる答え方です*1．それに加えて，うまくプログラムを書けば，ワークシートの選択（後述のActivate）やセルの並べ替えをすることなく，ワークシートの内容を一切変えずに，セルから値を取り出して計算し，グラフを作ることができ，これがVBAの意義の一つと言っていいでしょう．
　といったところで文法やコード例を紹介します．マクロとして実行できるのは，引数のないSubプロシージャに限られます．例えば次のものです．この場合，マクロの名前は「Macro1」です．

Sub Macro1()
    With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With
End Sub

　「Sub マクロ名(引数)」で始まって「End Sub」で終わる部分までを，Subプロシージャといいます（他に「Functionプロシージャ」というのもあります）．「With」から始まって「End With」で終わる，というのも見てとれますが，このWithは最初から使いこなすものではない（意味などは「ピリオド」について，知っておくとよいことをご覧ください）ので，代わりにもっと分かりやすいコードに変更します．

Sub Macro1()
    Range("A1:C3").Value = "VBA"
    Range("A1:C3").Font.Color = vbBlue
End Sub

　上記に対し，Excel上でマクロとしてMacro1を実行すると，ワークシート上のA1からC3まで，3×3で9個のセルが（それまで何が書かれていても，または空白のセルであっても），すべて青色の「VBA」になります．
　Rangeから始まる行は，次のように読むことができます．

  • 「Range("A1:C3").Value = "VBA"」は，「A1:C3の範囲(Range)のセルの値(Value)を，"VBA"という文字列にしなさい」．
  • 「Range("A1:C3").Font.Color = vbBlue」は，「A1:C3の範囲(Range)のセルのフォント(Font)の色(Color)を，青色(vbBlue)にしなさい」．

　vbBlueもそうですが，「vb」から始まるものは，VBAの定数です．色に限らず，以下で見ることができます．

　ところで，Rangeは日本語でいう「範囲」のことなので，コロンを使用して範囲指定にしましたが，コロンなしでRange("A1")と書くこともでき，これはA1セルのみを表します．Range("Sheet2!A1:C3")と書けば，Sheet2という名前のワークシートの，A1からC3までになります．Excelの（「=」から始まる式での）セル参照の書式を，Rangeの引数に使うことができます．ただし二重引用符を忘れないようにしましょう．
　「"VBA"」と二重引用符を付けた場合と，「vbBlue」と二重引用符を付けない場合で，扱いが変わることも，VBAに限らずプログラミングにおいて重要です．上のコードでもし，「Range("A1:C3").Value = VBA」だったら，セルに格納するのは，VBAという名前の変数または定数の値になるのです…が，そのような変数も定数もないのでエラーです．また「Range("A1:C3").Font.Color = "vbBlue"」と書いたら，セルの色が"vbBlue"という文字列で，これまたNGなのです．
　以下では「Sub ～」と「End Sub」を省略します．セルへの格納の次に，知っておくとよいのは，Cのprintf関数に相当する出力処理です．決まった文字列や実行時点の変数の値を，ユーザに見せる方法でもあります．VBAでは，MsgBox関数を使うといいでしょう．次のコード*2を実行すると，ダイアログ（小さなポップアップウインドウ）が出てきて，中央には「Hello, World!」と表示されます．OKボタンを押すことで，この関数の処理を終えます．

MsgBox ("Hello, World!")

　MsgBox関数の引数には，式を書くことができます．例えば次のように書けば，A1セルのフォント名（「游ゴシック」など）が表示されます．

MsgBox (Range("A1").Font.Name)

　変数yearの値に「年」をつけて表示させたい場合には，次のように書きます．

MsgBox (year & "年")

　この「&」は，連結演算子と呼ばれます*3．変数yearの保持する値が整数であっても，強制的に文字列となり（ただし変数の中身は変わりません），"年"という文字列と連結します．例えばyearの値が2020（という整数値）のとき，「year & "年"」は，"2020年"になってくれます．
　文字列どうしであれば，それらの連結には + も使用できますが，VBA（あるいはBASIC言語）の特色として，& というのを知っておくといいでしょう*4．なおJavaやJavaScriptでは，文字列連結演算子は + のみで，オペランドは文字列以外でもかまいません．Cではそのような演算子はなく，文字列を連結したければ，strcatやsprintfといった文字列関数を使うことになります．
　それと，VBAでは文字列定数は必ず二重引用符で囲みます．引用符（「'」，アポストロフィ）は，文字や文字列を表すのには使用できず，VBAではこの文字から行末までをコメントとみなします．
　連結演算子は，MsgBox以外にも応用が利きます．「Range("E" & i)」という式を書き，変数iの値を（後述のFor文で）4から8まで変化させれば，E4からE8までのセルを，For文の処理でアクセス（読み書き）できるようになります．
　VBAの制御構造（分岐，反復）についてはコード例をいくつか示します．

If result = 0 Then
    MsgBox ("結果はゼロ!")
Else
    MsgBox ("結果は非ゼロ!")
End If
If hand = 1 Then
    MgsBox ("最初はグー")
ElseIf hand = 2 Then
    MgsBox ("最初はチョキ")
Else
    MgsBox ("最初はパー")
End If
Dim x As Integer
Dim y As Integer
y = 0
For x = 1 To 10
    y = y + x
Next x
MsgBox (y) '「55」を表示

　Cのwhile文に対応する繰り返しも，書けます．具体的には「Do While 条件式」で始めて「Loop」で終えます．条件式を満たす間(while)，処理を行うというものです．WhileをUntilに置き換えると，条件を満たすまで(until)，処理を行います．「End While」や「End Until」でないのは，ちょっと面白いところですが，Cのdo文（do～while）に対応するVBAの書式が「Do（だけの行）」から「Loop While 条件式」までになり，「While（またはUntil） 条件式」が最初の行か最後の行かで書き分けられます．
　変数宣言は，上記のうち「Dim x As Integer」「Dim y As Integer」が該当します．DimとAsは固定で*5，間に書くのが変数名，Asの後ろが型名です．実数型ならDouble，文字列ならStringで，他の例はVBA基本(変数のデータ型)をご覧ください．
　配列は「Dim customer(4) As String」のように宣言し，こう書いたら，customer(1)からcustomer(4)までに，文字列を格納・参照することができます．プロシージャを有効範囲とするなら，変数を宣言して使用すべきですが，計算の経過や結果を，Excel上に残しておきたければ，「Range(セル).Value = 値」によりワークシート上に書くというのも，選択肢の一つとするといいでしょう．
　VBAでは代入も（比較の）等号も，「=」です．ノットイコールは「<>」です．べき乗の演算子は「^」です*6．剰余は「Mod」を書きます（前後に空白を置きます）．
　さて，次の式は，何を表すでしょうか．

Int (Rnd * 6) + 1

　IntもRndも，関数です（自作と区別するため「VBA関数」とも呼ばれます）．Intは実数値をとり，整数にした値を返します．Rndは引数のない関数で，0以上1未満のランダムな実数値を返します．ですのでこの式により，1から6までの整数のうち一つを，得ることができます．サイコロを振るのにちょうどよい式です．
　ワークシート関数というのも，あります．VBAの式（ですので使う場合には，結果を変数に格納するなどします）と，Excelの式（セルで「=」のあとに書くもの）の対応例を3つ，挙げておきます．

WorksheetFunction.Average(Range("H2:H49"))
' AVERAGE(H2:H49) ... 平均
WorksheetFunction.Max(Range("F1:F20"))
' MAX(F1:F20) ... 最大値
WorksheetFunction.Correl(Range("B1:B24"), Range("D1:D24"))
' CORREL(B1:B24,D1:D24) ... 相関係数

　VBAで書くことのできる「Excelの操作」のうち，授業で取り上げたものを解説します．まず「Worksheets("Sheet2").Activate」を実行すると，処理するワークシートの指定を行います．マウスなどで下段の「Sheet2」を押すという操作が，プログラムだとこの1行になります．
　グラフの配置は，次のように書くといいでしょう．

With ActiveSheet.ChartObjects.Add(200, 10, 300, 200).Chart
    .ChartType = xlColumnClustered
    .SetSourceData Source:=Range("A1:E2")
    .HasLegend = False
    .HasTitle = True
    .ChartTitle.Text = "科目平均点"
End With

　ワークシートの左上を原点として，右に200ピクセル，下に10ピクセル移動させたところから，幅300ピクセル，高さ200ピクセルの，棒グラフ(xlColumnClustered)のオブジェクトを追加(ChartObjects.Add)します．グラフにする対象は，A1からE2までの2行5列のセルで，A1からE1まではラベル（国語，数学など），A2からE2までに値が格納されているものとします．「.HasLegend = False」により凡例は記載せず，Titleを含む2行から，「科目平均点」というタイトルをグラフオブジェクト内に書くよう指定しています．
　「.ChartType = 」のあとに書く定数について，折れ線グラフならxlLine，散布図ならxlXYScatterです．散布図の場合，Rangeで指定する範囲にある値は，2次元座標平面上の値にします．また「.SeriesCollection(1).Trendlines.Add Type:=xlLinear」という行を入れると，回帰直線を乗せることになります．
　「:=」は，変数やオブジェクトへの代入ではなく，名前付き引数の指定となります．「.SetSourceData Source:=Range("A1:E2")」は，SetSourceDataメソッドの引数のうちSourceの値を指定します．Chart.SetSourceData メソッドのパラメーターに注意すると，以下のコードはどれも同じとなります*7．

.SetSourceData Source:=Range("A1:E2")
.SetSourceData(Range("A1:E2"))
.SetSourceData(Range("A1:E2"), xlRows)
.SetSourceData Source:=Range("A1:E2"), PlotBy:=xlRows
.SetSourceData PlotBy:=xlRows, Source:=Range("A1:E2")

*1:関連：[https://takehikom.hateblo.jp/entry/20160219/1455893999

*2:VBEで「msgbox("Hello, World!")」と打ち込んで，Enterキーを押しても，関数名はMsgBoxになり，関数名とカッコの間に空白が入ります．

*3:https://docs.microsoft.com/ja-jp/dotnet/visual-basic/programming-guide/language-features/operators-and-expressions/concatenation-operators

*4:もう一つ，VBAで使用することができ，BASIC言語に由来するものとして，マルチステートメントの「:」があります．

*5:Dimはdimension（次元）に由来します．

*6:この記号は，CやJavaScriptでは排他的論理和を求めます．

*7:手元の環境では，「.SetSourceData(Source:=Range("A1:E2") )」や「.SetSourceData(Source:=Range("A1:E2"), PlotBy:=xlRows)」は，エラーとなりました．