funzione val.vuoto e simili in VBA
Montalbano Sono
visto che uso spesso funzioni tipo val.vuoto, val.numero etc.. nella forma
=se(val.vuoto(A1);"costante";A1)
e la cosa mi da un po' noia specie nelle formule complesse ho pensato
bene di scrivere una funzione da richiamare all'uopo di essere più
sintentico
public function seNULL (variabile as variant, valore_se_null as variant)
as variant
' seNULL restituisce il valore in input se NON è null altrimenti lo
sostituisce con il secondo parametro
seNULL = iif(isnull(variabile);valore_se_null,variabile)
end function
purtroppo questa funzione NON funziona quando uso le formule in forma
matriciale in quanto viene reso solo il primo valore della matrice
Questo non accade con val.vuoto usato direttamente dal foglio di lavoro
Come mai?
sapreste suggerirmi una correzione?
Grazie a tutti
plinius
"Montalbano Sono" <Salvo.Mo...@PrucuraSicula.ahah> ha scritto nel
messaggio news:i1otku$vnq$1...@speranza.aioe.org...
A parte le sviste ("," per "," e "isnull" per "isempty"), la tua funzione
gestisce solo il primo (o unico) valore di "variabile"
Modifica così:
-----------------
Public Function seNULL(variabile As Range, valore_se_vuoto)
Dim i
ReDim m(1 To variabile.Count)
For i = 1 To variabile.Count
m(i) = IIf(IsEmpty(variabile(i)), valore_se_vuoto, variabile(i))
Next
seNULL = m
End Function
-----------------
Ciao,
E.
r
Ciao Montalbano,
intanto in excel non esistono i valori null esiste l'empty ... quindi
isnull in una udf scordatelo :-)
per la udf da utilizzare anche con uso matriciale ... non è così
semplice come sembrerebbe ... devi far si che il risultato sia
coerente col tipo di matrice che viene passato ( per una spegazione
puoi guardare qui http://groups.google.it/group/excel_vba_free/msg/a1d222d48343afbf?hl=it)
... e devi ovviamente distinguere quando l'argomento passato è una
matrice e quando un valore singolo ... per seguire le due strade
diversamente ...
appena riesco comunque ci provo anch'io a scriverla ... sembra
interessante :-)
saluti
r
r
> "Montalbano Sono" <Salvo.Montalb...@PrucuraSicula.ahah> ha scritto nel
> messaggionews:i1otku$vnq$1...@speranza.aioe.org...
>
> - Mostra testo citato -
ciao E
non avevo pensato che il primo argomento deve essere un range ... per
forza di cose se vogliamo una udf di quel tipo ... quindi ok ne prendo
atto ... stavo pensando però ... per gioco ... facciamo qualcosa di un
po' più *allargato* del tipo:
Function IsEmp( _
Rng As Excel.Range, _
vConst As Variant, _
Optional Rng2 As Variant)
If IsMissing(Rng2) Then
Set Rng2 = Rng
End If
'[...]
End Function
questa la struttura da cui partire ... però vorrei che sia rispettata
la coerenza tra argomenti e risultato ... per spiegare guarda i
risultati di:
=se(val.vuoto(A1:A5);colonna(A1:E1))
=se(val.vuoto(A1:A5);rif.riga(1:5))
=se(val.vuoto(A1:A5);A1:E1)
=se(val.vuoto(A1:B2);D1:E2)
ti va di provare? ...
ciao
r
plinius
"r" <robb...@gmail.com> ha scritto nel messaggio
news:8eeb0605-3581-46bc...@j8g2000yqd.googlegroups.com...
End Function
*****************
Non so se ho capito bene la tua proposta.
Una funzione che crei una matrice riempita con il valore vConst in
corrispondenza di celle vuote nel primo range e, invece, del corrispondente
valore del secondo range se le celle del primo contengono qualsivoglia dato.
Nel caso in cui il secondo range non sia indicato, viene usato il primo.
------------
Function IsEmp( _
Rng1 As Excel.Range, _
vConst As Variant, _
Optional rng2 As Range)
If rng2 Is Nothing Then Set rng2 = Rng1
If (Rng1.Rows.Count <> rng2.Rows.Count) _
Or (Rng1.Columns.Count <> rng2.Columns.Count) Then
Exit Function
End If
Dim i, j
ReDim m(1 To Rng1.Rows.Count, 1 To Rng1.Columns.Count)
For i = 1 To Rng1.Rows.Count
For j = 1 To Rng1.Columns.Count
m(i, j) = IIf(IsEmpty(Rng1(i, j)), vConst, rng2(i, j))
Next
Next
IsEmp = m
End Function
--------------
Così?
Ciao,
E.
r
> "r" <robb....@gmail.com> ha scritto nel messaggionews:8eeb0605-3581-46bc...@j8g2000yqd.googlegroups.com...
la provo ... ma così a occhio non credo sia quello che intendevo ...
ti faccio sapere
ciao
r
Scossa
> Così?
>
Ciao Plinius, ciao E.
C'è qualcosa che non va:
=SOMMA.SE(B16:B25;">20")
premendo F9 su B16:B25 mostra questo:
=SOMMA.SE({4\8\12\16\20\24\28\32\36\0};">20")
=SOMMA.SE(IsEmp(E16:E25;100);">20")
premendo F9 su B16:B25 mostra questo:
=SOMMA.SE(IsEmp({4\8\12\16\20\24\28\32\36\0};">20")
Sembrerebbero uguali ma la prima restituisce correttamente 120, la
seconda invece restituisce #VALORE!
Secondo me, se deve lavorare su un range anzhichè su una matrice io
aggiungerei alla fine
Set IsEmp = rng2
isimp = m
e magari dichiarerei la function As Excel.Range:
Function IsEmp( _
ByVal Rng1 As Excel.Range, _
vConst As Variant, _
Optional ByVal rng2 As Range) As Excel.Range
If rng2 Is Nothing Then Set rng2 = Rng1
If (Rng1.Rows.Count <> rng2.Rows.Count) _
Or (Rng1.Columns.Count <> rng2.Columns.Count) Then
Exit Function
End If
Dim i, j
ReDim m(1 To Rng1.Rows.Count, 1 To Rng1.Columns.Count)
For i = 1 To Rng1.Rows.Count
For j = 1 To Rng1.Columns.Count
m(i, j) = IIf(IsEmpty(Rng1(i, j)), vConst, rng2(i, j))
Next
Next
Set IsEmp = rng2
isimp = m
End Function
Bye!
Scossa
Scossa
>
> Ciao Plinius, ciao E.
e ovviamente ciao Maontalbano :-)
Bye!
Scossa
r
>
> e magari dichiarerei la function As Excel.Range:
ciao Scossa
questo è da escludere ... vogliamo una udf ... risultato variant direi
che va bene
ciao
r
plinius
"Scossa" <scos...@gmail.com> ha scritto nel messaggio
news:28f156c5-6c72-449a...@x21g2000yqa.googlegroups.com...
On 16 Lug, 13:08, "plinius" <punto.linea...@LEVARElibero.it> wrote:
> Cosě?
>
Ciao Plinius, ciao E.
C'č qualcosa che non va:
=SOMMA.SE(B16:B25;">20")
premendo F9 su B16:B25 mostra questo:
=SOMMA.SE({4\8\12\16\20\24\28\32\36\0};">20")
=SOMMA.SE(IsEmp(E16:E25;100);">20")
premendo F9 su B16:B25 mostra questo:
=SOMMA.SE(IsEmp({4\8\12\16\20\24\28\32\36\0};">20")
Sembrerebbero uguali ma la prima restituisce correttamente 120, la
seconda invece restituisce #VALORE!
Secondo me, se deve lavorare su un range anzhichč su una matrice io
aggiungerei alla fine
End Function
Bye!
Scossa
*********************
SOMMA.SE come primo parametro vuole un range, non una matrice.
Anche questa:
=SOMMA.SE({4\8\12\16\20\24\28\32\36\0};">20")
non viene digerita...
Scossa
> questo è da escludere ... vogliamo una udf ... risultato variant direi
> che va bene
Ok, però
Set IsEmp = rng2
isimp = m
ci vuole, infatti modificata così anche:
=SOMMA.SE(IsEmp(E16:E25;0);">20")
da 120
Bye!
Scossa
Scossa
> "Scossa" <scossa...@gmail.com> ha scritto nel messaggionews:28f156c5-6c72-449a...@x21g2000yqa.googlegroups.com...
> On 16 Lug, 13:08, "plinius" <punto.linea...@LEVARElibero.it> wrote:
>
>
> Ciao Plinius, ciao E.
>
> C'è qualcosa che non va:
>
> =SOMMA.SE(B16:B25;">20")
> premendo F9 su B16:B25 mostra questo:
> =SOMMA.SE({4\8\12\16\20\24\28\32\36\0};">20")
>
> =SOMMA.SE(IsEmp(E16:E25;100);">20")
> premendo F9 su B16:B25 mostra questo:
> =SOMMA.SE(IsEmp({4\8\12\16\20\24\28\32\36\0};">20")
>
> Sembrerebbero uguali ma la prima restituisce correttamente 120, la
> seconda invece restituisce #VALORE!
>
No, hai letto male :-)
F9 lo premo per vedere cosa restituisce la funzione, in relatà
intendevo dire che
=SOMMA.SE(B16:B25;">20") restituisce 120
mentre
=SOMMA.SE(IsEmp(E16:E25;100);">20") restituisce #VALORE!
mentre modificata con
Set IsEmp = rng2
isimp = m
alla fine invece funziona
Bye!
Scossa
plinius
mentre modificata con
alla fine invece funziona
Bye!
Scossa
*****************
Infatti esattamente questo dicevo.
La funzione non modificata restituisce una matrice (che non è il parametro
corretto per SOMMA.SE) mentre quella da te modificata restituisce un range
(che è il parametro corretto per SOMMA.SE)
Ovviamente le due cose sono diverse e non si escludono l'un l'altra, ma
l'obbiettivo che aveva posto r era quello di ottenere una matrice e non un
range.
Entrambe funzionerebbero correttamente con SOMMA che digerisce tutte e due
le cose
Montalbano Sono
Ciao :)
Mi intrometto tra esperti per portare un piccolo contributo, spero utile
c'è un altro caso da gestire: se la funzione viene chiamata via VBA
con degli scalari non funzionerà in quanto una cosa del tipo
dim data as date
dim data_default as date
data_default= cdate("1/1/2010")
data= isemp(una_qualche_funzione_che_forse_mi_da_un_risultato, data_default)
genererà un errore di tipo (data non è un range)
avevo quindi pensato di anteporre alla funzione qualcosa del tipo
if not isobject(rng) then
else
enf if
tuttavia incontro dei problemi nella definizione dei tipi delle
variabili.. c'è qualche escamotage da poter adottare?
r
>
> - Mostra testo citato -
l'ho guardata un attimo ... intanto Rng2 lo vorrei variant per poter
accettare matrici
ciao
r
Scossa
> l'obbiettivo che aveva posto r era quello di ottenere una matrice > e non un range.
Ecco, avevo capito il contrario :-(
> Ovviamente le due cose sono diverse e non si escludono l'un l'altra
Eventualmente un argomento opzionale per decidere cosa restituire:
if bRng then
Set IsEmp = rng2
endif
isemp = m
Bye!
Scossa
r
poi perchè
If (Rng1.Rows.Count <> rng2.Rows.Count) _
Or (Rng1.Columns.Count <> rng2.Columns.Count) Then
Exit Function
End If
mica detto
ciao
r
r
se non ho capito male ... la funzione che ho proposto (solo come
schema) ti andrebbe bene
saluti
r
Montalbano Sono
Certo.. mi siete di gran lunga superiori e faccio fatica a seguirvi
ho copiato e incollato questi suggerimenti e sembra funzionare tutto
meglio ( a parte la gestione del caso più semplice quello dove ho un
semplice scalare richiamato da una funzione VBA)
Onestamente non capisco il ruolo di Rng2, inizialmente avevo capito che
fosse un'altrernativa più elaborata a vConst (in modo di poter variare
l'esito di ogni singola assegnazione non restando vincolati al valore di
una semplice costante) ma poi leggendo il codice.. mi sono perso!
Montalbano Sono
> schema) ti andrebbe bene
> saluti
> r
no.. mi spiace ma devo ritrattare
ho provato a metterci le mani e mi sono reso conto di non aver capito niente
intuisco che mi manca un concentto fondamentale: che differenza c'è tra
range e matrice?
Scossa
<Salvo.Montalb...@PrucuraSicula.ahah> wrote:
>
> con degli scalari non funzionerà in quanto una cosa del tipo
>
Se ho capito bene, Montalbano voleva una udf che, dato un range di
celle, restituisse i valore delle stesse sostituendo PER LE SOLE CELLE
VUOTE (attenzione che isempty("") restituisce false ) un valore
costante.
Prendendo spunto dal suggerimento di r, ho aggiunto anche la
possibilità di sostituire non con un valore costante ma col valore
della cella corrispondente in un secondo range (facoltativo)
Ho scritto due udf:
IsVuoto() gestisce le celle vuote (isepmty() or "")
IsErrore() gestisce le celle con qualsiasi errore (iserror())
Se volete controllare se ci sono castronerie ..... ho depositato qui
un file esplicativo:
http://groups.google.it/group/excel_vba_free/web/IsVuoto_IsErrore.xls?hl=it
Andrebbe migliorato per gestire range orizzontali, magari Rob .....
Questo il codice:
Option Explicit
'---------------------------------------------------------------------------------------
' Procedure : IsErrore
' Author : Scossa
' Date : 16/07/2010
' Purpose : restituisce una matrice di valori sostituendo
' : per le sole celle contenenti un qualsiasi errore
' : o vConst o il valore della cella corrispondente
' : in rng2
'---------------------------------------------------------------------------------------
'
Function IsErrore( _
ByVal Rng1 As Excel.Range, _
vConst As Variant, _
Optional ByVal rng2 As Excel.Range) As Variant
Dim j As Long
Dim arr()
Dim arrV()
Dim v As Variant
If rng2 Is Nothing Then
Set rng2 = Rng1
arrV() = Rng1
For Each v In arrV
j = j + 1
arrV(j, 1) = vConst
Next
Else
arrV() = rng2
End If
arr() = Rng1
j = 0
For Each v In arr
j = j + 1
If IsError(v) Then arr(j, 1) = arrV(j, 1)
Next
IsErrore = arr
End Function
'---------------------------------------------------------------------------------------
' Procedure : IsVuoto
' Author : Scossa
' Date : 16/07/2010
' Purpose : restituisce una matrice di valori sostituendo
' : per le sole celle vuote un qualsiasi errore
' : o vConst o il valore della cella corrispondente
' : in rng2
'---------------------------------------------------------------------------------------
'
Function IsVuoto( _
ByVal Rng1 As Excel.Range, _
vConst As Variant, _
Optional ByVal rng2 As Excel.Range) As Variant
Dim j As Long
Dim arr()
Dim arrV()
Dim v As Variant
If rng2 Is Nothing Then
Set rng2 = Rng1
arrV() = Rng1
For Each v In arrV
j = j + 1
arrV(j, 1) = vConst
Next
Else
arrV() = rng2
End If
arr() = Rng1
j = 0
For Each v In arr
j = j + 1
If (IsEmpty(v) Or v = "") Then arr(j, 1) = arrV(j, 1)
Next
IsVuoto = arr
End Function
'-----------------
Sub prova()
Dim rng As Range
Dim rngV As Range
Set rng = Range("C16:C25")
Set rngV = Range("E16:E25")
Debug.Print Application.Sum(IsErrore(rng, 0, rngV))
Set rng = Range("B16:B25")
Debug.Print Application.Sum(IsVuoto(rng, 1000))
End Sub
Bye!
Scossa
r
> On 16 Lug, 14:16, Montalbano Sono
>
> <Salvo.Montalb...@PrucuraSicula.ahah> wrote:
>
> > c'è un altro caso da gestire: se la funzione viene chiamata via VBA
> > con degli scalari non funzionerà in quanto una cosa del tipo
>
> Se ho capito bene, Montalbano voleva una udf che, dato un range di
> celle, restituisse i valore delle stesse sostituendo PER LE SOLE CELLE
> VUOTE (attenzione che isempty("") restituisce false ) un valore
> costante.
> Prendendo spunto dal suggerimento di r, ho aggiunto anche la
> possibilità di sostituire non con un valore costante ma col valore
> della cella corrispondente in un secondo range (facoltativo)
>
> Ho scritto due udf:
> IsVuoto() gestisce le celle vuote (isepmty() or "")
> IsErrore() gestisce le celle con qualsiasi errore (iserror())
>
> Se volete controllare se ci sono castronerie ..... ho depositato qui
> un file esplicativo:
>
>
> Andrebbe migliorato per gestire range orizzontali, magari Rob .....
>
> Questo il codice:
>
> Option Explicit
>
> ' Procedure : IsErrore
> ' Author : Scossa
> ' Date : 16/07/2010
> ' Purpose : restituisce una matrice di valori sostituendo
> ' : per le sole celle contenenti un qualsiasi errore
> ' : o vConst o il valore della cella corrispondente
> ' : in rng2
> ' Procedure : IsVuoto
> ' Author : Scossa
> ' Date : 16/07/2010
> ' Purpose : restituisce una matrice di valori sostituendo
> ' : per le sole celle vuote un qualsiasi errore
> ' : o vConst o il valore della cella corrispondente
> ' : in rng2
appena ho tempo le guardo ... ma non ci siamo con il mio obiettivo ...
è tutto molto più complesso ...
vediamo la struttura
se(val.vuoto(rng1);costante;rng2)
allora ci sono moltissimi casi da gestire se si vuole ottenere un uso
matriciale simile a quanto fa excel
rng1 e rng2 possono essere dimensionati in modo diverso -> alcuni
esempi che non restituiscono un errore:
a1:a4 e b1:b4
a1:a4 e b1:e1
a1:a4 e b1:c2
...
ma anche
b1:b4 a1:a4
e tanti altri
...
ognuno di questi diversi modi restituisce una matrice ben precisa del
tipo:
{1;1;1;1} oppure {1\1\1\1} o ancora {1;1\1;1\1;1} e molte altre ...
diversamente dimensionate
se aggiungiamo che rng2 potrebbe essere oltre che un range anche una
costante o una matrice (ricordate ho chiesto rng2 variant) ... beh i
casi da prendere uin esame sono molti .... e nno sempre si ottiene un
prodotto di matrici ... c'è prodotti tensoriali e prodotti tra matrice
e scalare ...
adesso stiamo discutendo ... non è che mi interessi avere un risultato
come quello atteso ... per il risultato in se ... ma è sicuramente
interessante capirne le logiche ... rimarco che replicare con una udf
una funzione excel è già normalmente molto molto complicato ... ancora
di più lo è replicare una funzione nidificata anche se composta da due
semplici funzioni ...
saluti
r
Montalbano Sono
non vorrei fare il tardo della compagnia.. ma a me non funziona.. o
meglio funziona ma non è quello che intendevo
ho messo in una tabella dei valori (colonna1) lasciando dei valori non
definiti
se in colonna2 metto
=isvuoto(tabella1[colonna1];"0")
ottengo in ogni cella di colonna2 sempre lo stesso vettore (visto con
f9) con i risultati che mi aspettavo: solo che li ho in forma
={12\12\2\0\2\0\3} per ogni cella. io mi aspettavo di avere un valore
per ogni cella!
se in colonna 2 metto invece
=isvuoto(tabella1[[questa riga][colonna1]];"0")
ottengo solo #valore in ogni cella di colonna2
credo che mi manchi un mattone fondamentale per capire cosa stia
succedendo.. mi potete aiutare?
grazie
Scossa
<Salvo.Montalb...@PrucuraSicula.ahah> wrote:
> =isvuoto(tabella1[colonna1];"0")
Scusa ma non ho capito molto il tuo *contesto*, così .. sparando alla
cieca: hai provato a confermaree con ctrl+maiusc+invio?
Comunque guarda il file depoistato qui:
Ma può essere che non abbia capito niente io, anzi eè molto
probabile :-)
Bye!
Scossa
Scossa
> appena ho tempo le guardo ... ma non ci siamo con il mio obiettivo ...
> è tutto molto più complesso ...
Humm, troppo complesso .... vabbè se mi gira nel weekend ci penso.
Bye!
Scossa