excel - 回避 - vba 選択範囲 取得




Excel VBAでの選択の使用を避ける方法 (9)

"... Select関数の代わりに変数を使用することができれば、私のコードがより再利用可能になることがわかっています。"

.Selectは、直接セル参照というよりも良い選択となるような、ほんの一握りの状況を考えることはできませんが、私はSelectionの防衛に.Select 、同じ理由で投げられるべきではないことを指摘し.Selectは避けて.Select

いくつかのキーのタップで使用可能なホットキーの組み合わせに割り当てられた短い、時間を節約するマクロサブルーチンを持つことは、多くの時間を節約することがあります。 ワークコード上で操作コードを制定するためのセル群を選択することができれば、ワークシート全体のデータフォーマットに準拠していないポケットデータを扱う際に不思議に思うことがあります。 セルのグループを選択してフォーマットの変更を適用するのと同じように、特別なマクロコードを実行するセルのグループを選択することは、大きな時間を節約することができます。

選択ベースのサブフレームワークの例:

Public Sub Run_on_Selected()
    Dim rng As Range, rSEL As Range
    Set rSEL = Selection    'store the current selection in case it changes
    For Each rng In rSEL
        Debug.Print rng.Address(0, 0)
        'cell-by-cell operational code here
    Next rng
    Set rSEL = Nothing
End Sub

Public Sub Run_on_Selected_Visible()
    'this is better for selected ranges on filtered data or containing hidden rows/columns
    Dim rng As Range, rSEL As Range
    Set rSEL = Selection    'store the current selection in case it changes
    For Each rng In rSEL.SpecialCells(xlCellTypeVisible)
        Debug.Print rng.Address(0, 0)
        'cell-by-cell operational code here
    Next rng
    Set rSEL = Nothing
End Sub

Public Sub Run_on_Discontiguous_Area()
    'this is better for selected ranges of discontiguous areas
    Dim ara As Range, rng As Range, rSEL As Range
    Set rSEL = Selection    'store the current selection in case it changes
    For Each ara In rSEL.Areas
        Debug.Print ara.Address(0, 0)
        'cell group operational code here
        For Each rng In ara.Areas
            Debug.Print rng.Address(0, 0)
            'cell-by-cell operational code here
        Next rng
    Next ara
    Set rSEL = Nothing
End Sub

処理する実際のコードは、単一の行から複数​​のモジュールに至るまでのあらゆるものになります。 私はこのメソッドを使用して、外部ワークブックのファイル名を含む不規則なセルの選択に対して長時間実行するルーチンを開始しました。

つまり、 .SelectActiveCellとの密接な関連性のためにSelection破棄しないでください。 ワークシートのプロパティとして、他の多くの目的があります。

(はい、私はこの問題について知っていました。 SelectionではなくSelectionしかし、初心者のVBAコーダーが推論するかもしれない誤解を取り除きたかったのです。)

私はExcel VBAで.Selectを使用することの理解できない嫌悪について多くのことを聞いたことがありますが、それを避ける方法はわかりません。 私は、 Select関数の代わりに変数を使用することができれば、私のコードをより再利用できると考えています。 しかし、 Selectを使用しない場合、 ActiveCellなどのようなものを参照する方法がわかりません。

私は範囲この選択肢を使用しないの利点についてのこの例では、 どのように何かを見つけることができないこの記事を発見した


.select使用は、私のような人は、必要に応じてマクロを記録し、その後、 .selectとそれに続くselection.selectすることなくコードを修正することでVBAを学び始めました。

.selectは、既存のオブジェクトを直接操作することで、既に投稿されているように避けることができます。複雑な方法でiとjを計算し、セル(i、j)などを編集するなどの間接参照が可能です。

それ以外の場合は暗黙のうちに.select自体に間違っていることは何もなく、これを簡単に使用することができます。例えば、日付を入力するスプレッドシートを持っています。マクロを有効にしてマジックを有効にし、しかし、それは隣接するセルへのいくつかの最終的な手動(予測不可能な)入力を必要とする。 ここで、 .selectが追加のマウスの動きとクリックを節約する瞬間が来ます。


.Select / .Activate / Selection / Activecell / Activesheet / Activeworkbookなどを避けるべき主な2つの理由

  1. それはあなたのコードを遅くする。
  2. これは通常、ランタイムエラーの主な原因です。

どうしたらそれを避けることができますか?

1)関連するオブジェクトを直接操作する

このコードを検討する

Sheets("Sheet1").Activate
Range("A1").Select
Selection.Value = "Blah"
Selection.NumberFormat = "@"

このコードは、次のように書くこともできます。

With Sheets("Sheet1").Range("A1")
    .Value = "Blah"
    .NumberFormat = "@"
End With

2)必要に応じて変数を宣言します。 上記の同じコードは、次のように書くことができます。

Dim ws as worksheet

Set ws = Sheets("Sheet1")

With ws.Range("A1")
    .Value = "Blah"
    .NumberFormat = "@"
End With

これは、セル "A1"の内容を消去する例です(選択タイプがxllastcellの場合など)。 すべての細胞を選択することなく行われます。

Application.GoTo Reference:=Workbook(WorkbookName).Worksheets(WorksheetName).Range("A1")
Range(Selection,selection(selectiontype)).clearcontents 

私はこれが誰かを助けることを望む。


これらの答えのどれもが.Offsetプロパティに言及していないことに気付きました。 これは、特定のセルを操作するときに、特に選択されたセルを参照するときにSelectアクションを使用しないようにするためにも使用できます(OPにはActiveCell記述されていActiveCell )。

ここにいくつかの例があります。

また、「ActiveCell」はJ4と仮定します。

ActiveCell.Offset(2, 0).Value = 12

  • これにより、セルJ6が値12に変更されます
  • マイナス2はJ2を参照していました

ActiveCell.Offset(0,1).Copy ActiveCell.Offset(,2)

  • これによりk4のセルがL4コピーされます。
  • 必要でない場合は、オフセットパラメータに「0」は必要ないことに注意してください(2)
  • 前の例と同様に、マイナス1はi4なります

ActiveCell.Offset(, -1).EntireColumn.ClearContents

  • これにより、列kのすべてのセルの値がクリアされます。

これらは、上記のオプションよりも「優れている」と言っているのではなく、選択肢をリストするだけです。


ワークブック、ワークシート、セル/範囲を常に記述してください。

例えば:

Thisworkbook.Worksheets("fred").cells(1,1)
Workbooks("bob").Worksheets("fred").cells(1,1)

エンドユーザーは常にボタンをクリックするだけで、フォーカスがブックの外に移動するとすぐに、コードは問題を完全に間違えるようになります。

そして、ブックのインデックスを決して使用しないでください。

Workbooks(1).Worksheets("fred").cells(1,1)

ユーザーがコードを実行したときに他のブックが開かれることはわかりません。


以下では、Selectアプローチ(OPが避けたいもの)とRangeアプローチ(これは質問に対する答えです)を比較しています。 だから、最初の選択を見るときに読書を止めないでください。

それは本当にあなたがしようとしていることに依存します。 とにかく簡単な例が役に立つかもしれません。 アクティブセルの値を "foo"に設定するとします。 ActiveCellを使用すると、次のような記述ができます。

Sub Macro1()
    ActiveCell.Value = "foo"
End Sub

"B2"のようにアクティブなセルではないセルに使用する場合は、まず次のように選択する必要があります。

Sub Macro2()
    Range("B2").Select
    Macro1
End Sub

範囲を使用すると、必要な任意のセルの値を設定するために使用できるより汎用的なマクロを書くことができます。

Sub SetValue(cellAddress As String, aVal As Variant)
    Range(cellAddress).Value = aVal
End Sub

次に、Macro2を次のように書き直すことができます:

Sub Macro2()
    SetCellValue "B2", "foo"
End Sub

そしてMacro1として:

Sub Macro1()
    SetValue ActiveCell.Address, "foo"
End Sub

これは少し物事をクリアするのに役立ちます。


私は誰もが長いものを与えて以来、短い答えを与えるつもりです。

マクロを記録して再利用するたびに.selectと.activateを取得します。 セルまたはシートを選択すると、セルまたはシートがアクティブになります。 その時点からRange.Valueような修飾されていない参照を使うたびに、アクティブなセルとシートだけが使用されます。 これは、コードの配置場所やユーザーがブックをクリックするのを見ないと問題になることもあります。

したがって、セルを直接参照することで、これらの問題を排除できます。 どちらが行く:

'create and set a range
Dim Rng As Excel.Range
Set Rng = Workbooks("Book1").Worksheets("Sheet1").Range("A1")
'OR
Set Rng = Workbooks(1).Worksheets(1).Cells(1, 1)

またはあなたができる

'Just deal with the cell directly rather than creating a range
'I want to put the string "Hello" in Range A1 of sheet 1
Workbooks("Book1").Worksheets("Sheet1").Range("A1").value = "Hello"
'OR
Workbooks(1).Worksheets(1).Cells(1, 1).value = "Hello"

これらの方法のさまざまな組み合わせがありますが、それは私のような辛抱強い人たちのためにできるだけ早く表現される一般的な考えです。


選択を避ける方法のいくつかの例

Dim 'd変数を使う

Dim rng as Range

変数を必要な範囲にSetします。 単一セル範囲を参照するには多くの方法があります

Set rng = Range("A1")
Set rng = Cells(1,1)
Set rng = Range("NamedRange")

またはマルチセル範囲

Set rng = Range("A1:B10")
Set rng = Range("A1", "B10")
Set rng = Range(Cells(1,1), Cells(10,2))
Set rng = Range("AnotherNamedRange")
Set rng = Range("A1").Resize(10,2)

Evaluateメソッドへのショートカットを使用することはできますが、これは効率が悪く、プロダクションコードでは一般的に避けるべきです。

Set rng = [A1]
Set rng = [A1:B10]

上のすべての例は、 アクティブシート上のセルを参照しています 。 特にアクティブなシートでのみ作業したい場合を除いて、 Worksheet変数をDimにすることをお勧めします

Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
Set rng = ws.Cells(1,1)
With ws
    Set rng = .Range(.Cells(1,1), .Cells(2,10))
End With

ActiveSheetで作業したい場合は、明確にするために明示的にすることをお勧めます。 しかし、いくつかのWorksheetメソッドがアクティブシートを変更するので、注意してください。

Set rng = ActiveSheet.Range("A1")

ここでも、これはアクティブなワークブックを指します。 特に、 ActiveWorkbookまたはThisWorkbookでのみ作業したい場合をThisWorkbookWorkbook変数をDimにする方がよいでしょう。

Dim wb As Workbook
Set wb = Application.Workbooks("Book1")
Set rng = wb.Worksheets("Sheet1").Range("A1")

ActiveWorkbookで作業したい場合は、明快にするために明示的にすることをお勧めます。 しかし、多くのWorkBookメソッドがアクティブブックを変更するので、注意してWorkBook

Set rng = ActiveWorkbook.Worksheets("Sheet1").Range("A1")

また、 ThisWorkbookオブジェクトを使用して、実行中のコードを含むブックを参照することもできます。

Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1")

一般的な(悪い)コードは、本を開いてデータをもう一度閉じる

これは悪いです:

Sub foo()
    Dim v as Variant
    Workbooks("Book1.xlsx").Sheets(1).Range("A1").Clear
    Workbooks.Open("C:\Path\To\SomeClosedBook.xlsx")
    v = ActiveWorkbook.Sheets(1).Range("A1").Value
    Workbooks("SomeAlreadyOpenBook.xlsx").Activate
    ActiveWorkbook.Sheets("SomeSheet").Range("A1").Value = v
    Workbooks(2).Activate
    ActiveWorkbook.Close()
End Sub

そして、より良くなるでしょう:

SUb foo()
    Dim v as Variant
    Dim wb1 as Workbook
    Dim  wb2 as Workbook
    Set wb1 = Workbooks("SomeAlreadyOpenBook.xlsx")
    Set wb2 = Workbooks.Open("C:\Path\To\SomeClosedBook.xlsx")
    v = wb2.Sheets("SomeSheet").Range("A1").Value
    wb1.Sheets("SomeOtherSheet").Range("A1").Value = v
    wb2.Close()
End Sub

範囲を範囲変数としてSubFunction渡す

Sub ClearRange(r as Range)
    r.ClearContents
    '....
End Sub

Sub MyMacro()
    Dim rng as Range
    Set rng = ThisWorkbook.Worksheets("SomeSheet").Range("A1:B10")
    ClearRange rng
End Sub

また、変数にメソッド( FindCopy )を適用する必要があります

Dim rng1 As Range
Dim rng2 As Range
Set rng1 = ThisWorkbook.Worksheets("SomeSheet").Range("A1:A10")
Set rng2 = ThisWorkbook.Worksheets("SomeSheet").Range("B1:B10")
rng1.Copy rng2

ある範囲のセルをループしている場合は、範囲値を最初にバリアント配列にコピーし、その範囲をループする方が良い(より速い)ことがよくあります

Dim dat As Variant
Dim rng As Range
Dim i As Long

Set rng = ThisWorkbook.Worksheets("SomeSheet").Range("A1:A10000")
dat = rng.Value  ' dat is now array (1 to 10000, 1 to 1)
for i = LBound(dat, 1) to UBound(dat, 1)
    dat(i,1) = dat(i,1) * 10 'or whatever operation you need to perform
next
rng.Value = dat ' put new values back on sheet

これは可能な限り小物です。





excel-vba