What's up with VBA (?)

[KrunoG]

Hi,

I've migrated to Excel 2007 with my work documents and encountered numerous
problems. One of them is related to simple macro's that switch input for
scatter chart month after month.
The old Excel (v.2003) generated this macro:
/////////////////////////////////////////////////////////////////////////////////////////////////////////
Sub jan()
'
' jan Macro
' Macro recorded 4.4.2006 by Me
'

'
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
Application.Run "OnGenericSetSheetActive"
ActiveChart.SeriesCollection(1).XValues = "=QP!R6C6:R37C6"
ActiveChart.SeriesCollection(1).Values = "=QP!R6C5:R37C5"
ActiveChart.SeriesCollection(1).BubbleSizes = "=QP!R6C4:R37C4"
ActiveWindow.Visible = False
Windows("some_document.xls").Activate
Application.Run "OnGenericSetSheetActive"
End Sub
//////////////////////////////////////////////////////////////////////////////////////////////////////////

PROBLEM 1:
Excel 2007 does't run this code. It stops on line 3 and then 4,5...
I've found out that it doesn't recognize this R1C1 reference style so with a
little tuning it worked with this code below:
//////////////////////////////////////////////////////////////////////////////////////////////////////////

Sub jan()
'
' jan Macro
' Macro recorded 9.1.2006 by Me
'

'
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).XValues = "=QP!$F$6:$F$37"
ActiveChart.SeriesCollection(1).Values = "=QP!$E$6:$E$37"
ActiveChart.SeriesCollection(1).BubbleSizes = "=QP!$D$6:$D$37"
ActiveSheet.ChartObjects("Chart 1").Activate
End Sub
//////////////////////////////////////////////////////////////////////////////////////////////////////////
So, this one (reduced and non-R1C1 style) gives the same effect and the
result as the old version.

PROBLEM 2 (general one):
When I save document as an old version (so the coleagues can open with their
old Excel's) and distribute "report" they can't use this macro I've adjusted
(the similar error occures to them).

Can anyone help me figuring out how to fix this???

I tought that VBA is a standard working wherever you need it but...


BR


Kruno

 

[KrunoG]

Hi,

I've migrated to Excel 2007 with my work documents and encountered
numerous problems. One of them is related to simple macro's that switch
input for scatter chart month after month.
The old Excel (v.2003) generated this macro:
/////////////////////////////////////////////////////////////////////////////////////////////////////////
Sub jan()
'
' jan Macro
' Macro recorded 4.4.2006 by Me
'

'
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
Application.Run "OnGenericSetSheetActive"
ActiveChart.SeriesCollection(1).XValues = "=QP!R6C6:R37C6"
ActiveChart.SeriesCollection(1).Values = "=QP!R6C5:R37C5"
ActiveChart.SeriesCollection(1).BubbleSizes = "=QP!R6C4:R37C4"
ActiveWindow.Visible = False
Windows("some_document.xls").Activate
Application.Run "OnGenericSetSheetActive"
End Sub
//////////////////////////////////////////////////////////////////////////////////////////////////////////

PROBLEM 1:
Excel 2007 does't run this code. It stops on line 3 and then 4,5...
I've found out that it doesn't recognize this R1C1 reference style so with
a little tuning it worked with this code below:
//////////////////////////////////////////////////////////////////////////////////////////////////////////

Sub jan()
'
' jan Macro
' Macro recorded 9.1.2006 by Me
'

'
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).XValues = "=QP!$F$6:$F$37"
ActiveChart.SeriesCollection(1).Values = "=QP!$E$6:$E$37"
ActiveChart.SeriesCollection(1).BubbleSizes = "=QP!$D$6:$D$37"
ActiveSheet.ChartObjects("Chart 1").Activate
End Sub
//////////////////////////////////////////////////////////////////////////////////////////////////////////
So, this one (reduced and non-R1C1 style) gives the same effect and the
result as the old version.

PROBLEM 2 (general one):
When I save document as an old version (so the coleagues can open with
their old Excel's) and distribute "report" they can't use this macro I've
adjusted (the similar error occures to them).

Can anyone help me figuring out how to fix this???

I tought that VBA is a standard working wherever you need it but...


BR


Kruno

I got it... don't know why but I've got it
Here's the solution:
/////////////////////////////////////////////////////////////////////////////////////////////////////////////
Sub jan()
'
' jan Macro
' Macro recorded 4.4.2006 by Me
'

'
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).XValues = "=QP!R6C6:R37C6"
ActiveChart.SeriesCollection(1).Values = "=QP!R6C5:R37C5"
ActiveChart.SeriesCollection(1).BubbleSizes = "=QP!R6C4:R37C4"
ActiveWindow.Visible = False
End Sub
/////////////////////////////////////////////////////////////////////////////////////////////////////////////

I've just removed the lines;
Windows("some_document.xls").Activate
Application.Run "OnGenericSetSheetActive"

 

[Jon Peltier]

This line is also irrelevant:

ActiveChart.ChartArea.Select

- Jon

 

[KrunoG]

This line is also irrelevant:

ActiveChart.ChartArea.Select

- Jon

Yeah! I've noticed that as well
tnx

 

[Guest]

Hi Guys,


I am using Excel 2007 and trying to execute this line:

G.SeriesCollection(1).BubbleSizes = "={100}"

and getting this error:

"the specified dimension is not valid for the current chart type"

The same line of code is working fine in Excel 2000 and XP.

Does anyone know whats wrong with Excel 2007.