Excel Funktionen in VBA nutzen - ich verzweifle

Bild

Betrifft: Excel Funktionen in VBA nutzen - ich verzweifle
von: Stefan
Geschrieben am: 27.08.2015 18:15:15

Hallo!
Bin neu hier und .. Anfänger mit VBA. Aber dank eurer Hilfe, komme ich schon sehr weit.
Nun zu meinem Problem. Ich musste, abhängig von Arbeitsbeginn und Arbeitsende
(z.B. 01:00 - 5:00) herausfinden, wieviel Stunden davon in einem Zeitbereich gearbeitet wurden (z.B. 00:00 - 4:00). In diesem Beispiel wäre die Lösung 3 Std.
Ich habe auch eine Lösung im Internet gefunden die funktioniert
Hier die Lösung die ich in Excel einsetze
=RUNDEN((MAX(;1-MAX(A32;F31)+MAX(;G31-A32)+MIN(B32;G31)+(B32>F31)*(B32-F31)-(A32 < B32)*(1-(F31-G31)) -(F31 < G31)*(B32+(A32 > B32)-A32))*(A32<>B32)*(F31<>G31))*24;2)
A32 = Beginn_Arbeit = 01:00
B32 = Ende_Arbeit = 05:00
F31 = Bereich_Von = 00:00
G31 = Bereich_Bis = 04:00
Da ich aber mit dem zurückgegebenen Wert noch andere Dinge berechnen muss, wird die Formelzeile in Excel immer länger. Also habe ich Hilfsspalten eingesetzt.
Lange Rede kurzer Sinn, ich hatte keine Lust mehr auf Hilfsspalten, und habe versucht, die obige Formel als Funktion in VBA umzusetzen.
Die Funktion heisst:
StundenEnthalten(Beginn_Arbeit, Ende_Arbeit, Bereich_Von, Bereich_Bis As Double) As Double
Damit ich nicht alles neu schreiben muss, dachte ich mir, ich verwende die gleiche Funktion, innerhalb meiner Funktion, die Excel auch benutzt.
Hier meine Funktion:
'Anfang----------------------------------------------------

Public Function StundenEnthalten(Beginn_Arbeit, Ende_Arbeit, Bereich_Von, Bereich_Bis As Double) _
 As Double
Dim dEnthaltene_Stunden As Double
With WorksheetFunction
        
Enthaltene_Stunden = .Round((.Max(0, 1 - .Max(Beginn_Arbeit, Bereich_Von) + .Max(0, Bereich_Bis  _
- _
Beginn_Arbeit) + .Min(Ende_Arbeit, Bereich_Bis) + (Ende_Arbeit > Bereich_Von) * _
(Ende_Arbeit - Bereich_Von) - (Beginn_Arbeit < Ende_Arbeit) * (1 - (Bereich_Von - Bereich_Bis))  _
- _
(Bereich_Von < Bereich_Bis) * (Ende_Arbeit + (Beginn_Arbeit > Ende_Arbeit) - Beginn_Arbeit)) *  _
_
(Beginn_Arbeit <> Ende_Arbeit) * (Bereich_Von <> Bereich_Bis)) * 24, 2)
   
StundenEnthalten = dEnthaltene_Stunden
End With
End Function
'Ende----------------------------------------------------
Aber ... egal was ich tue. Meine Funktion liefert nicht denselben Wert zurück
wie die Formel, die ich in Excel benutze.
Ich habe den Variablentyp auf Variant oder Range geändert (Übergabewerte), ich habe direkt den Zellwert ausgelesen (.Value), ich habe ohne Übergabewerte gearbeitet, und die gleichen Dezimalzahlen innerhalb der Funktion benutzt, die mir auch Excel in der Tabelle anzeigt (4 konstante Zahlenwerte innerhalb der Funktion). Hat alles nichts gebracht.
Ich weiss aber nicht wieso.
Was mache ich falsch ?
Grüße aus Berlin
Stefan

Bild

Betrifft: AW: Excel Funktionen in VBA nutzen - ich verzweifle
von: fcs
Geschrieben am: 28.08.2015 07:50:05
Hallo Stefan,
VBA kann die sich ergebenden WAHR/FALSCH-Werte in der Formel nicht korrekt als 1 oder 0 bei der Multiplikation verarbeiten.
Hier muss man mit der VBA-Funktion IIf nachhelfen.
Gruß
Franz

Public Function StundenEnthalten(Beginn_Arbeit, Ende_Arbeit, Bereich_Von, Bereich_Bis) _
        As Double
    With WorksheetFunction
        StundenEnthalten = .Round( _
            (.Max( _
                0, _
                1 - .Max(Beginn_Arbeit, Bereich_Von) _
                    + .Max(0, Bereich_Bis - Beginn_Arbeit) _
                    + .Min(Ende_Arbeit, Bereich_Bis) _
                    + IIf(Ende_Arbeit > Bereich_Von, 1, 0) * (Ende_Arbeit - Bereich_Von) _
                    - IIf(Beginn_Arbeit < Ende_Arbeit, 1, 0) * (1 - (Bereich_Von - Bereich_Bis)) _
 _
                    - IIf(Bereich_Von < Bereich_Bis, 1, 0) * _
                        (Ende_Arbeit + IIf(Beginn_Arbeit > Ende_Arbeit, 1, 0) - Beginn_Arbeit))  _
_
              * IIf(Beginn_Arbeit <> Ende_Arbeit, 1, 0) _
              * IIf(Bereich_Von <> Bereich_Bis, 1, 0)) * 24, 2)
    End With
End Function


Bild

Betrifft: AW: Excel Funktionen in VBA nutzen - ich verzweifle
von: Stefan
Geschrieben am: 28.08.2015 08:24:34
Hallo!
Du bist der Beste !!! Wer kann das ahnen, das Excel damit nicht umgehen kann.
Nur noch eine Frage zur Funktion. Du hast im Funktioneskopf die 4 übergebenen Werte nicht dimensioniert. Muss man das nie machen, wenn man sich Werte aus der Tabelle übergeben lässt? Oder nur in diesem Fall?
Hat das Vor-/Nachteile ?
Vielen Dank nochmal !!
VG
Stefan

Bild

Betrifft: AW: Excel Funktionen in VBA nutzen - ich verzweifle
von: Daniel
Geschrieben am: 28.08.2015 08:56:32
Hi
VBA kann schon richtig mit WAHR und FALSCH umgehen, nur muss man beachten, dass in Berechnungen ein WAHR in Excel als 1 gewertet wird, in VBA jedoch als -1.
Gruß Daniel

Bild

Betrifft: AW: Excel Funktionen in VBA nutzen-Variablendeklar
von: fcs
Geschrieben am: 28.08.2015 11:48:31
Hallo Stefan,
wie Daniel schreibt kann VBA die boolschen Ausdrücke auswerten, aber es kommt ein anderes ergebnis raus.
Statt mit IIf zu arbeiten könnte man hier auch jewels mit -1 multiplizieren.
Deklaration der Variablen:
generell sollte man die Variablen möglichst typgenau entsprechend den Eingabewerten deklarieren.
Macht man keine Deklaration (Typ = Variant), interpretiert Excel die Daten in der Zelle/Zellbereich.
Jede Variable muss dabei einzeln deklariert werden! Es reicht nicht nur die letzte Variable zu deklarieren.
Deklarationen der Variablen sind dann wichtig, wenn man Excel bei der Interpretation der Eingabewerte keinen Spielraum lassen möchte. Auch die Fehlerbehandlung ist ggf. einfacher, wenn typgerecht deklariert wird.
Gruß
Franz

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Excel Funktionen in VBA nutzen - ich verzweifle"