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

prabha_friend · Sep 10, 2021

Code:

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

MajP · Sep 10, 2021

arnelgp · Sep 10, 2021

You need String for the Array.

Code:

Sub CAT_Report_RD()
Dim element As Variant
Dim i As Integer
Dim Inputfiles As Variant
Dim CYLedger As Object
Dim PYDLedger As Object
Dim IO_PO As Object
Dim AiREAS As Object
Dim LossEstimations As Object
Inputfiles = Array("CYLedger", "PYDLedger", "IO_PO", "AiREAS", "LossEstimations")
For i = 0 To UBound(Inputfiles)
    element = Application.GetOpenFilename("Excel, *.xls*", , "Select " & Inputfiles(i) & " workbook")
    If Len(element & "") > 1 Then
        Select Case i
            Case 0
                Set CYLedger = Workbooks.Open(Filename:=element, ReadOnly:=True)
            Case 0
                Set PYDLedger = Workbooks.Open(Filename:=element, ReadOnly:=True)
            Case 0
                Set IO_PO = Workbooks.Open(Filename:=element, ReadOnly:=True)
            Case 0
                Set AiREAS = Workbooks.Open(Filename:=element, ReadOnly:=True)
            Case 0
                Set LossEstimations = Workbooks.Open(Filename:=element, ReadOnly:=True)
        End Select
    End If
Next
MsgBox CYLedger.Name
End Sub

theDBguy · Sep 10, 2021

Hi. Welcome to AWF!

Are you doing this in Excel?

cheekybuddha · Sep 10, 2021

Code:

' ...
        Select Case i
            Case 0
                Set CYLedger = Workbooks.Open(Filename:=element, ReadOnly:=True)
            Case 0
                Set PYDLedger = Workbooks.Open(Filename:=element, ReadOnly:=True)
            Case 0
                Set IO_PO = Workbooks.Open(Filename:=element, ReadOnly:=True)
            Case 0
                Set AiREAS = Workbooks.Open(Filename:=element, ReadOnly:=True)
            Case 0
                Set LossEstimations = Workbooks.Open(Filename:=element, ReadOnly:=True)
        End Select
' ...

Interesting Case statements!

MajP · Sep 10, 2021

As I said "yes", Paramarray arguments can and in fact always are passed byRef. In VBA paramarray are always ByRef and in VB it is always by val. So I do not even know what the real question is. The code is illogical so that does not help. I cannot even find a way for it compile using byval.

Code:

Public Sub ParamByRef(ParamArray TheVals() As Variant)
  'if not passed by reference then then values would change
  TheVals(0) = TheVals(0) + 2
  TheVals(1) = "Good Night World"
  TheVals(2) = False
End Sub

Public Sub TestByRef()
  Dim x As Long
  Dim STR As String
  Dim z As Boolean

  x = 1
  STR = "Hello World"
  z = True
  ParamByRef x, STR, z
  Debug.Print "X " & x & " STR " & STR & " Z " & z
End Sub

Clearly all values are passed by ref

Code:

X 3 STR Good Night World Z False

cheekybuddha · Sep 10, 2021

MajP said:
Public Sub ParamByRef(ParamArray TheVals() As Variant)

That's the first reference to ParamArray I've seen in any of the code in this thread

A collection sounds more appropriate to the concept attempted in the OP

MajP · Sep 10, 2021

Again, no idea what is really being asked. But what the OP is trying is doable. You can declare objects and assign them to an array, uninstantiated. Then you can instantiate them from the array. No idea why you would ever do this, but it can be done.

Code:

Public Sub Demo()
  Dim RS As DAO.Recordset
  Dim RS2 As DAO.Recordset
  Dim aObs() As Variant
  Dim i As Integer
  Dim db As DAO.Database
  'assign two uninstantiated objects to an array
  aObs = Array(RS, RS2)
  Set db = CurrentDb
  For i = 0 To UBound(aObs)
   If i = 0 Then Set aObs(i) = db.OpenRecordset("table1")
   If i = 1 Then Set aObs(i) = db.OpenRecordset("Select ID from table1")
  Next i
  Debug.Print aObs(0).Name
  Debug.Print aObs(1).Name
End Sub

prabha_friend · Sep 13, 2021

Hi All,
Sorry for the late reply. I went to my native village thus, was out of internet...

prabha_friend · Sep 13, 2021

theDBguy said:
Hi. Welcome to AWF!

Are you doing this in Excel?

Yes. But the Question in Generic for VBA.

prabha_friend · Sep 13, 2021

MajP said:
As I said "yes", Paramarray arguments can and in fact always are passed byRef. In VBA paramarray are always ByRef and in VB it is always by val. So I do not even know what the real question is. The code is illogical so that does not help. I cannot even find a way for it compile using byval. Further where is a Paramarray used in the code?

Code:

Public Sub ParamByRef(ParamArray TheVals() As Variant) 'if not passed by reference then then values would change TheVals(0) = TheVals(0) + 2 TheVals(1) = "Good Night World" TheVals(2) = False End Sub Public Sub TestByRef() Dim x As Long Dim STR As String Dim z As Boolean x = 1 STR = "Hello World" z = True ParamByRef x, STR, z Debug.Print "X " & x & " STR " & STR & " Z " & z End Sub

Clearly all values are passed by ref

Code:

X 3 STR Good Night World Z False

I cannot even find a way for it compile using byval. Further where is a Paramarray used

Inputfiles = Array(CYLedger, PYDLedger, IO_PO, AiREAS, LossEstimations)

In the Array function... The parameters are passed as Paramarray only.

prabha_friend · Sep 13, 2021

cheekybuddha said:
That's the first reference to ParamArray I've seen in any of the code in this thread

A collection sounds more appropriate to the concept attempted in the OP

Hi. I Need your Help. A small reference on using Dictionary here would be Helpful. Thank You.

cheekybuddha · Sep 13, 2021

Prabhakaran said:
Inputfiles = Array(CYLedger, PYDLedger, IO_PO, AiREAS, LossEstimations)

Just a technicality: the above is not a ParamArray.

A ParamArray, whilst it is an array, is a special type of function parameter which allows for a variable number of arguments to be passed.

The above is just a plain array.

A Collection is a type of supercharged array (it is an Object vs a datatype), and is best used for holding a number of other Objects. It has methods to add and delete from the Collection, and properties for each object such as .Key which can be used to refer to a particular member, and .Count which gives you the number of members contained.

To help you we better need to understand the purpose of what you are trying to achieve. Why do you need an array or collection or Workbooks? Will the number be variable? Or is just to reduce repetitive coding? Or ...

prabha_friend · Sep 13, 2021

cheekybuddha said:
Just a technicality: the above is not a ParamArray.

A ParamArray, whilst it is an array, is a special type of function parameter which allows for a variable number of arguments to be passed.

The above is just a plain array.

A Collection is a type of supercharged array (it is an Object vs a datatype), and is best used for holding a number of other Objects. It has methods to add and delete from the Collection, and properties for each object such as .Key which can be used to refer to a particular member, and .Count which gives you the number of members contained.

To help you we better need to understand the purpose of what you are trying to achieve. Why do you need an array or collection or Workbooks? Will the number be variable? Or is just to reduce repetitive coding? Or ...

Just a technicality: the above is not a ParamArray.

A ParamArray, whilst it is an array, is a special type of function parameter which allows for a variable number of arguments to be passed.

The above is just a plain array.

I am not telling the variable 'inputfiles' is a ParamArray. But inside the 'Array( )' function, The sheet object variables (CYLedger, PYDLedger, IO_PO, AiREAS, LossEstimations) are being passed as ParamArray only. So my Question here is: "Is it possible to pass the sheet objects variable through 'by ref' mode?

To help you we better need to understand the purpose of what you are trying to achieve. Why do you need an array or collection or Workbooks? Will the number be variable? Or is just to reduce repetitive coding? Or ...

To reduce repetitive Coding... and also for "Career Enhancement"

Hope you Help. Thanks.

cheekybuddha · Sep 13, 2021

OK, I'm not sure where you are having the problem.

As @MajP pointed out, objects added to an array using Array() function are added ByRef.

I did the following test:

Code:

Function ArrayTest() As Boolean

  Dim wb1 As Object, wb2 As Object, arrWB As Variant, i As Integer

  Set wb1 = fGetWorkbook("C:\Users\dm\Desktop\test1.xls")
  Set wb2 = fGetWorkbook("C:\Users\dm\Desktop\test2.xls")

  arrWB = Array(wb1, wb2)
  For i = LBound(arrWB) To UBound(arrWB)
    Debug.Print arrWB(i).Name
  Next i
  For i = LBound(arrWB) To UBound(arrWB)
    arrWB(i).Close False
    Set arrWB(i) = Nothing
  Next i
  
  Debug.Print "wb1 Is Nothing:", wb1 Is Nothing
  Debug.Print "wb2 Is Nothing:", wb2 Is Nothing
  Set wb1 = Nothing
  Set wb2 = Nothing
  Debug.Print "arrWB(0) Is Nothing:", arrWB(0) Is Nothing
  Debug.Print "arrWB(1) Is Nothing:", arrWB(1) Is Nothing


  ArrayTest = Err = 0

End Function

(fGetWorkbook() is a utility function to get an Excel workbook object, see code later)

In the Immediate Window (Ctrl+G) I get:

Code:

?ArrayTest
PepysRoadBookings.xlsm
qryYourTableWithCounts2.xls
wb1 Is Nothing:             False
wb2 Is Nothing:             False
arrWB(0) Is Nothing:        True
arrWB(1) Is Nothing:        True
True

As you can see I got the .Name property and was able to close the workbooks.

I was not able to set their original object variables to Nothing by using the array elements, probably because the original object variables still held a reference

(NB I have edited this because I wasn't reading my results properly

)

Utility function code:

Code:

Option Compare Database
Option Explicit

Function fGetExcel(blNewInstance As Boolean) As Object
On Error Resume Next

  Dim objXL As Object

  blNewInstance = False
  Set objXL = GetObject(, "Excel.Application")
  If Err <> 0 Then
    Set objXL = CreateObject("Excel.Application")
    blNewInstance = True
    Err = 0
  End If
  Set fGetExcel = objXL

End Function

Function fGetWorkbook(strPathToFile As String, _
                      Optional blAlreadyOpen As Boolean, _
                      Optional objXLPass As Object) As Object
On Error Resume Next

  Dim objXL As Object, blNewInstance As Boolean, objWB As Object, strFileName As String

  If Not objXLPass Is Nothing Then
    Set objXL = objXLPass
  Else
    Set objXL = fGetExcel(blNewInstance)
  End If
  strFileName = Dir(strPathToFile)
  Set objWB = objXL.Workbooks(strFileName)
  If Err Then Err = 0
  If objWB Is Nothing Then
    Set objWB = objXL.Workbooks.Open(strPathToFile)
  Else
    blAlreadyOpen = True
  End If
  Set fGetWorkbook = objWB
  Set objXL = Nothing

End Function

prabha_friend · Sep 13, 2021

When this is working...

Code:

arrWB = Array(wb1, wb2)
  For i = LBound(arrWB) To UBound(arrWB)
    Debug.Print arrWB(i).Name
  Next i

Why not this...?

Code:

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

Where am I doing wrong...?

cheekybuddha · Sep 13, 2021

(First, I made some edits to my previous reply - did you see them?)

Why not this...?

It seems to work for me.

I did this test (I used an array for the workbook names because I didn't want tot create file dialog code):

Code:

Function ArrayTest2() As Boolean

  Dim wb1 As Object, wb2 As Object, arrWB As Variant, i As Integer, arrWBNames As Variant

  arrWB = Array(wb1, wb2)
  arrWBNames = Array("C:\Users\dm\Desktop\test1.xls", _
                     "C:\Users\dm\Desktop\test2.xls")
  For i = LBound(arrWB) To UBound(arrWB)
    Set arrWB(i) = fGetWorkbook(CStr(arrWBNames(i)))
  Next i
  For i = LBound(arrWB) To UBound(arrWB)
    Debug.Print arrWB(i).Name
  Next i
  For i = LBound(arrWB) To UBound(arrWB)
    arrWB(i).Close False
    Set arrWB(i) = Nothing
  Next i
 
  Debug.Print "wb1 Is Nothing:", wb1 Is Nothing
  Debug.Print "wb2 Is Nothing:", wb2 Is Nothing
  Set wb1 = Nothing
  Set wb2 = Nothing
  Debug.Print "arrWB(0) Is Nothing:", arrWB(0) Is Nothing
  Debug.Print "arrWB(1) Is Nothing:", arrWB(1) Is Nothing
  
  ArrayTest2 = Err = 0

End Function

I get the following result:

Code:

?ArrayTest2
test1.xls
test2.xls
wb1 Is Nothing:             True
wb2 Is Nothing:             True
arrWB(0) Is Nothing:        True
arrWB(1) Is Nothing:        True
True

This time I could also set the array elements to Nothing, probably because they were the ones originally 'Set'

prabha_friend · Sep 13, 2021

Thanks Cheeky.

Can someone tell me the Difference on Using 'For Each' vs 'For i = '

arnelgp · Sep 13, 2021

array cannot hold Object:

Code:

Sub CAT_Report_RD()
Dim element As Variant
Dim i As Integer
Dim Inputfiles As Variant
Dim CYLedger As Object
Dim PYDLedger As Object
Dim IO_PO As Object
Dim AiREAS As Object
Dim LossEstimations As Object
Inputfiles = Array("CYLedger", "PYDLedger", "IO_PO", "AiREAS", "LossEstimations")
For i = 0 To UBound(Inputfiles)
    'element = Application.GetOpenFilename("Excel, *.xls*", , "Select " & Inputfiles(i) & " workbook")
    element = Inputfiles(i)
    If Len(element & "") > 1 Then
        Select Case i
            Case 0
                Set CYLedger = Workbooks.Open(Filename:=element, ReadOnly:=True)
            Case 1
                Set PYDLedger = Workbooks.Open(Filename:=element, ReadOnly:=True)
            Case 2
                Set IO_PO = Workbooks.Open(Filename:=element, ReadOnly:=True)
            Case 3
                Set AiREAS = Workbooks.Open(Filename:=element, ReadOnly:=True)
            Case 4
                Set LossEstimations = Workbooks.Open(Filename:=element, ReadOnly:=True)
        End Select
    End If
Next
MsgBox CYLedger.Name
End Sub

cheekybuddha · Sep 13, 2021

arnelgp said:
array cannot hold Object:

I originally thought this, but my test showed different.

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

Prabhakaran Karuppaih

You've got your good things, and you've got mine.

..forever waiting... waiting for jellybean!

I’m here to help

AWF VIP

You've got your good things, and you've got mine.

AWF VIP

You've got your good things, and you've got mine.

Prabhakaran Karuppaih

Prabhakaran Karuppaih

Prabhakaran Karuppaih

Prabhakaran Karuppaih

AWF VIP

Prabhakaran Karuppaih

AWF VIP

Prabhakaran Karuppaih

AWF VIP

Prabhakaran Karuppaih

..forever waiting... waiting for jellybean!

AWF VIP

Similar threads

Users who are viewing this thread