CODEWITHSUNDEEP
excelvbasubroutine
Pak Mariman
Pak Mariman

Reputation: 45

Passing 2-dimensional arrays between subroutines Excel VBA

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

Upvotes: 2

Views: 1261

Answers (3)

DisplayName
DisplayName

Reputation: 13386

along the lines of @Jeeped 's approach, you could simplify code by turning ReadData() to a Function, as follows:

Sheet1:

Function ReadData() As Variant
    On Error Resume Next
    ReadData = Application.InputBox("Mark the data array", Type:=8).Value
End Function

Module1:

Option Explicit
Private Sub CommandButton1_Click()
    Dim InputArray As Variant

    InputArray = ReadData

    If IsEmpty(InputArray) Then
        MsgBox "not a valid selection", vbCritical
    Else
        'do your stuff with InputArray
    End If
End Sub

or you could have your ReadData() function return a Boolean to flag for a successful range selection :

Sheet1:

Function ReadData(arr As Variant) As Boolean
    On Error Resume Next
    arr = Application.InputBox("Mark the data array", Type:=8).Value
    ReadData = Not IsEmpty(arr)
End Function

Module1:

Option Explicit

Private Sub CommandButton1_Click()
    Dim InputArray As Variant

    If ReadData(InputArray) Then
        'do your stuff with InputArray
    Else
        MsgBox "not a valid selection", vbCritical
    End If        
End Sub

Upvotes: 0

user4039065
user4039065

Reputation:

No need for public variables at all.

option explicit

'** Sheet1**:
Private Sub CommandButton1_Click()
    dim InputArray as variant, i as long, j as long

    ReadData InputArray 

    for i = lbound(InputArray, 1) to ubound(InputArray, 1)
        for j = lbound(InputArray, 2) to ubound(InputArray, 2)
            debug.print InputArray(i, j)
        next j
    next i

End Sub

'**Module1**:
Option Explicit

Sub ReadData(byref arr as variant)
    dim myrange as range

    Set MyRange = Application.InputBox("Mark the data array", Type:=8)

    If Not MyRange Is Nothing Then
        arr  = MyRange.value
    End If

End Sub

enter image description here

 1 
 4 
 7 
 10 
 2 
 5 
 8 
 11 
 3 
 6 
 9 
 12

Upvotes: 1

QHarr
QHarr

Reputation: 84475

Populate the array as follows and inspect in the locals window. By using .Value you create a 2D array from the selected sheet range. MyRange I think can be local scope and declared as Range. InputArray should probably be a local scope as well and simply passed as an argument to other subs/functions.

Public InputArray As Variant

Sub ReadData()
    Dim MyRange As Range
    Set MyRange = Application.InputBox("Mark the data array", Type:=8)
    If Not MyRange Is Nothing Then
        InputArray = MyRange.Value
        Stop '<==Delete me after inspection
    End If
End Sub

Sheet

Upvotes: 1

Related Questions