excel-vba
Excel VBAのヒントとテクニック

備考

xlVeryHiddenシートの使用

ExcelのワークシートにはVisibleプロパティの3つのオプションがあります。これらのオプションは、 xlSheetVisibility列挙体の定数で表され、次のとおりです。

1. xlVisibleまたはxlSheetVisible値： -1 （新しいシートのデフォルト）
2. xlHiddenまたはxlSheetHidden値： 0
3. xlVeryHidden xlSheetVeryHidden値： 2

可視シートは、シートのデフォルトの可視性を表します。シートタブバーに表示され、自由に選択して表示できます。非表示のシートはシートタブバーから隠されているため選択できません。ただし、シートのタブを右クリックして[再表示]を選択すると、非表示のシートをExcelウィンドウから隠すことができます

一方、非常に非表示のシートは、Visual Basic Editorからのみアクセスできます。これにより、Excelのインスタンス間でデータを格納し、エンドユーザーから非表示にする必要のあるデータを格納するための非常に便利なツールになります。 VBAコード内の名前付き参照でシートにアクセスでき、保存されたデータを簡単に使用できます。

ワークシートの.VisibleプロパティをxlSheetVeryHiddenに手動で変更するには、VBEのPropertiesウィンドウ（ F4 ）を開き、変更するワークシートを選択し、13行目のドロップダウンを使用して選択します。

コードでワークシートの.VisibleプロパティをxlSheetVeryHidden¹に変更するには、同様に.Visibleプロパティにアクセスして新しい値を割り当てます。

with Sheet3
    .Visible = xlSheetVeryHidden
end with

_{¹xlVeryHiddenとxlSheetVeryHiddenの両方が2の数値を返します（これらは互換性があります）。}

ワークシート.Name、.Indexまたは.CodeName

「ベストプラクティス」は、範囲オブジェクトが親ワークシートを明示的に参照するように指示しています。ワークシートは、.Nameプロパティ、数値の.Indexプロパティ、または.CodeNameプロパティで参照できますが、ユーザーは名前タブをドラッグするか、同じタブをダブルクリックしてワークシートの名前を変更するだけでワークシートのキューを並べ替えることができます。保護されていないブックに入力します。

標準の3つのワークシートを考えてみましょう。月曜日、火曜日、水曜日の順に3つのワークシートの名前を変更し、これらを参照するVBAのサブプロシージャをコード化しました。ここで、1人のユーザーが来て、月曜日がワークシート・キューの最後に属し、別のユーザーが来ると判断し、ワークシート名がフランス語でよく見えると判断したとします。次のようなワークシート名のタブ・キューを持つワークブックが作成されました。

次のいずれかのワークシート参照メソッドを使用していた場合は、コードが壊れてしまいます。

'reference worksheet by .Name
with worksheets("Monday")
    'operation code here; for example:
    .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp)) = 1
end with

'reference worksheet by ordinal .Index
with worksheets(1)
    'operation code here; for example:
    .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp)) = 1
end with

元の注文と元のワークシート名の両方が改ざんされています。ただし、ワークシートの.CodeNameプロパティを使用していた場合、サブプロシージャは引き続き操作可能です

with Sheet1
    'operation code here; for example:
    .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp)) = 1
end with

次の図は、VBA Projectウィンドウ（[Ctrl] + R）を示しています.CodeName、.Name（括弧内）のワークシートがリストされています。表示される順序は変更されません。通常の.Indexは、ワークシート・ウィンドウの名前タブ・キューに表示される順序で取得されます。

.CodeNameの名前を変更することは珍しいことではありませんが、不可能ではありません。 VBEの[プロパティ]ウィンドウ（[F4]）を開くだけです。

ワークシート。コード名は、最初の行にあります。ワークシートの.Nameは1/10です。どちらも編集可能です。

動的配列の代わりにデリミタを使用した文字列の使用

VBAでダイナミックアレイを使用すると、非常に大規模なデータセットに対して、非常に控えめで時間がかかることがあります。単純なデータ型を動的配列（Strings、Numbers、Booleansなど）に格納する場合、 Split()関数を使用してVBAでダイナミック配列に必要なReDim Preserveステートメントを避けることができます。たとえば、範囲から一連の値を条件に基づいて文字列に追加し、その文字列を使用してListBoxの値を設定するループを見てみましょう。

Private Sub UserForm_Initialize()

Dim Count As Long, DataString As String, Delimiter As String

For Count = 1 To ActiveSheet.UsedRows.Count
    If ActiveSheet.Range("A" & Count).Value <> "Your Condition" Then
        RowString = RowString & Delimiter & ActiveSheet.Range("A" & Count).Value
        Delimiter = "><" 'By setting the delimiter here in the loop, you prevent an extra occurance of the delimiter within the string
    End If
Next Count

ListBox1.List = Split(DataString, Delimiter)

End Sub

Delimiter文字列自体は任意の値に設定できますが、セット内で自然に発生しない値を選択することは賢明です。例えば、あなたは日付の列を処理していたとしましょう。その場合は、を使用してください. 、 - 、 /または区切り記号としては賢明ではありません。これらのいずれかを使用するように日付を設定できるため、予想以上のデータポイントが生成されます。

注意：このメソッドの使用には制限があります（つまり文字列の最大長）。非常に大きなデータセットの場合は注意が必要です。これは必ずしもVBAでダイナミックアレイを作成するための最速または最も効果的な方法ではありませんが、実行可能な代替方法です。

Excel図形のダブルクリックイベント

既定では、Excelの図形には、シングルクリック対ダブルクリックを処理する特定の方法がなく、クリックを処理できる「OnAction」プロパティのみが含まれています。しかし、コードでは、ダブルクリックで異なる（または排他的に）行動する必要がある場合があります。次のサブルーチンをVBAプロジェクトに追加することができます。また、 OnActionルーチンとして設定すると、ダブルクリックにOnActionすることができます。

Public Const DOUBLECLICK_WAIT as Double = 0.25 'Modify to adjust click delay
Public LastClickObj As String, LastClickTime As Date

Sub ShapeDoubleClick()
    
    If LastClickObj = "" Then
        LastClickObj = Application.Caller
        LastClickTime = CDbl(Timer)
    Else
        If CDbl(Timer) - LastClickTime > DOUBLECLICK_WAIT Then
            LastClickObj = Application.Caller
            LastClickTime = CDbl(Timer)
        Else
            If LastClickObj = Application.Caller Then
                'Your desired Double Click code here
                LastClickObj = ""
            Else
                LastClickObj = Application.Caller
                LastClickTime = CDbl(Timer)
            End If
        End If
    End If
    
End Sub

このルーチンは、シェイプが最初のクリックを機能的に無視するようにします。指定した期間内に2回目のクリックで目的のコードを実行するだけです。

ファイルを開くダイアログ - 複数のファイル

このサブルーチンは、ユーザーが複数のファイルを選択し、それらのファイルパスを使ってファイル名を取得し、debug.print経由でコンソールに送信するなど、何かを実行する方法を簡単に示したものです。

Option Explicit

Sub OpenMultipleFiles()
    Dim fd As FileDialog
    Dim fileChosen As Integer
    Dim i As Integer
    Dim basename As String
    Dim fso As Variant
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    basename = fso.getBaseName(ActiveWorkbook.Name)
    fd.InitialFileName = ActiveWorkbook.Path ' Set Default Location to the Active Workbook Path
    fd.InitialView = msoFileDialogViewList
    fd.AllowMultiSelect = True
    
    fileChosen = fd.Show
    If fileChosen = -1 Then
        'open each of the files chosen
        For i = 1 To fd.SelectedItems.Count
            Debug.Print (fd.SelectedItems(i))
            Dim fileName As String
            ' do something with the files.
            fileName = fso.getFileName(fd.SelectedItems(i))
            Debug.Print (fileName)
        Next i
    End If

End Sub