Pass a multpile range to a function

[terzaghi]

Hi,

I want to pass to a user made VBA function non only a range bu a multiple range.

In particular, in excel it is possible to make something like:

=SUM(Sheet1:Sheet3!A1)

and it makes the sum of Sheet1!A1 +Sheet2!A1+Sheet3!A1.

I'd like to have a function that can manage the same type of "union of ranges":

I was not able to define the function in such a way to manage the multiple range in it.

for example this doesn't work:

=MyFun (r as range)

I obtain always a #VALUE! hen in the worksheet i select a multiple range.

Thenk you

a

 

[xlbo]

Can you post the code ??
It depends which functions you are using as some are not available to be used over multiple ranges

Rgds, Geoff
[blue]Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?[/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[bryanbayfield]

Hmmm ... quirky.

One way round it is to have a named range, then pass the named range as the range argument rather than a non-contiguous set of ranges.

 

[terzaghi]

some code.

for example

Function MyFun(a As Range)

MyFun = a.Areas.Count

End Function


Myfun works fine if you make something like =MyFun(A1:B100)

It doesn't work with something like:

=MyFun(E6:J20;M15:M19)

the problem is to define a correctly in the declaration and then manage the variable...

thank you

a

 

[terzaghi]

a litte improvement:

Function MyFun(a As Variant)

If IsArray(a) Then
MyFun = a.Areas.Count
Else
MyFun = "JOY"

End If


End Function


this works when you make something like =MyFun(Sheet2:Sheet3!A1) and returns JOY. But I cannont manage in any way the variable a. a(1) makes non-sense.

thank you

a

 

[xlbo]

Well, if it is always 3 sheets, could you not use
Function myFun(rng1 as range,rng2 as range, rng3 as range)
select 'em individually then sum up in the function ??

You could also pass them through as a string instead of a range and then parse it but if it is on multiple sheets, you'll have problems as the range selection functionality doesn't list all sheets - only the 1st and last

Rgds, Geoff
[blue]Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?[/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244