L’azienda di Jeffrey ha una serie di rapporti che utilizzano un ampio numero di formule di matrice CSE (Ctrl + Maiusc + Invio). Quando qualcuno si dimentica di tenere premuto Ctrl e Maiusc quando si preme Invio, le formule risultanti non corrispondono alla risposta corretta. Controllare ogni cella, cercare le parentesi \ {} è sia noioso che richiede tempo. Jeffrey si chiede se esiste un modo rapido per trovare le “parentesi mancanti” o sollevare un flag di errore se Ctrl + Maiusc + Invio non viene premuto quando dovrebbe essere?

Non esiste un metodo intrinseco o formulato per farlo in Excel. Ciò significa che è necessario rivolgersi a una soluzione basata su una macro.

Fortunatamente, VBA offre diversi modi per affrontare questo problema. Un approccio consiste nell’usare semplicemente una formula per assicurarsi che ogni formula all’interno di una selezione sia effettivamente una formula di matrice.

Sub MakeCSE1()

Dim rCell As Range

For Each rCell In Selection         rCell.FormulaArray = rCell.Formula     Next rCell End Sub

Questa macro presuppone che selezionerai le celle da “convertire” prima di eseguire effettivamente la macro. Se preferisci, puoi definire un intervallo di celle (dare un nome all’intervallo) e quindi eseguire una macro simile che funzioni sempre su quell’intervallo.

Sub MakeCSE2()

Dim rng As Range     Dim rCell As Range     Dim rArea As Range

Set rng = Range("CSERange")

For Each rArea In rng.Areas         For Each rCell In rArea.Cells             If rCell.HasArray = False Then                 rCell.FormulaArray = rCell.Formula             End If         Next rCell     Next rArea End Sub

Questa macro cerca un intervallo denominato CSERange e quindi controlla ogni cella dell’intervallo. Se non contiene una formula di matrice, la formula viene convertita in una formula di matrice.

Notare l’uso della proprietà HasArray per verificare se una cella contiene una formula di matrice. Questa proprietà può effettivamente essere utile in altri modi. Ad esempio, potresti creare una semplice funzione definita dall’utente, come questa:

Function NoCellArray1(rng As Range) As Boolean     NoCellArray1 = Not rng.HasArray End Function

Questa funzione restituisce True se la cella a cui si punta non contiene una formula di matrice. Se ne contiene uno, viene restituito False. È quindi possibile utilizzare questa funzione come base per un formato condizionale. Tutto quello che devi fare è creare un formato che lo utilizzi in questo modo:

=NoCellArray1(A5)

Poiché NoCellArray restituisce True se la cella non contiene una formula di matrice, il formato condizionale potrebbe impostare il colore della cella su rosso o impostare qualche altro segno visibile che la cella non ha la formula di matrice richiesta. Puoi anche usare la seguente funzione per eseguire la stessa operazione:

Function NoCellArray2(rng As Range) As Boolean     NoCellArray2 = (Evaluate(rng.FormulaArray) <> rng.Value)

End Function

Un approccio completamente diverso consiste nell’aggiungere qualcosa alle formule che consenta loro di essere facilmente riconosciute come formule di matrice. Ad esempio, potresti aggiungere quanto segue alla fine di una qualsiasi delle tue formule di matrice:

+N("{")

Ciò non influisce in alcun modo sul calcolo ma può essere facilmente controllato per vedere se è presente. Il controllo può essere eseguito da un gestore di eventi, come il seguente:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Right(Selection.FormulaArray, 5) = "(""{"")" Then         ActiveCell.Select         Selection.FormulaArray = ActiveCell.Formula     End If End Sub

Si noti che il gestore verifica se la formula termina con (“\ {“) e, in caso affermativo, forza la formula da trattare come una formula di matrice. La cosa grandiosa di questo approccio è che non dovrai mai più premere Ctrl + Maiusc + Invio sul foglio di lavoro: il gestore di eventi se ne prende cura per te. Se, a un certo punto, desideri riconvertire la formula in una versione normale (non array), modifica semplicemente la formula in modo che non includa + N (“\ {“).

_Nota: _

Se desideri sapere come utilizzare le macro descritte in questa pagina (o in qualsiasi altra pagina dei siti ExcelTips), ho preparato una pagina speciale che include informazioni utili.

Fare clic qui per aprire quella pagina speciale in una nuova scheda del browser.

ExcelTips è la tua fonte di formazione economica su Microsoft Excel.

Questo suggerimento (478) si applica a Microsoft Excel 2007, 2010, 2013, 2016, 2019 ed Excel in Office 365. Puoi trovare una versione di questo suggerimento per l’interfaccia del menu precedente di Excel qui:

Verifica della corretta immissione di formule di array.