Using a Dynamic Array when adding a chart series

dcanham

Active Member
Joined
Jun 7, 2006
Messages
306
Can anyway suggest a way to use the dynamic array SalesNumbersArray() to fill out the XValues for the chart? Or lacking that a better way to fill the values? Thanks.


Code:
    ' determining number of datapoints
    nSeriesDataPoints = (nSeriesTotalDays / nSeriesDivisor)
    
    ' initializing array
    Dim SalesNumbersArray(nSeriesDataPoints) As Double
    
    ' loop for gathering sales
    Dim nLoop As Integer
    Dim nTotalSales As Double
    For nLoop = 0 To (nSeriesDataPoints - 1)
        ' getting total sales for this iteration
        nTotalSales = SUM_SALES_RANGE(sProductCode, nSeriesDivisor, (dStartDate + (nDivisor * nLoop)))
        
        ' placing total in array
        SalesNumbersArray(nLoop) = nTotalSales
    Next nPoints
    
    ' adding series
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(nSeriesCounter).Name = sProductCode
    ActiveChart.SeriesCollection(nSeriesCounter).XValues = SalesNumbersArray()
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,922
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
You're going to run into the limit of around 250 (no, it's less than 256, but I don't know exactly what it is) characters for the literal array of values:

{1.23456,1.34689,2.98765}

That's 25 characters for three values, and Excel is carrying more digits than that for each.

The best way is to dump the array into the worksheet, and use the worksheet range as the chart data source.
 

dcanham

Active Member
Joined
Jun 7, 2006
Messages
306
Thanks...I like that way better. Its a good way to see exactly what numbers came up outside of execution.
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,922
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
It's so much easier in many ways to dump the data somewhere where you can see them, or use them in another way, or even just debug the process. Sometimes people are so intent on hiding numbers, using them directly in a chart, or saving cells that they forget how important it is to be able to access them.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,061
Messages
5,545,763
Members
410,704
Latest member
Cobber2008
Top