Aggiornamento valori funzione custom

Ho realizzato una funzione VBA su excel, che non richiede immissioni di valori di input.
Effettuando una semplice conta delle celle vuote in una colonna
la funzione e' definita nel seguente modo:
public Function Count_Empty_Cell()
l'ho richiamata in una cella con la seguente stringa:
=Count_Empty_Cell()
il PROBLEMA e' il seguente:

Per Capirci

se io ad esempio nella cella C34 scrivo:
=SOMMA(C1:C32)
poi vado ad esempio sulla terza riga, faccio menu->inserisci righe e nella colonna C aggiungo un valore numerico sulla cella aggiunta, la somma viene aggiornata, cosi' come viene aggiornata se io cancello un valore nelle celle comprese tra la 1 e la 32 della colonna C.

Il problema e' che la funzione da me definita mi da il valore numerico corretto dell'ammontare delle celle vuote, ma solamente appena inserisco la funzione all'interno di qualuque cella, poi se le celle controllate dalla funzione vengono riempite successivamente, il valore totale delle celle vuote non vengono aggiornate
MI serve di sapere come e' possibile aggiornare sempre in tempo reale i valori di una funzione da me definita (come ad esempio avviene quando si fa una funzione di somma di valori).
Per completezza posto l'intera routine scritta da me.

Grazie per il cortese aiuto.

Public Function Count_Empty_Cell()
'System_Row_Title Rappresenta il numero di righe di sistema presenti in un foglio e quindi da sottrarre
'al numero di righe occupate.
System_Row_Title = 3
'nrow ncol sono il numero di righe e il numero di colonna che andra' poi a definire il range variabile
'in funzione del numero di celle occupate
'Last_Row e' la variabile indicante il numero di riga che rappresenta l'ultima cella occupata di un range
Dim nRow As Long, nCol As Long, Last_Row As Long
Dim A_Col_Busy__Cell As Range
Dim Content_Cell
Dim Count_Blanks_Cells
nRow = 1
nCol = 1
'End(xlUp).Row Da il valore dell'ultimo numero di riga occupata del range "B:B", Range("B65536")
Last_Row = Range("B65536").End(xlUp).Row
Last_Row = Last_Row - System_Row_Title
'definisce il range variabile in funzione dell'ultima cella occupata di un'altra colonna
Set A_Col_Busy__Cell = Range(Cells(nRow, nCol), Cells(nRow + Last_Row, nCol))
    For Each Content_Cell In A_Col_Busy__Cell
        'se il valore della cella e' uguale a "" valore vuoto
        If Content_Cell.Value = "" Then
        Count_Blanks_Cells = Count_Blanks_Cells + 1
        End If
    Next Content_Cell
    'La riga che segue serve ad assegnare a questa funzione il valore finale del conto totale delle celle vuote
Count_Empty_Cell = Count_Blanks_Cells
'questa funzione Count_Empty_Cell che non richiede immissione di valori,
'verra' richiamata sul foglio excel inserendo =Count_Empty_Cell() all'interno di una qualunque cella
End Function

Ciao. Non ho nemmeno cominciato a leggere il tuo codice. Anzitutto mi chiedo: cosa aveva la funzione CONTA.VUOTE che non andava?

=CONTA.VUOTE(B:B)

Potresti chiarirci questa cosa?

Originariamente inviato da yronium

Ciao. Non ho nemmeno cominciato a leggere il tuo codice. Anzitutto mi chiedo: cosa aveva la funzione CONTA.VUOTE che non andava?

=CONTA.VUOTE(B:B)

Potresti chiarirci questa cosa?

Premesso:
Non conoscevo che esisteva la funzione excel conta vuote.
Grazie per la info e per la rapida risposta.

Ad ogni modo la mia funzione funziona e da risultato corretto; solo che al variare delle celle occupate non aggiorna automaticamente il valore ma l'aggiornamento deve essere eseguito con CTRL+ALT+F9

Cercando su internet ho trovato cose del tipo:
Application.Calculate
Application.Calculation = xlCalculationAutomatic
ma se le pongo a fine codice non risolvo nulla
sto usando office 2000
pare che per office 2003 esista un comando del tipo
Application.CalculateFullRebuild

SEMBRA CHE il mancato aggiornamento in tempo reale DIPENDI DA UN BUG DI OFFICE.
Correggetemi se sbaglio.

Ad ogni modo grazie al tuo suggerimento CONTA.VUOTE avrei al momento risolto il problema, tuttavia il fatto che un valore di una funzione custom non venga aggiornato automaticamente al variare dell'occupazione delle celle sembra una cosa paradossale.

Ad ogni modo visto che potrei sviluppare funzioni in futuro di altro tipo, simili o diverse da questa, vorrei capire cosa e' che non va.

Per questo vi o ti pregherei di seguire il seguente esempio, fare una colonna A con i seguenti valori:

RAVEL MAURICE
RAVEL MAURICE
RAVEL MAURICE
RAVEL MAURICE

J. S. BACH
J. S. BACH
J. S. BACH
J. S. BACH
J. S. BACH
J. S. BACH
J. S. BACH
J. S. BACH
J. S. BACH
J. S. BACH
J. S. BACH
J. S. BACH
J. S. BACH
J. S. BACH
J. S. BACH
J. S. BACH
J. S. BACH
J. S. BACH
J. S. BACH
J. S. BACH
J. S. BACH

J. S. BACH
J. S. BACH
J. S. BACH
J. S. BACH
J. S. BACH
J. S. BACH
J. S. BACH
J. S. BACH

J. S. BACH ( DISC 1 )
J. S. BACH ( DISC 1 )
J. S. BACH ( DISC 1 )
J. S. BACH ( DISC 1 )
J. S. BACH ( DISC 1 )
J. S. BACH ( DISC 1 )


di fianco nella colonna b poni questi altri valori

BOLERO
CONCERTO PIANOFORTE
CONCERTO PIANOFORTE
CONCERTO PIANOFORTE
MAGNIFICAT
MAGNIFIFICAT ANMA MEA
ET EXSULTAVIT SPIRITUS MEUS
QUIA RESPEXIT HUMILITATEM
OMNES GENERATIONES
QUIA FECIT MIHI MAGNA
ET MISERICORDIA
FECIT POTENTIAM
DEPOSUIT POTENTES
ESURIENTES IMPLEVIT BONIS
SUSCEPIT ISRAEL
SICUT LOCUTUS EST
GLORIA PATRI
MAGNIFICAT ANIMA MEA
QUIA RESPEXIT HUMILITATEM
QUIA FECIT MIHI MAGNA
ET MISERICORDIA
FECIT POTENTIAM
DEPOSUIT POTENTES
SUSCEPIT ISRAEL
GLORIA PATRI
SICUT ERAT
ORGAN FAVOURITES
PRELUDE & FUGUE IN E FLAT MAJOR
PRELUDE & FUGUE IN E FLAT MAJOR
PASTORALE IN F MAJOR
TOCCATA IN D MINOR
PRELUDE & FUGUE IN D MAJOR
PRELUDE & FUGUE IN D MAJOR
PRELUDE & FUGUE IN E MINOR
PRELUDE & FUGUE IN E MINOR
TOCCATA AND FUGUE IN D MINOR
PRELUDE AND FUGUE C DUR
WACHET AUF UNS DIE STIMME
WO SOLL ICH FLIEHEN HIN
WER NUR DEN LIEBEN GOTT LASST WALTEN
MEINE SEELE ERHEBET DEN HERRN
ACH BLIEB BEI UND. HERR JEUS CHRIST



ponendo la mia formula nel modulo cosi' come l'ho postata.
Richiamando la mia formula su una qualunque cella ad esempio la C5 scrivendo al suo interno la seguente sintassi:
=Count_Empty_Cell()

noterai che in quella cella sara' visualizzato il valore delle celle vuote
ma se successivamente al valore calcolato, procedo col riempire una cella della colonna A che prima era vuota o ne cancello 5 nella colonna A il valore non viene aggiornato a meno che non faccia CTRL+ALT+F9

Posso sapere cosa e' che non va????? I risultati non vengono aggiornati mentre vengono sempre aggiornati se eseguo una formula qualunque, compresa quella =CONTA.VUOTE(A4:A8)

Vi ringrazio se vorrete rivolvere il mio quesito.

Originariamente inviato da maui.maui

Posso sapere cosa e' che non va????? I risultati non vengono aggiornati mentre vengono sempre aggiornati se eseguo una formula qualunque, compresa quella =CONTA.VUOTE(A4:A8)

Dunque, ho fatto la tua prova (manca la dichiarazione della variabile System_Row_Title nel codice che hai postato) ed effettivamente la tua funzione personalizzata non viene ricalcolata. In effetti io non sapevo nemmeno la differenza tra F9 e Ctrl+Alt+F9.

Così, cercando un po' sul web ho trovato dapprima questo articolo, nel quale quasi per caso ho notato l'affermazione "Volatile functions are always calculated". Da lì sono giunto a quest'altro articolo che contiene la spiegazione, e nella quarta riga c'è la soluzione al tuo quesito.

Quindi il tuo codice modificato (testato e funzionante) da incollare in un modulo, è il seguente:

Option Explicit


Public Function Count_Empty_Cell()
    Dim System_Row_Title As Long
    ' rende la funzione "volatile", venendo così sempre eseguita
    Application.Volatile True
    
    'System_Row_Title Rappresenta il numero di righe di sistema presenti in un foglio e quindi da sottrarre
    'al numero di righe occupate.
    System_Row_Title = 3
    'nrow ncol sono il numero di righe e il numero di colonna che andra' poi a definire il range variabile
    'in funzione del numero di celle occupate
    'Last_Row e' la variabile indicante il numero di riga che rappresenta l'ultima cella occupata di un range
    Dim nRow As Long, nCol As Long, Last_Row As Long
    Dim A_Col_Busy__Cell As Range
    Dim Content_Cell
    Dim Count_Blanks_Cells
    nRow = 1
    nCol = 1
    'End(xlUp).Row Da il valore dell'ultimo numero di riga occupata del range "B:B", Range("B65536")
    Last_Row = Range("B65536").End(xlUp).Row
    Last_Row = Last_Row - System_Row_Title
    'definisce il range variabile in funzione dell'ultima cella occupata di un'altra colonna
    Set A_Col_Busy__Cell = Range(Cells(nRow, nCol), Cells(nRow + Last_Row, nCol))
        For Each Content_Cell In A_Col_Busy__Cell
            'se il valore della cella e' uguale a "" valore vuoto
            If Content_Cell.Value = "" Then
            Count_Blanks_Cells = Count_Blanks_Cells + 1
            End If
        Next Content_Cell
        'La riga che segue serve ad assegnare a questa funzione il valore finale del conto totale delle celle vuote
    Count_Empty_Cell = Count_Blanks_Cells
    'questa funzione Count_Empty_Cell che non richiede immissione di valori,
    'verra' richiamata sul foglio excel inserendo =Count_Empty_Cell() all'interno di una qualunque cella
End Function

Le modifiche sono quelle in rosso, così potrai servirtene nelle future programmazioni. Per adesso sai che puoi anche utilizzare la semplice funzione CONTA.VUOTE che ti ho già segnalato.

Spero sia utile. Ciao.

Sono molto felice di essermi iscritto a questa comunita', (in genere non lo faccio mai perche' la registrazione richiede sempre troppo tempo, ma in questo caso e' stata sufficientemente celere). Prima di tutto ringrazio per la tempestivita' delle risposte e per la disponibilita' dimostrata alle sperimentazioni.

La tua risposta e' stata davvero utilissima e illuminante.

Io sono nuovo di vba vengo dallo shell script decisamente piu' umano e meno criptico pochi comandi che fanno molte cose, con molte meno cose da imparare a memoria e piu' intuitivo; qui invece abbiamo, mi pare, un comando per ogni cosa specifica, che o la sai o non la sai, se non la sai sei a un punto morto, aggiramento dell'ostacolo prossimo allo ZERO.

A parte questa piccolo sfogo ti ringrazio ancora.

Volevo al momento chiederti una sola piccola delucidazione.
Noto a inizio codice la seguente stringa

Option Explicit
io l'ho messa nel mio codice ma se la inserisco prima di
Public Function Count_Empty_Cell() la tale stringa mi va a finire sulla funzione precedente identificata sull'editor vba dalla riga posta a fine codice.
End Function
della funzione precedente.
Ho letto che Option Explicit serve a imporre di dichiarare tutte le variabili presenti nel codice in effetti avevo dimenticato di dichiarare
Dim System_Row_Title As Long

Sono molto felice di essermi iscritto a questa comunita', (in genere non lo faccio mai perche' la registrazione richiede sempre troppo tempo, ma in questo caso e' stata sufficientemente celere). Prima di tutto ringrazio per la tempestivita' delle risposte e per la disponibilita' dimostrata alle sperimentazioni.

La tua risposta e' stata davvero utilissima e illuminante.

Io sono nuovo di vba e autodidatta per hobby. Vengo dallo shell script decisamente piu' umano e meno criptico pochi comandi che fanno molte cose, con molte meno cose da imparare a memoria e piu' intuitivo; qui invece abbiamo, mi pare, un comando per ogni cosa specifica, che o la sai o non la sai, se non la sai sei a un punto morto, aggiramento dell'ostacolo similmente prossimo allo ZERO.

A parte questa piccolo sfogo ti ringrazio ancora.

Volevo al momento chiederti una sola piccola delucidazione.
Noto a inizio codice la seguente stringa

Option Explicit

io l'ho messa nel mio codice ma se la inserisco prima di
Public Function Count_Empty_Cell() la tale stringa mi va a finire sulla funzione precedente identificata sull'editor vba dalla riga posta a fine codice dopo l'istruzione.
End Function
della funzione precedente.

Ho letto che Option Explicit serve a imporre sempre di dichiarare tutte le variabili presenti nel codice altrimenti da errore. Io in effetti avevo dimenticato di dichiarare
Dim System_Row_Title As Long

Ad ogni modo per ora ho evitato appunto di inserire il comando Option Explicit
E tutto funziona a dovere.

1) Quale l'utilita' di option explicit 2) Perche' e se e' corretto che nell' editor vba, option explicit vba mi va a finire sulla funzione precente

Grazie per avermi aiutato anche per i futuri script, rispondimi solo se hai tempo, anche in breve.

Grazie

Ciao maui.maui

L'option explicit ti obbliga a dichiarare le variabili ed evidenzia errori nello script
In VBA le variabili non dichiarate rischiano in alcuni casi di fare errori
è di buona norma usarlo e esegui sempre un Debug (nelle'editor VBA Strumenti-> CompilaVBAProjesct) che evidenza tutti i possibili errori ,if senza End if, variabili dichiarate che prendono un valore non consono ecc.

Ciao