PDA

Vollständige Version anzeigen : Formeln aktualisieren aus Quellmappe


RainerGruber
11.07.2014, 06:18
Hallo zusammen,

ich habe eine allgemeine Frage zu welcher ich nicht wirklich eine zufriedenstellende Antwort auf meiner Recherche gefunden habe, vl kann mir jemand von euch hierzu eine klare Aussage machen:

Ich habe ein einer Mappe eine Bedarfsmatrix (365 Spalten (Tage)* 120Zeilen (Materialien)). In jeder dieser Zellen ist ein sverweis zu einer anderen Mappe, aus welcher die Bedarfsdaten gezogen werden. Um die Bedarfszahlen zu aktualisieren, muss die zweite Mappe (Quellmappe mit aktuellen Bedarfen) kurz geöffnet und gespeichert werden, damit sich die sverweise aktualisieren. Nun meine Frage:

Ich lasse die Quellmappe per Makro öffnen und als nächste Anweisung auch gleich wieder speichern und schließen (wbBedarfeQuellmappe.Close savechanges:=True).

Laufe ich hier Gefahr, dass diese Quellmappe geschlossen wird, BEVOR sich die sverweise in der zweiten Mappe gänzlich aktualisiert haben? (Beide Mappen sind relativ groß weshalb alle Lade- und Berechnungsvorgänge relativ lange benötigen) Oder kann ich davon ausgehen, dass VBA jede neue Anweisung erst dann beginnt, wenn alle vorgelagerten Anweisungen und damit verbundenen Lade-, Berechnungs- und Aktualisierungsvorgänge abgeschlossen sind?


Hat da jemand einen Rat für mich?
Danke!"

LG
Rainer

Mc Santa
11.07.2014, 07:03
Hallo,

zwei Fragen dazu:
Ich habe es so verstanden, dass du aus Mappe A mit einem Sverweis Daten aus Mappe B holst. Damit sich der Sverweis aus Mappe A aktualisiert, öffnest und schließt du Mappe B. Warum kannst du nicht direkt in Mappe A die Daten aktualisieren?

Zweitens habe ich einen Tipp für dich:
Häufig sind diese Sverweisabfragen so aufgebaut, dass pro Zeile immer wieder der gleiche Begriff gesucht wird, aber unterschiedliche Spalten abgefragt werden. Hier sparst du immens Rechenleistung, wenn du einmal die Position abfragst und anschließend immer wieder auf diese Zahl zurückgreifst. Sie dir das Beispiel an (Ich habe irgendeinen Anwendungsfall erfunden):
<br/><b><em>Abfrage</em></b><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:40px;" /><col style="width:97px;" /><col style="width:97px;" /><col style="width:97px;" /><col style="width:97px;" /><col style="width:97px;" /><col style="width:97px;" /><col style="width:97px;" /><col style="width:97px;" /><col style="width:97px;" /><col style="width:97px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td><td>I</td><td>J</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">1</td><td colspan="4" rowspan="1; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; "><b>Langsame Variante: Überall Sverweis</b></td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td colspan="5" rowspan="1; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; "><b>Schnelle Variante: Einmal Vergleich, sonst Index!</b></td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Produkt ID</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">auf Lager</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Lagerplatz</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Preis</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Produkt ID</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Hilfsspalte</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">auf Lager</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Lagerplatz</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">Preis</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">3</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">2</td><td style=" color:#000000; border-color:#000000; background-color:#FF9900 ;; text-align:center; ">4</td><td style=" color:#000000; border-color:#000000; background-color:#FF9900 ;; text-align:center; ">B13</td><td style=" color:#000000; border-color:#000000; background-color:#FF9900 ;; text-align:center; ">22,3</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">2</td><td style=" color:#000000; border-color:#000000; background-color:#FF9900 ;; text-align:center; ">3</td><td style=" color:#000000; border-color:#000000; background-color:#00FF00 ;; text-align:center; ">4</td><td style=" color:#000000; border-color:#000000; background-color:#00FF00 ;; text-align:center; ">B13</td><td style=" color:#000000; border-color:#000000; background-color:#00FF00 ;; text-align:center; ">22,3</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">4</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">6</td><td style=" color:#000000; border-color:#000000; background-color:#FF9900 ;; text-align:center; ">22</td><td style=" color:#000000; border-color:#000000; background-color:#FF9900 ;; text-align:center; ">C28</td><td style=" color:#000000; border-color:#000000; background-color:#FF9900 ;; text-align:center; ">3,22</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">6</td><td style=" color:#000000; border-color:#000000; background-color:#FF9900 ;; text-align:center; ">7</td><td style=" color:#000000; border-color:#000000; background-color:#00FF00 ;; text-align:center; ">22</td><td style=" color:#000000; border-color:#000000; background-color:#00FF00 ;; text-align:center; ">C28</td><td style=" color:#000000; border-color:#000000; background-color:#00FF00 ;; text-align:center; ">3,22</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">5</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">7</td><td style=" color:#000000; border-color:#000000; background-color:#FF9900 ;; text-align:center; ">34</td><td style=" color:#000000; border-color:#000000; background-color:#FF9900 ;; text-align:center; ">F5</td><td style=" color:#000000; border-color:#000000; background-color:#FF9900 ;; text-align:center; ">22</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:center; ">7</td><td style=" color:#000000; border-color:#000000; background-color:#FF9900 ;; text-align:center; ">8</td><td style=" color:#000000; border-color:#000000; background-color:#00FF00 ;; text-align:center; ">34</td><td style=" color:#000000; border-color:#000000; background-color:#00FF00 ;; text-align:center; ">F5</td><td style=" color:#000000; border-color:#000000; background-color:#00FF00 ;; text-align:center; ">22</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">6</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">7</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FF9900 ;; text-align:left; ">Langsam</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">8</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#00FF00 ;; text-align:left; ">Schnell</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width:1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><tr style="background-color:#CCFF66; text-align:center; font-weight:bold; "><td>&nbsp;verbundene Zellen&nbsp;</td></tr><tr><td>A1:&nbsp;D1</td></tr><tr><td>F1:J1</td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px;font-size:11pt; background-color:#ffffff; width:800px;padding-left:2pt; padding-right:2pt; "><tr style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td colspan="3" > verwendete Formeln </td><td> </td></tr><tr valign="top" style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td> Zelle </td><td> Formel</td><td> Bereich </td> <td>N/A</td></tr><tr><td>B3:B5</td><td>=SVERWEIS($A3;Quelldatenbank!$A$1:$F$11;5)</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>C3:C5</td><td>=SVERWEIS($A3;Quelldatenbank!$A$1:$F$11;6)</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>D3:&nbsp;D5</td><td>=SVERWEIS($A3;Quelldatenbank!$A$1:$F$11;2)</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>G3:G5</td><td>=VERGLEICH($F3;Quelldatenbank!$A$1:$A$11;0)</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>H3:H5</td><td>=INDEX(Quelldatenbank!$A$1:$F$11;$G3;5)</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>I3:I5</td><td>=INDEX(Quelldatenbank!$A$1:$F$11;$G3;6)</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>J3:J5</td><td>=INDEX(Quelldatenbank!$A$1:$F$11;$G3;2)</td><td>&nbsp;</td><td>&nbsp;</td></tr></table><table cellspacing="0" cellpadding="0"><tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href="http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip" >Excel-Inn.de</a></td></tr> <tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href='http://Hajo-Excel.de/tools.htm' >Hajo-Excel.de</a></td></tr><tr style="text-align:left; font-weight:bold;" ><td style="text-align:left; font-size: xx-small" >XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007</td></tr><tr style="text-align:left; font-weight:bold; " ><td style="text-align:left; font-size: xx-small" > Add-In-Version 18.01 einschl. 64 Bit</td></tr></table><br/>Fragen dazu gerne :)

VG

RainerGruber
14.07.2014, 11:58
Hi Mc Santa,

danke erstmal für deinen Tipp! Die Sache mit match-index anstatt sverweis hört sich sehr hilfreich an. Das werde ich auf jeden Fall ein meiner Mappe in welcher die Bedarfe generiert werden versuchen soweit wie möglich umzusetzen, um Rechenleistung zu sparen.

Zu deiner Frage warum ich nicht die Daten direkt in Mappe A aktualisieren kann...
Mappe A: Analysemappe in welcher auf Basis der Bedarfszahlen für einen definierbaren Zeitraum verschiedene Analysen und Berechnungen ausgeführt werden
Mappe B: Diese Mappe dient rein zur Berechnung der Bedarfszahlen welche sich durch die Stücklistenaufspaltung bis in die unterste Ebene erstellt. Sprich man gibt die Bedarfe an Fertigerzeugnissen ein und erhält die Bedarfe aller im Fertigerzeugnis enthaltenen Halbfabrikaten, Zukaufteilen, Rohstoffen, Hilfsstooffen etc.

In Mappe A werden grundsätzlich keine Bedarfszahlen eingetragen, sondern rein durch einen Klick auf den "Bedarfe aktualisieren-Button" in der Userform aus der Mappe B über einen SVerweis in die Mappe A gezogen und anschließend von der formel getrennt. Sprich nachdem die Bedarfe in eine Bedarfsmatrix in der Analysemappe gezogen wurden, werden die Formeln gelöscht um das file so klein wie möglich zu halten. Da sich die Analyse in Mappe A nur immer auf einige wenige Produkte bezieht, wäre es nicht sinnvoll die gesamten generierten Bedarfe aus Mappe B in Mappe A zu führen. (Mappe B hat ca 30 MB)...


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

LG,
rainer

Mc Santa
14.07.2014, 12:10
Hallo,

ok die Erklärung ist für mich verständlich, leider kann ich nicht genau sagen, ob das Makro tatsächlich stoppt. Theoretisch sollte es dies tun, und wenn sich die Formeln nur die eigene Tabelle beziehen, funktioniert es auch.

Aber ich habe auch schon das Problem gehabt, dass ich Daten von einem SharePoint über Formeln abgefragt habe und mein Makro einfach weiter läuft, bevor es ein Ergebnis hat.
Leider habe ich dafür keine (automatische) Lösung gefunden, vielleicht kann dir hier jemand anderes helfen.

VG

RainerGruber
17.07.2014, 08:55
Ich habe nun bereits einige Testläufe gemacht (ohne schrittweises debuggen, also Normaldurchlauf) und es scheint als ob das Makro tatsächlich erst mit einer Anweisung fortfährt, wenn die vorherige gänzlich durchgeführt wurde. Davon bin ich auch ausgegangen, aber bei teilweise extrem langen Rechenzeiten war ich eben nicht 100 Prozent sicher ob ich mich verlassen kann.. Scheinbar spielt aber die Rechenzeit keine Rolle. zumindest in meinen Versuchen ist es zu keinen Falschberechnungen gekommen...

LG