PDA

View Full Version : Calling a Sub from another Sub()



tomzko
12-27-2015, 07:24 PM
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!

Aussiebear
12-27-2015, 08:47 PM
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.

tomzko
12-27-2015, 09:35 PM
15052 here is a snap of the problem, it says: "Compilation Error, it has not been defined Sub or function"

mikerickson
12-27-2015, 10:48 PM
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

tomzko
12-28-2015, 10:15 AM
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 :)
15055

SamT
12-28-2015, 08:54 PM
(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.

tomzko
12-28-2015, 09:03 PM
i finally did it :D i feel so good making my first macro thx for you all! :P