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

 

[cheekybuddha]

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

@Prabhakaran do you have Option Explicit declared at the top of every code module?

 

[prabha_friend]

No 'Option Explicit'

 

[arnelgp]

I originally thought this, but my test showed differen

show me your code, you all have string array there.

 

[cheekybuddha]

Make sure you add it tot the top of every code module (above or below Option Compare Database.

Then, go to Debug menu in VBA editor and click 'Compile'. It might highlight code errors and undeclared variables.

You never declared element in your For loop, so it might not like its datatype.

 

[cheekybuddha]

show me your code, you all have string array there.

All my code in post #15 and post #17

 

[prabha_friend]

 

[cheekybuddha]

Interesting.

It doesn't work with the For ... Each construct.

I tested by declaring the variable both as Variant and as Object.

Try using the index method (For i = LBound(InputFiles) To ...)

 

[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 i = 0 To UBound(Inputfiles) 'iTerationBook In Inputfiles
    Set Inputfiles(i) = Workbooks.Open(Application.GetOpenFilename())
Next i
MsgBox CYLedger.Name 'Giving Error
MsgBox Inputfiles(0).Name 'Showing the Sheet Name
End Sub

 

[MajP]

Lots of misstatements here.
1. You most certainly can load an array with objects, unless they are user types
2. You can do a For Each on an array

From Microsoft

The For...Each...Next statement syntax has these parts:

SYNTAX

Part	Description
element	Required. Variable used to iterate through the elements of the collection or array. For collections, element can only be a Variant variable, a generic object variable, or any specific object variable. For arrays, element can only be a Variant variable.
group	Required. Name of an object collection or array (except an array of user-defined types).

Declare the array as variant

Public Sub ForEachInArrayOfObjects()
  Dim myArray() As Variant
  Dim ctrl As Access.Control
  Dim i As Integer
  Dim itm As Variant
 
  ReDim myArray(Me.Controls.Count - 1)
  'load array with objects
  For Each ctrl In Me.Controls
    Set myArray(i) = ctrl
    i = i + 1
  Next ctrl
  'For each an array
 
  For Each itm In myArray
    Debug.Print itm.Name
  Next itm
 
End Sub

Results

Combo0
ReviewerID_Label
Command2
Label3
List4
Label5

 

[cheekybuddha]

2. You can do a For Each on an array

No one disputes this!

The curious point is:

' ...
  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
' ...

works fine.

... but:

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

  arrWB = Array(wb1, wb2)
  arrWBNames = Array("E:\Dropbox\Pepys Road\PepysRoadBookings.xlsm", _
                     "C:\Users\dm\Desktop\qryYourTableWithCounts2.xls")
  i = 0
  For Each wb In arrWB
    Set wb = fGetWorkbook(CStr(arrWBNames(i)))
    i = i + 1
  Next wb
  For Each wb In arrWB
    Debug.Print wb.Name
  Next wb
' ...

throws Error 91 (Object variable not set) At Debug.Print wb.Name

 

[prabha_friend]

The curious point is:

Lets see who Solves this issue...

 

[theDBguy]

Lets see who Solves this issue...

This one worked for me... Did I mess it up?

 

[cheekybuddha]

Interesting - my equivalent errors on Debug.Print wb.Name

(I'm using Acc2K7)

Will you try something else as well - add a couple of lines to your sub at the end:

  Debug.Print wb1.Name
  Debug.Print wb2.Name

 

[theDBguy]

Interesting - my equivalent errors on Debug.Print wb.Name

(I'm using Acc2K7)

Will you try something else as well - add a couple of lines to your sub at the end:

  Debug.Print wb1.Name
  Debug.Print wb2.Name

Hi David,

Simply adding those lines errors just like the original code. Did you notice the changes I added? So, doing this also works.

 

[cheekybuddha]

Did you notice the changes I added?

No - sorry - skimmed through without paying enough attention!

OK, I see.

But that suggests that the object in the For Each (wb) is ByVal rather than ByRef as otherwise you wouldn't need the intermediate Set back to the array element object. ????

 

[theDBguy]

But that suggests that the object in the For Each (wb) is ByVal rather than ByRef as otherwise you wouldn't need the intermediate Set back to the array element object. ????

Maybe that's the part of the discussion I missed. However, in my mind, an object has to be set. So, after declaring wb1 As Object, there has to be a Set wb1 = line somewhere. I didn't think using arrWB = Array(wb1, wb2) would be enough to do it. At that point in the procedure, both wb1 and wb2 are still set to Nothing.

 

[cheekybuddha]

Maybe that's the part of the discussion I missed. However, in my mind, an object has to be set. So, after declaring wb1 As Object, there has to be a Set wb1 = line somewhere. I didn't think using arrWB = Array(wb1, wb2) would be enough to do it. At that point in the procedure, both wb1 and wb2 are still set to Nothing.

In which case there's no point in using the array, if you have to set each individual object variable.

 

[The_Doc_Man]

You asked for the difference between "For Each x" vs. "For counter = start To ..."

For Each...Next statement (VBA)

Office VBA reference topic

docs.microsoft.com

For...Next statement (VBA)

Office VBA reference topic

docs.microsoft.com

The "For Each x in x-group" construct steps through the elements in the group, which must have some uniformity of data type. The X that follows the word "Each" can be a variant or it can be the data type that is common to the members of the group, including the possibility of it being an object. Case in point for a group of objects would be "For Each TableDef-object In AllTables..." or "For Each FieldDef-object in MyTable.Fields" for two examples. You would of course supply variables of the appropriate type for the Each object. Groups have indexes (or for arrays, subscripts). The "For Each" construct determines the highest and lowest indexes in the group and steps automatically from lowest to highest with no gaps

The "FOR counter = start TO stop STEP stepsize" construct just counts out numbers in the sequence specified. You can use the "counter" variable as an index or a subscript as appropriate to whatever you are examining, but in fact it would be possible to use it both ways in the same loop if you had both an array AND a collection ... or you might use it in neither of those ways, if all you wanted was to generate a sequence of numbers.

Therefore, the biggest difference in the "For Each" syntax is that it is bound to a collection, whereas the "For counter=start TO stop..." syntax is not necessarily bound to anything at all - except the counter variable.

 

[theDBguy]

In which case there's no point in using the array, if you have to set each individual object variable.

Sorry, I'm still a bit confused. Are you saying the following lines of code should produce/create the objects wb1 and wb2?

Dim wb1 As Object, wb2 As Object

arrWB = Array(wb1, wb2)

When I step through that, both wb1 and wb2 were empty (set to Nothing), so doing Debug.Print wb1.Name produced the "Object Not Set" error.

 

[cheekybuddha]

Sorry, I'm still a bit confused. Are you saying the following lines of code should produce/create the objects wb1 and wb2?

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.