Dare un Numero ID identificativo delle righe di un foglio in Calc

[nickGiard]

Buon giorno cari colleghi
vorrei porre come argomento di discussione e di stimolo una problematica che sembra banale: come assegnare via macro in una colonna di un foglio di lavoro una serie di numeri interi univoci che rappresentino le righe stesse con dati, ossia assegnare degli ID non modificabili dall'utente.
Bene, un semplice ciclo può inizialmente popolare tale colonna delle righe con dati.
E se l'Utente fa un copia incolla di alcune righe, copierebbe anche il relativo ID che non sarebbe più unico. Come correggerlo?
E se l'Utente eliminasse alcune righe, con che criterio assegno gli ID ad eventuali nuove righe utilizzate?
Ovviamente La macro partirà con un evento sulle righe interessate, tipo Doppio Click o equivalenti.

Grazie in anticipo per i suggerimenti.
Nicola

[patel]

Ma a cosa può servire ? è solo un esercizio o può essere utile ?

[nickGiard]

salve Patel. grazie intanto per l'intervento.
supponi di avere dei dati registrati in varie righe. Ad esempio delle Voci di Lavori complete di Articolo, Quantità Prezzo ed Importi (casualmente è il mio software di Computo Metrico).
Ora questa Voce necessita di essere elaborata in un secondo foglio accessorio, ad esempio per analizzare su più righe le componenti che determinano il Prezzo.
Una macro attivata da un doppio click copia incolla la Voce nel secondo foglio, con la quale faccio le mie analisi ed ottengo un risultato del Prezzo.
Ora con un doppio click tale prezzo deve tornare sulla Voce originaria per essere scritto.
Come individuare tale Voce Originaria ? La risposta più ovvia sarebbe cercare l'Articolo, ma se l'Articolo non è univoco ? Se la Voce originaria è stata nel frattempo duplicata? o e' stata rimossa ? Ecco l'idea dell' ID , ma questo avrebbe le stesse sorti dell'Articolo, a meno di soddisfare le condizioni indicate nel post.
Nicola

[Gaetanopr]

Ciao, risulta difficile la logica di quello che chiedi non avendo un esempio pratico a disposizione.

[nickGiard]

Ciao Gaetanopr, hai ragione a chiedere un esempio pratico, in effetti nel post parlavo di problematica piuttosto che di una specifica correzione di una macro. Provo pertanto a spiegarmi meglio.
Nei fogli elettronici quando voglio usare un dato presente in un altro foglio, nella barra della formula inizio con = e poi mi sposto nell'altro foglio selezionando la cella con il dato da riportare, così da creare il classico collegamento tra celle. Cambiando il valore nel secondo foglio, automaticamente me lo ritrovo nel primo, e forse per questo i fogli elettronici si chiamano anche spreadsheet. Questo meraviglioso meccanismo avviene istantaneamente e ovviamente a cascata per tutte le celle collegate, dando sensazione di dinamicità, ma creando una struttura instabile nel caso si immetta un dato errato, questo si trasmette a cascata su tutto l'ambiente, per cui devo evitare questa tecnica e ricorrere ad in copia incolla da macro.
Pochè io tratto di contabilità di cantieri edili, questa si basa su Voci di lavorazioni presenti in un primo foglio che rappresenta il contratto, e su più fogli successivi che rappresentano le quantità eseguite delle varie Voci alla scadenza di determinati periodi.
Ad esempio per una previsione di Voce di Scavo di 1.000 mc al prezzo unitario di 8 € e quindi per Importo previsto di 8.000€ scritta in una riga, contabilizzo su più righe in un secondo foglio 400 mc per il primo avanzamento, ed in un terzo foglio altri 500 mc per il secondo avanzamento. Per tenere traccia sintetica di questi parziali, li devo RIPORTARE in linea della prima Voce di previsione, così' da vedere in celle a seguire i vari mc 1.000, 400 e 500 per sapere le disponibilità residue.
Quindi per me il nocciolo della questione è trovare un meccanismo logico (di cui poi sviluppo la macro) che consenta partendo dalla Voce nel secondo o terzo foglio di individuare la Voce corrispondente nel primo foglio e quindi poter scrivere le quantità calcolate.
Spero di non essere stato troppo prolisso.
Grazie.
P.S. Questa problematica viene affrontata dai vari prodotti commerciali di computo metrico in ambiente Data Base, dove la Voce è un record dotata automaticamente di ID, ma io mi sono intestardito a volerla affrontare su foglio elettronico.

[patel]

Pe gente non del mestiere è difficile comprendere bene quanto hai esposto, un file di esempio commentato forse aiuterebbe.

[patel]

La Voce di Scavo di 1.000 mc avrà un numero di commessa/ordine, non basta che le voci negli altri vfogli abbiano lo stesso numero di commessa/ordine?

[nickGiard]

Grazie patel, la gestione di un preventivo di lavori e della conseguente contabilità di cantiere è alquanto articolata.
In termini astratti in ambiente Data Base, una Voce di preventivo è assimilabile ad una Tabella GENERALE con ogni record con un suo ID identificativo fisso, alla quale si relazionano all'occorrenza altre tabelle accessorie con ulteriori dati e che contengono un campo ID identico al primo e che consente di individuare a quale Voce/Record originaria assegnare i nuovi dati a completamento della stessa.
Si tratta ora di individuare una logica analoga trasferita ai fogli elettronici, gestita ovviamente da macro, nella quale la problematica principale è la salvaguardia da modifiche o manomissioni del numero ID delle righe del foglio PRINCIPALE e al quale i record dei fogli accessori possono riferirsi.
Allego un file di esempio con due Fogli, uno PRINCIPALE che contiene le Voci con alcuni dati quali QUANTITA DI PREVENTIVO ed ID, e il foglio accessorio ESEGUITE con le Voci effettivamente eseguite nel cantiere con il relativo ID e con le relative computazioni, che poi vengono riportate a fianco delle Voci Principali nella colonna QUANTITA ESEGUITE.
Via macro riesco a copiare i valori calcolati dal secondo foglio al primo individuando i corrispondenti ID, ma se l'utente nel PRINCIPALE mi cancella l'ID , o lo duplica copiando le Voci, il meccanismo non funziona più. Quindi si ritorna sempre allo stesso problema, come assegnare e rendere l'ID nel foglio PRINCIPALE FISSO ED UNIVOCO, gestito eventualmente da macro.
Qualche idea ?
Nick

[charlie]

Capisco il motivo di una ricerca multipla quando non si ottengono risposte soddisfacenti, ma il crosspopsting andrebbe comunque segnalato https://forum.openoffice.org/en/forum/v ... p?t=111611

[nickGiard]

grazie charlie per il tuo intervento, anche se non conosco il significato della parola crosspopsting.
Il mio intento in questo caso non è ottenere o validare linee di codice non funzionanti, ma stimolare una discussione con la fantasia e le proprie abilità degli utenti esperti per cimentarsi ad una soluzione teorica ma fattibile di una problematica che ritengo non banale.
Se argomenti di questo tipo non sono previsti in questo forum, che tra l'altro nel passato mi è stato molto prezioso nello sviluppo delle mie app, ne prendo atto e sospendo ulteriori interventi.
Nick

[charlie]

Ciao, nessun problema per gli argomenti, spero tu possa trovare interlucotori validi.

[Gaetanopr]

Ciao, la migliore soluzione è quella da te proposta, cioè l'uso di un database, se proprio vuoi utilizzare un foglio di calcolo, puoi sfruttare il sistema degli id da te descritto, ti allego il file con una macro che incrementa l'id nella colonna C e un contatore in A1 questo serve ad avere una chiave incrementale a prescindere da quanto indicato nella C(Vedi possibili eliminazioni) se ad esempio inserisci un articolo con id 5 e poi lo cancelli il prossimo id sarebbe nuovamente 5 invece come nei database grazie al contatore sarà 6.
Basta inserire un CODE nella colonna B che la macro si aziona e compila l'id.
Naturalmente la colonna C deve essere protetta in modo da non manomettere i dati e in modo da evitare i copia incolla duplicando l'id.
Nell'esempio ho protetto solo alcune righe della colonna C e la cella A1.

[nickGiard]

Grazie Gaetanopr per la tua soluzione che mi sembra anche di buona didattica, la cui macro ID viene attivata dall'evento 'Contenuto modificato' del foglio PRINCIPALE, allo scrivere qualcosa in qualunque cella che poi viene controllato dalla macro e passato solo se riguarda la col. B (e non oltrepassare la riga successiva all'ultima, non essenziale).
L'idea di un contatore 'protetto' e di una colonna ID 'protetta' (potrebbe essere nascosta) e disponibile alla scrittura una sola volta solo a valore iniziale vuoto, impedisce la manomissione diretta di questi valori una volta determinati. Il foglio ha inoltre il blocco di inserire e rimuovere righe sempre a salvaguardia dell'univocità e persistenza dell'ID.
Questa soluzione appare idonea e leggera nell'ipotesi di inserimento dati piuttosto ordinato, e supera il problema dell'eliminazione di righe.
Nella mia app avevo implementato qualcosa di simile, la gestione dell'ID era affidata all'evento Doppio Click che l'Utente deve comunque attivare sulla singola Voce per fare varie elaborazioni, ad es. calcolare il prodotto via macro della Quantità per il Prezzo ed ottenere l'Importo, ed in quella fase veniva determinato e scritto l'ID.
Il problema è nato che il mio Utente nel fare un Preventivo deve essere libero di duplicare una Voce in più punti, può fare il copia incolla di una o più righe Voci, e quindi a questo punto ricopia anche il benedetto ID !!! . E' pur vero che c'e' la mia raccomandazione di non ricopiare righe ma solo la parte della Voce escludendo l' ID (tra l'altro nascosto e fuori zona ma comunque in riga), ma prima o poi viene ricopiata anche la riga intera, per cui occorre intervenire manualmente a sistemare .
Ne consegue che suppongo l' ID sebbene scritto nella sua colonna, debba essere determinato in un altro ambiente non accessibile all'Utente ...
Nick

[Gaetanopr]

Il problema è nato che il mio Utente nel fare un Preventivo deve essere libero di duplicare una Voce in più punti, può fare il copia incolla di una o più righe Voci, e quindi a questo punto ricopia anche il benedetto ID !!! . E' pur vero che c'e' la mia raccomandazione di non ricopiare righe ma solo la parte della Voce escludendo l' ID (tra l'altro nascosto e fuori zona ma comunque in riga), ma prima o poi viene ricopiata anche la riga intera, per cui occorre intervenire manualmente a sistemare .
Ne consegue che suppongo l' ID sebbene scritto nella sua colonna, debba essere determinato in un altro ambiente non accessibile all'Utente ...
Nick

Se l'utente deve avere la possibilità di poter fare copia/incolla l'unica soluzione che mi viene in mente è un copia/incolla personalizzato, fermo restando che il foglio deve rimanere sempre PROTETTO per salvaguardare l'id ho aggiunto una macro associata all'evento doppio click(che forse già tu utilizzi, quindi dovresti rivedere pure questo aspetto).
La macro effettua una copia di quello memorizzato nell'area degli appunti, quindi all'utente basta fare copia sulle celle da copiare, invece di fare incolla in quanto si riceverebbe un messaggio di impossibilità a modificare le celle protette, l'utente deve fare doppio click sulla cella di destinazione, a questo punto un messaggio chiede se la copia è avvenuta correttamente, l'utente potrebbe aver sbagliato nella selezione o sbagliato la cella di destinazione, se si risponde No tutto torna come prima altrimenti la copia viene confermata e viene aggiornato il contatore, il nuovo Id o i nuovi id in caso di copia di più righe.

Allego nuovo file con le due macro, prova ad effettuare la copia di una o più righe.

[nickGiard]

Grazie Gaetanopr per l'esempio della funzione dispatch CopiaIncolla modificata per la sola funzione paste, che funziona perfettamente secondo le assunzioni che hai posto, e che sprotegge/riprotegge il foglio, e la validazione dell'operazione con la possibilità di Undo.
L'azione del doppio click, quella che uso in modo generale nella mia app per attivare le varie funzionalità, va assolutamente bene, ed è in conseguenza di quella che quando serve leggo o determino l'ID .
La mia app gestisce molte operazioni ed attività del Computo, pertanto la protezione con il blocco di celle anche lasciando libere le righe, in questo caso non mi è funzionale, e devo pensare ad una soluzione alternativa.
Ricapitolando le caratteristiche dell' ID :
-viene generato/letto/verificato/corretto a seguito di un evento che nel mio caso è il doppio click in una cella qualsiasi della riga relativa;
-per quanto sopra l'ID di righe ricopiate viene automaticamente corretto al seguito del primo doppio click sulla riga, se righe eliminate tale ID non viene più ripreso;
-per quanto sopra l' ID modificato/cancellato dall'Utente, viene ripristinato corretto al primo doppio click sulla riga
-la zona di gestione ID potrebbe essere in un foglio separato, questo sì protetto e non accessibile, anche nascosto.
Pertanto sono portato a ritenere che l' ID, assegnato in modo consequenziale a seguito di un numeratore come detto, sia legato a qualche proprietà unica della riga interessata, e lo segua anche se la riga viene spostata o duplicata salvo correzioni...

Tanto per mantenere viva la conversazione e l'interesse (spero), ma senza voler condizionare altre idee, io ancorerei l' ID al nome della relativa cella AbsoluteName, che può essere letto come link in una cella ausiliaria che ad esso si riferisce, e al fianco di questa scrivere l' IDAusiliario assegnato.
Allo spostare della riga della Voce, il riferimento nella cella ausiliaria cambia di conseguenza, e partendo con un Doppio Click sull' ID della Voce di cui conosco AbsoluteName e facendo una ricerca tra gli indirizzi delle celle ausiliarie troverei quella con pari link, e potrei così leggere il valore dell' IDAusiliario di fianco che resta salvo da qualsiasi manomissione e disponibile per eventualmente correggere quello della Voce.
In caso di duplicazione della Voce, non verrebbe trovato l' AbsoluteName tra gli IDAusiliari e quindi la macro creerebbe l' IDAusiliario successivo da ricopiare poi come ID della Voce copiata, lo stesso per una nuova Voce.

Nel file allegato, dove ho disabilitato la Sub ID, ho indicato un esempio di quello che ho detto, nel quale potrei inserire una sub di gestione se ritenuta una soluzione valida ed interessante.
Nick

[Gaetanopr]

Tanto per mantenere viva la conversazione e l'interesse (spero), ma senza voler condizionare altre idee, io ancorerei l' ID al nome della relativa cella AbsoluteName, che può essere letto come link in una cella ausiliaria che ad esso si riferisce, e al fianco di questa scrivere l' IDAusiliario assegnato

Sinceramente questo sistema non mi piace molto, ho pensato ad un altro stratagemma, l'uso dei nomi definiti.
Puoi assegnare ad ogni articolo un nome definito del tipo ID_1, ID_2 e così via, in questo modo non si necessita di un contatore e nemmeno di memorizzare gli ID da qualche parte nascosta.
Nell'esempio che allego in realtà dopo aver assegnato ad ogni articolo "COPIATO"(ho solo variato la macro che copia se va bene puoi modificare la macro ID) il relativo nome definito, in un foglio chiamato Riepilogo riporto tutti gli articoli con lo stesso ordine del foglio PRINCIPALE con l'aggiunta del campo ID nella colonna C.
Il foglio PRINCIPALE non è protetto, il foglio Riepilogo invece si.
Puoi aggiungere/togliere righe questo non influirà sul corretto ID associato ad ogni articolo.
Il foglio Riepilogo può servire per una ripresa degli articoli dato che contiene il nome dell'ID, volendo puoi modificare la macro in modo da avere anche uno storico degli ID eliminati, al momento elenca solo quelli presenti nel foglio PRINCIPALE, si possono aggiungere alcuni controlli in modo da non copiare sopra articolo già presenti e altro, questa intanto è un'idea e un punto di partenza per creare degli ID univoci e non manomissibili.

[nickGiard]

Ciao Gaetanopr, hai avuto una splendida idea !!! in Effetti il NamedRange e' una caratteristica propria della cella, è basata su AbsoluteName come il link che dicevo, ma al posto di essere da un'altra parte su un'altra cella come il link, è impostato proprio direttamente sulla stessa cella ID interessata e quindi aderisce molto meglio al concetto di incapsulamento, e ne segue meglio le sorti come la duplicazione, l'eliminazione, lo spostamento ...
Ho pensato di fare una tabella che al doppio click su una cella ne crea l'ID ed assegna il NamedRange il cui Nome è proprio l'ID preceduto da _, o scritto un ID seleziona la cella relativa se assegnato. Un contatore nel commento della cella intestazione consente di assegnare il nuovo ID incrementale.

Data una cella, ovviamente e' importante sapere se ad essa è già stato assegnato un Nome prima di un nuovo assegnamento. Non sono riuscito a trovare una funzione che possa rispondere, ho dovuto creare un ciclo (evidenziato con '???) su TUTTI i NamedRanges del foglio e recuperato il Content, ossia l'AbsoluteName controllare se corrisponde a quello della cella. Nella mia App posso avere anche 1000 ID, e ciclarli ogni volta tutti mi sembra poco efficiente, hai qualche idea ??

Ecco il codice abbastanza testato, che trovi nel file allegato funzionante:

Codice: Seleziona tutto

Function OnDoppioClick(Optional oEv)
	Dim oActCell : oActCell = ThisComponent.CurrentSelection
	' Scrivi un Numero in B2 e Doppio Click si posiziona su oID
	If oActCell.CellAddress.Row = 1 And oActCell.CellAddress.Column = 1 Then
		Dim oIdCC : oIdCC = IDNameSetGet(Nothing, oActCell.String)
		ThisComponent.CurrentController.Select(oIdCC)
	' Doppio Click su cella Vuota, o con ID, o dopo eliminazione di Riga, Crea ID
	ElseIf oActCell.CellAddress.Row > 3 And oActCell.CellAddress.Column = 2 Then
		Dim sID$ : sID = IDNameSetGet(oActCell)
		oActCell.SpreadSheet.getCellByPosition( 1, 1).String = sID
		ThisComponent.CurrentController.Select(oActCell)		
	End If
	OnDoppioClick = True
End Function ' OnDoppioClick

' ---------------------------------------
Function IDNameSetGet(oIdCC, Optional sID$)  ' oIdCC, Missing -> sID e lo scrive ; Nothing, sID -> oIdCC
	' Assegna NamedRange = "_" & sID alla cella di oIdCC valore sID, sIDNext in commento di SOMM_ID
	' Il Nome della cella NON puo' iniziare con un Numero e neppure assomigliare ad A1
	' Se si elimina una riga, aNamedRanges.getByName.Content ha "#REF!"

	'** MyErrorMsg("AppDoClick.IDNameSetGet") ' da ripristinare in Edil
	'** Dim oWb : oWb = goLavoroOds
	Dim oWb : oWb = ThisComponent ' **
	' oSOMM_ID nel Commento ha IDNext
	Dim oSOMM_ID : oSOMM_ID =  oWb.NamedRanges.getByName("SOMM_ID").getReferredCells().getCellbyPosition(0, 0)
	Dim oSh : oSh = oSOMM_ID.SpreadSheet
	Dim aNamedRanges : aNamedRanges = oSh.NamedRanges ' >>> NamedRanges del Foglio
	Dim aCellsNames : aCellsNames = aNamedRanges.getElementNames()
	Dim sIdNext$ : sIdNext = oSOMM_ID.Annotation.String
	If sIdNext = "" Then GoTo Exi ' >>>
	Dim i%, sCellName$, oNameRg, oCellN
	
	' oIdCC -set-> sID$
	If IsMissing(sID) Then ' oIdCC NO Nothing
		'** oIdCC = Rg.OffsetTo(oIdCC, oSOMM_ID, "C")
		Dim sIDPresente$ : sIDPresente = oIdCC.String
		' Cella con ID : Controllo sIDPresente sia uguale a "_" & Nome della Cella, altrimenti sIDPresente e' manomesso
		If sIDPresente <> "" And aNamedRanges.hasByName("_" & sIDPresente) Then
			oNameRg = aNamedRanges.getByName("_" & sIDPresente)
			If InStr(oNameRg.Content, "#REF!") = 0 Then ' Riga NON Eliminata
				Dim oIDLetto : oIDLetto = oNameRg.ReferredCells.getCellbyPosition(0, 0)
				If oIDLetto.AbsoluteName = oIdCC.AbsoluteName Then IDNameSetGet = sIDPresente : GoTo Exi: ' >>>
			End If
		End If
		
		' Cella Vuota : per cella vuota oIdCC Trova se esiste il suo NamedRange che avra' quindi sID, altrimenti lo assegna
		Dim bIDHasCellName As Boolean
		' ??? Parte pesante da ottimizzare --> Trovare se esiste il CellName di una cella
		For i = 0 To UBound(aCellsNames)
			sCellName = aCellsNames(i) ' "_" & sID
			If Left(sCellName, 1) <> "_" Then GoTo Nex
			oNameRg = aNamedRanges.getByName(sCellName)
			If InStr(oNameRg.Content, "#REF!") Then  GoTo Nex ' Riga Eliminata da Utente
			oCellN = oNameRg.ReferredCells.getCellbyPosition(0, 0)
			oCellN.String = Mid(sCellName, 2) ' Visto che ci sono confermo sID
			If oCellN.AbsoluteName = oIdCC.AbsoluteName Then bIDHasCellName = True : Exit For
Nex:
		Next ' ???
		' Cella Vuota senza ID: lo crea con valore sIdNext
		If Not bIDHasCellName Then
			Dim sNIdNext$ : sNIdNext = "_" & sIdNext
			oIdCC.String =  sIdNext
			If aNamedRanges.hasByName(sNIdNext) Then  aNamedRanges.removeByName(sNIdNext)
			aNamedRanges.addNewByName(sNIdNext, oIdCC.AbsoluteName, oIdCC.CellAddress, 0)
			sIdNext = Trim("" & (1 + CInt(sIdNext))) 
			oSh.Annotations.insertNew(oSOMM_ID.CellAddress, sIdNext)
		End If
		IDNameSetGet = oIdCC.String
	
	'sID$ -get-> oIdCC	
	ElseIf aNamedRanges.hasByName("_" & sID) Then
		oNameRg = aNamedRanges.getByName("_" & sID)
		If InStr(oNameRg.Content, "#REF!") = 0 Then ' Riga NON Eliminata
			oIdCC = oNameRg.ReferredCells.getCellbyPosition(0, 0)
			IDNameSetGet = oIdCC
		End If
	End If

Exi:	
	'** MyErrorMsg()
End Function ' IDNameSetGet

Come debolezza resta il fatto che un Utente potrebbe accedere liberamente al menù dei Nomi e creare facilmente danni, mentre la mia ipotesi iniziale di un foglio nascosto e completamente protetto da password, che contenga la colonna degli IDAusiliari assegnati con a fianco i relativi link alle celle ID gestite dall'Utente mi sembrerebbe più sicura.

[Gaetanopr]

Data una cella, ovviamente e' importante sapere se ad essa è già stato assegnato un Nome prima di un nuovo assegnamento. Non sono riuscito a trovare una funzione che possa rispondere, ho dovuto creare un ciclo (evidenziato con '???) su TUTTI i NamedRanges del foglio e recuperato il Content, ossia l'AbsoluteName controllare se corrisponde a quello della cella. Nella mia App posso avere anche 1000 ID, e ciclarli ogni volta tutti mi sembra poco efficiente, hai qualche idea ??

Si devi farlo tramite ciclo di tutti gli elementi, 1000 ID non sono tanti anzi...non credo che la macchina possa impiegare più di qualche secondo quindi penso sia un buon compromesso.

Come debolezza resta il fatto che un Utente potrebbe accedere liberamente al menù dei Nomi e creare facilmente danni, mentre la mia ipotesi iniziale di un foglio nascosto e completamente protetto da password, che contenga la colonna degli IDAusiliari assegnati con a fianco i relativi link alle celle ID gestite dall'Utente mi sembrerebbe più sicura.

Come detto nel mio primo messaggio la migliore soluzione sarebbe l'uso di un database, non credo proprio che con la colonna degli IDAusiliari e l'AbsoluteName a fianco possa tu raggiungere prestazioni migliori o aumentare l'efficienza e la sicurezza, pensa alla cancellazione di una riga e il dover aggiornare tutti gli AbsoluteName a cascata, un lavoraccio.
Ti allego un database con una tabella dove puoi aggiungere eliminare e modificare i record, si può creare un form con un pulsante che duplica il record corrente tramite una semplice macro, puoi stabilire come deve essere la form, di solo INSERT oppure prevedere pure il DELETE e/o l'UPDATE.
Non devi prenderti cura dell'ID farà tutto il motore del database.
Tramite una sorgente dati avrai un foglio PROTETTO su un file CALC alimentato dalla tabella nel database, nell'allegato Riepilogo2.
Dopo aver effettuato modifiche o inserimenti fare aggiorna sul file Calc andando su Dati - Aggiorna area, naturalmente questo passaggio si può automatizzare.

[Gaetanopr]

Ecco il file CALC.

[nickGiard]

Ciao Gaetanopr, grazie per l'esempio su data base. Al riguardo ho due problemi: i miei dati sono sparsi nella colonna e non in ordine come nella tabella del db, e soprattutto NON conosco l'ambiente db
Sono tornato ad analizzare la soluzione di foglio nascondibile e proteggibile che contiene la numerazione originaria progressiva dell' ID e a fianco il link verso gli ID del foglio DATI, foglio DATI gestito dall'utente e sul quale viene scritto automaticamente l'ID corrispondente al seguito del Doppio Click e prelevato dal foglio protetto IDS.
Riassumo la logica che si basa sui link che sono dinamici ed unici in riferimento all' AbsoluteName :
- Al Doppio Click sul foglio DATI su una cella ID leggo il suo .AbsoluteName (corrisponde anche al link di qualsiasi cella che voglia leggere il suo valore);
- Vado nel foglio di supporto IDS e cerco se tale link esiste già come Formula, se lo trovo leggo l'ID a fianco e lo ricopio per sicurezza nell'origine, se non lo trovo sempre nel foglio IDS scrivo alla fine della zona link la nuova formula =AbsoluteName, assegno il nuovo ID consequenziale e lo ricopio nell'origine.
Questa soluzione ha beneficiato della ricerca SearchDescriptor nelle Formule di un Range, cosa scoperta cercando nel web, in quanto non pubblicata nelle API.
Altra debolezza che credo risolta: eliminando la cella o la riga di un ID nel foglio DATI, il link da errore #REF!, che andrebbe bene, ma se inserisco una riga nella zona precedente a quella eliminata, il riferimento viene ripristinato, cosa che devo evitare. Per non dover ciclare ogni volta su tutti i riferimenti, ho messo una banale formula segnalatrice =SOMMA(ZonaLink) che anch'essa dà errore nel caso di un #REF! qualunque , e quindi vado in cerca del #REF! solo in caso di errore tramite ciclo For. Tutto questo attivato dall'evento Formule calcolate del foglio dei link IDS .
Avrei piacere avere riscontro su un tuo test approfondito.

Codice: Seleziona tutto

' ------------------ ASSOCIATO AD DOPPIO CLICK su DATI----------------
Sub getsetID(Optional oCell) As String
	If IsMissing(oCell) Then oCell =  ThisComponent.CurrentSelection
	' Sh DATI AbsoluteName della cella Doppio Click
	Dim sAbsoluteNameDC$ : sAbsoluteNameDC = oCell.AbsoluteName ' Cella del Doppio Click
	
	' Determina in sh IDS la zona di registrazione degli IDS
	Dim oIDS : oIDS = ThisComponent.NamedRanges.getByName("N_ID").getReferredCells()
	Dim shIDS : shIDS = oIDS.SpreadSheet
	Dim rgUsed : rgUsed = shIDS.createCursorByRange(oIDS)
	rgUsed.GotoEndOfUsedArea(True)
	
	Dim rowUsed& : rowUsed =  rgUsed.RangeAddress.EndRow
	Dim rgLinks : rgLinks = rgUsed.getCellRangeByPosition(1, 0, 1, rowUsed)
	' Cerca nella Formula, non riportato nelle API
	Dim oSearchDescriptor : oSearchDescriptor = shIDS.createSearchDescriptor()
	oSearchDescriptor.SearchString = "=" & sAbsoluteNameDC
	oSearchDescriptor.SearchWords = bWhole
	oSearchDescriptor.SearchType = 0 ' Formule
	Dim oFindLink : oFindLink = rgLinks.findFirst(oSearchDescriptor)' IsNull se non trovato
	Dim sID$
	If IsNull(oFindLink) Then
		sID = "" & (rowUsed + 1)
		shIDS.getCellByPosition(0, rowUsed + 1).String = sID
		shIDS.getCellByPosition(1, rowUsed + 1).Formula = "=" & sAbsoluteNameDC
	Else
		sID = shIDS.getCellByPosition(0, oFindLink.CellAddress.Row).String
	End If
	oCell.String = sID	
End Sub ' getsetID

' Il Link #RIF! dovuto alla eliminazione della riga in DATI col. ID,  viene ripristinato 
' se inserisco delle nuove righe, necessita quindi renderlo fisso trasformandolo in stringa.
' Il controllo avviene con una formula banale tipo SOMMA(B:B) che da errore se presente un #RIF!
' Il controllo si attiva con l'evento Formule calcolate del foglio IDS
' ------------------ ASSOCIATO A FORMULE CALCOLATE su IDS --------------
Sub ChkLinkRIF()
	' Determina in sh IDS la zona di registrazione degli IDS
	Dim oIDS : oIDS = ThisComponent.NamedRanges.getByName("N_ID").getReferredCells()
	Dim shIDS : shIDS = oIDS.SpreadSheet
	
	' Cella con formula qualsiasi che mi controlla se da errore
	Dim oRifChk : oRifChk = shIDS.getCellByPosition(2, 1)
	if oRifChk.Formula = "" Then oRifChk.Formula = "= SUM(B:B)"
	If oRifChk.Error = 0 Then Exit Sub '>> ' 477 Tutto a posto
	
	Dim rgUsed : rgUsed = shIDS.createCursorByRange(oIDS)
	rgUsed.GotoEndOfUsedArea(True)
	
	Dim rowUsed& : rowUsed =  rgUsed.RangeAddress.EndRow
	Dim rgLinks : rgLinks = rgUsed.getCellRangeByPosition(1, 0, 1, rowUsed)
	Dim aaLinks : aaLinks = rgLinks.DataArray ' Valori del Link	
	Dim i%, oRif
	For i = 0 To UBound(aaLinks)
		If IsEmpty(aaLinks(i)(0)) Then ' #RIF! in DataArray ha valore Empty
			oRif =  rgLinks.getCellByPosition(0, i)
			oRif.String = "#REF!" ' Consolida con un testo
			If oRifChk.Error = 0 Then Exit Sub '>> ' Errore risolto esce
		End If
	Next
End Sub ' ChkLinkRIF

' ------------ ASSOCIATO AD DOPPIO CLICK su DATI --------------
Function OnDatiDoppioClick(oCellDC)
	Dim oID : oID = ThisComponent.NamedRanges.getByName("ID").getReferredCells()
	If oCellDC.CellAddress.Column <> oID.CellAddress.Column Then Exit Function
	getsetID(oCellDC)
	OnDatiDoppioClick = True ' Termina l'evento
End Function ' OnDatiDoppioClick