Turn a VBA Array into chart

Tinkerz

Board Regular
Joined
Feb 26, 2007
Messages
179
I am still pretty new to VBA, I have array held in VBA code, what is the best what to get it into a chart?


I was after a bar chart, and the data for the chart will update once every 5 mins or so from the array so how do i keep this graph updated

thanks

Tinkerz
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Maybe the following codes help you.
Code:
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub Macro1()
Dim s(1 To 7) As Integer, i As Integer, j As Integer
    Charts.Add
    ActiveChart.ChartType = xlBarClustered
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
    For i = 1 To 100
    Sleep 1000 ' change to your period
    DoEvents
    For j = 1 To 7
    s(j) = (i + j) ^ 2
    Next
    ActiveChart.SeriesCollection(1).Values = s
    ActiveChart.PlotArea.Select
    ActiveChart.HasTitle = True
    ActiveChart.ChartTitle.Characters.Text = "Time (" & i & ")"
    Next
End Sub

Best Regards
 
Upvote 0
thankyou, have to get the book out to see what u are doing

the data is in the array in VB and not yet in excel spreadsheet how can i get the data out of VB and into excel, the array is dymanic also

thanks
 
Upvote 0
thankyou, have to get the book out to see what u are doing

the data is in the array in VB and not yet in excel spreadsheet how can i get the data out of VB and into excel, the array is dymanic also

thanks

The codes above iwas just chart with VBAarray series but not in excel spreadsheet as you want.

:rolleyes: :rolleyes: :rolleyes:
 
Upvote 0
Thank the code is in an array in a worksheet and it looks like this.

Code:
Dim Askarray()

'make array bounds
If counterx = 0 Then
Lowerarray = 7000
Upperarray = 9000
ReDim Askarray(Lowerarray To Upperarray)
ReDim Bidarray(Lowerarray To Upperarray)
counterx = 1

But I am lost with how get to this to chart in a simple way.
 
Upvote 0
?????????

Code:
ActiveChart.SeriesCollection(1).Values = Askarray
 
Upvote 0
How do I put in the axis labels from the array?

My array index is what i what the labels to be so i can match up the values in the array with array index

thanks
 
Upvote 0
You can assign an array to a series values as Northwolves shows, but you are limited to relatively few data points, because the length of the series formula is limited, and each value takes up precious characters.

It's more reliable to dump the data into a worksheet and use the range as the chart's source data as follows.

Code:
Dim rData As Range
Set rData = Worksheets(1).Range("A1").resize(Ubound(AskArray)+1-LBound(AskArray))
rData.Value = WorksheetFunction.Transpose(AskArray)

Worksheets(2).ChartObjects(1).Chart.SeriesCollection(1).Values = rData
 
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top