Argument syntax problem

Argument syntax problem

• Gib20055

  Hello Cindy! Thank you very mcuh for your help! However I seem to have problems writing your code. The thing is that I know what you mean but I really am bad at syntax. The code looks like:

  Sub arrayLoop()
  Dim obj As Object
  Dim myChart As Excel.Chart
  For Each obj In Selection
  If TypeName(obj) = "ChartObject" Then
  Set myChart = obj
  Call linjeDiagramKnapp(myChart)
  End If
  Next
  end sub

  Sub linjeDiagramKnapp(argChart As Excel.Chart)
  Dim mySize As DoubleWith argChart
  with argChart

  .ApplyCustomType ChartType:=xlUserDefined, TypeName:="Standard"
  
  With argChart
  .Height = 150
  .Width = 150
  .Top = 100
  .Left = 100
  End With

  With argChart.PlotArea
  .Height = 100
  .Width = 100
  .Top = 10
  .Left = 10
  .Interior.ColorIndex = xlNone
  End With

  The problem is:

  1) I get "incompatible types" at the line "Set myChart = obj". You had not declared obj in your code so I did that because otherwise I got "Undefined variable".

  2) Assuming that I fix problem 1) how shall I refer to the chart Eg if I want to change the plot area as in the last piece of code, is the current syntax correct If not how shall I write it

  Please, please help me out here! I know that I am a pain but I really cannot solve it! Thank you so much for all your help! Any assistance always appreciated!
  

  
  

• Jo-Jo

  Hi Anders
  
  Just use argChart. You shouldn't have to use argChart.Chart, as you do
  in the sample code you show, as it's already typed as a Chart.
  
  If this isn't working, you need to tell us about the error it throws...
  
  -- Cindy
  
  
  
  

• Christian Sparre

  Hi Anders
  
  OK, then I need more information. Since you haven't corrected me, I
  guess you are working in Excel (you will note I'm WORD MVP, so my
  knowledge of the Excel object model is not extensive).
  
  To what is Selection supposed to refer Can you describe the Excel sheet
  or workbook so that I can try to replicate what you're doing I get the
  impression you have chart objects embedded on the sheet, but...
  
  To answer your second question, I'm going to describe a way for you to
  figure it out yourself. You'll learn more that way :-) From what I can
  tell, your code is correct, except that you repeat With argChart (look
  carefully at the Dim line) - you will need to correct that.
  
  Then set up a procedure where you start with a Chart object (rather than
  trying to pick it up from a bunch of objects). Pass that to this
  function and see what the result is. If that works, then you know the
  function is fine and you/we can concentrate on getting the Chart out of
  the "object".
  
  
  
  

• programmingisfun

  Hello Cindy! Thanks for your help. However my big problem was not how pass the object as an arhument but how to refer to it later in the sub. Assuming that I use your code:

  Dim myChart as Excel.Chart
  For Each obj In Selection
  If TypeName(obj) = "ChartObject" Then
  Set myChart = obj
  Call linjeDiagramKnapp(myChart)
  End If
  Next
  
  Sub linjeDiagramKnapp(argChart As Excel.Chart)
  
  how shall I refer to the chart when making the changes as I wrote in my question Any help appreciated and thank you very much for your help!

  
  

• Glenn Haworth

  There's certainly more than one way to approach this. What I'd probably
  do is pass a chart object. More or less:
  
  Dim myChart as Excel.Chart
  For Each obj In Selection
  If TypeName(obj) = "ChartObject" Then
  Set myChart = obj
  Call linjeDiagramKnapp(myChart)
  End If
  Next
  
  Sub linjeDiagramKnapp(argChart As Excel.Chart)
  
  
  
  
  
• Argument syntax problem