Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
declare multiple variables
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
declare multiple variables
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
declare multiple variables
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" (noSPAMs) schrieb im Newsbeitrag ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Declare variables to a code? | Excel Discussion (Misc queries) | |||
Declare Variables in Array | Excel Discussion (Misc queries) | |||
Declare and Set Public variables | Excel Discussion (Misc queries) | |||
How to efficiently declare variables | Excel Discussion (Misc queries) | |||
Best place to declare variables | Excel Programming |