プログラミング > Excel & VBA >

VBAの基本と操作

2023-06-27

目次

変数の扱い

   宣言方法はDimを用いる。変数名をname、変数の型となるクラス (オブジェクト) をClassとすれば以下のコードになる。また、宣言は省略も可能。ただし変数の持つ関数やメソッドが不明になるのでコーディングはしにくくなる場合があるので注意。
                    
Dim name As Class
   よく使う型として以下の3種。WorkbookはExcelファイル、WorksheetはExcelファイルの中のシート、Rangeはシート中の単体セルから範囲セルを表している。
  • Dim work_book As Workbook
  • Dim work_sheet As Worksheet
  • Dim table as Range
   データ型は主に以下 (参考) 。
  • Class
    • 任意のクラス (オブジェクト) 。
  • Boolean
    • TrueもしくはFalse。
  • Date
    • 日付。
  • Integer
    • 整数。さらに扱う範囲の広い整数は順にLong、LongLongとなっている。
  • Single
    • 浮動小数点数で単精度の場合。倍精度はDoubleがある。
  • Variant
    • 任意の数値。
  • String
    • 文字列。

excelファイルの操作

  • Excelファイルを開く・閉じる
    • 
With Workbooks.Open(file_name, ReadOnly:True)
	.Close SaveChanges:=False
       With内であればドットメソッド.methodを呼び出せる。ReadOnly:Trueで読み取り専用で開いている。また閉じるときはによって保存せずに終了させている。こうしておかないとダイアログで保存の可否を尋ねてくる。そもそもExcelはセルの値を取得するためにカーソル位置が動く。そのためカーソルが動いただけで編集したことになる。その状態でファイルを閉じようとすると保存するかどうかとなる。
  • Excelファイルを読み込む
    • 
Set work_book = Workbooks("file_name.xlsx")
       単純に読み込む場合はSetで宣言する。
  • シートの読み込み
    • 
Set work_sheet = Worksheets("sheet_name")
Set work_sheet = Worksheets(1)
       ファイル同様Setで宣言することで扱える。シート名"sheet_name"ではなく、シート番号でも読み込みができる。

構文

  • If
    • 
If flag = condition 
	...
End If
       等しいは=、大小関係は<と>、否定は<>もしくはIf Not ...。
  • For
    • 
For i = 0 To N 
	...
Next i
       条件i = 0からNまで。
  • For Each 
    • 
For Each cell In Range(“A1:A5”)
	...
Next cell
       指定した範囲の各セルを回る。Excelらしい。
  • Do While 
    • 
i = 0
Do While i <> 10
	...
	i = i + 1
Loop
       条件が満たされない間はループする。
  • Go To 
    • 
For i = 0 To N
	...
	If i = 0 Then
		Go To
			Continue
	End If
Continue:
Next i
       i = 0 のときだけスキップ、Continue文の代わりになる。

デバッグとパフォーマンス

  • Debug.Print ..., ...
    •    メニューの「表示」からイミディエイトウィンドウを表示しておくと出力結果が写る。カンマで区切ることで複数出力できる。
  • Application.StatasBar = ...
    •    シートの左下のステータスバーに値を出力できる。実行の割合やステップを表示すると便利かも。
  • Application.ScreenUpdating = False
  • Application.Calculation = xlCalculationManual
  • Application.EnableEvents = False
    •    これを実行すると画面更新やセルの値の更新、マウスやキーボードによるイベントが止まるので計算が早くなる。計算が終わった後、FalseはTrueに、xlCalculationManualはxlCalculationAutomaticに戻しておくこと！

具体的なプログラム書く前の「とりあえず」

   VBAでプログラム作るときの1つのテンプレート、雛形を紹介する。主な目的は表内の値を自由に取り出したり、書き換えたりすること。Excelは表計算ソフトでUIの都合的から基本1シートに1つの表で構成されるのが理想的。小さい表なら1シートに複数の表があるかもしれない。いずれにしてもシート内に表がある。以下の3つを考えれば最低限表内の値を取得できる。
  • 原点: origin
    •    シートにある表の左上、1行1列目の場所 (セルの位置) によって表がどこから始まるかがわかる。
  • 表: table
    •    何行何列なのかという表全体を表す。
  • 値: value
    •    表の任意の場所にアクセスしたときの値を表す。
   具体的なコードは以下のようになる。
                    
Dim work_book As Workbook
Dim work_sheet As Worksheet
Dim origin As Range
Dim table As Range
Set work_book = Workbooks("file_name.xlsx")
With work_book
	Set work_sheet = .Worksheets("sheet_name")
	With work_sheet
		Set origin = .Range("A1")
		col = origin.End(xlToRight).Column
		row = origin.Offset(Rows.Count - 1, 0).End(xlUp).Row
		origin.Resize(1, col).AutoFilter
		origin.Resize(1, col).AutoFilter
		Set table = origin.Resize(row, col)
		value = table.Value
		For i = 1 To row
			For j = 1 To col
				Debug.Print value(i, j)
			Next j 
		Next i
	End With
End With
   原点はA1セルとしている。
   colは原点から空白までの列数を返している。もし空白の列名を含みたい場合、origin.Offset(0, Columns.Count - 1).End(xlToLeft).Columnとする。rowも同様の処理で行数を得ている。
   origin.Resize(1, col).AutoFilterは列のフィルターを外すため。フィルターが掛かっていた場合、取得する値が変わってしまう。なぜかはわからないが2回コールしないとはずれない。
   value = table.Valueとすると2次元配列のように値にアクセスできる。例えばvalue(3, 4)とすれば表の3行4列の値を取得できる。とても便利。