VBA project : Graphics with variable data sources
Hello everyone,
I just learnt how to use VBA, I'm beginner!
I try for 2 days to do something and id still doesn't work so I think I need some help... I don't understand what is wrong with my code.
I'm sorry I couldn't attach the excel document but it's too big so I took photos...
and here are some explanations of what I'd like to obtain :
I have two sheets:
-sheet 1 : data source
-sheet 2 : Graphics related to these data sources
Boths of the sheets are a 12x12 matrix, for which each element is a column of 180 values (it's a result as function of the angle)
So I have a total amount of 12x12x180 values.
For each column of 180 values from sheet one, I'd like in sheet 2 an area graphic with the value as function of the angle, which would be automatically created at the right place with the right dimensions (see as exemple the first graphic in sheet 2).
I started to write the macro "Graphe" that firstly should create and place the graphics (no dimensions yet), but it doesn't work and i don't understand why... :
Sub Graphe()
'
' Graphe Macro
'
Dim i As Integer
Dim j As Integer
For i = 1 To 12
For j = 1 To 12
'begin of the column to select as source for y axis of the graphic
Dim X As Variant
X = "5 + (j - 1) * 186" 'line
Dim Y As Variant
Y = "1 + 3 * i" ' column
'End of the column to select as source for y axis of the graphic
Dim U As Variant
U = "5 + (j - 1) * 186 + 180" 'line
Dim V As Variant
V = "1 + 3 * i" ' column
Dim Graphic As Chart, Values As Range
Cells(X, Y).Select 'So that the graphik is created at the right place
Set Values = Worksheets("Sheet1").Range(Cells(X, Y), Cells(U, V))
Set Graphic = ThisWorkbook.Charts.Add
Graphic.ChartType = xlArea
Graphic.SetSourceData Values, Range("B5,B185") 'its always the same (0 to 180) so it doesn't need to vary with i and j
Next
Next
End Sub
Can someone help me with this thing?
Coralie
| Attachment | Size |
|---|---|
| Capture d’écran 2012-12-05 à 12.17.50.png | 200.72 KB |
| Capture d’écran 2012-12-05 à 12.18.52.png | 39.06 KB |
Hi, (Bonjour, je pense que
Hi, (Bonjour, je pense que vous parlez français, non ?"
Well, you defined your variable X and Y with String values.
An object Cell's coordinates must have Long values to set the row and the column. So, you have to remove "".
Bonjour! Yes I'm french!
Bonjour!
Yes I'm french! :)
Thank you for your answer!
Though...I changed the code but it still doesn't work
--> the line "Set Values = Worksheets("OutputDeltafest").Range(Cells(X, Y), Cells(U, V))" becomes yellow and the error message is 1004.
Sub Graphe()
'
' Graphe Macro
'
Dim i As Integer
Dim j As Integer
For i = 1 To 12
For j = 1 To 12
'begin of the column to select as source for y axis of the graphic
Dim X As Long
X = 5 + (j - 1) * 186 'line
Dim Y As Long
Y = 1 + 3 * i ' column
'End of the column to select as source for y axis of the graphic
Dim U As Long
U = 5 + (j - 1) * 186 + 180 'line
Dim V As Long
V = 1 + 3 * i ' column
Dim Graphic As Chart, Values As Range
'Cells(X, Y).Select 'So that the graphic is created at the right place
Set Values = Worksheets("Values").Range(Cells(X, Y), Cells(U, V))
Set Graphic = ThisWorkbook.Charts.Add
Graphic.ChartType = xlArea
Graphic.SetSourceData Values, Range("B5,B185") 'its always the same (0 to 180) so it doesn't need to vary with i and j
Next
Next
End Sub
Well, if the code is
Well, if the code is hightlighted, it's certainly because the values are not set correctly
When the row is in yellow, the code is stopped ;
Then, put your mouse cursor on each letter
Range(Cells(X, Y), Cells(U, V))
X, Y, U, V
and check that it is coherent...
For example, the X mustn't be less than U and also for Y and each value must be over than 0
Ah, please, don't name variable with keyword such as Values.
Put a prefix near each variable to determine the type :
rngChartValues for a range
strCellContentValue for a String
intCellValue for an integer
lngCellValue for a Long
blnValueIsOk for a Boolean
Sub Graph() Dim MyChart As
Sub Graph()
Dim MyChart As Chart
Set MyChart = ThisWorkbook.Charts.Add
ActiveChart.SetSourceData Source:=Range("Sheet1!$B$5:$B$185")
ActiveChart.ChartType = xlArea
MyChart.Activate
MyChart.Location Where:=xlLocationAsNewSheet
End Sub
Thank you AndyLitch for your
Thank you AndyLitch for your answer but I don't understand your code because I don't see what "chart" represents. Though I tried it and Excel crashed each time.
Argyronet, thank you too, I'm gonna try your advice!
Je ne comprends pas
Je ne comprends pas tout... Normalement, cette procédure suffit, non ?
Sub Graphe()
Dim oSheet As Worksheet
Dim Graphic As Chart
Dim oRngValues As Range
Dim rngAngle As Range
Dim C As Integer
Dim R As Integer
Const MAX_COL As Integer = 13
Set oSheet = ActiveWorkbook.Worksheets("Values")
'Set rngAngle = oSheet.Range(Cells(2, 2), Cells(180, 2))
For C = 4 To MAX_COL Step 3
R = Cells(2, C).End(xlDown).Row
Set oRngValues = oSheet.Range(Cells(2, C), Cells(R, C))
Set Graphic = ThisWorkbook.Charts.Add
Graphic.ChartType = xlArea
Graphic.SetSourceData rngValues, xlColumns
oSheet.Select
Next
Set rngValues = Nothing
Set oSheet = Nothing
End Sub
Argyronet, thanks I tried
Argyronet, thanks I tried your solution, but it still doesn't work. And I think it's not exactly what I need. Like I said, I have 12x12=144 graphes (each time the values as function of 180 angles) to create so it need to bee an implementation of columns and rows. But the main problem I have is with the syntax of range apparently, it's always what becomes yellow even in your code...
Well if you have an error,
Well if you have an error, there is not 36 reasons :
If you try to set a range to a variable where the target range is not focused, you raise and error because the variable tries to set itself to a range that not corresponding to the activesheet and so more, when your code is running the current worksheet is a Chart.
So you have to declare an object that representing the target worksheet ("Values" for your case).
Then each time a chart is generated, the target worksheet must be qualified to specify the appropriate range such as :
Set MySheet = MyWorkbook.Worksheet("Value")
Set MyRange = MySheet.Range(Cells(R,C), Cells(R,C))
where MyWorkbook is declared as Workbook and initialized with the ActiveWorkbook or ThisWorkbook object and MySheet is declared as Worksheet and initialized with the Worksheet "Values"
See the last sample I posted you yesterday.
Are you trying to create 144
Are you trying to create 144 charts because the chart creation is in the inner of a 12x12 nested for/next..
Andy Litch : Yes yes, 144
Andy Litch : Yes yes, 144 charts, exactly! That's why I want to do that with VBA!
Argyronet : J'essaye ça et je vous dis!