declare multiple variables

[geebee]

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

 

[Jim Thomlinson]

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)

 

[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

 

[Heimo]

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