Passing a range to a function
 

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

Passing a range to a function
 

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" wrote in
message ...

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