Ricalcolo Function VBA | Excel e gli applicativi Office

Ricalcolo Function VBA
di alfrimpa data: 14/04/2015 16:30:57
Ciao a tutti e spero che qualcuno mi sappia dare una spiegazione.

Ho scritto la function qui sotto che funziona bene nel senso che fornisce il risultato atteso.

Il problema è che, al variare dei paramentri su altro foglio, non si aggiorna automaticamente come una qualsiasi funzione di Excel ma per poterlo lo fare devo obbligatoriamente posizionarmi sulla cella premere F2 ed Invio ed a quel punto viene fuori il risultato corretto.

Se necessario posso postare anche il file.

Alfredo

Function prova1(giorno As Integer, turno As String, posto As String)
Dim miorange As Range
Dim nome As String
Set miorange = Worksheets("Foglio1").Range("d6:d100")
stringa = CStr(giorno) & turno & posto
    prova1 = ""
For Each cll In miorange
    If stringa = cll.Value Then
    nome = cll.Offset(0, -2).Value
       prova1 = Trim(prova1 & " " & nome)
    End If
Next cll
Columns("B:B").EntireRow.AutoFit
End Function

COLONNE CON DATI TESTUALI
di antonio (utente non iscritto) data: 14/04/2015 16:41:27
Ciao a tutti,
mi sto creando una lista dei miei album: 1° colonna nome del gruppo, 2° colonna genere, e così via; vorrei riuscire a fare in modo che, pur cambiando l'ordine delle righe, e considerando che la listo che vorrei creare sarà abbastanza lunga, allo stesso gruppo corrisponda sempre lo stesso genere (il contenuto della colonna B segue sempre il contenuto della colonna A, nel file esemplificativo che allego).
Come si potrebbe fare?
Grazie mille

di Mister_x (utente non iscritto) data: 14/04/2015 17:28:07
ciao

inserisci
Application.Volatile
da help
Contrassegna una funzione definita dall'utente come volatile. Una funzione volatile deve essere ricalcolata ogni volta che vengono eseguiti dei calcoli in qualsiasi cella del foglio di lavoro. Una funzione non volatile verrà ricalcolata soltanto quando vengono modificate le variabili di input. Questo metodo ha effetto soltanto all'interno di una funzione definita dall'utente per calcolare una cella di un foglio di lavoro

ciao

Function prova1(giorno As Integer, turno As String, posto As String)
Application.Volatile
Dim miorange As Range
Dim nome As String

di alfrimpa data: 14/04/2015 18:10:28
Grazie Mister_x

Ora tutto funziona perfettamente

A saperle tutte queste cose.......

Alfredo

@ Antonio

Ti sei accodato ad una discussione esistente; ne devi aprire una nuova "tua"

di scossa data: 14/04/2015 19:45:11
cit. alfrimpa: "Ho scritto la function qui sotto che funziona bene nel senso che fornisce il risultato atteso."

Ciao Alfredo,
la tua function non mi convince per più di un motivo, ma prima di esprimermi vorrei avere maggiori dettagli (dove viene utilizzata? una cella? più celle?) e magari un file di prova.

scossa's web site

Se tu hai una mela, ed io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno.
Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)

di alfrimpa data: 15/04/2015 12:01:33
Ciao Scossa e grazie dell’intervento.

Premesso che è pacifico che la “qualità” del codice sia direttamente proporzionale alle conoscenze possedute da colui che l’ha scritto (e le mie sono assolutamente non da programmatore ma da semplice appassionato/autodidatta) per cui è sicuramente migliorabile.

Detto questo passo alla descrizione del file che ho allegato.

La cartella di lavoro contiene due fogli: Foglio1 e Tabelle (in più vi è un foglio nascosto di servizio).

In foglio1 c’è una griglia dove sono elencati (in colonna B) dei nominativi ed in colonna C vi sono delle convalide dati per la scelta del turno e del posto di lavoro.

In colonna D (nascosta) vi è una formula che concatena giorno, turno e posto allo scopo di creare una stringa univoca.

Lo scopo è quello di, dopo aver compilato il foglio1, di inserire nel foglio Tabelle (nella tabella specifica del giorno) il nominativo nella giusta cella (quella ad intersezione tra turno e posto)

Nella Function proposta creo una stringa concatenando gli argomenti della funzione e poi ricerco (con un ciclo For Each) tale stringa nella colonna D.

Quando il dato viene trovato vado a “pescare” il nominativo con un offset

La funzione provvede anche ad accodare i nominativi qualora vi fossero più persone che hanno il medesimo turno e posto.

Ripeto, sicuramente si può fare di meglio ed il codice va sicuramente integrato ancora, ma sono contento comunque di essere riuscito da solo a raggiungere il risultato che mi ero prefisso e questo, per me, è motivo di soddisfazione.

Qualunque suggerimento, consiglio od altro è, ovviamente, ben accetto.

Alfredo

P.S. L’istruzione: “Columns("B:B").EntireRow.AutoFit” in una function ovviamente non ha senso ma era una prova che avevo fatto ed ho dimenticato di cancellarla.

di scossa data: 17/04/2015 09:11:04
cit. X: "P.S. L’istruzione: “Columns("B:B").EntireRow.AutoFit” in una function ovviamente non ha senso ma era una prova che avevo fatto ed ho dimenticato di cancellarla. "

Ciao,

questo era uno dei punti che non mi piaceva. Un altro è l'inserire un range fisso nel codice anziché passarlo come parametro, se ci pensi nessuna funzione nativa di Excel fa una cosa del genere.
Non ho ancora visto il file, spero nel corso del weekend di farlo.

scossa's web site

Se tu hai una mela, ed io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno.
Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)

di alfrimpa data: 17/04/2015 11:37:03
Ciao Scossa

Anche il Range fisso era una prova ed è ovvio che va "dinamicizzato".

Ti ringrazio sin d'ora per il tuo interessamento.

Alfredo

di scossa data: 17/04/2015 11:43:40
cit. alfrimpa: "Anche il Range fisso era una prova ed è ovvio che va "dinamicizzato""

Se lo passi come parametro puoi evitare di rendere volatile la function, in quanto le variazioni nelle celle del range forzano il ricalcolo della udf.

scossa's web site

Se tu hai una mela, ed io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno.
Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)

di alfrimpa data: 17/04/2015 14:13:31
Ciao Scossa

Il problema di passare il Range come parametro risiede nel fatto che le colonne su cui effettuare la ricerca sono una trentina (i giorni del mese) per cui vi sarebbe la necessità di ricordarsi, per un qualsiasi giorno del mese, qual è la colonna da esaminare.

Io avrei pensato ad un Select Case legato al giorno......

Forse questa mia spiegazione non è proprio il massimo ma quando vedrai il file credo che sarà tutto più chiaro.

Un'ultima cosa: questo "lavoro" lo avevo fatto per un utente di altro forum che da alcuni giorni non si fa più sentire ahimè

Alfredo

di scossa data: 17/04/2015 23:19:29
cit. alfrimpa: "Il problema di passare il Range come parametro risiede nel fatto che le colonne su cui effettuare la ricerca sono una trentina (i giorni del mese) per cui vi sarebbe la necessità di ricordarsi, per un qualsiasi giorno del mese, qual è la colonna da esaminare. "

Ho modificato la tua UDF aggiungendo il parametro range.
In B4 del foglio Tabele:: =prova(GIORNO($F$1);A$3;A4;Foglio1!$D$6:$BK$100)
da copiare fino a B26 poi in D4:D26, F4:F26 ed H4:H26.

All'interno del codice il range viene limitato alla sola colonna del giorno.

scossa's web site

Se tu hai una mela, ed io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno.
Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)

Function prova(giorno As Integer, turno As String, posto As String, ByRef miorange As Range)
  Dim nome As String
  Dim cll As Range
  Dim stringa As String
  
  stringa = CStr(giorno) & turno & posto
      prova = ""
  For Each cll In miorange.Offset(0, (giorno - 1) * 2).Resize(, 1)
      If stringa = cll.Value Then
      nome = cll.Offset(0, giorno * -2).Value
         prova = Trim(prova & " " & nome)
      End If
  Next cll

End Function

di scossa data: 19/04/2015 11:27:47
cit. alfrimpa: "...per un utente di altro forum che da alcuni giorni non si fa più sentire ahimè"

E non è il solo, a quanto pare ...

scossa's web site

Se tu hai una mela, ed io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno.
Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)

di alfrimpa data: 19/04/2015 11:53:07
Perdonami Scossa ma ho avuto un periodo un po' incasinato (è un eufemismo).

Al più presto ti darò il dovuto e doveroso riscontro.

Buona domenica.

Alfredo

di alfrimpa data: 19/04/2015 17:50:16
Ciao Scossa.

Ho provato la tua Function ed opera perfettamente (ma su questo non avevo alcun dubbio).

Ti sarei grato se mi spiegassi una cosa che non mi è ben chiara e cioè l'ultimo parametro della UDF: ByRef Miorange as Range (cosa sta a significare ByRef?)

Posto che come parametro passiamo l'intero Range D6:BK100 come fa la funzione a variare la colonna di ricerca al variare del giorno? Apparentemente non si vede un collegamento tra le due cose (almeno io non lo vedo )).

Ti ringrazio tantissimo per i tuoi interventi e la tua disponibilità.

Alfredo

di scossa data: 19/04/2015 20:07:20
cit.: "come fa la funzione a variare la colonna di ricerca al variare del giorno?"

Con l'istruzione miorange.Offset(0, (giorno - 1) * 2).Resize(, 1)

"sposto" il range (Offset(0, (giorno - 1)) di tante colonne quanto è il valore di giorno -1 e lo riduco alla sola prima colonna ( .Resize(, 1))

scossa's web site

Se tu hai una mela, ed io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno.
Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)

di scossa data: 19/04/2015 20:13:22
cit.: "cosa sta a significare ByRef?"

Il discorso è piuttosto complesso. Mi limiterò ad una spiegazione semplificata.

I parametri ad una routine (function o sub che sia) possono essere passati per riferimento (ByRef - default) o per valore (ByVal).
ByRef significa che passi un "riferimento" (un indirizzo che punta alla locazione di memoria della variabile) a quella variabile, ByVal significa che passi il suo valore .

Riferendosi alle variabili "semplici" (non oggetti) la differenza fondamentale è che - passandola ByRef - la routine può modificare direttamente il valore della variabile originale, mentre - passandola ByVal - la routine non può modificare la variabile originale.

Sarà più chiaro se provi il seguente codice:

Sub prova()
  Dim nMiaVar As Long
  
  nMiaVar = 5
  
  testVal nMiaVar
  MsgBox nMiaVar
  
  testRef nMiaVar
  MsgBox nMiaVar

End Sub


Sub testVal(ByVal nVar As Long)
  nVar = nVar * 2
End Sub


Sub testRef(ByRef nVar As Long)
  nVar = nVar * 2
End Sub

Il discorso è diverso per le variabili oggetto, come un range, in quanto loro stesse "puntano" all'oggetto (sono istanze di quell'oggetto: se saluto Alfredo o se saluto alfrimpa, saluto comunque sempre la stessa persona).

Quindi nel caso di un range, che sia passato ByVal o ByRef, il codice potrà comunque agire sulle celle originali.
La differenza fondamentale tra ByRef e ByVal è che, con ByRef passi il riferimento all'istanza precedentemente creata (nella routine chiamante), mentre con ByVal crei una nuova istanza.
Questo significa che nel secondo caso (ByVal) è opportuno distruggere l'isatnza con Set ... = Nothing, mentre nel primo caso (ByRef) tale distruzione è assolutamente da evitare, in quanto distruggeresti l'istanza nella sub chiamante con ovvie disastrose conseguenze:

Sub provaRng()
  Dim rngMioRange As Range
  
  Set rngMioRange = Range("A2")
  rngMioRange.Value = 2
  
  testRngVal rngMioRange
  MsgBox rngMioRange.Value 'ok
  
  testRngRef rngMioRange
  MsgBox rngMioRange.Value 'errore!!
  
  Set rngMioRange = Nothing
End Sub


Sub testRngVal(ByVal rng As Range)
  rng.Value = rng.Value * 5
  Set rng = Nothing
End Sub

Sub testRngRef(ByRef rng As Range)
  rng.Value = rng.Value * 3
  Set rng = Nothing
End Sub

Spero di essere riuscito almeno in parte a chiarire la differenza.

scossa's web site

Se tu hai una mela, ed io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno.
Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)

di alfrimpa data: 20/04/2015 12:43:57
Ciao Scossa.

Cit.: "Il discorso è piuttosto complesso. Mi limiterò ad una spiegazione semplificata."

E figuriamoci se fosse stata complicata

A parte gli scherzi, ti ringrazio tantissimo per il chiarimento anche se, onestamente lo ammetto, devo confessare di aver capito solo "in superficie" i concetti da te espressi e mi riprometto - testando anche le routine di esempio che hai allegato - di cercare di metabolizzare maggiormente l'argomento.

Tuttavia, rimango, ogni volta, sempre più sorpreso e stupito (in senso altamente positivo) dalle tue spiegazioni che hanno una profondità e precisione che oserei definire "chirurgica" tant'è che spesso mi chiedo come tu abbia fatto ad acquisire un know-how così specialistico e quali siano state le tue fonti di apprendimento.

Non è che per caso sei un MVP (Most Value Professional) di Microsoft?

Grazie ancora e complimenti.

Alfredo

di scossa data: 23/04/2015 15:05:24
cit.: "Grazie ancora e complimenti"

Prego, grazie a te per l'apprezzamento.

P.S.: no, non sono un MVP.

scossa's web site

Se tu hai una mela, ed io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno.
Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)