[MS Excel 2003] Snel wegschrijven van data naar binaire file

[Verwijderd]

Ik heb een sheetje met wat data (1000 rijen x 100 kolommen) en wil deze graag via VBA wegschrijven naar een binair bestand (dit ivm latere bewerking door een programma dat dit binaire formaat nodig heeft). Het wegschrijven an sich is geen probleem, maar de snelheid is onwerkbaar. Voor bovenstaande afmetingen is ~2 mins nodig (op een behoorlijk recente computer). Ik gebruik nu de voor de hand liggende methode 'Put':

VBScript:

Dim vals As Variant
vals = Range(Cells(offsetRow, offsetKol), Cells(offsetRow + maxScen - 1, offsetKol + maxJaar)).Value

intFileNum = FreeFile: b = 1

ReDim waardes(1 To 4)
waardes(1) = maxScen: waardes(2) = 0: waardes(3) = maxJaar + 1: waardes(4) = 0
'Bestand met yield curves, op te slaan als binair bestand met extensie ecorts.
On Error GoTo Wegschrijffout
Open Left(ActiveWorkbook.FullName, Len(ActiveWorkbook.FullName) - 4) & ".ecorts" For Binary Access Write As intFileNum
'Schrijf beginwaarden weg:
Put intFileNum, , b
For i = LBound(waardes) To UBound(waardes)
    Put intFileNum, , waardes(i)
Next i
'Schrijf rentedata weg:
For scen = 1 To maxScen
    For jaar = 0 To maxJaar
        If vals(scen, jaar + 1) = "" Then
            Close intFileNum
            Call finalize(False, "Cel (" & Str(offsetRow - 1 + scen) & "," & Str(offsetKol + jaar) & ") is leeg terwijl het de yield bij jaar" & Str(jaar) & " en scenario" & Str(scen) & " zou moeten bevatten.")
            Exit Sub
        End If
        On Error GoTo Conversiefout
        dd = vals(scen, jaar + 1)
        On Error GoTo 0
        Put intFileNum, , dd
    Next jaar
Next scen
Close intFileNum
On Error GoTo 0

Ik zit al een tijdje op internet te zoeken naar een mogelijk om de snelheid te verbeteren, maar ik heb nog niets gevonden. Heeft iemand hier een suggestie?

[onkl]

Geen antwoord, maar wat spookt de routine "vals" uit? Je roept 'm twee keer aan in je loop, dus inefficiënties in die routine kunnen meespelen.
Als testje kan je kijken of je een versnelling krijgt door vals één keer aan te roepen:

Visual Basic:

For scen = 1 To maxScen
    For jaar = 0 To maxJaar
        On Error GoTo Conversiefout
        dd = vals(scen, jaar + 1)
        On Error GoTo 0
        If dd = "" Then
            Close intFileNum
            Call finalize(False, "Cel (" & Str(offsetRow - 1 + scen) & "," & Str(offsetKol + jaar) & ") is leeg terwijl het de yield bij jaar" & Str(jaar) & " en scenario" & Str(scen) & " zou moeten bevatten.")
            Exit Sub
        End If
        Put intFileNum, , dd
    Next jaar
Next scen
Close intFileNum
On Error GoTo 0

Als dit een significante versnelling geeft, zit je probleem in die routine, anders niet.

Ik zou overigens de "Conversiefout" foutafhandeling rondom "vals" in die routine afhandelen, dat lijkt me op het eerste gezicht handiger. (Geen wet van Meden en Perzen overigens, misschien is er beziend e rest van je code reden het zo te doen)

[Verwijderd]

onkl schreef op dinsdag 24 februari 2009 @ 11:53:
Geen antwoord, maar wat spookt de routine "vals" uit? Je roept 'm twee keer aan in je loop, dus inefficiënties in die routine kunnen meespelen.
Als testje kan je kijken of je een versnelling krijgt door vals één keer aan te roepen:

Visual Basic:

For scen = 1 To maxScen
    For jaar = 0 To maxJaar
        On Error GoTo Conversiefout
        dd = vals(scen, jaar + 1)
        On Error GoTo 0
        If dd = "" Then
            Close intFileNum
            Call finalize(False, "Cel (" & Str(offsetRow - 1 + scen) & "," & Str(offsetKol + jaar) & ") is leeg terwijl het de yield bij jaar" & Str(jaar) & " en scenario" & Str(scen) & " zou moeten bevatten.")
            Exit Sub
        End If
        Put intFileNum, , dd
    Next jaar
Next scen
Close intFileNum
On Error GoTo 0

Als dit een significante versnelling geeft, zit je probleem in die routine, anders niet.

Ik zou overigens de "Conversiefout" foutafhandeling rondom "vals" in die routine afhandelen, dat lijkt me op het eerste gezicht handiger. (Geen wet van Meden en Perzen overigens, misschien is er beziend e rest van je code reden het zo te doen)

Dank voor het meedenken alvast. . 'vals' is een array die in de eerste twee regels van de code in mijn post wordt aangemaakt (geen routine dus), daar lijkt de vertraging me niet in zitten. Verder wordt die 'dd' in een ander gedeelte van de code gedeclareerd als een double, ik wil nl. alleen doubles wegschrijven en strings e.d. afvangen, vandaar die 'On Error GoTo Conversiefout'.

[ Voor 3% gewijzigd door Verwijderd op 24-02-2009 12:33 ]

[Verwijderd]

*Schop*. Niemand die een idee heeft?

[onkl]

Grappige methode om een range in een array te vangen. Kende ik nog niet, maar ziet er nuttig uit.

Ik was een beetje aan het kijken naar de verschillende methodes om naar bestanden weg te schrijven en struikelde hierover:
http://www.vb-helper.com/howto_read_write_binary_file.html
waarin er eerst een binary array wordt opgebouwd, die dan in één put in het bestand wordt weggeschreven. Misschien om iedere rij (scenario) die je wegschrijft als één blok weg te zetten en dan die array te overschrijven met de volgende rij. weg te schrijven.

[Bolukan]

Ik vermoed dat de vertraging door vals komt. Je laadt data over in het geheugen, waarom?

En wat doetdeed dd in de code?

Visual Basic:

Dim vals As Range
Set vals = ActiveSheet.Range(Cells(offsetRow, offsetKol), Cells(offsetRow + maxScen - 1, offsetKol + maxJaar))

intFileNum = FreeFile: b = 1

Redim waardes(1 To 4)
waardes(1) = maxScen: waardes(2) = 0: waardes(3) = maxJaar + 1: waardes(4) = 0
'Bestand met yield curves, op te slaan als binair bestand met extensie ecorts.
On Error GoTo Wegschrijffout
Open Left(ActiveWorkbook.FullName, Len(ActiveWorkbook.FullName) - 4) & ".ecorts" For Binary Access Write As intFileNum
'Schrijf beginwaarden weg:
Put intFileNum, , b
For i = LBound(waardes) To UBound(waardes)
    Put intFileNum, , waardes(i)
Next i
'Schrijf rentedata weg:
For scen = 1 To maxScen
    For jaar = 0 To maxJaar
        If IsEmpty(vals.Cells(scen, jaar + 1)) Then
            Close intFileNum
            Call finalize(False, "Cel (" & Str(offsetRow - 1 + scen) & "," & Str(offsetKol + jaar) & ") is leeg terwijl het de yield bij jaar" & Str(jaar) & " en scenario" & Str(scen) & " zou moeten bevatten.")
            Exit Sub
        End If
        On Error GoTo Conversiefout
        On Error GoTo 0
        Put intFileNum, , vals.cells(scen, jaar + 1).value
    Next jaar
Next scen
Close intFileNum
On Error GoTo 0

[Verwijderd]

onkl schreef op woensdag 25 februari 2009 @ 20:15:
Grappige methode om een range in een array te vangen. Kende ik nog niet, maar ziet er nuttig uit.

Ik was een beetje aan het kijken naar de verschillende methodes om naar bestanden weg te schrijven en struikelde hierover:
http://www.vb-helper.com/howto_read_write_binary_file.html
waarin er eerst een binary array wordt opgebouwd, die dan in één put in het bestand wordt weggeschreven. Misschien om iedere rij (scenario) die je wegschrijft als één blok weg te zetten en dan die array te overschrijven met de volgende rij. weg te schrijven.

Ik heb al geprobeerd om helemaal niet te loopen en de tweedimensionale array vals in één keer weg te schrijven. Dat kan, maar maakt nauwelijks iets uit qua snelheid...

[Verwijderd]

Bolukan schreef op woensdag 25 februari 2009 @ 22:50:
Ik vermoed dat de vertraging door vals komt. Je laadt data over in het geheugen, waarom?

En wat doetdeed dd in de code?

Visual Basic:

Dim vals As Range
Set vals = ActiveSheet.Range(Cells(offsetRow, offsetKol), Cells(offsetRow + maxScen - 1, offsetKol + maxJaar))

intFileNum = FreeFile: b = 1

Redim waardes(1 To 4)
waardes(1) = maxScen: waardes(2) = 0: waardes(3) = maxJaar + 1: waardes(4) = 0
'Bestand met yield curves, op te slaan als binair bestand met extensie ecorts.
On Error GoTo Wegschrijffout
Open Left(ActiveWorkbook.FullName, Len(ActiveWorkbook.FullName) - 4) & ".ecorts" For Binary Access Write As intFileNum
'Schrijf beginwaarden weg:
Put intFileNum, , b
For i = LBound(waardes) To UBound(waardes)
    Put intFileNum, , waardes(i)
Next i
'Schrijf rentedata weg:
For scen = 1 To maxScen
    For jaar = 0 To maxJaar
        If IsEmpty(vals.Cells(scen, jaar + 1)) Then
            Close intFileNum
            Call finalize(False, "Cel (" & Str(offsetRow - 1 + scen) & "," & Str(offsetKol + jaar) & ") is leeg terwijl het de yield bij jaar" & Str(jaar) & " en scenario" & Str(scen) & " zou moeten bevatten.")
            Exit Sub
        End If
        On Error GoTo Conversiefout
        On Error GoTo 0
        Put intFileNum, , vals.cells(scen, jaar + 1).value
    Next jaar
Next scen
Close intFileNum
On Error GoTo 0

Maar als je niet eerst alle data in het geheugen laadt zoals in 'vals()' bijv., moet je per cel steeds de data gaan halen vanaf het sheet. Zo had ik het in eerste instantie gedaan, maar dit is (logischerwijs m.i.) alleen nog maar trager.

Verder wordt 'dd' in een eerder stuk van de code als Double gedeclareerd. Ik wil alleen Doubles wegschrijven. Door de weg te schrijven waarde eest gelijk te stellen aan 'dd' wordt er een foutmelding geforceerd als het per ongeluk een string oid zou zijn

[onkl]

Zomaar nog een dwarsstraat: Je schrijft doubles weg naar een binair bestand, terwijl je daar (meen ik) losse bytes naartoe moet schrijven. Nou ben ik niet heel erg thuis in het byte type, maar ik kan me voorstellen dat de put routine daarvan gaat kokhalzen.

[Lustucru]

Het inlezen van een met getallen gevulde matrix van 1000 bij 100 cellen in een array om die vervolgens stuk voor stuk om te zetten naar een double en met put weg te schrijven duurt hier op een bepaald niet snelle celeron laptop 2,2 sec. 40 ms voor het inlezen, 1800 ms v oor het wegschrijven.

Time je routine eens met in het begin t=timer en vervolgens her en der debug.print timer-t. Je doet wat rare dingen met foutafhandeling en je cast nogal wat, maar zo op het eerst egzicht weinig wat 2 min (2mins= 2min of 2sec?) zou verklaren. Wat doet die conversie-errorroutine trouwens en hoe vaak treedt die execptie op? Klopt de aanroep wel, maw de waarden van maxjaar en maxscen?

[Verwijderd]

Lustucru schreef op donderdag 26 februari 2009 @ 14:01:
Het inlezen van een met getallen gevulde matrix van 1000 bij 100 cellen in een array om die vervolgens stuk voor stuk om te zetten naar een double en met put weg te schrijven duurt hier op een bepaald niet snelle celeron laptop 2,2 sec. 40 ms voor het inlezen, 1800 ms v oor het wegschrijven.

Time je routine eens met in het begin t=timer en vervolgens her en der debug.print timer-t. Je doet wat rare dingen met foutafhandeling en je cast nogal wat, maar zo op het eerst egzicht weinig wat 2 min (2mins= 2min of 2sec?) zou verklaren. Wat doet die conversie-errorroutine trouwens en hoe vaak treedt die execptie op? Klopt de aanroep wel, maw de waarden van maxjaar en maxscen?

Die conversie-errorroutine is een check om ervoor te zorgen dat alleen doubles naar het bestand worden geschreven (want dd wordt bovenaan gedeclareerd als double). Normaal gesproken treedt ie helemaal niet op, is enkel een veiligheidsmaatregel. Wat doe ik voor rare dingen met foutafhandeling? Zou goed kunnen, want ik ben de afhandeling zoals in Python gewend.

Het probleem lijkt inmiddels opgelost. Voor de zekerheid heb ik het sheet eens lokaal opgeslagen & output laten produceren, dan is ie wel binnen een paar seconden klaar.Dus het lijkt aan de invloed van het netwerk te liggen...