Zielwertsuche in VBA
Rolf Kamieth
Zinseszins bei Anlage über ein Jahr), möchte die Zielwertsuche zur
Lösung des Problems benutzen.
Zunächst habe ich mir ein entsprechendes Makro aufgezeichnet:
Sub Makro1()
Range("J14").Select
Range("J14").GoalSeek Goal:=0, ChangingCell:=Range("I14")
End Sub
Wenn ich jetzt über Extra/Makro/Makros... das Makro ausführe,
funktioniert alles.
Schreibe ich aber dieses Makro als Funktion oder rufe es von einer
eigenen Funktion auf, passiert überhaupt nichts. Die Zielwertsuche wird
nicht durchgeführt, aber das Makro wird abgearbeitet (kann man ja durch
ein MsgBox "Hallo" am Ende des Makros sehen).
Hat jemand eine Idee, wie ich das Problem lösen kann?
--
Rolf Kamieth
Uni Leipzig, WiFa, FRZ
Marschnerstr. 31
04109 Leipzig
Robert Gelbmann
Rolf Kamieth <kam...@wifa.uni-leipzig.de> schrieb in im Newsbeitrag:
38230A86...@wifa.uni-leipzig.de...
> Da die finanzmathematische Funktion Renditedis falsch rechnet (kein
> Zinseszins bei Anlage über ein Jahr), möchte die Zielwertsuche zur
> Lösung des Problems benutzen.
> Zunächst habe ich mir ein entsprechendes Makro aufgezeichnet:
> Sub Makro1()
> Range("J14").Select
> Range("J14").GoalSeek Goal:=0, ChangingCell:=Range("I14")
> End Sub
>
> Wenn ich jetzt über Extra/Makro/Makros... das Makro ausführe,
> funktioniert alles.
> Schreibe ich aber dieses Makro als Funktion oder rufe es von einer
> eigenen Funktion auf, passiert überhaupt nichts. Die Zielwertsuche wird
> nicht durchgeführt, aber das Makro wird abgearbeitet (kann man ja durch
> ein MsgBox "Hallo" am Ende des Makros sehen).
Ich vermute mal, Du versuchst die GoalSeek-Methode via benutzerdefinierter
Tabellenfunktion in Deiner Arbeitsmappe zu nutzen. Dies ist aber zum
Scheitern verurteilt, da die Zielwertsuche ja den Wert einer anderen Zelle
verändert und dies für benutzerdefinierte Tabellenfunktionen leider nicht
erlaubt ist. Daher wird die entsprechende Codezeile (und nur diese!)
ignoriert ...
Abhilfe könnte aber (ab XL97; früher war ähnliches mit OnEntry möglich) das
Change-Ereignis des entsprechenden Worksheet-Objektes (Tabellenblattes)
schaffen:
- Zum Visual Basic-Editor wechseln (Extras / Makro),
- im Projekt-Explorer auf das entsprechende Objekt (in diesem Beispiel:
Tabelle1) doppelklicken um das dazugehörige Codefenster zu öffnen und
folgenden Code einfügen:
---snip---
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo xFehlerbehandlung
If Not (Application.Intersect(Target, Tabelle1.Range("A2:A5")) Is Nothing)
Then
Tabelle3.Range("B1").GoalSeek Goal:=0,
ChangingCell:=Tabelle1.Range("A8")
End If
Exit Sub
xFehlerbehandlung:
MsgBox "Fehlerhafte Eingabe!"
End Sub
---snip---
Beschreibung:
Das Change-Ereignis wird immer ausgeführt, wenn der Wert oder eine Formel in
irgendeiner Zelle geändert wird. Um dies auf den Eingabebereich (hier A2:A5)
zu beschränken, verwende ich If gemeinsam mit "Intersect", welches die
Schnittmenge des tatsächlich geänderten Bereiches (Target) und des
Eingabebereiches liefert. Wenn der (für uns relevante) Eingabebereich nicht
betroffen war, wird Nothing zurückgegeben und das VBA-Programm wird beendet.
Andernfalls wird die Zielwertsuche ausgelöst, wobei das im Endeffekt
konstante (!) Ergebnis in A8 präsentiert wird, während die Formel, die Null
erreichen soll, in B1 abgelegt ist. Die Fehlerbehandlungsroutine soll eine
halbwegs vernünftige Meldung gewährleisten, wenn mal ein Text statt einer
Zahl oder sonst irgendein ungültiger Wert eingegeben wurde.
In der Hoffnung geholfen zu haben, verbleibe ich mit einem leisen
Servus aus Wien,
-Robert Gelbmann-
---
Microsoft MVP für Excel
<rgel...@roehrenbacher.at>
ERC - Zeitschrift für Excel, Rechnungswesen und Controlling
Rolf Kamieth
vielen Dank für deinen Hinweis, werde ihn ausprobieren.
Unabhängig davon war ich auch noch bei Microsoft fündig geworden:
http://support.microsoft.com/support/kb/articles/Q124/0/96.asp
Die dort angegebene Prozedur funktioniert fast so, wie ich es will.
Sub Test()
Application.ExecuteExcel4Macro "GOAL.SEEK(""r1c1"",15,""r1c2"")"
End Sub
Ich kann sie sowohl über Extra/Makro/Makros... als auch via benutzerdefinierter
Tabellenfunktion aufrufen. Allerdings kann ich dabei keine Variablen übergeben
aber die kann ich ja auch global festlegen (oder in einer Zelle
zwischenspeichern o. ä.).
Viele Grüße aus Leipzig,
Rolf Kamieth
Robert Gelbmann schrieb:
> Hallo Rolf!
--
rkrkrkrkrkrkrkrkrkrkrkrkrkrkrkrkrkrkrkr
Rolf Kamieth
Uni Leipzig, WiFa, FRZ
Marschnerstr. 31
04109 Leipzig
Tel.: (0341) 97-33735
e-Mail: kam...@wifa.uni-leipzig.de
krkrkrkrkrkrkrkrkrkrkrkrkrkrkrkrkrkrkrk