ExcelVBAの使い方

はじめに

ExcelVBAは業務用処理にも使えるプログラミングである。Excel自体、セルを用いた計算を得意とし、データの可視性としてはかなり良い開発環境である。また、テキスト処理などにも向いている。保存画面で、ファイルの種類は[Excel マクロ有効ブック(*.xlsm)]で保存するとExcelVBAを記述したExcelファイルを保存できる。

プロジェクトエクスプローラ

タイトルに[プロジェクト - VBAProject]と表示されているウィンドウがプロジェクトエクスプローラである。[VBAProject(・・・.xlsm)]と表示されているツリーが表示されているが、トップは各Excelファイルを表す。

[Microsoft Excel Objects]フォルダにはExcelファイル内の各シートに対応するコードと、[ThisWorkbook]にはExcelブック自体に対応するコードを記述する。プロパティウィンドウの[オブジェクト名]を変更することで、分かりやすい名前でオブジェクトにアクセスできる。シート名と同じにすると良いだろう。

標準モジュールにはExcelブック内すべてのシートから参照可能なコードを記述できる。

ExcelVBAの基礎

変数型にはInteger、Long、String、Variantなどがある。Integer(整数)型は-32,768～32,767の範囲まで表現できるが、足りなくなる場合もある。代わりに-2,147,483,648～2,147,483,647の値を表現できるLong(長整数)型を使うことで予期せぬエラーを防げるだろう。例を示す。

Dim i As Long

定数はConstを使って定義する。例を示す。

Const 最大件数 = 30

プロシージャはSubとFunctionがある。戻り値がある場合はFunctionを使うと良いだろう。Privateで修飾すると他のモジュールからアクセスできなくなる。省略可能だが、Callを使って呼び出すことも可能である。外部のモジュールのプロシージャを呼び出すには、モジュール名.プロシージャ名とすると名前衝突を回避できる。

制御及びプロシージャを抜けるにはExitを使用する。Exitの後にDo、For、Function、Subと対応するものを記述する。

セルにアクセスするにはRangeとCellsを使用することができる。Rangeは名前ボックスの名前で指定できるので、基本的には名前をつけてこちらを使う。Cellsは相対位置を指定できるので、１件ずつアクセスする場合には有効である。Cellsを使いたい場合はRangeと併用したい。例を示す。

v = Range("データ").Cells(i, 1).Value

Cellsに似たものにOffsetプロパティがある。

オブジェクトを代入する場合はSetを使用する。例を示す。

  Dim r As Range

  Set r = Range("pMsg")

  r.Value = "Hello"

ステートメントを複数に分けて記述したい場合は、行末に _ を記述する。

MsgBoxはメッセージボックスを表示する。引数が１つのときは括弧を省略可能である。第２引数にvbYesNoを指定すると、「はい いいえ」の選択肢が表示される。「はい」が選択された場合は、戻り値がvbYesになる。vbOKCancelなどもある。

分岐構文

分岐構文はIf文が使用可能である。C言語のifに似ている。例を示す

If i < 5 then

  MsgBox("5未満")

ElseIf i < 10 then

  MsgBox("5以上10未満")

Else

  MsgBox("10以上")

End If

Select Case文も分岐構文として使用できる。例を示す。

Select Case コード

Case 1 To 99

  分類番号 = 0

Case 101 To 199

  分類番号 = 1

Case 201 To 299

  分類番号 = 2

Case 301 To 399

  分類番号 = 3

Case Else

  分類番号 = -1

End Select

繰り返し構文

繰り返し文のFor文が使用可能である。C言語のfor文とは少し違う。例を示す。

For i = 0 To 10

  v = v + i

Next

途中で終了するには Exit For を実行する。

配列またはコレクションに対して繰り返し処理をしたい場合は、For Each文を使用する。例を示す。

For Each c In Range("A1:B2")

  c.value = 123

Next c

無限ループの構文の例を示す。

Do While True

Loop

Trueの部分が条件式で、この場合はTrueなので無限ループになる。Exit Doでループを抜ける。Loopの方に条件式「While True」を記述した場合は後判定式になる。また、Whileの代わりにUntilを使用すと、条件式が偽の間ループし、真になったときにループを抜けるようになる。

ThisWorkbookでの記述内容

ThisWorkbookではブックに関するイベントを記述する。一例として、ブックを開いたときに行う処理を記述することができるが、それにはOpenイベントを記述していく。伝票コードがある場合には、入力を開始コードを更新したりすることができる。またEnterキー入力方向の変更などもここで行ってよいだろう。グローバル変数の初期化を行うこともできる。簡単な例を示す。

ThisWorkbookの記述

Private Sub Workbook_Open()

  グローバル変数 = 3 'グローバル変数への書き込み

  'Enterキー入力後のセル移動を右にする。

  '変更する場合は、メッセージボックスを表示する。

  With Application

    If .MoveAfterReturn <> True Or .MoveAfterReturnDirection <> xlToRight Then

      MsgBox "Enterキー入力後のセル移動を右にします。"

      .MoveAfterReturn = True

      .MoveAfterReturnDirection = xlToRight

    End If

  End With

End Sub

標準モジュール グローバル変数の記述

Option Explicit

Public グローバル変数 As Long

セル背景とフォントの色を変える

セルの背景色を変えるにはInterior.Color、フォントの色を変えるにはFont.ColorにRGB関数で得た値を代入する。A1セルの背景色とフォント色、Sheet1シートタブの色を変更する例を示す。

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

  Range("A1").Font.Color = RGB(255, 0, 255) 'マゼンタ

  Worksheets("Sheet1").Tab.Color = RGB(0, 255, 255) 'シアン

Excel2003までの方法として、「Interior.ColorIndex = 15」のようにするとセルの色を変更できる。番号はグーグル等で検索すること。Font.ColorIndexではフォントカラーを指定できる。

「Worksheets("Sheet1").Tab.ColorIndex」のようにするとシートタブの色を変更できる。番号を取得することで、条件分岐などにも利用可能である。また、マクロで記録しながら色を変えて、そのコードを利用する方法も有効である。

アクティブセルの自動移動

データ入力の際は１行入力し終わったら、次の行の入力開始位置に自動的に移動したい時がある。そのようのときは、行の入力最後の右セルに到達したときに自動的に移動するようにできる。また、Enterキーで右移動の設定をしているときに、右ではなく下に移動するようなことも可能である。B2セルに移動したとき、B3へ移動するコードを示す。

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 

  If Target.row = 2 And Target.column = 2 Then

    Range("B3").Select

  End if

End Sub

「列 = ActiveCell.column」、「 行 = ActiveCell.row」としてから各分岐をするとよいだろう。３列目に移動したとき、次の行の１列目に移動するコードを示す。

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 

  列 = ActiveCell.column

  行 = ActiveCell.row

  If 列 = 3 Then

    Cells(行 + 1, 1).Select

  End if

End Sub

日付

Formatは日付を形式付き文字列で取得できる。Format(日付, "m月d日")といった具合。

Date（日付）型を引数とするYearは年を返し、Monthは月を返す。

セルの日付形式はNumberFormatLocalで変更できる。選択中のセルを変更するいくつかプロシージャ例を示す。

Public Sub 月日形式にする()

  Selection.NumberFormatLocal = "m""月""d""日"";@"

End Sub

Public Sub 年月形式にする()

  Selection.NumberFormatLocal = "yyyy""年""m""月"";@"

End Sub

Public Sub yyyymd形式にする()

  Selection.NumberFormatLocal = "yyyy/m/d"

End Sub

並び替え

シート内のデータを並び替えるにはSortを使用する。例を示す。

'条件が１つの場合

With Worksheets(シート名)

  .Range(rng).Sort key1:=.Range(key), Order1:=xlAscending, _

      Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End With

'条件が２つの場合

With Worksheets(シート名)

  .Range(rng).Sort key1:=.Range(key1), Order1:=xlAscending, _

      key2:=.Range(key2), Order2:=xlAscending, _

      Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End With

コントロールに名前を付けてアクセスする

コントロールにはフォームコントロールとActiveXコントロールの２種類がある。ActiveXコントロールの場合は無効にするなどの操作が可能である。リボンの[開発]タブの[挿入]からシート上に追加できる。

コントロールの名前を変更するには、Excelリボンの開発タブで、[デザインモード]にし、[名前ボックス]を変更する。または、[デザインモード]でコントロールを右クリックし[プロパティ]をクリックし（オブジェクト名）を変更する。表示されるプロパティウィンドウの[オブジェクト名]を変更する。

シート名が「Sheet1」にあるActiveXコントロールを「btn更新」という名前にしたとして、それを使用不能にするには以下のようにする。

Sheets("Sheet1").OLEObjects("btn更新").Object.Enabled = False

「Sheets("Sheet1").」は（モジュールではない）シートのコードにある場合は省略可能である。

ファイルの保存

SaveAsでファイルに名前を付けて保存、Killでファイルを削除、Dirでファイルのチェック、MkDirでフォルダを作成する。hoge.xlsmをバックアップしたあと、保存する例を示す。

Const AppDir = "D:\" 'アプリケーションディレクトリ

If Dir(AppDir & "hogeバックアップ.xlsm") <> "" Then

  Kill (AppDir & "hogeバックアップ.xlsm")

End If

SaveAs (AppDir & "hogeバックアップ.xlsm")

Kill (AppDir & "hoge.xlsm")

SaveAs (AppDir & "hoge.xlsm")

マクロを使用したコードの生成

マクロは一連のユーザーの処理を記録し、再現することができる。リボン[開発]タブの[マクロの記録]ボタンをクリックして記録を開始する。記録を開始すると、[マクロの記録]ボタンが[記録終了]ボタンに変わるので、操作が終了したら[記録終了]ボタンをクリックする。

B2セルに「123」と入力し、太字にし、背景をオレンジに変え、そのセルを下のセルにコピーし、2行目を非表示にし、1～3行目を選択し再表示する操作を記録したときのマクロのコード（コメント省略）を示す。

Sub Macro1()

    Range("B2").Select

    ActiveCell.FormulaR1C1 = "123"

    Range("B2").Select

    Selection.Font.Bold = True

    With Selection.Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .Color = 49407

        .TintAndShade = 0

        .PatternTintAndShade = 0

    End With

    Selection.Copy

    Range("B3").Select

    ActiveSheet.Paste

    Application.CutCopyMode = False

    Rows("2:2").Select

    Selection.EntireRow.Hidden = True

    Rows("1:3").Select

    Selection.EntireRow.Hidden = False

End Sub

マクロのコードは標準モジュールフォルダのModule1などに保存される。必要な部分はコピーするなどして、自分のコードに組み込むことができる。

クリップボードの貼り付け

クリップボードのテキストをセルをクリックしただけで張り付けられるようなプログラムを作成する。クリップボードがテキスト形式なら２列目のいずれかのセルを選択時に貼り付けを行い、その後クリップボードをクリアする。

ワークシート（sheet）のプログラムを示す。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  Dim cf As Variant

  Dim i As Long

  cf = Application.ClipboardFormats

  If Target.Column = 2 Then

    For i = 1 To UBound(Application.ClipboardFormats)

      Select Case cf(i)

        Case xlClipboardFormatText

          ActiveSheet.Paste

          クリップボードクリア

          Exit For

      End Select

    Next

  End If

End Sub

ActiveSheet.Pasteのところで上手くいかないようであれば、ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=Trueとする。

「クリップボードクリア」によって呼び出される標準モジュールのプログラムを示す。

Public Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long

Public Declare Function CloseClipboard Lib "user32" () As Long

Public Declare Function EmptyClipboard Lib "user32" () As Long

Sub クリップボードクリア()

  OpenClipboard (0&)

  EmptyClipboard

  CloseClipboard

End Sub

雑多

ExcelのISBLANK関数の機能として、VBAではIsEmpty関数が用意されている。セルをクリアするには、セルのvalueにEmptyを代入する。

Range内の一番上の行を取得するには、「Range(範囲).End(xlUp).Row」とする。下はxlUpをxlDownにする。Endはセルの終端を示すものである。

シートの保護はシートに対してProtectを実行する。Unprotectではシートの保護を解除する。「Protect AllowFormattingCells:=True」とすると書式設定だけは可能になる。

シートを保護している間でも、セルを編集したい場合は、セルに対して「Locked = False」とする。戻す場合はTrueにする。

一時的にイベントの発生を防ぐには「Application.EnableEvents = False」とする。戻す場合はTrueにする。

画面の更新を止めるには、「Application.ScreenUpdating = False」とする。Trueにすると更新を再開する。プログラムの高速化することも可能である。