So vermeiden Sie die Verwendung von Select in Excel VBA
Ich habe viel über die verständliche Abscheu vor der Verwendung .Select
in Excel VBA gehört, bin mir aber nicht sicher, wie ich die Verwendung vermeiden soll. Ich stelle fest, dass mein Code wiederverwendbarer wäre, wenn ich Variablen anstelle von Select
Funktionen verwenden könnte. Ich bin mir jedoch nicht sicher, wie ich mich auf Dinge (wie die ActiveCell
usw.) beziehen soll, wenn ich sie nicht benutze Select
.
Ich habe diesen Artikel über Bereiche und dieses Beispiel über die Vorteile der Nichtverwendung von select gefunden , aber ich kann nichts darüber finden, wie .
Antworten
Einige Beispiele zur Vermeidung von Auswahl
Verwenden Sie Dim
'd Variablen
Dim rng as Range
Set
die Variable auf den gewünschten Bereich. Es gibt viele Möglichkeiten, sich auf einen Einzelzellenbereich zu beziehen:
Set rng = Range("A1")
Set rng = Cells(1, 1)
Set rng = Range("NamedRange")
Oder ein Mehrzellenbereich:
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)
Sie können die Verknüpfung zur Evaluate
Methode verwenden, dies ist jedoch weniger effizient und sollte im Produktionscode generell vermieden werden.
Set rng = [A1]
Set rng = [A1:B10]
Alle obigen Beispiele beziehen sich auf Zellen auf dem aktiven Blatt . Wenn Sie nicht speziell nur mit dem aktiven Blatt arbeiten möchten, ist es besser, auch eine Worksheet
Variable zu dimmen :
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
Wenn Sie mit dem arbeiten möchten ActiveSheet
, ist es aus Gründen der Klarheit am besten, explizit zu sein. Aber seien Sie vorsichtig, da einige Worksheet
Methoden das aktive Blatt ändern.
Set rng = ActiveSheet.Range("A1")
Dies bezieht sich wiederum auf die aktive Arbeitsmappe . Sofern Sie nicht speziell nur mit ActiveWorkbook
oder arbeiten möchten ThisWorkbook
, ist es besser, auch eine Workbook
Variable zu dimmen .
Dim wb As Workbook
Set wb = Application.Workbooks("Book1")
Set rng = wb.Worksheets("Sheet1").Range("A1")
Wenn Sie mit dem arbeiten möchten ActiveWorkbook
, ist es aus Gründen der Klarheit am besten, explizit zu sein. Aber seien Sie vorsichtig, da viele WorkBook
Methoden das aktive Buch verändern.
Set rng = ActiveWorkbook.Worksheets("Sheet1").Range("A1")
Sie können das ThisWorkbook
Objekt auch verwenden , um auf das Buch zu verweisen, das den laufenden Code enthält.
Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1")
Ein häufiger (schlechter) Code besteht darin, ein Buch zu öffnen, einige Daten abzurufen und dann wieder zu schließen
Das ist schlecht:
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
Und es wäre besser wie:
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
Übergeben Sie Bereiche als Bereichsvariablen an Ihre Sub
s und Function
s:
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
Sie sollten auch Methoden (wie Find
und Copy
) auf Variablen anwenden :
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
Wenn Sie einen Bereich von Zellen durchlaufen, ist es häufig besser (schneller), die Bereichswerte zuerst in ein variantes Array zu kopieren und diesen zu durchlaufen:
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
Dies ist ein kleiner Vorgeschmack auf das, was möglich ist.
Zwei Hauptgründe , warum .Select
, .Activate
, Selection
, Activecell
, Activesheet
, Activeworkbook
, sollten usw. vermieden werden
- Es verlangsamt Ihren Code.
- Dies ist normalerweise die Hauptursache für Laufzeitfehler.
Wie vermeiden wir das?
1) Arbeiten Sie direkt mit den relevanten Objekten
Betrachten Sie diesen Code
Sheets("Sheet1").Activate
Range("A1").Select
Selection.Value = "Blah"
Selection.NumberFormat = "@"
Dieser Code kann auch als geschrieben werden
With Sheets("Sheet1").Range("A1")
.Value = "Blah"
.NumberFormat = "@"
End With
2) Deklarieren Sie bei Bedarf Ihre Variablen. Der gleiche Code wie oben kann geschrieben werden als
Dim ws as worksheet
Set ws = Sheets("Sheet1")
With ws.Range("A1")
.Value = "Blah"
.NumberFormat = "@"
End With
Ein kleiner Schwerpunkt, den ich zu allen zuvor gegebenen hervorragenden Antworten hinzufügen werde:
Das wahrscheinlich größte Mittel, um die Verwendung von Select zu vermeiden, besteht darin , benannte Bereiche (kombiniert mit aussagekräftigen Variablennamen) in Ihrem VBA-Code so weit wie möglich zu verwenden . Dieser Punkt wurde oben erwähnt, aber er wurde ein wenig beschönigt; es verdient jedoch besondere Aufmerksamkeit.
Hier sind einige zusätzliche Gründe, benannte Bereiche liberal zu nutzen, obwohl ich mir sicher mehr vorstellen kann.
Benannte Bereiche erleichtern das Lesen und Verstehen Ihres Codes.
Beispiel:
Dim Months As Range
Dim MonthlySales As Range
Set Months = Range("Months")
' E.g, "Months" might be a named range referring to A1:A12
Set MonthlySales = Range("MonthlySales")
' E.g, "Monthly Sales" might be a named range referring to B1:B12
Dim Month As Range
For Each Month in Months
Debug.Print MonthlySales(Month.Row)
Next Month
Es ist ziemlich offensichtlich , was die genannten Bereiche Months
und MonthlySales
enthalten, und was die Prozedur tut.
Warum ist das wichtig? Teilweise, weil es für andere Menschen einfacher ist, es zu verstehen, aber selbst wenn Sie die einzige Person sind, die Ihren Code jemals sehen oder verwenden wird, sollten Sie dennoch benannte Bereiche und gute Variablennamen verwenden, da Sie vergessen, was Sie damit vorhatten Ein Jahr später verschwenden Sie 30 Minuten damit, herauszufinden, was Ihr Code tut.
Benannte Bereiche stellen sicher, dass Ihre Makros nicht beschädigt werden, wenn (nicht wenn!) Die Konfiguration der Tabelle geändert wird.
Überlegen Sie, ob das obige Beispiel folgendermaßen geschrieben wurde:
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Range("A1:A12")
Set rng2 = Range("B1:B12")
Dim rng3 As Range
For Each rng3 in rng1
Debug.Print rng2(rng3.Row)
Next rng3
Dieser Code funktioniert zunächst einwandfrei - bis Sie oder ein zukünftiger Benutzer entscheiden, "gee wiz, ich denke, ich werde eine neue Spalte mit dem Jahr in Spalte hinzufügen A
!" Oder eine Spaltenspalte zwischen den Monaten und einfügen Verkaufsspalten, oder fügen Sie jeder Spalte einen Header hinzu. Jetzt ist Ihr Code kaputt. Und weil Sie schreckliche Variablennamen verwendet haben, brauchen Sie viel mehr Zeit, um herauszufinden, wie Sie das Problem beheben können, als es dauern sollte.
Wenn Sie zunächst benannte Bereiche verwendet hätten, könnten die Spalten Months
und Sales
um beliebig verschoben werden, und Ihr Code würde weiterhin einwandfrei funktionieren.
Ich werde die kurze Antwort geben, da alle anderen die lange gegeben haben.
Sie erhalten .select und .activate, wenn Sie Makros aufnehmen und wiederverwenden. Wenn Sie eine Zelle oder ein Blatt auswählen, wird es nur aktiviert. Von diesem Punkt an, wenn Sie nicht qualifizierte Referenzen verwenden, wie Range.Value
sie nur die aktive Zelle und das Blatt verwenden. Dies kann auch problematisch sein, wenn Sie nicht beobachten, wo Ihr Code platziert ist oder ein Benutzer auf die Arbeitsmappe klickt.
Sie können diese Probleme also beseitigen, indem Sie direkt auf Ihre Zellen verweisen. Welches geht:
'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)
Oder du könntest
'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"
Es gibt verschiedene Kombinationen dieser Methoden, aber das wäre die allgemeine Idee, die für ungeduldige Menschen wie mich so kurz wie möglich ausgedrückt wird.
"... und stelle fest, dass mein Code wiederverwendbarer wäre, wenn ich Variablen anstelle von Select-Funktionen verwenden könnte."
Ich kann mir zwar nicht mehr als eine isolierte Handvoll Situationen vorstellen, in denen .Select
eine bessere Wahl als eine direkte Zellreferenzierung wäre, aber ich würde mich zur Verteidigung erheben Selection
und darauf hinweisen, dass sie nicht aus den gleichen Gründen verworfen werden .Select
sollte, die vermieden werden sollten.
Es gibt Zeiten, in denen kurze, zeitsparende Makro-Subroutinen, die Tastenkombinationen zugewiesen werden, durch Tippen auf einige Tasten viel Zeit sparen. Die Möglichkeit, eine Gruppe von Zellen auszuwählen, auf die der Betriebscode angewendet werden soll, wirkt Wunder, wenn mit Daten in der Tasche gearbeitet wird, die nicht einem arbeitsblattweiten Datenformat entsprechen. Ähnlich wie Sie möglicherweise eine Gruppe von Zellen auswählen und eine Formatänderung anwenden, kann die Auswahl einer Gruppe von Zellen, für die spezieller Makrocode ausgeführt werden soll, eine erhebliche Zeitersparnis bedeuten.
Beispiele für ein auf Auswahl basierendes Unterframework:
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
Der tatsächlich zu verarbeitende Code kann von einer einzelnen Zeile bis zu mehreren Modulen reichen. Ich habe diese Methode verwendet, um lang laufende Routinen für eine unregelmäßige Auswahl von Zellen zu initiieren, die die Dateinamen externer Arbeitsmappen enthalten.
Kurz gesagt, nicht Selection
wegen seiner engen Verbindung mit .Select
und wegwerfen ActiveCell
. Als Arbeitsblatteigenschaft hat es viele andere Zwecke.
(Ja, ich weiß, dass es um diese Frage ging .Select
, Selection
aber ich wollte alle Missverständnisse beseitigen, auf die unerfahrene VBA-Codierer schließen könnten.)
Vermeiden Select
und Activate
ist der Schritt, der Sie ein bisschen besser VBA-Entwickler macht. Im Allgemeinen Select
und Activate
wenn ein Makro aufgezeichnet wird, wird das Parent
Arbeitsblatt oder der Bereich immer als aktives betrachtet.
So können Sie Folgendes vermeiden Select
und Activate
in folgenden Fällen:
Hinzufügen eines neuen Arbeitsblatts und Kopieren einer Zelle darauf:
Von (Code generiert mit Makrorecorder):
Sub Makro2()
Range("B2").Select
Sheets.Add After:=ActiveSheet
Sheets("Tabelle1").Select
Sheets("Tabelle1").Name = "NewName"
ActiveCell.FormulaR1C1 = "12"
Range("B2").Select
Selection.Copy
Range("B3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
Zu:
Sub TestMe()
Dim ws As Worksheet
Set ws = Worksheets.Add
With ws
.Name = "NewName"
.Range("B2") = 12
.Range("B2").Copy Destination:=.Range("B3")
End With
End Sub
Wenn Sie den Bereich zwischen Arbeitsblättern kopieren möchten:
Von:
Sheets("Source").Select
Columns("A:D").Select
Selection.Copy
Sheets("Target").Select
Columns("A:D").Select
ActiveSheet.Paste
Zu:
Worksheets("Source").Columns("A:D").Copy Destination:=Worksheets("Target").Range("a1")
Verwenden Sie ausgefallene benannte Bereiche
Sie können mit ihnen zugreifen []
, was im Vergleich zum anderen Weg wirklich schön ist. Überprüfen Sie sich:
Dim Months As Range
Dim MonthlySales As Range
Set Months = Range("Months")
Set MonthlySales = Range("MonthlySales")
Set Months =[Months]
Set MonthlySales = [MonthlySales]
Das Beispiel von oben würde folgendermaßen aussehen:
Worksheets("Source").Columns("A:D").Copy Destination:=Worksheets("Target").[A1]
Werte nicht kopieren, sondern nehmen
Wenn Sie dazu bereit select
sind, kopieren Sie normalerweise höchstwahrscheinlich etwas. Wenn Sie nur an den Werten interessiert sind, ist dies eine gute Option, um die Auswahl zu vermeiden:
Range("B1:B6").Value = Range("A1:A6").Value
Versuchen Sie immer, auch auf das Arbeitsblatt zu verweisen
Dies ist wahrscheinlich der häufigste Fehler in vba . Wenn Sie Bereiche kopieren, wird manchmal nicht auf das Arbeitsblatt verwiesen, und daher betrachtet VBA das falsche Blatt als ActiveWorksheet.
'This will work only if the 2. Worksheet is selected!
Public Sub TestMe()
Dim rng As Range
Set rng = Worksheets(2).Range(Cells(1, 1), Cells(2, 2)).Copy
End Sub
'This works always!
Public Sub TestMe2()
Dim rng As Range
With Worksheets(2)
.Range(.Cells(1, 1), .Cells(2, 2)).Copy
End With
End Sub
Kann ich wirklich nie .Select
oder .Activate
für irgendetwas verwenden?
- Ein gutes Beispiel dafür, wann die Verwendung gerechtfertigt sein könnte
.Activate
und.Select
wann Sie sicherstellen möchten, dass ein bestimmtes Arbeitsblatt aus visuellen Gründen ausgewählt ist. Beispiel: Ihr Excel wird immer mit dem zuerst ausgewählten Deckblatt geöffnet, unabhängig davon, welches ActiveSheet beim Schließen der Datei verwendet wurde.
Somit ist so etwas wie der folgende Code absolut in Ordnung:
Private Sub Workbook_Open()
Worksheets("Cover").Activate
End Sub
Ein weiteres gutes Beispiel ist, wenn Sie alle Blätter in eine PDF-Datei exportieren müssen, wie in diesem Fall erwähnt. Wie vermeide ich in diesem Beispiel select / active-Anweisungen in VBA?
Wenn ein Befehl funktioniert nur mit
ActiveWindow
wie ActiveWindow.Zoom oder ActiveWindow.FreezePanes
Bitte beachten Sie, dass ich im Folgenden den Select-Ansatz (den das OP vermeiden möchte) mit dem Range-Ansatz vergleiche (und dies ist die Antwort auf die Frage). Hören Sie also nicht auf zu lesen, wenn Sie die erste Auswahl sehen.
Es hängt wirklich davon ab, was Sie versuchen zu tun. Auf jeden Fall könnte ein einfaches Beispiel nützlich sein. Angenommen, Sie möchten den Wert der aktiven Zelle auf "foo" setzen. Mit ActiveCell würden Sie ungefähr Folgendes schreiben:
Sub Macro1()
ActiveCell.Value = "foo"
End Sub
Wenn Sie es für eine Zelle verwenden möchten, die nicht aktiv ist, z. B. für "B2", sollten Sie es zuerst wie folgt auswählen:
Sub Macro2()
Range("B2").Select
Macro1
End Sub
Mithilfe von Bereichen können Sie ein allgemeineres Makro schreiben, mit dem Sie den Wert jeder gewünschten Zelle auf einen beliebigen Wert festlegen können:
Sub SetValue(cellAddress As String, aVal As Variant)
Range(cellAddress).Value = aVal
End Sub
Dann können Sie Macro2 wie folgt umschreiben:
Sub Macro2()
SetCellValue "B2", "foo"
End Sub
Und Macro1 als:
Sub Macro1()
SetValue ActiveCell.Address, "foo"
End Sub
Geben Sie immer die Arbeitsmappe, das Arbeitsblatt und die Zelle / den Bereich an.
Zum Beispiel:
Thisworkbook.Worksheets("fred").cells(1,1)
Workbooks("bob").Worksheets("fred").cells(1,1)
Da Endbenutzer immer nur auf Schaltflächen klicken und der Fokus von der Arbeitsmappe abweicht, mit der der Code arbeiten möchte, laufen die Dinge völlig schief.
Und verwenden Sie niemals den Index einer Arbeitsmappe.
Workbooks(1).Worksheets("fred").cells(1,1)
Sie wissen nicht, welche anderen Arbeitsmappen geöffnet sind, wenn der Benutzer Ihren Code ausführt.
Diese Methoden sind eher stigmatisiert, also übernehmen Sie die Führung von Vityata und Jeeped, um eine Linie in den Sand zu ziehen:
Anruf .Activate
, .Select
, Selection
, ActiveSomething
Methoden / Eigenschaften
Grundsätzlich, weil sie hauptsächlich aufgerufen werden, um Benutzereingaben über die Benutzeroberfläche der Anwendung zu verarbeiten. Da es sich um die Methoden handelt, die aufgerufen werden, wenn der Benutzer Objekte über die Benutzeroberfläche verarbeitet, werden sie vom Makrorecorder aufgezeichnet. Daher ist das Aufrufen in den meisten Situationen entweder spröde oder redundant: Sie müssen keine auswählen Objekt, um eine Aktion mit Selection
direkt danach auszuführen .
Diese Definition regelt jedoch Situationen, in denen sie erforderlich sind:
Wenn nennen .Activate
, .Select
, .Selection
, .ActiveSomething
Methoden / Eigenschaften
Grundsätzlich, wenn Sie erwarten, dass der Endbenutzer eine Rolle bei der Ausführung spielt.
Wenn Sie entwickeln und erwarten, dass der Benutzer die Objektinstanzen auswählt , die von Ihrem Code verarbeitet werden sollen, .Selection
oder .ActiveObject
sind Sie entsprechend.
Auf der anderen Seite .Select
und .Activate
von Nutzen, wenn Sie auf die nächste Aktion des Benutzers schließen können und möchten, dass Ihr Code den Benutzer führt, wodurch er möglicherweise Zeit und Mausklicks spart. Wenn Ihr Code beispielsweise gerade eine brandneue oder aktualisierte Instanz eines Diagramms erstellt hat, möchte der Benutzer es möglicherweise auschecken, und Sie können .Activate
es oder sein Blatt aufrufen , um dem Benutzer die Zeit für die Suche zu sparen. Wenn Sie wissen, dass der Benutzer einige Bereichswerte aktualisieren muss, können Sie diesen Bereich programmgesteuert auswählen.
IMHO Verwendung von .select
kommt von Leuten, die wie ich angefangen haben, VBA durch Notwendigkeit zu lernen, indem sie Makros aufzeichneten und dann den Code modifizierten, ohne zu bemerken, dass .select
und anschließend selection
nur ein unnötiger Mittelsmann ist.
.select
kann vermieden werden, wie viele bereits gepostet haben, indem direkt mit den bereits vorhandenen Objekten gearbeitet wird, was verschiedene indirekte Verweise wie die Berechnung von i und j auf komplexe Weise und die anschließende Bearbeitung der Zelle (i, j) usw. ermöglicht.
Ansonsten ist nichts implizit falsch an sich .select
selbst und Sie können leicht eine Verwendung dafür finden, z. B. habe ich eine Tabelle, die ich mit Datum fülle, aktiviere ein Makro, das etwas Magie damit macht, und exportiere es in einem akzeptablen Format auf ein separates Blatt, das erfordert jedoch einige endgültige manuelle (unvorhersehbare) Eingaben in eine benachbarte Zelle. Hier kommt also der Moment .select
, der mir diese zusätzliche Mausbewegung und das Klicken erspart.
Um die Verwendung der .Select
Methode zu vermeiden , können Sie eine Variable festlegen, die der gewünschten Eigenschaft entspricht.
► Wenn Sie beispielsweise den Wert in möchten, können Cell A1
Sie eine Variable festlegen, die der value-Eigenschaft dieser Zelle entspricht.
- Beispiel
valOne = Range("A1").Value
► Wenn Sie beispielsweise den Codenamen der you could set a variable equal to the
Eigenschaft 'Sheet3 Codename` dieses Arbeitsblatts möchten .
- Beispiel
valTwo = Sheets("Sheet3").Codename
Mir ist aufgefallen, dass keine dieser Antworten die .Offset-Eigenschaft erwähnt . Dies kann auch verwendet werden, um die Verwendung der Select
Aktion beim Bearbeiten bestimmter Zellen zu vermeiden , insbesondere in Bezug auf eine ausgewählte Zelle (wie im OP erwähnt ActiveCell
).
Hier sind einige Beispiele.
Ich gehe auch davon aus, dass "ActiveCell" J4 ist .
ActiveCell.Offset(2, 0).Value = 12
- Dadurch wird die Zelle
J6
auf den Wert 12 geändert - Ein Minus -2 hätte auf J2 verwiesen
ActiveCell.Offset(0,1).Copy ActiveCell.Offset(,2)
- Dies wird kopieren Sie die Zelle in
k4
zuL4
. - Beachten Sie, dass "0" im Offset-Parameter nicht benötigt wird, wenn er nicht benötigt wird (, 2).
- Ähnlich wie im vorherigen Beispiel wäre eine minus 1
i4
ActiveCell.Offset(, -1).EntireColumn.ClearContents
- Dadurch werden Werte in allen Zellen in der Spalte k gelöscht.
Diese sollen nicht sagen, dass sie "besser" sind als die oben genannten Optionen, sondern nur Alternativen auflisten.
Wie vermeide ich Copy-Paste?
Seien wir ehrlich: Dieser erscheint häufig beim Aufnehmen von Makros:
Range("X1").Select
Selection.Copy
Range("Y9).Select
Selection.Paste
Während das einzige, was die Person will, ist:
Range("Y9").Value = Range("X1").Value
Daher würde ich anstelle von Copy-Paste in VBA-Makros den folgenden einfachen Ansatz empfehlen:
Destination_Range.Value = Source_Range.Value
In diesem Beispiel wird anhand der Funktion .Parent gezeigt, wie das Festlegen nur einer myRng-Referenz den dynamischen Zugriff auf die gesamte Umgebung ohne .Select, .Activate, .Activecell, .ActiveWorkbook, .ActiveSheet usw. ermöglicht. (Es gibt keine generische .Child- Funktion.)
Sub ShowParents()
Dim myRng As Range
Set myRng = ActiveCell
Debug.Print myRng.Address ' An address of the selected cell
Debug.Print myRng.Parent.name ' The name of sheet, where MyRng is in
Debug.Print myRng.Parent.Parent.name ' The name of workbook, where MyRng is in
Debug.Print myRng.Parent.Parent.Parent.name ' The name of application, where MyRng is in
' You may use this feature to set reference to these objects
Dim mySh As Worksheet
Dim myWbk As Workbook
Dim myApp As Application
Set mySh = myRng.Parent
Set myWbk = myRng.Parent.Parent
Set myApp = myRng.Parent.Parent.Parent
Debug.Print mySh.name, mySh.Cells(10, 1).Value
Debug.Print myWbk.name, myWbk.Sheets.Count
Debug.Print myApp.name, myApp.Workbooks.Count
' You may use dynamically addressing
With myRng
.Copy
' Pastes in D1 on sheet 2 in the same workbook, where the copied cell is
.Parent.Parent.Sheets(2).Range("D1").PasteSpecial xlValues
' Or myWbk.Sheets(2).Range("D1").PasteSpecial xlValues
' We may dynamically call active application too
.Parent.Parent.Parent.CutCopyMode = False
' Or myApp.CutCopyMode = False
End With
End Sub
Der Hauptgrund, warum Sie Select oder Activesheet niemals verwenden, ist, dass die meisten Benutzer mindestens ein paar weitere Arbeitsmappen geöffnet haben (manchmal Dutzende), wenn sie Ihr Makro ausführen und wenn sie während der Ausführung Ihres Makros von Ihrem Blatt wegklicken und auf ein anderes klicken Buch, das sie geöffnet haben, dann ändert sich das "Aktivitätsblatt", und die Zielarbeitsmappe für einen nicht qualifizierten Befehl "Auswählen" ändert sich ebenfalls.
Im besten Fall stürzt Ihr Makro ab, im schlimmsten Fall schreiben Sie möglicherweise Werte oder ändern Zellen in der falschen Arbeitsmappe, ohne sie "rückgängig machen" zu können.
Ich habe eine einfache goldene Regel, der ich folge: Fügen Sie Variablen mit den Namen "wb" und "ws" für ein Arbeitsmappenobjekt und ein Arbeitsblattobjekt hinzu und verwenden Sie diese immer, um auf mein Makrobuch zu verweisen. Wenn ich mich auf mehr als ein Buch oder mehr als ein Blatt beziehen muss, füge ich weitere Variablen hinzu.
Zum Beispiel,
Dim wb as Workbook
Dim ws as Worksheet
Set wb = ThisWorkBook
Set ws = wb.sheets("Output")
Der Befehl "Set wb = ThisWorkbook" ist absolut wichtig. "ThisWorkbook" ist ein spezieller Wert in Excel und bezeichnet die Arbeitsmappe, aus der Ihr VBA-Code derzeit ausgeführt wird . Eine sehr hilfreiche Verknüpfung zum Festlegen Ihrer Arbeitsmappenvariablen.
Nachdem Sie dies oben in Ihrem Sub getan haben, könnte die Verwendung nicht einfacher sein. Verwenden Sie sie einfach überall dort, wo Sie "Auswahl" verwenden würden:
So ändern Sie den Wert der Zelle "A1" in "Ausgabe" in "Hallo" anstelle von:
Sheets("Output").Activate
ActiveSheet.Range("A1").Select
Selection.Value = "Hello"
Wir können dies jetzt tun:
ws.Range("A1").Value = "Hello"
Dies ist nicht nur viel zuverlässiger und weniger absturzgefährdet, wenn der Benutzer mit mehreren Tabellen arbeitet. Es ist auch viel kürzer, schneller und einfacher zu schreiben.
Wenn Sie Ihre Variablen immer "wb" und "ws" nennen, können Sie als zusätzlichen Bonus Code von einem Buch in ein anderes kopieren und einfügen. Dies funktioniert normalerweise mit minimalen Änderungen, falls vorhanden.