EXCEL VBAの小技：よく調べるあれ

はじめに

EXCELVBAの文法って中々慣れないのと、シートの操作等に戸惑うことが多いですよね。よく調べることをここにまとめておき、随時更新しようと思います。

小技

末行、末列の取得（空白なしでセルに値が入っている時）

表形式のデータが入力されている時For文を書くときに表の最後の行や列を知りたいことがあると思いますが、下記で取得できます。

Cells(Rows.Count, 1).End(xlUp).Row ' 最終行
Cells(1, Columns.Count).End(xlToLeft).Column ' 最終列
' Rows.Count：EXCEL上の最終行、Columns.Count：EXCEL上の最終列
' End(xlUp)：Ctrl＋↑、End(xlToLeft)：Ctrl＋←
' 

発送としては、EXCELシートの最終行から、最後に入力があるところまで移動し、Ctrl＋矢印で一気に入力されているところまで移動し、その行や列番号を取得します。

ウィンドウの最大化・最小化

VBA実行時は、最小化しておけば、EXCEL以外の作業が可能

ActiveWindow.WindowState = xlMaximized ' 最大化 
ActiveWindow.WindowState = xlMinimized ' 最小化

画面更新

画面更新をストップすると、高速化されるためプロシージャの頭と最後に固定値として入れます。ただし、ただし、セルの値の取得や入力など画面の更新があるもののみ効果があるようです。（デバッグの際は、コメントアウトする）

Application.ScreenUpdating = False ' 画面更新しない
Application.ScreenUpdating = True  ' 画面更新する

確認メッセージ

シート削除時などをVBAで実行した場合、確認メッセージが表示され、処理が停止します。それを防ぐためには、下記を入力します。

Application.DisplayAlerts = False ' 非表示
Application.DisplayAlerts = True ' 表示

ある程度長めの動作をするプログラムの場合、先頭に上記3つをセットすることが多いです。
ActiveWindow.WindowState = xlMinimized ' 最小化
Application.ScreenUpdating = False ' 画面更新しない
Application.DisplayAlerts = False ' 非表示

シート上の行のコピー＆ペースト

ワークシートを選択し忘れてエラーを出すことが多いのでメモ。

Worksheets("Sheet1").Select
Rows(2).Cut
Worksheets("Sheet2").Select
Range("A2").Select
ActiveSheet.Paste

エラーハンドリング(初歩)

忘れがちなので。他の言語でいう、Try...Catch文に相当。

Sub main()
' エラーログ用のシートを用意する
Dim ws_error As Worksheet
Dim error_row As Long
Set ws_error = ThisWorkbook.Worksheets("Error")

On Error GoTo ErrorHandler

    ' エラーが起こりうる処理を書く

Exit Sub ' プロシージャを終了させる
    
ErrorHandler:
    ' エラーが投げられたときの処理を書く
    ' エラーが発生したログをシートに記録する
    error_row = ws_error.Cells(Rows.Count, 1).End(xlUp).row + 1
    ws_error.Cells(error_row, 1).Value = Format(Now, "yyyy/mm/dd hh:nn:ss")
    ws_error.Cells(error_row, 2).Value = Err.Number
    ws_error.Cells(error_row, 3).Value = Err.Description

End Sub

エラーハンドリング(ループ内)

上のForループ内の処理方法

Sub main()

' エラーログ用のシートを用意する
Dim ws_error As Worksheet
Dim error_row As Long
Set ws_error = ThisWorkbook.Worksheets("Error")

Dim i As Lomg
On Error GoTo ErrorHandler
    For i = 0 To 100
        ' エラーが起こりうる処理
    Next i
Exit Sub ' プロシージャを終了させる
    
ErrorHandler:
    ' エラーが投げられたときの処理を書く
    ' エラーが発生したログをシートに記録する
    error_row = ws_error.Cells(Rows.Count, 1).End(xlUp).row + 1
    ws_error.Cells(error_row, 1).Value = Format(Now, "yyyy/mm/dd hh:nn:ss")
    ws_error.Cells(error_row, 2).Value = Err.Number
    ws_error.Cells(error_row, 3).Value = Err.Description
    Resume Next ' Nextの行から再開＝次のiを実行する
End Sub

ガード節

条件分岐が複雑になりそうなとき、ガード節を使うことがあると思います。関数の頭で、最初に例外条件を記載して関数を終了させるようなIF文です。

guardSample = function() {
    if (例外) return 0;
    if (例外) return 0;
 
    // 正常系の処理
    return result;
}

VBAの場合は、下記のようにGoto文を使うことで実装可能です。

Function guardSample()
    Dim result
    result = 0

    If (例外) Then
        GoTo continue
    End If

    ' 正常系の処理
continue:
    guardSample = result
    
End Function

Goto文は一般的に推奨されないと思いますが、ガード節にだけ使うなど用途を決めておけば、複雑になることはないと思います。

宣言強制

VBAでコード書く際は、先頭につけるおまじない。

Option Explicit

シートを余分な行を削除する

上からだと行がズレてしまうので、下から削除していく。

For i = lastRow To 1 Step -1
    If ws.Cells(i, 1).value = "" Then
        ws.Rows(i).Delete
    End If
Next i

シート内のセルクリア

ThisWorkbook.Sheets("{シート名}").Cells.Clear

CSVファイルをブック内のシートにインポート

Function copy_csv_to_sheet(file_path)
    Workbooks.Open Filename:=file_path
    ActiveSheet.Cells.Copy Application.ThisWorkbook.Sheets("{シート名}").Cells
    ActiveWorkbook.Close SaveChanges:=False
End Function

シートの存在チェック

エラーを使ったシンプルな方法を下記ブログで紹介されていました

OneDriveを使っている場合

OneDriveで同期しているファイルまたはフォルダを指定することでエラーになるのは、OneDrive上のファイルはURLとなってしまうためです。そのため、ローカルパスに変換する必要があるのですが、下記ブログのプログラムを使うと解消します。

まとめ

他にも毎回使うのに、都度調べるものは追記していこうと思います。