Excel Datum aus KW & Feldfarbe verändern durch Zahleneingabe

Hallo,

ich hab 2 Fragen:

1. Ich habe den Wochentag und die KW, kann ich mir daraus in Excel das datum des Tages anzeigen lassen ?

2. In einem Feld steht ein Text. Durch Zahleneingabe in dem Feld daneben möchte ich die Textfarbe(zur not auch Hintergrundfarbe) des Feldes mit Text verändern ?


Jemand eine Idee ? ich wär euch sehr dankbar!!!!!

Grüße

d4chillin

 

Hi,

das 2. Problem kannst du mit Bedingter Formatierung lösen. Angenommen A1 soll in Abhängigkeit von B1 gefärbt werden: wähle unter Bedingung1 "Formel ist" aus und gib als Formel ein =B1=10 (wenn bei einem Wert von 10 gefärbt werden soll) und wähle dann unter Format das gewünschte Format aus.

 

@Hascheff,

Frage 1 überlasse ich dir gern. Bin nicht so der Formelspezialist.

 

Hi Hascheff,

vom Prinzip her kann man sicher auch spezifische Parameter einbinden, denn eine benutzerdefinierte Funktion ist ja auch VBA-Code. Aber mir ist nicht so ganz klar, welche Parameter du meinst. Deine Datei kann ich leider nicht herunterladen, bzw. es wird Dateifehler beim Öffnen/Extrahieren angezeigt.

 

Hi Hascheff,

ach so, diese Parameter meinst du, klar. Auf welche Weise sollen sie denn übergeben werden - direkt als Konstanten in der Funktion festschreiben, sodass sie im Code ausgetauscht werden müssen oder sollen sie aus verschiedenen Zellen im Tabellenblatt entnommen werden?

Die Datei konnte ich nicht lesen, weil ich nur das in WIN integrierte Zip-Programm besitze und offensichtlich hat es Probleme mit diesem Zip-Format.

 

Hi Hascheff,

ich bin mal jetzt davon ausgegangen, dass die Ausgangswerte in den selben Zellen stehen wie in deinem Beispiel

Code:

Private Function kwdatum()
    kwdatum = DateValue("1.1." & Range("B4")) + 7 * (Range("B2") - Range("B7")) - Weekday(DateValue("1.1." & Range("B4")), Range("B6")) + Range("B3")
End Function

Das Problem dabei ist, dass die Zelle im Anschluss erst noch als Datum formatiert werden muss, genau wie bei deiner Formel. Man könnte mit der Formel ja noch das Format anpassen

=TEXT(DATWERT("1.1."&B4)+7*(B2-B7)-WOCHENTAG(DATWERT("1.1."&B4);B6)+B3;"TT.MM.JJJJ")

was mit VBA dann so aussehen würde

Code:

Private Function kwdatum()
    kwdatum = Application.Text(DateValue("1.1." & Range("B4")) + 7 * (Range("B2") - Range("B7")) - Weekday(DateValue("1.1." & Range("B4")), Range("B6")) + Range("B3"), "dd.mm.yyyy")
End Function

Das Problem ist aber, dass ich mir nicht sicher bin, wie sich das auf irgendwelche weiteren Berechnungen auswirkt, da die Zelle auf diese Weise ja als Text formatiert ist.

 

Hi Hascheff,

Konstanten kann man nicht in einer Funktion übergeben, soweit ich weiß, aber man kann sie außerhalb der Prozedur deklarieren:

Code:

Option Explicit
Const Korrektur As Integer = 1

Function kwdatum(KW, WT, Jahr, WTTyp As Variant)
    Application.Volatile
    If Not IsNumeric(Jahr) Then Jahr = Year(Now())
    kwdatum = DateValue("1.1." & Jahr) + 7 * (KW - Korrektur) - Weekday(DateValue("1.1." & Jahr), WTTyp) + WT
End Function

Ich habe noch Application.Volatile ergänzt, sodass eine Neuberechnung automatisch erfolgt.

Damit die Zelle als Datum formatiert wird, hatte ich schon Application.Caller vewendet, funktioniert jedoch auch nicht, weil eine Schleife zur Neuberechnung aller Zellen, die die Funktion enthalten, ausgelöst würde - und das unterbindet Excel. Aber es funktionert, wenn man zusätzlich noch das Worksheet_Change Ereignis verwendet:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim raZelle As Range
    If Target.Count > 1 Then
        For Each raZelle In Selection
            If InStr(raZelle.Formula, "=kwdatum(") > 0 Then raZelle.NumberFormat = "dd/mm/yyyy;   "
        Next raZelle
    Else
        If InStr(Target.Formula, "=kwdatum(") > 0 Then Target.NumberFormat = "dd/mm/yyyy;  @"
    End If
End Sub

 

Da muss ich dir widersprechen, denn der Vorteil der Deklaration als Konstante zu Beginn jeglichen Codes macht es wesentlich einfacher, die Konstante bei Erfordernis zu ändern und dadurch wird der Code wesentlich übersichtlicher, gleichgütltig wie lang oder kurz er ist. Außerdem kann eine Kontante sowieso im Code nicht verändert werden, weshalb eine Deklaration innerhalb des Codes wenig sinnvoll ist.

Ob sich jemand die Mühe macht, den Wochentag als Wort in die Funktion einzugeben statt als Zahl, soll dahingestellt bleiben, da ja die Gschmäcker bekanntlich verschieden sind . Aber auf jeden Fall sollte bei Eingabe als Zahl geprüft werden, ob sie innerhalb des gültigen Bereichs liegt, also zwischen 1 und 7.

Code:

Function WOCHENTAG2ZAHL(WT_Wort As Variant) As Integer
    If IsNumeric(WT_Wort) And WT_Wort > 0 And WT_Wort < 8 Then
        WOCHENTAG2ZAHL = Weekday(WT_Wort)
    Else
        Select Case WT_Wort
            Case "So", "Sonntag":    WOCHENTAG2ZAHL = 1
            Case "Mo", "Montag":     WOCHENTAG2ZAHL = 2
            Case "Di", "Dienstag":   WOCHENTAG2ZAHL = 3
            Case "Mi", "Mittwoch":   WOCHENTAG2ZAHL = 4
            Case "Do", "Donnerstag": WOCHENTAG2ZAHL = 5
            Case "Fr", "Freitag":    WOCHENTAG2ZAHL = 6
            Case "Sa", "Samstag":    WOCHENTAG2ZAHL = 7
        End Select
    End If
End Function

Da weiß ich jetzt leider nicht, was genau du damit meinst.

 

Wenn man "Case Select" keine gültige Auswahlmöglichkeit anbietet, soll es manchmal erratisch reagieren.
Je nach Wetter? Es wird hier offensichtlich die letzte Case-Zeile gewählt...
Bei mir (mit Excel 2003 und schönem Wetter) ergibt eine leere Zelle wie gewünscht gar keinen Wert.

Versuche es einmal so >

Case "Sa", "Samstag": WOCHENTAG2ZAHL = 7
Case Else: WOCHENTAG2ZAHL = "" >oder> Exit Function

End Select


Gruss Urs

 

Hi,

ich verstehe immer noch nicht, was ihr mit "leere Zelle" meint - Funktion ohne Wertangabe in Klammern bzw. nicht im Code definierten Wert oder noch etwas ganz anderes?

Bei einer leeren Funktion wird doch logischerweise #WERT ausgegeben und wenn der Wert in Klammern nicht in der Funktion definiert ist eine Null - auch ohne Case Else. Ich verwende Excel2002.

 

Normalerweise schon, aber das Case Select verfolgt seine eigenen, unerfindlichen Gedankengänge...
...wie offensichtlich bei Hascheff...
...und #WERT könnte unbedarfte Leute schon erschrecken...

Ich hatte zuerst bei "" eine freundlichere Fehlermeldung drin, aber die Zelle ist wohl zu klein...
Case Else: Exit Function
müsste eigentlichl das Problem umgehen, oder nicht?

Warten auf Hascheff...

Gruss Urs

 

Hi Urs,

bei mir bewirkt diese Codezeile überhaupt nichts, da die Funktion gar nicht ausgeführt wird, wenn kein Wert in die Klammern geschrieben wird. Folglich wird automatisch #WERT ausgegeben.

 

@Hascheff

Ich hatte leider nicht die ganze Function getestet, nur das Select Case, weil ich wusste, dass das manchmal Aerger macht...
Aber jetzt ist alles klar.

Ich würde >

- die "unvernünftigen" Zahlen abfangen, er rechnet auch mit -1 oder 9 >>> sinnlos, aber das Resultat sieht aus wie echt...

- Die Fehler explizit anzeigen, es muss ja nicht kryptisch sein, wie bei MS
> Gross-/Kleinschreibung muss beachtet werden
> "Donnerstag" und ""Domerstag" sehen gleich aus, und ein Leerzeichen hinten dran sieht man nicht... ein Tipp ist willkommen...

Code:

Function WOCHENTAG2ZAHL(WT_Wort As Variant) As Variant
Application.Volatile

    If IsNumeric(WT_Wort) Then
        If WT_Wort = "" Then
            WOCHENTAG2ZAHL = ""
                'er würde auch mit grösseren Zahlen sinnlos rechnen...
        ElseIf WT_Wort < 1 Or WT_Wort > 7 Then
            WOCHENTAG2ZAHL = "Zahl 1-7 !"
                'warum nicht einfach >>>   = WT_Wort ??
        Else: WOCHENTAG2ZAHL = Weekday(WT_Wort)
        End If
    Else
        Select Case WT_Wort
            Case "So", "Sonntag":    WOCHENTAG2ZAHL = 1
            Case "Mo", "Montag":     WOCHENTAG2ZAHL = 2
            Case "Di", "Dienstag":   WOCHENTAG2ZAHL = 3
            Case "Mi", "Mittwoch":   WOCHENTAG2ZAHL = 4
            Case "Do", "Donnerstag": WOCHENTAG2ZAHL = 5
            Case "Fr", "Freitag":    WOCHENTAG2ZAHL = 6
            Case "Sa", "Samstag":    WOCHENTAG2ZAHL = 7
            Case Else: WOCHENTAG2ZAHL = "Text ??"
        End Select
    End If
End Function

Benutzerdefinierte Liste im Code benutzen? Weiss ich nicht, aber ich denke nicht, dass das vorgesehen ist....
Der Code wäre dann ja an diesen PC gebunden, also doch besser hart codieren.

Gruss Urs

 

@Hascheff,

aha, du nimmst also den Wert aus einer Zelle, das war nicht so granz klar. Man kann ja schließlich auch den Wert direkt in die Funktion schreiben, was ich gemacht hatte, und da trat dieses Problem nicht auf.

Wozu genau willst du denn die benutzerdefinierten Listen verwenden?


@Urs,

das hatte ich in meinen Code schon integriert.

Weshalb so kompliziert? Das sollte es doch auch tun

Code:

Function WOCHENTAG2ZAHL(WT_Wort As Variant) As Variant
    Application.Volatile
    If IsEmpty(WT_Wort) Or IsNumeric(WT_Wort) Then
        If WT_Wort > 0 And WT_Wort < 8 Then
            WOCHENTAG2ZAHL = Weekday(WT_Wort)
        Else
            WOCHENTAG2ZAHL = ""
        End If
    Else
        Select Case WT_Wort
            Case "So", "Sonntag":    WOCHENTAG2ZAHL = 1
            Case "Mo", "Montag":     WOCHENTAG2ZAHL = 2
            Case "Di", "Dienstag":   WOCHENTAG2ZAHL = 3
            Case "Mi", "Mittwoch":   WOCHENTAG2ZAHL = 4
            Case "Do", "Donnerstag": WOCHENTAG2ZAHL = 5
            Case "Fr", "Freitag":    WOCHENTAG2ZAHL = 6
            Case "Sa", "Samstag":    WOCHENTAG2ZAHL = 7
            Case Else: WOCHENTAG2ZAHL = ""
        End Select
    End If
End Function