I have asked this question before and I am grateful for the help I received but the problem persists. I am trying to plot a Shear-Force and Bending-Moment diagram calculated from the loads. I create two arrays of points which I pass over to two named charts.
The program is stalling at the .SeriesCollection(1).Values = SFnode and the BMnode.
My code
Sub DOGRAPH()
'Calculate the Maximum BM by calculating the area of the SF diagram
'Data
Dim L As Single, UDL As Single, RA As Single
L = 3 'm
UDL = 1 'kN/m
RA = L * UDL / 2
'Global variable
Dim i As Integer
'Declare the nodes
Dim nNodes As Integer
Dim SFnode() As Variant, BMnode() As Variant 'Graph points
'Number of nodes
nNodes = L * 1000 'Divide length into mm
ReDim SFnode(nNodes), BMnode(nNodes)
'Do the SF and BM Diagrams
Dim SFsum As Single 'Sum the area of the SF diagram
SFnode(0) = RA 'First Node = RA
SFsum = 0
For i = 1 To nNodes
SFnode(i) = RA - UDL * i / 1000 'SF Diagram
SFsum = SFsum + SFnode(i)
BMnode(i) = SFsum 'BM Diagram
Next i
Dim cSF As Chart, cBM As Chart
'The charts have been named in the sheet
Set cSF = ActiveSheet.ChartObjects("SFdiagram").Chart
Set cBM = ActiveSheet.ChartObjects("BMdiagram").Chart
With cSF
.SeriesCollection(1).Values = SFnode
End With
With cBM
.SeriesCollection(1).Values = BMnode
End With
Set cSF = Nothing
Set cBM = Nothing
MsgBox "Done"
End Sub
Please, try the next way. It processes two Cluster Column charts type. Edited:*
Your pseudo chart form the downloaded workbook cannot be used in this way. Without any series, it should be obvious that cBM.SeriesCollection(1) will return an error in any circumstances. Please, delete your existing charts and run the next code. It, firstly, will create two chats and their first 'Series' and then it will only change/refresh their Series1 series:
Sub ChartFromArray() 'creation firstly
Dim sh As Worksheet, arr, cSF As Chart, cBM As Chart
Set sh = ActiveSheet
arr = makeArrays
'for SFdiagram chart:_____________________________________________
On Error Resume Next
Set cSF = sh.ChartObjects("SFdiagram").Chart
If Err.Number = -2147024809 Then
Err.Clear: On Error GoTo 0
Set cSF = sh.ChartObjects.Add(Left:=1, Top:=10, _
Width:=300, Height:=300).Chart
cSF.ChartType = xlColumnStacked: cSF.Parent.Name = "SFdiagram"
cSF.SeriesCollection.NewSeries.Values = arr(0)
Else
On Error GoTo 0
cSF.SeriesCollection(1).Values = arr(0)
End If:
'________________________________________________________________
'for BMdiagram chart:_____________________________________________
On Error Resume Next
Set cBM = sh.ChartObjects("BMdiagram").Chart
If Err.Number = -2147024809 Then
Err.Clear: On Error GoTo 0
Set cBM = sh.ChartObjects.Add(Left:=cSF.Parent.Width + 5, _
Top:=10, Width:=300, Height:=300).Chart
cBM.ChartType = xlColumnStacked: cBM.Parent.Name = "BMdiagram"
cBM.SeriesCollection.NewSeries.Values = arr(1)
Else
On Error GoTo 0
cBM.SeriesCollection(1).Values = arr(1)
End If
'________________________________________________________________
End Sub
It uses the next function to calculate the two necessary arrays, so copy it, too:
Function makeArrays() As Variant
Dim L As Single, UDL As Single, RA As Single, i As Integer
L = 3: UDL = 1: RA = L * UDL / 2 'kN/m
'Declare the nodes
Dim nNodes As Integer
Dim SFnode() As Variant, BMnode() As Variant 'Graph points
'Number of nodes
nNodes = L * 1000 'Divide length into mm
ReDim SFnode(nNodes), BMnode(nNodes)
'Do the SF and BM Diagrams
Dim SFsum As Single 'Sum the area of the SF diagram
SFnode(0) = RA 'First Node = RA
SFsum = 0
For i = 1 To nNodes
SFnode(i) = RA - UDL * i / 1000 'SF Diagram
SFsum = SFsum + SFnode(i)
BMnode(i) = SFsum 'BM Diagram
Next i
makeArrays = Array(SFnode, BMnode)
End Function
Please, test it and send some feedback.