Calling 2 subroutines from the third subroutine

[Nasser]

Hi Everybody,

I am trying to run 2 programs. Two options are available:

Option 1: run both the programs using IF ...then ; else from only one sub.
Option 2: call 2 subroutines ( fuse sub and relay sub) from the main sub.

My application is as follows:

I want to run program no 1 for fuse and the program no 2 for relay. Both have different programs where separately they are working nicely.
I actually have 6 tables for input and 6 tables for output (output results to be displayed on a graph sheet). Total i can display 6 curves. They can be all fuse curves or all 6 relay curves or 1 fuse curve and 5 relays or 4 fuse curves and 4 relays and so on . I can also display only 1 curve either fuse or relay. The maximum i can display is 6 curves combined or same type as i like.
I put variable i varying from 1 to 6 ( 6 tables) and i have some data belonging to fuse and relay in each table. There is one special cell called "Crv_typ, if this cell contains "FUS" then fuse program will run for that particular table and from the output table the corresponding table will filled up by with a series of current and time value to be displayed on the graph. If instead of "FUS" a word "SI" (Standard Inverse Relay curve) is entered then relay program will be runing for that particular table and the corresponding table will filled with a series of current and time value to be displayed on the graph.

Since it is a bit long program so I am trying to use the subroutine method.

My statements are as follows:

Sub Do_Calcs5()

‘step 1 declaration of variables

Dim Crv_typ(6) As String
‘and so more variables ….
‘
‘step 2 location of each variable in excel sheet

Sheets("Setting Data 3").Select’ I am naming my sheet “setting data 3”

For i = 1 To 3 Step 1 'First 3 relays / fuses, top 3 tables

Crv_typ(i) = ActiveSheet.Cells(33, 3 + (i - 1) * 6).Value '
If Crv_typ(i) = "" Then
MsgBox ("Enter Curve Type")
Exit For
End If

‘Entering other variables
‘
‘
Next i

For i = 4 To 6 Step 1 'Last 3 relays / fuses, bottom 3 tables

'relays

Crv_typ(i) = ActiveSheet.Cells(43, 3 + (i - 4) * 6).Value
If Crv_typ(i) = "" Then
MsgBox ("Enter Curve Type")
Exit For
End If

‘Entering other variables as well
‘
‘
Next i

For i = 1 To 6 'Step 1'For each of the 6 fuses or relays

If Crv_typ(i) = "FUS" Then
Call fuseselect
Else
Call relayselect
End If

Next i ' I THINK NEXT i SHOULD NOT BE HERE

End Sub

Sub fuseselect()

‘ fuse orgram goes in here
‘

End sub

Sub relayselect()

‘program relay goes in here
‘
End sub

What I want is when i = 1, the program checks the first table no 1, if it find Crv_typ (1) = “FUS” then it should go to fuse program and run it when it finishes with i = 1 ( means table 1) it should go to table 2 and check for i = 2 from the main sub means it should go back to line IF Crv_typ. If it finds for example Crv_typ(i) = “SI” then it should run relay program. When it is done it should go and check table 3 means now i = 3, again either Crv_typ is "FUS or SI and so on until i = 6 (table 6). Every time a corresponding table is filled up by a series of current and time. A total of 6 tables for output values.

Am i calling the subroutines correctly?

When it finishes one of the programs how to go back to the main sub and keep checking for the next i?

Many Thanks
Nasser.

[HansV]

Welcome to Eileen's Lounge!

If you only want to run the subroutines if all 6 crv_typ cells are filled, you should use Exit Sub instead of Exit For in the first two loops.

When you call subroutine B from another subroutine A, code execution in A will continue when B has finished, so the way you call fuseselect and relayselect is OK. But I see one problem: fuseselect and relayselect don't know which table they should act on. One way to handle this is to pass the value of the variable i to them:

In the main routine:

Code: Select all

  If Crv_typ(i) = "FUS" Then
    Call fuseselect(i)
  Else
    Call relayselect(i)
  End If

and then change the definitions of the subroutines:

Code: Select all

Sub fuseselect(ByVal i As Integer)
  ' You can use the value of i in here
  ...
End Sub

Sub relayselect(ByVal i As Integer)
  ' You can use the value of i in here
  ...
End Sub

[Nasser]

Hi Hans,

Thanks for your quick reply. Still not working :(

Cheers,
Nasser.

[HansV]

I have received your e-mail with the workbooks, but I'm afraid I don't understand the code.

Could you tell us a bit about the way fuseselect and relayselect are supposed to work?

[Nasser]

Hi Hans,

The main objective is to display curves of maximum of 6 fuses.
I can display as much as I want upto 6 curves
What I will explain for table 1 is exactly the same as for other 5 tables.
The cells in white coulor are all inputs to be entered by the user.

A / Fuse:
Only 4 inputs are involved with the fuse which are.
1- Trf ratio (variable Trf_ratio(i)) : for the time being I will keep it equal to unity so no worries about it.
2- Curve Type [variable Crv_typ(i)]
3- Fuse Type [variable Fus_typ(i)]
4- Fuse Rating [variable Fus_rat(i)]
At the right hand side of the tables are kept 6 tables for 100Amperes fuse ( these are standard values).
If I want to display a fuse curve type “aR”, fuse rating 50A, what I will do is as follows:

In cell C33, I write FUS
In cell C34, I write aR
In cell E34, I write 50A
And I keep the transformer ratio equal to 1 so I enter in cell C32 number 1.

I just click on DoCals and the curve is displayed on the graph.

Now how the program is running:

First I have to locate all my variables mentioned above in the excel sheet.
The top 3 tables : i = 1 to 3
The bottom 3 tables: i = 4 to 6

After I have to take data of base rating 100A from the table located at the right hand side. The following program will only locate where are from different tables each fuse type ”aR, gR,gG,aM,MV and Custom. We need to know the row and column number so that we can use this information later in the calculation.
For i = 1 To 6 Step 1 'For each of the 6 fuses

If Fus_typ(i) = "" Then
MsgBox (" Fuse Type Not Selected")
Exit For
End If
If Fus_typ(i) = "aR" Then ' fuse_typ(1)= "aR" means if aR is selected we take row =29 and col=30 to use them to find pry_curf ( primary current of fuse), PSM or Mult (plug setting multiplier) and op_timf (operational time of fuse)
irow = 29
icol = 33
Else
If Fus_typ(i) = "gR" Then
irow = 29
icol = 36
Else
If Fus_typ(i) = "gG" Then
irow = 29
icol = 39
Else
If Fus_typ(i) = "aM" Then
irow = 50
icol = 33
Else
If Fus_typ(i) = "MV" Then
irow = 50
icol = 36
Else
If Fus_typ(i) = "Custom" Then
irow = 50
icol = 39
Else
MsgBox (" Wrong Fuse Type")
Exit For
End If
End If
End If
End If
End If
End If

After that we have to make some calculations and enter the result in the 6 other tables located at the bottom (15 rows)

For j = 1 To 15 Step 1 'Calculates the 15 currents and operational times
Pry_curf = ActiveSheet.Cells(irow + 1 + j, icol).Value ' values from table of 100A fuse
Op_timf = ActiveSheet.Cells(irow + 1 + j, icol + 1).Value ' time of different 100A fuses
Bas_rat = ActiveSheet.Cells(irow, icol + 1).Value 'Fuse curve base rating 100A
Pry_curf : primary current for fuse because we have also primary current for relay which is called Pry_cur. I added letter f for fuse.
Op_timf: operational time for fuse
Bas_rat: base rating current of 100A which I took from a manufacturer, they are standard values.
These 3 parameters are located at the right hand tables ( 6 tables).
The program will take all the data for each type of fuse (primary current, operational time and base current values). Standard values
At the beginning its written: For j = 1 To 15 Step 1 'Calculates the 15 currents and op times

I want now to calculate the values of primary current and operational to be displayed on the graph. I need to calculate the new current and new time for the selected fuse of 50 A, type aR
I have a total of 15 rows to fill them with current and time values.

If i <= 3 Then
ActiveSheet.Cells(69 + j, 4 + (i - 1) * 5).Value = Pry_curf / Bas_rat 'displays Mult values
'ActiveSheet.Cells(69 + j, 5 + (i - 1) * 5).Value = Pry_curf * Fus_rat(i) / Bas_rat / Trf_ratio(i) 'displays pry current
ActiveSheet.Cells(69 + j, 5 + (i - 1) * 5).Value = Pry_curf * Fus_rat(i) * Trf_ratio(i) / Bas_rat 'displays pry current
ActiveSheet.Cells(69 + j, 6 + (i - 1) * 5).Value = Op_timf ' displays time on 3 first tables

At the bottom we have 6 tables, each table with 15 rows and 3 main columns.
PSM or Mult column is:
ActiveSheet.Cells(69 + j, 4 + (i - 1) * 5).Value = Pry_curf / Bas_rat 'displays Mult values
And so on for primary current and operational time
Now if the i is more than 3 then it should go to other 3 tables

Else
ActiveSheet.Cells(88 + j, 4 + (i - 4) * 5).Value = Pry_curf / Bas_rat ' displays PSM or Mult values
'ActiveSheet.Cells(88 + j, 5 + (i - 4) * 5).Value = Pry_curf * Fus_rat(i) / Bas_rat / Trf_ratio(i) 'displays pry current
ActiveSheet.Cells(88 + j, 5 + (i - 4) * 5).Value = Pry_curf * Fus_rat(i) * Trf_ratio(i) / Bas_rat 'displays pry current
ActiveSheet.Cells(88 + j, 6 + (i - 4) * 5).Value = Op_timf ' displays time
After that the program moves to the second table “relay / fuse 2”

Next j
Next i
MsgBox (" End of Job")
'
End Sub

I don’t have to display all the 6 curves if not needed. If my network has only 2 fuses, I just enter data in the first 2 tables and display the 2 curves.
Shall I explain in the same way how the program works with relay or I change the method.
Attached are 3 files. The first one is only fuse running nicely, the second is only relay running nicely as well and the third one which I am having problem running it relayfuse.

Meanwhile i am preparing the relay program explanation.

Many thanks for your help,
Nasser.

[Nasser]

Oops! i for forgot to attach the files

Cheers,
Nasser.

[HansV]

It's not going to work this way. You'll have to process each table separarely within the For i = 1 To 3 Step 1 ... Next i and For i = 4 To 6 Step 1 ... Next i loops in the main Sub.

By the way, your code is very hard to read because the indentation is inconsistent.

Another remark: in a declaration such as

Dim Mults(15), CT_Pry(6), CT_Sec(6), Inst(6), Inst_time(6) As Single

only Inst_time is declared as Single; the others are all declared as Variant. If you want all of them to be declared as Single, you must use

Dim Mults(15) As Single, CT_Pry(6) As Single, CT_Sec(6) As Single, Inst(6) As Single, Inst_time(6) As Single

or preferably declare each variable on a separate line:

Dim Mults(15) As Single
Dim CT_Pry(6) As Single
Dim CT_Sec(6) As Single
Dim Inst(6) As Single
Dim Inst_time(6) As Single

[Nasser]

Hi Hans,

Please find attached a copy of the operation of the relay.

Many Thanks
Nasser.

[HansV]

I have moved the previous reply from a separate thread into this one, because it belongs to the same problem.

Analyzing the working of fuses and relays is beyond the scope of this discussion forum, I fear.