Rückgabewert einer Funktion formatieren
Carsten Seifart
kann mir jemand sagen, ob bzw. wie ich den Rückgabewert einer Funktion
formatieren kann?
Die Funktion berechnet einen Wert der nach einer Bedingung rot oder blau
dargestellt werden soll.
(zB. Gerade Zahl = rot, ungerade Zahl = blau)
Die Funktion soll als "Benutzerdefiniert" vom Benutzer beliebig
positioniert werden können.
carsten
(mit Excel 97 und NT)
Melanie Breden
"Carsten Seifart" schrieb:
> kann mir jemand sagen, ob bzw. wie ich den Rückgabewert einer Funktion
> formatieren kann?
>
> Die Funktion berechnet einen Wert der nach einer Bedingung rot oder blau
> dargestellt werden soll.
> (zB. Gerade Zahl = rot, ungerade Zahl = blau)
>
> Die Funktion soll als "Benutzerdefiniert" vom Benutzer beliebig
> positioniert werden können.
eine Funktion kann keine Formatierungen transportieren.
Da ist leider Nichts zu machen.
Entweder verwendest du die bedingte Formatierung,
oder eine Prozedur in welche die Formatierungen ausführt werden.
--
Mit freundlichen Grüßen
Melanie Breden
_____________________
[Microsoft MVP für Excel]
Carsten Seifart
danke für die schnelle Auskunft. Zwei kleine Fragen hätte ich da jetzt noch
>eine Funktion kann keine Formatierungen transportiere.
>Da ist leider Nichts zu machen.
>
Schade, ich hatte es befürchtet.
>Entweder verwendest du die bedingte Formatierung,
>oder eine Prozedur in welche die Formatierungen ausführt werden.
>
Was ist eine bedingte Formatierung?
Wie erhalte ich die Zelle in der die Funktion steht, wenn ich die
Schrift mit einer Prozedur formatieren will?
carsten
Alexander Wolff
Newsbeitrag: 3F5D9E64...@ingenieurbuero-kgs.de...
Frage 1: (Format/Bedingte Formatierung): weist einer dementsprechend
formatierten Zelle dann ein (anderes) Format zu, wenn eine Bedingung
(Formel oder der Wert der Zelle) WAHR ist. Sonst bleibt es beim
voreingestellten Format.
Frage 2: Gib Deiner Zelle einen Namen: Mit VBA formatierst Du dann den
Bereich, der den Namen trägt. Ersetze mit diesem Namen ein aufgezeichnetes
Makro. Aus diesem (Beispiel)
Sub Makro2()
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End Sub
wird so dann
Sub GelbenHintergundSetzen()
With Range("DeinName").Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End Sub
--
Gruß Alexander
- Bitte keine eMails -
Carsten Seifart
danke erstmal für deine Antwort. Leider hilft es mir nicht weiter-ich
habe mein Vorhaben jetzt auch aufgegeben.
>
>Frage 1: (Format/Bedingte Formatierung): weist einer dementsprechend
>formatierten Zelle dann ein (anderes) Format zu, wenn eine Bedingung
>(Formel oder der Wert der Zelle) WAHR ist. Sonst bleibt es beim
>voreingestellten Format.
>
Geht nicht weil sich die Bedingung nur aus der (komplexen) Berechnung in
der Funktion ergibt.
>
>
>Frage 2: Gib Deiner Zelle einen Namen: Mit VBA formatierst Du dann den
>Bereich, der den Namen trägt. Ersetze mit diesem Namen ein aufgezeichnetes
>Makro. Aus diesem (Beispiel)
>
Geht nicht weil der user nur die Funktion erhält und diese dann in
beliebige Zellen einordnen will. Ich müßte also in der Funktion die
Zelle lokalisieren können in der die Funktion steht.
Trotzdem besten Dank für eure Bemühungen
carsten
Melanie Breden
"Carsten Seifart" schrieb:
> danke erstmal für deine Antwort. Leider hilft es mir nicht weiter-ich
> habe mein Vorhaben jetzt auch aufgegeben.
>
> >Frage 1: (Format/Bedingte Formatierung): weist einer dementsprechend
> >formatierten Zelle dann ein (anderes) Format zu, wenn eine Bedingung
> >(Formel oder der Wert der Zelle) WAHR ist. Sonst bleibt es beim
> >voreingestellten Format.
> >
> Geht nicht weil sich die Bedingung nur aus der (komplexen) Berechnung in
> der Funktion ergibt.
du kannst die bedingte Formatierung doch genau auf das Ergebnis der Funktion anpassen,
welches nach der Berechnung in der Zelle steht.
> >Frage 2: Gib Deiner Zelle einen Namen: Mit VBA formatierst Du dann den
> >Bereich, der den Namen trägt. Ersetze mit diesem Namen ein aufgezeichnetes
> >Makro. Aus diesem (Beispiel)
>
> Geht nicht weil der user nur die Funktion erhält und diese dann in
> beliebige Zellen einordnen will. Ich müßte also in der Funktion die
> Zelle lokalisieren können in der die Funktion steht.
die Adresse der aufrufenden Zelle erhälst du mit:
Application.Caller.Address
Aber was willst du damit machen?
Du kannst aus einer Funktion auch keine andere Prozedur aufrufen!
Du könntest die Adresse global an Excel übergeben, falls dir das weiterhilft.
Versuche es noch einmal mit der bedingten Formatierung.
Bei mehr als drei Bedingungen kannst du auch das Selection_Change-Ereignis einsetzen
und so bei jedem Zellwechsel die Werte im Bereich prüfen und danach die Zellen formatieren.
Carsten Seifart
Danke für eure Hinweise, aber zur Zeit ist meine Zeit zu knapp hier
weiter zu experimentieren.
Um mein Problem nochmal genau zu beschreiben folgendes:
Ich habe eine benutzerdefinierte Funktion geschrieben, die ermittelt mit
welcher Last ein Balken belastet werden darf (Baustatik). Dabei gibt es
2 Kriterien:
1. Festigkeit - d.h. ist die Last größer bricht der Balken durch
2. Steifigkeit - d.h. ist die Last größer biegt sich der Balken zu stark
durch
Das Ergebnis der Funktion ist natürlich der kleinere Wert aus 1 und 2.
Schön wäre es nun, wenn es möglich wäre den Ausgabetext rot zu machen
wenn Kriterium1 zum Ergebnis geführt hat und blau wenn Kriterium 2 greift.
Mann kann das natürlich mit 2 Funktionen erledigen, diese in zwei
Spalten bringen und die Auswertung in eine dritte Spalte - aber das ist
nicht gewünscht. Es soll eine Funktion sein, die ein Ergebnis liefert
und in den Tabellen beliebig angeordnet werden kann.
Der folgende Versuch ist gescheitert:
Public Function cstst()
Application.Cells(Application.Caller.Address).Font.Color = vbRed
cstst = "Hallo"
End Function
Ich hatte auf ein rotes Hallo gehofft.
carsten
Ahmed Martens
"Carsten Seifart" <c_Punkt...@ingenieurbuero-kgs.de> schrieb im
Newsbeitrag news:3F5F1456...@ingenieurbuero-kgs.de...
> Aufgeben wollt ihr wohl nicht gelten lassen? ;-)
>
Ich habe nicht aufgegeben. :-)
>[...]
> Schön wäre es nun, wenn es möglich wäre den Ausgabetext rot zu machen
> wenn Kriterium1 zum Ergebnis geführt hat und blau wenn Kriterium 2
greift.
>
Gibt es und zwar wie folgt:
Option Explicit 'In einem separeten Modul 2
Funktionen
Public vZelle As String
Public vFarbe as Long
Public Function cstst(wert As Currency)
cstst = (wert ) ' Deine Berechnung
vZelle = Application.Caller.Address 'Die Adresse der Zelle
mit der Funktion
vFarbe = vbRot 'je
nach Bedingung
End Function
Public Function farbe(wert As String, Farbe as Long) '2
Funktion zum einfärben
Application.Range(wert).Font.Color = vFarbe
End Function
In dem Tabellenblatt wird diese Funktion beim Ereignis Berechnung
aufgerufen.
Private Sub Worksheet_Calculate()
Dim retval
retval = farbe(vZelle,vFarbe )
End Sub
Und schon wird die aktuelle Zelle immer entsprechend Deiner Vorgabe
eingefärbt. GGf. ist noch ein zusätzlicher Paramter mit zu geben.
> Mann kann das natürlich mit 2 Funktionen erledigen, diese in zwei
> Spalten bringen und die Auswertung in eine dritte Spalte - aber das
ist
> nicht gewünscht. Es soll eine Funktion sein, die ein Ergebnis liefert
> und in den Tabellen beliebig angeordnet werden kann.
>
Ist nicht nötig.
Gruß Ahmed
Thomas Ramel
Die Idee mit den globalen Variablen und derm .Calculate ist echt gut!
Ich habe die Funktion etwas angepasst, um eine Bedingung reinzubringen:
Public Function MyFunction(Zahl As Currency)
MyFunction = Zahl/2 ' Deine Berechnung
vZelle = Application.Caller.Address
If Zahl / 2 = Int(Zahl / 2) Then 'Wenn ganzzahlig dann Blau
vFarbe = vbBlue
Else 'ansonsten Rot
vFarbe = vbRed
End If
End Function
Public Function Farbe2(wert As String, Farbe As Long)
Application.Range(wert).Font.Color = vFarbe
End Function
Private Sub Worksheet_Calculate()
Dim retval
retval = farbe(vZelle,vFarbe )
End Sub
>
> Und schon wird die aktuelle Zelle immer entsprechend Deiner Vorgabe
> eingefärbt. GGf. ist noch ein zusätzlicher Paramter mit zu geben.
Wenn nun ein ganzer Bereich auf den sich die Funktion bezieht geändert
wird, kann es sein, dass die Einfärbung ins Stolpern kommt:
A1:A10 --> fortlaufende Zahlen 1 - 10
B1 =MyFunction(A1) --> ziehen bis B10
===> B1:B9 werden Rot, B10 wird Blau <===
Wenn B2:B10 einzeln mit F2/Return eingegeben werden, klappt es.
In der Praxis wird es, sobald die Funktin des öftern eingesetzt wird, zu
Ungereimtheiten mit der Farbe kommen. Daher führt der 'sichere' Weg IMHO
über eine VBA-Sub, die explizit ausgeführt wird. Wie man diese triggert ist
noch offen, ich könnte mir aber ein Change-Ereignis beschränkt aufeinen
Eingabe-Bereich vorstellen, das die Parameter 'zusammensucht' und an die
Sub übergibt, welche dann das Resultat der Berechnung liefert und die Zelle
auch gleich einfärbt.
--
Mit freundlichen Grüssen
Thomas Ramel
- MVP für Microsoft-Excel -
[Win 2000Pro SP-4 / xl2000 SP-3]
Carsten Seifart
das funktioniert super!
Da hab' ich ja einiges gelernt - Vielen Dank an alle!
carsten
Ahmed Martens
"Thomas Ramel" <t.r...@mvps.org> schrieb im Newsbeitrag
news:3F5F380E...@mvps.org...
> Grüezi Ahmed
>
> Die Idee mit den globalen Variablen und derm .Calculate ist echt gut!
>
> [...]
manchmal hat ein Hobby-Programmierer eben auch mal Glück :-)).
Vor allen Dingen wenn er nur über ein beschränktes Wissen wie ich verfüge,
dann muss man eben mal ungewöhnliche Wege gehen.
Ahmed
Melanie Breden
"Carsten Seifart" schrieb:
>"Ahmed Martens" schrieb:
man kann die Schriftfarbe der Zelle der aufrufenden Funktion auch ohne
eine zusätzliche Funktion über globale Variablen einfärben.
In der Funktion wird die Bedingung über eine Select Case-Anweisung angepasst.
Diese kann man beliebig ändern und erweitern.
' Standardmodul
Public rngCell As Range
Public lngColor As Long
Function ColorValue(dbl As Double) As Double
' Funktionskopf anpassen
' hier der Code deiner Funktion
ColorValue = dbl / 2
Select Case ColorValue
Case Is > 100
lngColor = vbBlue
Case Is > 50
lngColor = vbRed
' weitere Case-Blöcke
Case Else
lngColor = xlNo
End Select
Set rngCell = Application.Caller
End Function
' Klassenmodul der Tabelle
Private Sub Worksheet_Calculate()
If Not rngCell Is Nothing Then
rngCell.Font.Color = lngColor
Set rngCell = Nothing
lngColor = 0
End If
End Sub
Ahmed Martens
"Melanie Breden" <Melanie...@mvps.org> schrieb im Newsbeitrag
news:bjp5fc$ls6f4$1...@ID-200118.news.uni-berlin.de...
[..Full quoute entsorgt..]
ich habe deshalb eine 2. Funktion genommen, weil er ja die Berechnung
ggf. in mehreren Tabellenblättern machen möchte. Aus diesem Grunde habe
ich die Einfärbung abgekapselt. So wird im Calculate-Ereignis immer nur
die Funktion aufgerufen und bei Erweiterungen der Einfärbefunktion
braucht man dann nur einmal eine Änderung vornehmen und nicht in jedem
Tabellenblatt.
Gruß Ahmed
Carsten Seifart
Thomas Ramel wrote:
>
> Wenn nun ein ganzer Bereich auf den sich die Funktion bezieht geändert
> wird, kann es sein, dass die Einfärbung ins Stolpern kommt:
>
Das habe ich jetzt auch festgestellt. Daher ist die Funktion mit
vorsicht zu genießen!
Melanie Breden wrote:
>
>man kann die Schriftfarbe der Zelle der aufrufenden Funktion auch ohne
>eine zusätzliche Funktion über globale Variablen einfärben.
>
>
Funktioniert auch gut - hat aber auch das Problem das das Einfärben
nicht zuverlässig klappt, wenn die Formel in mehreren Zellen steht. Wie
Thomas das schon bei Ahmeds Version festgestellt hat.
Nochmals vielen Dank euch Dreien für die hilfreichen und fachkundigen Tipps.
carsten
Thomas Ramel
Carsten Seifart schrieb:
>
> Thomas Ramel wrote:
>>
>> Wenn nun ein ganzer Bereich auf den sich die Funktion bezieht geändert
>> wird, kann es sein, dass die Einfärbung ins Stolpern kommt:
>>
> Das habe ich jetzt auch festgestellt. Daher ist die Funktion mit
> vorsicht zu genießen!
>
> Nochmals vielen Dank euch Dreien für die hilfreichen und fachkundigen
> Tipps.
Bitteschön, ist gern geschehen - auch wenn mein Beitrag eher 'kritisch' war ;-)
Könntest Du mit einer reinen VBA-Lösung leben?
Eine Einschränkung ware z.B. dass die Anordnung der Ausgangs-Daten in einer
bestimmten Weise erfolgen müsste, oder dass die 'Funktion' nicht wahlweise
auf dem Tabellenblatt positioniert werden könnte.
Eine Aktualisierung könnte im zweiten Fall auch automatisiert werden.
Ahmed Martens
"Carsten Seifart" <c_Punkt...@ingenieurbuero-kgs.de> schrieb im
Newsbeitrag news:3F602F20...@ingenieurbuero-kgs.de...
> Hallo zusammen,
>
>
> Thomas Ramel wrote:
>
> >
> > Wenn nun ein ganzer Bereich auf den sich die Funktion bezieht
geändert
> > wird, kann es sein, dass die Einfärbung ins Stolpern kommt:
> >
> Das habe ich jetzt auch festgestellt. Daher ist die Funktion mit
> vorsicht zu genießen!
Ich glaube ich habe endlich die Lösung.
Option Explicit
Public vZelle() As String 'Dimensionieren
Public vFarbe() As Long
Public Function MyFunction(Zahl As Currency)
On Error GoTo Fehler 'Nur beim ersten Aufruf
MyFunction = Zahl / 2 ' Deine Berechnung
vZelle(UBound(vZelle())) = Application.Caller.Address
If Zahl / 2 = Int(Zahl / 2) Then 'Wenn ganzzahlig dann Blau
vFarbe(UBound(vFarbe())) = vbBlue
Else 'ansonsten Rot
vFarbe(UBound(vFarbe())) = vbRed
End If
ReDim Preserve vZelle(UBound(vZelle()) + 1)
ReDim Preserve vFarbe(UBound(vFarbe()) + 1)
Exit Function
Fehler:
If Err.Number = 9 Then
ReDim vZelle(0)
vZelle(UBound(vZelle())) = Application.Caller.Address
ReDim vFarbe(0)
Resume Next
End If
End Function
Public Function Farbe2(wert As String, Farbe As Long)
Application.Range(wert).Font.Color = Farbe
End Function
Private Sub Worksheet_Calculate()
Dim retval, i As Long
For i = 0 To (UBound(vZelle()) - 1)
retval = Farbe2(vZelle(i), vFarbe(i))
Next
ReDim vZelle(0)
ReDim vFarbe(0)
End Sub
Ich bekomme es einfach nicht hin, dass die vZelle usw. automatisch mit
Dimension 0 sind, deshalb mußte ich den Fehler abfangen.
Bei der ersten Berechnung wird automatisch die Dimensionierung
vorgenommen.
Ich habe die Funktionen ausprobiert und bei erfolgt jetzt die richtige
Farbdarstellung.
Gruß Ahmed
Ahmed Martens
Private Sub Worksheet_Calculate()
On Error goto Abruch
Dim retval, i As Long
For i = 0 To (UBound(vZelle()) - 1)
retval = Farbe2(vZelle(i), vFarbe(i))
Next
ReDim vZelle(0)
ReDim vFarbe(0)
Abbruch:
End Sub
Da das Ereignis Calculate ja bei jeder Eingabe erfolgt muß unbedingt
noch die Abbruch-Bedingung mit rein, da es ansonsten zu einem Fehler
kommt.
Gruß Ahmed
Carsten Seifart
>
>Ich glaube ich habe endlich die Lösung.
>
>
Danke für die Mühe, ich probiere es bei nächster Gelegenheit.
carsten