Excel 2013 VBA　覚え書き（環境設定、基本命令）

最終更新　　2015.4.16

◆◆ VBA を使うための準備 ◆◆


Excel 2013
「ファイル」→「オプション」→「リボンのユーザー設定」の右半分の欄
の「開発」にチェックを入れる。

「開発」→「コード：Visual Basic」で Visual Basic for Applications
のウィンドウが表示される（以下 VBA のウィンドウと表記する）。

◆◆ VBA エディタと Excel の関係 ◆◆


・VBA ウィンドウのプロジェクトエクスプローラが概念構造を表している。

・Excel の 1 ファイル ( *.xlsm ) が 1 つのプロジェクトに対応する。

・1 つのプロジェクトの中に Microsoft Excel Objects と
　標準モジュールが並列の関係で存在する。

・標準モジュールは 1 つ以上のモジュールファイルを含む。
  1 つのモジュールは複数のマクロを含むことができる。

・マクロは Sheet? や ThisWorkbook にも書ける。
  しかし、Sheet? や ThisWorkbook の中に書いた関数は他のモジュールから
  呼ぶことができない。
　ゆえに、普通のプログラムは標準モジュールの中に書く。
  Sheet? は Sheet に発生したイベントを処理する関数を書く。
  ThisWorkbook は Book を開いたときに自動実行するマクロを書く。

◆◆ Visual Basic for Applications の使い方 ◆◆


＜ 画面構成 ＞

・「表示」→「イミディエイトウィンドウ」でイミディエイトウィンドウが表示される。
　このウィンドウ内で命令を書いて enter を押すと即実行される。
　プログラム実行中にエラーが発生して「デバッグ」を選んだとき、
　イミディエイトウィンドウ内で「? 変数名 ↓」と打つと変数の内容が表示される。

・「表示」→「プロジェクトエクスプローラ」でプロジェクトエクスプローラ画面を表示

＜ モジュール ＞

・「挿入」→「標準モジュール」で標準のジュールを新規作成
・「ファイル」→「Module1の開放」でモジュール1 を削除

・モジュールを選択するとき「ダブルクリック」する。「シングルクリック」ではない。

＜ エディタ ＞

・ヘルプが表示されている状態 (ex. dim a as in と打つ) は tab で補完

・「表示」→「ツールバー」→「編集」のツールバーの中に、選択領域を全て
　コメントアウトするボタンがある。

・領域を選択して tab を押すと、その領域全体に tab が適用される

・右端のスクロールバーの「▲」のすぐ上の部分を下へドラッグすると、
　マルチウィンドウ

・モジュールが表示されている領域の左下のボタンで
　「モジュール全体を表示」「一つのプロシージャのみを表示」を切り替える

＜ 実行 ＞

・「実行」→「sub/ユーザーフォームの実行」で、そのときカーソルがある
　マクロを実行。

・無限ループに陥ったときなど「ESC」でマクロの実行を中断する

◆◆ セルの指定方法 ◆◆


Range("A1") = 123       数値
Range("A2") = "abc"     文字列

Range("A" & i) = 123         行を変数で指定
Range("A" + CStr(i)) = 123   + で連結するときは数値を文字列に変換するための CStr が必要

Cells(i, j) = 123       i は行  j は列
Cells(i, "B") = "acd"   列は文字列で指定してもよい。

◆◆ 変数の型とスコープ ◆◆


・大文字小文字は区別しない。

・主な型は以下の通り

  Variant     数値、文字列何でも入る
  Integer     2 バイト整数  -32768 〜 32767
  Long        4 バイト整数  -32768 〜 32767
  Double      8 バイト実数
  String      文字列

・変数の宣言

　デフォルトで宣言不要　　宣言しないとき Variant 型

　モジュールの先頭（declaration 領域）で Option Explicit と書くと、
　全ての変数に宣言が必要となる。

・宣言の方法 (要注意！！！)

    Dim a As Integer, b As Double
    Dim c, d As Long
        ~~ <----- c は Long ではなく Variant

  各変数名の後に As Integer のような型が必要
  As 型名  を省略したとき As Variant を省略したと見なされる。

  上の例のとき、C 言語のように c は Long ではない。
  As Variant が省略されていると見なされ、c は Variant 型となる。

　変数の宣言場所は、各マクロの先頭でなくてもよい。

・定数

　　Const i As Integer = 12

・グローバル変数

　モジュールは C 言語で言えば一つのファイルに相当する。
　グローバル変数はモジュールの先頭（declaration 領域）で宣言する。

　declaration 領域で Dim a As Integer のように宣言した
　グローバル変数はそのモジュール内のみで共有される。
　デフォルトで Private である。

　モジュールにまたがるグローバル変数は Public a As Integer と宣言する。

　Module1 の中で Public a As Integer と宣言すると、Module2 の中でも
　変数 a が使える。

　逆に Module2 の中でも Public a As Integer と宣言すると、
　Module1 の a とは別の変数として扱われるので要注意。C とは異なる。
　上記のような場合、Module1 で宣言された変数 a を使うには Module1.a
　と表記する。

・Static a As Integer のように宣言すると、その変数の値はマクロの実行が
　終わっても保持されるらしいが、初期化されるタイミングがややこしいので、
　使わないほうが良いと思う。

・配列の宣言

  Dim a(1 to 10) As Long のように上限と下限を指定するのがお勧め。
  Dim a(10) と宣言すると、デフォルトでは a(0) 〜 a(10) の 11 個が使える。
　配列の下限を指定するには option base

・配列のサイズを動的に決めたい場合

　    Dim buf()
　    Redim buf(1 to row_count, 1 to column_count)

◆◆ ループ ◆◆


For i = 10 to 1 Step -1

   Exit For

Next i

Do While i < 10

    Exit Do

Loop

Do While True

    Exit Do

Loop

Do

    Exit Do

Loop While i < 10

◆◆ if 文 ◆◆


If a = 1 And b = 3 Then
    x = "A"
ElseIf c = 1 Or c = 2 Then
    x = "B"
Else
    x = "C"
Endif

◆◆ 継続行 ◆◆


スペースとアンダーバーを右端に置く

◆◆ goto 文 ◆◆


   goto LABEL

   LABEL:

◆◆ サブルーチン（マクロ）の宣言とスコープ ◆◆


Public Sub test1()

End Sub

Private Sub test2()

End Sub

識別子 (Public or Private) を省略したとき、デフォルトは public。

test1() は他のモジュールから参照できるが、
test2() は同一モジュール内からしか参照できない。

Sheet1, ThisWorkbook, UserForm1 の中に書いた関数は他のモジュールから
呼ぶことができない。Public と書いても駄目である。

◆◆ サブルーチンの途中で抜ける ◆◆


Sub Sub1(i As Integer, j As Integer)
    i = i + 1
    j = j * 2
    Exit Sub    '  <---- 途中で関数を抜けるとき
    .......
End Sub

◆◆ サブルーチン（マクロ）の引数 ◆◆


・参照渡しと値渡し

引数は、参照渡しがデフォルト

Call sub1(i, j)

あるいは

sub1 i, j

で参照渡し。Call を付けない場合、

sub1 (i), j

とすると、i のみ値渡し。

sub1 (i), (j)

とすると i, j ともに値渡し。

・配列の渡し方

Dim array(1 to 10) As Long
Call Sub2(i,array())

Sub Sub2(i as Long, array() As Long)

・型の一致

呼び出し側と、受け手側の型が一致しないとエラーが発生することがある。

----------------

Dim a       ' <---- 暗黙の型宣言により Variant 型
call sub1(a)

----------------

Sub sub1(i As Integer)

End Sub

----------------

は Variant 型を Integer で受けるので、エラーが発生する。

ただし、サブルーチン側が Variant 型で受けるときは、
呼び出し側の変数の型は何であってもよい。

◆◆ 関数 ◆◆


Function func1(i As Integer) As String
    If i = 0 Then
        func1 = "Err"
        Exit Function
    End If
    If i > 3 Then
        func1 = "A"
    Else
        func1 = "B"
    EndIf
End Function

◆◆ 文字と数値の変換 ◆◆


str = CStr(num)
num = Val(str)

Val ではなく CInt, CLng, CDbl を使ってもよい。

◆◆ 文字列を扱う関数 ◆◆


Trim(str)      左右の空白を切り落とし、途中の2個以上の連続空白は1個にする
Right(str,3)   右側 3 文字を取り出す
Left(str,2)    左側 3 文字を取り出す
Mid(str,4,2)   4 文字目から 2 文字取り出す
Len(str)       文字列の長さ
InStr(str,"2") 文字列 "2" が含まれるとき何文字目か　見つからないとき 0
CStr(2)        数値 2 を文字列 "2" に変換
Space(12)      半角空白 12 個

Dim a as Variant
a = Split(str, ",")
   a(0), a(1), .... に分割された文字列が入る。
   a は Variant 型でないとエラーになる。
   Split を実行すると a は自動的に配列になるようだ。

◆◆ 領域の指定方法 ◆◆


Range("A5").Select
Range("A5:B8").Select
Rows("4:4").Select
Columns("E:E").Select

Selection.Value = "abc"
Selection.Interior.Color = RGB(255, 255, 150)

◆◆ ワークシート関数の使用 ◆◆


VBA で用意されている関数と Excel が用意する関数は異なる。
Excel の関数を VBA で使うときは、以下のように書く。

Range("A1") = WorkSheetFunction.RandBetween(1, 20)

セル A1 に「 = RandBetween(1, 20) 」と書いて enter を押すのと同じ

◆◆ 乱数 ◆◆


Randomize 命令で乱数系列を設定する。
Rnd() 命令で乱数を取得する。

◆ 乱数系列の設定

Randomize   　　   システムタイマーから取得した値を seed として設定
Randomize(seed)    seed を設定

◆ 乱数の取得

a = Rnd(seed)   乱数値は 0 以上 1 未満
                1〜6 の乱数が得たいときは Int(Rnd() * 6) + 1

seed < 0   seed を設定
seed = 0   直前の値を返す
seed > 0 あるいは引数省略
　         直前に生成された乱数を seed とする

Excel の乱数の仕組みについては
このサイト が詳しい。

Randomize
a = Rnd
b = Rnd
c = Rnd
.....

とするのが一般的な使い方である。

しかし、偏りが激しい。1〜6 までの乱数を 120 回発生させたところ
1 が 12 回 6 が 26 回というケースがあった。
Rnd の前に引数なし Randomize を毎回実行すると偏りはなくなる。

毎回決まった乱数系列が欲しいときは

a = Rnd(-1.2)
b = Rnd
c = Rnd
.....

のように書く。

Randomize(1.2)
a = Rnd
b = Rnd
c = Rnd
.....

では同じ乱数系列にならない。Randomize(1.2) は現在の seed と
引数 1.2 をかけあわせて新しい seed を作る。それに対して
Rnd(-1.2) は seed を -1.2 のみから生成する。

◆ Randbetween の使用

実は、ワークシート関数 Randbetween を使った方が簡単である。

Range("A1") = WorksheetFunction.RandBetween(1, 20)
              1 から 20 までの乱数を発生

◆◆ ライブラリ的な使い方 ◆◆


他のプロジェクトに所属するモジュールを使用するときは
VBA ウィンドウにおいて「ツール」→「参照設定」で使用したい
プロジェクト名にチェックを入れる。

プロジェクト名のデフォルトは VBAProject である。参照先の
ファイルは見分けやすい名前にするため、「ツール」→「VBAProject の
プロパティ」でプロジェクト名を変更しておいたほうが良い。

参照先ファイルの path を変更すると参照できなくなる。

◆◆ ボタンの作成 ◆◆


「開発」→「コントロール：挿入」→「ボタン（フォームコントロール）」

移動やサイズ変更のとき、alt を押すと、セル罫線に吸い付く。


ボタンを押したときに実行されるマクロを登録するとき、

　　　「マクロシートに対する参照を指定してください」

というエラーが出ることがある。
マクロ名が a2, ab3, abc44 などのとき、このエラーが発生する。

ネットで調べると、セル名と重複するマクロ名はだめらしい。
Excel の列名は 3 桁まではありうる（4 桁は大丈夫）ので、

   ローマ字 1 〜 3 字 ＋ 数字

はダメである。

   toi1 ---> toi_1

のように変更すると OK である。

◆◆ ctrl キーにマクロを割り当てる ◆◆


ctrl + 1 で横の 2 つのセル結合
ctrl + 2 で縦の 2 つのセルを結合
を実現するプログラムは以下の通り。
Module1 の中に記述する。

Sub auto_open()

  Application.OnKey "^1", "Ketsugou_yoko"
  Application.OnKey "^2", "Ketsugou_tate"

End Sub

Sub Ketsugou_yoko()

    Row = Selection.Row
    Column = Selection.Column

    cha1 = Chr(Asc("A") - 1 + Column)
    cha2 = Chr(Asc("A") - 1 + Column + 1)

    Range(cha1 + CStr(Row) + ":" + cha2 + CStr(Row)).Select
    With Selection
        .MergeCells = True
    End With

End Sub

Sub Ketsugou_tate()

    Row = Selection.Row
    Column = Selection.Column

    cha = Chr(Asc("A") - 1 + Column)

    Range(cha + CStr(Row) + ":" + cha + CStr(Row + 1)).Select
    With Selection
        .MergeCells = True
    End With