All Excel Properties Not Available When Updating Embedded Chart

I have a Lotus Notes form which has an embedded chart in it.  I am wanting to change the chart based on user input, but for some reason, some excel properties and/or methods do not seem available to me. For instance, in the following code, each line runs just fine EXCEPT the ".plotby".  This produces an OLE Autpmation Object Error.  

      Dim wb As Variant
      Dim ChartObject as Variant
      Dim xlSheet As Variant
      Dim myChart As Variant
      Dim sheet1 As Variant
      Set ChartObject= uDoc.GetObject("Chart1")
      Set wb = uDoc.GetObject("ExpenseSheet")
      Set sheet1 = wb.ActiveSheet

      Set myChart = ChartObject.Charts(1)
      With mychart      
            .HasTitle = True
            .ChartTitle.Text = "Monthly Expense Chart"
            .SetSourceData (sheet1.Range("A1:D9"))
            .PlotBy = xlcolumns
      End With

Yet, when I use virtually the same code in Excel, as shown below, I have no problem:

Dim chtObject As ChartObject
Dim mychart As Chart

    Set chtObject = Me.ChartObjects(1)
    Set mychart = chtObject.Chart
    With mychart
        .HasTitle = True
        .ChartTitle.Text = "Monthly Expense Chart"
        .SetSourceData Source:=sheet1.Range("A1:D9")
        .PlotBy = xlColumns
    End With

What am I missing here?  Is there a declaration I need to make somewhere?  

In a perhaps related issue, I am not able to use the " := " reference in LN the same way as I am in Excel, as shown in the 9th line of the above Excel example; it always produces an error.  Is this normal?  I'm really most concerned with my first issue, but since I'm here already, I thought I'd mention this since it may be related.

Thanks to all who respond.
Who is Participating?
Sjef BosmanGroupware ConsultantCommented:
Try the value 2
Sjef BosmanGroupware ConsultantCommented:
If xlColumns is a constant, you have to put that constant in...
sprice205Author Commented:

sjef_bosman:  Using the value "2" worked! Thank you!  Now, I am, of course, absolutely going to award you the points, but could you  please indulge me a bit longer if you will and explain a bit about what you mean by "you must put those constants in,."  I'm afraid this is a great weak point in my development knowledge.  
Could you please provide an example of how I would, for instance, put in the constant for "xlcolumns" ?  

Thank you so much!

Sjef BosmanGroupware ConsultantCommented:
Excel works with predefined named constants, like xlColumns. Notes has no notion of these names, and if you do nothing special it creates a variable with the same name, with a value "" (empty string). Hence the error.

Soo what you could do is
- define a local or global variable xlColumns, and assign it the value 2
- add a Const definition in LotusScript, much like the variable:
      Const xlColumns = 2
- or just bluntly use a 2 instead of xlColumns, I suppose that's what you did

Thanks for the grade!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.