Theory behind passing workbook
How Workbook objects are passed into functions, is it passed as copy of
workbook or is passed as reference? please help me |
Theory behind passing workbook
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) "kris" wrote in message ... How Workbook objects are passed into functions, is it passed as copy of workbook or is passed as reference? please help me |
Theory behind passing workbook
"Chip Pearson" wrote: 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) "kris" wrote in message ... How Workbook objects are passed into functions, is it passed as copy of workbook or is passed as reference? please help me 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 |
Theory behind passing workbook
"kris" wrote in message
... <snip 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 |
Theory behind passing workbook
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" |
Theory behind passing workbook
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 "kris" wrote in message ... 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" |
All times are GMT +1. The time now is 11:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com