Come registrare data e ora automaticamente a ogni lettura del lettore di codici a barre in Excel e Google Fogli

Per avere data e ora fisse accanto a ogni codice letto serve qualcosa che scriva l'orario come valore nel momento della lettura: in Excel una macro Worksheet_Change, in Google Fogli uno script onEdit(e). La formula =ADESSO(), che quasi tutte le guide suggeriscono, non va bene per un registro: si ricalcola, e al ricalcolo successivo tutte le righe mostrano la stessa ora, quella attuale.

Perché ADESSO() non funziona per un registro

Microsoft lo scrive nella pagina della funzione: il risultato di ADESSO (NOW nella versione inglese) "cambia solo quando il foglio viene calcolato o quando viene eseguita una macro che contiene la funzione". Non è un orario registrato, è un orologio. Appena scrivi in un'altra cella, apri il file domani o premi F9, ogni =ADESSO() della colonna mostra l'ora di quel momento. In un foglio di prova sembra funzionare, perché subito dopo la lettura l'ora è giusta; il problema compare solo quando rileggi il registro.

Il consiglio sopravvive perché risponde a un'altra domanda, "come mostro l'ora corrente in una cella", ed è quella che trovano le ricerche generiche. Le scorciatoie per data e ora fisse danno un valore corretto, ma vanno premute a mano dopo ogni lettura.

Prima di scrivere codice: Invio o Tab decide la colonna

Il lettore in emulazione tastiera digita il codice nella cella attiva e poi preme il suffisso. Excel e Fogli considerano la cella modificata solo quando arriva quel tasto: senza suffisso la cella resta in modifica e nessuna macro parte. Se l'Invio non arriva, risolvi prima con il lettore non invia Invio.

Il suffisso decide anche dove andrà la lettura successiva, e quindi dove la macro può scrivere l'ora senza che il codice dopo la sovrascriva:

Con il suffisso Tab l'errore tipico è scrivere l'ora in B: la lettura successiva o la quantità finisce proprio lì e cancella l'orario. Come si imposta il suffisso è spiegato in aggiungere Invio o Tab dopo la lettura. Controlla anche in Excel l'opzione "Dopo aver premuto Invio, sposta la selezione": deve essere Giù.

Excel: la macro Worksheet_Change

L'evento Worksheet_Change si verifica, secondo la documentazione Microsoft, "quando le celle del foglio vengono modificate dall'utente o da un collegamento esterno". Per Excel le battute del lettore sono battute di un utente, quindi l'evento scatta a ogni lettura confermata. La stessa pagina precisa che l'evento "non si verifica quando le celle cambiano durante un ricalcolo": è il motivo per cui una formula non può fare questo lavoro e una macro sì.

1. Apri l'editor VBA con Alt+F11.
2. Doppio clic sul foglio dove leggi i codici (per esempio Foglio1) nel riquadro a sinistra. Il codice va nel modulo del foglio, non in un modulo generico.
3. Incolla la macro qui sotto e adatta le due costanti alle tue colonne.
4. Salva come "Cartella di lavoro con attivazione macro di Excel" (.xlsm): in un .xlsx la macro viene eliminata.
5. Prova con tre letture, poi premi F9 e verifica che gli orari non cambino.

Private Sub Worksheet_Change(ByVal Target As Range)
    Const COL_CODICE As String = "A"   ' dove scrive il lettore
    Const COL_ORA As String = "B"      ' dove va la data e ora
    Dim zona As Range, c As Range

    Set zona = Intersect(Target, Me.Columns(COL_CODICE), Me.UsedRange)
    If zona Is Nothing Then Exit Sub

    On Error GoTo fine
    Application.EnableEvents = False
    For Each c In zona.Cells
        If c.Row > 1 And c.Value <> "" And Me.Cells(c.Row, COL_ORA).Value = "" Then
            Me.Cells(c.Row, COL_ORA).Value = Now
            Me.Cells(c.Row, COL_ORA).NumberFormat = "dd/mm/yyyy hh:mm:ss"
        End If
    Next c
fine:
    Application.EnableEvents = True
End Sub

Tre dettagli fanno la differenza rispetto alle macro che circolano nei forum:

• Now, non una formula. In VBA Now restituisce data e ora e la macro le scrive come valore: la cella contiene un numero di data, non =ADESSO().
• EnableEvents spento e riacceso. Scrivere l'ora è a sua volta una modifica del foglio. È lo stesso schema dell'esempio ufficiale Microsoft: disattivare gli eventi prima di modificare le celle e riattivarli subito dopo, così la macro non richiama se stessa. La riga On Error GoTo fine garantisce che vengano riattivati anche se qualcosa va storto: se restassero spenti, le macro di evento smetterebbero di partire.
• L'ora si scrive solo se la cella è vuota. Se correggi un codice a mano, l'orario della prima lettura resta. Se vuoi l'ora della correzione, cancella prima la cella dell'ora. Target "può essere più di una cella", dice la documentazione: per questo il ciclo gestisce anche un incolla di molte righe.

E senza macro?

Circola una formula che evita il VBA, =SE(A2="";"";SE(B2="";ADESSO();B2)): la cella si riferisce a se stessa per "congelare" il primo orario. È un riferimento circolare, e funziona solo attivando il calcolo iterativo (in Excel per Windows: File, Opzioni, Formule, "Abilita calcolo iterativo"). Microsoft, nella pagina sui riferimenti circolari, raccomanda di tenere il calcolo iterativo disattivato nella maggior parte dei fogli e di riservarlo a modelli finanziari o ingegneristici in cui il ricalcolo ripetuto è voluto. Per un registro di letture che deve fare fede, la macro è la scelta da preferire; se le macro sono vietate in azienda, Google Fogli con uno script o il lettore in batch con orario sono alternative più solide.

Google Fogli: lo script onEdit(e)

In Fogli lo stesso lavoro lo fa un trigger semplice di Apps Script. Google lo descrive così: onEdit(e) viene eseguito "quando un utente modifica il valore di una cella". Le letture del lettore arrivano come digitazione, quindi lo attivano esattamente come la tastiera.

1. Menu Estensioni, Apps Script. Si apre l'editor legato al foglio.
2. Incolla lo script, cambia il nome del foglio ("Letture") e le colonne, poi salva.
3. Formatta la colonna dell'ora con Formato, Numero, Data e ora.
4. Prova leggendo nel foglio, non con il pulsante Esegui: lanciato dall'editor lo script non riceve l'oggetto e e si ferma con un errore.

function onEdit(e) {
  const COL_CODICE = 1;  // colonna A: dove scrive il lettore
  const COL_ORA = 2;     // colonna B: data e ora della lettura
  const r = e.range;
  const foglio = r.getSheet();
  if (foglio.getName() !== 'Letture') return;
  if (r.getColumn() > COL_CODICE || r.getLastColumn() < COL_CODICE) return;

  const n = r.getNumRows();
  const codici = foglio.getRange(r.getRow(), COL_CODICE, n, 1).getValues();
  const celleOra = foglio.getRange(r.getRow(), COL_ORA, n, 1);
  const orari = celleOra.getValues();
  const adesso = new Date();
  let cambiati = false;
  for (let i = 0; i < n; i++) {
    if (r.getRow() + i > 1 && codici[i][0] !== '' && orari[i][0] === '') {
      orari[i][0] = adesso;
      cambiati = true;
    }
  }
  if (cambiati) celleOra.setValues(orari);
}

Lo script legge le celle dal foglio invece di usare e.value, perché la documentazione degli eventi indica che value e oldValue esistono solo quando la modifica riguarda una singola cella: con un incolla di più righe sarebbero vuoti. I limiti dei trigger semplici, dalla guida ufficiale, da conoscere prima di affidarsi allo script:

• Massimo 30 secondi per esecuzione. Leggere e scrivere in un solo blocco, come sopra, tiene lo script veloce anche con molte righe.
• Le modifiche fatte da script o API non lo attivano. Se i codici arrivano nel foglio da un'app, un modulo collegato o un'integrazione che scrive via API, l'ora non viene scritta: serve un'altra soluzione lato integrazione.
• Nessun servizio che richiede autorizzazione, per esempio l'invio di email. Scrivere nel foglio stesso è consentito.

In batch l'ora della macro è l'ora dello scarico

Con la modalità batch il lettore conserva i codici e li digita tutti insieme quando leggi il codice di scarico. La macro e lo script funzionano lo stesso, ma registrano il momento in cui ogni riga arriva nel foglio: cento letture fatte in due ore in magazzino ricevono orari concentrati in pochi secondi. Se ti serve l'ora reale della lettura, l'orario deve salvarlo il lettore stesso. Alcuni modelli con memoria lo fanno (nel manuale cerca "timestamp" o "time stamp"): in quel caso data e ora arrivano insieme al codice, nel formato descritto dal manuale, e se il separatore è configurabile conviene il Tab per averle già in colonne diverse. La macro, a quel punto, va disattivata o fatta puntare a una colonna diversa, per non sovrascrivere nulla. Per l'organizzazione dei giri di conteggio vedi magazzino e inventario.

Quando questa soluzione non basta

• L'orologio è quello del PC (o di Google, per Fogli): se la postazione ha l'ora sbagliata, il registro la eredita.
• Un foglio non è un sistema di tracciabilità. Chiunque può modificare a mano un orario già scritto. Per registri con valore legale o di qualità serve un software che protegga i dati.

Prima di un inventario, verifica nel test del lettore che il codice arrivi completo e seguito dal suffisso giusto: una macro perfetta non rimedia a un lettore che non preme Invio.

Domande frequenti

La macro funziona se il file è chiuso?

No. La macro gira solo con il file aperto in Excel, con le macro abilitate e il foglio giusto attivo mentre leggi.

Il lettore può aggiungere da solo data e ora in emulazione tastiera?

Di norma no: in lettura diretta trasmette solo il codice, con eventuali prefissi e suffissi. L'orario lo aggiunge il foglio o, nei modelli che lo prevedono, la memoria batch.

Fonti e riferimenti

• Evento Worksheet.Change (Excel), Microsoft Learn.
• Proprietà Application.EnableEvents (Excel), Microsoft Learn.
• Funzione ADESSO (NOW), Microsoft Support.
• Rimuovere o consentire un riferimento circolare in Excel, Microsoft Support.
• Simple triggers e restrizioni, Google Apps Script.
• Event objects, evento Edit di Google Fogli, Google Apps Script.