Convertire una macro in una funzione per Excel

[biancocandido55]

Per prima cosa, lasciatemi fare un saluto a tutto il forum
chiedo umilmente un aiuto a voi tutti, per un prob, si tratta di questo:
attualmente sto usando una macro per riconoscere un numero attraverso un colore, e devo dire che per l'uso che ne faccio, funziona bene, anzi benissimo, ma mi occorre adesso trasformare questa macro in una funzione per excel, del tipo " =num2col(e8) ", vi allego il testo della macro

Codice: Seleziona tutto

Sub Num2Col()
'
' Num2Col Macro
' Macro registrata il 29/07/2002 da UAS
'
For Each cell_in_loop In Range("E8")
     If cell_in_loop.Value >= 1 And cell_in_loop.Value <= 56 Then
          With cell_in_loop.Offset(0, 1).Interior
               .ColorIndex = cell_in_loop.Value
               .Pattern = xlSolid
           End With
    ElseIf cell_in_loop.Value >= 57 And cell_in_loop.Value <= 360 Then
           With cell_in_loop.Offset(0, 1).Interior
                  .Color = 10830535 + ((cell_in_loop.Value + 2) ^ 3)
                  .Pattern = xlSolid
           End With
     End If
Next
End Sub

in attesa di un vosto aiuto, porgo a tutti un caloroso saluto.
Grazie, ciao.

[pietrol]

Ciao biancocandido55
una funzione, contrariamente ad una macro che compie "un'azione" quale quella di modificare lo sfondo di una cella, per sua natura restituisce un valore.
Il valore restituito può essere utilizzato in excel in vari modi, penso per esempio alla formattazione condizionale, ma non può agire attivamente su altre celle.
Se non, di nuovo, utilizzandolo come argomento di una macro.
Se quello che ti serve è rendere la macro flessibile, cioè non legata ad una particolare cella, si potrebbe modificarla sostituendo la riga

For Each cell_in_loop In Range("E8")

con

For Each cell_in_loop In Selection

Facci sapere

Ciao
pietrol

[biancocandido55]

Ciao pietrol,
ti ringrazio del suggerimento che mi dai, la provo e ti faccio sapere, ma la mia intenzione era un'altra, cercerò di spiegarmi meglio.
Non sò se tu conosci l'ormai famosa funzione utente "colorecella(ai;0)", che ti da i valori numerici del colore di sfondo della cella o il valore numero del colore del testo di una cella ecc. ecc., ebbene la mia intenzione era quella di avere una funzione che svolgesse il compito nell' esatto contrario, cioè che da un numero in una cella mi da come risultato il suo colore d'appartenenza, come ho già scritto prima la macro che uso per questo lavoro è perfetta ma mi servirebbe riscritta in modalità funzione
come l'esempio che ho scritto in precedenza cioè una funzione del tipo "=numeroToColore(a1)".
Intanto vado subito a provare il tuo suggerimento e ti faccio sapere.
Ciao e grazie.
Salut.

[biancocandido55]

Scusami pietro,
a parziale correzione della mia risposta la funzione che intendevo colorecella() è scritta così :" =colorecella(a1;0)", cioè con "A1" e non con "ai" come riportato prima
ciao
a pré..

[Anthony47]

Una funzione utente nasce per risolvere un problema peculiare non risolvibile in modo pratico dalle funzioni native excel; su questo sito ne abbiamo pubblicate tante, da GetCellColor e GetFontColor [in palese "concorrenza" con colorecella(A1;0)] a Function SSe3d(IShI, FShI, Cella, Crit) di qualche post fa.

Una Function per definizione non puo' fare altro che elaborare dei dati e restituirli nella cella che contiene la funzione; non puo' cioe' fare operazioni tipo formattazione, o manipolazione di altre celle (salvo leggerene i dati se sono stati passati come dati da elaborare).
Ed e' inutile fare call di un' altra "macro" perche' la macro, quando invocata da una Function, ne eredita tutti i vincoli.

Se vuoi fare cose piu' complesse potresti, ad esempio, sfruttare l' evento Worksheet_Change o piuttosto Worksheet_Calculate, che programmi affinche' se una certa cella contiene un certo valore fa le cose che ti servono, senno' exit.

Detto cio', assodato che "la famosa" colorecella (o la umile getcellcolor) ti restituisce l' indice di colore della cella target, tu che cosa vorresti fare oltre o invece?

Ciao.

[biancocandido55]

Ciao Anthony47,

avendo capito la differenza sostanziale che c'è tra una macro e una funzione, devo intendere che avevo riposto male la mia richiesta e per questo me ne scuso , a merito di questo posso provare a riformulare la domanda, che premesso potresse essere banale,- avrei bisogno di una funzione che dato un valore numerico (ad es. 44) riposto in un cella mi restituisca come risultato il colore del numero inserito (nel caso del numero 44) questo "▄" e cioè il colore arancione nella cella che contiene la funzione.

Nel caso degli eventi che mi citi "l' evento Worksheet_Change o l'evento Worksheet_Calculate",
per me che sono ancor meno che "gnostico" e "neofita" nel VBA in generale, cosa ti posso dire, per me pari sono.
Spero e certo in un vostro aiuto, rimango in attesa di giudizio.
Ciao a tutti e grazie.

[Anthony47]

Non c' e' bisogno di scusarsi, ma se esprimi in modo incompleto il problema allora le ipotesi di soluzione non arrivano.

Veniamo a quanto hai esposto; se vuoi una "funzione utente" che ti colori una cella in funzione del valore contenuto in altra cella, questo non si puo' fare: le funzioni utente non formattano le celle.
Ma si possono utilizzare approcci alternativi.

Ora, tu dici che il problema reale e' "dato un numero in una cella, applicami il colore che per excel corrisponde a quell' indice di colore a una cella"; se permetti, dubito che questo sia il problema: mi pare piu' il metodo che stai cercando di utilizzare per risolvere il problema vero.
Ma rimaniamo sulla richiesta; supponiamo che il numero sia contenuto nella cella F23 e che con questo vuoi formattare la cella G24:
-in una cella qualsiasi del "foglio" dove lavori metti la formula =casuale()
-tasto dx sul tab col nome foglio, scegli Visualizza codice; ti si apre l' editor delle macro
-incolla il seguente codice nel frame di dx:

Codice: Seleziona tutto

Private Sub Worksheet_Calculate()
If Range("F23").Value >56 then exit sub
Range("G24").Interior.ColorIndex = Range("F23").Value
End Sub


Se le tue celle non sono F23 e G24 devi modificare, mi pare evidente dove!
Torna sul foglio di lavoro, scrivi un numero in F23 e vedi l' effetto in G24; tieni presente che excel gestisce una "palette" di 56 colori, ColorIndex accetta quindi come numero max 56. Esiste anche la modalita' RGB, che applica 256*256*256 combinazioni; in questo caso dovrai usare "Interior.Color =" invece di "Interior.ColorIndex =" e cancellerai la riga If

Ciao, fai sapere.

[biancocandido55]

Caro Anthony47,
la soluzione che mi offri è utile e l'ho salvata in entrambi i modi, da parte mia c'è sempre da imparare, però perdonami ma non sono ancora persuaso del fatto che non si possa creare una funzione che faccia al caso mio e faccia le stesse cose della tua macro, quindi ti chiedo di aiutarmi a capire (data ancora una volta la mia ignoranza in materia), come è strutturata la funzione che citavo all'inizio del topic e provare a vedere se si possono trovare istruzioni idonee per lo svolgimento della funzione.
La Funzione citata all'inizio è "colorecella(A1;0)" e la sua stesura cita così:

Function ColoreCella(a As Range, Filtro As Integer) As String
Application.Volatile True
If Filtro=0 Then
ColoreCella = a.Interior.ColorIndex
Else
ColoreCella=a.Font.Color
End If
End Function
Rem =ColoreCella(A1,0)
Rem per avere il codice colore dello sfondo della cella A1

Rem =ColoreCella (A1,1)
Rem per avere il codice colore del testo della cella A1

ora mi chiedo se nello stesso modo si possa fare una funzione che faccia il percorso inverso.
scrivo bovinamente un esempio:

Function DaNumeroTocolore(Numero as range) As (quello che è ??)
controlla dato in cella
if cella = Interior.ColorIndex() then
DaNumeroTocolore = numeroTabella (da 1 a 56)
else
interior.color() then
DaNumeroTocolore = numero >56 fino a 16777215
end if
end Function

Se proprio non si può fare nulla, si potrebbe provare con un insieme di formule concatenate?
Confido fiducioso nella tua comprensione e rimango in attesa.

[biancocandido55]

Ciao Anthony47,
da ieri sera, sono passate molte ore e come si sà la notte porta consiglio , premesso ciò, nel rileggere il topic, avevo la soluzione sotto gli occhi e non me ne sono accorto .
Creare una funzione vera e propria non serviva (anche perchè da come dicevi era impossibile) almeno per quello che riguardava il controllo della formattazione dei colori in una cella tramite una funzione vera e propria.
Torniamo a noi, rileggendo tutti i suggerimenti del il topic ho tratto le conclusioni per l'uso della macro che già avevo a disposizione e con i vostri suggerimenti ho apportato piccole modifiche ora te la posto, ma avrei bisogno di alcuni suggerimenti (sempre ben accetti da voi tutti)

Private Sub Worksheet_Calculate()
For Each cell_in_loop In Selection
If cell_in_loop.Value >= 1 And cell_in_loop.Value <= 56 Then
With cell_in_loop.Offset(0, 1).Interior
.ColorIndex = cell_in_loop.Value
.Pattern = xlSolid
End With
ElseIf cell_in_loop.Value >= 57 And cell_in_loop.Value <= 90 Then
With cell_in_loop.Offset(0, 1).Interior
.Color = 10830535 + ((cell_in_loop.Value + 2) ^ 3)
.Pattern = xlSolid
End With
End If
Next
End Sub

come noterai ho usato il (Private Sub Worksheet_Calculate()),
successivamente ho messo il (For Each....) in selection e non in range, come diceva pietrol (grazie
il tutto l'ho inserito nel (modulo del Foglio1) ora però vi chiedo alcuni suggerimenti in merito:
- come posso eliminare il ricalcolo del foglio (cioè dopo avere inserito il numero, "evitare di premere ogni volta il
pulsante "F9" della tastiera;
- incrementare la macro facendo si che il numero da me inserito in una cella vada poi a colorare lo stesso numero già
inserito in precedenza in un'altro range di dati
- contare quanti sono i numeri colorati (ma questo è semplice, credo che posso fare il controllo con una funzione)
credo che devo rivedere l'uso del suggerimento di pietrol (come si fa come le lampadine : accendi (selection) e smorza(controllo range dati e colora) accendi (ecc..) e smorza (ecc..).
Rimango sempre fuducioso, in attesa, un grazie a voi tutti, ciao

[Anthony47]

Continui a non dire che cosa devi fare, di conseguenza non so che dire a proposito di quello che hai gia' fatto.
Relativamente alla domanda su F9, hai inserito =casuale()?

Ciao.

[biancocandido55]

Ciao Anthony,ti chiedo di scusarmi ,
ma mentre scrivo sono abituato a pensare a più cose contemporaneamente.
Come dai vostri suggerimenti ho anche inserito "=casuale()" nel foglio.
inserisco il numero nella cella, premo "enter", niente!
premo "F9", niente!
sposto il cursore dove ho inserito il numero, premo "F9", funziona!, mi da il colore nella cella a destra del numero inserito.
potete limitare la macro al solo inserimento del numero, e premere "enter".
Domanda: se limito la macro ad una zona del foglio con un range, sul resto del foglio non funziona? vero.
Per gli altri enigmi, un passo alla volta.
Sempre fuducioso, grazie, ciao.

[biancocandido55]

Ciao Anthony,
a seguito della mia richiesta e per spiegarmi meglio ho pensato di allegare questa immagine sintetica di tutto quello che vorrei fare con il programma, l'immagine è questa:

schema.jpg

schema evidenziazione e conta totale dei numeri inseriti

(66.3 KiB) Scaricato 58 volte

nella prima tabella in alto sulla 1ª colonna ci sono i numeri che inserisco , sulla 2ª colonna compaiono i colori (con l'uso della macro), sulla sezione della tabella archivio, come puoi notare sono evidenziati i numeri, e solo quelli che ho inserito nella tabella piccola, sulla 3ª tabella in basso invece mi fa il totale dei numeri che ci sono sulla tabella archivio.
Spero di essere stato esauriente nella spiegazione.
Un grazie e ciao, a presto.

biancocandido

[Anthony47]

Spero di essere stato esauriente nella spiegazione

Veramente non ho capito dove e' la spiegazione.
Se puntavi sull' immagine, essa non dice quello che vuoi fare; e sinceramente non capisco perche' costringi noi a fare le ipotesi invece che spiegarti.
Allora, visto che il nostro tempo vale poco:
-i numeri in C3:C8 li scrivi o sono calcolati?
-l' obiettivo tuo e' per caso applicare il colore sottinteso dall' indice in C3:C8 alle celle D3:D8?
-anche concentrandomi non capisco quale e' la " sezione della tabella archivio"; e' per caso l' area intestata "sez. tabella generale"??? Come ipotesi supponiamo di si.
-non capisco che cosa sono i numeri in area G3:K15, e che senso hanno i colori applicati ad alcune di queste celle; cortesemente lo spieghi?
-la "terza tabella" immagino che sia quella in C12:D17; se e' cosi', perche' dici che contiene il " totale dei numeri che ci sono sulla tabella archivio" quando (mi fermo al primo numero) di 18 nella tabella presunta archivio il 18 figura solo 2 volte?

Quanto poi all' affermazione del post precedente, "premo "enter", niente! premo "F9", niente! sposto il cursore dove ho inserito il numero, premo "F9", funziona!": ti rendi conto che ti sei scritta una macro che lavora sulla selezione attiva? Te la sei scritta tu, nessuno te l'ha suggerita; e allora perche' ti meravigli che "ti sembra" che funzioni solo quando e' selezionata la cella che contiene un indicedi colore? E dico "ti sembra" perche' in realta' la macro funziona anche quando la selezione e' diversa, salvo che fa una cosa che tu non vedi; perche' il colore che corrisponde allo zero e' il bianco.

Ciao.

[biancocandido55]

Ciao Anthony,
non sia mai detto che il vostro tempo vale poco, anzi è esattamente tutto il contrario, vale moltissimo.
Vediamo:

la tabella G3:K15 che in realtà è un archivio di 55 colonne e circa 6000 righe, riga + riga -. La uso per inserirvi i numeri in modo diretto (in questa fase la macro non deve influire).

- vado a scrivere i numeri a piacere da C3:C8 (da qui inizia il lavoro della macro);
- i colori in D3:D8 me li da la macro dopo che ho scritto i numeri di cui sopra;

poi la macro dovrebbe proseguire il suo compito così:

- leggere i numeri in C3:C8 e andare a cercarli nella tabella G3:K15 e metterli in evidenza con il proprio codice colore;

- quando il numero evidenziato ha il fondo bianco o molto chiaro evidenziare il numero in rosso, viceverso qundo il fondo del numero evidenziato è nero o molto scuro evidenziare i numeri in bianco.

- fine lavoro della macro.

- (in questa fase la macro non deve avere nessun effetto) i numeri della tabella in C12:D17 invece è così composta nella 1ª colonna C12:C17 ci sono i stessi numeri inseriti in C3:C8, nella 2ª colonna D12:D17 il totale dei numeri che inserisco nella 1ª colonna contandoli dall'archivio di 55 colonne e 6000 righe.
(per questo i totali dei numeri non tornavano)


Ti ringrazio come sempre per la tua cordiale disponibità.

ciao

[Anthony47]

Ehm... e se ti dico che la ricca descrizione (molto ricca rispetto a come ci avevi abituato) non descrive il problema vero ma "come tu hai pensato di risolverlo" ti offendi?
Nella mia interpretazione di excel i colori sono un aspetto che determina la "presentazione dei dati" non la loro natura; sono quindi di principio contrario a usare i colori per caratterizzare la natura dei dati. Al momento non ho ancora capito l' uso che fai dei colori, se cioe' "violi" questa mia convinzione, ne' di quanti colori hai bisogno. Quindi perche' devo complicarmi la vita per risolvere un problema che (forse) e' stato impostato in modo contrario alle mie convinzioni, cioe' male (sempre secondo le mie convinzioni)? Insomma vorrei mettere il naso nel vero problema (per aiutare a risolverlo, sia chiaro), non solo in come fare un For /Next che abbraccia le celle giuste e prendermi il mal di pancia di andare a dedurre se un certo colore offre un contrasto cromatico adeguato oppure no. Tutte cose che si possono fare, se non c' e' una via piu' diretta per l' obiettivo.
Spero che condividerai molto di quanto ho detto.

Ciao.

[biancocandido55]

Ciao Anthony,
eccomi qua, tranquillo non mi offendo affatto, anzi.
Allora, mi dici

Nella mia interpretazione di excel i colori sono un aspetto che determina la "presentazione dei dati" non la loro natura

è giusto quello che dici, ma ti chiedo

perchè non usare proprio la loro natura per rappresentarli?

,penso che sia anche un modo più diretto per usare entrambe colori e numeri insieme.

Mi chiedi di quanti colori ho bisogno, i colori che a me servono sono 90 (uno per ogni numero).

Che tu voglia mettere il naso nel vero problema, non può che farmi piacere (tranquillo non puzza ).

Il mio "problema" era "come posso mettere in evidenza i numeri che scelgo nella tabellina piccola sulla tabella grande ovvero ,l'archivio, per vedere dove sono posizionati?", senza dover usare ogni volta la formattazione condizionale per tutti e 90 i numeri.

Il fatto di poter evidenzare i numeri che scelgo mi renderebbe più facile il controllo su dove sono posizionati nell'archivio (è solo una questione di effetto visivo)

L'uso di tutto questo ambaradan? (ti prego di non ) Spaziometria.

Sul fatto di condividere quello che dici, in parte sono d'accordo con te, ma per mia natura non dò mai nulla per scontato, mi piace sperimentare le cose.

Infine per il tuo mal di pancia, mi dispiace moltissimo sinceramente, ci avrei pensato io se!!
Qui ti faccio un'altra domanda, Come si può mandare un digestivo via Internet?
Ti ringrazio come sempre per la tua cordiale disponibità, sempre tuo debitore.
Ciao.
Biancocandido55

[Anthony47]

Rieccomi.
Allora, hai spiegato che l' obiettivo finale e' osservare a colpo d' occhio la distribuzione dei numeri su un tabellone 55*6000: che occhio, e che schermo!
Se i numeri che imposti sono in C3:C8, quindi 6, ti dovrebbero bastare 6 colori! Se e' cosi', e se hai Excel 2007, lo puoi fare con la formattazione condizionale. Se hai versioni precedenti servira' una macro, la cui area di lavoro sara' C3:C8 in input e G??:XX?? (cioe' da chiarire) in output; e' di modesta complessita', ma se hai 2007 e' meglio farlo con la formattazione condizionata.

Infine per il mal di pancia, non sentirti in colpa: mi prendo solo quelli che voglio!

Ciao.

[biancocandido55]

Ciao Anthony47,
lo sapevo che sei un grande,
allora, nussun occhio e nessuno schermo (anzi uso occhiali e mouse ), si ho l'excel 2007 mentre altri amici usano il 2003, e come ti avevo già accennato

senza dover usare ogni volta la formattazione condizionale per tutti e 90 i numeri

, mi sembrava piuttosto caustica la questione nel dover usare la formattazione condizionale per 90 numeri, e reimpostarli ogni volta per ogni numero che mi interessava, ecco perchè ho tirato giù quella macro che abinava colori e numeri e per la mia Gnoranza del VBA non sapendo come applicarla all'uso dell'archivio dei numeri (dal 1841 ad oggi, ....l'achivio), pensavo quindi che si potesse creare una funzione ad hoc per questo.
Le colonne da A a F le tengo vuote, l'archivio va da G3 a BI3 (55 colonne), le righe attuali circa 6000 e aumentano ad ogni estrazione.
Nela speranza che venga accolta la mia richiesta di aiuto, cordialmente un saluto e chissa se accetterai anche un dialog in pvt (lo spero), ciao grande .

P.S. per i tuoi mal di pancia cercherò di organizzarmi io in qualche modo.

Ciao

[Anthony47]

No, guarda che se i numeri che imposti sono 6 (C3:C8), quindi devi discernere 6 colori allora dovrai impostare 6 condizioni e basta, non devi piu' cambiarle;
-selezioni tutte le colonne che ti interessano, es G:AZ
-avvii la formattazione condizionata
-prima condizione: se il valore della cella e' uguale a C3, applica il giallo
-seconda condizione, se il valore della cella e' uguale a C4, applica il verde
-terza..
-quarta..
-quinta..
-sesta..

Fine
Cosa non torna?

Ciao.

[biancocandido55]

Ciao Anthony, buongiorno.
Ok, va bene, ho capito , ma ........ per chi avesse l'excel 2003????? , cosa dovrebbe fare?
Anthony, su forza hai fatto 30..., che ne pensi se fai anche 31 ,
penso che potrebbe tornare comunque utile a molti di coloro che non usano excel 2007.
un cordialissimo saluto.
Sempre grande.
ciao, bianco.