Assigning ChartObject to ChartObject Array and then getting them out

[bpat83]

Hi Guys,

I am trying to assign number of charts on a worksheet into an array of Chartobject

My code is:

Dim Source() As ChartObject
ReDim Source(ChartCount)

For i = 1 To ChartCount

Set Source(i) = ActiveSheet.ChartObjects(i)
Debug.Print Source(i).Name
next i

This seems to work and i get an output in debug of its names.

my problem comes when i am trying to get the individual charts out of this array

Dim c1, c2 As ChartObject
Set c1 = Source(1)
Set c2 = Source(2)

when i run this, i get a null value back. Can someone please help me out here?

Thanks
BPAT

 

[Domenic]

Although there are a few things that should be addressed in your code, it seems to successfully assign chartobjects to c1 and c2. What is it that you tried, which causes a null value to be returned?

 

[bpat83]

once the chartobjects are assigned into c1 and c2, i am then trying to call another procedure with two chartobjects as arguments.

Call WorkOnCharts (c1, c2)

however, it doesn't work and i get a debug error.

After that i put a STOP before the call function and when i move my cursor over c1 and c2 it mentions that c1 = nothing and c2=nothing.

so i am guessing that it hasn't assigned the chartobject and so when i call the next procedure it is not recognizing them as chartobjects.

 

[Domenic]

Can you post your complete code, together with the code associated with WorkOnCharts?

By the way, here are some things to consider...

1) When you declare an array with only the upper index, VBA assumes a zero-based array. So, for example, if ChartCount is equal to 3, and you declare your array as ReDim Source(ChartCount), you're actually creating an array that consists of 4 elements. You can declare an array of 3 elements as ReDim Source(1 to ChartCount).

2) Dim c1, c2 As ChartObject declares c1 as Variant, not as ChartObject. To declare c1 as ChartObject too, you should declare them as Dim c1 As ChartObject, c2 As ChartObject.

 

[bpat83]

Hi Domenic,

Thanks for the tips, i tried to work both those things into the code, but it still doesnt work.

The whole procedure code is:

Sub GroupCharts()

Dim ChartCount As Integer
ChartCount = ActiveSheet.Range("au1").Value
Dim i, x As Integer
Dim ChartNames() As String
ReDim ChartNames(ChartCount)

For i = 1 To ChartCount
ChartNames(i) = ActiveSheet.Cells(i, "av").Value
Debug.Print ChartNames(i)
Next i

Dim Source() As ChartObject
ReDim Source(1 To ChartCount)
Dim namecheck As String

For i = 1 To ChartCount
If namecheck = ChartNames(i) Then
Set Source(i) = ActiveSheet.ChartObjects(i)
Debug.Print Source(i).Name
End If
Next i

ChartCount = UBound(Source)

If ChartCount = 1 Then

ElseIf ChartCount = 2 Then
Dim c1 As ChartObject
Dim c2 As ChartObject
Set c1 = Source(1)
Set c2 = Source(2)

Call WorkOnCharts(c1, c2)

ElseIf ChartCount = 3 Then

ElseIf ChartCount = 4 Then

End If
End Sub

The above code still passes on Null value to c1 and c2. So WorkOnCharts returns an error as it unable to use c1 or c2.

Regards
BPAT

 

[Domenic]

It looks like you've declared namecheck , however you haven't assigned it anything. And, as a result, namecheck is equal to an empty/null string. So If namecheck = ChartNames(i) Then will always evaluate to False, and hence nothing gets assigned to your array Source(), and in turn c1 and c2 are each assigned Nothing.

By the way, your array ChartNames() should be declared as follows...

ReDim ChartNames(1 to ChartCount)

...similar to your array Source(), which is a 1-based indexed array.