Datenschutzerklärung


Direktnachricht



Ihre Software
Details
Excel/VBA 🔍
Add-Ins

Suche in Beispielen und Tipps zu Excel und VBA

Suchbegriff(e) mit Leerzeichen getrennt:

Bei Uhrzeiten in Spalte A Makro ausführen (OnTime)Makro/Sub/Prozedur

Kategorien: Ereignisse ▸ Zeit und Tabelle ▸ Zellen

(Tipp 125) Nachricht zum Beitrag an Autor Nach oben

In Spalte A stehen Uhrzeiten. Zu jeder dieser Zeit soll ein Text in die Nachbarzellen eingelesen werden. Wie lautet das Makro?

Basis: In Spalte A (im Code also 1) stehen aufsteigend sortiert und ohne leere Zellen dazwischen die Zeiten.

Zum Umsetzen der Aufgabenstellung benötigen wir vier einzelne Routinen. Diesen müssen wir zwei Variablen zur Verfügung stellen, die deshalb ganz am Anfang des Moduls deklariert werden müssen:

Private lngZ As LongPtr Private bolC As Boolean

In der ersten Routine Start() löschen wir die Zellinhalte neben den Uhrzeiten und legen in lngZ die erste Zeile mit einer Uhrzeit fest. Dazu setzen wir bolC auf True. Das Makro, das später die Eintragungen vornimmt, wird diese Variable prüfen und den Vorgang nur fortsetzen, wenn diese auf True gesetzt ist. Anschließend starten wir den Vorgang mit der Sub ZeitFestLegen():

Sub Start() lngZ = 2 'erste Zeile mit Uhrzeit Range(Cells(lngZ, 2), Cells(Rows.Count, 3)).ClearContents bolC = True ZeitFestLegen End Sub

Die Sub ZeitFestLegen() hat nur folgende Aufgaben:

  • prüfen, ob in Spalte A der Zeile lngZ eine Zeit steht,
  • prüfen, ob bolC noch True ist,
  • wenn zweimal ja: festlegen, dass bei der Zeit in Zeile lngZ etwas passiert (das Makro Eintragen ausführen) - zunächst ist das hier die Zeit in Zeile 2, weil wir die bereits in lngZ festgelegt haben:

Sub ZeitFestLegen() Dim datZeitAngabe As Date If Cells(lngZ, 1).Value = "" Then Exit Sub If bolC = False Then MsgBox "Makro wurde angehalten" Exit Sub End If datZeitAngabe = Cells(lngZ, 1).Value Application.OnTime datZeitAngabe, "Eintragen" End Sub

Nun weiß der Code, wann etwas passieren soll, nämlich bei der ersten Zeit. Mit der Sub Eintragen() legen wir fest, was passieren soll. Hier werden einfache Texte in die Nachbarzellen eingetragen.

Nach dem Eintragen wird die Zeile lngZ um 1 erhöht und wieder die Sub ZeitFestLegen aufgerufen, die nun dafür sorgt, dass das Ganze bei der nun in der nächsten Zeile gefundenen Zeit von vorn losgeht - wenn eine Zeit in dieser neuen Zelle steht und bolC immer noch True ist:

Sub Eintragen() Cells(lngZ, 2).Value = "Hallo, Spalte B" Cells(lngZ, 3).Value = "Hallo, Spalte C" lngZ = lngZ + 1 ZeitFestLegen End Sub

Das sollte schon mal funktionieren. Aber: Es gibt für den Anwender keine Möglichkeit, das vorzeitig abzubrechen. Bei der nächsten Zeit wird eingetragen und sofort die neue Zeit festgelegt.

Deshalb sollten wir eine Möglichkeit einbauen, den Wert der Variablen bolC auf False zu setzen, damit die Sub ZeitFestLegen den Vorgang abbricht. Dazu reicht das Stück Code, das man vielleicht an einen Button o. ä. binden kann:

Sub Stoppen() bolC = False End Sub

Hochkomma (') entfernenMakro/Sub/ProzedurTipp

Kategorien: Tabelle ▸ Zellen und Format ▸ Text

(Tipp 234) Nachricht zum Beitrag an Autor Nach oben

Wie kann das führende Hochkomma entfernt werden?

Zum Verständnis: Das führende Hochkomma gehört zum Format der Zelle, nicht zum Inhalt.

Am einfachsten ist sicher, das Format einer anderen Zelle zu übertragen.

Dazu wird eine leere Zelle benötigt, die genau so formatiert wird, wie es bei den Zellen mit den Hochkommas der Fall ist. Damit sind also Farben, Rahmen, Schriftformat und ggf. Zahlenformat gemeint. Aber Vorsicht: Nicht das Format einer Zelle mit Hochkomma auf diese Zelle übertragen, sondern diese Zelle manuell formatieren!

Das Formatieren kann natürlich entfallen, wenn es bei den Zellen mit den Hochkommas nur um die Inhalte geht, wenn es sich also um reine Daten handelt, die nicht zur Ansicht oder zum Druck vorgesehen sind.

Nun die so formatierte Zelle aktivieren (anklicken), anschließend im Ribbon Start auf den Pinsel zum Format übertragen klicken und sofort über die Zellen ziehen, in denen die zu entfernenden Hochkommas sind.

Alternativ geht das auch per VBA. M2 ist die manuell formatierte Zelle, in A2:A20 sind die zu entfernenden Hochkommas:

Range("M2").Copy Range("A2:A20").PasteSpecial Paste:=xlPasteFormats

Sollten dann noch Formeln angezeigt werden oder Zahlen als Text, kann dieser Code mit dem Code in Textzahl zu Zahl kombiniert werden.


Ähnlich ist eine Alternative, bei der Excel zum Rechnen gezwungen wird. Dazu wird eine Zelle mit einer 1 kopiert und der zu ändernde Bereich per Inhalte einfügen ▸ Multiplizieren damit multipliziert:

Range("IV10000") = "1" Range("IV10000").Copy 'Bereich anpassen: Range("A2:A16").PasteSpecial Paste:=xlAll, Operation:=xlMultiply Range("IV10000").ClearContents


Außerdem können die Hochkommas so entfernt werden:

  1. Eine Hilfsspalte anlegen, die später wieder gelöscht werden kann.
  2. In die erste Zeile dieser Hilfsspalte einen Bezug auf die Zelle mit dem Hochkomma schreiben, z. B. =A1.
  3. Diese Formel nach unten kopieren, soweit, wie sich Einträge mit Hochkommas in den Zellen befinden. Jetzt sind die Einträge doppelt - einmal mit Hochkomma und einmal als Ergebnis des Bezugs.
  4. Die Zellen mit den Bezügen markieren.
  5. Kopieren und gleich Bearbeiten - Inhalte einfügen - Werte wählen.
  6. Da die Zellen noch markiert sind, noch einmal kopieren.
  7. Die erste Zelle mit einem Hochkomma aktivieren und einfügen. Jetzt müßten die Hochkommas verschwunden sein und die Hilfsspalte kann nun wieder gelöscht werden.

Nicht geschützte Zellen suchenMakro/Sub/Prozedur

Kategorien: Tabelle ▸ Zellen und Tabelle ▸ Schutz

(Tipp 147) Nachricht zum Beitrag an Autor Nach oben

Ich habe die Arbeitsblätter geschützt. Teilweise sind die Zellen aber ungeschützt. Ich würde gerne einfach die Tabelle markieren und dann im VBA die Werte aller ungeschützten Zellen löschen ohne genaue Bezüge angeben zu müssen.

Die gesamte Tabelle nach ungeschützten Zellen durchsuchen zu lassen würde sicher zu lange dauern. Besser ist es, den Bereich, der durchsucht werden soll, einzugrenzen und dann in einer verschachtelten Schleife alle Inhalte ungeschützter Zellen zu löschen. Das Makro wäre dann:

Sub UngeschuetztLoeschen() Dim lngZ As Long, lngZ1 As Long, lngZ2 As Long, lngS As Long, lngS1 As Long, lngS2 As Long lngZ1 = 1 'Erste Zeile mit zu löschenden Inhalten lngZ2 = 100 'Letzte Zeile mit zu löschenden Inhalten lngS1 = 1 'Erste Spalte mit zu löschenden Inhalten lngS2 = 5 'Letzte Spalte mit zu löschenden Inhalten For lngZ = lngZ1 To lngZ2 For lngS = lngS1 To lngS2 Application.StatusBar = Cells(lngZ, lngS).Address If Cells(lngZ, lngS).Locked = False Then Cells(lngZ, lngS).ClearContents Next Next Application.StatusBar = False End Sub

Oder einfach per UsedRange:

Dim rngZelle As Range For Each rngZelle In ActiveSheet.UsedRange If rngZelle.Locked = False Then rngZelle.ClearContents Next

Rechnen ohne Gleichheitszeichen (Worksheet_Change)Makro/Sub/ProzedurUDF - benutzerdefinierte Funktion

Kategorien: Ereignisse ▸ Tabellen und Tabelle ▸ Formeln

(Tipp 417) Nachricht zum Beitrag an Autor Nach oben

In Spalte A werden Berechnungen ohne Gleichheitszeichen eigetragen. Wie erhalte ich in B die Ergebnisse?

Worksheet_Change-Ereignis

Die Routine wird im VBA-Editor in das Modul eingetragen, das durch Doppelklick auf die Tabelle, in der der Code wirken soll, geöffnet wird. Es werden hier zwei Varianten aufgezeigt: In Spalte B wird eine Formel eingetragen, die das Ergebnis liefert. Falls ein Ergebnis ohne Formel gewünscht wird, wird dies noch in Spalte C eingetragen.

Die Routine wird nur ausgeführt, wenn die Eingabezelle in Spalte 1 (A) ist. Dann werden zunächst die Zielzellen daneben in B und C geleert.

Da intern mit Punkt statt Komma als Dezimaltrenner gerechnet wird, wird ein eventuell vorhandenes Komma zuerst ersetzt. Anschließend wird mit Evaluate versucht, zu berechnen. Wird die Berechnung erkannt, wird ein Ergebnis geliefert, sonst der Fehler #NAME?. Letzteres kommt zum Beispiel vor, wenn ein Text in A eingetragen wurde.

Tritt kein Fehler auf, wird in B die entsprechende Formel eingetragen, in C direkt das Ergebnis.

Private Sub Worksheet_Change(ByVal Target As Range) Dim varTemp, varErg If Target.Column > 1 Then Exit Sub Range("B" & Target.Row & ":C" & Target.Row).ClearContents varTemp = Replace(Target, ",", ".") varErg = Application.Evaluate(varTemp) If Not IsError(varErg) Then Cells(Target.Row, 2).Formula = "" & "=" & varTemp & "" Cells(Target.Row, 3) = varErg End If End Sub


UDF - benutzerdefinierte Funktion

Es ist (in diesem Fall ab Excel 365) auch möglich, das Ergebnis der Berechnung ohne Gleichheitszeichen per Formel zu erhalten. Notwendig ist dazu eine solche benutzerdefinierte Funktion in einem Standardmodul:

Function Evaluate_String(ByVal strString As String, Optional intWas As Integer = 0) Dim varTemp, varErg Evaluate_String = "" varTemp = Replace(strString, ",", ".") varErg = Application.Evaluate(varTemp) If Not IsError(varErg) Then Evaluate_String = IIf(intWas <> 0, "=" & varTemp & "", varErg) End Function

In die Zelle, in der das Ergebnis der Formel ohne Gleichheitszeichen erscheinen soll, muss dann nur:

=Evaluate_String(C10)

Wenn die Formel nicht das Ergebnis, sondern die Formel (also mit Gleichheitszeichen) anzeigen soll, kann als zweiter Parameter etwas anderes als 0 verwendet werden, zum Beispiel:

=Evaluate_String(C10;1)

Sie hat dann ein vergleichbares Verhalten wie die integrierte Funktion FORMELTEXT().

Verzeichnis auslesen (Makro und Funktion)Makro/Sub/ProzedurUDF - benutzerdefinierte Funktion

Kategorien: Dateien und Ordner ▸ Ordner und Dateien und Ordner ▸ Dateien

(Tipp 32) Nachricht zum Beitrag an Autor Nach oben

Wie kann man mit VBA ein Verzeichnis mit allen darin befindlichen Dateien auslesen?

An der Stelle des Sternchens in Dir(strOrdner & "*.*") können auch bestimmte Dateiendungen eingetragen wreden, so dass nur nach Dateien eines Typs gesucht wird.

Subs

Sollen Dateien nur direkt im Ordner, nicht aber in Unterordnern, gesucht werden, reicht dieser Code:

Sub Suchen_nur_Dateien() Dim strOrdner As String, strDatei As String Dim lngZ As LongPtr strOrdner = "C:\Eigene Dateien\" lngZ = 2 Range("a1:e50000").ClearContents strDatei = Dir(strOrdner & "*.*") Do While strDatei <> "" If strDatei <> "" Then lngZ = lngZ + 1 Cells(lngZ, 1) = strOrdner & strDatei 'Pfad Cells(lngZ, 2) = FileLen(strOrdner & strDatei) 'Größe Cells(lngZ, 3) = FileDateTime(strOrdner & strDatei) 'Datum/Zeit Cells(lngZ, 4) = strDatei 'nur Dateiname End If strDatei = Dir Loop End Sub

Soll auch in Unterordnern gesucht werden, ist dies eine Möglichkeit:

Private lngZ As LongPtr Sub Suchen_mit_Unterordnern() 'Aufruf lngZ = 2 Range("a1:e50000").ClearContents Dateisuche "C:\Eigene Dateien", "*.*" End Sub Sub Dateisuche(strOrdner As String, strDateien As String) Dim strTemp As String, strWdhlg As String strOrdner = strOrdner & IIf(Right(strOrdner, 1) <> "\", "\", "") strTemp = Dir(strOrdner & strDateien) Do While Len(strTemp) Cells(lngZ, 1) = strOrdner & strTemp 'Pfad Cells(lngZ, 2) = FileLen(strOrdner & strTemp) 'Größe Cells(lngZ, 3) = FileDateTime(strOrdner & strTemp) 'Datum/Zeit Cells(lngZ, 4) = strTemp 'nur Dateiname lngZ = lngZ + 1 strTemp = Dir() Loop strTemp = Dir(strOrdner, vbDirectory) Do While Len(strTemp) If (strTemp <> ".") And (strTemp <> "..") Then If (GetAttr(strOrdner & strTemp) And vbDirectory) = vbDirectory Then Dateisuche strOrdner & strTemp, strDateien lngZ = lngZ - 1 strWdhlg = Dir(strOrdner, vbDirectory) lngZ = lngZ + 1 Do While strWdhlg <> strTemp: strWdhlg = Dir(): Loop End If End If strTemp = Dir() Loop On Error GoTo 0 End Sub


Matrixfunktion für dynamische Arrayformel

Wenn es nicht zu viele Dateien sind, können die auch mit einer Formel, die in einer Zelle steht, ausgegeben werden (ab Excel 365). Dazu dient folgende benutzerdefinierte Funktion (UDF):

Function Suchen_nur_Dateien(strOrdner) Dim strDatei As String, arrTemp() Dim lngArr As LongPtr lngArr = 0 strDatei = Dir(strOrdner & "*.*") Do While strDatei <> "" If strDatei <> "" Then lngArr = lngArr + 1 ReDim Preserve arrTemp(1 To 4, 1 To lngArr) arrTemp(1, lngArr) = strOrdner & strDatei 'Pfad arrTemp(2, lngArr) = FileLen(strOrdner & strDatei) 'Größe arrTemp(3, lngArr) = FileDateTime(strOrdner & strDatei) 'Datum/Zeit arrTemp(4, lngArr) = strDatei 'nur Dateiname End If strDatei = Dir Loop Suchen_nur_Dateien = arrTemp End Function

Wenn in A1 der Ordner (z. B. C:\Eigene Dateien\) steht, kann in eine andere Zelle folgende Formel eingetragen werden:

=MTRANS(Suchen_nur_Dateien(A1))

Damit werden die gefundenen Dateien ab der Zelle mit der Formel gefloatet eingetragen (verschüttet). Auch die Suche mit den Unterordnern lässt sich in dieser Form gestalten - aber Vorsicht, damit die Formel nicht ewig zum Berechnen braucht.

Werte in Zeile 2 eingeben, alle Zeilen mit anderem Wert ausblenden (Worksheet_Change)Makro/Sub/Prozedur

Kategorien: Tabelle ▸ Matrix und Filter/Sortieren

(Tipp 389) Nachricht zum Beitrag an Autor Nach oben

Wenn ich in Zeile 2 Werte eingebe, sollen die Zeilen mit anderen Werten ausgeblendet werden.

Hier werden zwei Varianten vorgestellt, bei denen Werte in mehrere Zellen in einer Zeile eingegeben werden können und diese als Filterkriterium dienen. Die Zellen, in die die Filterkriterien eingegeben werden können, sind hier A2:G2, also die ersten sieben Zellen in Zeile 2. Die Tabelle mit den zu filternden Werten ist darunter von A5:Gx.

In beiden Fällen im VBA-Editor auf die entsprechende Tabelle doppelklicken und den Code dort einfügen.

Ein Kriterium

Im ersten Beispiel richtet sich der Filter nach nur einem Kriterium, also einer Zelle in A2:G2. Wenn also z. B. in B2 etwas eingegeben wird, soll die Tabelle nach dem Eintrag in B2 gefiltert werden - etwaige Eintragungen in anderen Zellen in Zeile 2 werden ignoriert bzw. gelöscht:

Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim lngLZ As LongPtr, intS As Integer, intSAkt As Integer, bolEvent As Boolean If Target.Row <> 2 Or Target.Column > 7 Then Exit Sub lngLZ = Cells(Rows.Count, 1).End(xlUp).Row If ActiveSheet.AutoFilterMode = True Then If ActiveSheet.FilterMode = True Then ActiveSheet.ShowAllData ActiveSheet.Range("A4:G" & lngLZ).AutoFilter End If bolEvent = Application.EnableEvents Application.EnableEvents = False For intS = 1 To 7 If intS <> Target.Column Then Cells(2, intS).ClearContents Next Application.EnableEvents = bolEvent If Target <> "" Then ActiveSheet.Range("A4:G" & lngLZ).AutoFilter Field:=Target.Column, Criteria1:=Range(Target.Address) End Sub

Nach der Eingabe werden zunächst alle Zeilen eingeblendet und der Autofilter ausgeschaltet, falls er gesetzt ist. Anschließend wird ausgeschaltet, dass die Tabelle auf Ereignisse reagiert (da es sonst zu Endlosschleifen kommen könnte) und die Inhalte der anderen Zellen in Zeile 2 werden gelöscht. Danach wird der Autofilter auf der Basis der aktuellen Eingabe gesetzt.


Mehrere Kriterien

In der zweiten Variante sind in Zeile 2 mehrere Einträge möglich und der Autofilter wird auf der Basis dieser Einträge gesetzt. Das Vorgehen entspricht ansonsten der ersten Variante.

Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim lngLZ As LongPtr, intS As Integer If Target.Row <> 2 Or Target.Column > 7 Then Exit Sub lngLZ = Cells(Rows.Count, 1).End(xlUp).Row With ActiveSheet If .AutoFilterMode = True Then If .FilterMode = True Then .ShowAllData .Range("A4:G" & lngLZ).AutoFilter End If For intS = 1 To 7 If .Cells(2, intS) <> "" Then .Range("A4:G" & lngLZ).AutoFilter Field:=intS, Criteria1:=.Cells(2, intS).Value End If Next End With End Sub


Eingabe nur in A2 - ältere Variante mit weniger Funktionalität

Im VBA-Editor auf die Tabelle doppelt klicken, in der es funktionieren soll. Anschließend in das Modul einfügen:

Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Address <> "$A$2" Then Exit Sub ActiveSheet.Range("A4:B" & Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter If Range("A2") <> "" Then ActiveSheet.Range("A4:B" & Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=1, Criteria1:=Range("a2") End If End Sub



Zufallszahlen sortiert in einem bestimmten Bereich generieren (mit Arrayfunktion)Makro/Sub/ProzedurUDF - benutzerdefinierte FunktionArrayfunktion/Matrixfunktion

Kategorien: Tabelle ▸ Zellen und Filter/Sortieren

(Tipp 139) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich zwischen 6 und 15 Zufallszahlen zwischen 1 und 49 generieren? Die Zahlen sollen auf dem Blatt Tab1 in der Zeile 1 ab A1 stehen. Keine Zahl darf sich wiederholen.

Dazu gibt es verschiedene Möglichkeiten.

Direkt in Zellen eintragen

In der ersten Variante erfolgt die Arbeit direkt an den Zellen, weil hier die Funktionen Finden und Sortieren von Excel genutzt werden. Funktionen, die Excel zur Verfügung stellt, sind i. d. R. recht schnell, so dass das in diesem Fall sicher die kürzere und schnellere Variante ist:

Sub Zufall() Dim intWert As Integer, varWieviele, intI As Integer Dim bolVorhanden As Boolean, rngGef As Range varWieviele = InputBox("Wieviele Zahlen sollen erzeugt werden?", "Anzahl", 6) If Not IsNumeric(varWieviele) Then Exit Sub If varWieviele > 15 Then Exit Sub Sheets("Tab1").Range("A1:O1").ClearContents For intI = 1 To varWieviele intWert = Int((49 * Rnd) + 1) If intI = 1 Then Sheets("Tab1").Cells(1, intI) = intWert Else Do bolVorhanden = False Set rngGef = Range(Cells(1, 1), Cells(1, intI - 1)).Find(intWert) If Not rngGef Is Nothing Then bolVorhanden = True intWert = Int((49 * Rnd) + 1) End If Loop While bolVorhanden = True Sheets("Tab1").Cells(1, intI) = intWert End If Next Sheets("Tab1").Range("A1:O1").Sort Key1:=Sheets("Tab1").Range("A1"), Order1:=xlAscending, Orientation:=xlLeftToRight End Sub

Kern des Codes ist eine Schleife, die so lange läuft, wie eine Zufallszahl nicht mehr in den bisherigen Zufallszahlen gefunden wird. Erst dann wird sie als neue Zufallszahl verwendet.

Weiteres Beispiel - Schleife statt Find:

Sub Zufall() Dim intAnzahl As Integer, intMax As Integer, intMin As Integer Dim intWert As Integer, intI As Integer, intN As Integer Dim bolVorhanden As Boolean intAnzahl = 10 intMax = 49 intMin = 1 'alte Zahlen löschen Range(Cells(1, 2), Cells(intAnzahl, 2)).ClearContents Cells(1, 2) = Int((intMax * Rnd) + intMin) '1. Zahl erzeugen For intI = 2 To intAnzahl 'nächste Zahlen erzeugen Do bolVorhanden = False intWert = Int((intMax * Rnd) + intMin) For intN = 1 To intI 'Kontrolle ob schon vorhanden If Cells(intN, 2) = intWert Then bolVorhanden = True Exit For End If Next Loop While bolVorhanden = True Cells(intI, 2) = intWert 'Zahl eintragen Next End Sub


Erst Array, zum Schluss in Zellen

Manchmal kann die Arbeit mit Zellen aber auch von Nachteil sein. Deshalb ist hier der Vollständigkeit halber noch ein Beispiel, in dem Herangehensweisen mit einem Array aufgezeigt werden:

Sub Zufall1() Dim intWert As Integer, intI As Integer, intN As Integer, varWieviele Dim bolVorhanden As Boolean, bolSortiert As Boolean Dim arrZahlen() varWieviele = InputBox("Wieviele Zahlen sollen erzeugt werden?", "Anzahl", 6) If Not IsNumeric(varWieviele) Then Exit Sub If varWieviele > 15 Then Exit Sub ReDim Preserve arrZahlen(varWieviele) For intN = 0 To varWieviele - 1 intWert = Int((49 * Rnd) + 1) If intN = 0 Then arrZahlen(intN) = intWert Else Do bolVorhanden = False For intI = 0 To intN If arrZahlen(intI) = intWert Then bolVorhanden = True intWert = Int((49 * Rnd) + 1) Exit For End If Next Loop While bolVorhanden = True arrZahlen(intN) = intWert End If Next Do bolSortiert = True For intN = 0 To varWieviele - 2 If arrZahlen(intN + 1) < arrZahlen(intN) Then bolSortiert = False intWert = arrZahlen(intN + 1) arrZahlen(intN + 1) = arrZahlen(intN) arrZahlen(intN) = intWert End If Next Loop While bolSortiert = False Sheets("Tab1").Range("A1:O1").ClearContents For intN = 0 To varWieviele - 1 Sheets("Tab1").Cells(1, intN + 1) = arrZahlen(intN) Next End Sub

Das gesamte Erstellen der Zufallszahlen - bis hin zum Sortieren - erfolgt zunächst in einem Array. Auch hier erfolgt die Prüfung auf Doppelungen. Aber es ist zu sehen, dass dafür eine weitere Schleife eingebaut ist. Eine Funktion wie in PHP in_array() wäre da natürlich günstiger.

Auch zum Sortieren wird die innere Schleife so lange durchlaufen, bis im Array das nächste Element nicht kleiner als das gerade durchlaufene Element ist.

Erst ganz zum Schluss wird der fertige Array in die Zellen übernommen.

Die zweite Variante hat den Vorteil, dass sie beliebig - und unabhängig von Zellen - eingesetzt werden kann. Sie kann auch als eigenständige Funktion erstellt werden, die dann den Array mit den Zufallszahlen zurückgibt. So könnte die Funktion von überall aufgerufen werden; in die Zellen würde dann das Ergebnis eingetragen.

Oft stößt man bei Schleifen, die Zellen lesen und schreiben, auf das Problem, dass die Laufzeit enorm steigt. Deshalb sollte zumindest in Betracht gezogen werden, das Ganze mit Arrays zu erledigen und die Zellzugriffe auf ein Minimum zu beschränken.


Dynamische Arrayformel mit Matrixfunktion (ab Excel 365)

Natürlich kann das auch als Matrixfunktion für eine dynamische Arrayformel erstellt werden. Die Funktion erstellt einen Array mit eindeutigen Zahlen. Zum Schluss wird mit der Arrayfunktion SORTIEREN() sortiert und der Array ausgegeben.

Function Zufallszahlen_Eindeutig(ByVal intAnzahl As Integer, ByVal intMin As Integer, ByVal intMax As Integer) Dim intWert As Integer, intI As Integer, intN As Integer Dim bolVorhanden As Boolean, arrZahlen() Application.Volatile ReDim Preserve arrZahlen(1 To intAnzahl) For intN = 1 To intAnzahl intWert = Int((intMax * Rnd) + intMin) If intN = 1 Then arrZahlen(intN) = intWert Else Do bolVorhanden = False For intI = 1 To intN If arrZahlen(intI) = intWert Then bolVorhanden = True intWert = Int((intMax * Rnd) + intMin) Exit For End If Next Loop While bolVorhanden = True arrZahlen(intN) = intWert End If Next Zufallszahlen_Eindeutig = Application.WorksheetFunction.Sort(arrZahlen, 1, 1, 1) End Function

In die Zelle kommt einfach:

=Zufallszahlen_Eindeutig(Anzahl;Minimum;Maximum)

=Zufallszahlen_Eindeutig(5;1;49)

Sollen die Zahlen untereinander erscheinen, kann die Funktion in MTRANS() gesetzt werden:

=MTRANS(Zufallszahlen_Eindeutig(5;1;49))

Die Funktion ZUFALLSMATRIX() klingt danach, als ob sie die Aufgabe auch erfüllen könnte. Jedoch hat sie bei dieser Aufgabenstellung den großen Nachteil, dass sie sehr häufig mehrfach vorhandene Zahlen erzeugt. Das heißt, dass man so oft berechnen lassen müsste, bis jede Zahl eindeutig ist - und das kann etwas dauern.

Apropos eindeutig: Mit der Funktion EINDEUTIG() könnte natürlich geprüft werden, ob eindeutige Zufallszahlen vorliegen. Allerdings ist die Funktion manchmal schneller als die Sortieren-Funktion, so dass letztere Funktion noch rechnet, wenn EINDEUTIG() schon fertig ist. Das führt dann zum bekannten Fehler ÜBERLAUF.