MS-Office-Forum

Zurück   MS-Office-Forum > Microsoft Office > Microsoft Excel
Registrieren Forum Hilfe Alle Foren als gelesen markieren

Banner und Co.

Antworten
Ads
Themen-Optionen Ansicht
Alt 06.12.2018, 22:17   #1
fornacu
Neuer Benutzer
Neuer Benutzer
Standard VBA - Einfügen mehrere Formeln in .range - Prrformance

Moin an die VBA-Profis:

Der Code an sich funktioniert, ich hoffe nur, ihn performanter hin zu bekommen!

Ich habe also folgenden Code, bei welchem durch Doppelklick in die Spalte A unter die Zeile, in die geklickt wurde, eine weitere Zeile einfügt und viele verschiedene Formeln in die einzelnen Zellen gesetzt werden.
(Ziel ist es, im Kalkulationsblatt nachträglich Zeilen einzufügen und in diesen funktionierende Formeln zu haben, im echten VBA-Code sind es knapp 30 Formeln unterschiedlichster Art je Zeile).

Über ein Dropdownmenü (Einträge: 1,3,5,10,20,100) kann der Nutzer wählen, wie viele leere (aber mit Formeln gefüllte) Zeilen er einfügen will. -> in 'zielCounter' geschrieben

Werden nun 100 neue Zeilen ausgewählt, juckelt der Code immer wieder durch jede neu hinzugefügte Zeile durch und trägt alle Formeln ein, dann nächste Zeile neu, alle Formeln... usw.

Alle Formeln haben das unten angegebene Schema mit INDIREKT("#"&ZEILE()).

Gibt es nun eine Möglichkeit, die Formeln auf einmal am Ende der Routine in die neue range einzufügen? Und gibt es eine performante Alternative zu den vielen INDIREKT()-Formeln?

[DELL Server R740 mit 6 CPUs und ca. 100GB Ram, also keine schwache Maschine]

Code:

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As range, Cancel As Boolean)
    If Intersect(Target, [A:A]) Is Nothing Or ActiveSheet.range("V1") = False Then Exit Sub ' Bereichseinschränkung für Doppelklickfunktion
    
        On Error GoTo DeadInTheWater
        Application.ScreenUpdating = False ' Screenupdating ausschalten
        Application.Calculation = xlCalculationManual 'automat.Berechnung ausschalten
        
        Dim zielCounter As Long
        Dim anzahlRepeat As Long
        Dim counter As Integer
        Cancel = True
        
        zielCounter = ActiveSheet.range("V2")
        
    Select Case zielCounter
        Case 1
            anzahlRepeat = 1
        Case 2
            anzahlRepeat = 3
        Case 3
            anzahlRepeat = 5
        Case 4
            anzahlRepeat = 10
        Case 5
            anzahlRepeat = 20
        Case 6
            anzahlRepeat = 50
        Case 7
            anzahlRepeat = 100
        Case Else
        GoTo DeadInTheWater
    End Select

    counter = 1
    Do Until counter = anzahlRepeat + 1
    
        Rows(Target.row + 1).Insert 'Shift:=xlDown
        ActiveSheet.range("A" & Target.row + 1).FormulaLocal = _
            "=INDIREKT("A"&ZEILE())+INDIREKT("A"&ZEILE())"
        ActiveSheet.range("B" & Target.row + 1).FormulaLocal = _
            "=INDIREKT("F"&ZEILE())*INDIREKT("X"&ZEILE())"
        ActiveSheet.range("C" & Target.row + 1).FormulaLocal = _
            "=INDIREKT("B"&ZEILE())*2+INDIREKT("M"&ZEILE())*6"
       [...uvm...]
        
        counter = counter + 1
    Loop
        
    Select Case counter
        Case Is < 5
            GoTo EverythingFine
        Case Is >= 5
            ActiveSheet.range("V2") = 1
        Case Else
        GoTo DeadInTheWater
    End Select
    
    GoTo EverythingFine

DeadInTheWater:
    MsgBox "Fehler beim multiplen Einfügen", _
        vbOKOnly + vbCritical, _
        "Fehler"
    Application.CutCopyMode = False
    Cells(Target.row + 1, Target.Column).Select
    Application.Calculation = xlCalculationAutomatic 'automat.Berechnung einschalten
    Application.ScreenUpdating = True ' Screenupdating einschalten
    Exit Sub

EverythingFine:
    Application.CutCopyMode = False
    Cells(Target.row + 1, Target.Column).Select
    Application.Calculation = xlCalculationAutomatic 'automat.Berechnung einschalten
    Application.ScreenUpdating = True ' Screenupdating einschalten
    Exit Sub
        
End Sub
Schon mal vielen Dank für Eure Hilfe!
fornacu ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 07.12.2018, 08:50   #2
Beverly
MOF Guru
MOF Guru
Standard

Hi,

weshalb fügst du die Formeln per Schleife ein? Einfacher ist es doch, wenn du die Zeile darüber kopierst und mittels PasteSpecial nur Formeln in den eignefügten Zellbereich überträgst oder du verwendest AutoFill. Allerdings müsstest du eine Referenzzeile haben (oberste Zeile - kann man ausblenden), damit auch nach der "sichtbar" ersten Zeile eingefügt werden kann.


GrußformelBeverly's Excel - Inn

__________________

Bitte im Beitrag eine kurze Rückmeldung auch in dem Fall geben, wenn ein Problem gelöst wurde - dies hilft auch anderen Usern, wenn sie den betreffenden Thread lesen.
Möchtest du dich außerdem für die Hilfe bei der Lösung deines Problems bedanken? Das kannst du ganz einfach durch die Bewertung eines Beitrags (Schalter unten links).
Beverly ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 07.12.2018, 08:54   #3
Oge
MOF Profi
MOF Profi
Standard

Hallo f...,


A) Die ADHS-Funktion INDIREKT ist hier doch gar nicht notwendig oder sogar hinderlich beim Einfügen zusätzlicher Spalten.
Sie ist eine volatile Funktion und sollte so wenig wie möglich genutzt werden. Bei jeder Änderung in der Datei werden alle Formeln mit dieser Funktion und alle Formeln der von diesen Zellen abhängigen Zellen neu berechnet.

B) Nutze die Möglichkeiten der strukturierten (oder intelligenten) Tabellen (Strg+T).
Wenn du dann Zeilen einfügst, werden die Zellen mit Formeln automatisch angelegt und du brauchst sie nicht über VBA einzutragen.
Ein zusätzlicher Vorteil ist, dass die Formeln bei strukturierten Verweisen leichter verstanden werden.

__________________

helmut

Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität.
Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.
Oge ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 07.12.2018, 13:42   #4
fornacu
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

Vielen Dank für die Rückmeldungen!

@Beverly: Die Referenzzeile war die bisher gewählte Option. Bekommt der Nutzer irgendwann eingeblendet und dann kaputt. Da hiervon Kalkulationen und Angebote abhängen, ist mir das zu gefährlich. Aber nach dem einmaligen Anlegen der Formel in einer Schleife per PasteSpecial zu kopieren finde ich gut. Werden ich ausprobieren.

@Oge: Mir gefallen die Indirekt-Formeln auch nicht, aber nur so kann ich sicher sein, dass keine Formeln durch Nutzeraktionen falsche Bezüge haben. Beispiel ist hier z.B.:

Zeile 1: 50 Stck á 10€
Zeile 2: 2 Stck á 1000€

Die Eingabe ist hier sehr variabel und der Nutzer baut damit eine Kalkulation auf, die eventuelle Nacharbeiten fordert (Zeile=Position=Bauteil vergessen). Zielt nun plötzlich der Preis von Zeile 2 auf den Wert von Zeile 1 gehen mir 1980€ flöten. Das ist mir Performanceeinbußen wert.

Aber der erste Blick in strukturierte Tabellen sieht sehr vielversprechend aus. Das werde ich mir genauer anschauen und vermutlich einsetzen. Nur komplett neue Materie...

Also noch mal vielen Dank für die Anregung!
fornacu ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 07.12.2018, 15:05   #5
Oge
MOF Profi
MOF Profi
Standard

Hallo f...,

A) zu:

Zitat:

...aber nur so kann ich sicher sein, dass keine Formeln durch Nutzeraktionen falsche Bezüge haben.

Das verstehe ich nicht. Welchen Fehler durch eine Nutzeraktion verhinderst du denn mit der Indirekt-Funktion gegenüber einer Formel mit direkten Einträgen von Relativbezügen?

B) Ich glaube weiterhin, dass eine strukturierte Tabelle der richtige Ansatz ist. Mit dem strukturiertem Verweis wird dir in der Formel die Überschrift der gewählten Spalte angezeigt.

Wenn du trotzdem Formeln mit der aktuellen Zeile per VBA einfügen möchtest, dann geht das auch ohne Indirekt. zB
Code:

INDEX(F:F;Zeile())
anstatt
INDIREKT("F"&ZEILE())
Bei 100 eingefügten Zeilen sollte dies nicht nur das Einfügen der Zeilen mehr als 1000 mal schneller machen, sondern auch die Bearbeitung nach dem Eintragen eines beliebigen Wertes in der Datei mehr als 100 mal.

__________________

helmut

Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität.
Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.

Geändert von Oge (07.12.2018 um 15:14 Uhr).
Oge ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 07.12.2018, 16:34   #6
fornacu
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

Zitat:

Das verstehe ich nicht. Welchen Fehler durch eine Nutzeraktion verhinderst du denn mit der Indirekt-Funktion gegenüber einer Formel mit direkten Einträgen von Relativbezügen?

Jede Zeile hat bei mir die gleichen Formeln und die gleiche Funktion, es wird Zeilenweise berechnet und damit kalkuliert.

Habe ich folgende Tabelle:

A1=5 B1=A1+2=7
A2=9 B2=A2+2=11
A99 = Summe(B:B) = 18

Und nun markiert der Nutzer die Zelle B1, 'klicktundhält' den Rahmen (den schwarzen mit dem kleinen Kästchen rechts unten, aber nicht am Kästchen) und verschiebt die Zelle B1 auf B2, dann steht in der Tabelle:

A1=5 B1=A1+2=7
A2=9 B2=A1+2=7
A99 = Summe(B:B) = 14

Da es sich aber wie gesagt um unternehmenskritische Kalkulationen handelt, wäre das fatal. Daher die Lösung mit Indirekt... dann steht nämlich, egal, was er verschiebt:

A1=5 B1=INDIREKT("A"&ZEILE())+2=7
A2=9 B2=INDIREKT("A"&ZEILE())+2=11
A99 = Summe(B:B) = 18

(Sollte der Nutzer in der Spalte verrutschen, schmeißt ihm die Tabelle fast immer ein #NV)

INDEX(F:F;Zeile()) ist dennoch viel besser!

Vielen Dank dafür! Und bei uns bin ich schon der Excel-Profi....

Ich werde mir dennoch die strukturierten Tabellen mal anschauen. [Richtig was dazu gelernt, danke!]
fornacu ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 07.12.2018, 16:40   #7
Beverly
MOF Guru
MOF Guru
Standard

Hi,

Zitat: von fornacu Beitrag anzeigen

@Beverly: Die Referenzzeile war die bisher gewählte Option. Bekommt der Nutzer irgendwann eingeblendet und dann kaputt.

Setze doch einfach einen Blattschutz, dann gibt es das Problem nicht. Außerdem kannst du mit deinem Code prüfen, ob in der ersten Zeile eine Formel steht - falls ja, dann einfach AutoFill, falls Nein dann Formel per VBA eintragen und AutoFill.

Wenn ich mir deine Formeln so anschaue würde ich fast vermuten, dass es ausreicht die Formel nach diesem Prinzip zu schreiben =A1+A1, =F1*X1 und =B1*2+M1*6 - die Zeilennummer passt sich doch automatisch an wenn die Formeln nach unten kopiert werden, da es keine absolute Zeilenangabe ist.


GrußformelBeverly's Excel - Inn

__________________

Bitte im Beitrag eine kurze Rückmeldung auch in dem Fall geben, wenn ein Problem gelöst wurde - dies hilft auch anderen Usern, wenn sie den betreffenden Thread lesen.
Möchtest du dich außerdem für die Hilfe bei der Lösung deines Problems bedanken? Das kannst du ganz einfach durch die Bewertung eines Beitrags (Schalter unten links).
Beverly ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 07.12.2018, 18:20   #8
fornacu
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

Zitat:

etze doch einfach einen Blattschutz, dann gibt es das Problem nicht. Außerdem kannst du mit deinem Code prüfen, ob in der ersten Zeile eine Formel steht - falls ja, dann einfach AutoFill, falls Nein dann Formel per VBA eintragen und AutoFill.

Blattschutz geht nicht, da dann die Gruppierungen nicht mehr funktionieren (Was aber bei einer Tabelle, die aktiv von Spalte A bis CD bearbeitet wird, elementar ist)

_____________________________

Habe nun den Code angepasst, den Insert in der Schleife gelassen und das Einfügen der Formeln auf Range angewand. Bei der Performanceanalyse zeigt sich, dass der Insert langsam ist bei 100 Zeilen. Das Einfügen der Formeln geht schnell.

Code:

Änderungen fett - Performanceprobleme rot
Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As range, Cancel As Boolean)
    If Intersect(Target, [A:A]) Is Nothing Or ActiveSheet.range("V1") = False Then Exit Sub
    
        On Error GoTo DeadInTheWater
        Application.ScreenUpdating = False ' Screenupdating ausschalten
        Application.Calculation = xlCalculationManual 'automat.Berechnung ausschalten
        
        [...]

    counter = 1
Do Until counter = anzahlRepeat + 1
    
        Rows(Target.row + 1).Insert 'Shift:=xlDown
        counter = counter + 1
    Loop

    
        ActiveSheet.range("A" & Target.row + 1 & ":A" & Target.row + anzahlRepeat).FormulaLocal = _
            "=ZEILE() - 3"
        [...]
        
    Select Case counter
        Case Is < 5
            GoTo EverythingFine
        Case Is >= 5
            ActiveSheet.range("V2") = 1
        Case Else
        GoTo DeadInTheWater
    End Select
    
    GoTo EverythingFine

    [...]
        
End Sub
Nun also die Analyse und das Ergebnis inkl. Lösung:

Code:

Variante A mit Schleife
    counter = 1
    Do Until counter = anzahlRepeat + 1
        Rows(Target.row + 1).Insert 'Shift:=xlDown
        counter = counter + 1
    Loop
--> 31,5 sec

Code:

Variante B mit Range
       range(Rows(Target.row + 1), Rows(Target.row + anzahlRepeat)).Insert
--> 1,1 sec
fornacu ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Ads
Antworten


Aktive Benutzer in diesem Thema: 1 (Registrierte Benutzer: 0, Besucher: 1)
 
Themen-Optionen
Ansicht

Forumregeln
Es ist Ihnen nicht erlaubt, neue Themen zu verfassen.
Es ist Ihnen nicht erlaubt, auf Beiträge zu antworten.
Es ist Ihnen nicht erlaubt, Anhänge anzufügen.
Es ist Ihnen nicht erlaubt, Ihre Beiträge zu bearbeiten.

vB Code ist An.
Smileys sind An.
[IMG] Code ist An.
HTML-Code ist An.
Gehe zu


Alle Zeitangaben in WEZ +1. Es ist jetzt 00:12 Uhr.


Partner und Co.
Access-Paradies -Alles rund um die Datenbank Microsoft Access -Code -Programme-Tools -Tipps   Kostenlose Tipps & Tricks, Downloads und Programme   www.kulpa-online.com - Tipps - Tricks - Tutorials - Meinungen - Downloads uvm...   vb@rchiv · Willkommen in der Welt der VB Programmierung   Access-Garhammer - Hier finden Sie jede Menge Beispiel-Datenbanken zu Access und mehr ...   mcseboard.de   Die Top Seite für Excel-VBA-Makros uvm.

Powered by: vBulletin Version 3.6.2 (Deutsch)
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.

Copyright ©2000-2018 MS-Office-Forum. Alle Rechte vorbehalten.
Copyright ©Design: Manuela Kulpa ©Rechte: Günter Kramer
Eine Verwendung der Inhalte in anderen Publikationen, auch auszugsweise,
ist ohne ausdrückliche Zustimmung der Autoren nicht gestattet.