excel-vba
Excel VBAのヒントとテクニック
サーチ…
備考
このトピックは、SOユーザーがコーディングで経験したことで発見されたさまざまな有益なヒントとテクニックから構成されています。これらは、多くの場合、一般的な不満や、Excelをより巧妙な方法で使用する方法を回避する方法の例です。
xlVeryHiddenシートの使用
ExcelのワークシートにはVisible
プロパティの3つのオプションがあります。これらのオプションは、 xlSheetVisibility
列挙体の定数で表され、次のとおりです。
-
xlVisible
またはxlSheetVisible
値:-1
(新しいシートのデフォルト) -
xlHidden
またはxlSheetHidden
値:0
-
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