[VBA excel]concatenazione stringhe e occupazione memoria

[swarm]

Salve a tutti

mi rivolgo a voi per un problema nella creazione di una macro per excel

Questa macro si occupa, a partire da un file .csv anche molto grande (oltre 50.000 righe) di accorpare le righe che hanno alcuni campi uguali.

In pratica, siano x e y due righe con i campi A e B (celle): se il valore del campo A è uguale, quello che fa la macro in pratica è accodare il valore dei campi B, e quindi alla fine resterà solo la riga x, con il campo A inalterato e il campo B = Bx + By

l'accodamento in origine era fatto con l'operatore & (Bx = Bx & By), ma mi sono accorto che l'occupazione di memoria era molto elevata

un articoo della microsoft (http://support.microsoft.com/kb/170964/en-us) suggeriva di utilizzare il metodo Mid$

ho fatto così

   
            l1 = Len(ActiveSheet.Cells(i - 1, colScenario).Value)
            l2 = Len(ActiveSheet.Cells(i, colScenario).Value)
            ' allocazione buffer per l'accodamento
            strTemp = Space$(l1 + l2 + 1)
            ' accodamento stringa campo scenario
            Mid$(strTemp, 1) = ActiveSheet.Cells(i - 1, colScenario).Value
            Mid$(strTemp, l1 + 1) = ":"
            Mid$(strTemp, l1 + 2) = ActiveSheet.Cells(i, colScenario).Value
            ' aggiornamento valore cella
            ActiveSheet.Cells(i - 1, colScenario).Value = strTemp

ma ho visto che il problema della memoria resta, causato dall'ultima assegnazione

se invece volessi utilizzare nella funzione Mid$ come primo parametro direttamente ActiveSheet.Cells(i - 1, colScenario).Value, mi dà errore di copilazione. richiesta Variabile

come posso fare per utilizzare la cella direttamente in MId$ ?

oppure non serve a niente? ( a livello di occupaz di memoria)

Grazie a tutti

[swarm]

nessuno ha mai letto quell'articolo microsoft??

ma smettiamola di leggere Novella2000

[icecube_HU]

Originariamente inviato da swarm

Salve a tutti

mi rivolgo a voi per un problema nella creazione di una macro per excel

Questa macro si occupa, a partire da un file .csv anche molto grande (oltre 50.000 righe) di accorpare le righe che hanno alcuni campi uguali.

In pratica, siano x e y due righe con i campi A e B (celle): se il valore del campo A è uguale, quello che fa la macro in pratica è accodare il valore dei campi B, e quindi alla fine resterà solo la riga x, con il campo A inalterato e il campo B = Bx + By

l'accodamento in origine era fatto con l'operatore & (Bx = Bx & By), ma mi sono accorto che l'occupazione di memoria era molto elevata

un articoo della microsoft (http://support.microsoft.com/kb/170964/en-us) suggeriva di utilizzare il metodo Mid$

ho fatto così

   
            l1 = Len(ActiveSheet.Cells(i - 1, colScenario).Value)
            l2 = Len(ActiveSheet.Cells(i, colScenario).Value)
            ' allocazione buffer per l'accodamento
            strTemp = Space$(l1 + l2 + 1)
            ' accodamento stringa campo scenario
            Mid$(strTemp, 1) = ActiveSheet.Cells(i - 1, colScenario).Value
            Mid$(strTemp, l1 + 1) = ":"
            Mid$(strTemp, l1 + 2) = ActiveSheet.Cells(i, colScenario).Value
            ' aggiornamento valore cella
            ActiveSheet.Cells(i - 1, colScenario).Value = strTemp

ma ho visto che il problema della memoria resta, causato dall'ultima assegnazione

se invece volessi utilizzare nella funzione Mid$ come primo parametro direttamente ActiveSheet.Cells(i - 1, colScenario).Value, mi dà errore di copilazione. richiesta Variabile

come posso fare per utilizzare la cella direttamente in MId$ ?

oppure non serve a niente? ( a livello di occupaz di memoria)

Grazie a tutti

Ciao !
Ho letto l'articolo della MS, conosco la tecnica, ma direi che NON si applica al tuo caso ! Li' Infatti si parla di manipolazione di grandi stringhe (ciascuna "pesante" svariati Kb), ma tu hai necessita' di manipolare molte stringhe, ma di dimensioni "normali" (stanno in una cella di Excel).

Comunque quella tecnica puo' servire ad aumentare la velocita' di elaborazione e a ridurre la "garbage collection", non a ridurre l'occupazione di memoria !

Quindi non farti troppi problemi e continua tranquillamente ad utilizzare l'operatore '&' per la concatenazione delle stringhe.

[swarm]

Originariamente inviato da icecube_HU

Ciao !
Ho letto l'articolo della MS, conosco la tecnica, ma direi che NON si applica al tuo caso ! Li' Infatti si parla di manipolazione di grandi stringhe (ciascuna "pesante" svariati Kb), ma tu hai necessita' di manipolare molte stringhe, ma di dimensioni "normali" (stanno in una cella di Excel).

Comunque quella tecnica puo' servire ad aumentare la velocita' di elaborazione e a ridurre la "garbage collection", non a ridurre l'occupazione di memoria !

Quindi non farti troppi problemi e continua tranquillamente ad utilizzare l'operatore '&' per la concatenazione delle stringhe.

ciao

grazie per la risposta

il fatto è che volevo cmq cercare di ottimizzare la memoria,
il codice che ho postato sopra sta all'interno di un ciclo che convolge tutto lo sheet (che per me può arrivare anche a 65536 righe)
non riesco cmq a capire perchè, quando effettuo l'assegnazione della stringa construita da me

ActiveSheet.Cells(i - 1, colScenario).Value = strTemp

viene allocata memoria, ma quando cancello la riga dopo così

Rows(i).Delete

la memoria non viene rilasciata, e quindi aumenta a dismisura....

ho provato anche a disabilitare la modalità tagliaCopia o a svuotare ogni volta manualmente la clipboard, ma nulla...

mah

[icecube_HU]

Originariamente inviato da swarm

non riesco cmq a capire perchè, quando effettuo l'assegnazione della stringa construita da me

ActiveSheet.Cells(i - 1, colScenario).Value = strTemp

viene allocata memoria, ma quando cancello la riga dopo così

Rows(i).Delete

la memoria non viene rilasciata, e quindi aumenta a dismisura....

C'e' un po' di confusione.....

Se utilizzi una stringa temporanea strTemp, la memoria che occupa (pochi bytes, pero' ) verra' rilasciata quando la variabile termina il suo "ciclo vitale" (uscita dalla procedura) o quando fai strTemp="".

Non ha nessun effetto la cancellazione di una riga dello sheet, che c'entra !??

E soprattutto: se usi sempre la stessa variabile temporanea, l'occupazione di memoria NON PUO' "crescere a dismisura" !

Eventualmente posta uno stralcio del codice VBA... Ciao !

[swarm]

ciao

ecco il codice incriminato

Public Sub Compatta(ByVal numRighe As Long, sht As Worksheet)
    'Dichiarazione variabili locali
    Dim i, j, l1, l2 As Long
    Dim strTemp As String
    ' Compattazione sheet: ciclo a partire dall'ultima riga fino alla terza:
    With sht
        For j = 4 To numRighe
            i = numRighe - j + 4
            ' se (la colonna descrizione è uguale a quella della riga precedente) e se(coincidono le celle NID_PI)
            If ((.Cells(i, colDescr).Value = .Cells(i - 1, colDescr).Value) And (.Cells(i, colNID).Value = .Cells(i - 1, colNID).Value)) Then
                ' aggiungo alla cella Scenario della precedente riga ",<cella Scenario della riga attuale>"
                ' Alfonso: aggiunto controllo superamento massimo numero caratteri nella cella
                ' solo in caso la somma dei due campi non superi i 32768 caratteri procedo all'accodamento
                If (Len(.Cells(i, colScenario).Value) + Len(.Cells(i - 1, colScenario).Value)) < NMAXCAR Then
                    ' Alfonso: utilizzo un buffer e le funzioni MId$ per l'accodamento dei valori "Scenario"
                    l1 = Len(.Cells(i - 1, colScenario).Value)
                    l2 = Len(.Cells(i, colScenario).Value)
                    ' allocazione buffer per l'accodamento
                    strTemp = Space$(l1 + l2 + 1)
                    ' accodamento stringa campo scenario
                    Mid$(strTemp, 1) = .Cells(i - 1, colScenario).Value
                    Mid$(strTemp, l1 + 1) = ":"
                    Mid$(strTemp, l1 + 2) = .Cells(i, colScenario).Value
                    ' aggiornamento valore cella
                    .Cells(i - 1, colScenario).Value = strTemp
                End If
                ' elimino la riga
                Rows(i).Delete
            End If
        Next j
    End With
End Sub

in pratica, per un file .csv da 50000 righe, excel arriva ad allocare anche 200MB...

[icecube_HU]

Ciao !

Quanto esponi mi lascia perplesso...

Tu non memorizzi in vettori VBA tutte le righe del file, come pensavo, ma le elabori direttamente dalle celle excel, quindi non puo' essere la tua procedura che alloca tutta quella memoria.
Non e' che i 200 Mb sono relativi all'occupazione di memoria complessivamente utilizzata dal programma Excel + i tuoi dati ???

E gia' che ci sei, perche non posti 2 o 3 righe di dati presenti nel file csv, come esempio...

Inoltre: avevi gia' provato a scrivere la routine senza l'accorgimento del Mid$ ? Ti dava problemi di tempi di elaborazione ??

[swarm]

Originariamente inviato da icecube_HU

Ciao !

Quanto esponi mi lascia perplesso...

Tu non memorizzi in vettori VBA tutte le righe del file, come pensavo, ma le elabori direttamente dalle celle excel, quindi non puo' essere la tua procedura che alloca tutta quella memoria.
Non e' che i 200 Mb sono relativi all'occupazione di memoria complessivamente utilizzata dal programma Excel + i tuoi dati ???

E gia' che ci sei, perche non posti 2 o 3 righe di dati presenti nel file csv, come esempio...

Inoltre: avevi gia' provato a scrivere la routine senza l'accorgimento del Mid$ ? Ti dava problemi di tempi di elaborazione ??

rispondo in ordine alle tue domande

1) i 200mb sono escusivamente allocati da excel (tenevo d'occhio il task manager durante l'esecuzione della macro)
2) i dati presenti nella cella che viene concatenata sono semplicemente il nme di un file (esclusa l'estensione); questi file hanno nel nome una descrizione testuale + una progressiva: ho quindi, prima di effettuare il concatenamento, utilizzato solo la progressiva (numero di 5 cifre): il testo presente nelle caselle è quindi numero

all'inizio le caselle contengono 00001, poi 00002 ecc,...
dopo il concatenamento la casella sarò 00001:00002:00003 ecc...
3)all'inizio la routine era fatta con l'operatore &, cioè facevo

.Cells(i - 1, colScenario).Value = .Cells(i - 1, colScenario).Value & .Cells(i, colScenario).

ciao

[swarm]

non so se può servirti, ma se facevo girare la macro sidabilitando l'istruzione

.Cells(i - 1, colScenario).Value = strTemp

l'occupazione di memoria era in pratica nulla

[icecube_HU]

Mah.... io semplificherei con:

Public Sub Compatta2(ByVal numRighe As Long, sht As Worksheet)
    Dim i As Long
    With sht
        For i = numRighe To 4 Step -1
            ' se (la colonna descrizione e' uguale a quella della riga precedente) e se(coincidono le celle NID_PI)
            If ((.Cells(i, colDescr).Value = .Cells(i - 1, colDescr).Value) And (.Cells(i, colNID).Value = .Cells(i - 1, colNID).Value)) Then
                ' aggiungo alla cella Scenario della precedente riga ",<cella Scenario della riga attuale>"
               .Cells(i - 1, colScenario) = .Cells(i - 1, colScenario) & ":" & .Cells(i, colScenario)
            Rows(i).Delete
            End If
        Next
    End With
End Sub

Prova in po'..... Ciao !

N.B.:
Occhio, se dichiari Dim a, b, c, d As Long
solo L'ULTIMA variabile e' Long, le altre vengono implicitamente dichiarate come Variant, con conseguente spreco di memoria e minor velocita'...
Questo in generale, non e' certo il problema nel caso in questione.

[swarm]

Originariamente inviato da icecube_HU

Mah.... io semplificherei con:

Public Sub Compatta2(ByVal numRighe As Long, sht As Worksheet)
    Dim i As Long
    With sht
        For i = numRighe To 4 Step -1
            ' se (la colonna descrizione e' uguale a quella della riga precedente) e se(coincidono le celle NID_PI)
            If ((.Cells(i, colDescr).Value = .Cells(i - 1, colDescr).Value) And (.Cells(i, colNID).Value = .Cells(i - 1, colNID).Value)) Then
                ' aggiungo alla cella Scenario della precedente riga ",<cella Scenario della riga attuale>"
               .Cells(i - 1, colScenario) = .Cells(i - 1, colScenario) & ":" & .Cells(i, colScenario)
            Rows(i).Delete
            End If
        Next
    End With
End Sub

Prova in po'..... Ciao !

N.B.:
Occhio, se dichiari Dim a, b, c, d As Long
solo L'ULTIMA variabile e' Long, le altre vengono implicitamente dichiarate come Variant, con conseguente spreco di memoria e minor velocita'...
Questo in generale, non e' certo il problema nel caso in questione.

hai postato il codice esattamente come lo facevo prima...

cmq il tuo consiglio sulla diciharazione dei dati mi è molto utile, non lo sapevo!

cmq fa nulla, ormai mi sono arreso

sei stato gentilisssssssssssimo

byezzzz