VBA Excel chart in User form error - Compiler Error- Argument not optional

Question

I'm trying to write a program that gets the values from an excel spreadsheet and puts them into a chart on a userform. I'm using Excel 2011, and I've downloaded a copy of Microsoft OWC 10.0 to allow the addition of a chart. My code is below:

Private Sub UserForm_Initialize()

Dim M As Integer Dim N As Integer

With UserForm1.ChartSpace1

M = .Charts.Count
If M = 0 Then GoTo COMEHERE Else GoTo Finish

COMEHERE: .Charts.Add With .Charts(0) ' Create a bar chart. .Type = chChartTypeXYScatterLine

           ' Add data series to the chart.

            N = .SeriesCollection.Count
            If N = 0 Then GoTo COMEHERE1 Else GoTo Finish

COMEHERE1:

            .SeriesCollection.Add
        'Worksheets("Sheet1").Range("D1").Value = 1 ' test to check how far program gets


        ' Set the properties of the first data series.
          With .SeriesCollection(0)
            .SetData chDimSeriesNames = Worksheets("Sheet1").Range("A1").Value
            .SetData chDimXValues = Worksheets("Sheet1").Range("A3:A12").Value
            .SetData chDimYValues = Worksheets("Sheet1").Range("B3:B12").Value

           End With
           .HasLegend = True

Finish:

        End With

End With End Sub

The M and N integers are a hangover from originally writing the code as private sub ChartSpace1_Click(), where I'd get a new chart and series every time I clicked the mouse. The values in columns A3:A12 and B3:B12 are simply 1 to 10

When the code gets to the .setData function, it falls over (Compiler Error - Argument not optional). I've seen similar examples of this code online where people have apparently gotten around this, but I couldn't figure it out or get their code to work.

The purpose for this code is to go into a larger program that I'm writing to show various scientific graphs based on user inputs.

ANy help that people have would be greatly appreciated

J

Your use of setData is incorrect. See example here for some guidance: support.microsoft.com/kb/235885/EN-US — Tim Williams
– Tim Williams, Commented Mar 14, 2014 at 4:30
Hi Tim, Thanks for the response - Unfortunately it doesn't help too much as the .datasource command being used here appears to not be compatible with OWC charts. I found something online (vbaexpress.com/forum/…) where the solution appeared to be to insert a OWC spreadsheet into the work book, copy values out of the active workbook and paste into the OWC spreadsheet, and plot from that. Modifying the code from that website I was able to get the program working — user3417982
– user3417982, Commented Mar 14, 2014 at 6:36

Jon Peltier · Accepted Answer · 2014-03-18 12:43:12Z

0

You may find this approach to be much easier: Displaying A Chart In A Userform. No need for OWC or other added libraries. It's written for a Windows version of Excel, but you can probably figure out how to make it work on a Mac.

answered Mar 18, 2014 at 12:43

Jon Peltier

6,1011 gold badge32 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3417982 Over a year ago

Hi Jon, Thanks for the link. I had previously came across a post by you suggesting this method, and yes it would be much easier, but I'm trying to design the entire macro so its "self sufficient" and can easily run on any other computer (with Excel and VBA). The program is intended to do some 1D geophysical modelling, and there are several userforms worth of inputs and outputs, with several charts. J

Jon Peltier Over a year ago

I would think saving a gif of a chart and using it as the picture of an image control would be more portable than hoping OWC is present and would work on any computer.

user3417982 Over a year ago

Hi John, Your probably right. However, I've decided to re-programme the whole thing in VB as an executable file, and charts (seem) to be handled better in VB. Also, the program seems less buggy and runs faster by storing the data for the charts in a database table rather than accessing it from excel.

Collectives™ on Stack Overflow

VBA Excel chart in User form error - Compiler Error- Argument not optional

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related