Passing Variables through Loops

Ed Song

Board Regular
Joined
Sep 1, 2014
Messages
90
Can a subroutine both pass and call up variables? I am attempting a complex numerical iteration program that requires subroutines to both pass and call up variables. If it can't be done, is this a reason why I should switch to a Gnu Octave or Matlab program?

Because my program is very complicated, I will only show you an outline of my program (or at least what I want my program to be) which goes like this:

Option Explicit

Sub Main ()

Dim t As Integer
Dim w As Integer
Dim TotalRev As Double
Dim ErrorRev As Double
Dim ETotalRev As Double

ErrorRev = 10
t = 1
w = 1

Do
A

Do While (ErrorRev > 0.005)
B
C
D
E
F
G
H

If w = 100 Then
ErrorRev = 0.001
End If

If w > 1 And w < 100 Then
ErrorRev = Abs((ETotalRev-TotalRev)/TotalRev)
Else ErrorRev = 10
End If

w = w + 1
TotalRev = ETotalRev
Loop

(Lines of Code - Mostly printing out tables on Excel worksheet)

t = t + 1
Loop Until t = 41
End Sub

Sub A (Signature)
(Routine that sets up initial values of variables, then recalculates the values for each value of t, t = 1.....40)
End Sub

Sub B (Signature)
(Routine that takes values from A and calculates values for new variables)
End Sub

Sub C (Signature)
(Essentially the same a B but with different variables)
End Sub

Sub D (Signature)
(Essentially the same a C but with different variables)
End Sub

Sub E (Signature)
(Essentially the same a D but with different variables)
End Sub

Sub F (Signature)
(Essentially the same a E but with different variables)
End Sub

Sub G (Signature)
(Essentially the same a F but with different variables)
End Sub

Sub H (Signature)
(Aggregate Variables. Depending on the condition, the program either loops back to B or to A or program finishes)
End Sub

Subroutines B through G can be collapsed into one subroutine if I learned how to use arrays. However, it is also my understanding that arrays can only be passed through ByRef and not ByVal. I kind of know how to arrays, but I don't know how to print output on Excel worksheet using arrays.

Unfortunately, it is my understanding that a sub routine cannot both pass to or call other sub routines. It has to do one or the other, usually the main subroutine calls up variables from the other routines, and the other routines passes variable or its values to the main routine.

If this is true, then the iteration process has to be done in one subroutine. Is my analysis correct? If so, what other free programs can I learn that will produce nice tables and charts like Excel?
 
Last edited:

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Forum statistics

Threads
1,147,516
Messages
5,741,625
Members
423,674
Latest member
Charles2dodo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top