Solved Possible to pass the ParamArray arguments through 'By ref' ? (1 Viewer)

[prabha_friend]

Sub CAT_Report_RD()
Dim CYLedger As Workbook
Dim PYDLedger As Workbook
Dim IO_PO As Workbook
Dim AiREAS As Workbook
Dim LossEstimations As Workbook
Dim Inputfiles As Variant
Inputfiles = Array(CYLedger, PYDLedger, IO_PO, AiREAS, LossEstimations)
For Each element In Inputfiles
    Set element = Workbooks.Open(Application.GetOpenFilename(), ReadOnly:=True)
Next element
MsgBox CYLedger.Name
End Sub

 

[theDBguy]

No, I was saying that if the objects are added to the array ByRef then after

  For Each wb In arrWB
    Set wb = fGetWorkbook(CStr(arrWBNames(i)))
    i = i + 1
  Next wb

then wb1 and wb2 should not be Nothing because arrWB(0) and arrWB(1) should be pointers to those objects, and the wb in the For loop should act on those elements.

Okay, thanks for the explanation. I may have to digest that a bit. Cheers!

 

[cheekybuddha]

I'm only thrashing this out since it's the OP's question.

Personally, I wouldn't approach it this way. I'd either use a collection or an array of workbooks (ie Dim wb(5) As Workbook).

But I've probably spent too much time on this already to do any more testing. Would be interested to hear your further thoughts/discoveries.

d

 

[theDBguy]

I'm only thrashing this out since it's the OP's question.

Personally, I wouldn't approach it this way. I'd either use a collection or an array of workbooks (ie Dim wb(5) As Workbook).

But I've probably spent too much time on this already to do any more testing. Would be interested to hear your further thoughts/discoveries.

d

Could you please double check if my thinking is correct in this little experiment?

At the time we declare a variable, I believe Access allocates a memory space for it. So, the below code tries to see if this address is the same throughout the code as we try to manipulate the object variable.

Am I thinking correctly here? Here's my attempt so far...

 

[cheekybuddha]

@Leo

Will you post your code ('cos I'm too lazy to type it all out! )

 

[theDBguy]

@Leo

Will you post your code ('cos I'm too lazy to type it all out! )

Sorry about that. Here you go...

Option Compare Database
Option Explicit

Public Sub TestByRef()
'thedbguy@gmail.com
'9/13/2021

Dim db As Object
Dim rs1 As Object
Dim rs2 As Object
Dim arr() As Variant
Dim var As Variant

'display memory address of array object variables
Debug.Print "address of rs1: " & VarPtr(rs1)
Debug.Print "rs1 Is Nothing: "; rs1 Is Nothing
Debug.Print "address of rs2: " & VarPtr(rs2)
Debug.Print "rs2 Is Nothing: "; rs2 Is Nothing

'put the object variables inside an array
arr = Array(rs1, rs2)

'display the memory address of the objects in the array
Debug.Print "address of arr(0): " & VarPtr(arr(0))
Debug.Print "rs1 Is Nothing: "; rs1 Is Nothing
Debug.Print "address of arr(1): " & VarPtr(arr(1))
Debug.Print "rs2 Is Nothing: "; rs2 Is Nothing

Set db = CurrentDb()

'try to instantiate the objects in the array using for/each
For Each var In arr
    Set var = db.OpenRecordset("Table1")
    Debug.Print "address of var: " & VarPtr(var)
    Debug.Print "rs1 Is Nothing: "; rs1 Is Nothing
    Debug.Print "rs2 Is Nothing: "; rs2 Is Nothing
Next

Set var = Nothing
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing

End Sub

 

[prabha_friend]

So. What's the Verdict then?

 

[Nixversteher]

Byref means that the pointer of the variable is passed. a "z =Array(varptr(..), Varptr(...), ...)" would be equivalent to that. But VBA can't do anything with pointers. But it is possible, with a small helper function and a completely insane manipulation of a variant data type. Therefore it is easier, as already mentioned here, to create an array with the object data type or to use a collection.

 

[theDBguy]

So. What's the Verdict then?

In post #32, I showed what you could add to your code to make it work. Have you tried it? For example, maybe something like:

...
Inputfiles = Array(CYLedger, PYDLedger, IO_PO, AiREAS, LossEstimations)
i = 0
For Each element In Inputfiles
    Set element = Workbooks.Open(Application.GetOpenFilename(), ReadOnly:=True)
    Set Inputfiles(i) = element
    i = i + 1
Next element
...

Hope that helps...