ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   What's up with VBA (?) (https://www.excelbanter.com/excel-programming/380817-whats-up-vba.html)

KrunoG
What's up with VBA (?)
 
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
What's up with VBA (?)
 

"KrunoG" wrote in message
...
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
What's up with VBA (?)
 
This line is also irrelevant:

ActiveChart.ChartArea.Select

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"KrunoG" wrote in message
...

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

KrunoG[_3_]
What's up with VBA (?)
 

"Jon Peltier" wrote in message
...
This line is also irrelevant:

ActiveChart.ChartArea.Select

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com

Yeah! I've noticed that as well :)
tnx



IMRTD
What's up with VBA (?)
 
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.
--
RoadToData


"KrunoG" wrote:

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


All times are GMT +1. The time now is 04:12 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com