PDA

View Full Version : [SOLVED] Relation between two or more "Sub"



Radka
11-05-2004, 03:33 AM
I have two Sub, which calculate the average of data. Example: First Sub calculates the "VoltN", and second Sub - the "VoltS". Now I have to connect these two Subs and to calculates the difference between "VoltN" and "VoltS".
As a result in the new cell I have to write down: "DeltaV=VoltN-VoltS".

How can I connect the Subs, so that they work as one?

Jacob Hilderbrand
11-05-2004, 04:52 AM
You can use Global Variables to store the values from the two Subs. To do that put the Dim VoltN and Dim VoltS statements above the Sub line. Like this:



Option Explicit
Dim VoltN As Double
Dim VoltS As Double

Sub Macro1()
'Code
End Sub

Sub Macro2()
'Code
End Sub


You can also turn the Subs into Functions and get the values directly when the third macro runs.

Killian
11-05-2004, 06:13 AM
There are two approaches to look at here:
The first is to control the "scope" or visibility of the variables you are dealing with (DeltaV, VoltN and VoltS)
If you declare your variables (Dim VoltN, VoltS) inside the Sub, then they will not be visible elswhere - they are delared at procedure level.
If, however, you declare them outside, in the General declarations section at the start of the module code and then write your Subs, the variables will be available to all the procedures in that module - they are delared at module level.
Example: (paste this to a module and run GetDeltaV)


Dim VoltN, VoltS 'declared first
Sub GetDeltaV()
'call our subs
GetVoltN
GetVoltS
'use the results
DeltaV = VoltN - VoltS
End Sub

Sub GetVoltN()
'code to calc VoltN
VoltN = 10 * 3
End Sub

Sub GetVoltS()
'code to calc VoltS
VoltS = 5 * 3
End Sub

(If you comment out the declaration, the variables are effectively declared at procedure level and in GetDeltaV, they will both be "Empty")
So why not increase the scope of all variables this way, or take the next step and declare them as "Public" - visible to the entire project? The problem is that if a variable can be read by another Sub somewhere else, it can also be changed. You may use VoltN in another procedure, change it's value, then come back later in the code to use GetDeltaV and get an unexpected result. Remeber that this may not produce an actual error, so you may not even notice for some time. As your code get bigger and more complex, trying to find where this has happened can be a real nightmare!
Scope is an important programming concept that can be used to your advantage but you need to keep track of whats going on in your project as a whole and manage the scope of your variables appropriately.

So the second approach? Keep your variables' scope at procedure level and pass them as arguments as needed.
A function is a procedure that returns a value. Here, when we make our Function declarations, we specify that when the function is called, an integer will be returned to the calling procedure.


Function GetVoltN(VoltN As Integer)
'code to calc VoltN
VoltN = 10 * 3
End Function

Function GetVoltS(VoltS As Integer)
'code to calc VoltS
VoltS = 5 * 3
End Function

Sub GetDeltaV()
'some temp procedure variables to hold our returned values
Dim a As Integer
Dim b As Integer
' call the function - the value returned will be assigned to the arguments (a and b)
Call GetVoltN(a)
Call GetVoltS(b)
DeltaV = a - b
End Sub

It seems a little more complex but you can see the sense in working this way. A function can be written once to return a frequently used value then called whenever that value is needed. Building a library of frequently used functions is always a good idea if you want to save writing the same code over and over. A good example is MsgBox - you pass your arguments, the MsgBox function draws the box and returns a result based on the button clicked.

Hope this helps

K :-)

Radka
11-05-2004, 06:52 AM
Thank both of you very very much !!!!

Now I'm trying!

Thanks!

Richie(UK)
11-05-2004, 09:55 AM
Nice first post, Killian. Welcome to the board :)

Zack Barresse
11-05-2004, 10:18 AM
Nice first post, Killian. Welcome to the board :)

Indeed!! :yes

geekgirlau
11-08-2004, 06:43 PM
Just a postscript to Killian's excellent post :super:

There is an additional advantage to creating a custom function in Excel, in that you can use it directly in a formula. To use Killian's example, to show DeltaV in a cell, your formula would be something along these lines:


=GetVoltN(A1)-GetVoltS(B1)

You can type the custom function directly into the cell, or go to the User Defined category in the Excel function wizard, which will also show you the variables that your function might be expecting.

Generally I would recommend against increasing the scope of your variables unless there is absolutely no alternative. Having had to debug code in the past that was liberally populated with global variables, it's very difficult to maintain, and in most cases unnecessary.

Radka
11-09-2004, 09:46 AM
Dear Killian, I tryed your suggestion, but for me they didn't work. Maybe, I make a mistake, but I don't know where is it.
I used follows code:



Function GetVoltN(VoltN As Double)
Dim VoltN As Double
Dim Difference As Double
Dim i As Long
Dim n As Long
Dim AvrPoints As Boolean
Dim LastRow As Long
LastRow = ActiveSheet.Range("B65536").End(xlUp).Row
For i = 0 To LastRow
n = LastRow - i
VoltN = Application.WorksheetFunction.Average(ActiveSheet.Range("B" & n & ":B" & LastRow))
Difference = VoltN - Cells(n - 1, 2)
If Difference > 0.0004 Then
AvrPoints = True
Exit For
End If
Next i
If AvrPoints = True Then
MsgBox "The point, from with the averaging starts is:_" & n
MsgBox "The value of Vn[mV] is:_" & VoltN
End If
ActiveSheet.Cells(2, 4).Value = n
ActiveSheet.Cells(2, 5).Value = VoltN
End Function

'Calculation of Vs[mV]


Function VoltS(VoltS As Double)
Dim VoltS As Double
Dim Difference As Double
Dim i As Long
Dim AvrPoints As Boolean
Dim LastRow As Long
LastRow = ActiveSheet.Range("B65536").End(xlUp).Row
For i = 1 To LastRow
VoltS = Application.WorksheetFunction.Average(ActiveSheet.Range("B1:B" & i))
Difference = Cells(i + 1, 2) - VoltS
If Difference > 0.0004 Then
AvrPoints = True
Exit For
End If
Next i
If AvrPoints = True Then
MsgBox "The number of averaged points is:_" & i
MsgBox "The value of Vs[mV] is:_" & VoltS
End If
ActiveSheet.Cells(1, 4).Value = i
ActiveSheet.Cells(1, 5).Value = VoltS
End Function

Sub GetDeltaV()
Dim a As Integer
Dim b As Integer
Call GetVoltN(a)
Call GetVoltS(b)
DeltaV = a - b
MsgBox DeltaV
End Sub

Zack Barresse
11-09-2004, 09:52 AM
Hi Radka,

I've added the [ VBA ], [ VBA ] tags to your post. Can you see how it makes your code look? We thank Mark007 for that, a Board Coder. :yes

Radka
11-09-2004, 10:00 AM
Thank you, Firefytr.
I am new in the Forum, and new in the VBA.

Thank all of you for help! :hi:

Killian
11-09-2004, 10:07 AM
Hiya,

I just have a couple of minutes to go so I wo'nt get to test the code but I see a couple of problems scanning thru....
First, you don't need to declare the VoltN & VoltS since you do that in the function declaration
More importantly, the a & b variables in GetDeltaV must be declared as doubles, since that's the data type you're passing
Finally, you need to rename your second function GetVoltS
...and from what I can see, that should work

I'm in the UK and it's (past) time to go home now so good luck

Regards K :-)

mdmackillop
11-09-2004, 10:12 AM
Hi,
The following should run, but I've not checked the logic of the result.
MD


Function GetVoltN(VoltN As Double)
Dim Difference As Double
Dim i As Long
Dim n As Long
Dim AvrPoints As Boolean
Dim LastRow As Long
LastRow = ActiveSheet.Range("B65536").End(xlUp).Row
For i = 0 To LastRow
n = LastRow - i
VoltN = Application.WorksheetFunction.Average(ActiveSheet.Range("B" & n & ":B" & LastRow))
Difference = VoltN - Cells(n - 1, 2)
If Difference > 0.0004 Then
AvrPoints = True
Exit For
End If
Next i
If AvrPoints = True Then
MsgBox "The point, from with the averaging starts is:_" & n
MsgBox "The value of Vn[mV] is:_" & VoltN
End If
ActiveSheet.Cells(2, 4).Value = n
ActiveSheet.Cells(2, 5).Value = VoltN
End Function

'Calculation of Vs[mV]


Function GetVoltS(VoltS As Double)
Dim Difference As Double
Dim i As Long
Dim AvrPoints As Boolean
Dim LastRow As Long
LastRow = ActiveSheet.Range("B65536").End(xlUp).Row
For i = 1 To LastRow
VoltS = Application.WorksheetFunction.Average(ActiveSheet.Range("B1:B" & i))
Difference = Cells(i + 1, 2) - VoltS
If Difference > 0.0004 Then
AvrPoints = True
Exit For
End If
Next i
If AvrPoints = True Then
MsgBox "The number of averaged points is:_" & i
MsgBox "The value of Vs[mV] is:_" & VoltS
End If
ActiveSheet.Cells(1, 4).Value = i
ActiveSheet.Cells(1, 5).Value = VoltS
End Function

Sub GetDeltaV()
'some temp procedure variables to hold our returned values
Dim a As Double
Dim b As Double
' call the function - the value returned will be assigned to the arguments (a and b)
Call GetVoltN(a)
Call GetVoltS(b)
DeltaV = a - b
MsgBox DeltaV
End Sub

Radka
11-09-2004, 10:28 AM
I have made some changes in my code, but for my regret it continue isn't working.
This is the last code:


Function GetVoltN()
Dim Difference As Double
Dim i As Long
Dim n As Long
Dim AvrPoints As Boolean
Dim LastRow As Long
LastRow = ActiveSheet.Range("B65536").End(xlUp).Row
For i = 0 To LastRow
n = LastRow - i
VoltN = Application.WorksheetFunction.Average(ActiveSheet.Range("B" & n & ":B" & LastRow))
Difference = VoltN - Cells(n - 1, 2)
If Difference > 0.0004 Then
AvrPoints = True
Exit For
End If
Next i
If AvrPoints = True Then
MsgBox "The point, from with the averaging starts is:_" & n
MsgBox "The value of Vn[mV] is:_" & VoltN
End If
ActiveSheet.Cells(2, 4).Value = n
ActiveSheet.Cells(2, 5).Value = VoltN
End Function

'Calculation of Vs[mV]


Function VoltS()
'Dim VoltS As Double - Delete, named as function
Dim i As Long
Dim AvrPoints As Boolean
Dim LastRow As Long
LastRow = ActiveSheet.Range("B65536").End(xlUp).Row
For i = 1 To LastRow
VoltS = Application.WorksheetFunction.Average(ActiveSheet.Range("B1:B" & i))
Difference = Cells(i + 1, 2) - VoltS
If Difference > 0.0004 Then
AvrPoints = True
Exit For
End If
Next i
If AvrPoints = True Then
MsgBox "The number of averaged points is:_" & i
MsgBox "The value of Vs[mV] is:_" & VoltS
End If
ActiveSheet.Cells(1, 4).Value = i
ActiveSheet.Cells(1, 5).Value = VoltS
End Function

Sub GetDeltaV()
Dim a As Double
Dim b As Double
a = GetVoltN
b = GetVoltS
DeltaV = a - b
MsgBox DeltaV
End Sub

mdmackillop
11-09-2004, 02:32 PM
Hi Radka,
Can you zip your excel file and attach it, to test with "live" data
MD

Radka
11-10-2004, 12:46 AM
This is my code. It consists 3 Subs, and I have to joint them i.e. these three Subs should be executed concequently.
How I can do this?

Killian
11-10-2004, 02:39 AM
Hi again,
below is your original code with the changes I suggested. I ran it with the data you posted later and it seems to work... well there aren't any errors. I can only assume the results are what you're looking for!?

Keep us posted k :-)



Function GetVoltN(VoltN As Double)
' the function declaration above casts variable VoltN as data type Double
' as the value that is retruned when the function is called
Dim Difference As Double
Dim i As Long
Dim n As Long
Dim AvrPoints As Boolean
Dim LastRow As Long
LastRow = ActiveSheet.Range("B65536").End(xlUp).Row
For i = 0 To LastRow
n = LastRow - i
VoltN = Application.WorksheetFunction.Average(ActiveSheet.Range("B" & n & ":B" & LastRow))
Difference = VoltN - Cells(n - 1, 2)
If Difference > 0.0004 Then
AvrPoints = True
Exit For
End If
Next i
If AvrPoints = True Then
MsgBox "The point, from with the averaging starts is:_" & n
MsgBox "The value of Vn[mV] is:_" & VoltN
End If
ActiveSheet.Cells(2, 4).Value = n
ActiveSheet.Cells(2, 5).Value = VoltN
End Function

'Calculation of Vs[mV]


Function GetVoltS(VoltS As Double)
Dim i As Long
Dim AvrPoints As Boolean
Dim LastRow As Long
LastRow = ActiveSheet.Range("B65536").End(xlUp).Row
For i = 1 To LastRow
VoltS = Application.WorksheetFunction.Average(ActiveSheet.Range("B1:B" & i))
Difference = Cells(i + 1, 2) - VoltS
If Difference > 0.0004 Then
AvrPoints = True
Exit For
End If
Next i
If AvrPoints = True Then
MsgBox "The number of averaged points is:_" & i
MsgBox "The value of Vs[mV] is:_" & VoltS
End If
ActiveSheet.Cells(1, 4).Value = i
ActiveSheet.Cells(1, 5).Value = VoltS
End Function

Sub GetDeltaV()
Dim a As Double
Dim b As Double
'***we call the function which returns a value (a Double)
'***this value is passed to the named argument, our local variable, a
Call GetVoltN(a)
Call GetVoltS(b)
DeltaV = a - b
MsgBox DeltaV
End Sub

Radka
11-10-2004, 03:12 AM
Hi Killian, now all works perfectly. Thank you very much!