Why is it necessary to specify activesheet for chartobjects.add

[initialize]

Sub smarterway()
        Dim chartfirst As ChartObject
        Dim rngChart As Range
        Set rngChart = Range("D3:J20")
    
        Set chartfirst = ActiveSheet.ChartObjects.Add(Left:=rngChart.Left, Top:=rngChart.Top, Width:=rngChart.Width, Height:=rngChart.Height)
    [....]

For this line:
> Set chartfirst = ActiveSheet.ChartObjects.Add(..)


if I dont add in *Activesheet* in. I would get a *Object Required* error.


I don't understand why though. Isnt it assumed that it is Activesheet, cause I usually don't put Activesheet in my codes and it works as long as I have the required sheet opened and activated.


I'm clearly missing and misunderstanding something very fundamental in vba, please advice

 

[ctd2015]

This is just an assumption since I am no VBA expert myself.
You are dealing with the VBA code i.e. the code working at a more fundamental level.. this requires greater precision
So you need to specify the sheet on which the chart object is to be placed.
As an experiment try to see if you can place the chart object on other sheets in the workbook i.e. Set chartfirst = Sheet(2).ChartObjects.Add(...)
If it puts the chart on another sheet you have your answer

 

[initialize]

This is just an assumption since I am no VBA expert myself.
You are dealing with the VBA code i.e. the code working at a more fundamental level.. this requires greater precision
So you need to specify the sheet on which the chart object is to be placed.
As an experiment try to see if you can place the chart object on other sheets in the workbook i.e. Set chartfirst = Sheet(2).ChartObjects.Add(...)
If it puts the chart on another sheet you have your answer

This code is macro and doesn't belong to any worksheet.

Let me add more detail to my confusion:
Let's say I have 'sheet 1' open/activated, and I run the following two lines in a macro.
1. Activesheet.range("A1").value = "hello" 'will put hello in cell A1 of the activesheet (which is sheet 1)
2. worksheets("sheet4").range("A1").value = "Hello" 'will put hello in cell A1 of sheet1

Now for the code on (1.) I can achieve the same result by just doing:
1. range("A1").value = "hello" 'This is put hello in cell A1 of sheet 1 too, it will assume activesheet is there

So going back to my original question, so why do I need to specify ActiveSheet, when I was running this line:
Set chartfirst = ActiveSheet.ChartObjects.Add(...)




Thank you for your reply, I really appreciate it =)

 

[MARK858]

The below works for me

Sub chartxxxx()
    Dim rngChart As Range, chartfirst As Object
    Set rngChart = Worksheets("Sheet1").Range("D3:J20")
    Set chartfirst = Worksheets("Sheet2").ChartObjects.Add(Left:=rngChart.Left, _
             Top:=rngChart.Top, Width:=rngChart.Width, Height:=rngChart.Height)
    chartfirst.Chart.ChartWizard Source:=rngChart
End Sub

P.S. can you please post links to any sites where you have cross-posted as per the forum rules

http://stackoverflow.com/questions/34051840/why-need-to-specify-activesheet-for-chartobjects-add

 

[initialize]

Hi,

My apology I didn't realize the cross posting rule.

As for the code you mention, I understand that that would work. My question is:

Usually if we omit Activesheet or not specifying a worksheet, VBA would assume by default it is Activesheet, such as if I do: Range("A1").value = "hello". This will assume it is Activesheet.Range("A1").

Now why when we do Set chartfirst = ChartObjects.Add(..) it gives an error. However if I add in Activesheet.chartobjects.add(..) it would work. If I omit the Activesheet, shouldnt it assume by default it is activesheet just as the range example I've provided?

I understand how to make this program work from the beginning, but I'm asking this question because I feel like I might be misunderstanding something fundamental.


EDIT: it wouldnt let me edit my original post, I cannot add the link to it. Is it because the post was made last week?

 

[RoryA]

You got the answer at SO from Tim Williams so why did you post the question here afterwards?

 

[initialize]

You got the answer at SO from Tim Williams so why did you post the question here afterwards?

I posted the question here too because I want to get more opinions and answers. Either I'm not understanding his answer or his answer doesn't fully solve my confusion.

So from his answer I interrupted the following:
Hierarchy:
ChartObject is under worksheet, it is not application level. While Activesheet is application level.

This doesnt clear up to me as to why when we type Range("A1").value, it would default to Activeworksheet.Range("A1").value. Range is also not application level and under worksheet. While when I use ChartObject I have to specify Activeworksheet.ChartObject or else I get an error

Isn't the hierarchy: Application.activeworkbook.worksheet("sheet x").Range. And for chartObject: Application.activeworkbook.worksheet.chartobject

ChartObject and Range are both under worksheet. So why do I need to specify activeworksheet for chartObject only.

If you know the answer to my question, can you give your input and explanation. I think it would be easier for me to understand if I get your input too. Thank you

 

[RoryA]

Range is also not application level and under worksheet.

Actually, yes it is. Range is a property of the Application object and that is what is called if you just use Range in a normal module without specifying a worksheet. Chartobjects is not a property of Application and therefore won't work without a Worksheet qualifier.

 

[MARK858]

ChartObject and Range are both under worksheet. So why do I need to specify activeworksheet for chartObject only.

Go into Help in your VB editor and type in Worksheet (offline version). Click Worksheet Object Members.

Range and ChartObjects are not the same as relates to the Worksheet object

You will see that ChartObjects is a Method not a property of the worksheet.

Range is a property of the worksheet.

Perhaps the link below will help you understand the basics better

Excel Objects

 

[initialize]

Thank you Rory and Mark.
That cleared it up for me, I guess I really did miss some important and very fundamental structure of vba, I just went on doing examples and program without understanding the underlying details.

Thank you so much