Consulting

Results 1 to 7 of 7

Thread: Calling a Sub from another Sub()

  1. #1
    VBAX Regular
    Joined
    Dec 2015
    Posts
    7
    Location

    Calling a Sub from another Sub()

    Hi, im new to these forum and also in VBA, ive never used it before and i dont know any other programing languaje.
    Here is my question:
    i made a Sub. in a module that from data of cells generates a matrix that ill need to use later (te sub is called GenerarMatriz() )
    then in sheet1 i made a Sub. that is called Prueba() that executes GenerarMatriz but when i press play an error occurs, here are the codes:
    (i want the answer for the specific question, my intention is not to analyze the full code) ,
    Module:
    Sub GenerateMatriz()
    Dim SumaTotalCamiones, TotalClase, i As Integer
    Dim Matriz() As Integer
    i = 0
    TotalClaseA = Worksheets("hoja1").Cells(4, 1).Value
    TotalClaseB = Worksheets("hoja1").Cells(4 + 1, 1).Value
    TotalClaseC = Worksheets("hoja1").Cells(4 + 2, 1).Value
    SumaTotalCamiones = Worksheets("hoja1").Cells(4, 1).Value + Worksheets("hoja1").Cells(5, 1).Value + Worksheets("hoja1").Cells(6, 1).Value
    ReDim Matriz(SumaTotalCamiones - 1, 3)
    For j = 0 To TotalClaseA
           n = 0
        For n = 0 To 3
            Matriz(i, n) = Worksheets("hoja3").Cells(i + 1, n + 1).Value
        Next n
    Next j
    For j = TotalClaseA To TotalClaseA + TotalClaseB
           n = 0
        For n = 0 To 3
            Matriz(i, n) = Worksheets("hoja3").Cells(i + 1, n + 1).Value
        Next n
    Next j
    For j = TotalClaseA + TotalClaseB To TotalClaseA + TotalClaseB + TotalClaseC
    n = 0
        For n = 0 To 3
            Matriz(i, n) = Worksheets("hoja3").Cells(i + 1, n + 1).Value
        Next n
    Next j
    End Sub
    Sheet1: (Where i want to use my GenerarMatriz)

    Sub Test()
    GenerateMatriz
    Cell(1, 1) = Matriz(1, 1)
    End Sub
    Thanks for your time!
    Last edited by Aussiebear; 12-27-2015 at 08:48 PM. Reason: Added hash tag to code

  2. #2
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    3,862
    Location
    Where I believe the issue lies is that in calling the sub GenerateMatriz the focus then moves to the called sub, therefore the next line in the Sub Test doesn't run.
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  3. #3
    VBAX Regular
    Joined
    Dec 2015
    Posts
    7
    Location
    Capture.jpg here is a snap of the problem, it says: "Compilation Error, it has not been defined Sub or function"

  4. #4
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    There are two routines, the first fills the array Matriz. However Matriz is declared so that it is scoped only to the procedure level. One the Sub GenerateMatriz is finished, the variable Matriz passes out of scope and loses its value.
    There are two ways to approach this.
    One way would be to declare Matriz as a Public variable, so it will be in scope when execution returns to Test.
    Note that the declaration Publid Matriz() As Integer is before any of the routines in that module
    Public  Matriz() As Integer
    
    Sub GenerateMatriz() 
        Dim SumaTotalCamiones, TotalClase, i As Integer 
         
        i = 0 
        TotalClaseA = Worksheets("hoja1").Cells(4, 1).Value 
        TotalClaseB = Worksheets("hoja1").Cells(4 + 1, 1).Value 
        TotalClaseC = Worksheets("hoja1").Cells(4 + 2, 1).Value 
        SumaTotalCamiones = Worksheets("hoja1").Cells(4, 1).Value + Worksheets("hoja1").Cells(5, 1).Value + Worksheets("hoja1").Cells(6, 1).Value 
        ReDim Matriz(SumaTotalCamiones - 1, 3) 
        ' etc
    End Sub
    Sub Test() 
        GenerateMatriz 
        Cell(1, 1) = Matriz(1, 1) 
    End Sub
    A different approach would be to cast GenerateMatriz as a Function rather than a Sub

    Function GenerateMatriz() As Variant
        Dim SumaTotalCamiones, TotalClase, i As Integer 
        Dim Matriz() As Integer 
        i = 0 
        TotalClaseA = Worksheets("hoja1").Cells(4, 1).Value 
        TotalClaseB = Worksheets("hoja1").Cells(4 + 1, 1).Value 
        TotalClaseC = Worksheets("hoja1").Cells(4 + 2, 1).Value 
        SumaTotalCamiones = Worksheets("hoja1").Cells(4, 1).Value + Worksheets("hoja1").Cells(5, 1).Value + Worksheets("hoja1").Cells(6, 1).Value 
        ReDim Matriz(SumaTotalCamiones - 1, 3) 
        For j = 0 To TotalClaseA 
            n = 0 
            For n = 0 To 3 
                Matriz(i, n) = Worksheets("hoja3").Cells(i + 1, n + 1).Value 
            Next n 
        Next j 
        For j = TotalClaseA To TotalClaseA + TotalClaseB 
            n = 0 
            For n = 0 To 3 
                Matriz(i, n) = Worksheets("hoja3").Cells(i + 1, n + 1).Value 
            Next n 
        Next j 
        For j = TotalClaseA + TotalClaseB To TotalClaseA + TotalClaseB + TotalClaseC 
            n = 0 
            For n = 0 To 3 
                Matriz(i, n) = Worksheets("hoja3").Cells(i + 1, n + 1).Value 
            Next n 
        Next j
    
        GenerateMatriz = Matriz
    End Sub
    And call that function from the other routine
    Sub Test()
        Dim myMatriz as Variant
       
        myMatriz = GenerateMatriz()
    
        Cell(1, 1) = myMatriz(1, 1) 
    End Sub

  5. #5
    VBAX Regular
    Joined
    Dec 2015
    Posts
    7
    Location
    thanx for the help but i tried both solutions and im getting the same error :/ i cant find the problem, im uploading my excel for a better analysis
    Generador Random (beta).xlsm

  6. #6
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,238
    Location
    (i want the answer for the specific question, my intention is not to analyze the full code)
    Sorry, but the problem is in the full code.

    For j = 0 To TotalClaseA
    n = 0
    For n = 0 To 3
    Matriz(i, n)

    And also
    For j = 0 To TotalClaseA - 1
    This type problem is continuous through the code.

    After fixing these issues in the code, then you must use one of Mike's suggestions.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  7. #7
    VBAX Regular
    Joined
    Dec 2015
    Posts
    7
    Location
    i finally did it i feel so good making my first macro thx for you all! :P

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •