Code ist extrem langsam
05.03.2020 16:37:28
Peter
ich habe eine extrem grosse Excel-Datei. Diese Datei zu übermitteln ist aufgrund der Grösse nicht möglich.
Mit der UF wird ein Datum-Bereich ausgewählt und anhand dieser Datumsdaten wird die ListBox1 befüllt.
Ein Code zum Befüllen von drei Möglichkeiten ist:
'Auswahl anzeigen Datum
Private Sub CommandButton17_Click()
Dim i As Long, maxlen As Long, Li As Long, ii As Long, LC As Long
Dim str As String, str2 As String, arr As Variant
Dim Startdatum As Date, EndDatum As Date
Dim wsName As String
Dim Kat As String
wsName = Me.ComboBox3.Value
Worksheets(wsName).Activate
'geändert auf CDate(UserForm4.tbx_Startdatum)
'in einer Textbox steht Text der wird mit
'CDate in ein Datum gewandelt
Startdatum = CDate(Me.TextBox2)
EndDatum = CDate(Me.TextBox3)
Kat = Me.TextBox1
maxlen = 35
With ListBox1
.Clear
.ColumnCount = 10
.ColumnWidths = "3cm;6cm;3cm;3cm;3cm;3cm;0cm;0cm;0cm;0cm" 'Spalte 6 Bestand wird _
ausgeblendet
'hier geändert von i=9 auf i=10 (ohne die Kopfzeilen)
'Spaltenbeschriftung über Label
''' For i = 10 To Cells(Rows.Count, "B").End(xlUp).Row
For i = 11 To Cells(Rows.Count, "B").End(xlUp).Row '11=ohne Saldozeile
' For i = 12 To Cells(Rows.Count, "B").End(xlUp).Row '12=ohne Saldozeile
Li = i - 9
''' Li = i - 10
str = Cells(i, 3)
If CDate(Cells(i, 2)) >= Startdatum And CDate(Cells(i, 2)) 9 Then
If str = "" Then str = " "
arr = Split(str, " ")
str2 = arr(0)
For ii = 1 To UBound(arr)
If Len(str2 & arr(ii)) + 1 "" Then
intSum1 = intSum1 + .List(i, 4)
End If
Next
End With
Label6 = intSum1
Label6 = Format(Label6, "#,##0.00 ")
'Summe Spalte4=SpalteF=Ausgaben
With ListBox1
For i = 0 To .ListCount - 1
If .List(i, 5) > "" Then
intSum2 = intSum2 + .List(i, 5)
End If
Next
End With
Label7 = intSum2
Label7 = Format(Label7, "#,##0.00 ")
'Summe Einnahmen ./. Ausgaben in Label8
Label8 = Label6 - Label7
Label8 = Format(Label8, "#,##0.00 ")
'Anfang Anfangbestand und Endbestand in jeweils ausgewähltem Bereich
With ListBox1
If ListBox1.ListCount > 0 Then
Label18 = (.List(0, 6))
Label18 = Format(Label18, "#,##0.00 ")
Label19 = (.List(0, 4))
Label19 = Format(Label19, "#,##0.00 ")
Label20 = (.List(0, 5))
Label20 = Format(Label20, "#,##0.00 ")
Label21 = CDbl(Label18) - CDbl(Label19) + CDbl(Label20)
Label21 = Format(Label21, "#,##0.00 ")
Label22 = CDbl(Label21) + CDbl(Label6) - CDbl(Label7)
Label22 = Format(Label22, "#,##0.00 ")
End If
End With
'Ende Anfangbestand und Endbestand in jeweils ausgewähltem Bereich
If ListBox1.ListCount > 0 Then
'CommandButton28.Enabled = True
CommandButton3.Enabled = True
End If
End Sub
Das Auslesen der gesuchten Daten im Formular wird dann mit dem nachfolgenden Code und weiteren Daten in die Tabelle1 übertragen:'Werte übertragen für Druckvorschau schnell
Private Sub CommandButton31_Click()
Dim lngLetzte As Long 'benötigt für letzte beschriebene Zelle - löschen von Tabelle1
' 'Anfang Dim für Kontoinhaber übertragen
Dim SuchDatumA As Date 'benötigt für Übertrag der Daten von Tabelle: Kontoinhaber
Dim SearchFor As Object 'benötigt für Übertrag der Daten von Tabelle: Kontoinhaber
' 'Ende Dim für Kontoinhaber übertragen
Dim wsName As String 'benötigt für Kontodaten und Überschriften
Dim i As Long 'benötigt für Übertrag von ListBox1 in Tabelle1
Dim j As Long 'benötigt für Übertrag von ListBox1 in Tabelle1
Dim Zelle As Range 'benötigt für Umwandlung in Zahl in Tabelle1
Dim s As Long 'benötigt für Umwandlung in Zahl in Tabelle1
Dim zB As String 'benötigt für letzte beschriebene Zelle in Spalte B
Dim zF As String 'benötigt für letzte beschriebene Zelle in Spalte F
Dim zG As String 'benötigt für letzte beschriebene Zelle in Spalte G
Dim zH As String 'benötigt für letzte beschriebene Zelle in Spalte H
Dim zJ As String 'benötigt für letzte beschriebene Zelle in Spalte J
With Worksheets("Tabelle1")
lngLetzte = .Cells(Rows.Count, 7).End(xlUp).Row 'wählt die letzte, beschriebene Zelle _
von unten
.Range("A1:M" & lngLetzte).Clear
End With
'Anfang für Kontoinhaber übertragen
SuchDatumA = Me.TextBox2 'Worksheets("Kontoinhaber").Cells(3, 16)
Set SearchFor = Worksheets("Kontoinhaber").Cells(WorksheetFunction.Match(CLng(SuchDatumA), _
Worksheets("Kontoinhaber").Columns(6), 1), 6)
With Worksheets("Kontoinhaber")
Worksheets("Tabelle1").Range("B1") = .Cells(SearchFor.Row, 1) 'Inhaber_Name
Worksheets("Tabelle1").Range("B2") = .Cells(SearchFor.Row, 2) 'Inhaber_Strasse
Worksheets("Tabelle1").Range("B3") = .Cells(SearchFor.Row, 3) 'Inhaber_PLZ
Worksheets("Tabelle1").Range("C3") = .Cells(SearchFor.Row, 4) 'Inhabere_Ort
Worksheets("Tabelle1").Range("C4") = 0 & .Cells(SearchFor.Row, 5) '0 & Inhaber_Tel
End With
'Ende für Kontoinhaber übertragen
'!Übertrag Kontoinhaber funktioniert
'Anfang Kontodaten übertragen
wsName = Me.ComboBox3.Value 'übernimmt Kontoname für Kontoinhaber übertragen
With Worksheets("Tabelle1")
.Range("B4") = Sheets(wsName).Range("B4")
.Range("B5") = Sheets(wsName).Range("B5")
.Range("B6") = Sheets(wsName).Range("B6")
.Range("B7") = Sheets(wsName).Range("B7")
.Range("D3") = Sheets(wsName).Range("D3")
.Range("E3") = Sheets(wsName).Range("E3")
.Range("D4") = Sheets(wsName).Range("D4")
.Range("E4") = Sheets(wsName).Range("E4")
.Range("G3") = Sheets(wsName).Range("G3")
.Range("H3") = Sheets(wsName).Range("H3")
.Range("G4") = Sheets(wsName).Range("G4")
.Range("H4") = Sheets(wsName).Range("H4")
.Range("F5") = Sheets(wsName).Range("F5")
.Range("G5") = Sheets(wsName).Range("G5")
.Range("F6") = Sheets(wsName).Range("F6")
.Range("G6") = Sheets(wsName).Range("G6")
.Range("G7") = Sheets(wsName).Range("G7")
.Range("G8") = Sheets(wsName).Range("G8")
.Range("H7") = Sheets(wsName).Range("H7")
.Range("H8") = Sheets(wsName).Range("H8")
'Ende Kontodaten übertragen
'Anfang Überschriften übertragen
.Range("B9") = Sheets(wsName).Range("B9")
.Range("C9") = Sheets(wsName).Range("C9")
.Range("D9") = Sheets(wsName).Range("D9")
.Range("E9") = Sheets(wsName).Range("E9")
.Range("F9") = Sheets(wsName).Range("F9")
.Range("G9") = Sheets(wsName).Range("G9")
.Range("H9") = Sheets(wsName).Range("H9")
'Ende Überschriften übertragen
'!Übertrag Kontodaten und Überschriften funktioniert
'Anfang Übertrag von ListBox1 in Tabelle1
With Worksheets("Tabelle1")
With ListBox1
For i = 0 To .ListCount - 1
For j = 0 To .ColumnCount - 1
Worksheets("Tabelle1").Cells(i + 10, j + 2) = .List(i, j) 'Zeile 10 _
Spalte 2=B
Next
Next
End With
End With
'Ende Übertrag von ListBox1 in Tabelle1
'Anfang Umwandlung von Zahl in Währung
zF = .Cells(Rows.Count, 6).End(xlUp).Row
'For Each Zelle In Worksheets("Tabelle1").Range("F10:F16")
For Each Zelle In Worksheets("Tabelle1").Range("F10:F" & zF)
s = Zelle.Value
If Zelle.Offset(0, -1) > "" And s > "0" And IsNumeric(s) Then
s = s * 1 'hier wird von Text in Zahl umgewandelt
Zelle.Value = s
Zelle.NumberFormat = "#,##0.00 " 'hiermit wird auf Währung umgestellt
ElseIf Zelle.Offset(0, -1) = "" Then
Zelle = ""
ElseIf Zelle.Offset(0, -1) > "" And s = "0" Then
Zelle = ""
End If
Next Zelle
zG = .Cells(Rows.Count, 7).End(xlUp).Row
'For Each Zelle In Worksheets("Tabelle1").Range("G10:G16")
For Each Zelle In Worksheets("Tabelle1").Range("G10:G" & zG)
s = Zelle.Value
If Zelle.Offset(0, -2) > "" And s > "0" And IsNumeric(s) Then
s = s * 1 'hier wird von Text in Zahl umgewandelt
Zelle.Value = s
Zelle.NumberFormat = "#,##0.00 " 'hiermit wird auf Währung umgestellt
ElseIf Zelle.Offset(0, -2) = "" Then
Zelle = ""
ElseIf Zelle.Offset(0, -2) > "" And s = "0" Then
Zelle = ""
End If
Next Zelle
zH = .Cells(Rows.Count, 8).End(xlUp).Row
For Each Zelle In Worksheets("Tabelle1").Range("H10:H" & zH)
s = Zelle.Value
If Zelle.Offset(0, -3) > "" And s > "0" And IsNumeric(s) Then
s = s * 1 'hier wird von Text in Zahl umgewandelt
Zelle.Value = s
Zelle.NumberFormat = "#,##0.00 " 'hiermit wird auf Währung umgestellt
ElseIf Zelle.Offset(0, -3) = "" Then
Zelle = ""
ElseIf Zelle.Offset(0, -3) > "" And s = "0" Then
Zelle = ""
End If
Next Zelle
'Ende Umwandlung von Zahl in Währung
'Anfang Übertrag der Salden
.Range("B65536").End(xlUp).Offset(2, 3) = "Anfangsaldo der Auswahl" 'Me.Label23
.Range("B65536").End(xlUp).Offset(3, 3) = CDbl(Me.Label21)
.Range("B65536").End(xlUp).Offset(3, 3).NumberFormat = "#,##0.00 "
.Range("B65536").End(xlUp).Offset(2, 4) = "Summe Einnahmen der Auswahl" 'Me.Label24
.Range("B65536").End(xlUp).Offset(3, 4) = CDbl(Me.Label6)
.Range("B65536").End(xlUp).Offset(3, 4).NumberFormat = "#,##0.00 "
.Range("B65536").End(xlUp).Offset(2, 5) = "Summe Ausgabe der Auswahl" 'Me.Label25
.Range("B65536").End(xlUp).Offset(3, 5) = CDbl(Me.Label7)
.Range("B65536").End(xlUp).Offset(3, 5).NumberFormat = "#,##0.00 "
.Range("B65536").End(xlUp).Offset(4, 5) = "Ges.- Einnahmen - Ges.- Ausgaben" 'Me.Label26"
.Range("B65536").End(xlUp).Offset(5, 5) = CDbl(Me.Label8)
.Range("B65536").End(xlUp).Offset(5, 5).NumberFormat = "#,##0.00 "
.Range("B65536").End(xlUp).Offset(2, 6) = "Endsaldo der Auswahl" 'Me.Label27"
.Range("B65536").End(xlUp).Offset(3, 6) = CDbl(Me.Label22)
.Range("B65536").End(xlUp).Offset(3, 6).NumberFormat = "#,##0.00 "
'Ende Übertrag der Salden
'Anfang Spalte J u K leeren - wegen Drucken
zJ = .Cells(Rows.Count, 10).End(xlUp).Row
.Range("J2:K" & zJ).Clear
'Ende Spalte J u K leeren - wegen Drucken
End With
End Sub
Und zum Schluss wird mittels dem nachfolgenden Code die Druckvorschau geöffnet:'Druckvorschau Test - schnell
Private Sub CommandButton30_Click()
Dim lngLetzte As Long 'benötigt für letzte beschriebene Zelle
Me.Hide
With Worksheets("Tabelle1")
lngLetzte = .Cells(Rows.Count, 7).End(xlUp).Row 'wählt die letzte, beschriebene Zelle _
von unten
'Anfang - Seite_einrichten Makro
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = ""
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintArea = "$B$1:$H$" & lngLetzte
.PrintTitleRows = "$1:$9"
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.590551181102362)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
.PrintHeadings = False
.PrintGridlines = True
.PrintComments = xlPrintNoComments
.PrintQuality = -3
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 75
End With
'Ende - Seite_einrichten Makro
.PrintPreview
Me.Show
End With
End Sub
Insgesamt dauert diese Prozedur ca. 2-3 Minuten, wobei die Tabelle1 nur mit 74 Zeilen gefüllt ist. Es können auch ca. 600 werden.Könnt ihr mir bitte helfen, dass der zweite Code zum Befüllen der Tabelle1 deutlich schneller wird.
Besten Dank
Gruss
Peter