Theory behind passing workbook

[Guest]

How Workbook objects are passed into functions, is it passed as copy of
workbook or is passed as reference? please help me

 

[Chip Pearson]

Kris,

Objects are always passed by reference. ByVal or ByRef indicates how the
pointer is passed to the function. If the pointer is passed ByRef, the
called procedure can change the object to which the argument refers and this
will be changed in the calling procedure. If the pointer is passed ByVal,
the object reference isn't changed in the calling procedure.

For example,

Sub Test1()
Dim Rng As Range
Set Rng = Range("A1")
Debug.Print "Before ByVal: " & Rng.Address
ByValExample Rng
Debug.Print "After ByVal: " & Rng.Address

Set Rng = Range("A1")
Debug.Print "Before ByVal: " & Rng.Address
ByRefExample Rng
Debug.Print "After ByVal: " & Rng.Address
End Sub

Sub ByValExample(ByVal R As Range)
Set R = Range("B1")
End Sub

Sub ByRefExample(ByRef R As Range)
Set R = Range("B1")
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Guest]

Kris,

Objects are always passed by reference. ByVal or ByRef indicates how the
pointer is passed to the function. If the pointer is passed ByRef, the
called procedure can change the object to which the argument refers and this
will be changed in the calling procedure. If the pointer is passed ByVal,
the object reference isn't changed in the calling procedure.

For example,

Sub Test1()
Dim Rng As Range
Set Rng = Range("A1")
Debug.Print "Before ByVal: " & Rng.Address
ByValExample Rng
Debug.Print "After ByVal: " & Rng.Address

Set Rng = Range("A1")
Debug.Print "Before ByVal: " & Rng.Address
ByRefExample Rng
Debug.Print "After ByVal: " & Rng.Address
End Sub

Sub ByValExample(ByVal R As Range)
Set R = Range("B1")
End Sub

Sub ByRefExample(ByRef R As Range)
Set R = Range("B1")
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

Thanks Chip Pearson for ur very good example, but why workbooks can't be
passed as Byref. the following code is showing "ByRef Argument type mismatch"

Function func1(ByRef S1 As Workbook)
----------------
end Function

Sub ff()
dim D as workbook
set D=Application.WorkBook.open("C:\D.xls")
call func1(D)
end sub

 

[Peter T]

Thanks Chip Pearson for ur very good example, but why workbooks can't be
passed as Byref. the following code is showing "ByRef Argument type mismatch"

Function func1(ByRef S1 As Workbook)
----------------
end Function

Sub ff()
dim D as workbook
set D=Application.WorkBook.open("C:\D.xls")
call func1(D)
end sub

That works fine for me, subject changing the typo WorkBook to 'WorkBooks'.

Also try simply passing the ActiveWorkbook

Regards,
Peter T

 

[Guest]

I got when it is creating problems, but why i don't know

Function func1(ByRef S1 As Workbook)
MsgBox "hi"
End Function

Sub ff()
Dim D As Workbook
Set D = Application.Workbooks.Open("C:\practice\D.xls")
Call func1(D)
End Sub

Sub ff1()
Dim D, S As Workbook
Set D = Application.Workbooks.Open("C:\practice\D.xls")
Call func1(D)
End Sub

when I call function "Func1" using sub ff1 then i am getting the error msg
"ByRef argument type mismatch"

 

[Peter T]

The error message is reporting exactly what the problem is. The variable 'D'
is declared as variant (which is what it defaults to if you don't fully
declare the variable), ie it does not match the argument in your function.

Change -

Dim D, S As Workbook

to

Dim D as Workbook, S As Workbook

Regards,
Peter T