How to return the data handling in Matrix passed as parameter to routines in VBA / Excel?

Question

How to return the data handling in Matrix passed as parameter to routines in VBA / Excel?

Navigation

#1 by (1 votes)

0

I need to pass distinct arrays to a routine that sorts its elements and receives other treatments, and then I want it to return those sorted and treated values to the original array ; for example, I passed out-of-order values and some fractionated into the routine through an array, first the fractional values are rounded and then sorted, then returned to the array that "fed" the routine in this way (without decimals and ordered).

First the data is taken from a spreadsheet in Excel and fill in the matrices that I will treat. This data can not be changed because it is typed data and must remain in the order and as it was typed, however, I need to resolve these cases only by VBA .

The idea is to have a routine that treats the matrix passed to it, which works, but that the values of the original matrix, receive back the treated values; this I could not do or find how to do.

I looked it up for VBA , but what I found only refers to using these array values within the routine , but not returning the result to the original array .

The example below has been taken from the link :

Understanding Parameter Arrays

The example works to receive and manipulate these values, however I need to have the results returned to the original array which in my case are dimensioned variables, not just data as this example shows.

Can you do this in VBA / Excel?

The following example shows how you can define a procedure with an array of parameters.

Sub AnyNumberArgs(strName As String, ParamArray intScores() As Variant) 

     Dim intI As Integer 

     Debug.Print strName; " Scores" 

   ' Use UBound function to determine upper limit of array. 
     For intI = 0 To UBound(intScores()) 

         Debug.Print " "; intScores(intI) 

     Next intI 

End Sub

The following examples show how you can call this procedure.

AnyNumberArgs "Jamie", 10, 26, 32, 15, 22, 24, 16 

AnyNumberArgs "Kelly", "High", "Low", "Average", "High"

Basically the tests I did had this format, however I made numerous attempts to change the way the array is used, calling the function, changing the type of variable declared in the function, and so on. How the basics did not work. I did not have the complete code, because I tried to test first before doing the routine, since I had never done this before: I used the function because the idea is that it "returns" with the data processed and I did not make public statements, it would work this way. So the code below shows the structure of the idea of how to try to solve the problem:

Private Sub CommandButton1_Click()

    ...
    ...

    MatrizA() = TrataDepoisRetornaMatriz( MatrizA(), QtdeDeItensDaMatrizA )

    MatrizB() = TrataDepoisRetornaMatriz( MatrizB(), QtdeDeItensDaMatrizB )

    MatrizC() = TrataDepoisRetornaMatriz( MatrizC(), QtdeDeItensDaMatrizC )

    ...
    ...
    ...
End Sub


Private Function TrataDepoisRetornaMatriz( _
      ParamArray Matriz() As Variant, _
           Byval QtdeDeItensDaMatriz As Integer) _
              As Variant
    ...
    ...
    ...

    TrataDepoisRetornaMatriz = Matriz()

End Function

matriz excel vba

asked by anonymous 05.07.2016 / 19:53

1 answer

Integration between systems with PHP [closed] How to remove dynamically selected images before submitting

score 1 · Accepted Answer

The solution is presented in the code below.

The example code is simplified to show with small adaptations to the link link indicated by Evert , how to work the main need :" pass data from one array to another array in a function and have the results of this array handled in this function returned to the array that fed it.

Option Explicit

Private Sub UserForm_Initialize()

    Dim i As Integer

    ' Declare dynamic array - not allocated
    Dim arr() As String

    ' Return new array
    arr = GetArray

    For i = 0 To 5

        MsgBox arr(i)

    Next i

End Sub


Public Function GetArray() As String()

    Dim i As Integer

    ' Create and allocate new array
    Dim arr(0 To 5) As String

    ' Return array

    For i = 0 To 5

        arr(i) = i & " elevado ao quadrado é igual a " & i ^ 2

    Next i

    GetArray = arr

End Function

Of course, the treatments mentioned in the question can also be done, such as ordering data, for example, but the issue is not the treatment itself, but the solution to this problem of transferring and receiving value from matrices in a function.