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)