Live-Forum - Die aktuellen Beiträge
Datum
Titel
08.05.2024 23:37:20
Datenverbindung automatisiert aktualisieren
08.05.2024 21:22:24
In vorhandener Formel Jahreszahl mit einbeziehen
08.05.2024 21:15:37
Koordinaten im XY-Diagramm per Cursor ändern.
08.05.2024 19:10:09
doppelte Zeile gem. mehrerer Bedingungen löschen?
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1908to1912
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1908to1912
1908to1912
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Überlauf

Überlauf
06.12.2022 18:36:25
Thomas
Hi,
ich möchte in einem kleinen Skript fast 36.000 Zellen mit fortlaufenden Nummern füllen, das klappt auch bis Zeile 31199, aber dann bekomme ich einen Laufzeitfehler 6 "Überlauf".
Der Long Datentyp müsste doch eigentlich ausreichen oder was übersehe ich?

Sub AlleNummernKreise()
Application.ScreenUpdating = False
Dim lngPraefix As Long
Dim lngStart As Long
Dim lngEnde As Long
Dim lngZaehler As Integer
lngStart = 1
lngEnde = 999
For lngPraefix = 1 To 35
For lngZaehler = lngStart To lngEnde
tblDaten.Cells(lngZaehler, 1).NumberFormat = "@"
tblDaten.Cells(lngZaehler, 1) = Format(lngPraefix, "000") & Format(lngZaehler, "000")
Next lngZaehler
lngStart = lngEnde + 1
lngEnde = lngStart + 999
Next lngPraefix
Application.ScreenUpdating = True
End Sub
Danke
MFG
Thomas

14
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Überlauf
06.12.2022 18:58:20
Oberschlumpf
Hi
und was ist mit lngZaehler ?
Ciao
AW: Überlauf
06.12.2022 19:06:45
onur
Dim lngZaehler As Integer
AW: Überlauf
06.12.2022 19:07:29
Thomas
Hi,
oh man, den Namen hab ich angepasst aber den Datentyp nicht und das ist mir nicht aufgefallen :-(
Aber jetzt wo das angepasst ist, hab ich wohl noch einen logik Fehler drin, es gibt Präfixe von 001 bis 035 und dann soll es hinter jedem Präfix 3 stellen von 001 bis 999 geben, hätte jetzt als letzte Zahl 035999 erwartet, allerdings bekomme ich 03534999
Wo könnte da mein Fehler liegen?
Danke
AW: Überlauf
06.12.2022 19:13:31
onur
1)For lngPraefix = 1 To 36
2)tblDaten.Cells(lngZaehler, 1) = Format(lngZaehler, "000")
AW: Überlauf
06.12.2022 19:38:10
Thomas
Hi Onur,
wieso 36?
Das Ergebnis ist zumindest noch nicht das was ich gerne hätte, der erste Teil besteht immer aus dem Präfix 001 bis 035 (immer 3 stellen) und der zweite Teil (auch immer drei Stellen) von 001 bis 999,
also
von 001001 bis 001999 und dann weiter von 002001 bis 002999 und so weiter. Am ende sollte dann 035999 dort stehen.
MFG
Anzeige
AW: Überlauf
06.12.2022 19:50:33
Daniel
Hi
dein Logikfehler ist, dass du für die Zeile, die beschrieben werden soll und den hinteren Nummernkreis (1-999) die selbe Variable verwendest.
das musst du aber trennen.
auch solltest du, wenn du so viele Werte hast, diese nicht direkt in die Zellen schreiben, sondern erstmal in ein Array und dann das Array als ganzes ins Tabellenblatt.
das geht wesentlich schneller, weil jede Änderung von Zellwerten in Excel Zeit braucht, deswegen sollte man mit möglichst wenigen Änderungen auskommen und über das Array hast du dann nur eine Wertänderung (wieviele Werte dabei geändert werden, ist egal, solange sie alle gemeinsam geändert werden:

Sub AlleNummernKreise()
Dim lngPräfix As Long
Dim lngSuffix As Long
Dim Zeile As Long
Dim Arr
ReDim Arr(1 To (35# - 1 + 1) * (999 - 1 + 1), 1 To 1)
For lngPräfix = 1 To 35
For lngSuffix = 1 To 999
Zeile = Zeile + 1
Arr(Zeile, 1) = "'" & Format(lngPräfix, "000") & Format(lngSuffix, "000")
Next
Next
Cells(1, 1).Resize(UBound(Arr, 1), 1) = Arr
End Sub
weitere Infos: das Hochkomma kennzeichnet die Werte als Text und erspart die Formatierung mit dem Zahlenformat Text um eine Umwandlung in eine Zahl zu vermeiden.
die etwas aufwendige Berechnung der der Größe des Arrays ist der Tatsache geschuldet, dass deine Nummernkreise nicht vollständig sind, denn es fehlt die 0 für den hinteren Nummernkreis.
Die Berechnung berücksichtigt hier die tatsächliche Anzahl der Werte für unvollständige Nummernkreise, die sich aus "Endwert - Startwert + 1" ergibt.
solltest du tatsächlich volltändige Nummerkreise haben wollen (also mit 0-999 für den hinteren Nummernkreis) könntest du das ganze auch mit einer einfachen Formel berechnen: =Text(Zeile()+1000;"000000")
der Code wäre dann dieser.

Sub AlleNummernKreise()
With Cells(1, 1).Resize(34999)
.NumberFormat = "General"
.FormulaR1C1 = "=Text(Row()+1000,""000000"")"
.Copy
.PasteSpecial xlPasteValues
.NumberFormat = "@"
End With
End Sub
aber wie gesagt, das nur bei vollständigem hinteren Nummernkreis (0-999), sollte hier der Bereich nicht vollständig genutzt werden (1-999), wird die Formel deutlich aufwendiger
Gruß Daniel
Anzeige
AW: Überlauf
07.12.2022 06:12:28
Thomas
Hallo Daniel,
vielen Dank für deine ausführlichen Erklärungen und den Code, damit passiert genau das was wie es sein soll und dank deiner Array Lösung auch noch um ein vielfaches schneller.
Das mit dem 

'
als Textkennzeichnung kannte ich auch noch nicht, gibts noch mehr solcher "Sonderkennzeichen"? bzw. wonach müsste ich googlen?
Wie du ja schon schreibst ist das ReDim des Arrays etwas aufwendiger und dazu hätte ich dann noch eine paar Frage

ReDim Arr(1 To (35# - 1 + 1) * (999 - 1 + 1), 1 To 1)
Was genau macht das "#" bei 

35#
?
Auch die Dimensionierung verstehe ich noch nicht ganz, Wieso 35#-1+1?, genauso das bei 999-1+1?
Ich hätte jetzt gesagt 1to35+999, 1to1 .
Auch hierzu noch eine Frage

Cells(1, 1).Resize(UBound(Arr, 1), 1) = Arr
was genau macht dieser Teil 


Resize(UBound(Arr, 1), 1)
ich verstehe es wird die größe der benötigten Zeile definiert, wozu das obere Ende des Arrays genommen wird, aber was genau macht die letze ,1 noch?
Danke
Anzeige
AW: Überlauf
07.12.2022 08:02:45
Daniel
Hi
Das # verhindert hier den Überlauf.
Intern arbeitet VBA auch mit den Variablenkategorieen Interger, Long, Double.
Wenn du nur Integerzahlen mit +-* verrechnest, erwartet VBA auch ein Integer-Ergebnis.
Ist das Ergebnis größer, dann bekommst du den Überlauffehler.
Mit # kennzeichne ich die Zahl als Long, somit erwartet VBA auch ein Long-Ergebnis
Das ist noch ein Relikt aus der Zeit der 8- und 16-bit-Prozessoren, dort wurden Integerberechnungen schneller durchgeführt als Long- oder Double-Berechnungen.
999+35 macht 1034, und das ist zuwenig.
Ich wollte dir hier zeigen wie die Werte ausgehend von Start- under Endwert berechnent werden müssen, wenn die Nummernkreise nicht vollständig sind, damit das Array exakt die benötigte Größe bekommt. (aber das hatte ich doch beschrieben)
Natürlich kannst du auch direkt die Endwerte einsetzten, aber du musst ja wissen, wie sie zustande kommen.
Gruß Daniel
Anzeige
AW: Überlauf
07.12.2022 08:04:36
Daniel
Bei Cells(), Offset() und auch Resize() beschreibt der erste Parameter die Zeilen und der zweite die Spalten.
Gruß Daniel
weils Spass macht, hier noch
06.12.2022 21:40:13
Daniel
die vollständige Formelbasierte Lösung, für Nummernkreise mit beliebigen Start- und End-Werten.
(für den hinteren Nummernkreis natürlich bis maximal 999 begrenzt, ansonsten müsste man den Multiplikator 1000 in der Formel erhöhen)
also es geht auch sowas wie
vorderer Nummernkreis von 23-37 und hinterer Nummernkreis von 123 bis 456

Sub AlleNummernKreise()
Dim Präfix_Start As Long
Dim Präfix_Ende As Long
Dim Suffix_Start As Long
Dim Suffix_Ende As Long
Dim Präfix_Anzahl As Long
Dim Suffix_Anzahl As Long
Dim Anzahl As Long
Dim FO As String
Präfix_Start = 1
Präfix_Ende = 35
Präfix_Anzahl = Präfix_Ende - Präfix_Start + 1
Suffix_Start = 1
Suffix_Ende = 999
Suffix_Anzahl = Suffix_Ende - Suffix_Start + 1
Anzahl = Präfix_Anzahl * Suffix_Anzahl
FO = "=TEXT((QUOTIENT(ROW(A1)-1,xxx)+yyy)*1000+MOD(ROW(A1)-1,xxx)+zzz,""000000"")"
FO = Replace(FO, "xxx", Suffix_Anzahl)
FO = Replace(FO, "yyy", Präfix_Start)
FO = Replace(FO, "zzz", Suffix_Start)
With Cells(1, 1).Resize(Anzahl)
Range(.Cells, .End(xlDown)).ClearContents
.NumberFormat = "General"
.Formula = FO
.Copy
.PasteSpecial xlPasteValues
.NumberFormat = "@"
End With
End Sub
prinzipiell bevorzuge ich hier Formelbasierte Lösungen, weil sie:
- schnell sind
- wenig programmierwissen erfordern, da sie auf einfachen linearen Abläufen bestehen
- kurze Codes ergeben
- man die Formeln auch unabhängig von VBA direkt in Excel testen und entwickeln kann
- man sich beim Testen im Einzelstepmodus nicht durch ewige Schleifen klicken oder mit Haltepunkten arbeiten muss
- man so auch oft in der Lage ist, vergleichbare Aufgaben auch mal auf die schnelle bearbeiten zu können ohne vorher ein Makro schreiben zu müssen.
Gruß Daniel
Anzeige
AW: weils Spass macht, hier noch
07.12.2022 06:19:10
Thomas
Hi Daniel,
schön das ich dir eine Freude bereiten konnte :-) Wenn ich das so lese, dann sieht das für dich echt leicht aus und dann verstehe ich auch das das Spass macht :-)
Mir macht das Thema zwar insgesamt auch Spass, aber für mich ist das echt noch "Arbeit".
Grundsätzlich verstehe ich deinen Code oben, aber hier kämpf ich noch ein bisschen

FO = "=TEXT((QUOTIENT(ROW(A1)-1,xxx)+yyy)*1000+MOD(ROW(A1)-1,xxx)+zzz,""000000"")"
Wieso nutzt du da xxx,yyy,zzz , könnte man da niciht direkt mit Variablen arbeiten und sich das anschließende Replace sparen? Die Quotient Funktion habe ich allerdings auch noch nie benötigt und mich daher damit auch noch nie beschäftigt.
Mir fällt auch immer wieder auf das mir die "kreativität" manchmal fehlt, sieht man an diesem Beispiel auch, auf diesen Lösungsansatz muss man ja erstmal kommen.
Danke
MFG
Thomas
Anzeige
AW: weils Spass macht, hier noch
07.12.2022 07:49:16
Daniel
Hi
Ja, man könnte bei der Formel die Variablen direkt einsetzen um auf das Replache zu verzichten.
Du musst sie aber mit "Text" & Variable & "Text" verketten.
Mach das mal für die komplette Formel und entscheide dann selbst, was dir besser gefällt, voralkem unter dem Aspekt, dass du schauen musst, ob die fertige Formel dann auch korrekt ist und keine Schreibfehler aufweist.
Quotient ist einfach der Ganzzahlwert einer Division und ersetzt hier das Abrunden
Gruß Daniel
AW: weils Spass macht, hier noch
07.12.2022 10:18:41
Thomas
Hallo Daniel,
danke für die zusätzlichen Erklärungen und die Hilfe.
Zum Code von Daniel
07.12.2022 08:34:33
Daniel
Sehr schöne Variablenbehandlung - das sieht super übersichtlich aus.
QUOTIENT habe ich übrigens noch nie benötigt, da die wichtigsten Funktionen in Excel Zahlen von selbst KÜRZEN (INDEX, ZEILE, SPALTE, ...) und entspr. kürzer werden.
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige