Ich habe Probleme mit einer VBA-Funktion, die aus einer Excel-Zelle aufgerufen wird und nicht automatisch aktualisiert wird bzw. die manuell mit F9, Umschalt+F9 usw. aktualisiert wird.
Ich verwende „Microsoft® Excel® für Microsoft 365 MSO (Version 2207 Build 16.0.15427.20166) 32-Bit“ unter Windows 10.
Ich habe eine Tabelle mit verschiedenen Werten, die ich zur Erstellung eines Budgets verwende. Jede Spalte ist ein Teammitglied und jede Zeile ist eine Aufgabe. Jedem Benutzer werden Stunden für jede Aufgabe zugewiesen. Jeder Aufgabe wird ein Multiplikator mit Namen zugewiesen.
Ein einfaches Beispiel:
| Aufgabe [cellA1] | Person 1 | Person 2 | Multiplikator |
|---|---|---|---|
| Erste Aufgabe | 1,00 | 0,25 | Thema |
| Zweite Aufgabe | 0,05 | 0,15 | Teilnehmer |
Wie dargestellt benötigt Person 1 1 Stunde pro Thema und 0,05 Stunden pro Teilnehmer. Person 2 benötigt 0,25 Stunden pro Thema und 0,15 Stunden pro Teilnehmer.
Die Anzahl der Themen und Teilnehmer ist in benannten Bereichen enthalten (z. B. „_topics“, „_participants“). „Thema“ und „Teilnehmer“ sind Platzhalternamen. Was sie sind, ist unwichtig (es könnten „Hunde“ oder „Katzen“ sein). Wichtig ist, dass jedem ein einzelner numerischer Wert zugewiesen ist. Aufgaben können Multiplikatoren gemeinsam nutzen (z. B. kann mehr als eine Zeile „Thema“ verwenden).
Ziel ist es, die Anforderungen eines komplexen Budgets (viele Mitglieder und viele Aufgaben) mit sehr einfachen Eingaben (Änderung der Anzahl der Themen, der Anzahl der Teilnehmer usw.) zu modellieren.
Wenn dies eine kleine Tabelle wäre, würde ich eine Formel in der Zelle verwenden, etwa:
=sumif(D1:D2,"Topic",B1:B2) * _topic + sumif(D1:D2,"Participant",B1:B2) * _participants
Wenn _Thema = 5 und _Teilnehmer = 10, dann wäre die Summe für Person 1 5,5 (1 * 5 + 0.05 * 10).
In meinem Fall gibt es viele Zeilen und viele Multiplikatoren, und es gibt auch eine zweite Spalte, die die Aufgabe einer Projektphase zuweist. Daher habe ich eine VBA-Funktion geschrieben, um den Inhalt der Zelle lesbar zu halten und das Hinzufügen von Elementen in Zukunft zu erleichtern. Es ist nicht elegant, aber es tut, was ich brauche.
Function SUM_MEMBER_VALUES(phase As String, data_range As Range, phase_range As Range, mult_range As Range)
p = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Project")
mc = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Market_collections") * _
Range("_markets_collections").Value
mr = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Market_calls") * _
Range("_markets_calls").Value
c = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Caller") * _
Range("_callers").Value
b = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Brand") * _
Range("_brands").Value
bmc = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Brand-Market_collections") * _
Range("_brand_markets_collections").Value
bmr = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Brand-Market_calls") * _
Range("_brand_markets_calls").Value
bmpc = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Brand-Market-Product_collections") * _
Range("_brand_market_products_collections").Value
bmpr = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Brand-Market-Product_calls") * _
Range("_brand_market_products_calls").Value
bp = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Brand-Product") * _
Range("_brand_products").Value
uc = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Unsuccessful Calls") * _
Range("_calls_unsuccessful").Value
br = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Brand-Responses") * _
Range("_brand_responses").Value
pr = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Product-Responses") * _
Range("_product_responses").Value
SUM_MEMBER_VALUES = p + mc + mr + c + b + bmc + bmr + bmpc + bmpr + bp + uc + br + pr
End Function
Die Funktion wird in der Zelle einer Tabelle wie folgt aufgerufen:
| Phase [ZelleA1] | Person 1 | Person 2 | Gesamt |
|---|---|---|---|
| Beginnen | =SUM_MEMBER_VALUES($A2,[Bereich von Person1],[Bereich der Aufgabenphase],[Bereich des Aufgabenmultiplikators]) | =SUM_MEMBER_VALUES($A2,[Bereich von Person2],[Bereich der Aufgabenphase],[Bereich des Aufgabenmultiplikators]) | =SUMME(B2:B3) |
Diese Funktion funktioniert. Mein Problem ist, dass sie nicht automatisch aktualisiert wird, wenn ich die Werte für die benannten Variablen ändere. Wenn sich also _topic von 5 auf 10 ändert, SUM_MEMBER_VALUESändern sich die Ergebnisse nicht.
Die Berechnungsoptionen sind auf „Automatisch“ eingestellt. F9, UMSCHALT+F9 und Menübandschaltflächen zum Neuberechnen lösen die Aktualisierung nicht aus. Die Datei ist makrofähig und ich habe versucht, die Datei zu schließen/erneut zu öffnen und den Computer neu zu starten.
Die einzige Möglichkeit, die Ergebnisse zu aktualisieren, besteht darin, sie manuell einzugeben und für jede Zelle, die aufruft, die Eingabe zu drücken SUM_MEMBER_VALUES. Das zu ignorieren ist mühsam und außerdem gefährlich, da man leicht die Aktualisierung einer Zelle vergisst (insbesondere, wenn jemand anderes als ich das Modell verwendet, was ja beabsichtigt ist).
Ich kann mir nur vorstellen, dass die Funktion die Änderung der Werte möglicherweise nicht erkennt, weil sie auf eine bestimmte Art und Weise an die Funktion übergeben oder von ihr referenziert wird (d. h. ich habe einen Wert als Bereich übergeben und der Bereich hat sich nicht geändert, obwohl sich der Inhalt des Bereichs geändert hat). Mir fällt jedoch keine Problemumgehung ein.
FRAGE:Gibt es eine Möglichkeit, sicherzustellen, dass die VBA-Funktion automatisch aktualisiert wird, wenn sich die Eingabewerte ändern?
AKTUALISIEREN
SHIFT+CTRL+ALT+F9Erstellt den Abhängigkeitsbaum neu und führt eine vollständige Neuberechnung durch. Außerdem Application.Volatilesollte das Hinzufügen oben in der Funktion bei jeder Änderung am Arbeitsblatt eine Neuberechnung erzwingen. Einige Mitwirkende haben jedoch davor gewarnt, da dadurch die Aktualisierung erzwungen wird, auch wenn keine Aktualisierung erforderlich ist.1
Dies löst meine unmittelbaren Bedürfnisse, aber ich bin neugierig, ob jemand eine Lösung zwischen den beiden Lösungen hat (keine manuelle Neuberechnung erforderlich und keine Neuberechnung bei jeder Änderung einer Schriftart).
Antwort1
Ein möglicher Kompromiss wäre, der Funktion statische Variablen hinzuzufügen, die die „alten“ Werte in der Tabelle enthalten, und den restlichen Code zu überspringen, wenn sich nichts ändert … klar wie Kloßbrühe?
Nehmen wir an, der ListObject-Name ist Tbl1 und die ListColumn-Namen sind Ihre Spaltenüberschriften (Task, Person 1, Person 2, Multiplikator).
Static sTaskOld$, sP1Old$, sP2Old$, sMultOld$
Dim sTask$, sP1$, sP2$, sMult$
Application.Volatile
sTask = Join(Application.Transpose([Tbl1[Task]]),"")
sP1 = Join(Application.Transpose([Tbl1[Person 1]]),"")
sP2 = Join(Application.Transpose([Tbl1[Person 2]]),"")
sMult = Join(Application.Transpose([Tbl1[Multiplier]]),"")
'first recalc, static variables will be null
if sTask<>sTaskOld or sP1<>sP1Old or sP2<>sP2Old or sMult<>sMultOld then
...
end if
'save existing values to static variables
sTaskOld = sTask
sP1Old = sP1
sP2Old = sP2
sMultOld = sMult
Natürlich könnten Sie anstelle einzelner Variablen ein Array verwenden, aber dieses Beispiel ist konzeptionell.
Die Funktion würde zwar trotzdem ausgelöst, aber Sie würden zumindest nicht viel Verarbeitungszeit mit dem Vergleichen der alten und aktuellen Werte verbringen.