hi,

is there any way i can declare multiple variables at the same time without
creating tons of different functions. for example, lets say i have:

Function zum() As Variant
Application.Volatile
zum = 0
For Each w In Worksheets
zum = zum + w.Range("C2").Value
Next
End Function

i want to be able to modify it to something like:

Function zum() As Variant
Application.Volatile
zum = 0
For Each w In Worksheets
zum = zum + w.Range("C2").Value
zum2 = zum2 + w.Range("D2").Value
zum3 = zum3 + w.Range("F2").Value
Next
End Function


how should I modify it?

thanks in advance,
geebee

It looks like you are doing a UDF which by default can only return one value.
How about something more like this...

Function zum(byval Target as range) As double
dim w as worksheet

Application.Volatile
zum = 0
For Each w In Worksheets
zum = zum + w.Range(Target.Address).Value
Next w
End Function

Use it like
=Zum(C2)
or
=Zum(D2)

--
HTH...

Jim Thomlinson


"geebee" wrote:

> hi,
>
> is there any way i can declare multiple variables at the same time without
> creating tons of different functions. for example, lets say i have:
>
> Function zum() As Variant
> Application.Volatile
> zum = 0
> For Each w In Worksheets
> zum = zum + w.Range("C2").Value
> Next
> End Function
>
> i want to be able to modify it to something like:
>
> Function zum() As Variant
> Application.Volatile
> zum = 0
> For Each w In Worksheets
> zum = zum + w.Range("C2").Value
> zum2 = zum2 + w.Range("D2").Value
> zum3 = zum3 + w.Range("F2").Value
> Next
> End Function
>
>
> how should I modify it?
>
> thanks in advance,
> geebee
>
>

hi,

this works good. but now i would like to know how i can add hem. lts say
for example that cell C1 has a formula of =zum(E12) in it. and cell C2 has a
formula of =zum(E13) in it. and so forth. i would like to know how i can
add these zums up in cell C5.i tried to use =sum(C1:C4) but its not working.

thanks in advance,
geebee


"Jim Thomlinson" wrote:

> It looks like you are doing a UDF which by default can only return one value.
> How about something more like this...
>
> Function zum(byval Target as range) As double
> dim w as worksheet
>
> Application.Volatile
> zum = 0
> For Each w In Worksheets
> zum = zum + w.Range(Target.Address).Value
> Next w
> End Function
>
> Use it like
> =Zum(C2)
> or
> =Zum(D2)
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "geebee" wrote:
>
> > hi,
> >
> > is there any way i can declare multiple variables at the same time without
> > creating tons of different functions. for example, lets say i have:
> >
> > Function zum() As Variant
> > Application.Volatile
> > zum = 0
> > For Each w In Worksheets
> > zum = zum + w.Range("C2").Value
> > Next
> > End Function
> >
> > i want to be able to modify it to something like:
> >
> > Function zum() As Variant
> > Application.Volatile
> > zum = 0
> > For Each w In Worksheets
> > zum = zum + w.Range("C2").Value
> > zum2 = zum2 + w.Range("D2").Value
> > zum3 = zum3 + w.Range("F2").Value
> > Next
> > End Function
> >
> >
> > how should I modify it?
> >
> > thanks in advance,
> > geebee
> >
> >

Hi,

replace the line:
zum = zum + w.Range(Target.Address).Value

with this line:
zum = zum + worksheetfunction.sum(w.Range(Target.Address)

It should work

Much fun, Heimo


"geebee" <(E-Mail Removed)(noSPAMs)> schrieb im Newsbeitrag
news:A0402D76-02C4-46B6-B209-(E-Mail Removed)...
> hi,
>
> this works good. but now i would like to know how i can add hem. lts say
> for example that cell C1 has a formula of =zum(E12) in it. and cell C2 has
> a
> formula of =zum(E13) in it. and so forth. i would like to know how i can
> add these zums up in cell C5.i tried to use =sum(C1:C4) but its not
> working.
>
> thanks in advance,
> geebee
>
>
> "Jim Thomlinson" wrote:
>
>> It looks like you are doing a UDF which by default can only return one
>> value.
>> How about something more like this...
>>
>> Function zum(byval Target as range) As double
>> dim w as worksheet
>>
>> Application.Volatile
>> zum = 0
>> For Each w In Worksheets
>> zum = zum + w.Range(Target.Address).Value
>> Next w
>> End Function
>>
>> Use it like
>> =Zum(C2)
>> or
>> =Zum(D2)
>>
>> --
>> HTH...
>>
>> Jim Thomlinson
>>
>>
>> "geebee" wrote:
>>
>> > hi,
>> >
>> > is there any way i can declare multiple variables at the same time
>> > without
>> > creating tons of different functions. for example, lets say i have:
>> >
>> > Function zum() As Variant
>> > Application.Volatile
>> > zum = 0
>> > For Each w In Worksheets
>> > zum = zum + w.Range("C2").Value
>> > Next
>> > End Function
>> >
>> > i want to be able to modify it to something like:
>> >
>> > Function zum() As Variant
>> > Application.Volatile
>> > zum = 0
>> > For Each w In Worksheets
>> > zum = zum + w.Range("C2").Value
>> > zum2 = zum2 + w.Range("D2").Value
>> > zum3 = zum3 + w.Range("F2").Value
>> > Next
>> > End Function
>> >
>> >
>> > how should I modify it?
>> >
>> > thanks in advance,
>> > geebee
>> >
>> >