Office: Dynamische Zeitreihenanpassung mit VBA

Hallo Profis!

Schon lange her seit ich das letzte Mal hier war.......

Gleich zur Aufgabenstellung:
Ich baue derzeit ein ziemlich umfangreiches Excelmodell für Wirtschaftlichkeitsrechnungen neu zusammen. Um dynamisch den Betrachungszeitraum zu steueren, waren bis jetzt in allen relevanten Zellen Formeln, die sobald sie innerhalb dieses Zeitraumes lagen Werte ausgaben, ansonsten auf 0 waren.

Der Bewertungszeitraum wird über 2 Zellen gesteuert - Anfangsjahr und Zeitraum.

Da die ständige Befüllung der relevanten Zellen mit den Formeln (mehrere aber nicht alle worksheets - bis IV) ziemlich viel Speicherplatz verbraucht, würde ich gerne eine Lösung mit VBA finden. Der Code soll von der letzten vorhandenenen Spalte (also dem alten Endjahr) die Spalten bis zum neuen Endjahr weiterziehen.

Jedes worksheet in dem dieses Vorgehen relevant ist, besteht nur aus solchen Zellen, d.h. man kann den Bereich ab Zeile 6 in diesen wks bis ganz unten verwenden um nichts zu vergessen.

Bevor ich mich jetzt noch weiter im Selbstversuch betätige, würde ich gerne von den Profis erfahren wie ihr das Lösen würdet. Damit meine ich nicht unbedingt den Code, sondern eine kurze Beschreibung wie ich das am Besten aufbaue.

Ich hoffe ich konnte alles halbwegs verständlich erklären.

Freue mich schon auf Antworten!

mfg
Herbert

 

keine inputs?

 

Ich hab das Posting jetzt erst gesehen, aber auch nach 2, 3 mal durchlesen mit 1/2h Pause dazwischen fehlt mir irgendwie ein bißchen die Vorstellung, wie das ganze aussehen soll.

Gruß, Michael

 

Hallo Michael!

Danke für die Antwort! Werde morgen mal ein kleines Beispielfile hochladen, vielleicht erklärt das mehr!

Lg
Herbert

 

Hello Michael!

Anbei ein Teil der Datei. Natürlich gibt es auch Blätter wo die Zeitreihen nicht vorkommen, d.h. diese müssen aus dem VBA code ausgenommen sein.

Hab neben die Zellen eine kurze Beschreibung hingeschrieben - hoffe das erklärt meine Fragestellung besser.

Dank im Voraus für eine Antwort!

Lg
Herbert

 

Hm, ja, glaube schon...

Erster Ansatz wäre zwar ein VBA-Code, der auf "Change" von "c_startjr" reagiert. Aber vielleicht... geht es auch alles viel einfache und ohne VBA?

Deine Formel in dcf!e11 überprüft ja schon, ob e$9 gefüllt ist und rechnet nur dann, läßt ansonsten die Zelle leer. Gleiches machen auch die Formeln in den Zellen darunter (wenn auch tlw. ohne das $ übrigens). Das wäre doch ein viel leichterer und offensichtlicher Ansatz...

Wenn Du die Formeln aus e11:e36 bis zur Spalte iv durchziehst, brauchst Du nur noch dafür sorgen, daß die Überschrift mit den Jahreszahlen (Zeile 9) nur dann mit einem Jahr gefüllt wird, wenn es im Zeitraum ist.

Code:

e9: =wenn(d9+1<=c_endejr;d9+1;"")

So oder so ähnlich müßte es doch schon gehen, oder nicht?

Gruß, Michael

 

Servus Michael!

So war es ja beim alten Modell - das Problem ist, dass das Ziehen der Formeln bis IV eine Menge Speicherplatz braucht, auch wenn dann gar nichts angezeigt wird.

Ich kann auch ein Steuerelement einbauen, dass zu drücken ist wenn sich der Zeitraum ändern soll (ruft dann den Code auf).

Die Frage ist, wie ich am praktischten die Formeln und Formatierungen in den verschiedenen Blättern per VBA verlängern lasse (löschen ist ja nicht das Problem).

Lg
Herbert

 

Wenn diese (über'n groben Daumen) 5000 Formeln bereits ein Speicherplatz-Problem auslösen, dann scheint Dein Rechner aber ein grundlegend anderes Problem zu haben. Hier werden in einigen Mappen 30.000 und mehr Formeln durchgerechnet, was sich zwar in kurzem Ruckeln bemerkbar macht, aber ansonsten kein ernsthaftes Problem darstellt.

Aber ok, Ansatz per VBA... Das gestaltet sich auch nicht unbedingt ganz einfach, weil das Feld "c_ibn" sich nicht in der aktuellen Arbeitsmappe befindet. Es gibt mehrere Möglichkeiten, die Formeln zu aktualisieren, vor allem:

"Change" - reagiert auf Änderungen in einer Zelle - Ausnahme: Durch Formeln (neu) berechnete Werte. Das fällt also für die Überwachung von "c_endejr" aus.

Leider kriegt man auch nur das Feld "c_horizont" mit "Change" überwacht, nicht aber "c_ibn", da dies eben in einer anderen Mappe ist.

"SelectionChange" - reagiert auf Bewwegung des Cursors - Das willst Du wahrscheinlich nicht, dann jede Cursorbewegung würde dann zu einem Ruckeln führen.

Bleibt eigentlich nur "Calculate" - wird aufgerufen, wenn die Formeln neu durchgerechnet werden und ist damit irgendwo hinter "Change" anzusieden, was die Häufigkeit der Aufrufe (Rechenzeit) betrifft, denn nicht jedes "Change" zieht auch immer gleich ein "Calculate" nach sich - nur, wenn Formel-relevante Zellen verändert werden.

Das wäre zumindest eine Alternative, denn ich schätze, das Einklinken in "Calculate" wird Dir dann genauso wenig Spaß bringen, wie das Ziehen der Formeln bis IV. Aber schauen wir mal...

Der folgende Code im Modul von "I_Bewertungsparam" sollte tun, was Du möchtest:

Code:

Option Explicit

Private Sub Formeln_aktualisieren()

    Dim i As Long
    Dim y1 As Long
    Dim x1 As Long
    Dim sht As Worksheet
    Dim y2 As Long
    Dim x2 As Long

    For i = 1 To 2

        y1 = 9
        x1 = 5
        Select Case i
            Case 1:
                Set sht = Worksheets("dcf")
                y2 = 36
            Case 1:
                Set sht = Worksheets("eva")
                y2 = 33
        End Select
        x2 = x1 + Range("c_endejr").Value - Range("c_startjr").Value - 1

        If (x2 > x1) Then
            sht.Range(sht.Cells(y1, x1 + 1), sht.Cells(y2, 256)).ClearContents
            sht.Range(sht.Cells(y1, x1), sht.Cells(y2, x1)).Copy Destination:=sht.Range(sht.Cells(y1, x1 + 1), sht.Cells(y2, x2))
        End If

    Next i

End Sub

Private Sub btn_Formeln_aktualisieren_Click()

    Formeln_aktualisieren

End Sub

Private Sub Worksheet_Calculate()

    Formeln_aktualisieren

End Sub

Ein Button namens "btn_Formeln_aktualisieren" gehört dann noch auf das Blatt. Dann kannst Du schauen, ob "Calculate" oder der Button die bessere Alternative ist und das jeweils andere Ereignis deaktivieren.

Allerdings solltest Du nochmal die Formeln in Spalte "D" überprüfen, ob die Bezüge so wirklich korrekt sind nach dem Kopieren, oder ob vielleicht noch das eine oder andere $-Zeichen zu verteilen ist. Genauer konnte ich, wegen Zellbezügen auf eine andere Mappe, nicht testen.

Gruß, Michael

 

Danke für die rasche Antwort - werde es mir morgen mal genau ansehen und versuchen.

Zwecks Speicherplatz - das sind nur 2 Blätter von vielen - da kommen ja noch Bilanz, GuV, Geldflussrechnung und einige Detailblätter dazu - also ein Haufen Formeln :-)

Melde mich bei Erfolg!

Lg
Herbert