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
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:
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
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.
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
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
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...
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.