Hilfe bei Stichprobengenerator - diverse vba Probleme

[BigMike]

Hallo,
ich versuche mich an einem Stichprobengenerator. Nur habe ich ein paar Probleme, die ich einfach mangels vba-Expertise nicht gelöst bekomme:

a) Performance ist teilweise schlecht (Berechnungen dauern lange)
b) Auf manchen Seiten erzeuge ich einen Button per vba und benenne ihn um - das erzeugt dann häufig einen Laufzeitfehler ; drücke ich den Button nochmal klappt es wie vorgesehen...warum auch immer

c) für mich das Wichtigste:
Die Ergebnisse einer vorgelagerten Pilotstichprobe werden im Tabellenblatt "Stichprobenauswahl_Pilot" gesammelt.
Die Ergebnisse einer ggfs. notwendigen Nachziehung weiterer Prüffälle im Tabellenblatt "Prüfergebnis_Unterstichprobe"

Wie kann ich die beiden Tabellen mit dynamischer Zeilenanzahl hintereiander kopieren?

Dabei wäre es auch wichtig, dass einmal die Stichprobenergebnisse aufgelistet werden, die als "Oberschichtfall" gelten und andererseits die Fälle, die als "Stichprobenfall" gelten.

Möglicherweise geht das auch mit einer Art SVERWEIS?

Könnt ihr mir da helfen?

Vielen Dank vorab! LG

[Fennek]

Hallo,

ich werde Dir helfen, aber

- erkläre bitte in Worten das Konzept der Stichprobenziehung
- die in der Datei enthaltenen Makros sind zu komplex für meinen Sicherheitscheck, ich habe die Datei als xlsx ohne Makros gespeichert und werde nur damit weiterarbeiten
- der Begriff "Stichprobengenerator" ist negativ belegt, sollte es nicht "Stichprobeziehung heißen?

mfg

##
als Ansatz kommen infrage

- zufällige Auswahl
- nach sortieren n-tes Element auswählen
##

[snb]

1. verzichte immer auf verbundene Zellen
2. fang jedes Arbeitsblatt in Zelle A1 an
3. Verzichte auf 'select' end 'Activate' in VBA
4. kreiere ActiveX-controls in design mode (eigenschaft invisible); mach sie .visible in realtime
5. die Code bezieht nur auf 'wie alles aussieht', nich auf Stichproben; was ist wichtiger ?

Code:

With Range("A1:E11").Borders
        .LineStyle = 1
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With

reicht für alle

[BigMike]

Hallo,

ich werde Dir helfen, aber

- erkläre bitte in Worten das Konzept der Stichprobenziehung
- die in der Datei enthaltenen Makros sind zu komplex für meinen Sicherheitscheck, ich habe die Datei als xlsx ohne Makros gespeichert und werde nur damit weiterarbeiten
- der Begriff "Stichprobengenerator" ist negativ belegt, sollte es nicht "Stichprobeziehung heißen?

mfg

##
als Ansatz kommen infrage

- zufällige Auswahl
- nach sortieren n-tes Element auswählen
##

Das ist wirklich nett, vielen Dank - ich war eigentlich Willens, es irgendwie selber hinzubekommen, aber das übersteigt leider mein Codeschnipsel-Zusammenfüg-Fähigkeiten.

Das Konzept der Stichprobenziehung:
Das Besondere ist hier eine Ziehung nach dem sog. Monetary Unit Sampling. Da gewisse Parameter aber oft unbekannt sind, die man dafür braucht, ist es eine zweiteilige Ziehung - eine mit einer Pilotstichprobe, die mindestens 30 Prüffälle umfasst und dann ggfs. eine weitere Nachziehung (die tatsächliche Größe wird nach den Ergebnissen der Pilotstichprobe festgelegt).
Daher habe ich das Ganze so konzipiert, dass man von einer Tabelle zur nächsten durchläuft. Die Werte werden möglichst automatisch verarbeitet/weiter übertragen und bedingen nur noch wenige händische Eingriffe.
Ich weiß jetzt nicht, ob das dir weiterhilft...


Zu den Makros:
Ich befürchte die 4 Makros sind von Relevanz. Aber keine Sorge, ich wäre nicht in der Lage (selbst wenn ich wollte) Makrovoiren zu implementieren.
Ein Makro (das auf einem Knopfdruck liegt) ist z.B. auch die Basis, aus der ein weiterer Commandbutton (OLBD.Objekt oder so ähnlich) auf der Folgeseite erzeugt wird. Da erhalte ich beim ersten mal immer einen Laufzeitfehler, beim zweiten Klicken klappts kurioserweise dann.

Stichprobenziehung / Stichprobengenerator:
Die negative Belegung des Begriffs war mir nicht bewusst. Ich dachte mir, irgendwie muss man das Werkzeug ja benennen...insofern Stichprobengenerator...aber ich hänge nicht an dem Begriff...Stichprobenziehung trifft es genauso

LG

[Fennek]

Hm, bei dem Schlüsselwort "Stichprobe" denke ich zuerst an ein anderes Fachgebiet.

Ich kann die Berechnungen zwar ziemlich nachvollziehen, aber das reicht nicht für die Beantwortung der Frage.

Sollen die Stichproben aus den Sheets "Stichprobenauswahl_Pilot" und "Prüfergebnis_Unterstichprobe" zusammen in das Sheet "Zusammenführung_Prüfergebnisse" kopiert werden?

Warum ist im Sheet "Grundgesamtheit" der Block "C6:G12" nicht ausgefüllt?

Kannst Du die Frage direkt nach einem VBA-Code stellen?


PS:eine Analyse deines VBA-Codes dauert zu lange und ein einfaches Ausführen verstößt gegen meine selbstdefinierten Sicherheitsstandards

Also, ich kenne nur die Arbeitsblätter inkl. Formeln

[Fennek]

Hallo,

aus Interesse habe ich mit Python den VBA-Code extrahiert. Bis auf die letzte "Sub" sind alles nur Formatierungen????

mfg

----------------

oledump.py -s 3 -v Entwurf_Substichprobengenerator_clean.xlsm

Streams -s 3 bis -s 15

[snb]

mit Python den VBA-Code extrahiert.

Geht einfacher: im VBEditor, Modul1 selektieren  /  rechtsclick /  Export File /  save

[BigMike]

Hm, bei dem Schlüsselwort "Stichprobe" denke ich zuerst an ein anderes Fachgebiet.

Ich kann die Berechnungen zwar ziemlich nachvollziehen, aber das reicht nicht für die Beantwortung der Frage.

Sollen die Stichproben aus den Sheets "Stichprobenauswahl_Pilot" und "Prüfergebnis_Unterstichprobe" zusammen in das Sheet "Zusammenführung_Prüfergebnisse" kopiert werden?

Warum ist im Sheet "Grundgesamtheit" der Block "C6:G12" nicht ausgefüllt?

Kannst Du die Frage direkt nach einem VBA-Code stellen?


PS:eine Analyse deines VBA-Codes dauert zu lange und ein einfaches Ausführen verstößt gegen meine selbstdefinierten Sicherheitsstandards

Also, ich kenne nur die Arbeitsblätter inkl. Formeln

Das "Fachgebiet" dürfte regulär im Bereich Wirtschaftsprüfer / Finanzkontrolle liegen. Das sogenannte Monetary Unit Sampling soll insbesondere wertmäßig hohe Einzelpositionen beim Auslosen bevorzugen. Das macht z.B. bei einer Stichprobenauswahl aus einem Wust an Rechnungen Sinn. Möglicherweise aber auch in anderen Bereichen. Ist zumindest eine unter vielen wissenschaftlich-statistischen Methoden (in meinem Fall die bevorzugte Methode).

so...nochmal nachgedacht...Die Goldrandlösung wäre:
a)
Alle Zeilen aus der (dynamischen) Tabelle "Pilotstichprobe" die in Spalte "P" als "Stichprobenfall" gekennzeichnet sind in Tabelle "Zusammenfühurng_Prüfergebnisse" kopieren
Letzte Zeile ermitteln
Alle Zeilen aus der (dynamischen) Tabelle "Unterstichprobe" die in Spalte "W" als "Stichprobenfall" gekennzeichnet sind in Tabelle "Zusammenfühurng_Prüfergebnisse" direkt dahinter kopieren
b)
Alle Zeilen aus der (dynamischen) Tabelle "Pilotstichprobe" die in Spalte "P" als "Oberschichtfall" gekennzeichnet sind in Tabelle "Zusammenfühurng_Prüfergebnisse_Oberschicht"* kopieren
Letzte Zeile ermitteln
Alle Zeilen aus der (dynamischen) Tabelle "Unterstichprobe" die in Spalte "W" als "Oberschichtfall" gekennzeichnet sind in Tabelle "Zusammenfühurng_Prüfergebnisse_Oberschicht" direkt dahinter kopieren

*die Tabelle gibts im Beispieldokument noch nicht, müsste dann noch hinzugefügt werden.

Bevor es missverdständlich ist: Unter dynamischer Tabelle verstehe ich eine, bei der die Anzahl der Zeilen nicht feststeht...die ergebit sich ja auch erst aus der Anzahl der zu prüfenden Fälle...daher kann die Anzahl der Zeilen immer variieren. (Hoffe das ist verständlicher)

Ab dann würde ich glaube ich weiterkommen...


C6:G12 sind normalerweise befüllt (da steht drin, welcher Prüffall es ist und eine Vorgangsnummer, etc. - ansonsten nebensächlich).

---

Hallo,

aus Interesse habe ich mit Python den VBA-Code extrahiert. Bis auf die letzte "Sub" sind alles nur Formatierungen????

mfg

----------------

oledump.py -s 3 -v Entwurf_Substichprobengenerator_clean.xlsm

Streams -s 3 bis -s 15

Prima, dank dir.

Ja es sind Formatierungen und es werden hier und da eine Formel eingefügt, die ich mit dem Makrorekorder aufgenommen habe, mit einer Ausnahme:

Es wird noch ein Button in das folgende Dokument eingefügt:

'Button einfügen
    ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
        , DisplayAsIcon:=False, Left:=800, Top:=50, Width:=200, Height:= _
        35.25).Select

und dann umbenannt:

'Button umbenennen

ActiveSheet.OLEObjects("CommandButton1").Object.Caption = "Zusammenführung der Prüfergebnisse"

...aber das führt meiner Annahme nach zu diesem Laufzeitfehler...

[Fennek]

Hallo,

so ist die Kommunikation einfacher. Prüfe diesen Code, das neue Sheet "Neu_Oberschicht" mußte anders benannt werden:

Code:

Sub F_en()
Dim rng As Range

With Sheets("Pilotstichprobe")
    Set rng = .Range("C22:P22")
    Set rng = Range(rng, rng.End(xlDown))
    With rng
        .AutoFilter 14, "Stichprobenfall"
        .Copy Sheets("Zusammenführung_Prüfergebnisse").Range("A1")
        .AutoFilter
        
        .AutoFilter 14, "Oberschichtfall"
        .Copy Sheets("Neu_Oberschicht").Range("A1")
        .AutoFilter
    End With
End With

With Sheets("Unterstichprobe")
    Set rng = .Range("C22:W22")
    Set rng = Range(rng, rng.End(xlDown))
    With rng
        .AutoFilter 21, "Stichprobenfall"
        .Offset(1).Copy Sheets("Zusammenführung_Prüfergebnisse").Cells(Rows.Count, 1).End(xlUp).Offset(1)
        .AutoFilter
        
        .AutoFilter 21, "Oberschichtfall"
        .Offset(1).Copy Sheets("Neu_Oberschicht").Cells(Rows.Count, 1).End(xlUp).Offset(1)
        .AutoFilter
    End With
End With
End Sub

mfg

-------------------

@snb

Diese universitär-ähnlichen Virenjägen (SANS.edu) haben täglich mit vielen verdächtigen Office-Dokumenten zu tun. Auch wenn der Python-Programmierer MVP ist, ein Ansatz mit Excel VBA-Code auszulesen ist zu einfach und auf Seminaren schlecht verkäuflich. Deshalb Python, das auch unter Linux eingesetzt werden kann.

Gestern hat ein anderer aus dieser Gruppen einen Powershell-Code zur Prüfung auf malicious Office-files veröffentlicht, allerdings ohne VBA zu deaktivieren. (Hm...)

[BigMike]

Also vielen Dank schon mal für deine Mühen! Leider habe ich das Ganze noch nicht zum Laufen gebracht.

Beim Code habe ich jetzt nur die umbenannten Tabellennamen verändert:

Private Sub CommandButton1_Click()

Dim rng As Range

With Sheets("Pilotstichprobe")
    Set rng = .Range("C22:P22")
    Set rng = Range(rng, rng.End(xlDown))
    With rng
        .AutoFilter 14, "Stichprobenfall"
        .Copy Sheets("Stichprobenfälle_Gesamt").Range("A1")
        .AutoFilter
       
        .AutoFilter 14, "Oberschichtfall"
        .Copy Sheets("Oberschichtfälle_Gesamt").Range("A1")
        .AutoFilter
    End With
End With

With Sheets("Unterstichprobe")
    Set rng = .Range("C22:W22")
    Set rng = Range(rng, rng.End(xlDown))
    With rng
        .AutoFilter 21, "Stichprobenfall"
        .Offset(1).Copy Sheets("Stichprobenfälle_Gesamt").Cells(Rows.Count, 1).End(xlUp).Offset(1)
        .AutoFilter
       
        .AutoFilter 21, "Oberschichtfall"
        .Offset(1).Copy Sheets("Oberschichtfälle_Gesamt").Cells(Rows.Count, 1).End(xlUp).Offset(1)
        .AutoFilter
    End With
End With
End Sub



Beim Auslösen des Buttons bekomme ich dann aber eine Fehlermeldung:
"Laufzeitfehler 1004"Die Methode range für das Objekt_worksheet ist fehlgeschlagen"

Was ist falsch gelaufen?

Das Ergebnis in meinem von dir überarbeiteten Exceldokument ist genau das, was ich erreichen wollte.

Bin übrigens immer wieder baff auf welche Wege man mit vba zum Ziel kommen kann...auf die Idee den Autofilter zu nutzen, wäre ich nie gekommen.