Passing 2-dimensional arrays between subroutines

[Pak Mariman]

I cannot seem to get this to work.

I want to pass a 2D-array from a subroutine to other subroutines in which a data manipulation should take place.

I am calling below subroutine from a command button on Sheet1. In Module1 I have declared the variables as public. Inside the subroutine ReadData, at the End If statement, InputArray is populated with an array consisting of numbers that the user has marked following the inputbox. After the subroutine ReadData has completed, InputArray is empty.

What is the obvious and blatantly daft thing that am I doing wrong?

Sheet1:
Private Sub CommandButton1_Click()
Call ReadData
End Sub

Module1:
Option Explicit
Option Base 1
Public MyRange, InputArray As Variant

Sub ReadData()
Set MyRange = Application.InputBox("Mark the data array", Type:=8)
If Not MyRange Is Nothing Then

InputArray = MyRange​

End If
End Sub

 

[Peter_SSs]

After the subroutine ReadData has completed, InputArray is empty.

That is not the case for me. If you add this line of code, does it error? If so, what error message? If not, what does it return.
Note that it will error if only a single cell is selected so make sure you select a range of cells



Sheet1:
Private Sub CommandButton1_Click()
Call ReadData
MsgBox "First element in array: " & InputArray(1, 1)
End Sub

 

[dmt32]

Hi,
try passing your Range object variable as an argument to the called procedure.

Sheet1

Option Explicit
Private Sub CommandButton1_Click()
    Dim myRange As Range
    Call ReadData(myRange)
    If Not myRange Is Nothing Then MsgBox myRange.Address
End Sub

Module 1

Sub ReadData(ByRef Target As Range)
    On Error Resume Next
    Set Target = Application.InputBox("Mark the data array", Type:=8)
'cancel pressed
        If Err <> 0 Then Set Target = Nothing
    On Error GoTo 0
End Sub

When passing arguments by reference you are referencing the original variable value. The value of myrange is changed in the called procedure.
You should now be able to use your variable with rest of your code.

Dave

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...2-dimensional-arrays-between-subroutines.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Pak Mariman]

https://stackoverflow.com/questions...arrays-between-subroutines-excel-vba#51581314