Excel – Utilizzo della proprietà “NumberFormat” (VBA)

Utilizzo della proprietà "NumberFormat"

Sappiamo quanto sia importante predisporre il Formato Celle sul foglio di lavoro in modo da corrispondere al tipo preciso di dati che dovranno contenere. Quando poi questi dati sono il risultato di un'istruzione vba, diventa ancora più necessario far corrispondere il formato cella al "tipo" di dati restituito dal codice stesso. Sappiamo anche che il Formato Celle preimpostato da Excel per tutte le celle è  il formato "Generale", che appunto perchè "Generale" dovrebbe accettare qualsiasi tipo di dato immesso. Esistono tuttavia delle condizioni di formato che devono essere impostate manualmente perchè il "Generale" da solo non riuscirebbe a darci il dato così come lo vorremmo.

Prendiamo il caso di volere in una cella, una cifra a due decimali mentre il risultato di un'operazione eseguita tramite codice sia a più decimali, esempio 13,4578,  nella cella predisposta a ricevere il dato, se  impostata col formato cella a "Generale" vedremmo il numero così come restituito dal vba. Dovremo quindi selezionare la cella, scegliere "Formato celle", e nella scheda "Numero" selezionare "numero" e impostare manualmente il numero di decimali (2) e se vogliamo, anche il separatore delle migliaia.

Torna utile quindi poter disporre di istruzioni via codice in modo da predisporre il Formato cella nella cella che conterrà il dato restituito dal codice senza farlo manualmente dal foglio di lavoro.

Premesso che se definiamo già via codice il "tipo" di dati che il codice restituisce, Excel predispone automaticamente il Formato Cella quando riceve il dato, esistono tuttavia istruzioni nelle quali o perchè ci dimentichiamo di definire il tipo di dato, o perchè non lo sappiamo, o per altri motivi, il risultato che vediamo nella cella non è quello che ci aspettavamo. Facciamo un esempio per capire cosa si intende per "tipo di dato":

• cella A1 con Formato Cella impostato a "Generale"
• istruzione che assegna alla variabile X il valore 13,4578 (ricordo che il codice usa il sistema di separatori inglesi, esattamente l'opposto del sistema basato sul SMD, il nostro, e cioè il punto (.) per indicare i decimali, e la virgola ( , ) per indicare il separatore delle migliaia)
• X = 13.4567   (sarebbe il nostro 13,4567)
• istruzione per predisporre il formato del valore rappresentato da X, con due decimali :
• X = Format(X, "#,###.00")  (X ora sarebbe uguale al nostro 13,46 per via degli arrotondamenti che fa Excel, anche se in realtà non è così, ma lo vedremo un'altra volta)
• assegnazione alla cella A1 del valore rappresentato da X :
• Range("A1") =  X

e vedremmo la cella A1 in questa maniera: il numero scritto a due decimali, ma allineato a sinistra come se fosse testo, infatti viene evidenziato come "errore" e cioè : "numero memorizzato come testo", ed in più Excel continuerebbe ad avere il Formato Cella impostato a "Generale". Se invece modifichiamo l'ultima riga dell'istruzione dichiarando il "tipo" di dato rappresentato da X, Excel ora lo accetterà come numero:

• Range("A1") =  CDbl(X)      (CDbl è un "tipo" di dato Double, cioè numero con decimali)

e questa la nostra istruzione completa:

X = 13.4567
X = Format(X, "#,###.00")
Range("A1") = CDbl(X)

ora sì che vedremo il nostro numero allineato a destra nella cella e senza che Excel rilevi un errore. Ma nonostante tutto, se controlliamo il Formato Cella, vedremo che è ancora impostato a "Generale". Riallacciandoci quindi al tema dell'argomento, per avere anche il Formato cella che desideriamo, dovremo usare un'istruzione appropriata per definire il formato cella voluto; inseriremo quindi l'istruzione prima di assegnare alla cella il valore X (possiamo mettere la seguente istruzione anche a inizio routine).

Useremo quindi la proprietà "NumberFormat" che è una proprietà degli oggetti CellFormat e Range.

Range("A1").NumberFormat = "#,##0.00"

In questo modo non solo otteniamo che il Formato Cella sarà quello voluto (cioè Formato cella impostato a numero con due decimali e separatore delle migliaia) ma potremo evitare anche l'istruzione di formattazione del valore X e anche la dichiarazione del "tipo" di dato, come esemplificato sotto:

Range("A1").NumberFormat = "#,##0.00"
X = 13.4567
Range("A1") = X

Ma vediamo quali sono le istruzioni da usare in Vba per i formati cella più comuni. Uso la cella A1 come esempio, ma sarà possibile istruire istruzioni per ogni cella e anche per Range di celle o aree.

• per formattare una cella con numero a 2 decimali e separatore delle migliaia
  

  Range("A1").NumberFormat = "#,##0.00"

• per formattare una cella in formato testo:
  

  Range("A1").NumberFormat = "@"

• per formattare una cella in formato data con anno a due cifre ( tipo: 01/02/03)

  Range("A1").NumberFormat = "m/d/yyyy"

• per formattare una cella in formato data con anno a quattro cifre ( tipo: 01/02/2003)

  Range("A1").NumberFormat = "mm/dd/yyyy"

• per formattare una cella in formato percentuale (%)

  Range("A1").NumberFormat = "0.00%"

oppure usando Cells al posto di Range (per la A1 possiamo usare  Cells(1, 1) (cella riga 1 colonna 1))

Cells(1, 1).NumberFormat = "#,##0.00"

o ancora per un insieme di celle da A1 a A10:

Range("A1:A10").NumberFormat = "#,##0.00"

oppure usando Cells (in questo caso si deve usare Cells non come oggetto, ma come proprietà di un oggetto Range) per il solito insieme di celle (da A1 a A10):

Range(Cells(1, 1), Cells(10, 1)).NumberFormat = "#,##0.00"

Un ultima cosa: per ottenere la trasposizione in codice di altri Formati Cella, usate il "registratore di macro": avviate il registratore, selezionate una cella, scegliete da Formato celle il formato desiderato, date Ok, stoppate il registratore, e andate a vedere cosa ha compilato Excel per voi: quella è l'istruzione da adoperare assegnandola poi alla o alle celle che vi interessano.