Relation between two or more "Sub"

[Radka]

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]

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]

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]

Thank both of you very very much !!!!

Now I'm trying!

Thanks!

[Richie(UK)]

Nice first post, Killian. Welcome to the board

[Zack Barresse]

Nice first post, Killian. Welcome to the board

Indeed!!

[geekgirlau]

Just a postscript to Killian's excellent post

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]

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]

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.

[Radka]

Thank you, Firefytr.
I am new in the Forum, and new in the VBA.

Thank all of you for help!

[Killian]

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]

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]

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]

Hi Radka,
Can you zip your excel file and attach it, to test with "live" data
MD

[Radka]

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]

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]

Hi Killian, now all works perfectly. Thank you very much!