Operazioni su Collection

Hai un problema con Excel?

Nuova discussione

Operazioni su Collection
di Mangusta data: 28/12/2012 19:48:22
Ciao a tutti! sto lavorando un pochino sulle collection! le creo e riesco a validarle ma ho ancora delle difficoltà sulle operazioni da fare con ogni valore delle collection!

in pratica vorrei sommare i valori accanto al cella corrispondente al valore della collection (in C risultato voluto)
A B C
mario 10 10
francesco 2 5
francesco 3
antonio 17 17

    With Sheets("Foglio1").Activate

        For Each cella In rng
            cella = Trim(cella)
        Next
        
        Set elenco = rng
        Set col = New Collection

        On Error Resume Next
        For Each nome In elenco
            col.Add nome.Value, CStr(nome.Value)
        Next

        'per ogni valore della collection creo la lista
        For Each valore In col
             som = som + valore.Offset(0, 1)
            With Sheets("Foglio1")
               valore.Offset(0, 2) = som
            End With
            lista = lista & valore & ","
        Next
        lista = Replace(lista & "@", ",@", "")
    End With

di Vecchio Frac data: 28/12/2012 20:32:03
I casi in cui si rivela l'utilità di una Collection sono invero molto limitati.
Tipicamente io la utilizzo solo con classi oggetto (ad esempio per definire e radunare un insieme di oggetti di tipo persona). Qualche giorno fa ho postato una soluzione che faceva uso di Dictionary, oggetto simile alle Collection ma un poco più flessibile.

cit. " le creo e riesco a validarle ma ho ancora delle difficoltà sulle operazioni da fare con ogni valore delle collection! "
---> le crei, ma "validarle" che significa? in realtà la Collection è concettualmente semplice: una coppia chiave-valore. Quando aggiungi un elemento alla Collection assegnandogli un valore, è tutto. Che difficoltà avresti sulle operazioni con i valori di una collection? nessuna, in teoria: se un elemento ha un valore di tipo stringa, potrai farci le operazioni tipiche delle stringhe; se è un intero, le operazioni sugli interi; e così via.

Le Collection sono variabili in memoria e non puntano ad alcuna cella del foglio con cui non hanno niente a che fare. Sono una componente di VBA, non di Excel, e infatti si trovano pari pari in Word, Access, ecc.
Il ciclo For Each valore del tuo esempio cerca di fare una cosa che non può fare: tratta un valore di un elemento di una Collection come se fosse una cella: l'istruzione
som = som + valore.Offset(0, 1)
non ha senso perchè "valore" non è un oggetto di tipo range (non è una cella e non punta a una cella).
Non ottieni un messaggio di errore perchè (poco saggiamente in questa fase di test) hai impostato una trappola per gli errori senza gestirli (On Error Resume Next indica a Excel di continuare imperterrito ignorando qualsiasi errore di compilazione, quindi ti nasconde i messaggi di errore che possono essere sollevati da un codice come questo).

A essere sinceri non ho capito quale fosse lo scopo del test, dal tuo esempio non mi è chiaro il dato di partenza e il risultato voluto. Se si tratta di cercare un valore in colonna B e memorizzare i corrispondenti valori in colonna C per ottenerne una somma, ci sono altre soluzioni più performanti; se lo scopo è didattico, cioè capire come si può utilizzare una Collection, allora bisogna studiare meglio l'esempio :)
Nelle tue intenzioni, volevi creare una Collection che ad ogni nominativo associasse un valore? ripensa meglio a come scrivere le istruzioni in For Each nome in elenco.
Recuperare i valori scritti in celle del foglio? uhm... la Collection non punta di per sè a celle del foglio... è una cosa separata... forse devo memorizzare - in qualche modo separandoli dal nome - questi valori ^_^
di Mangusta (utente non iscritto) data: 28/12/2012 20:55:51
didattico nel senso che ogni scusa è buona per consolidare le mie conoscenze.
ho due fogli uno pulito dove ad ogni nominativo corrisponde un valore
esempio:

Mangusta 10

nell'altro foglio ho

Mangusta 5
Mangusta 3
Mangusta 2
risultato è sempre 10 quindi è giusto
il confronto che devo fare deve considerare questo differenza tra i fogli e riportare ok accando al nominativo Mangusta!

Come affronteresti il problema?
ordinando i nominativi (sort)
sommando i valori con nomitativo uguale (for each)
infine fare il confronto (find)
?

di Vecchio Frac data: 28/12/2012 21:59:42
Va bene consolidare le conoscenze, ma ogni problema dovrebbe anche essere affrontato con la soluzione più efficiente. Poi, per carità: tutto si può fare, se è per imparare :)

In questo caso lascerei stare le Collection (ripeto: non servono davvero quasi mai).
Supponendo che in foglio1 ci sia una serie di nomi non ripetuti:
pippo ----- 10
topolino --- 8
pluto ------ 5
mangusta - 10

E in foglio2 un elenco disordinato di nomi-valori:
pippo ----- 1
pippo ----- 1
mangusta - 3
topolino --- 5
mangusta - 6
pluto ----- 2
mangusta - 1

L'obiettivo è scorrere l'elenco in foglio1, sommare i corrispondenti valori di foglio2 e se coincidono marcare la cella adiacente al nome di foglio1 con "ok".
Perchè non usare SOMMA.SE?
La sua sintassi è semplice: SOMMA.SE(intervallo, criterio, intervallo_valori_da_sommare)
Unitamente a un For Each sull'elenco di foglio1...
Prova a leggere questo pezzo di codice e vedi se può aiutarti nel tuo progetto.

Option Explicit

Sub check()
Dim cella As Range, valore_foglio1 As Integer, sum_valori_foglio2 As Integer, evaluate_string As String

    For Each cella In Foglio1.[A1:A10]
        
        If cella = "" Then Exit For
    
        valore_foglio1 = cella.Offset(, 1)
        
        evaluate_string = "SUMIF(foglio2!A1:A10, " & Chr(34) & cella & Chr(34) & ", foglio2!B1:B10)"
        
        sum_valori_foglio2 = Evaluate(evaluate_string)
        
        If valore_foglio1 = sum_valori_foglio2 Then cella.Offset(, 2) = "coincide!"
    
    Next
    
End Sub

di Mangusta (utente non iscritto) data: 29/12/2012 10:50:03
Bhe sai che sorpresa il tuo codice funziona!! me lo studio e se ho dei dubbi so che sei a disposizione.

tornando alla questione delle collection riflettendo ho capito il mio errore di logica ma non capisco allora il senso del codice funzionante di harry per la questione delle differenze su 3 condizioni.
mi interesa capire la logica del codice (visto che volevo ispirarmi a questo frammento di codice)

Sub Diff()
    Dim ur As Integer, ur2 As Integer
    Dim rng As Range, dest As Range, rng2 As Range, valore As Range
    Dim stringhe() As Variant, i As Integer
    Dim v As Variant, col As Collection
    Dim som As Double
    Application.ScreenUpdating = False

    With ActiveSheet
        ur = Cells(Rows.Count, 1).End(xlUp).Row
        Set rng = Range("e2:e" & ur)

        ReDim stringhe(ur)

        For Each dest In rng
            stringhe(i) = Trim(dest.Offset(0, -3)) & Trim(dest) & Trim(dest.Offset(0, 7))
            dest.Offset(0, 21) = stringhe(i)
            i = i + 1
        Next

        If .AutoFilterMode = True Then
            .AutoFilter.Range.AutoFilter
        End If

        Set col = New Collection
        On Error Resume Next
        For Each dest In Range("z2:z" & ur)
            col.Add dest.Value, CStr(dest.Value)
        Next

        For Each v In col
            Columns("Z").AutoFilter Field:=1, Criteria1:=v

            For Each dest In rng.SpecialCells(xlCellTypeVisible)
                If dest.Offset(0, 15).Interior.ColorIndex = 6 Then
                    som = som + dest.Offset(0, 12)
                End If
            Next

            For Each dest In rng.SpecialCells(xlCellTypeVisible)
                If dest.Offset(0, 15).Interior.ColorIndex = 6 Then
                    dest.Offset(0, 14).NumberFormat = "0.00"
                    dest.Offset(0, 14) = som
                End If

            Next
            som = 0
            .ShowAllData
        Next
        .AutoFilter.Range.AutoFilter
        Columns("Z").ClearContents
    End With

di Mangusta (utente non iscritto) data: 29/12/2012 11:07:25
non capisco il senso del criterio e perchè usi Chr(34) e non di nuovo "

" & Chr(34) & cella & Chr(34) & "
di Vecchio Frac data: 29/12/2012 12:44:58
Prima di studiare con calma il tuo post precedente, rispondo a questo:
cit. non capisco il senso del criterio e perchè usi Chr(34) e non di nuovo " & Chr(34) & cella & Chr(34) & "

Uso Chr(34) solo per questione di gusto personale. Corrisponde al codice SCII delle virgolette ed equivale in VBA a scrivere """ cioè una tripla virgoletta per indicarne una, il che è brutto a vedersi, a leggersi e a capirsi.

Il senso del criterio è semplice, in pratica SUMIF è la traduzione di SOMMA.SE e semplicemente faccio cercare nel range A1:A10 il valore di ogni cella, sommando poi i valori che corrispondono a cella ma in colonna B.
di Mangusta (utente non iscritto) data: 29/12/2012 13:03:37
quindi il criterio cerca il valore di ogni cella si scritta in maniera meno elegante è cosi:
" & """ & cella & """ & "

è appunto questo che non capivo! il senso del codice (il perchè viene scritto cosi) che indica come criterio il valore di ogni cella.

se per dire per ciascuna è facile = for each
per dire ogni cella quel frammento di codice non mi sembra cosi intuitivo
di Vecchio Frac data: 29/12/2012 13:54:23
Il punto è che bisogna scrivere nella cella la formula via VBA come la scriveresti direttamente in Excel.
In Excel scriveresti
=SOMMA.SE(foglio1!A1:A10; "pippo"; foglio2!B1:B10)

Ma per passare a Evaluate la stringa da valutare le virgolette semplici indicherebbero solo la fine di un pezzo di stringa, quindi bisogna dire al codice che vuoi proprio inserire il carattere virgolette dentro la stringa stessa. Il risultato si ottiene o raddoppiando le virgolette per ogni virgoletta da inserire oppure (soluzione più elegante) utilizzando il corrispondente codice ASCII (34).
Inoltre è da ricordare che la formula da passare a Evaluate va scritta in inglese:
=SUMIF(foglio1!A1:A10, "pippo", foglio2!B1:B10)
di Mangusta (utente non iscritto) data: 29/12/2012 16:26:22
ADESSO è chiaro!!! (non sapevo tra l'altro che bisognava passare per evaluta per assegnare il risulatato di una funzione ad una variabile)

grazie gentilissimo
di Vecchio Frac data: 29/12/2012 20:47:03
cit. "non sapevo tra l'altro che bisognava passare per evaluta per assegnare il risultato di una funzione ad una variabile"
---> Non è propriamente così. Puoi assegnare una funzione anche direttamente:
a = [SUM(A1:A10)]

Il trucchetto con Evaluate serve per utilizzare una funzione del foglio di lavoro e calcolarne il risultato... funziona come WorksheetFunction.

Le parentesi quadre sono una scorciatoia per Evaluate, ma non puoi passare delle variabili, cosa che invece puoi fare con quest'ultimo perchè costruisce una stringa.

[1+1] restituisce 2
equivale a
Evaluate("1+1")
ma se hai delle variabili non puoi fare:
[a+b] perchè Excel cerca di interpretare a e b come nomi definiti; invece puoi fare così:
Evaluate(a & "+" & b).
di Vecchio Frac data: 30/12/2012 15:38:56
cit. " mi interesa capire la logica del codice (visto che volevo ispirarmi a questo frammento di codice)"

Nel codice che hai mostrato sopra in realtà la Collection è quasi inutile ^_^
Le celle Excel sono già delle "collezioni" di dati, sulle quali è possibile ciclare.
In teoria, ma anche in pratica, quasi tutte le variabili che si usano scrivendo codice VBA in Excel si possono omettere riferendosi a celle di Excel.

Il pezzo di codice seguente:
Set col = New Collection
On Error Resume Next
For Each dest In Range("z2:z" & ur)
col.Add dest.Value, CStr(dest.Value)
Next

For Each v In col
Columns("Z").AutoFilter Field:=1, Criteria1:=v
...

mette in "col" (variabile di tipo Collection) il range che va da Z2 a Z...(fine area dati) e poi scorre ogni elemento della collection per eseguire un filtro automatico. Si poteva saltare l'assegnazione a col e saltare direttamente a:
For Each v In Range("z2:z" & ur)
Columns("Z").AutoFilter Field:=1, Criteria1:=v

Vuoi Approfondire?