function reference or by value?

function reference or by value?

Post by CodeMonke » Wed, 24 Sep 2008 01:34:36

Lets say I have this:

dim objExcel , objWorkBook, objWorkSheet
Set objExcel = Server.CreateObject("Excel.Application")
Set objWorkBook = objExcel.WorkBooks.Add

Create_WorkSheet_Tab(objWorkBook )

Sub Create_WorkSheet_Tab(objWorkBook )

Set objWorkSheet = objWorkBook.WorkSheets.Add
objWorkSheet.Name = "new"
objWorkSheet.Outline.SummaryRow = xlAbove


End Sub

Sub Create_WorkSheet_Header(objWorkSheet)

' buncha stuff
End Sub

now then, I think I messed up...because paramenters are not being
passed by reference (I.E., after all of this objExcel will have a new
worksheet with new headers). It is pointless to pass objWorkbook and
objWorksheet as paramenters?


function reference or by value?

Post by Richard Mu » Wed, 24 Sep 2008 02:00:24

The object reference objWorkBook has global scope. There is no need to pass
this as a parameter to the Subs. The way you have it the object reference
objWorkBook in Sub Create_WorkSheet_Tab is not the same as the reference in
the main program. And objWorkSheet in Sub Create_WorkSheet_Tab is not the
same as the reference in Create_WorkSheet_Header. I would eliminate the Subs
altogether to avoid the confusion. I see no need for them.

If there is a need, perhaps make the Subs into Functions and assign the
object reference to the value of the funtion in a Set statement.

Richard Mueller
MVP Directory Services
Hilltop Lab -


function reference or by value?

Post by OldDo » Wed, 24 Sep 2008 02:02:02

> Set objWorkSheet = objWorkBook.WorkSheets.Add> > objWorkSheet.Name = "ne>"
> objWorkSheet.Outline.SummaryRow = xlA>ov>
> Create_WorkSheet_Header(objWork>he>t)
> >nd>Sub
> Sub Create_WorkSheet_Header(objWor>Sh>et)
> bunc>a stuff
>>En> Sub
> now then, I think I messed up...because paramenters are n>t being
> passed by reference (I.E., after all of this objExcel will ha>e a new
> worksheet with new headers). t is pointless to pass objWork>ook and
> objWorksheet as para>en>ers?
> John

Well, I am not sure why you need the sub at all. Seems like this would
so it.

Dim XL , Book, Sheet
Set XL = CreateObject("Excel.Application")
Const xlAbove = 0

XL.Visible = True
XL.DisplayAlerts = False
set book = XL.Workbooks.Add
Set Sheet = Book.WorkSheets.Add
Sheet.Name = "new"
Sheet.Outline.SummaryRow = xlAbove

function reference or by value?

Post by ekkehard.h » Wed, 24 Sep 2008 06:19:19

odeMonkey schrieb:

(1) Using parameters instead of global variables is a good thing

(2) Avoiding to answer a question about parameter passing by proposing
to use globals is not a good thing

(3) In VBScript Parameters are passed by reference (default); this can
be changed by using "ByVal" in the function/sub definition or by
putting a parameter in "pass me per value" parentheses

Example code:

Dim one : one = "one"
Dim two : two = "two"
WScript.Echo 0, one, two
toGerman one, (two)
WScript.Echo 1, one, two
toGerman one, two
WScript.Echo 2, one, two

Dim reOne : Set reOne = New RegExp
Dim reTwo : Set reTwo = New RegExp
WScript.Echo 3, CStr( reOne Is reTwo )
isSameObj 4, reOne, reOne
isSameObj 5, reOne, (reOne)

WScript.Quit 0

Sub toGerman( ByRef frs, ByRef sec ) ' ByRef is default
frs = "eins"
sec = "zwei"
End Sub

Sub isSameObj( nCnt, o1, o2 ) ' ByRef is default
WScript.Echo nCnt, CStr( o1 Is o2 )
End Sub


=== passParams: passing params (value/reference)
0 one two
1 eins two
2 eins zwei
3 Falsch
4 Wahr
5 Wahr
=== passParams: 0 done (00:00:00) ===============

From output line 1 you can see that the () in "toGerman one, (two)"
send a copy of variable two to the sub. Changing this copy doesn't
affect the variable. Called without the () in "toGerman one, two" the
sub can do its work properly.

(4) While function calls need parameter list parentheses - e.g.
"Server.CreateObject("Excel.Application")", sub calls *don't*.
So you sub calls like "Create_WorkSheet_Header(objWorkSheet)" are
bad/misleading VBScript, because what looks like parameter list ()
are in fact "pass me per value" brackets. So write
Create_WorkSheet_Header objWorkSheet
or (if you like () and Call)
Call Create_WorkSheet_Header( objWorkSheet )

(4) Objects are references, so passing them by value (accidentialy)
will still allow access to same underlying object the reference
points to. See output line 5 as evidence: "isSameObj 5, reOne, (reOne)"
passes a reference and a copy of that reference, both point to the
same RegExp.

(5) I don't know if I understand your

"this objExcel will have a new worksheet with new headers"

correctly. You use

Set objExcel = Server.*CreateObject*("Excel.Application")

That will start a *new* instance of Excel. Furthermore you
ask Excel to create a new workbook:

Set objWorkBook = objExcel.WorkBooks.Add

Of course it will have new sheets with new headers (if you write
them in Sub Create_WorkSheet_Header.

Perhaps you meant to use GetObject and/or .Workbooks.Open to
access an existing sheet?

(6) To sum up: I don't think that your problem is caused by parameter