Passing a range to a function

[Isabelle]

I'm trying to add charts programmatically by passing the cell range to a function. Simple version is like this:

Application.Run _
"myPlotFunct", ActiveSheet, Range(Cells(1,1),Cells(5,5))

I have no problem with the first chart but the 2nd, 3rd,... don't like this command because the ActiveSheet is a chart after each run through the function. My solution is:

set mySheet = ActiveSheet
Application.Run _
"myPlotFunct", ActiveSheet, Range(Cells(1,1),Cells(5,5))
mySheet.Activate

This works fine but is awfully clunky. How do I pass the range over to the function without having to switch pages in between? I've tried:

set mySheet = ActiveSheet
Application.Run _
"myPlotFunct", mySheet(Range(Cells(1,1),Cells(5,5)))

and I've also tried:

set mySheet = ActiveSheet
Application.Run _
"myPlotFunct", mySheet.Range(Cells(1,1),Cells(5,5)))

No luck so far.

[Bob Phillips]

Try

Application.Run _
"myPlotFunct", mySheet.Range(mySheet.Cells(1,1),mySheet.Cells(5,5)))


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Isabelle" <[email protected]> wrote in
message news:[email protected]...
>
> I'm trying to add charts programmatically by passing the cell range to a
> function. Simple version is like this:
>
> Application.Run _
> "myPlotFunct", ActiveSheet, Range(Cells(1,1),Cells(5,5))
>
> I have no problem with the first chart but the 2nd, 3rd,... don't like
> this command because the ActiveSheet is a chart after each run through
> the function. My solution is:
>
> set mySheet = ActiveSheet
> Application.Run _
> "myPlotFunct", ActiveSheet, Range(Cells(1,1),Cells(5,5))
> mySheet.Activate
>
> This works fine but is awfully clunky. How do I pass the range over to
> the function without having to switch pages in between? I've tried:
>
> set mySheet = ActiveSheet
> Application.Run _
> "myPlotFunct", mySheet(Range(Cells(1,1),Cells(5,5)))
>
> and I've also tried:
>
> set mySheet = ActiveSheet
> Application.Run _
> "myPlotFunct", mySheet.Range(Cells(1,1),Cells(5,5)))
>
> No luck so far.
>
>
> --
> Isabelle
> ------------------------------------------------------------------------
> Isabelle's Profile:
http://www.excelforum.com/member.php...o&userid=30752
> View this thread: http://www.excelforum.com/showthread...hreadid=504203
>