Microsoft Excel

Herbers Excel/VBA-Archiv

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

Code optimieren | Herbers Excel-Forum


Betrifft: Code optimieren von: otto
Geschrieben am: 12.01.2010 07:24:59

Hi,

kann man nachfolgenden Code noch optimieren? Ich habe ihn mal im Forum gefunden und etwas angepasst. Er sucht in nur ca. 1500 Teilen, könnte aber schneller sein.

Private Sub TextBox1_Change()
On Error Resume Next
Dim arr() As Variant, Tmp As Variant, wks As Worksheet, zelle, preis1 As Currency, preis2 As  _
Currency, preisg As Currency
  Dim index As Integer
  Dim X, anz, icount
  anz = 0
  zelle = Cells(12, 4)
  Set wks = Sheets("Stamm")
  X = wks.Range("C65536").End(xlUp).Row
  Tmp = wks.Range("C4:CH" & 4 + X)
  X = X - 4
  If TextBox1 = "" Then
    On Error GoTo weiter
    ReDim arr(0 To 5, 0 To X - 1)
    For index = 1 To UBound(Tmp, 1)
    On Error Resume Next
      arr(0, icount) = Tmp(index, 23)
        arr(1, icount) = Tmp(index, 35)
        arr(2, icount) = Tmp(index, 20)
        preis1 = Tmp(index, 37)
        preis2 = Tmp(index, 38)
        preisg = preis1 + preis2
        arr(3, icount) = VBA.Format(preisg, "0.00")
        arr(4, icount) = Tmp(index, 2)
        arr(5, icount) = Tmp(index, 1)
    Next
weiter:
    ListBox1.Clear
    Länge.Enabled = True
Else
  For index = 1 To UBound(Tmp, 1)
    If VBA.LCase(Left(Tmp(index, 23), VBA.Len(TextBox1))) = VBA.LCase(TextBox1) _
    Or Tmp(index, 23) Like "*" & VBA.UCase(TextBox1) & "*" _
    Or Tmp(index, 23) Like "*" & VBA.LCase(TextBox1) & "*" _
    Or VBA.LCase(Left(Tmp(index, 1), VBA.Len(TextBox1))) = VBA.LCase(TextBox1) _
    Or Tmp(index, 1) Like "*" & VBA.UCase(TextBox1) & "*" _
    Or Tmp(index, 1) Like "*" & VBA.LCase(TextBox1) & "*" _
    Or VBA.LCase(Left(Tmp(index, 2), VBA.Len(TextBox1))) = VBA.LCase(TextBox1) _
    Or Tmp(index, 2) Like "*" & VBA.UCase(TextBox1) & "*" _
    Or Tmp(index, 2) Like "*" & VBA.LCase(TextBox1) & "*" Then
        ReDim Preserve arr(0 To 5, 0 To icount)
        arr(0, icount) = Tmp(index, 23)
        arr(1, icount) = Tmp(index, 35)
        arr(2, icount) = Tmp(index, 20)
        preis1 = Tmp(index, 37)
        preis2 = Tmp(index, 38)
        preisg = preis1 + preis2
        arr(3, icount) = VBA.Format(preisg, "0.00")
        arr(4, icount) = Tmp(index, 2)
        arr(5, icount) = Tmp(index, 1)
        icount = icount + 1
        anz = anz + 1
    End If
  Next
End If
werticount = icount
  If icount <> 0 Then
  ListBox1.Column = arr
  Else
  ListBox1.Clear
  End If
End Sub

Ich freue mich auf eure Vorschläge.

Gruß
otto

  

Betrifft: AW: Code optimieren von: Mike
Geschrieben am: 12.01.2010 09:31:04


Hey Otto,

ich kann Dir zwar den Code nicht anpassen, trotzdem wäre es schön, etwas
mehr zu Deinen Wünschen zu erfahren.

Wenn nicht nur 1'500 "Teilen" relevant sind, wieviel dann in Deinem Beispiel?
Als Range wird ja schon mal alle Zeilen ausgewählt, das machts u.a. langsamer.

Gruss
Mike


  

Betrifft: AW: Code optimieren von: otto
Geschrieben am: 12.01.2010 09:54:01

Hi,

ich meine natürlich Zeilen und nicht Teilen !
Das Range sind nicht alle Zeilen, sondern nur die von Zeile 4 bis X, welches vorher ermittelt wird.

Die TextBox1 ist meine Suchbox, es wird dann in den angegebenen Spalten geschaut, ob die eigegebene Zeichenfolge enthalten ist. Das dauert halt.
Vielleicht gibt es noch eine andere Vergleichsmöglichkeit außer Like?

otto


  

Betrifft: AW: Code optimieren: ein paar Ideen. von: Daniel
Geschrieben am: 12.01.2010 22:39:27

Hi

es wäre schön, wenn du eine Vollständige Datei posten würdest, mit der man auch was ausprobieren kann. so rein theoretsich ist des doch recht schwierig.



ein erster Ansatz wäre, die Zähler- und Zeilen-Nr- Variablen X, anz und icount nicht als Variant, sondern als LONG zu deklarieren.

das würde schon mal etwas bringen.



und deine komische if-Abfrage würde ich auch mal überarbeiten.



wenn du überprüfen willst, ob eine Zeichenfolge irgendwo enthalten ist, dann ist die INSTR-Funkion schneller als Like. INSTR ermittelt die Position des Suchbegriffs im Text und hat als Ergebnis 0, wenn dieser nicht gefunden wird.

außderdem , statt auf Gross- und Kleinschreibung zu prüfen, wandel doch beides in Klein um, dann sparts du dir eine Bedingung.

und wenn du schon den Textboxinhalt ständig in Gross- oder Kleinbuchstaben umwandelst, dann ist es auch günstiger, das einmal zu machen und das Ergebnis in einer Variable abzulegen, statt jedesmal umzuwandeln.



ein weiterer Nachteil deiner riesen OR-Abfrage ist, daß immer alles geprüft wird, auch wenn es nicht notwendig ist, denn wenn z.B. die erste Bedingung erfüllt ist, dann wird der TRUE-Zweig von IF ausgeführt, egal wie die anderen Bedingungen sind, diese müssen nicht mehr geprüft werden.

dh.

folgende Struktur ist langsam, weil immer alle Bedingungen geprüft werden:

If Bed1 Or Bed2 or Bed3 Or Bed3 Or Bed4 Then
xxxx
xxxx
End if
das hier ist schneller, denn wenn Bedingung 1 erfüllt ist, werden 2, 3 und 4 nicht mehr geprüft: _
Dim Machen as Boolean

If Bed1 Then
  Machen = True
ElseIF Bed2 Then
  Machen = True
ElseIF Bed3 Then 
  Machen = True
ElseIF Bed4 Then
  Machen = True
Else
  Machen = False
End if
If Machen Then
   xxxx
   xxxx
End IF

du kannst diese Abfrage noch optimieren, in dem du die Bedingung, die statistisch am häufigsten erfüllt ist, an den Anfang stellst.



gruß, Daniel