VBAを使ってエクセルにシートを追加する

マクロを作っていてシートの追加を忘れてしまったので残しておく。
マクロの記録を使ったときに記録されるVBAのコード。

Sub Macro1()
    Sheets.Add After:=Sheets(Sheets.Count)
End Sub

Sheets.Addでシートの新規追加を実施する。
Afterはどのシートの後ろに追加するか指定する。
Sheets.Countはシートの総数を求める。
なので一番最後のシートの後ろに新規のシートを追加する意味となる。

3番目のシートの前に追加するコード。

Sheets.Add Before:=Sheets(3)

新規に作成するシートの名前をfooにするコード。

Sheets.Add.Name = "foo"

新規に作成するシートの名前を日時にするコード。

Sheets.Add.Name = Format(Now(), "yyyy年MM月dd日 h時mm分ss秒")

アクティブシートの前または後ろに新規のシートを追加するコード。

Sheets.Add Before:=ActiveSheet
Sheets.Add After:=ActiveSheet

追加位置、シート名を指定するコード。

Sheets.Add(Before:=ActiveSheet).Name = "foo"

Excel2010で確認した。

VBEでデバック文を出力する方法

マクロを作成していて途中で値を確認したいときにデバッグ文を出力して確認したい。
そんなときはDebug.Printが使える。
Debug.Printを使うとイミディエイトウィンドウに出力結果が表示される。

Dim num As Integer
num = 5
Debug.Print num

' イミディエイトウインドウに5と表示される。

イミディエイトウィンドウの表示のやり方は「表示」-「イミディエイトウィンドウ」。またはショートカットのCtrl+G。

VBAで指定したフォルダ以下のサブフォルダを含むファイルパスを出力する

VBAで指定したフォルダ以下のサブフォルダを含むファイルパスを出力する。
サブフォルダを含む場合は再帰を使いたいのでFunctionを作成してファイルパスを出力させる。

Excel2010で確認。

Sub fileDialogTest()
  Dim fd As FileDialog
  ' フォルダ選択ダイアログを表示する
  Set fd = Application.FileDialog(msoFileDialogFolderPicker)
  If Not fd.Show Then
    Debug.Print 'File Dialog is Canceled'
    Exit Sub
  End If
  ' 選択したフォルダパスを作成したFunctionに渡す
  loopTest (fd.SelectedItems(1))
End Sub

Private Function loopTest(fp As String)
  Dim fso As New FileSystemObject
  Dim f As File
  Dim fd As Folder
  ' 選択したフォルダパスにあるファイル分ループする
  For Each f In fso.GetFolder(fp).Files
    ' Immediate windowにファイルパスを出力する
    Debug.Print f
  Next
  
  ' 選択したフォルダパスにあるフォルダ分ループする
  For Each fd In fso.GetFolder(fp).SubFolders
    ' 再帰で自分自身にフォルダパスを渡してサブフォルダの処理を行う
    loopTest (fd)
  Next
End Function

VBAで指定したフォルダのフォルダパスを出力する

VBAで指定したフォルダのフォルダパスを出力する。

Excel2010で確認。

Sub fileDialogTest()
  Dim fd As FileDialog
  ' フォルダ選択ダイアログを表示する
  Set fd = Application.FileDialog(msoFileDialogFolderPicker)
  If Not fd.Show Then
    Debug.Print 'File Dialog is Canceled'
    Exit Sub
  End If

  Dim fso As New FileSystemObject
  Dim f As Folder
  ' 選択したフォルダパスにあるフォルダ分ループする
  For Each f In fso.GetFolder(fd.SelectedItems(1)).SubFolders
    ' Immediate windowにフォルダパスを出力する
    Debug.Print f
  Next
End Sub

SubFolders プロパティ

VBAで指定したフォルダのファイルパスを出力する

VBAで指定したフォルダのファイルパスを出力する。

Excel2010で確認。

Sub fileDialogTest()
  Dim fd As FileDialog
  ' フォルダ選択ダイアログを表示する
  Set fd = Application.FileDialog(msoFileDialogFolderPicker)
  If Not fd.Show Then
    Debug.Print 'File Dialog is Canceled'
    Exit Sub
  End If

  Dim fso As New FileSystemObject
  Dim f As File
  ' 選択したフォルダパスにあるファイル分ループする
  For Each f In fso.GetFolder(fd.SelectedItems(1)).Files
    ' Immediate windowにファイルパスを出力する
    Debug.Print f
  Next
End Sub

Files プロパティ

VBAでフォルダ選択ダイアログを出してフォルダパスを取得する

VBAでフォルダ選択ダイアログを出してフォルダパスを取得する

Excel2010で確認。

Sub fileDialogTest()
  Dim fd As FileDialog
  Set fd = Application.FileDialog(msoFileDialogFolderPicker)
  If Not fd.Show Then
    Debug.Print 'File Dialog is Canceled'
    Exit Sub
  End If
  ' Immediate windowに選択したフォルダパスを出力する
  Debug.Print fd.SelectedItems(1)
End Sub

Application.FileDialog Property (Excel)

引数に渡すときに以下のようにしても同じ動きになる。
:=(コロンイコール)は引数に名前を指定して渡したいときや、「,,,, 6」などのように第X引数にだけ引数を渡したいときに使う。

Set fd = Application.FileDialog(fileDialogType:=msoFileDialogFolderPicker)

エクセルのワークブック内のシート分ループさせる

エクセルのワークブック内のシート分ループさせる。

Excel2010で確認。

Sub printSheetName()
  Dim ws As Worksheet
  ' ワークシートの数分ループする。
  For Each ws In ActiveWorkbook.Sheets
    ' ワークシートの数分ループしているかメッセージボックスにワークシート名を出力して確認する。
    MsgBox ws.Name
  Next ws
End Sub

Option Explicitステートメントを使用して、宣言した変数のみ使用することを強制する。

VBAで変数を使用する時、DimありとDimなしを意識していなかった。本を読んでいて違いを把握したので残しておく。

VBAは、ある条件では、変数を使用する時に、Dimを使っても、使わなくても動作する。
例えば、Dimを使用しない以下のような「5と6を足し算した結果をメッセージボックスに表示する」コードも動作する。
しかし、以下のコードを実行するとメッセージボックスには11ではなく、5が表示される。
6を代入している変数はnum2だが、Totalに使用しているのは、num2ではなくて、スペル誤りのnum1だからだ。
つまり、変数のスペルが誤っていてもエラーとならないため、処理結果が誤っている時にバグを探すのが大変になることがある。


Sub testOptionExplicit()

    num = 5
    num2 = 6
    
    Total = num + num1
    MsgBox Total
End Sub

それを予防するために、Option Explicitステートメントを使用する。
このステートメントを使用すると、「Dimを用いて変数宣言していない変数は使用できない」という変数の宣言を強制することができる。
先に書いたコードを修正して実行すると、num1が変数宣言していないため、VBEがコンパイルエラーとしてメッセージを出力してくれる。
つまり、処理結果が出る前にバグに気づける。


Option Explicit
Sub testOptionExplicit()

    Dim num As Integer: num = 5
    Dim num2 As Integer: num2 = 6
    
    Total = num + num1
    MsgBox Total
End Sub

Option Explicitステートメントは、毎回記述するのは手間なので、VBEのオプションでデフォルトで設定しておくと便利。
やり方は、VBEの[ツール]-[オプション]を選択して、「編集」タブの「変数の宣言を強制する」チェックボックスにチェックを付ける。

VBA 7.1で動作確認。