Argument syntax problem  
Author Message
AndersBank





PostPosted: Visual Basic for Applications (VBA), Argument syntax problem Top

I have a problem with the syntax of my code. I first have a loop that checks if an object in the selection is a chart. If it is a chart then it passes that chart on to a sub as an argument. In that sub changes are made to the chart in therms of style, size etc. My code:

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

Sub linjeDiagramKnapp(argChart As Object)
Dim mySize As Double
With argChart.Chart
.ApplyCustomType ChartType:=xlUserDefined,TypeName:="Standard"
With argChart.Chart
.Height = 150
.Width = 150
.Top = 100
.Left = 100
End With

My problem is that I do not know how to write the part of the code referring to the chart. Now it is all wrong. But I pass obj.Chart as an argument and in the following sub I refer to it as argChart but I do not know when to write argChart.chart or not. Please help me out! Any assistance appreciated! Thanks very much!



Microsoft ISV Community Center Forums3  
 
 
Cindy Meister





PostPosted: Visual Basic for Applications (VBA), Argument syntax problem Top

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)




 
 
AndersBank





PostPosted: Visual Basic for Applications (VBA), Argument syntax problem Top

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!


 
 
Cindy Meister





PostPosted: Visual Basic for Applications (VBA), Argument syntax problem Top

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



 
 
AndersBank





PostPosted: Visual Basic for Applications (VBA), Argument syntax problem Top

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!


 
 
Cindy Meister





PostPosted: Visual Basic for Applications (VBA), Argument syntax problem Top

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".