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

Asked

Viewed 1,201 times

0

I need to pass separate matrices for a routine that sorts its elements and receive other treatments, and after that I want it to return those ordered and treated values to the original matrix; for example, I passed values out of order and some fractionated to the routine through a matrix, first the fractionated values are rounded and then ordered, then return to the matrix that "fed" the routine this way (without the decimal and ordered).

First the data is taken from a spreadsheet on Excel and fill in the matrices that I will treat. These data can not be changed, because it is data typing and must remain in order and as they were entered, anyway, need to solve these cases only by VBA.

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

I researched it for the VBA, but what I found only refers to use these matrix values within the routine, however not returning the result to the original matrix.

The example below was taken from link:

Basic notions about parameter matrices

The example works to receive and manipulate these values, but I need to have the results returned to the original matrix that in my case are scaled variables, and not only data as shown in this example.

You can do this in VBA/Excel?

The following example shows how you can define a procedure with a matrix 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 changing the way of using the matrices, calling the function, changing the type of variable declared in the function, etc. As the basic did not work. I don’t have the complete code, because I tried to test first before doing the routine, since I had never done this before: I used function, because the idea is that it "comes back" with the data processed and I did not make public statements, because I wanted to know if it would work this way. Therefore, the code below presents 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
excel vba matrix

  • When you say that the ordering of the "passed" matrix works, but cannot return to these values for the original matrix... would this original matrix be, or would be, in a spreadsheet/table? 'Cause I could do a simple macro to sort the desired column... if it’s not that, I guess I didn’t get it right... excuse me

  • Evert, the data is only taken from the spreadsheet, not returned. An example: the matrices (vectors) A, B and C took three distinct groups of data from the spreadsheet. It has the function/routine (I tested both cases), Sorted, which I pass as parameter one vector at a time I want to sort, informing the vector (A, B or C) and the respective amount of items for sorting. There are three function calls. As I present the data from A, B and C later, I need them to have the result of sorting. That’s it.

  • Evert, I know it seems strange I don’t sort first on the spreadsheet, is that I presented the simplified problem, my case is more complex and it’s not just about ordering the data, they are treated as well. If it works for this case, it will also work for what I need, that’s it buddy.

  • I edited the question to clarify.

  • Where have you declared your matrix? In a module? Or are you treating as classes? I believe that if you have matrix 1 declared globally, you will not need two... take the matrix with the data, make the changes in the values, within the function itself have a temporary matrix and return the filled matrix as accurate.... If you still can’t, show how you did your implementation, show your code to make it easier to identify the best way to use it. This microsoft code runs ok, but as was its implementation?

  • I didn’t put the code because what I did didn’t work in several ways that I tried, the code is the form’s own, I didn’t do it in a separate module or create a class (I haven’t worked with classes yet, but I’d like to). I’ll draft a call.

  • It is commented in your question that worked the ordination... I will see hj in the work I have Excel.

  • Hi Evert, I edited the question, the "example" is that it works when receiving the data from the matrix, this I also could, but return the data after some modification in the routine is that I could not.

  • 1

    Leo, I don’t have Excel at home... now that I understand what you can’t do... on the second I send you a function returning the data... but if you do as you did, you will be able to, putting the data read in an array and returning as a function or placing an array declared as global. Here’s how to do http://excelmacromastery.com/Blog/index.php/the-complete-guide-to-using-arrays-in-excel-vba/, sorry I can’t help you completely... but I left windows for development. In the second I answered your question and I believe it will be to the satisfaction. Hug and good fds

  • Evert, thank you, worked perfectly and without declaring the variable as Public. I await your example to mark the answer and vote. The link is didactic, excellent. Thanks!

  • Hi Evert, will you post the answer? If it is I wait, or let me know that I post not to be unanswered.

  • 1

    Oh Leo I didn’t have time. You can post here that you should already have until the routine of ordering you to do here, I think it will be more complete. Abs and Thanks for the consideration. If you want to mark my comment with the link as useful to the reply. Hug

  • Thanks for the tip, hug!

Show 8 more comments

1 answer

1


The solution is presented in the code below.

The example of the code is simplified to show with small adaptations to the link http://excelmacromastery.com/Blog/index.php/the-complete-guide-to-using-arrays-in-excel-vba/ indicated by Evert, how to make it work main need: "pass data from one matrix to another matrix in a function and have the results of this matrix treated in this function returned to the matrix 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 in the same way can be made the treatments cited in the question, as the data ordering, for example, but the issue is not the processing itself, but the solution of this problem of transferring and receiving value of matrices in a function.

Browser other questions tagged excel vba matrix

You are not signed in. Login or sign up in order to post.