Richiesta consiglio su utilizzo Excel/access o altro

[karug64]

Salve a tutti.
Ho praticamente terminato il mio programmino per la compilazione automatica di schemi di cruciverba.
Ho scritto tutto in vba di Excel gestendo con vari fogli l'elenco delle parole, lo schema ecc.

ora che il programma è fatto e funziona, avrei l'esigenza di "velocizzarlo". Alcune operazioni su fogli con oltre 60.000 parole sono molto dispendiose in termini di tempo ....

avrei pensato quindi di utilizzare un database access per estrarre i dati con delle query parametrizzate (immagino siano più veloci di lunghi cicli for o altro ....

la domanda:
secondo voi conviene "trasportare " il codice vba (con le opportune eventuali modifiche) su access e gestire tutto da lì o lavorare su Excel e gestire il database access da Excel?
inoltre, se passo ad access, esiste un controllo griglia (sganciato dal database) da usare come base del cruciverba?

pensate che eventualmente con vb2010 express otterrei dei vantaggi (altri linguaggi non li conosco quindi non posso utilizzarli)

grazie a chiunque voglia darmi qualche consiglio.

[Anthony47]

Alcune operazioni su fogli con oltre 60.000 parole sono molto dispendiose in termini di tempo ....

In attesa di ricevere suggerimenti sul confronto tra Access /Excel /VB (che io non sono in grdo di dare), se illustri qualche operazione particolarmente lenta sono sicuro che riusciremo a farla girare piu' velocemente.

[karug64]

Ok, Anthony.

Per indicarti qualche parte "lenta" del programma devo descriverti per grandi linee cosa e come fa il programmino.
Premetto che il programma attualmente funziona "quasi" perfettamente con schemi di parole crociate a "schema libero" e non simmetrici e svolge il suo compito con vocabolari dedicati. Mi spiego meglio:
creo uno schema vuoto ricopiato da una qualsiasi rivista del settore e del foglio "Dizionario" inserisco tutte le parole contenute nello schema. Così facendo il programma riesce a "sistemare" tutte le parole completando la compilazione del cruciverba.

Attualmente la logica che ho dato per svolgere questo compito è, a GROSSE linee, questa:
Inizio:
cerco_coordinate_libere
estraggo_parole_x_lunghezza e non ancora scritte ("N") = tua routine
cerco_parola_random_tra_quelle estratte = tua routine
scrivo_parola
se (controllo_incroci_possibili) = vero allora
aggiorno_parola = "S"
torno ad inizio
altrimenti
aggiorno_parola = "P" (un provata per evitare di riutilizzarla)
cancello_parola
goto inizio

Ovviamente ci sono tanti altri passaggi e controlli ma questo può bastare per farti capire una delle problematiche.

Ho trovato in rete un dizionario di circa 61.000 termini (anche se da integrare).

Durante i tentativi di ricerca della parola, come vedi, contrassegno le parole Provate che non vanno nello schema. Alla fine, quando trovo quella giusta, riporto tutti valori "P" ad "N" in modo tale da poterle eventualmente riestrarre per un altra parola.
Questa cosa, per esempio la faccio con un ciclo for da 1 a 65000 ..... veramente dispendioso

Inoltre, cosa non da poco, non so proprio come fare per "numerare" le caselle. Lo schema è fatto da celle (ovviamente). dopo averlo composto (inserito le caselle nere), dovrei fare in modo di scrivere i numeri (verticali/orizzontali) piccolini in alto a sinistra delle celle ed indelebili, per evitare che scrivendoci le lettere sopra vengano cancellate. E' possibile ?

Per il momento grazie (ma solo per il momento ......)

[karug64]

Un'altro grosso imbuto è questo:

Codice: Seleziona tutto

    For x = 1 To Sheets(foglio_dizionario).Range("A" & ultrigo).End(xlUp).row
        If UCase(Sheets(foglio_dizionario).Cells(x, 1).Value) Like UCase(pre) = True Then
            pre_controllo = True
            Exit Function
        End If
    Next

la variabile "pre" contiene la composizione ipotetica della parola che si forma con un incrocio.
Es. schema di 10 righe alla 5 casella ce n'è una nera

Scrivo prima parola in 2 orizzontale
1 orizzontale = ""
2 orizzontale = CAPO

controllo incroci e creo PRE
nel primo caso PRE = ?C??

ora devo cercare tutte le parole di 4 lettere che abbiano alla seconda lettere la "C".
Per fare questo utilizzo la routine sopra indicata.

Ora, se la trovo subito cerco in poche righe (relativamente ....), ma se devo andare in profondita' ..... mi tocca spolverare, in teoria, 65000 righe (se la parola è l'ultima) !!!! Per ogni incrocio e in un cruciverba sono proprio tanti .... quindi il tempo è improponibile.

Da qui l'idea di usare access o comunque un database in cui, immagino, ci sia la possibilità di fare una select specifica che estragga immediatamente tutte le parole che soddisfino le due condizioni: lunghezza e like

Già velocizzare questi due punti penso che porterebbe ad una drastica riduzione dei tempi di compilazione ......
Grazie

[Anthony47]

Quando vuoi velocizzare una macro devi evitare di accedere in continuazione ai dati sul foglio Excel.
Quindi potresti usare la logica della Sub FiltraBL di qualche discussione fa per mettere in una matrice tutte le parole di N lettere, dove N e' il numero di lettere che stai cercando.
Per flessibilita' la trasformiamo in una funzione con questo codice:

Codice: Seleziona tutto

Function FFiltraBL(ByVal myLen As Long, ByRef myWdList As Range) As Variant
Dim WArr, OArr(), I As Long, myOInd As Long, listCol As String
'Equivalente a FiltraBL, ma Function
'Va richiamata passandogli la lunghezza delle parole e Foglio/indirizzi del dizionario
'RESTITUISCE un array bidmensionale:
'     il primo valore e' la riga del dizionario dove si trova la parola
'     il secondo valore e' la parola
'
'Esempio:
'   myVAR = FFiltraBL(4, Sheets("Foglio3").Range("D1:D5000"))
'
WArr = myWdList.Value
ReDim OArr(1 To 2, LBound(WArr, 1) To UBound(WArr, 1))
myOInd = LBound(OArr)
For I = LBound(WArr, 1) To UBound(WArr, 1)
    If Len(WArr(I, 1)) = myLen Then         '<<< La lunghezza cercata
        OArr(1, myOInd) = WArr(I, 1)
        OArr(2, myOInd) = I
        myOInd = myOInd + 1
    End If
Next I
ReDim Preserve OArr(1 To 2, LBound(WArr, 1) To myOInd)
FFiltraBL = OArr
End Function


Poi al posto di
For x = 1 To Sheets(foglio_dizionario).Range("A" & ultrigo).End(xlUp).row
If UCase(Sheets(foglio_dizionario).Cells(x, 1).Value) Like UCase(pre) = True Then
pre_controllo = True
Exit Function
End If
Next

Userai invece:

Codice: Seleziona tutto

Dim peppArr                  'In testa, tra le dichiarazioni
'
peppArr = FFiltraBL(4, Sheets("Foglio3").Range("D1:D5000"))      '<<< Vedi testo
    For x = LBound(peppArr) to UBound(peppArr)
        If peppArr(2, i) Like pre Then
            pre_controllo = True
            Exit Function
        End If
    Next


La riga marcata <<< e' solo di esempio; vanno usati il nome del foglio contenente l'elenco parole e il relativo range.
NB: IN REALTA' QUESTO CODICE (copiato dal tuo messaggio) E' MONCO; immagino che l'obiettivo sia estrarre dall'array una parola, che e' quella che si trova in peppArr(2, i).
In peppArr(1,i) si trova invece la riga del dizionario da cui e' stata estratta quella parola, nel caso che volessi marcare quella parola come "Gia' usata"

Spero siano spunti utili, ma spero che qualcuno si intrometta per dare qualche buon consiglio riguardo all'eventuale uso degli altri strumenti.

Ciao

[karug64]

Buonasera Anthony.

Ho provato il tuo codice ma ho qualche problema:
1) mi accorgo che se chiamo la funzione con:

Codice: Seleziona tutto

peppArr = FFiltraBL(Len(pre), Sheets(foglio_dizionario).Range("A1:A" & ultrigo))

dove len(pre) = 11

l'array Oarr contiene 9 parole, ma questo ciclo:

Codice: Seleziona tutto

For x = LBound(peppArr) To UBound(peppArr)

gira sempre da 1 a 2 in quanto UBound(peppArr) è uguale a 2 (ma dovrebbe essere = 9).

2) per futuri utilizzi faccio presente che la variabile "i" del ciclo sottostante deve essere "x"

Codice: Seleziona tutto

For x = LBound(peppArr) To UBound(peppArr)
        If UCase(peppArr(1, x)) Like pre Then


3) avevi proposto la routine che filtrava su due condizioni (colonna "A" = parola" colonna "B" = "S/N") c'è modo di applicare al soluzione anche a questa routine ?

4) il codice non è monco. Infatti quella routine si occupa semplicemente di controllare che per tutti gli incroci siano presenti sul dizionario parole che possano essere inserite. Se il risultato è si (true) allora estraggo " a caso" (=tua soluzione a precedente post) una parola tra quelle che soddisfano il FFiltraBL..... quindi ora nasce il problema di come estrarre dall'array e non dal foglio ....

Grazie

[Anthony47]

Grrr...
Inizialmente la FFiltraBL restituiva un arrai di una sola dimensione; poi ho aggiunto anche la seconda (per restituire la riga su cui la parola e' presente) ma non ho aggiornato l'altro codice.
Insomma devi usare

Codice: Seleziona tutto

For x = LBound(peppArr, 2) To UBound(peppArr, 2)


Per la modifica del tuo punto 3, si puo' fare ma stasera il tempo e' tiranno; domani e' peggio; vedro' che si puo' fare...

Ciao

[karug64]

ci mancherebbe..... pure in fretta ..... io non ne ho, e nel frattempo ci provo io (!!!)

[Marius44]

Buongiorno a tutti
Scusate se mi intrometto nel vostro discorso ma vorrei dare un mio contributo, spero utile.
Oltre al giustissimo suggerimento di Anthony (ciao) circa l'utilizzo di Array per evitare continui riferimenti al Foglio perchè non pensare a Collection o Dictionary? E' vero che i valori da immagazzinare sono tanti ma i due oggetti menzionati escludono la possibilità di inserire, se opportunamente utilizzati, doppioni e sono veloci nelle ricerche.

Vi prego di scusarmi se ho detto c ... orbellerie.
Ciao e al piacere di rileggervi,
Mario

[karug64]

Ciao Marius.
Innanzitutto ti ringrazio per essere intervenuto dando il tuo contributo alla discussione. Il mio intervento è solo per dire che non è assolutamente il caso di scusarti di esserti intromesso, anzi!!!! (Non è certo una discussione a due, anche se Anthony è onnipresente su ogni post e con le soluzioni sempre a portata di mano...). Lo stesso Anthony qualche rigo più su, ha invitato tutti a dare il proprio contributo. Quindi grazie. E poi per chiederti, se possibile, di spiegarmi, magari con l'aiuto di qualche esempio, come potrei implementare questi concetti sul foglio di lavoro (se ti è possibile, ovviamente).
Grazie

[Marius44]

Buon pomeriggio
Non credo di essere in grado di spiegare esattamente le differenze (Anthony senza alcun dubbio si) ma, per tuo studio, allego un file in cui alcuni amici ed io (in altro Forum) ci siamo cimentati nel provare a velocizzare una macro.

Il pulsante Richiesta l'ho aggiunto apposta per farti vedere quale è il problema e cosa si vuole che la macro faccia.
Come potrai ben vedere le differenze nei tempi di esecuzione ci sono (e come se ci sono) specie se applicate ad oltre 60000 voci come per il tuo file.

https://www.dropbox.com/s/v2ob6qr7uqgxjsa/test_prove.xlsm?dl=0

Fammi sapere se ti è stato d'aiuto. Ciao,
Mario

[karug64]

Ok. Ci studio un po' su . Grazie

[Anthony47]

Per una infarinatura su Dictionary e Collection guarda qui: viewtopic.php?t=96606
Nel caso di karug, l'elenco da 60mila voci e' usato per la scelta delle parole ed e' richiesto l'uso dell'operatore Like, non vedo il vantaggio di queste strutture dati alternative.

Tuttavia io il problema nel file pubblicato da Marius l'avrei affrontato in altro modo; cioe', partendo dalla Sub SfondoColore:

Codice: Seleziona tutto

Sub SfondoColoreAnth() 'Codice iniziale by Marius44
'ARRAY, Modificata by Anthony
Dim i As Long, myRan As Range
Dim j As Long
Dim uriga As Long
Dim uriga1 As Long
Dim wks As Worksheet
Dim wks1 As Worksheet
Dim Rng As Range
Dim DP '() As String 'per le colonne P-T di Foglio Riepilogo
Dim RP '() As String 'per la colonna A di Foglio Riepilogo
Dim SH '() As String 'per la colonna A di Foglio Sheet
Dim CK '() As String 'per la colonna CK di Foglio Sheet
itime = Timer
Sheets("Riepilogo").Select
Set Rng = Range("P2:T" & Range("A" & Rows.Count).End(xlUp).Row)
Rng.Interior.ColorIndex = xlNone
Application.ScreenUpdating = False
Set wks = ThisWorkbook.Worksheets("Riepilogo")
Set wks1 = ThisWorkbook.Worksheets("Sheet")
uriga = wks.Range("A" & Rows.Count).End(xlUp).Row
uriga1 = wks1.Range("A" & Rows.Count).End(xlUp).Row
RP = wks.Range(Cells(2, 1), Cells(uriga, 1)).Value    ''''
DP = wks.Range(Cells(2, 16), Cells(uriga, 20)).Value   '''
'SH = wks1.Range(wks1.Cells(2, 1), wks1.Cells(uriga1, 1)).Value
CK = wks1.Range(wks1.Cells(2, 89), wks1.Cells(uriga1, 89)).Value
Set rsh = wks1.Range(wks1.Cells(1, 1), wks1.Cells(uriga1, 1))
For a = 16 - 15 To 20 - 15
    For i = 2 - 1 To uriga - 1
        If DP(i, a) <> "" Then
            mymatch = Application.Match(DP(i, a), rsh, 0)
            If Not IsError(mymatch) Then
                If CK(mymatch - 1, 1) = "Consegnato" Then
                    wks.Cells(i + 1, a + 15).Interior.ColorIndex = 43
                End If
            End If
        End If
1   Next i
Next a
Set wks = Nothing
Set wks1 = Nothing
ftime = Timer
Cells(13, 30) = ftime - itime
Application.ScreenUpdating = True
MsgBox "Fatto!", vbExclamation, "Manutenzione"
End Sub


Rispetto alle altre macro gia' presenti non fa brutta figura.

Avrei bisogno di un chiarimento circa il "punto 3" pendente: quale e' il contenuto della colonna B = "S/N", e quale il suo significato; immagino che possa contenere S oppure N, all'inizio sono tutte S e poi piano piano (man mano che usi le parole) metti N; di conseguenza la Function FFiltraBL dovrebbe restituire solo parole della lunghezza cercata che abbiano S in colonna B. Ma prima di modificare il codice vorrei sapere se e' proprio cosi'.

Ciao

[karug64]

Ciao Anthony, ma sei passato dalla "banca del tempo" ??!?

Si la logica è proprio quella.
La colonna può avere 3 possibili valori:

S=Stampata
N=Non selezionata
P=Provata

Di default all'inizio sono tutte "N", quindi dovrò estrarre tutte quelle con lunghezza x che siano = ad N

Grazie

[Marius44]

Ciao Anthony
Mi farò bello (si fa per dire), ovviamente lasciandoti la paternità della macro, con i miei amici.

Veramente non credevo si potesse arrivare a tanto
Grazie e ciao,
Mario

[karug64]

Salve.

Poichè per risolvere l'intoppo del programma, ho dovuto, in questi giorni, modificarne, in parte, la logica, l'esigenza esposta in questo post è variata.

Ho modificato la routine proposta da Anthony in questo modo:

Codice: Seleziona tutto

Public Sub new_carica_dizionario(ByRef myWdList As Range)
Dim WArr, i As Long, myOInd As Long

nome_modulo = "New_SubFunc_usate"
nome_routine = "New_carica_dizionario"

On Error GoTo errore:

WArr = myWdList.Value
ReDim OArr(1 To 3, LBound(WArr, 1) To UBound(WArr, 1))
myOInd = LBound(OArr)
For i = LBound(WArr, 1) To UBound(WArr, 1)
        OArr(1, myOInd) = i
        OArr(2, myOInd) = WArr(i, 1)
        OArr(3, myOInd) = Len(Trim(WArr(i, 1)))
        myOInd = myOInd + 1
Next i
ReDim Preserve OArr(1 To 3, LBound(WArr, 1) To myOInd)

Exit Sub
errore:
MsgBox "Rilevato errore." & Chr(13) & "Modulo: " & nome_modulo & Chr(13) & "Routine: " & nome_routine & Chr(13) & Chr(13) & Err.Description, vbCritical + vbOKOnly, "Errore"
Exit Sub

End Sub

in questo modo nell'array OArr() ho la posizione, la parola e la lunghezza della stessa.

Ora dovrei ordinare OArr() in base alla lunghezza_parola (3).

C'è modo di farlo abbastanza velocemente (visto che OArr conterrà oltre 60.000 parole) ?

Grazie

[Anthony47]

Il mio suggerimento e' di ordinare l'elenco sul foglio Excel, che lo fai una volta e vale per sempre (fintanto che non aggiungi nuove Parole all'elenco). Altrimenti ricorreremo a una "bubble sort"

Ciao

[karug64]

Il mio suggerimento e' di ordinare l'elenco sul foglio Excel, che lo fai una volta e vale per sempre (fintanto che non aggiungi nuove Parole all'elenco). Altrimenti ricorreremo a una "bubble sort"

Ciao

Ciao Anthony.
Ho dato un'occhiata in questi giorni a bubble e quick ....
Ma ordinare oltre 60.000 elementi col bubble (non ho provato), ma penso ci voglia un bel po' di tempo (e non so con quick).

Per quanto riguarda l'ordinamento sul foglio, in realtà, ci avevo pensato pure io, ma volevo ordinare perchè, magari, un domani, mi sgancio da excel e il dizionario mi trovo a caricarlo da un txt o altro ..... e avere la routine pronta all'uso mi permetterà di arenarmi ....

Grazie

[Anthony47]

In Inglese: We shall cross the bridge when we get there
Oppure: Domani e' un'altro giorno, si vedra'

[karug64]

In Inglese: We shall cross the bridge when we get there
Oppure: Domani e' un'altro giorno, si vedra'