Passing Dynamic Array between Sub-Routines

Question

I'm trying to pass a dynamic array DArrayRight() between different subroutines, from DefineArrayRight (that will create the Array ) to CellRightMarked ( that will perform the action to the worksheets). Unfortunately I tried without success. Any suggestions?

Many thanks

Sub DefineArrayRight()
Dim DArrayRight() As Variant ' dynamic array
Dim xrow As Long, i As Long
i = 0
xrow = 2

ReDim DArrayRight(0) ' resize the array to hold 1 string

 Do Until ThisWorkbook.Sheets("Sheet1").Cells(xrow, 2).Value = ""
  If ThisWorkbook.Sheets("Sheet1").Cells(xrow, 3).Value = "Right" Then
    DArrayRight(i) = ThisWorkbook.Sheets("Sheet1").Cells(xrow, 2).Value 'add the value in the array
    i = i + 1 ' increase the upper bound of the array
    ReDim Preserve DArrayRight(i) ' preserve the array
  End If
  xrow = xrow + 1
 Loop
ReDim Preserve DArrayRight(i - 1) ' delete the empty array
End Sub

and pass to this sub routine:

Sub CellRightMarked()
Dim DArrayRight() As Variant
Dim rcell As Range, rrow As Range
Dim r As Integer, i As Long

 For Each sht In ActiveWorkbook.Worksheets
   With sht
     Set rrow = .UsedRange
       For r = LBound(DArrayRight) To UBound(DArrayRight)
        For Each rcell In rrow
            If rcell.Value = DArrayRight(r) Then
                .Range(rcell.Offset(0, 1), rcell.Offset(0, 1)).Font.Color = 255
            End If
        Next rcell
     Next r
   End With
  Next sht
 End Sub

It's not clear from the posted code how have you tried to pass it. How do you call Sub DefineArrayRight() and Sub CellRightMarked()? — omegastripes
– omegastripes, Commented Jan 11, 2019 at 23:41
Also, why attempt to delete array (which I don't think will work like that)? Just let it go out of scope. — QHarr
– QHarr, Commented Jan 11, 2019 at 23:42
Looks like you to pass DArrayRight in the sub signature for CellRightMarked (and remove its declaration within the receiving sub); as well as, as mentioned, calling it. — QHarr
– QHarr, Commented Jan 11, 2019 at 23:44
@QHarr thanks for your heads-up How could I change the code in order to pass the array to Sub CellRightMarked? — M.Kuz
– M.Kuz, Commented Jan 11, 2019 at 23:49

Variatus · Accepted Answer · 2019-01-12 02:44:56Z

VBA has provided the Function for the kind of thing you want to do with a sub. Here is the function you need. It is based on your sub, suggestions for improvement commented.

Function ArrayRight() As Variant

    Dim Fun() As Variant                    ' function return value
    Dim Ws As Worksheet                     ' easier to refer to
    Dim R As Long, Rl As Long               ' row, last row
    Dim i As Long

    ' i = 0                                 ' i is already zero
    ' R = 2                                 ' defined in the For .. Next loop
    ' ReDim DArrayRight(0) ' resize the array to hold 1 string
    ' Not a good idea because the array will have to be completely
    ' re-written each time you expand it: very slow!

    Set Ws = ThisWorkbook.Sheets("Sheet1")
    Rl = Ws.Cells(Ws.Rows.Count, "B").End(xlUp).Row     ' find the last used row in column B
    ReDim Fun(Rl)                           ' maximum to be possibly required

    For R = 2 To Rl
        If Ws.Cells(R, 3).Value = "Right" Then
            Fun(i) = Ws.Cells(R, 2).Value   'add the value in the array
            i = i + 1                       ' next empty array element
            ' ReDim Preserve DArrayRight(i) ' no need to re-write the array
        End If
    ' R = R + 1                             ' Next is doing the counting
    ' Loop                                  ' Next is doing the looping

    If i Then                               ' skip if no match was found
        ReDim Preserve Fun(i - 1)           ' delete the unused part of array
        ArrayRight = Fun
    End If
End Function

The sub below shows how to use the function.

Sub TryArrayRight()

    Dim Arr As Variant

    ' This is the function call.
    ' simply assign its return value to a variable
    Arr = ArrayRight

    ' now test the return
    If IsEmpty(Arr) Then
        MsgBox "The array is empty"
    Else
        MsgBox "The array has " & UBound(Arr) & " elements."

        ' pass the value to another procedure
        CellRightMarked Arr
    End If
End Sub

An here the variable obtained from the function is passed as a parameter to another routine. I commented out your code because I didn't check it. The object is to show the passing of the variable.

Sub CellRightMarked(DArrayRight As Variant)

'    Dim rcell As Range, rrow As Range
'    Dim R As Integer, i As Long
'
'    For Each sht In ActiveWorkbook.Worksheets
'       With sht
'         Set rrow = .UsedRange
'           For R = LBound(DArrayRight) To UBound(DArrayRight)
'            For Each rcell In rrow
'                If rcell.Value = DArrayRight(R) Then
'                    .Range(rcell.Offset(0, 1), rcell.Offset(0, 1)).Font.Color = 255
'                End If
'            Next rcell
'         Next R
'       End With
'    Next sht
 End Sub

Of course, in this particular case it would be easier to call the function from within CellRightMarked as you have suggested yourself.

Great suggestion! It works pretty well and fast! Thanks a lot. Here how I adapt the sub code: Sub CellRightMarkedFun() , Dim DArrayRight As Variant and DArrayRight = ArrayRight I was wondering if there is a way to use directly the array generated in the function ArrayRight without passing to another array in the sub DArrayRight.
Yes. You can use it directly, too. For best practice, assign it to a variable in the procedure where it is used. Bear in mind that when you pass it to another procedure only its address is passed, not the array itself. Therefore the size of the array doesn't affect the speed of its notification.

M.Kuz · Accepted Answer · 2019-01-12 00:24:19Z

0

I've solved the issue following your suggestions
Many thanks, this is my solution

Sub DefineArrayRight()
...
Call CellRightMarked(darrayright) 
End Sub

and

Sub CellRightMarked(ByRef darrayright As Variant) 
...

edited Jan 12, 2019 at 0:24

answered Jan 12, 2019 at 0:09

M.Kuz

637 bronze badges

3 Comments

QHarr Over a year ago

You can remove the call keyword and the ()

M.Kuz Over a year ago

@QHarr thanks for suggestions, If I remove the Call keywork it works, but if I remove the (darrayright), I have a "Compile Error: Argument not optional" (Error 449)

QHarr Over a year ago

Remove the () not the argument inside the ()

Collectives™ on Stack Overflow

Passing Dynamic Array between Sub-Routines

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related