Formel in VBA statt in Excel?

[Spike87]

Hallo,

ich habe ein Auswertungsprogramm für eine Tabelle geschrieben. Diese spiegelt eine Ordnerstruktur mit Dokumenten wieder.

Eine Auswertung wäre, alle Dokumente mit ungerader Versionsnummer herauszufinden. Diese werden, wenn gefunden, mit einem Kommentar versehen wer das Dokument zuletzt bearbeitet hat und zudem werden sowohl der Ordner in dem das Dokument online liegt, als auch das Dokument selber direkt über einen Hyperlink versehen.

Das ganze funktioniert auch schon sehr gut, hat aber bei 50.000 schon eine Bemerkenswerte Ladezeit.

Ich glaube man kann über eine andere Herangehensweise, anstatt wie ich über Hilfstabellen in Excel selbst, die Auswertung stark beschleunigen. Ähnlich wie in dem Prozess des Hyperlink unterlegens. Ich habe einmal eine Beispieldatei erstellt, hier noch einmal die Formel um die es grundlegend geht:

Code:

' Einfügen der Berechnungstabelle aus den Ausgangsdaten von Wrongversion
Worksheets("WrongVersion").Select
    Range("D8").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(OR(LEN(Tabelle1!R[-6]C[19])>3,MOD(Tabelle1!R[-6]C[19],1)>0),Tabelle1!R[-6]C[16]&""_""&Tabelle1!R[-6]C[19],0),Tabelle1!R[-6]C[16]&""_""&Tabelle1!R[-6]C[19])"
    Selection.AutoFill Destination:=Range("D8: D" & LaengeGes + 8), Type:=xlFillDefault

'''' Die Formel hier drüber ist hauptsächlich gemeint''''

' Aus den gefundenen Dokumenten Hyperlinks machen
For count = 0 To LaengeGes
  With Worksheets("WrongVersion")
     If .Cells(8 + count, 4).Value <> 0 Then
        'Hyperlink Dokument
        .Hyperlinks.Add Anchor:=.Cells(8 + count, 5), _
           Address:="https://testurl/" & Worksheets("Tabelle1").Cells(2 + count, 19), _
           TextToDisplay:=.Cells(8 + count, 4).Value
           
        'Hyperlink Ordner
        .Hyperlinks.Add Anchor:=.Cells(8 + count, 6), _
           Address:="https://testurl/" & Worksheets("Tabelle1").Cells(2 + count, 8), _
           TextToDisplay:=Worksheets("Tabelle1").Cells(2 + count, 9).Value           
     End If
  End With
Next count

' Hier wird angegeben, wer das Dokument zuletzt bearbeitet hat
For count = 0 To LaengeGes
  With Worksheets("WrongVersion")
     If .Cells(8 + count, 4).Value <> 0 Then
     With .Cells(8 + count, 5)
                .AddComment
                .Comment.Text Text:="Last modified by:" & Worksheets("Tabelle1").Cells(2 + count, 25)
                End With
     End If
  End With
Next count

' Sortieren der gefundenen Daten
    ActiveWorkbook.Worksheets("WrongVersion").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("WrongVersion").Sort.SortFields.Add Key:=Range("E8" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("WrongVersion").Sort
        .SetRange Range("E8:F" & LaengeGes)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

' Anzahl gefundener falscher Versionen
LengthVersion = Sheets("WrongVersion").Range("I7").Value

' Beschriftung Ausgabeseite Wrong Version
Range("Tabelle4!B" & rngOverall1 + 2).Value = "List of Documents with wrong Versions"

'    Range("Tabelle4!B" & rngOverall1 + 2).Select
'    Selection.Font.Underline = xlUnderlineStyleSingle
    
    Range("Tabelle4!B" & rngOverall1 + 3).Value = "Number of the 'Wrong Version Documents'=" & LengthVersion
    Range("Tabelle4!F" & rngOverall1 + 3).Value = "A total of:" & LaengeGes & " Documents"
'    Range("Tabelle4!B" & rngOverall1 + 3).Select
'    Selection.Font.Underline = xlUnderlineStyleSingle
    
' Gefundene Daten aus Berechnungsblatt kopieren und in Ergebnisblatt ausgeben

'Ordner einfügen
    Sheets("WrongVersion").Select
    Range("F8:F" & LengthVersion + 7).Select
    Selection.Copy
    Sheets("Tabelle4").Select
    Range("B" & rngOverall1 + 5).Select
    ActiveSheet.Paste

'Dokument einfügen
    Sheets("WrongVersion").Select
    Range("E8:E" & LengthVersion + 7).Select
    Selection.Copy
    Sheets("Tabelle4").Select
    Range("F" & rngOverall1 + 5).Select
    ActiveSheet.Paste

rngOverall1 = rngOverall1 + LengthVersion + 7
rngOverall2 = rngOverall2 + LengthVersion + 7

Ich freue mich über jede Hilfe,

Liebe Grüße!

[snb]

Fang an:

- VBA code tags zu verwenden
- alle "Select' und 'Activate' in der Code zu löschen

[Spike87]

Danke, dass mit den Code-Tags kannte ich noch gar nicht.

Ja das Select und Activate Zeit rauben wusste ich bereits, leider hakt es ganz genau an diesem Aspekt. Ich weiß nicht, wie ich die Formel

Code:

Worksheets("WrongVersion").Select
   Range("D8").Select
   ActiveCell.FormulaR1C1 = _
       "=IFERROR(IF(OR(LEN(Tabelle1!R[-6]C[19])>3,MOD(Tabelle1!R[-6]C[19],1)>0),Tabelle1!R[-6]C[16]&""_""&Tabelle1!R[-6]C[19],0),Tabelle1!R[-6]C[16]&""_""&Tabelle1!R[-6]C[19])"
   Selection.AutoFill Destination:=Range("D8: D" & LaengeGes + 8), Type:=xlFillDefault

In andere Form umgesetzt bekomme, daher benutze ich Select und Activate und darauf bezieht sich auch hauptsächlich meine Fragestellung.

Liebe Grüße

[BoskoBiati]

Hallo,

da fangen wir doch mal vorne an:

Code:

With Worksheets("WrongVersion").Range("D8")
    .FormulaR1C1 = _
       "=IFERROR(IF(OR(LEN(Tabelle1!R[-6]C[19])>3,MOD(Tabelle1!R[-6]C[19],1)>0),Tabelle1!R[-6]C[16]&""_""&Tabelle1!R[-6]C[19],0),Tabelle1!R[-6]C[16]&""_""&Tabelle1!R[-6]C[19])"
   .AutoFill Destination:=Range("D8:D" & LaengeGes + 8), Type:=xlFillDefault
end with

ungetestet!

[Spike87]

Vielen, vielen Dank!

Auch wenn das ganze ziemlich offensichtlich war und ich Teilweise die "With" Syntax schon benutze, fehlte mir einfach mal eine genaue Anwendung dieser auf eine meiner Formeln.

Das Projekt ist einfach ein Moloch mit zig Anforderungen und in fast allen Bereichen muss ich mich komplett neu reinlesen, ohne Fachliteratur und daher nutze ich oft Umwege mit meinem begrenzten Wissen und übersehe dabei grundlegende Mechaniken. Es tut mir auch selbst leid, dass ich Teilweise dumme oder unvollständige Fragen stelle.


lg

[Kuwer]

Hallo,

da Edgar "ungetestet" schrieb, sei ihm verziehen.  :19:

Wenn schon mit With ... End With dann sollte man sich auch konsequent darauf (in diesem Fall die Zelle D8) beziehen:

Code:

With Worksheets("WrongVersion").Range("D8")
 .FormulaR1C1 = _
      "=IFERROR(IF(OR(LEN(Tabelle1!R[-6]C[19])>3,MOD(Tabelle1!R[-6]C[19],1)>0),Tabelle1!R[-6]C[16]&""_""&Tabelle1!R[-6]C[19],0),Tabelle1!R[-6]C[16]&""_""&Tabelle1!R[-6]C[19])"
 .AutoFill Destination:=.Resize(LaengeGes), Type:=xlFillDefault
End With

'oder auch gleich so:

Code:

Worksheets("WrongVersion").Range("D8").Resize(LaengeGes).FormulaR1C1 = _
 "=IFERROR(IF(OR(LEN(Tabelle1!R[-6]C[19])>3,MOD(Tabelle1!R[-6]C[19],1)>0),Tabelle1!R[-6]C[16]&""_""&Tabelle1!R[-6]C[19],0),Tabelle1!R[-6]C[16]&""_""&Tabelle1!R[-6]C[19])"

Codes ebenfalls ungetestet! ;)

Gruß Uwe

[BoskoBiati]

Hallo Uwe,

danke, wieder dazugelernt!

@Spike,

wenn die Anforderungen so umfangreich sind, dann würde ich erst mal mit Formeln arbeiten. Mit einer intelligenten Tabelle werden die Formeln sowieso weitergeführt. Es ist auch leichter an den Formeln zu basteln, wenn man die Bezüge direkt sieht!

[Spike87]

Super, vielen Dank für den neuen Imput!

@ Biati
Mit Anforderungen meinte ich verschiedenste Aspekte, die komplette Bedienbarkeit (Und Versuch es so schwer wie möglich zu machen, dass der User an die Daten/Code kommt über ein nicht minimierbares UserForm etc wovon aber das meiste umgesetzt ist), die Verknüpfung mit der Online Quelle und noch einiges mehr.
 Das Gute ist, dass das Grundkonstrukt tatsächlich schon funktioniert, es dauert nur halt noch sehr lange und ist teilweise ziemlich unsauber, was ins Gewicht fällt da der komplette Code und jegliche Funktion nach GAMP5 validiert werden muss. Daher versuche ich jetzt den Flickenteppich den ich bisher habe konstruiert habe zu überarbeiten und verbessern.

Da ich aber schon weiß welche Formeln wo eingefügt werden und ich die Formeln auch noch mal mit Ihren Bezügen, geschrieben in Excel direkt noch woanders gespeichert habe, denke ich, dass ich nun auch gut die FOrmeln in VBA selbst nutzen kann, ohne Ihre Bezüge nicht mehr zu verstehen. Oder habe ich deinen Ansatz komplett falsch verstanden?

Mit einer intelligenten Tabelle werden die Formeln sowieso weitergeführt. Es ist auch leichter an den Formeln zu basteln, wenn man die Bezüge direkt sieht!

Beziehst du dich damit darauf, dass ich Pivottabellen nutzen sollte? Sind Pivottabellen grundsätzlich "normalen" Formeln und Auswertungen über VBA vorzuziehen? Ich habe da leider nur ein rudimentäres Verständnis von.

@ Kuwer
Ich werde deinen Code Morgen direkt mal testen, vielen Dank für deine Müh =)

[BoskoBiati]

Hallo,

intelligente Tabellen haben nichts mit Pivot zu tun. Ist in der Rubrik Start zu finden, Punkt "Als Tabelle formatieren"

[Käpt'n Blaubär]

Hallo,

(Und Versuch es so schwer wie möglich zu machen, dass der User an die Daten/Code kommt über ein nicht minimierbares UserForm etc wovon aber das meiste umgesetzt ist), die Verknüpfung mit der Online Quelle und noch einiges mehr.

... nun frage ich mich unter Stirnrunzeln und Haare raufen, was eine nicht minimierbare Userform mit mehr Sicherheit zu tun haben könnte.