Copiare Formule (senza aggiornamento dei riferimenti). - dal 04/09/04 pagina vista: volte

Una recente domanda mi ha fatto sospettare che nella copia di formule, si può avere necessità di non aggiornare i riferimenti alle celle, (al di là dei riferimenti assoluti ottenuti usando il dollarone ($)), ma di copiare pari pari una formula contenuta in una cella, in un'altra cella dove vorremo la stessa identica formula, compreso i riferimenti alle celle. Forse con un esempio ci capiamo meglio. Supponiamo in C1 di avere la formula =A1*B1, e di volere la stessa formula in G10.

Excel ci consente tranquillamente di copiare solo delle formule, basta selezionare la cella con la formula da copiare, indi selezionare la cella destinazione, e dal menù Modifica/Incolla Speciale, selezionare l'opzione "Formule" e premere OK. La formula viene copiata, e solo lei, senza il valore restituito dalla formula stessa, MA, il bravo Excel provvede ad aggiornare anche i riferimenti (a meno che la formula copiata non contenesse i riferimenti assoluti).

Ora, sia perchè non sono in molti ad usare costantemente i riferimenti assoluti, sia perchè è comunque un esercizio interessante sapere usare la proprietà "Formula" applicata all' "oggetto Range", vediamo due soluzioni di come usare il vba per ottenere la copia di una formula. Vediamo intanto cosa ci dice la guida in linea a proposito di questa proprietà:

"Questa proprietà restituirà una costante se la cella ne contiene una, una stringa vuota se la cella è vuota oppure una formula sotto forma di stringa nello stesso formato in cui verrebbe visualizzata nella barra della formula, incluso il segno di uguale, se la cella contiene una formula."

L'esercizio: una semplice istruzione da usare come macro e da associare ad un pulsante:

• Sub CopiaFormula()
  X = [C1].Formula
  [G1].Formula = X
  End Sub

La macro recita: con X prendiamo la formula contenuta in C1, e rendiamo la formula nella cella G1. Con l'istruzione [G1].Formula ci assicuriamo che nella cella venga inserito il valore di X NON come testo MA appunto come formula.

Se vogliamo rendere variabile sia la cella da cui copiare la formula sia quella dove destinarla, possiamo usare due InputBox per richiedere i riferimenti a queste due celle. Nelle istruzioni dovremo definire che i dati reperiti con le InputBox sono riferimenti a celle, usando la parola Range che racchiude i due valori, così:

• Sub CopiaAScelta()
  origine = InputBox("Scrivi la cella la cui formula vuoi copiare")
  If origine = "" Then Exit Sub
  X = Range(origine).Formula
  dest = InputBox("Scrivi la cella di destinazione della formula")
  If dest = "" Then Exit Sub
  Range(dest).Formula = X
  End Sub

Le istruzioni sono semplici da capire. Se non scriviamo niente o annulliamo nelle due inputbox, usciremo dalla macro. Se quindi avremo la formula da copiare in C1, scriveremo C1 nella prima inputbox:

nella seconda inputbox scriveremo la destinazione, esempio D1, e questo il risultato:

e come vediamo dalla barra della formula il risultato è garantito.

Ma esiste anche un'altro modo di usare questo tipo di "Copia-Formule", sfruttando una Funzione Utente, quindi fatta da noi, che potrà essere salvata in una "Aggiunta .xla" (vedi l'altro sito, sezione vba, articolo "Funzioni Utente-Aggiunta.xla") in modo da avere la funzione disponibile per qualunque cartella di Excel.

Va subito chiarito che questa Funzione potrà essere usata solo in altre istruzioni vba, e NON come funzione sul foglio di lavoro, il motivo è semplice: si creerebbe un riferimento circolare; infatti nella stessa cella dove richiameremmo la funzione, vorremmo come risultato della funzione un'altra formula, il che è impossibile.

Ma vediamo la funzione:

• Function Copform(ByVal target As Range)
  Copform = target.Formula
  End Function

L'ho chiamata Copform per con scrivere tanto, comunque è semplice: "target" è l'argomento della funzione, cioè la cella che indicheremo nelle istruzioni che useranno la funzione; quindi Copform restituisce la formula contenuta in "target".

Esempio di istruzione vba come usare la funzione:

• '......altre istruzioni
  [D1].Formula = Copform([C1])
  '......altre istruzioni

Anche in questo caso ho simulato la cella C1 come sorgente della fornula e D1 come cella destinazione. Sarà possibile anche qui usare delle variabili tramite inputbox per reperire i riferimenti alle celle. Se poi si volesse sfruttare diversamente il risultato della funzione, per esempio come stringa per completare altre formule, basterà usare una variabile al posto della cella di destinazione, esempio:

• X = Copform([C1])
  MsgBox X

dove X sarà uguale a "=A1*B1" cioè la formula.
 

Buon lavoro.

prelevato sul sito www.ennius.altervista.org