Makro hängt sich manchmal auf?!

Forumthread: Makro hängt sich manchmal auf?!

23.08.2017 11:06:22

Martin

Hallo zusammen,
ich habe ein Makro für eine geschützte Datei, bei der spezielle Datenbankformeln "wertkopiert" werden müssen. Es wird eine Kopie des Registerblattes erstellt und dort das PW aufgehoben und die Daten wertkopiert. Leider hängt sich das Makro bei einigen Kollegen auf - bei mir läuft es in wenigen Sekunden durch. Woran könnte es liegen bzw. welche Befehle können verbessert/optimiert werden? Danke euch im Voraus!

Sub Wertkopie_DBR_DBS()
ActiveSheet.Unprotect "PW"
Calculate
Dim Bereich As Range
Range("A1").Select
ActiveCell.SpecialCells(xlLastCell).Select
Endspalte = Selection.Columns(Selection.Columns.Count).Column
EndZeile = Selection.Rows(Selection.Rows.Count).Row
Startspalte = 1
Startzeile = 1
Set Bereich = Range(Cells(Startzeile, Startspalte), Cells(EndZeile, Endspalte))
For Each z In Bereich
On Error Resume Next
' Wertkopie für DBRn, DBSn, SUBNM, VIEW und DIMNM - Funktionen:
If InStr(z.Formula, "DBR") Or _
InStr(z.Formula, "DBS") Or _
InStr(z.Formula, "SUBNM") Or _
InStr(z.Formula, "VIEW") Or _
InStr(z.Formula, "DIMNM") Then
z.Value = z.Value
End If
Next z
Range("A1").Select
'Next AnzAB
Mldg = "TEXT"
MsgBox (Mldg)
Range("A1").Select
'ActiveSheet.Protect "PW"
End Sub

2
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Makro hängt sich manchmal auf?!

23.08.2017 12:28:55

JoNNy
AW: Makro hängt sich manchmal auf?!

23.08.2017 12:38:32

ChrisL

AW: Makro hängt sich manchmal auf?!

23.08.2017 12:28:55

JoNNy

Servus Martin,
zunächst kannst du deinen Code dahingehend optimieren,
dass du nicht mit Selections arbeitest sondern,
direkt alles ansprichst. z.B.


Endspalte = Range("A1").SpecialCells(xlLastCell).Columns(Selection.Columns.Count).Column

Aber das muss nicht unbedingt sein.
Dann wäre noch die Frage ob ihr beide die gleiche Office Version verwendet,
wenn nicht kann das auch ein Problem darstellen.
Gruß JoNNy

AW: Makro hängt sich manchmal auf?!

23.08.2017 12:38:32

ChrisL

hi Martin
- Ist die automatische Formelberechnung ausgeschaltet?
- Ist die Neuberechnung (Applikation.Calculate) notwendig?
- Bezieht sich der Code immer auf das gerade aktive Blatt?
- Die Prüfung läuft über den ganzen benutzten Bereich. Könnte man dies evtl. einschränken?
Da aktuell sowieso der ganze benutzte Bereich angesprochen wird, könntest du wie folgt kürzen:

Sub t()
Dim Z As Range
For Each Z In ActiveSheet.UsedRange
If InStr(Z.Formula, "DBR") Or _
InStr(Z.Formula, "DBS") Or _
InStr(Z.Formula, "SUBNM") Or _
InStr(Z.Formula, "VIEW") Or _
InStr(Z.Formula, "DIMNM") Then
Z.Value = Z.Value
End If
Next Z
End Sub

M.E. nicht ursächlich aber dennoch verbesserungswürdig:
- auf Select/Activate verzichten (siehe Antwort Jonny)
- Variablen konsequent deklarieren
cu
Chris

Infobox / Tutorial

Makro optimieren, das sich manchmal aufhängt

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Um ein Excel-Makro zu optimieren, das sich manchmal aufhängt, kannst du folgende Schritte befolgen:

Vermeide Select und Activate: Diese Befehle können die Ausführungsgeschwindigkeit deines Makros beeinträchtigen. Greife direkt auf die Zellen zu. Zum Beispiel:
```
Endspalte = Range("A1").SpecialCells(xlLastCell).Column
```
Überprüfe die Formelberechnung: Stelle sicher, dass die automatische Berechnung nicht deaktiviert ist. Du kannst dies in den Excel-Optionen unter "Formeln" anpassen.
Einschränkung des benutzten Bereichs: Statt den gesamten benutzten Bereich zu durchlaufen, könntest du den Bereich einschränken, um die Leistung zu verbessern.

Code optimieren: Verwende die UsedRange-Eigenschaft, um nur den tatsächlich verwendeten Bereich anzusprechen:

Sub OptimiertesMakro()
   Dim Z As Range
   For Each Z In ActiveSheet.UsedRange
       If InStr(Z.Formula, "DBR") Or _
          InStr(Z.Formula, "DBS") Or _
          InStr(Z.Formula, "SUBNM") Or _
          InStr(Z.Formula, "VIEW") Or _
          InStr(Z.Formula, "DIMNM") Then
           Z.Value = Z.Value
       End If
   Next Z
End Sub

Variablen deklarieren: Achte darauf, dass alle Variablen im Code deklariert sind. Dies verbessert die Lesbarkeit und kann die Ausführungsgeschwindigkeit erhöhen.

Häufige Fehler und Lösungen

Fehler: Makro hängt sich auf, wenn es auf große Datenmengen zugreift.
- Lösung: Reduziere den Umfang der Daten, die das Makro bearbeitet, indem du den benutzten Bereich einschränkst.
Fehler: Unterschiedliche Excel-Versionen bei Nutzern.
- Lösung: Stelle sicher, dass alle Nutzer dieselbe Excel-Version verwenden, um Kompatibilitätsprobleme zu vermeiden.
Fehler: Automatische Berechnung ist deaktiviert.
- Lösung: Aktiviere die automatische Berechnung in den Excel-Optionen.

Alternative Methoden

Falls das Makro immer noch nicht zuverlässig funktioniert, gibt es alternative Methoden, um die Daten zu verarbeiten:

Power Query: Nutze Power Query, um Daten zu transformieren und zu laden, anstatt VBA zu verwenden. Dies kann oft schneller und benutzerfreundlicher sein.
Formeln: Statt ein Makro zu verwenden, kannst du auch Excel-Formeln nutzen, um die benötigten Berechnungen durchzuführen.

Praktische Beispiele

Hier ist ein einfaches Beispiel, wie du mit VBA Datenwertkopien effizienter erstellen kannst:

Sub WertkopieOptimiert()
    Dim Z As Range
    For Each Z In ActiveSheet.UsedRange
        If InStr(Z.Formula, "DBR") Or InStr(Z.Formula, "DBS") Then
            Z.Value = Z.Value
        End If
    Next Z
End Sub

Dieses Beispiel beschleunigt den Prozess, indem es nur die relevanten Formeln anspricht und die Verwendung von Select vermeidet.

Tipps für Profis

Performance-Optimierung: Schalte die Bildschirmaktualisierung und das Berechnen während der Makroausführung aus:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
' Makro-Code hier
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

Debugging: Setze Haltepunkte, um problematische Stellen im Code zu identifizieren und zu analysieren.

FAQ: Häufige Fragen

1. Warum hängt sich mein Excel-Makro auf?
Das kann an großen Datenmengen, nicht optimierten Schleifen oder unterschiedlichen Excel-Versionen liegen.

2. Wie kann ich die Leistung meines Makros verbessern?
Vermeide Select, beschränke den benutzten Bereich und deaktiviere die Bildschirmaktualisierung während der Ausführung des Makros.

Beliebteste Forumthreads (12 Monate)