Nome utilizzato come parametro di una funzione definita dall'utente non funzione se il nome si riferisce ad un insieme di celle

Question

Se rinomino una colonna del foglio Excel con il nome x e lo utilizzo nella colonna a fianco passandolo ad una funzione Excel predefinita, viene correttamente calcolato il valore della funzione corrispondente al valore contenuto nella colonna rinominata e alla riga corrispondente alla cella attiva.

Invece se mi definisco una funzione (funzione definita dall'utente) in VBA e la utilizzo al posto di quella predefinita il Foglio Excel mi da un errore sul valore (#VALORE!).

Di fatto il parametro attuale viene risolto in modo diverso a seconda che la funzione sia una predefinita o definita dall'utente.

Il problema non si verifica se al nome è associato un riferimento assoluto ad una singola cella. Questo conferma che il comportamento non è voluto per un qualche motivo, in quanto risulta essere incongruo (perché funziona con i nomi associati a riferimenti assoluti e non funziona con quelli parzialmente relativi?).

Answer 1

Ciao Norman, grazie per la risposta. e scusa per il ritardo (la replica che ho provato a inviare via e-mail il 9 mi è tornata indietro con un DSN Failure).

Mi rendo conto di non aver precisato abbastanza i termini del caso.

Le funzioni hanno un solo parametro formale in ingresso.

Con il nome x ho ridefinita l'intera colonna, associando al nome x un riferimento in parte assoluto (sulla colonna) in parte relativo (sulla riga). Di fatto a x associo un riferimento del tipo $A:$A (nel caso rinomini la colonna A).

Quando uso x in una qualsiasi cella attiva del foglio all'interno di una formula, mi aspetto che x assuma il valore contenuto nella stessa riga della mia cella attiva, ma nella colonna A.

In altre parole utilizzando x non mi riferisco ad una molteplicità di valori (quelli contenuti nell'intera colonna) ma al valore contenuto in una singola cella.

Se in una cella attiva inserisco =f(x) mi aspetto che il valore di x sia passato alla funzione f che mi sono definito in un modulo vba:

Function f(p As Integer) As Integer

f = p + 3

End Function

Il risultato nel foglio Excel di seguito riportato ( f è la mia funzione RADQ è la funzione Excel):

Un cordiale saluto,

Alessandro

Answer 2

Ciao Alessandro,

Avrei dovuto aggiungere che quando si utilizza la funzione nativa RADQ() di Excel, non si vede,  e non si può vedere. il codice utilizzato sotto il cappuccio!

===

Regards,

Norman

Answer 3

Ciao Alessandro, 

Mi rendo conto di non aver precisato abbastanza i termini del caso.

Le funzioni hanno un solo parametro formale in ingresso.

Con il nome x ho ridefinita l'intera colonna, associando al nome x un riferimento in parte assoluto (sulla colonna) in parte relativo (sulla riga). Di fatto a x associo un riferimento del tipo $A:$A (nel caso rinomini la colonna A).

Quando uso x in una qualsiasi cella attiva del foglio all'interno di una formula, mi aspetto che x assuma il valore contenuto nella stessa riga della mia cella attiva, ma nella colonna A.

In altre parole utilizzando x non mi riferisco ad una molteplicità di valori (quelli contenuti nell'intera colonna) ma al valore contenuto in una singola cella.

Se in una cella attiva inserisco =f(x) mi aspetto che il valore di x sia passato alla funzione f che mi sono definito in un modulo vba:

Function f(p As Integer) As Integer

f = p + 3

End Function

 

Il risultato nel foglio Excel di seguito riportato ( f è la mia funzione RADQ è la funzione Excel):


 

Veramente, Alessandro, io non vedo il problema!

In un modulo standard, ho inserto il codice per due UDF, f() e fSeconda():

'=========>>

Option Explicit

'--------->>

Public Function f(p As Range)

    Dim rng As Range

    Dim V As Variant

    If TypeName(Application.Caller) = "Range" Then

        Set rng = Intersect(Application.Caller.EntireRow, Range("X"))

    End If

    If Not rng Is Nothing Then

        f = Sqr(rng.Value)

    Else

        f = CVErr(xlErrNA)

    End If

End Function

'--------->>

Public Function fSeconda(p As Range)

    Dim rng As Range

    Dim V As Variant

    If TypeName(Application.Caller) = "Range" Then

        Set rng = Intersect(Application.Caller.EntireRow, Range("X"))

    End If

    If Not rng Is Nothing Then

        fSeconda = rng.Value ^ 2

    Else

        fSeconda = CVErr(xlErrNA)

    End If

End Function

'<<=========

la prima funzione dovrebbe essere un esatto equivalente VBA della funzione RADQ()  di Excel e la seconda funzione è un'alternativa VBA per la funzione POTENZA()  di Excel.

Per provare le funzione, in un foglio ho fatto un confronto tra le funzione **RADQ()**e POTENZA() da una parte e le UDF f() e fSeconda() da l'altra parte:

===

Regards,

Norman

Answer 4

Ciao Norman, grazie per la risposta. E scusa per il ritardo, ho provato a rispondere direttamente via e-mail ma ho notato che mi è tornato un DSN Failure.

Mi rendo conto di non aver precisato abbastanza i termini del caso.

Le funzioni hanno un solo parametro formale in ingresso.

Con il nome x ho ridefinita l'intera colonna, associando al nome x un riferimento in parte assoluto (sulla colonna) in parte relativo (sulla riga). Di fatto a x associo un riferimento del tipo $A:$A (nel caso rinomini la colonna A).

Quando uso x in una qualsiasi cella attiva del foglio all'interno di una formula, mi aspetto che x assuma il valore contenuto nella stessa riga della mia cella attiva, ma nella colonna A.

In altre parole utilizzando x non mi riferisco ad una molteplicità di valori (quelli contenuti nell'intera colonna) ma al valore contenuto in una singola cella.

Se in una cella attiva inserisco =f(x) mi aspetto che il valore di x sia passato alla funzione f che mi sono definito in un modulo vba:

Function f(p As Integer) As Integer

f = p + 3

End Function

Il risultato nel foglio Excel di seguito riportato ( f è la mia funzione RADQ è la funzione Excel):


Un cordiale saluto,

Alessandro

Answer 5

Ciao ACiancarelli,

Se rinomino una colonna del foglio Excel con il nome x e lo utilizzo nella colonna a fianco passandolo ad una funzione Excel predefinita, viene correttamente calcolato il valore della funzione corrispondente al valore contenuto nella colonna rinominata e alla riga corrispondente alla cella attiva.

Invece se mi definisco una funzione (funzione definita dall'utente) in VBA e la utilizzo al posto di quella predefinita il Foglio Excel mi da un errore sul valore (#VALORE!).

Di fatto il parametro attuale viene risolto in modo diverso a seconda che la funzione sia una predefinita o definita dall'utente.

Il problema non si verifica se al nome è associato un riferimento assoluto ad una singola cella. Questo conferma che il comportamento non è voluto per un qualche motivo, in quanto risulta essere incongruo (perché funziona con i nomi associati a riferimenti assoluti e non funziona con quelli parzialmente relativi?).

Sarebbe molto utile vedere un file di esempio dimostrando la tua funzione e il comportamento anomalo che hai riscontrato.

Tenendo conto del fatto che che sia molto possible che io abbia capito male, ho nominato l'intervallo A2:A21 con il nome X e ho creato una funzione utente, del tutto banale, myVal:

'=========>>

Option Explicit

'--------->>

Public Function myVal(r As Range)

    Dim rCell As Range

    Const myPi As Double = 3.141592654

    Set rCell = Application.Caller

    myVal = Intersect(r, rCell.EntireRow).Value * myPi

End Function

'<<=========

Nelle cella B2, ho immesso la formula 

       =A2*PI.GRECO()

Nella cella C2, ho immesso la formula 

       =myVal(X)

Ho trascinato ambedue le formule in basso fino alla riga 21 e ho ottenuto i risultati previsti, la funzione utente restituendo sempre gli stessi risultati  restituiti dalla formula intrinsica:


Chiaramente, la tua situazione deve essere diversa e aspetto di vedere il tuo file.

===

Regards,

Norman