Excel VBA の覚え書き 2018年度版

初版作成  2018.2.10
最終更新  2019.04.15


◆◆ プログラムを組むには ◆◆

ファイル --- オプション --- リボンのユーザー設定

右側の「開発」にチェックを入れる。


◆◆ Visual Basic Editor ◆◆

Excel でプログラムを開発するための環境。

開発 ---「コード:Visual Basic」で起動する。

挿入 --- 標準モジュール

で標準モジュールを挿入し、その中にプログラムを書く。
1 つのプログラムは以下の形式を持つ。

Sub program_name()


End Sub

Excel では「プログラム」のことを「マクロ」あるいは「プロシージャ」
と呼ぶ。

(注意!)
標準モジュールを切り替えるときは「ダブルクリック」
1995 年頃に作成されたインターフェースであるため、
今とは感覚が異なることがある。

(注意!)

Sheet1(Sheet1) の中にプログラムを書くと、そのシートのセルしか
アクセスできない。
標準モジュールの中に書く。


◆◆ xlsm ファイル ◆◆

Excel ファイルの拡張子は以下の 2 つがある。

  xlsx:シートだけを含む
  xlsm:シートとマイクロの両方を含む。

xlsx でセーブするとプログラムがセーブされないので注意!!!

  xlsm:複数の標準モジュールを含むことができる
  標準モジュール:複数のプログラムを含むことができる


◆◆ コメント文 ◆◆

' より右側はコメント


◆◆ 継続行 ◆◆

行末に _ を置くと、続きを次の行に書くことができる。


◆◆ エラーなどのトラブルの対処 ◆◆

・無限ループで終わらない

  Esc キーで終了する。それでも終わらない場合は
  残念ながらタスクマネージャから Excel を終了させるしかない。

・For に対する Next がないというエラーが出るが Next はある

  End If を忘れたときにこのエラーが出る。なぜかは謎。


◆◆ セルに値を代入する ◆◆

Range("A1").Value = 10
Range("A" & i).Value = 10
Range("A" + CStr(i)).Value = 10

Cells(i, j).Value = "abc"
Cells(i, "B").Value = "abc"

ただし、.Value は省略可


◆◆ 変数 ◆◆

宣言なしに用いることができる。
そのとき Variant 型になる。

モジュールの先頭に

Option Explicit

と書くと、宣言しないとエラーになる。
宣言するときは

Dim a As Integer    2バイト整数 -32768 〜 +32767 までしか入らない
Dim b As Long       4バイト整数
Dim c As Double     8バイト実数
Dim d As String     文字列
Dim d As Variant    何でも入る。
Dim e               型を省略すると Variant

(注意!)

Dim a, b As Long

とすると、a は型を省略したので Variant になる。
両方とも Long にしたいときは、

Dim a As Long, b As Long

と書く。


◆◆ セルに代入できるもの ◆◆

・数値
・文字列

「セルの中に入っているものの形式」と「右クリック --- 表示形式」は関係ない。
「右クリック --- 表示形式」は表示するときの見え方を指定するもので、
セルの中の内容には影響を及ぼさない。

Range("A1") = abc

とするとどうなるか?
何も入らない。

abc という名前の変数の内容を入れようとしている。
デフォルトではバリアント型変数 abc の値は "" (空) なので
空が代入される。

"123" という文字列をセルに入れるのはバグの元
罠にはまる可能性のあることはしない。

Excel は自動型変換の機能を持っているので、

Range("A1") = "12"

としたとき、

Range("B1") = Range("A1") + 10

はエラーとならずに B1 に 22 が代入されるが、
たちの悪いバグを引き起こすおそれがある。


◆◆ 型変換 ◆◆

num は整数型、str が文字型のとき

   str = CStr(num)
   num = CLng(str)

文字型 ---> 数値への型変換の関数

   a = CInt(str)    ' 2バイト整数
   b = CLng(str)    ' 4バイト整数
   d = CDbl(str)    ' 8バイト実数
   e = Val(str)     ' 万能型 ただし使用は推奨されない


◆◆ イミディエイトウィンドウ ◆◆

プログラム開発にはなくてはならないウィンドウ
閉じてしまったときは 表示 --- イミディエイトウィンドウ

使い道は 2 つある。

1. イミディエイトウィンドウに表示する

    Debug.Print "a = " & Range("A1")

のように書くとイミディエイトウィンドウに表示する。
+ あるいは & は文字列を接続する。+ は文字列同士しか接続できないが、
& は数値と文字列を接続すると、数値は文字列に自動的に変換される。

& と + 以外に ; や , も使えるが、古い Basic との互換性を
保つためにあるので(本当か?)使わない方が無難。

2. ダイレクトコマンドを入力する

イミディエイトウィンドウ内にカーソルを持って行き、
命令を入力すると、その命令を実行可能

ex. プログラムにバグがあり、実行中に止まった

イミディエイトウィンドウ内でコマンドを打つことで
その時点での変数の内容を表示させることができる。

? a  あるいは print a で変数 a の内容を表示。


◆◆ For ループ ◆◆

For i = 1 To 10

Next i

For i = 10 To 1 Step -1

    Exit For

Next i


◆◆ If ◆◆

If 条件式 Then

Else

End If


If 条件式1 Then

ElseIf 条件式2 Then

Else

End If

複数の条件を書くときは

If 条件式1 And 条件式2 Then

If 条件式1 Or 条件式2 Then

うっかり、

If a = 3 And 4 Then

とすると、4 という条件式は常に成立するので、
a が何であっても条件が成立してしまう。


◆◆ while ループ ◆◆

Do While 条件式

   Exit Do

Loop

無限ループの場合は条件式として True を入れる。


◆◆ セルの色 ◆◆

Range("A1").Interior.Color = RGB(255,255,0)

RGB は B * 256 * 256 + B * 256 + R の値を返す関数

塗りつぶし無しのときは

Range("A1").Interior.Pattern = xlNone

◆◆ データが入っている最後の行を取り出す ◆◆

Sub get_last_row(sname, column, last_row)
'
'   入力 sname     シート名
'         column    列名  ex. "A"
'   出力 last_row  最後の行
'
    last_row = Sheets(sname).Range(column & "65536").End(xlUp).row

End Sub

Range("A65536").End(xlUp) は
「A65536 から上向きに検索し、最初に見つけた空でないセル」
を意味する。

A1 から下向きに検索し、最初に見つけた空セルの手前の行
は以下のように取得する。

last_row = Sheets(sname).Range("A1").End(xlDown).row

(注意!!!)
A1 のみにデータが入っており、A2 以下は空白のとき、
last_row = 1 とはならず、last_row = 1048576 となる。
1 個しかデータ入っていない場合は使えない。2 個以上のデータが
必要。


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

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


◆◆ オブジェクト変数の使い方 ◆◆

Dim a As Object, c As Object
Dim i

Set a = Range("A1:C5")

i = 1
For Each c In a
    c.value = i
    c.Interior.Color = RGB(255,200,255)
    i = i + 1
Next c

・オブジェクト変数への代入は Set が必要。
・Range 以外に  Set a = Selection  でも可
・a, c は Range オブジェクトなので以下でも可。
  Dim a As Range, c As Range

For Each c In a で Range a に含まれる個々のセルを
取り出すとき、セルの順序は

   A1 B1 C1 A2 B2 C3....

の順に並ぶ。


◆◆ セルのクリア ◆◆

Cells.ClearContents   セルの中を空にする
                      サイズ(高さ・幅)、塗りつぶし、罫線はそのまま
Cells.Clear           上に加えて塗りつぶしをなし、罫線を消す
Cells.Delete          全てデフォルトにする(シートの新規作成と同等)


◆◆ 変数名 ◆◆

たとえば name という変数名を使うと、1 文字目が大文字に
なり Name となる。row, column も同様である。

    Dim name

と宣言すると、name のままである。

標準モジュールではなく Sheet1(シート名) の中に
プログラムを書き、

    Name = "abc"

とすると、シート名が "abc" になる。1 文字目が大文字になる
変数名は、Excel があらかじめ定義している変数のようである。

使わない方が無難であると思われる。

Dim で宣言してから使うと、ユーザー定義変数として扱われる。


◆◆ 列、行の設定 ◆◆

Rows(3).Interior.color = RGB(255,255,220)
Rows("3:5").RowHeight = 50

Columns(3).ColumnWidth = 10
Columns("B").ColumnWidth = 10
Columns("A:C").ColumnWidth = 8