For...next aus Excel heraus

Computer: Software & Programmierung Visual Basic
#1

Liebe/-r Experte/-in,

ich habe eine komplizierte Excel-Formel, von der ich wissen will, bei welcher Wertkombination von drei Variablen die Formel den kleinsten Wert zurückliefert. Programmieren kann ich ein bissel (habe ich vor Ewigkeiten mal gelernt), aber ich habe keine Ahnung von Visual Basic, d.h. ich kenne a) nicht die Syntax und b) nicht den Weg, wie ich aus einem ExcelSheet heraus eine Prozedur aufrufen kann.
Programmierlogisch ist das, was ich brauchte etwa folgendes:

Minimumfindeprogramm
Ergebnis = 99999 (Initialisierung mit hohem Wert)
For I = 1 To 1000
For J = 1 To 1000
For K = 1 To 1000
If Ergebnis > (Formelergebnis) (also neues Minimum gefunden)
Then
Ergebnis = Formelergebnis
I-Wert = I
J-Wert = J
K-Wert = K
Endif
Next K
Next J
Next I
Ausgabe Ergebnis
I-Wert
J-Wert
K-Wert
Ende Minimumfindeprogramm

Wer kann mir helfen?

#2

Hallo Elke!

Eigentlich ganz einfach: in Excel mit Alt+F11 oder Menü „Extras“ -> „Makro“ -> „VisualBasic-Editor…“ den Makroeditor aufrufen.
Links findest Du Deine Datei, den Code kannst Du entweder in den Code eines bestimmten Blattes oder in ein neues Modul einfügen, das ist in diesem Fall egal.
Also entweder im linken „Verzeichnisbaum“ auf das aktuelle Tabellenblatt doppelklicken oder dort einen Rechtsklick machen und „Einfügen“ -> „Modul“ klicken und das neu erstellte Modul doppelklicken.

Jetzt hast Du im rechten Fenster eine leere Arbeitsfläche für die Makroprogrammierung. Dein Makro müsste dann in etwa so aussehen (Copy&amp:stuck_out_tongue_winking_eye:aste in das rechte Fenster):

Sub minimumfindeprogramm() 'Anfang des Makros
Ergebnis = 99999 '(Initialisierung mit hohem Wert)
For I = 1 To 1000
For J = 1 To 1000
For K = 1 To 1000
If Ergebnis > (I * J + K) Then '(also neues Minimum gefunden)
Ergebnis = (I * J + K)
IWert = I
JWert = J
KWert = K
End If
Next K
Next J
Next I
ergebnisausgabe = MsgBox("Minimales Ergebnis = " & Ergebnis & " bei I = " & IWert & "; J = " & JWert & "; K = " & KWert)
End Sub 'Ende des Makros

Das Makro musst Du natürlich entsprechend anpassen - die richtige Formel muss zwei mal(!) eingegeben werden - ich denke, Du siehst, wo…
VBA-Editor kann geschlossen werden.

Aufrufen kannst Du das Makro dann entweder über das Menü „Extras“ -> „Makro“ -> „Makros“
oder z.B. eine Schaltfläche verwenden („Ansicht“ -> „Symbolleisten“ -> „Formular“, dann eine Schaltfläche erstellen und dieser das Makro zuweisen.

Das ist jetzt natürlich die Kurzform des Ganzen. Falls noch Fragen sind, helfe ich gerne weiter…

Schöne Grüße aus Nürnberg!

Florian

#3

eine Prozedur kann man nicht aufrufen, dafuer eine Funktion:

public Ergebnis as double ’ oder Long, usw.
public function getMini(param1, param2, usw) as double
call Minimumfindeprogram ’ ggf. Parameter
getMini = Ergebnis
end function

#4

Hallo Elke,
das ist eine interessante aber so nicht zu Excel passende Frage, es sei denn man möchte von Excel nur die VBA Programmiersprache nutzen. Die Eleganz von Excel liegt meines Erachtens aber in den Tabellen-Funktionen und nicht im VBA, wie ich auch in meiner Profilbeschreibung zu verdeutlichen versucht habe. (wenn man wirklich programmieren möchte, sollte man meines Erachtens eine richtige Programmiersprache wie zum Beispiel Pascal wählen)

Aber mit ein wenig Anpassung lässt sich das Problem auch ganz ohne VBA und viel anschaulicher mit den Tabellenfunktionen von Excel lösen:
die Auflösung der drei Variablen I,J und K müsste man (wegen der von Excel vorgegebenen maximalen Tabellengröße)
durch Viererschritte etwas verringern. D.h. die Formel wird nur für die 250 Werte I,J,K =1,5,9,… 1001 ausgewertet.
Das weitere Vorgehen beschreibt eines (von vielen möglichen) Verfahren um die dreidimensionale Funktion von I,J und K in einer zweidimensionalen Excel Tabelle unterzubringen:

in die Spalte A der Tabelle trägt man den K-Wert. ein:
in den Feldern A1-A250 steht also K gleich 1,
in den Feldern A251-A500 steht K gleich 5, bis
zu den Feldern A62251-A62500 mit K=1001

In die Spalte B jeder dieser 250 Teiltabellen trägt man die Werte für J ein also z.B B1…B250 wird (genauso wie B251 …B500)
mit der Zahlenreihe 1,5,9,…1001 gefüllt
Anschliessend wird eine neue erste Zeile eingefügt, d.h. alle bisherigen Einträge rutschen in der Tabelle um eine Position nach unten.
Diese erste Tabellenzeile erhält nun zwischen C1 bis IR1 die Werte für I indem
C1 , D1,…IR1 mit der gleichen Zahlenreihe 1,5,9,…1001 gefüllt wird.

Jetzt muss nur noch die eigentliche Formel(I,J,K) in die Tabellenfelder eingetragen werden. Zum einfachen Kopieren der Formel in alle Felder muss man innerhalb der Formel die Möglichkeiten der absoluten und relativen Adressierung ($-Prefix) geeignet nutzen.

Ein freies Feld, zum Beispiel A1, kann mit der Formel MIN(C2:IR62502) gefüllt werden, die den minimalen Funktionswert liefert.

Eine Suche nach dem gefundenen Wert liefert die zugehörige Position und damit die I,J und K Werte des Minimums.

Weitere Tabellenblätter können zur Verfeinerung der Suche in der Nähe des gefundenen Minimums verwendet werden.

Viel Erfolg
Werner

#5

Super! Vielen herzlichen Dank, das sind genau die Infos, die ich gebraucht habe. *freu*
Elke

#6

cool, das klingt nach der passenden Ergänzung, um das Progrämmle automatisch aufzurufen, das werde ich ausprobieren. Auch dir recht herzlichen Dank!
Elke

#7

Danke, so ähnlich habe ich es bisher, reicht aber leider einfach nicht aus. Mit Pascal hätte ich es gemacht, wenn meine Programmierkenntnisse nicht 20 Jahre alt wären. Ich habe keine Ahnung, wo man die Programmierumgebung heutzutage auf einem PC herkriegt, ob es das als Freeware gibt etc. etc. Excel ist mir aber eh lieber und das Makro von Florian ist so lauffähig, da muss jetzt nur noch die Formel rein. (Und Excel braucht nur ca. 5 Minuten für die Milliarde Berechnungen aus Florians Beispiel, wenn ich da an die Rechenzeiten von früher denke…)

Elke

#8

gern geschehen

cool, das klingt nach der passenden Ergänzung, um das
Progrämmle automatisch aufzurufen, das werde ich ausprobieren.
Auch dir recht herzlichen Dank!
Elke

#9

Hallo,

ja genau, so könnte man das auch in VBA machen. Mit ALT+F11 rufst Du den VBA-Editor auf. Mit ALT+e+m erstellst Du ein neues Modul. Dort kopierst Du folgenden Zeilen rein:

Option Explicit

Public Sub MinimumFindeProgramm()

Dim Ergebnis As Double
Dim i As Long
Dim j As Long
Dim k As Long
Dim iWert As Long
Dim jWert As Long
Dim kWert As Long

Ergebnis = 99999

For i = 1 To 1000
Debug.Print i
For j = 1 To 1000
For k = 1 To 1000

If Ergebnis > i * j / k Then
Ergebnis = i * j / k
iWert = i
jWert = j
kWert = k
End If

Next k
Next j
Next i

MsgBox "Ergebnis: " & Ergebnis & vbCr & _
"iWert: " & iWert & vbCr & _
"jWert: " & jWert & vbCr & _
"kWert: " & kWert, , „Berechnung beendet“

End Sub

Dann nur noch mit F5 ausführen. (Geht auch von Excel aus: über das Menü, Extras-Makro-Makros…)

LG Gerd

#10

FreePascal.org bietet an Turbo Pascal und Delphi angelehnt Freeware. Für Pascal gibt es auch Prozeduren, die unter der Annahme halbwegs gutmütiger Funktionen sehr schnell deren Extemwerte ermitteln.

#11

Liebe Elke,

das geht über die Rangfolge. Kann ich mal eine Tabelle habe in der die Abfrage erfolgen soll ?

LG

Alexander

#12

Hallo Elke,
eine eigene Funktion kannst du wie folgt aufrufen:
In VBA Definition der Funktion:
Public Function MyFunction (Parameterliste) as
…
…
hier mein Programm
…
…
End Function

und dann in einer Zelle: = MyFunction(Parameter)

Mehr kann ich Dir mit den wenigen Angaben nicht helfen.
Das Problem selbst sieht nach einer Anwendung für den Excel Solver aus.

Dein Funktion würde ich wie folgt umsetzen:
Public Sub FindeMinimum(IWert as Long, JWert as Long, KWert as Long)
Dim I as Long
Dim J as Long
Dim K as Long

I = 1
J = 1
K = 1
FindeMinimum = Formelergebnis(I,J,K) 'Formelergebnis eigene Funktion aufrufen
IWert=1
JWert=1
KWert=1

For I = 2 to 1000
For J = 2 to 1000
For K = 2 to 1000
If Formelergebnis(I,J,K)

#13

Hallo Elke,
ich habe mit VBA nicht all zu viel Erfahrung und kann da also leider nicht weiter helfen.
kein VBA steht auch bei mir im Profil.

Ich würde die Frage direkt im Forum stellen, da gibt es dafür wirklich gute Leute.
http://www.wer-weiss-was.de/app/service/board_navi?T…

Gruß Holger