備忘録

よく使うVBAのフレーズ集

最終行の取得

Dim lastRow As Long
lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

　現在のシートのA列の最終行数を返します。シートの指定と列の指定を忘れずに！

最終列の取得

Dim lastCol As Long
lastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

現在のシートの1行目の最終列数を返します。シートの指定と行の指定を忘れずに！

セルのコピー＆ペースト

Sub copyPasteAll()
    Range("A1").Copy
    Range("B1").PasteSpecial　　'すべて
    Application.CutCopyMode = False
End Sub

Sub copyPasteValues()
    Range("A1").Copy
    Range("B1").PasteSpecial Paste:=xlPasteValues　　'値のみ
    Application.CutCopyMode = False
End Sub

セルのコピー＆ペースト。Pasteプロパティでコピーの種類を変更できます。
CutCopyMode = False で、コピーで保持した値をクリアします。

描画処理を一時停止

Application.ScreenUpdating = False
'//// 何らかの処理 ////
Application.ScreenUpdating = True

高速化する時に使う一番お手軽な方法。

イベントを一時無効化にする

Application.EnableEvents = False	'イベントを無効化
    
'//// 何らかの処理 ////
    
Application.EnableEvents = True	'イベントを有効化

Change系のイベントプロシージャでよく使います。

確認メッセージを表示させない

Application.DisplayAlerts = False     'メッセージを無効化
       
'//// 何らかの処理 ////
    
Application.DisplayAlerts = True     '元に戻す

システムが出す「～しますか？」などの確認メッセージを抑止します。

メッセージボックス「はい・いいえ」

Sub msgboxSample()
    
    Dim result As Long
    Dim myMessage As String
    myMessage = "よろしいですか？"
    result = MsgBox(myMessage, vbYesNo + vbQuestion + vbDefaultButton2, "確認")
    Select Case result '押されたボタンの確認
        Case vbYes
            '「はい」の処理
        Case vbNo
            '「いいえ」の処理
            Exit Sub
    End Select
    
End Sub

計算を一時手動にする

Application.Calculation = xlCalculationManual '手動計算(自動計算停止)
'//// 何らかの処理 ////
Application.Calculation = xlCalculationAutomatic '自動計算（戻す）

こちらも高速化で使うことがあります。

ファイルオープンダイアログでファイル名取得

Sub getMyFileName()
    Dim vfilename As Variant
    ChDir ThisWorkbook.Path      '開くフォルダを指定したい時
    vfilename = Application.GetOpenFilename("Excelブック,*.xls?")
    If vfilename = False Then
        Exit Sub
    End If
    
    Debug.Print vfilename
End Sub

vfilenameはバリアント型で定義する。失敗すればFalse。成功すればフルパスを取得。

ダイアログでフォルダ名を取得

Sub getMyFolder()

    'フォルダ選択ダイログ
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "フォルダの選択"

        .InitialFileName = ThisWorkbook.Path    '初期パスを設定

        If Not .Show Then Exit Sub  'ダイアログを表示 キャンセルならば終了

        Debug.Print .SelectedItems(1) '複数選択可能なので1つ目を表示
    End With
End Sub

　フルパスのフォルダ名を返しますが、最後に￥が付きません。

ブックを開いてシートのセルに値を代入

Sub BookOpenTest()
    Dim wb As Workbook
    Set wb = Workbooks.Open(ThisWorkbook.Path & "\sample.xlsx")
    
    wb.Worksheets("Sheet1").Range("A1") = "sample"

    wb.Close
End Sub

　開くと同時にブックオブジェクトにセットする。
　上の例は、ファイルやシートがあることが前提ですが、ファイルが無い場合はエラーとなります。
　エラー対策した例は以下のようになります。

Sub BookOpenTest2()
    Dim wb As Workbook

    On Error Resume Next
    Set wb = Workbooks.Open(ThisWorkbook.Path & "\sample.xlsx")
    On Error GoTo 0
    If wb Is Nothing Then
        MsgBox "ファイルが開けませんでした。"
        Exit Sub
    End If
    wb.Worksheets("Sheet1").Range("A1") = "sample"
    wb.Close
End Sub

ブックを開いてデータを読み込み閉じる

Sub BookOpenReadTest()
    Dim actSh As Worksheet
    Set actSh = ActiveSheet
    
    Dim wb As Workbook
    Set wb = Workbooks.Open(ThisWorkbook.Path & "\sample.xlsx")
    
    actSh.Range("A1") = wb.Worksheets("Sheet1").Range("A1")

    wb.Close SaveChanges:=False  '保存せずに閉じる
End Sub

　別のブックから値を読み込む時、開いたブックのシートがアクティブになりますので、別ブックを開く前にアクティブシートを変数に入れておきます。
　別ブックを閉じる時は、保存せずに閉じる事が多いでしょう。
　　　

新しいシートを原本シートからコピー

Worksheets("原本").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "No." & cnt ' シート名をセット
Set ws = Worksheets(ActiveSheet.Name)

　シートをコピーすると、コピーされたシートがアクティブになるので、すぐに名前を付けて、必要なら変数に代入して使うようにします。
　内部で使っている cnt変数 はカウント用の変数で１つづつ値が増えていきます。

ワークシートの内容を全てクリアする

　ワークシートの値を全てクリアします。インプットデータの初期化などで使います。

  Worksheets("data").Cells.Clear

動的配列を1つずつ追加

　動的配列で1つずつ要素を追加していきます。

Sub myAddArray()
    Dim vArr()
    
    '要素を追加
    Dim i As Long
    For i = 0 To 5
        If (Not vArr) = -1 Then
            ReDim vArr(0)
            vArr(0) = "item0"
        Else
            ReDim Preserve vArr(UBound(vArr) + 1)
            vArr(UBound(vArr)) = "item" & i
        End If
    Next i

End Sub

最小値と最大値を指定してランダムな数値を得る

　最小値と最大値の範囲でランダムな数値を出力します。

Function MyRandom(mMin As Long, mMax As Long) As Long
    If mMin > mMax Then
        Dim v As Long
        v = mMin
        mMin = mMax
        mMax = v
    End If
        
    MyRandom = Int((mMax - mMin + 1) * Rnd + mMin)
End Function

'実行例（１～１０までの値を出力します。）
Sub testCode()
    Debug.Print MyRandom(1, 10)
End Sub