Tabellefunktion (Matrixfunktion!) in VBA selbst erstellen
Torsten Munkelt
ich moechte eine Tabellenfunktion mittels VBA selbst
erstellen. Es gelingt mir, eine Funktion zu erstellen und
anzuwenden, die eine Matrix als Parameter uebernimmt
und das Ergebnis der Berechnung ueber der Matrix in
eine Zelle schreibt. Hier ein Beispiel, das die Elemente
einer Matrix addiert:
Public Function MSUM(Matrix As Range) As Double
Dim i, j As Integer
Dim Sum As Double
Sum = 0
For i = 1 To Matrix.Rows.Count
For j = 1 To Matrix.Columns.Count
Sum = Sum + Matrix.Cells(i, j).Value
Next j
Next i
MSUM = Sum
End Function
Wenn ich dann in eine Zelle =MSUM(ZS(-2):Z(1)S(-1))
schreibe, wird korrekt ueber der Matrix summiert und das
Ergebnis der Summation in die besagte Zelle geschrieben.
Ich moechte nun nicht nur eine 'normale' Tabellenfunktion
mittels VBA erstellen, sondern eine Matrixfunktion,
wie z. B. HÄUFIGKEIT, deren Ergebnis wieder eine
Matrix ist, die in einen Zellbereich geschrieben wird.
Ich wollte z. B. alle Elemente einer Matrix einzeln mit einem
einzelnen Wert multiplizieren und die Ergebnisse in eine
andere Matrix gleicher Groesse auf dem Tabellenblatt
zurueckschreiben Das folgende klappt nicht:
Public Function MMALSKALAR(Matrix As Range, Skalar As Double) As Range
Dim i, j As Integer
For i = 1 To Matrix.Rows.Count
For j = 1 To Matrix.Columns.Count
MMALSKALAR.Cells(i, j).Value = MMALSKALAR.Cells(i, j).Value * Skalar
Next j
Next i
End Function
Der Aufruf =MMALSKALAR(ZS(-4):Z(1)S(-3);2)
ueber vier Zellen resultiert in einem #WERT! in
allen vier Zellen. Ich habe einen Bereich der gleichen
Groesse wie die Ausgangsmatrix markiert, der sich
nicht mit der Ausgangsmatrix ueberschneidet.
Und ich habe Control+Shift+Enter bei der Eingabe
gedrueckt.
Was mache ich falsch? Wie muss ich richtig vorgehen,
um eine Matrixfunktion in VBA zu erstellen?
Vielen Dank fuer Eure Antworten in voraus.
Schoene Gruesse - Torsten
Frank Kabel
folgendes funktioniert
Public Function MMALSKALAR(Matrix As Range, Skalar As Double) As
Variant
Dim ret As Variant
Dim i, j As Integer
ret = Matrix.Value
For i = 1 To Matrix.Rows.count
For j = 1 To Matrix.Columns.count
ret(i, j) = Matrix.Cells(i, j).Value * Skalar
Next j
Next i
MMALSKALAR = ret
End Function
----
Du kannst die Zeile
ret(i, j) = Matrix.Cells(i, j).Value * Skalar
ersetzen durch
ret(i, j) = ret(i,j) * Skalar
--
Regards
Frank Kabel
Frankfurt, Germany
Torsten Munkelt
vielen Dank, es funktioniert ausgezeichnet.
> Public Function MMALSKALAR(Matrix As Range, Skalar As Double) As
> Variant
> Dim ret As Variant
> Dim i, j As Integer
> ret = Matrix.Value
Auf die Idee, dass so (siehe oben) eine Kopie
der Matrix erzeugt wird, waere ich nie gekommen.
> For i = 1 To Matrix.Rows.count
> For j = 1 To Matrix.Columns.count
> ret(i, j) = ret(i,j) * Skalar
> Next j
> Next i
> MMALSKALAR = ret
> End Function
Eine Frage habe ich trotzdem noch:
Wenn ich eine Ergebnismatrix erzeugen
moechte, deren Ausmass nicht mit einer
der eingehende Matrizen uebereinstimmt,
wie erzeuge ich sie von Grund auf neu?
Schoene Gruesse - Torsten
Frank Kabel
[snip]
> Eine Frage habe ich trotzdem noch:
> Wenn ich eine Ergebnismatrix erzeugen
> moechte, deren Ausmass nicht mit einer
> der eingehende Matrizen uebereinstimmt,
> wie erzeuge ich sie von Grund auf neu?
>
> Schoene Gruesse - Torsten
Hi Torsten
die Zuweisung führt dazu, dass die Rückgabevariable richtig
dimensioniert wird. D.h. wenn Du eine andere Ergebnismatrix haben
willst, solltest du diese mittels ReDim am anfang (basiered auf Deinen
Parametern) richtig dimensionieren
z.B.:
Redim var_return (10,1)
Frank
Torsten Munkelt
langsam begeistert mich diese Sache.
> die Zuweisung führt dazu, dass die Rückgabevariable richtig
> dimensioniert wird.
Ja, aber laut Deines Vorschlags zum Ersetzen
> Du kannst die Zeile
> ret(i, j) = Matrix.Cells(i, j).Value * Skalar
> ersetzen durch
> ret(i, j) = ret(i,j) * Skalar
werden auch die Werte uebernommen.
Habe zu Testzwecken schnell eine eigene
Matrizenmultiplikation gestrickt:
Public Function MYMMULT(LinkeMatrix, _
ObereMatrix As Range) As Variant
Dim VarRet As Variant
Dim i, j, k As Integer
ReDim VarRet(1 To LinkeMatrix.Rows.Count, _
1 To ObereMatrix.Columns.Count)
For i = 1 To LinkeMatrix.Rows.Count
For j = 1 To ObereMatrix.Columns.Count
VarRet(i, j) = 0
For k = 1 To LinkeMatrix.Columns.Count
VarRet(i, j) = VarRet(i, j) + _
LinkeMatrix.Cells(i, k).Value _
* ObereMatrix.Cells(k, j).Value
Next k
Next j
Next i
MYMMULT = VarRet
End Function
Funktioniert prima. Leider ergeben sich noch
weitere Fragen.
1) Wie reagiere ich am besten auf den Fehler,
wenn die Spaltenanzahl der linken und die
Zeilenanzahl der oberen Matrix nicht
uebereinstimmen? Ich wuerde dann gern einen
Fehlerwert in die Zellen des Ergebnisbereiches
schreiben, weil naemlich etwas errechnet wird,
das Bloedsinn ist, man es aber nicht gleich
erkennt.
2) Wie bekomme ich heraus, dass der Anwender
der Funktion den Ergebnisbereich nicht korrekt
ausgewaehlt hat (Ergebnismatrix zu klein)?
Ich meine, Excel bringt, wenn der Bereich zu gross
ist, #NV in den ueberstehenden Zellen, aber wenn
er zu klein ist, schneidet es einfach ab, und auf
diesen Fehler wuerde ich gern hinweisen.
Vielleicht noch etwas zum Hintergrund.
Ich nutze Excel fuer Proberechnungen. Ich
muss endliche n-dimensionale Funktionen
miteinander verknispeln, das heisst, ich moechte
sie hinsichtlich einer oder mehrerer Dimensionen
strecken und stauchen, sie miteinander
multiplizieren, addieren usw. Dazu klopfe
ich die Funktionen flach und habe sie als
Spaltenvektoren in Excel. Und ueber die
Dimensionsangaben, die vor den Elementen
der Vektoren stehen, kann ich sie verarbeiten.
Bisher habe ich alles manuell zusammengestoppelt,
aber die Sache mit den selbstgeschriebenen
Matrizenfunktionen wuerde alles sehr vereinfachen
- besonders bei nachtraeglichen Aenderungen.
Schoene Gruesse - Torsten
Torsten Munkelt
langsam begeistert mich diese Sache.
> die Zuweisung führt dazu, dass die Rückgabevariable richtig
> dimensioniert wird.
Ja, aber laut Deines Vorschlags zum Ersetzen
> Du kannst die Zeile
> ret(i, j) = Matrix.Cells(i, j).Value * Skalar
> ersetzen durch
> ret(i, j) = ret(i,j) * Skalar
werden auch die Werte uebernommen.
Frank Kabel
>> die Zuweisung führt dazu, dass die Rückgabevariable richtig
>> dimensioniert wird.
>
> Ja, aber laut Deines Vorschlags zum Ersetzen
Wende das hier nur als kleinen Trick an (da sowieso jeder Inhalt neu
errechnet wird) und ich so nicht die Größe Deiner Eingabemaske
bestimmen muss :-)
[snip]
> 1) Wie reagiere ich am besten auf den Fehler,
> wenn die Spaltenanzahl der linken und die
> Zeilenanzahl der oberen Matrix nicht
> uebereinstimmen? Ich wuerde dann gern einen
> Fehlerwert in die Zellen des Ergebnisbereiches
> schreiben, weil naemlich etwas errechnet wird,
> das Bloedsinn ist, man es aber nicht gleich
> erkennt.
Versuche folgende codezeilen am Anfang:
If LinkeMatrix.columns.count <>ObereMatrix.rows.count then
MYMMULT = CVErr(xlErrValue)
Exit Function
end if
> 2) Wie bekomme ich heraus, dass der Anwender
> der Funktion den Ergebnisbereich nicht korrekt
> ausgewaehlt hat (Ergebnismatrix zu klein)?
> Ich meine, Excel bringt, wenn der Bereich zu gross
> ist, #NV in den ueberstehenden Zellen, aber wenn
> er zu klein ist, schneidet es einfach ab, und auf
> diesen Fehler wuerde ich gern hinweisen.
Dazu kannst du die
Application.Caller
Eigenschaft abfragen und einfach die Größe des Ausgabebereichs testen.
Fehler dann wie unter 1) einfach zurückgeben
> Vielleicht noch etwas zum Hintergrund.
> Ich nutze Excel fuer Proberechnungen. Ich
> muss endliche n-dimensionale Funktionen
> miteinander verknispeln, das heisst, ich moechte
> sie hinsichtlich einer oder mehrerer Dimensionen
> strecken und stauchen, sie miteinander
> multiplizieren, addieren usw. Dazu klopfe
> ich die Funktionen flach und habe sie als
> Spaltenvektoren in Excel. Und ueber die
> Dimensionsangaben, die vor den Elementen
> der Vektoren stehen, kann ich sie verarbeiten.
> Bisher habe ich alles manuell zusammengestoppelt,
> aber die Sache mit den selbstgeschriebenen
> Matrizenfunktionen wuerde alles sehr vereinfachen
> - besonders bei nachtraeglichen Aenderungen.
Frage: Für vieles sollten eigentlich Excels Standardfunktionen
ausreichen, aber wahrscheinlich wird es bei komplexeren Aufgaben dann
doch einfacher, es selber zu machen.
Ein Hinweis noch: Auf folgender Seite findest Du ein paar ganz gute
Beispiele für selbstgeschriebene Matrixfunktionen (Arrays.xls), die Dir
vielleicht beim selber machen helfen
http://home.pacbell.net/beban
Gruß
Frank
Torsten Munkelt
Entschuldigung fuer den doppelten Beitrag
vorhin, irgendwie spinnt unser News-Server. :-/
> > 1) Wie reagiere ich am besten auf den Fehler,
[...]
> Versuche folgende codezeilen am Anfang:
[...]
> Dazu kannst du die
> Application.Caller
> Eigenschaft abfragen und einfach die Größe des Ausgabebereichs testen.
Vielen Dank. Hat alles wunderbar geklappt. :-)
Eine letzte Frage habe ich noch: Wie kann ich in
VBA ein Objekt, das von einer Eigenschaft des
Typs Variant gehalten wird, einer Variable des
Typs des Objekts zuweisen?
Dim Rng As Range
' ... Tests, dass es der richtige Typ ist
Rng = Application.Caller ' geht nicht
Rng = Range(Application.Caller) ' geht auch nicht
Liegt es vielleicht daran, dass Caller schreibgeschuetzt
ist und ich ihn, wenn er von einer anderen Variable
gehalten wuerde, veraendern koennte, was ich nicht
soll/darf?
> Frage: Für vieles sollten eigentlich Excels Standardfunktionen
> ausreichen, aber wahrscheinlich wird es bei komplexeren Aufgaben dann
> doch einfacher, es selber zu machen.
Ich kenne mich einigermassen mit Excel aus,
und die Standardfunktionen schaffen es an dieser
Stelle auch in beliebiger Kombination leider nicht.
> http://home.pacbell.net/beban
Sehr schoen. Vor allem kann man da ein
wenig abgucken. ;-)
Vielen Dank noch einmal fuer alles.
Schoene Gruesse - Torsten
Frank Kabel
> Hallo, Frank,
>
> Entschuldigung fuer den doppelten Beitrag
> vorhin, irgendwie spinnt unser News-Server. :-/
Kein Problem :-)
[snip]
> Eine letzte Frage habe ich noch: Wie kann ich in
> VBA ein Objekt, das von einer Eigenschaft des
> Typs Variant gehalten wird, einer Variable des
> Typs des Objekts zuweisen?
>
> Dim Rng As Range
> ' ... Tests, dass es der richtige Typ ist
> Rng = Application.Caller ' geht nicht
> Rng = Range(Application.Caller) ' geht auch nicht
>
> Liegt es vielleicht daran, dass Caller schreibgeschuetzt
> ist und ich ihn, wenn er von einer anderen Variable
> gehalten wuerde, veraendern koennte, was ich nicht
> soll/darf?
z.B.: so
If TypeOf Application.Caller Is Range Then
set rng = Range(Application.Caller.Address)
end if
>> Frage: Für vieles sollten eigentlich Excels Standardfunktionen
>> ausreichen, aber wahrscheinlich wird es bei komplexeren Aufgaben
dann
>> doch einfacher, es selber zu machen.
>
> Ich kenne mich einigermassen mit Excel aus,
> und die Standardfunktionen schaffen es an dieser
> Stelle auch in beliebiger Kombination leider nicht.
Na dann kann man Dir ja nur viel Spaß beim selber programmieren
wünschen :-)
Gruß
Frank
Torsten Munkelt
> > Eine letzte Frage habe ich noch: Wie kann ich in
> > VBA ein Objekt, das von einer Eigenschaft des
> > Typs Variant gehalten wird, einer Variable des
> > Typs des Objekts zuweisen?
> >
> If TypeOf Application.Caller Is Range Then
> set rng = Range(Application.Caller.Address)
> end if
Funktioniert hervorragend.