Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF/Then Theory between two or more tabs. | Excel Worksheet Functions | |||
theory of biorhythms | Excel Discussion (Misc queries) | |||
Passing calling workbook name to called workbook | Excel Programming | |||
How to check workbook is already opened before passing the workbook obj to a subroutine in Word | Excel Programming | |||
Linked sheet design theory | Excel Programming |