PDA

View Full Version : Solved: Passing objects into procedure



JoeMarfice
08-16-2007, 02:14 PM
OK, I feel like a complete dolt here, but for the life of me - I cannot figure out how to pass an object in.

Here's an example program:

' ***********
Sub PassChart()
Dim cht As ChartObject
Set cht = ActiveChart
PlayWithChart (cht)
End Sub

Sub PlayWithChart(Optional cht As Chart)
MsgBox "Success!"
End Sub
' ***********

So, in theory, the subroutine PlayWithChart would receive a Chart Object, and do something with it. But instead, I get the error
Run-time error '91':
Object variable or With block variable not set.

Can someone steer me?

TIA. Much.

daniel_d_n_r
08-16-2007, 02:36 PM
Falling short of answering your question exactly I would say go into VB Editor and read the VB help, type chartobject into the help search.

There are many entrie there to give you a start.

Then record a few macros inserting,formatting and altering charts and their data.

Worksheets("sheet1").ChartObjects(1).Chart. _
ChartArea.Interior.Pattern = xlLightDown


Charts(1).SeriesCollection(1).Interior.Color = RGB(255, 0, 0)


Charts("sales").Move after:=Sheets(Sheets.Count)


Charts(1).ActivateWith ActiveChart
.Type = xlLine
.HasTitle = True
.ChartTitle.Text = "January Sales"End With


Charts("chart1").ActivateActiveSheet.SeriesCollection(1).Interior.ColorIndex = 5


these are a few examples I have pasted directly from the microsoft help file, somthing like this should get you started.
The help file also has chart methods .chart collection,,ect.

cheers

rory
08-16-2007, 02:43 PM
Firstly, ChartObject is not the same as Chart. Secondly, if you pass an object to a subroutine, you should not enclose it in parentheses:

Sub PassChart()
Dim cht As Chart
Set cht = ActiveChart
PlayWithChart cht
End Sub

mdmackillop
08-16-2007, 02:43 PM
Sub PassChart()
Dim cht As ChartObject

Set cht = ActiveSheet.ChartObjects("Chart 1")
PlayWithChart cht
End Sub

Sub PlayWithChart(Optional cht As ChartObject)
MsgBox "Success!"
MsgBox cht.Name

End Sub

JoeMarfice
08-16-2007, 02:51 PM
See, I knew it was something simple!

(BTW, Rory: good catch on Chart vs ChartObjects. My bad.)

(daniel_d_n_r: I think you missed the entire point of my question. I didn't ask for help on playing with charts; done that for years. I needed help passing objects (like Charts, for instance) into procedures.)

Once again, thank you, mdmackillop. You can get by passing strings & numbers that way ( ex: "PlayWithString(str)"), but not Objects.

Dammit.

:banghead:

rory
08-16-2007, 02:55 PM
Just to be explicitly clear, if you use Call, then you would use parentheses with a subroutine:
Call PlayWithChart(cht)
but otherwise not.

mdmackillop
08-16-2007, 03:01 PM
Just to be explicitly clear, if you use Call, then you would use parentheses with a subroutine:
Call PlayWithChart(cht) but otherwise not.
... and if it's a function of course
Sub PassChart()
Dim cht As ChartObject
Set cht = ActiveSheet.ChartObjects("Chart 1")
MsgBox PlayWithChart(cht)
End Sub

Function PlayWithChart(cht As ChartObject)
PlayWithChart = cht.Name
End Function

rory
08-16-2007, 03:11 PM
But only if you do something with the result!

Bob Phillips
08-16-2007, 03:33 PM
Not true. I use functions all the time, in fact rarely use Subs in my code, and don't always return a result let alone always use it.

rory
08-16-2007, 03:44 PM
I didn't say you had to use the result of a function. I said that you only use parentheses around the arguments if you use the result.

Bob Phillips
08-16-2007, 03:54 PM
Sub writedoc()
Call myFunc("xyz")
End Sub

Function hello(var1)
MsgBox myFunc
End Function

Bob Phillips
08-16-2007, 03:55 PM
Sub writedoc()
Call myFunc("xyz")
End Sub

Function hello(var1)
MsgBox myFunc
End Function

rory
08-16-2007, 04:16 PM
I'm not sure what that's supposed to show, but let me clarify what I was saying:
1. If you pass an argument to a subroutine you should not use parentheses unless you use the Call statement.
2. If you pass an argument to a function, you should not use parentheses unless you use the Call statement or use the return value. You can get away with it some of the time, but it is not good practice and you will get caught out passing objects.
3. Neither of the above apply in .Net where you should always use parentheses.

RichardSchollar
08-17-2007, 12:19 AM
I'm not sure what that's supposed to show, but let me clarify what I was saying:
1. If you pass an argument to a subroutine you should not use parentheses unless you use the Call statement.
2. If you pass an argument to a function, you should not use parentheses unless you use the Call statement or use the return value. You can get away with it some of the time, but it is not good practice and you will get caught out passing objects.
3. Neither of the above apply in .Net where you should always use parentheses.

That was interesting Rory (specifically point 2 above) - I didn't even realise you could pass an argument to a function (in VBA) without using parentheses!

Richard

Bob Phillips
08-17-2007, 12:32 AM
I'm not sure what that's supposed to show...

Because you said


I didn't say you had to use the result of a function. I said that you only use parentheses around the arguments if you use the result.

and I was showing that that was an incorrect statement

daniel_d_n_r
08-17-2007, 01:52 AM
(daniel_d_n_r: I think you missed the entire point of my question. I didn't ask for help on playing with charts; done that for years. I needed help passing objects (like Charts, for instance) into procedures.)


:banghead:


The VBA help file is absolutely full of good info on the chart object.

Dim ch As ChartObject
Set ch = Worksheets("sheet1").ChartObjects.Add(100, 30, 400, 250)
ch.Chart.ChartWizard source:=Worksheets("sheet1").Range("a1:a20"), _
gallery:=xlLine, title:="New Chart"


after looking for another 5 seconds I just found this example pasted directly from the VBA help file.
cheers

rory
08-17-2007, 02:06 AM
OK, my fault - I thought it was inferrable from my previous mention of Call with subroutines, that that would be an exception. Hopefully my last one covered all bases...