Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen
Informationen und Beispiele zum Thema Label
BildScreenshot zu Label Label-Seite mit Beispielarbeitsmappe aufrufen
Informationen und Beispiele zum Thema ListBox
BildScreenshot zu ListBox ListBox-Seite mit Beispielarbeitsmappe aufrufen

VBA Code erläutern

Betrifft: VBA Code erläutern von: Peer
Geschrieben am: 05.11.2020 15:29:47

Hallo VBA Profis.
Von Nepumuk hier im Forum habe ich nachfolgenden Code erhalten, den ich gen an meine Bedü _ rfnisse anpassen möchte, komme aber mit den Spalten der ListBox-Einträge vollkommen durcheinander, weil ich den Code (noch) nicht verstehe.

Private Sub UserForm_Initialize()
    Dim lngMonth As Long, ialngIndex As Long, lngRow As Long, lngColumn As Long
    Dim avntValues() As Variant, avntTemp As Variant, vntItem As Variant
    For lngMonth = 1 To 2 'auf 12 Monate erhöhen!!!
        lngRow = 6
        With Worksheets(MonthName(Month:=lngMonth))
            Do
                If IsEmpty(.Cells(lngRow + 1, 4).Value) Then
                    lngRow = .Cells(lngRow, 4).End(xlDown).Row
                Else
                    lngRow = lngRow + 1
                End If
                If lngRow < .Rows.Count Then
                    Redim Preserve avntValues(14, ialngIndex)
                    avntValues(0, ialngIndex) = .Cells(lngRow, 2).Value
                    lngColumn = 1
                    avntTemp = .Range(.Cells(lngRow, 4), .Cells(lngRow, 15)).Value
                    For Each vntItem In avntTemp
                        Select Case lngColumn
                            Case 2, 6, 9
                                avntValues(lngColumn, ialngIndex) = Format$(vntItem, "Hh:Nn")
                                If lngColumn = 6 Then
                                    If vntItem >= TimeSerial(24, 0, 0) Then avntValues(6,  _
ialngIndex) = "24:00"
                                ElseIf lngColumn = 9 Then
                                    Select Case vntItem
                                        Case Is < TimeSerial(8, 0, 0)
                                            avntValues(10, ialngIndex) = "0,00 €"
                                        Case Is >= TimeSerial(24, 0, 0)
                                            avntValues(10, ialngIndex) = "24,00 €"
                                            avntValues(9, ialngIndex) = "24:00"
                                        Case Is > TimeSerial(8, 0, 0)
                                            avntValues(10, ialngIndex) = "12,00 €"
                                        Case Else
                                            avntValues(10, ialngIndex) = "Fehler"
                                            Debug.Print vntItem
                                    End Select
                                    lngColumn = lngColumn + 1
                                End If
                                lngColumn = lngColumn + 1
                            Case Else
                                avntValues(lngColumn, ialngIndex) = vntItem
                                lngColumn = lngColumn + 1
                        End Select
                    Next
                    avntValues(14, ialngIndex) = .Name & "|" & CStr(lngRow)
                    ialngIndex = ialngIndex + 1
                Else
                    Exit Do
                End If
            Loop
        End With
    Next
    lst_Dienstreise.Column = avntValues
End Sub
Meine Spalten werden vollkommen verwirrend dargestellt und ich komme auch mit Durchprobieren nicht wirklich zu einer vielversprechenden Lösung.

Kann mir bitte jemand helfen?

LG
Peer

Betrifft: AW: VBA Code erläutern
von: ralf_b
Geschrieben am: 05.11.2020 16:03:25

Hallo,

Hatte Nepumuk den Code denn nicht für deine Bedürfnisse geschrieben?
Wie sind denn deine Bedürfnisse?

Was genau verstehst du nicht?
Du hast noch nichtmal aus der 2 ne 12 gemacht.
For lngMonth = 1 To 2 'auf 12 Monate erhöhen!!!


Hier wird mit Arrays gespielt bis schlußendlich das Array avntValues der Columneigenschaft der Listbox zugewiesen wird.

do loop schleife geht zeilenweise durch die Monatsblätter

avntValues bekommt eine wert aus Spalte 2
avnttemp bekommt die werte aus Spalte 4 bis 15
dann werden die Elemente von avntTemp ausgewertet und je nachdem Geldbeträge in avntValues
eingetragen, entsprechend der Case Bedingungen.

möglicherweise liegts auch nur an der Formatierung der Spaltenbreiten deiner Listbox. Die sehe ich nämlich nicht.

Betrifft: AW: VBA Code erläutern
von: Peer
Geschrieben am: 05.11.2020 19:55:12

Hallo ralf_b.
Ich war die letzte Zeit krankheitsbedingt nicht mehr am PC, um mich um die Fortsetzung meines Projektes zu kümmern.

Ich habe die Monate durchaus auf 12 erhöht. Aber erst in der Original-Datei.
Ich musste mit den 2 Monaten die Datei klein halten, um sie Nepumuk zu schicken.
Es liegt eher an der Zuordnung der Spalten und Zeilen. Deshalb wollte ich einmal wissen, wie das Array "gefüllt" wird.
Leider ist inzwischen meine Datei sehr groß geworden, weshalb ich sie nicht schicken kann. Deshalb habe ich für Nepumuk eine Dummy Datei erstellt und gehofft, selbst mit ein paar Anpassungen die Original Datei zu bearbeiten.

Ich lege mal eine stark reduzierte "Original"-Datei bei.
Die UF frm_Dienstreisen enthält mein Anliegen.

https://www.herber.de/bbs/user/141343.xlsm

LG
Peer

Betrifft: AW: VBA Code erläutern
von: ralf_b
Geschrieben am: 06.11.2020 01:02:42

Naja du hättest schon sagen können, das der Code für einen anderen Bereich programmiert wurde.

damit kanste mal bissel rumspielen. Is nicht ganz so astrein aber es wird wenigstens was angezeigt.
https://www.herber.de/bbs/user/141354.zip


Betrifft: AW: VBA Code erläutern
von: ralf_b
Geschrieben am: 06.11.2020 10:08:59

Moin,

wie ich sehe war das doch einfacher als gedacht. Ich hatte den Code wohl nicht so ganz kapiert. Also vergiss was ich dir geschickt habe.

avnttemp ist dann quasi die schleifenbestimmende Größe
Wenn du jetzt noch die Kosten und die Dauer einlesen willt, dann wäre eine Möglichkeit das avnttemp array zu vergrößern mit den beiden Werten hinten dran.
dann dürfte es auch mit der Spaltenanordnung hinhauen.

ReDim Preserve avntValues(14, ialngIndex) hier sollte die 14 zu der benötigten Spaltenanzahl passend gemacht werden

hier mal eine eingedampfte variante
Do
                If IsEmpty(.Cells(lngRow + 1, 26).Value) Then
                    lngRow = .Cells(lngRow, 26).End(xlDown).Row
                Else
                    lngRow = lngRow + 1
                End If
                If lngRow < .Rows.Count Then
                    ReDim Preserve avntValues(8, ialngIndex)
            
                    lngColumn = 0
                    avntTemp = .Range(.Cells(lngRow, 26), .Cells(lngRow, 32)).Value  'Werte aus  _
Spalte 26 bis 32
                    
                    ReDim Preserve avntTemp(1 To 1, UBound(avntTemp, 2) + 1)
                    avntTemp(1, 7) = .Cells(lngRow, 22).Text  'beispiel zelle
                    avntTemp(1, 8) = .Cells(lngRow, 25).Value  'beispiel zelle
                    
                    For Each vntItem In avntTemp
                        Select Case lngColumn
                            Case 2, 5, 7
                                avntValues(lngColumn, ialngIndex) = Format$(vntItem, "Hh:Nn")
                                lngColumn = lngColumn + 1
                            Case Else
                                avntValues(lngColumn, ialngIndex) = vntItem
                                lngColumn = lngColumn + 1
                        End Select
                    Next
                    ialngIndex = ialngIndex + 1
                Else
                    Exit Do
                End If
            Loop


Betrifft: AW: VBA Code erläutern
von: Peer
Geschrieben am: 06.11.2020 19:20:51

Hallo ralf_b.

Ich habe deinen beiden Codes genommen und sage vielen Dank für deine Rückmeldung. Ich versuche die ganze Zeit, wie vorher von dir erwähnt, rumzuspielen und die Spalten für Zeit und Betrag zu formatieren.
Klappt aber noch nicht.
In der Spalte für Dauer möchte ich gern 24:00 anzeigen, wenn der Beginn 0:00 und das Ende am nächsten Tag bei 0:00 liegt, also 24 Std, die für die Berechnung der Kosten relevant sind. Genauso sollen die Kosten ab einer Dauer von mehr als 8 Std 12 € und ab 24 Std Dauer 24 € betragen.
Und wenn beim Ort des Beginns oder/und beim Ort des Endes die PLZ mit A beginnt (also das erste Zeichen) sollen bei Kosten 28 € stehen.

Mit timeserial, wie schon von Nepumuk vorgeschlagen, könnte es eine Lösung geben, aber ich komme nicht weiter, weil ich nicht weiß, wie ich sie in deinen beiden Codes (bzw einen von beiden) einfügen soll.

Kannst du mir hierzu ein paar Hilfestellungen geben?

LG
Peer

Betrifft: AW: VBA Code erläutern
von: ralf_b
Geschrieben am: 06.11.2020 20:15:02

Hallo,

naja im Grunde wäre Nepumuk hier der richtige Ansprechpartner.

Aber soweit ich dieses Konstrukt nun durchschaut habe, bestimmst du den angezeigten Wert mit den Case Unterscheidungen. Case geht hier auf die Spaltennr. der Listbox. Ich ging davon aus das die Kosten und die Dauer einfach eine Zelle in der Tabelle sind. Wenn du die extra berechnen mußt, dann gibt es dafür schon zig Beispiele. Arbeitszeitberechnungen werden ständig angefragt. Unter Berücksichtigung von diversen Ländersätzen bei den VMA/Auslöse würde ich nicht nur auf Österreich oder Deutschland abstellen. ´Man weis ja nicht was später noch kommt.

Bastel dir eine Funktion, die du mit den Notwendigen Werten fütterst und nutze einen Rückgabewert zum Füllen der Array Werte. Das macht es etwas übersichtlicher. Die Profis hier basteln gern so kryptisch wie möglich. Da versteht man aber den Code schon nach ner Woche nicht mehr, wenn was angepasst werden muß. Lieber bissel ausführlicher und gut dokumentieren.
z.b.
avntTemp(1, 7) = functionberechneDauer( datum anfang , Datum ende )
avntTemp(1, 8) = functionberechneKosten(dauer, Länderwert)

hier im code wird nur auf die drei Spalten der Listbox abfgefragt weil die Werte als Zeit formatiert angezeigt werden sollen.
Case 2, 5, 7
avntValues(lngColumn, ialngIndex) = Format$(vntItem, "Hh:Nn")

Ich halte die Ortsangaben für ungünstig in der Tabelle hinterlegt. Zu Beginn hatte ich den Ort und die Strasse und die Hausnummer separiert, da es so aussah das die in eigene Spalten untergebracht werden sollten. Aber das war schließlich ein zeitaufwendiger Trugschluß.

Die Berechnungen würde ich gar nicht in der case Konstruktion unterbringen sondern, weiter oben beim avntTemp. Darin ist der aktuellen Datensatz gespeichert.


Gruß
RB



Betrifft: AW: VBA Code erläutern
von: Peer
Geschrieben am: 06.11.2020 22:29:03

Hallo ralf_b.

Tolle Erläuterung und sehr guter Ansatz.

Ich habe mal versucht, den Code ein wenig zu erweitern.
Dim lngMonth As Long, ialngIndex As Long, lngRow As Long, lngColumn As Long, lngDauer As  _
Long
    Dim avntValues() As Variant, avntTemp As Variant, vntItem As Variant
    For lngMonth = 1 To 2 'auf 12 Monate erhöhen!!!
        lngRow = 12
        
        With Worksheets(MonthName(Month:=lngMonth))
            Do
                If IsEmpty(.Cells(lngRow + 1, 26).Value) Then
                    lngRow = .Cells(lngRow, 26).End(xlDown).Row
                Else
                    lngRow = lngRow + 1
                End If
                If lngRow < .Rows.Count Then
                    ReDim Preserve avntValues(8, ialngIndex)
            
                    lngColumn = 0
                    avntTemp = .Range(.Cells(lngRow, 26), .Cells(lngRow, 32)).Value  'Werte aus  _
Spalte 26 bis 32
                    lngDauer = DateDiff("d", .Cells(lngRow, "AA", "AD"))
                    
                    ReDim Preserve avntTemp(1 To 1, UBound(avntTemp, 2) + 1)
                    avntTemp(1, 7) = .Cells(lngRow, "AE") - .Cells(lngRow, "AB").Value '.Text  ' _
beispiel zelle (Dauer)
                    avntTemp(1, 8) = .Cells(lngRow, 25).Value  'beispiel zelle (Kosten)
                    
                    For Each vntItem In avntTemp
                        Select Case lngColumn
                            Case 2, 5, 7
                                avntValues(lngColumn, ialngIndex) = Format$(vntItem, "Hh:Nn")
                                lngColumn = lngColumn + 1
                            If lngColumn = 5 Then
                                         If lngDauer > 0 Then avntValues(5, ialngIndex) = " _
24:00"
                            ElseIf lngColumn = 7 Then
                                         Select Case vntItem
                                             Case Is < TimeSerial(8, 0, 0)
                                                 avntValues(8, ialngIndex) = "0,00 €"
                                             Case Is >= TimeSerial(24, 0, 0)
                                                 avntValues(8, ialngIndex) = "24,00 €"
                                                 avntValues(7, ialngIndex) = "24:00"
                                             Case Is > TimeSerial(8, 0, 0)
                                                 avntValues(8, ialngIndex) = "12,00 €"
                                             Case Else
                                                 avntValues(8, ialngIndex) = "Fehler"
                                                 Debug.Print vntItem
                                         End Select
                                         lngColumn = lngColumn + 1
                                     End If
                            Case Else
                                avntValues(lngColumn, ialngIndex) = vntItem
                                lngColumn = lngColumn + 1
                        End Select
                    Next
                    ialngIndex = ialngIndex + 1
                Else
                    Exit Do
                End If
            Loop
        End With
    Next
    lst_Dienstreise.Column = avntValues

Aber bei DateDiff beschwert sich der Debugger. Ist DateDiff der falsche Ansatz?

LG
Peer

Betrifft: AW: VBA Code erläutern
von: ralf_b
Geschrieben am: 06.11.2020 23:35:32

lngDauer = DateDiff("d", .Cells(lngRow, "AA"), .Cells(lngRow, "AD"))

du solltest dir die Hilfe zu datedif ansehen. es werden zwei datum werte erwartet. das wird aber mit cells(zeile, Spalte,Spalte ) nichts. cells(zeile,spalte) ist die notation.

Wer hat denn die Commandbar gebaut?

Betrifft: AW: VBA Code erläutern
von: Peer
Geschrieben am: 07.11.2020 10:47:28

Hallo ralf_b.

Ich habe die Hilfe schon durchgelesen und nahm an, dass ich mit hier richtig lag, denn beide Spalten enthalten Datumswerte. Und da dachte ich, die Differenz beider Werte ist das, was ich brauche.
Also wie soll ich dann beginnen?
Mit einer Function, wie du im Post vorher geschildert hast, oder vielleicht doch mit Deklaration innerhalb dieses Sub?

Die Commandbar wurde durch fremder Hilfe und durch mich gebaut. Warum?

Gruß
Peer

Betrifft: AW: VBA Code erläutern
von: ralf_b
Geschrieben am: 07.11.2020 12:43:31

Ich möchte nicht so zu tief in diese Berechnungen einsteigen. Ist nicht so mein Ding. Es geht da um Zeiten und Datumsangaben. Wenn es dir ausreicht nur die reine Tagesdifferenz zu ermitteln, dann wird Datedif schon reichen. du hattest die Cells-Angabe nur falsch.

Nepumuk hat in der Select Case Abfrage geprüft ob eine Zeit kleiner oder größer einer Vergleichszeit ist. Timeserial(8,0,0) = 8 Stunden

Eine Funktion dient der Auslagerung von Code zur Wiederverwendung und zum schaffen von Übersichtlichkeit. Deshalb ist dies gerade bei diesen Zeitberechnungen sinnvoll. Ich schätze es gibt für deine Bedürfnisse sogar schon fertige Lösungen.

Die Commandbar ist sehr nice. Ich hatte mir vor längerer Zeit mit Hängen und Würgen ein Addin gebaut.



Betrifft: AW: VBA Code erläutern
von: Peer
Geschrieben am: 07.11.2020 13:46:00

Hallo ralf_b.
Ich arbeite mit der Freeware Custom UI Editor und durch eine Menge Hilfe hier im Forum habe ich die CommandBar nach und nach angepasst.
Du hast ja jetzt die Beispiel Datei, kannst damit rumspielen.

Den Blödsinn mit der Zuordnung der Variable "Dauer" habe ich dann festgestellt.

Ich konnte bis jetzt keine Lösung finden, wie ich die Spalten der ListBox erweitern kann. Mit DateDiff habe ich zwar den Ganzwert erzweugt, kann ihn aber nicht zufriedenstellend einbinden.
Ich habe auch it einer Hilfspalte (AL) experimentiert, das Ergebnis ist dasselbe.

Ich beabsichtige bei Reisezeit >= 24 Std, dass in Spalte "Dauer" statt "0:00" "24:00" steht, _ damit die Kostenberechnung korrekt durchgeführt wird.
            Do
                If IsEmpty(.Cells(lngRow + 1, 26).Value) Then
                    lngRow = .Cells(lngRow, 26).End(xlDown).Row
                Else
                    lngRow = lngRow + 1
                End If
                If lngRow < .Rows.Count Then
                    ReDim Preserve avntValues(8, ialngIndex)
            
                    lngColumn = 0
                    avntTemp = .Range(.Cells(lngRow, 26), .Cells(lngRow, 32)).Value  'Werte aus  _
Spalte 26 bis 32
                    lngDauer = DateDiff("h", .Cells(lngRow, "AA"), .Cells(lngRow, "AD"))
                    
                    ReDim Preserve avntTemp(1 To 1, UBound(avntTemp, 2) + 1)
                    avntTemp(1, 7) = .Cells(lngRow, "AE") - .Cells(lngRow, "AB").Value  '(Dauer) _

                    avntTemp(1, 8) = lngDauer   '.Cells(lngRow, "AL").Value  ' (Kosten)
                    
                    For Each vntItem In avntTemp
                        Select Case lngColumn
                            Case 2, 5, 7
                                avntValues(lngColumn, ialngIndex) = Format$(vntItem, "Hh:Nn")
                                lngColumn = lngColumn + 1
                            If lngColumn = 8 Then
                                         If vntItem >= 24 Then avntValues(7, ialngIndex) = "24: _
00"
                            ElseIf lngColumn = 7 Then
                                         Select Case vntItem
                                             Case Is < TimeSerial(8, 0, 0)
                                                 avntValues(8, ialngIndex) = "0,00 €"
                                             Case Is >= TimeSerial(24, 0, 0)
                                                 avntValues(8, ialngIndex) = "24,00 €"
                                                 avntValues(7, ialngIndex) = "24:00"
                                             Case Is > TimeSerial(8, 0, 0)
                                                 avntValues(8, ialngIndex) = "12,00 €"
                                             Case Else
                                                 avntValues(8, ialngIndex) = "Fehler"
                                                 Debug.Print vntItem
                                         End Select
                                         lngColumn = lngColumn + 1
                                     End If
                            Case Else
                                avntValues(lngColumn, ialngIndex) = vntItem
                                lngColumn = lngColumn + 1
                        End Select
                    Next
                    ialngIndex = ialngIndex + 1
                Else
                    Exit Do
                End If
            Loop

Die Spaltenbreiten habe ich in den Eigenschaften der ListBox festgelegt. Aber die Kostenspalte läßt sich nicht anzeigen.

https://www.herber.de/bbs/user/141386.xlsm

Bin mir sicher, du hast eine Lösung für mich.
Vielen Dank im Voraus, dass du drüber schaust.

LG
Peer

Betrifft: AW: VBA Code erläutern
von: ralf_b
Geschrieben am: 07.11.2020 14:47:03

hier werden die Listboxspalten mit 8 festgelegt
 ReDim Preserve avntValues(8, ialngIndex)
werte für die Spalten 1-8 werden hier ins array geladen
avntTemp = .Range(.Cells(lngRow, 26), .Cells(lngRow, 32)).Value 
und hier wird das schleifenarray vergrößert und die letzten beiden Spaltenwerte hinzugefügt
ReDim Preserve avntTemp(1 To 1, UBound(avntTemp, 2) + 1)
 avntTemp(1, 7) = .Cells(lngRow, "AE") - .Cells(lngRow, "AB").Value  '(Dauer) _
 avntTemp(1, 8) = lngDauer   '.Cells(lngRow, "AL").Value  ' (Kosten)
was du schlußendlich hinter die "="zeichen schreibst ist sollte das Ergebnis der Berechnungen
sein

ABER: grundsätzlich kommt mir das Ganze etwas komisch vor. Wofür benötigt du die berechneten Listboxeinträge? Was geschieht mit denen? Wenn du die Eergebnisse zur späteren Verwendung benötigst, dann wäre es doch besser diese dann in die Tabelle zu schreiben, statt sie in einer flüchtigen Listbox anzuzeigen.

Betrifft: AW: VBA Code erläutern
von: Peer
Geschrieben am: 07.11.2020 19:54:51

Hi ralf_b.

Deine Hinweise für einige Stellen im Code sind sehr hilfreich für mich gewesen.
Danke dafür.

Zu deinem letztem Absatz.
Die "flüchtigen" Werte brauche ich eigentlich nur temporär. Dies bedeutet, "Dauer" soll nur in der Liste angezeigt werden und zur Berechnung der nachfolgende Spalte zur Darstellung der Kosten. Und die Kosten sollen in Zukunft nur im UF in einem Label den Gesamtbetrag aller Zeilen in der ListBox anzeigen.
Ich möchte die schon vorhandenen ComboBoxen der UF als Filter nutzen und dann die Kosten gefiltert berechnen lassen. Das ist bis jetzt meine Idee.
An die zusätzlichen Spalten habe ich auch schon gedacht. Diel Lösung wäre sicherlich auch die bessere und einfachste. Zum Lernen mit VBA ist diese Version auch hilfreich.

Jetzt habe ich mit deiner Hilfe einiges lösen können,
Dim lngMonth As Long, ialngIndex As Long, lngRow As Long, lngColumn As Long, lngDauer As Long
    Dim datBeginn As Date, datEnde As Date
    Dim avntValues() As Variant, avntTemp As Variant, vntItem As Variant
    For lngMonth = 1 To 2 'auf 12 Monate erhöhen!!!
        lngRow = 12
        With Worksheets(MonthName(Month:=lngMonth))
            Do
                If IsEmpty(.Cells(lngRow + 1, 26).Value) Then
                    lngRow = .Cells(lngRow, 26).End(xlDown).Row
                Else
                    lngRow = lngRow + 1
                End If
                If lngRow < .Rows.Count Then
                    ReDim Preserve avntValues(10, ialngIndex)    'Festlegung der Listenspalten ( _
hier 8)
            
                    lngColumn = 0
                    datBeginn = CDate(.Cells(lngRow, "AA")) + CDate(.Cells(lngRow, "AB"))
                    datEnde = CDate(.Cells(lngRow, "AD")) + CDate(.Cells(lngRow, "AE"))
                    
                    avntTemp = .Range(.Cells(lngRow, 26), .Cells(lngRow, 33)).Value  'Werte für  _
Spalten 1-8 aus Spalte 26 bis 32 Sheets(oder 38 für AL)
                    'lngDauer = DateDiff("h", .Cells(lngRow, "AA"), .Cells(lngRow, "AD"))
                    lngDauer = DateDiff("h", datBeginn, datEnde)
                    
                    ReDim Preserve avntTemp(1 To 1, UBound(avntTemp, 2) + 1)    'Array  _
Spaltengröße erhöhen und 2 Spalten hinzufügen
                    avntTemp(1, 7) = .Cells(lngRow, "AE") - .Cells(lngRow, "AB").Value  '(Dauer) _

                    avntTemp(1, 8) = lngDauer   '.Cells(lngRow, "AL").Value  ' (Dauer  _
dezimal)
                    
                    
                    For Each vntItem In avntTemp
                        Select Case lngColumn
                            Case 2, 5, 7
                                avntValues(lngColumn, ialngIndex) = Format$(vntItem, "Hh:Nn")
                                lngColumn = lngColumn + 1
                            If lngColumn = 8 Then
                                         If lngDauer >= 24 Then avntValues(7, ialngIndex) = " _
24:00"
                            ElseIf lngColumn = 7 Then
                                         Select Case vntItem
                                             Case Is < TimeSerial(8, 0, 0)
                                                 avntValues(10, ialngIndex) = "0,00 €"
                                             Case Is >= TimeSerial(24, 0, 0)
                                                 avntValues(10, ialngIndex) = "24,00 €"
                                                 'avntValues(7, ialngIndex) = "24:00"
                                             Case Is > TimeSerial(8, 0, 0)
                                                 avntValues(10, ialngIndex) = "12,00 €"
                                             Case Else
                                                 avntValues(10, ialngIndex) = "Fehler"
                                                 Debug.Print vntItem
                                         End Select
                                    lngColumn = lngColumn + 1
                            End If
                            Case Else
                                avntValues(lngColumn, ialngIndex) = vntItem
                                lngColumn = lngColumn + 1
                        End Select
                    Next
                    ialngIndex = ialngIndex + 1
                Else
                    Exit Do
                End If
            Loop
        End With
    Next
    lst_Dienstreise.Column = avntValues
aber wie bekomme ich die weitere Spalte mit den Kosten in die Liste als letzte Spalte? Was schreibe ich nach

avntTemp(1,9) =
?

https://www.herber.de/bbs/user/141395.xlsm

Gruß
Peer

Betrifft: AW: VBA Code erläutern
von: ralf_b
Geschrieben am: 07.11.2020 21:08:10

avntTemp(1, 7) das war für die Dauer
avntTemp(1, 8) und das für die Kosten gedacht
avntTemp(1, 9) dürfte auf einen indexfehler laufen. weil das array zu ende ist,

Betrifft: AW: VBA Code erläutern
von: Peer
Geschrieben am: 08.11.2020 12:42:29

Hallo ralf_b.
avntTemp (1,8) habe ich zur Veranschaulichung bzw Kontrolle für die Variable lngDauer genutzt. Dies Spalte würde ich jetzt für die Kostenaufstellung nehmen.
Aber wie? Wie kann ich Werte abhängig der Dauer und Pauschalen für die ListBox nutzen?

Ich nahm an, dass die For-Schleife dies mit einbezieht?
Do
    If IsEmpty(.Cells(lngRow + 1, 26).Value) Then
          lngRow = .Cells(lngRow, 26).End(xlDown).Row
    Else
          lngRow = lngRow + 1
    End If

    If lngRow < .Rows.Count Then
           'Festlegung der Listenspalten (hier 8)
           ReDim Preserve avntValues(8, ialngIndex)    
            
           lngColumn = 0
           datBeginn = CDate(.Cells(lngRow, "AA")) + CDate(.Cells(lngRow, "AB"))
           datEnde = CDate(.Cells(lngRow, "AD")) + CDate(.Cells(lngRow, "AE"))
           lngDauer = DateDiff("h", datBeginn, datEnde)
           strBeginnOrt = .Cells(lngRow, "AC").Text
           strEndeOrt = .Cells(lngRow, "AF").Text
                    
           'Werte für Spalten 1-8 aus Spalte 26 bis 32 Sheets(oder 38 für AL)
           avntTemp = .Range(.Cells(lngRow, 26), .Cells(lngRow, 32)).Value
           'Array Spaltengröße erhöhen und 2 Spalten hinzufügen
           ReDim Preserve avntTemp(1 To 1, UBound(avntTemp, 2) + 1)
           avntTemp(1, 7) = .Cells(lngRow, "AE") - .Cells(lngRow, "AB").Value  '(Dauer)
           avntTemp(1, 8) = "0,00 €"  '(Kosten)
                    
                    
          For Each vntItem In avntTemp
              Select Case lngColumn
                     Case 2, 5, 7
                         avntValues(lngColumn, ialngIndex) = Format$(vntItem, "Hh:Nn")
                         lngColumn = lngColumn + 1
                         'Wenn Spalte 8
                         If lngColumn = 8 Then
                            'Wenn Dauer > 24 dann Wert in Spalte 7 auf "24:00"
                             If lngDauer >= 24 Then avntValues(7, ialngIndex) = "24:00"
                         'sonst wenn Spalte 7
                         ElseIf lngColumn = 7 Then   'dann
                                Select Case vntItem
                                       Case Is < TimeSerial(8, 0, 0)
                                            avntValues(8, ialngIndex) = "0,00 €"
                                       Case Is >= TimeSerial(24, 0, 0)
                                            avntValues(8, ialngIndex) = "24,00 €"
                                           'avntValues(7, ialngIndex) = "24:00"
                                       Case Is > TimeSerial(8, 0, 0)
                                            avntValues(8, ialngIndex) = "12,00 €"
                                       Case Else
                                            avntValues(8, ialngIndex) = "Fehler"
                                            Debug.Print vntItem
                                End Select
                              lngColumn = lngColumn + 1
                            End If
                       Case Else
                            avntValues(lngColumn, ialngIndex) = vntItem
                            lngColumn = lngColumn + 1
                End Select
            Next
                ialngIndex = ialngIndex + 1
                Else
                    Exit Do
                End If
            Loop
Gruß
Peer

Betrifft: AW: VBA Code erläutern
von: ralf_b
Geschrieben am: 08.11.2020 13:20:31

in die Listbox gibst du fertige Werte. Im case else Zweig gibst du dem Array für die Listbox(avntVAlues) das Element deiner Zeile z.b. avntTemp(1,8) (welches in dem Moment im vntItem drin steckt) mit.
Case Else
      avntValues(lngColumn, ialngIndex) = vntItem
Somit sollte der gewünschte Wert in dem vntItem drin stehen. Und das machst du oberhalb bei avnttemp(1,8) =
hier erfolgt also bereits die Berechnung des finalen Listenwertes. Von mir aus mittels Irgend einer Function.

Sollten die zur Berechnung der Kosten notwendigen Werte noch nicht zur Verfügung stehen, dann geht das ja erst später. Und jetzt kommen die Select case Abfragen ins Spiel. Hier wird qausi "on the fly" der Wert für die Listbox manipuliert. Wenn also erst dort passende Werte zur Verfügung stehen , müßtest du dort die berechneten Werte ins AvntValues in die entsprechende Spalte einfügen.

Betrifft: AW: VBA Code erläutern
von: Peerli
Geschrieben am: 08.11.2020 16:06:58

Hallo ralf_b.
Bis jetzt konnte ich deinen Erläuterungen ganz gut folgen, aber nun verstehe ich den Post nicht wirklich.
Soll ich jetzt die Select Case Anweisung zur Bestimmung der Kosten abhängig der Dauer vor avntTemp(1,8)= schreiben und später bei vntItem nochmal durchlaufen lassen?
Es tut mir leid, wenn ich nochmal nachfragen.
Gruß Peer

Betrifft: AW: VBA Code erläutern
von: ralf_b
Geschrieben am: 08.11.2020 16:22:21

oh, sorry. das waren keine Anweisungen sonder nur Ablauferklärungen.

wenn du den Wert für Dauer und Kosten oberhalb der ganzen Select's bereits hast, dann schreibe
sie entsprechend in avnttemp(1,7) und (1,8)

sollten die letzten beiden Spaltennr(lngColumn) in den Select-Abfragen nicht weiter behandelt werden, dann werden die Werte in das Array Avntvalues im Case Else Zweig geschrieben.

vntItem ist nur die aktuelle Position innerhalb des Array avntTemp. Dieses wird sowieso einmal pro Zeile komplett durchlaufen.

Betrifft: AW: VBA Code erläutern
von: Peer
Geschrieben am: 08.11.2020 17:29:12

Ich habe gedacht, ich gib avntTemp (1,8) eine Start-Variable von "0,00€" und lasse in der For _ Schleife bei

ElseIf lngColumn = 7 Then   'sonst wenn bei Spalte 7
      Select Case vntItem
           Case Is <= TimeSerial(8, 0, 0)  'der Wert < 8
                avntValues(8, ialngIndex) = "0,00 €"
           Case Is > TimeSerial(8, 0, 0)
             If strBeginnOrt = "A" Or strEndeOrt = "A" Then
                avntValues(8, ialngIndex) = "27,00 €"
             ElseIf strBeginnOrt = "I" Or strEndeOrt = "I" Then
                avntValues(8, ialngIndex) = "27,00 €"
             Else
                avntValues(8, ialngIndex) = "14,00 €"
             End If
           Case Is >= TimeSerial(24, 0, 0)
             If strBeginnOrt = "A" Or strEndeOrt = "A" Then
                avntValues(8, ialngIndex) = "40,00 €"
             ElseIf strBeginnOrt = "I" Or strEndeOrt = "I" Then
                avntValues(8, ialngIndex) = "40,00 €"
             Else
                avntValues(8, ialngIndex) = "28,00 €"
             End If
           Case Else
                avntValues(8, ialngIndex) = "Fehler"
                Debug.Print vntItem
       End Select

die "Dauer" abfragen und den Wert in Spalte 8 dementsprechend ändern.

Wie muss es denn richtig aussehen?

Ich habe hier noch die "Länder"-Variablen eingefügt, bei der ich den ersten Buchstaben abfrage. Hier habe ich bei der PLZ den Länderbuchstaben vorangestellt. Sicher kann man es eleganter machen, aber zu Übungszwecken muss es erstmal ausreichen.

Gruß
Peer

Betrifft: AW: VBA Code erläutern
von: ralf_b
Geschrieben am: 08.11.2020 18:14:20

was passiert denn wenn die schleife dann die Spalte 8 erreicht? Ich schätze dann überschreibt der Defaultwert deine Anpassung, die du beim Durchlauf für Spalte 7 gemacht hast.

da fehlt also eine leere Case 8 Abfrage.

Betrifft: AW: VBA Code erläutern
von: Peer
Geschrieben am: 08.11.2020 18:28:26

Ich glaube, ich habe es.
Sicher bin ich aber nicht ;-)
'----------- ListBox mit Daten von allen Sheets füllen mit Hilfe von ralf_b von herber.de ------ _
---------------------------------------

    Dim lngMonth As Long, ialngIndex As Long, lngRow As Long
    Dim lngColumn As Long, lngDauer As Long
    Dim datBeginn As Date, datEnde As Date, strBeginnOrt As String
    Dim strEndeOrt As String, strReiseOrt As String
    Dim avntValues() As Variant, avntTemp As Variant, vntItem As Variant

    For lngMonth = 1 To 2 'auf 12 Monate erhöhen!!!
        lngRow = 12
        With Worksheets(MonthName(Month:=lngMonth))
            Do
                If IsEmpty(.Cells(lngRow + 1, 26).Value) Then
                    lngRow = .Cells(lngRow, 26).End(xlDown).Row
                Else
                    lngRow = lngRow + 1
                End If
                If lngRow < .Rows.Count Then
                    'Festlegung der Listenspalten (hier 8)
                    ReDim Preserve avntValues(8, ialngIndex)    
            
                    lngColumn = 0
                    datBeginn = CDate(.Cells(lngRow, "AA")) + CDate(.Cells(lngRow, "AB"))
                    datEnde = CDate(.Cells(lngRow, "AD")) + CDate(.Cells(lngRow, "AE"))
                    lngDauer = DateDiff("h", datBeginn, datEnde)
                    strBeginnOrt = Left(.Cells(lngRow, "AC"), 1)
                    strEndeOrt = Left(.Cells(lngRow, "AF"), 1)
                    
                    
                    'Werte für Spalten 1-8 aus Spalte 26 bis 32 Sheets(oder 38 für AL)
                    avntTemp = .Range(.Cells(lngRow, 26), .Cells(lngRow, 32)).Value

                    'Array Spaltengröße erhöhen und 2 Spalten hinzufügen
                    ReDim Preserve avntTemp(1 To 1, UBound(avntTemp, 2) + 1)
                    avntTemp(1, 7) = .Cells(lngRow, "AE") - .Cells(lngRow, "AB").Value '(Dauer)
                    Select Case lngDauer
                        Case Is <= 8  'der Wert <= 8
                            avntTemp(1, 8) = "00,00 €"
                        Case Is > 8
                           If strBeginnOrt = "A" Or strEndeOrt = "A" Then
                               avntTemp(1, 8) = "27,00 €"
                           ElseIf strBeginnOrt = "I" Or strEndeOrt = "I" Then
                               avntTemp(1, 8) = "27,00 €"
                           Else
                               avntTemp(1, 8) = "14,00 €"
                           End If
                        Case Is >= 24
                           If strBeginnOrt = "A" Or strEndeOrt = "A" Then
                               avntTemp(1, 8) = "40,00 €"
                           ElseIf strBeginnOrt = "I" Or strEndeOrt = "I" Then
                               avntTemp(1, 8) = "40,00 €"
                           Else
                               avntTemp(1, 8) = "28,00 €"
                           End If
                        Case Else
                            avntTemp(1, 8) = "Fehler"
                            Debug.Print vntItem
                    End Select
                      
                    For Each vntItem In avntTemp
                        Select Case lngColumn
                            Case 2, 5, 7
                                avntValues(lngColumn, ialngIndex) = Format$(vntItem, "Hh:Nn")
                                lngColumn = lngColumn + 1
                            'Wenn Spalte 8
                            If lngColumn = 8 Then
                                'Wenn lngDauer > 24 dann Wert in Spalte 7 auf "24:00"
                                 If lngDauer >= 24 Then avntValues(7, ialngIndex) = "24:00"
                            End If
                            Case Else
                                avntValues(lngColumn, ialngIndex) = vntItem
                                lngColumn = lngColumn + 1
                        End Select
                    Next
                    ialngIndex = ialngIndex + 1
                Else
                    Exit Do
                End If
            Loop
        End With
    Next
    lst_Dienstreise.Column = avntValues
'--------- Ende Füllen ListBox ----------------------------------------------------------------
Gruß
Peer

Betrifft: AW: VBA Code erläutern
von: ralf_b
Geschrieben am: 08.11.2020 20:32:24

hast du die zwei Spalten in den Listboxeigenschaften auch vergrößert?
das case 8 ist Quatsch, weil du den Wert ja oben schon ermittelt hast. Die if-Abfrage innerhalb von case 2,5,7 wird nie zutreffen, weil du dort bei longcolumn = 8 nicht hinkommst.

IEne Daas 8 habe ich deshalb als Beispiel mal eingebaut und auskommentiert. Aber da du dort nur auf >24 prüfst past es nicht wenn dort Preis von 14 Euro vorhanden ist.

Select Case lngColumn
    Case 2, 5, 7
        avntValues(lngColumn, ialngIndex) = Format$(vntItem, "Hh:Nn")
        lngColumn = lngColumn + 1
    'Wenn Spalte 8
    If lngColumn = 8 Then
        'Wenn lngDauer > 24 dann Wert in Spalte 7 auf "24:00"
         If lngDauer >= 24 Then avntValues(7, ialngIndex) = "24:00"
    End If
    Case Else
        avntValues(lngColumn, ialngIndex) = vntItem
        lngColumn = lngColumn + 1
End Select

Select Case lngColumn
    Case 2, 5, 7
        avntValues(lngColumn, ialngIndex) = Format$(vntItem, "Hh:Nn")
        lngColumn = lngColumn + 1
    'Wenn Spalte 9
'    Case 8
'        'Wenn lngDauer > 24 dann Wert in Spalte 7 auf "24:00"
'         'If lngDauer >= 24 Then avntValues(8, ialngIndex) = "24:00"
'        lngColumn = lngColumn + 1
    Case Else
        avntValues(lngColumn, ialngIndex) = vntItem
        lngColumn = lngColumn + 1
End Select


Betrifft: AW: VBA Code erläutern
von: Peer
Geschrieben am: 08.11.2020 20:55:19

Aber wenn die Reise um 0:00 beginnt und am 24:00 endet, kommt normalerweise 0:00 raus. Und das _ wollte ich mit
If lngDauer >= 24 Then avntValues(7, ialngIndex) = "24:00"
ändern.

Nun habe ich es in der anfänglichen Case Anweisung eingebaut, aber nun macht er das nicht mehr.
                    
Case Is >= 24
     If strBeginnOrt = "A" Or strEndeOrt = "A" Then
          avntTemp(1, 8) = "40,00 €"
          avntTemp(1, 7) = "24:00"
      ElseIf strBeginnOrt = "I" Or strEndeOrt = "I" Then
          avntTemp(1, 8) = "40,00 €"
          avntTemp(1, 7) = "24:00"
      Else
          avntTemp(1, 8) = "28,00 €"
          avntTemp(1, 7) = "24:00"
      End If


Betrifft: AW: VBA Code erläutern
von: Peer
Geschrieben am: 08.11.2020 21:29:56

Nun fummle ich so lang an einer Lösung, dass ich schlussendlich wieder am Anfang bin.

Ich halte ich an deine Überlegungen und je länger mich daran versuche, um so schlimmer wird der Code und die folgenden Fehlermeldungen.

Ich weiß gerade nicht mehr weiter.
(Für heute) gebe ich auf...

Betrifft: AW: VBA Code erläutern
von: ralf_b
Geschrieben am: 08.11.2020 22:10:44

versuch mal das. zumindest bei mir macht es das was es soll, glaube ich.

'----------- ListBox mit Daten von allen Sheets füllen mit Hilfe von ralf_b von herber.de ------ _
 _
---------------------------------------

    Dim lngMonth As Long, ialngIndex As Long, lngRow As Long
    Dim lngColumn As Long, lngDauer As Long
    Dim datBeginn As Date, datEnde As Date, strBeginnOrt As String
    Dim strEndeOrt As String, strReiseOrt As String
    Dim avntValues() As Variant, avntTemp As Variant, vntItem As Variant

    For lngMonth = 1 To 2 'auf 12 Monate erhöhen!!!
        lngRow = 12
        With Worksheets(MonthName(Month:=lngMonth))
            Do
                
                If ialngIndex = 4 Then MsgBox "x"
                If IsEmpty(.Cells(lngRow + 1, 26).Value) Then
                    lngRow = .Cells(lngRow, 26).End(xlDown).Row
                Else
                    lngRow = lngRow + 1
                End If
                If lngRow < .Rows.Count Then
                    'Festlegung der Listenspalten (hier 8)
                    ReDim Preserve avntValues(8, ialngIndex)
            
                    lngColumn = 0
                    datBeginn = CDate(.Cells(lngRow, "AA")) + CDate(.Cells(lngRow, "AB"))
                    datEnde = CDate(.Cells(lngRow, "AD")) + CDate(.Cells(lngRow, "AE"))
                    lngDauer = DateDiff("h", datBeginn, datEnde)
                    strBeginnOrt = Left(.Cells(lngRow, "AC"), 1)
                    strEndeOrt = Left(.Cells(lngRow, "AF"), 1)
                    
                    
                    'Werte für Spalten 1-8 aus Spalte 26 bis 32 Sheets(oder 38 für AL)
                    avntTemp = .Range(.Cells(lngRow, 26), .Cells(lngRow, 32)).Value

                    'Array Spaltengröße erhöhen und 2 Spalten hinzufügen
                    ReDim Preserve avntTemp(1 To 1, UBound(avntTemp, 2) + 1)
                    avntTemp(1, 7) = .Cells(lngRow, "AE") - .Cells(lngRow, "AB").Value '(Dauer)
                    Select Case lngDauer
                        Case Is <= 8  'der Wert <= 8
                            avntTemp(1, 8) = "00,00 €"
                        Case Is >= 24
                              If strBeginnOrt = "A" Or strEndeOrt = "A" Then
                                   avntTemp(1, 8) = "40,00 €"
                               ElseIf strBeginnOrt = "I" Or strEndeOrt = "I" Then
                                   avntTemp(1, 8) = "40,00 €"
                               Else
                                   avntTemp(1, 8) = "28,00 €"
                               End If
                        Case Is > 8
                           If strBeginnOrt = "A" Or strEndeOrt = "A" Then
                               avntTemp(1, 8) = "27,00 €"
                           ElseIf strBeginnOrt = "I" Or strEndeOrt = "I" Then
                               avntTemp(1, 8) = "27,00 €"
                           Else
                               avntTemp(1, 8) = "14,00 €"
                           End If
                        Case Else
                            avntTemp(1, 8) = "Fehler"
                            Debug.Print vntItem
                    End Select
                      
                    For Each vntItem In avntTemp
                        Select Case lngColumn
                            Case 2, 5, 7
                                avntValues(lngColumn, ialngIndex) = Format$(vntItem, "Hh:Nn")
                                lngColumn = lngColumn + 1
                            Case Else
                                avntValues(lngColumn, ialngIndex) = vntItem
                                lngColumn = lngColumn + 1
                        End Select
                   Next
                     ialngIndex = ialngIndex + 1
                Else
                    Exit Do
                End If
            Loop
        End With
    Next
    lst_Dienstreise.Column = avntValues
'--------- Ende Füllen ListBox


Betrifft: AW: VBA Code erläutern
von: Peer
Geschrieben am: 09.11.2020 16:17:10

Hallo ralf_B.
Danke dafür, du hast mich aus der Verzweiflung gerettet.
Bis jetzt funktioniert es auch.
Ich musste nur noch das Problem mit der Reise >= 24 Std angreifen.
Ich habe es mit

Case Is >= 24
      avntTemp(1, 7) = "24:00"
   If strBeginnOrt = "A" Or strEndeOrt = "A" Then
      avntTemp(1, 8) = "40,00 €"
   ElseIf strBeginnOrt = "I" Or strEndeOrt = "I" Then
      avntTemp(1, 8) = "40,00 €"
   Else
      avntTemp(1, 8) = "28,00 €"
   End If
beseitigt.
Glaube ich... ;-).
Gestern habe ich vor lauter Unlust das nächste Anliegen anpacken wollen, und habe mich gefragt (auch fast die ganze Nacht), ob man dieses Array auch filtern kann. Z.B nach Monaten oder nach Reisezweck?

Muss ich für die Filter das Array neu einlesen lassen oder kann ich das vorhandene nutzen?

LG
Peer

Betrifft: AW: VBA Code erläutern
von: Peer
Geschrieben am: 09.11.2020 16:19:36

@ralf_b

Was hat es mit
 If ialngIndex = 4 Then MsgBox "x"
bezwecken wollen?

Betrifft: AW: VBA Code erläutern
von: ralf_b
Geschrieben am: 09.11.2020 17:48:21

kann weg.war nur ein stop bei zeile 4

Betrifft: Vielen Dank, ralf_b, für deine Hilfe und Mühe
von: Peer
Geschrieben am: 09.11.2020 18:54:30

Alles klar, habe ich entfernt.

Betrifft: AW: VBA Code erläutern
von: Matthias
Geschrieben am: 07.11.2020 15:35:54

Moin!
Lese interessiert mit und hätte was offtopic. Das mit den Zeiten (8 und 24) und den dazugehörigem Beträgen klingt ein bisschen nach Verpflegungsmehraufwendungen bei Reisekosten. Die wurden m.W. Angang 2020 erhöht und sind jetzt 14 bzw. 28 €. Ggf. mal prüfen, ob das bei euch auch so ist. VG

Betrifft: AW: VBA Code erläutern
von: ralf_b
Geschrieben am: 07.11.2020 17:01:48

Wieviel eine Firma von den gesetzlichen VMA zahlen möchte ist aber verschieden. Ich habe da in letzter Zeit schon ganz andere Werte gesehen. der TE wird schon wissen welche Beträge er heute oder in Zukunft eintragen möchte.

Dem Arbeitnehmer bleibt es leider dann überlassen, die Differenz bei der Steuererklärung mit einzuordern.

Betrifft: AW: VBA Code erläutern
von: Peer
Geschrieben am: 07.11.2020 19:05:01

Hallo Matthias.
Vollkommen richtig. Das Anpassen wäre die einfachste Aufgabe.

Gruß
Peer