benutzerdefinierte Funktion (VBA)

SIH007 · 15. August 2019

Hallo, NG,

ich habe eine Mappe mit etwas schwierigen Berechnungen, nachstehend 2 verschiedene Anforderungen.

Der Versatz gegenüber den Typen ist immer gleich, d. h. mit einer Berechnung ist die zu summierende Zahl vielleicht 1 Zeile unter der Typ-Bezeichnung, bei der nächsten Berechnung 3 Zeilen, etc. - es müssen alle Mit SUMMEWENNS kriege ich das nicht gelöst, daher habe ich mir 2 Funktionen programmiert:

Function NachTypSummieren(rng As Range, strType As String, intZeilenVersatz As Integer) As Long

'rng ==> der zu summierende Bereich, strType ==> der Typ nach dem summiert werden soll

'intZeilenVersatz ==> wie viele Zeilen unterhalb des Suchwerts ist der zu summierende Wert verschoben?

Dim rngZelle As Range, rngType As Range

For Each rngZelle In rng.Cells

On Error GoTo 0

If rngZelle.Value = strType Then

Set rngZelle = rngZelle.Offset(intZeilenVersatz)

'geht vom Suchbegriff aus um den Zeilenversatz nach unten, um die Zahl einzusammeln

NachTypSummieren = NachTypSummieren + rngZelle.Value

End If

Next rngZelle

End Function

Function NachTypSummieren2(rng As Range, strTyp As Range, intZeilenVersatz As Integer) As Double

'intZeilenVersatz ==> wie viele Zeilen unterhalb des Suchwerts ist der zu summierende Wert verschoben?

Dim rngZelle As Range, intZeileTyp, rngTyp As Range

For Each rngZelle In rng.Cells

intZeileTyp = rngZelle.Row

Set rngTyp = Range("B" & intZeileTyp)

If rngTyp.Value = strOrt Then

Set rngZelle = rngZelle.Offset(intZeilenVersatz)

'geht vom Suchbegriff aus um den Zeilenversatz nach unten, um die Zahl einzusammeln

NachTypSummieren2 = NachTypSummieren2 + rngZelle.Value

End If

Next rngZelle

End Function

Die funktionieren im Prinzip ganz gut, nur drücken die Schleifen offenbar auf die Performance. Hat jemand eine Idee, wie ich sie (bei gleicher Funktionalität ;-) ) rauskriegen kann?

Gruß

Susanne

Andreas Killer

Im Fall der o. g. Funktion packt mir Excel regelmäßig die Fehlermeldung '#Wert" in die Zellen. Ich kann das Problem beheben, indem ich die jeweiligen Zeilen markiere, mit F2 editiere und mit STRG + Enter bestätige.

Wie ich schon sagte: Es müssen alle Zellen als Argument übergeben werden und das bei allen UDF die Du benutzt.

Du darfst niemals innerhalb der UDF direkt auf Zellen zugreifen, ala X=Range("A1")

Nur so kann das Berechnungsmoduls von Excel sicherstellen das alle Zellen ein korrektes Ergebnis haben.

Andernfalls gibt es genau das Problem das Du beschrieben hast....

Datei \ Optionen \ Formeln:

Ich gehe mal davon aus das es keine Zirkelbezüge gibt und Arbeitsmappenberechnung auf Automatisch steht und keine Iterative Berechnung aktiviert ist.

Andreas.

Susanne_007

Vielen Dank und einen schönen Abend ;-)

Andreas Killer

Was mich überrascht: do... loop ist doch auch eine Schleife, ist das per se performanter?

Nein, die Schleifen an sich sind schnell, egal welche.

Das Problem ist der vielfache Zugriff auf das Objekt, wenn man eine ganze Spalte durchläuft sind das 1 Million mal das Auslesen einer Zelle und der Vergleich... dazu kommt ggf. Datentypen konvertieren... das dauert.

Range.Find muss intern das gleiche machen, allerdings läuft das um ein Vielfaches schneller (weil es auf die Datenstrukturen direkt zugreifen kann) und es durchsucht nur den benutzten Bereich... optimierter Algorithmus.

Andreas.

SIH007

Hallo, Andreas,

herzlichen Dank für deine schnelle Antwort, werde ich gleich ausprobieren. Aber ich verstehe folgendes nicht:

In NachTypSummieren2 begehst Du jedoch einen Fehler mitSet rngTyp = Range("B" & intZeileTyp)weil Du auf Daten zurückgreifst die nicht übergeben wurden.
Das habe ich doch direkt in der Zeile davor definiert (in der ungeliebten Schleife, von daher dürfte das doch kein Problem geben)

Gruß

Susanne

SIH007

Man lernt nie aus ;-) - danke für die Infos. Habe gerade die Functions ausprobiert, ich habe zwar keine Zeitmessung gemacht, aber scheint gut zu laufen.

Meine Mappe hat diverse Macken, z. B. dass regelmäßig/unregelmäßig Funktionen keine Ergebnisse zeigten. D. h. die Funktion war da und wenn sie berechnet wurde, gab es auch ein Ergebnis, aber in unregelmäßigen Abständen waren diese Zellen alle leer. Das betraf
übrigens nicht nur meine UDFs, sondern auch eine Formel, die ich leider auch 4 SVERWEISEN und noch ein bißchen anderer Rechnerei zusammenbasteln mußte - die SVWERWEISE waren nicht geschachtelt, sondern die Ergebnisse wurden multipliziert.

Was mich überrascht: do... loop ist doch auch eine Schleife, ist das per se performanter?

Gruß

Susanne

Hi,

willkommen im Windows Forum!

benutzerdefinierte Funktion (VBA) - Similar Threads - benutzerdefinierte Funktion VBA	Forum	Datum
Excel Funktion mit oder ohne VBA Excel Funktion mit oder ohne VBA: Hallo zusammen, kann mir jemand bitte bei einer Funktion helfen.Ich möchte wissen, wieviel Projekte man bei gleichen Rahmenbedingungen in einem Zeitraum Jahr abschließen kannParameter:Der...	Microsoft Office	6. März 2024
BILD Funktion & VBA BILD Funktion & VBA: Ich verwende den folgenden VBA-Code in Excel 365, um eine Formel in eine Zelle einzufügen:CellsLabelRow, LabelCol.Formula2 = "=bild" & ws.Name & "!" & ArtDataAddress & ",,0"Das bereitet mir eine...	Microsoft Office	15. Juni 2023
Warum funktioniert bei Word die Funktion 'Benutzerdefinierte Seitenränder' nicht? Warum funktioniert bei Word die Funktion 'Benutzerdefinierte Seitenränder' nicht?: Hallo,seit ein paar Wochen funktioniert bei Word die Einstellung 'benutzerdefinierte Seitenränder' nicht mehr. Es lässt sich kein Fenster öffnen. Zudem kann ich eine Word-Datei nicht als PDF-Datei...	Microsoft Office	29. Januar 2023
[VBA] Ausführung einer Funktion/eines Makros unterbinden [VBA] Ausführung einer Funktion/eines Makros unterbinden: Hallo. Sitze gerade vor einem für mich nicht lösbaren Problem. Wenn man eine XLSM normal öffnet, wird im Kopf von Excel die Sicherheitswarnung eingeblendet. Der Code einer Funktion/eines...	Microsoft Office	11. Januar 2020
Verwendung von VBA Funktionen in Office365 auf OSX Verwendung von VBA Funktionen in Office365 auf OSX: Hallo Zusammen Ich benutze ja nicht das erste mal VBA in Excel, aber ich komm einfach nicht weiter. Vielleicht steh ich auch irgendwo selbst auf dem Schlauch. Neues Excel Sheet angelegt,...	Microsoft Office	28. September 2017
Excel 2013: VBA-Funktion wird nicht mehr erkannt Excel 2013: VBA-Funktion wird nicht mehr erkannt: Hi, System: Windows 8 64-bit, Office 2013 Prof. 64-bit. Ich habe in VBA u.a. eine Funktion mit dem Namen bin_threshold() formuliert. Sie arbeitete bis dato einwandfrei. Im Zuge von...	Microsoft Office	18. Dezember 2016
Excel VBA Funktion mit mehrdimensionalen ParamArray Excel VBA Funktion mit mehrdimensionalen ParamArray: Hallo allerseits, ich will eine Funktion in VBA-Excel programmieren die am Ende eine ParamArray besitzt die jeweils zwei Werte pro Datensatz beinhaltet. Bis jetzt sieht meine Funktion so...	Microsoft Office	8. Dezember 2016

benutzerdefinierte Funktion (VBA)