calling a macro in a macro: array problem

choub

New Member
Joined
Jul 4, 2008
Messages
6
hello,

In sub "A" I am calling sub "B"

In sub A, I initialize a multidimensionnal array afrequencies(1, k) and afrequencies(2,k) before a loop.

then, in the loop i am calling the sub B where I use afrequencies(1, k) and afrequencies(2,k) but it returns an error (varible is not defined)

how can I fix this please?
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

choub

New Member
Joined
Jul 4, 2008
Messages
6
Hi Andrew,

thanks for your reply
actually, it's a "subscript out of range" error that i get

I have tried declaring my array at the top of the subs but it didnt work any better.
I usually declare may workbook and worksheets at the top of the module.
 
Last edited:

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
I don't think there's necessarily a need to use Public variables here - if Sub B is simply doing stuff on the array (ie processing it) before passing it back to Sub A, then simply passing a ByRef parameter of the array to Sub B should be sufficient.

It does depend on what you are doing though - and because we haven't seen your full code we can't really say definitively.
 

choub

New Member
Joined
Jul 4, 2008
Messages
6
hi Richard,

here is the code.
sorry for the length...

Code:
'cree les constantes
Public wbBook As Workbook
Public wsRC_Simple As Worksheet
Public wsAccu_Simple As Worksheet
Public wsOutput As Worksheet
 
'active les constantes
Sub Constants()
Set wbBook = Workbooks("Backtesting_Hoadley_200806_Master.xls")
Set wsRC_Simple = Worksheets("Input_RC_Simple")
Set wsAccu_Simple = Worksheets("Input_Accumulator")
Set wsOutput = Worksheets("Output")
End Sub
'Mouvement Brownien du sous jacent (1 seule action)
'payoff
Sub RC_simple()
Call Constants
Call Cleanup
 
Dim Start_Date As Double, End_Date As Double, Total_Days As Double, Total_Fixings As Double
Dim Strike As Double, Barrier_DI As Double, Barrier_UO As Double, Guarantee As Integer
Dim Vol As Double, Exp_Return As Double
Dim Start_Price As Double, Iterations As Double
Dim Prices() As Double
Dim objSim As Object
Dim i As Integer, j As Integer, k As Integer, Fixings As Integer
Dim Counter As Double, PctDone As Single 'used for the progress bar
Dim cCount As Double
Dim aFrequencies()
 
'qualibrage de la simulation (longueur)
Start_Date = wsRC_Simple.Cells(19, 1)
End_Date = wsRC_Simple.Cells(19, 2)
Total_Fixings = wsRC_Simple.Cells(19, 3)
'parametrage produit (on n'utilise pas le SET ici car on initialise ET on calcule)
'on multiplie par 100 car la fonction ne prends pas les %
Strike = wsRC_Simple.Cells(16, 2) * 100 'idem
Barrier_DI = wsRC_Simple.Cells(16, 3) * 100 'idem
Barrier_UO = wsRC_Simple.Cells(16, 4) * 100 'idem
Guarantee = wsRC_Simple.Cells(19, 4)
'parametres pour le tirage aleatoire
Vol = wsRC_Simple.Cells(12, 2)
Exp_Return = wsRC_Simple.Cells(12, 3)
Total_Days = networkdays(Start_Date, End_Date)
Fixings = Total_Days / Total_Fixings
Iterations = wsRC_Simple.Cells(12, 5)
'n'affiche pas les calculs a l'ecran
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
 
Set objSim = CreateSimulationObject
ReDim Prices(Total_Days + 1) 'on rajoute + 1 au total_days car la boucle du dessous commence a 2
Start_Price = wsRC_Simple.Cells(16, 1) * 100
Prices(1) = Start_Price
Counter = 1 'utilise pour afficher la barre de progression
 
'le tableau de frequence doit etre initialise AVANT de lancer la simulation
ReDim aFrequencies(1 To 2, Total_Fixings) 'tableau multi dimensions
For k = 1 To Total_Fixings
aFrequencies(1, k) = 0 'tableau regroupant les frequences de paiement de coupon
aFrequencies(2, k) = 0 'tableau regroupant les frequences de call du produit
Next k
'la suite du tableau est geree dans GGGGGGGG
 
'demarrage de la simulation, c'est parti pour la grande boucle!
For i = 1 To Iterations
    For j = 1 To Total_Days
 
    'iterations dans la colonne 1
    'tirage au sort au sein d'une loin lognormale
    'pointer pour les dates de fixing
    Prices(j + 1) = objSim.NextPrice(Prices(j), Vol, Exp_Return, 1, 0)
    wsOutput.Cells(j, 1) = Prices(j + 1)
 
    Next j
 
    For k = Total_Days To 1 Step -Fixings
        If Guarantee <> 0 And k - 1 <= Guarantee * Fixings Or k = 1 Then
        wsOutput.Cells(k, 2) = ""
        Else
        Set Spot_Current = wsOutput.Cells(k, 1)
        wsOutput.Cells(k, 2) = "=RC_Evaluation(" & Spot_Current.Address & "," & Strike & "," & Barrier_DI & "," & Barrier_UO & ")" 'on utilise Adress ici car a chaque iteration on souhaite recuperer le resultat de la fonction
        End If
    Next k
 
Call Strategy_Recap 'copie les fixings (spot et resultat) en colonne E et F
Call Proba_Coupon 'resultat du monte carlo
PctDone = Iterations * 5 / 100 'retourne la progression du processus en %
If i = Counter * PctDone Then
Application.StatusBar = "Simulations completed: " & Format(Counter * 0.05, "##0.00%")
Counter = Counter + 1
End If
 
Next i
Call Display_Data
 
Columns("D:M").EntireColumn.AutoFit
Application.StatusBar = ""
Application.ScreenUpdating = True
'Call cRC_Simple
End Sub
 
j = 2 'on va copier les cellules de B et C dans E et F sous une legende
's 'il y a une valeur dans B pour une valeur de A on retourne l'adresse de B et C dans E et F
For i = 1 To wsOutput.Cells(Rows.Count, "A").End(xlUp).Row
If Not IsEmpty(wsOutput.Cells(i, 2)) Then
    'ne fonctionne pas correctement (retourne 2 valeurs dans 1 seule cellule...)
    'wsOutput.Cells(j, 3).Offset(, 1).Resize(, 2) = "=" & wsOutput.Cells(i, 3).Offset(, -1).Resize(, 1).Address & ""
    wsOutput.Cells(j, 4) = "=" & wsOutput.Cells(i, 1).Address & ""
    wsOutput.Cells(j, 5) = "=" & wsOutput.Cells(i, 2).Address & ""
 
    j = j + 1
End If
wsOutput.Cells(1, 4) = "Spot on Fixing"
wsOutput.Cells(1, 5) = "Spot vs Strike"
 
Next
 
End Sub
 
Sub Proba_Coupon()
Dim l As Variant, k As Integer
Dim sProducts As Double
'Dim aFrequencies()
sProducts = wsRC_Simple.Cells(2, 3)
Total_Fixings = wsRC_Simple.Cells(19, 3)
 
Select Case sProducts 'output different en fonction du type de produit teste
Case Is = 1 'cas d'un RC convertible simple: 1 fixing a maturite
    If wsOutput.Cells(2, 5) = 1 Then
    wsOutput.Cells(2, 9) = wsOutput.Cells(2, 9) + 1
    Else
    wsOutput.Cells(3, 9) = wsOutput.Cells(3, 9) + 1
 
    End If
 
 
Case Is = 2
Last_Cell = wsOutput.Cells(Rows.Count, "E").End(xlUp).Address
    k = 1
    For Each l In wsOutput.Range("E2:E" & _
        wsOutput.Range("E" & Rows.Count).End(xlUp).Row)
 
        If l = 0 And wsOutput.Cells(k + 1, 5).Address = Last_Cell Then 'on est converti en actions
        wsOutput.Cells(2, 9) = wsOutput.Cells(2, 9) + 1
        Exit For
 
        ElseIf l = 1 Then 'on touche le coupon
        wsOutput.Cells(3, 9) = wsOutput.Cells(3, 9) + 1
        aFrequencies(1, k) = aFrequencies(1, k) + 1
 
        ElseIf l = 2 Then 'la structure est callee
        wsOutput.Cells(4, 9) = wsOutput.Cells(4, 9) + 1
        aFrequencies(2, k) = aFrequencies(2, k) + 1
        Exit For
 
        End If
    k = k + 1
    Next l
 
End Select
End Sub
Sub Display_Data()
Dim sProducts As Double, SumRange As Double
Dim Iterations As Integer
sProducts = wsRC_Simple.Cells(2, 3)
Iterations = wsRC_Simple.Cells(12, 5)
Select Case sProducts
'si le produit un Reverse Convertible
Case Is = 1
wsOutput.Cells(2, 8) = "Coupon Paid"
wsOutput.Cells(3, 8) = "No Coupon Paid"
wsOutput.Cells(1, 9) = "Occurences"
wsOutput.Cells(1, 10) = "Probability"
wsOutput.Cells(2, 10) = wsOutput.Cells(2, 9) / Iterations
wsOutput.Cells(3, 10) = wsOutput.Cells(3, 9) / Iterations
 
wsOutput.Cells(2, 10).NumberFormat = "#0.00%"
wsOutput.Cells(3, 10).NumberFormat = "#0.00%"
 
'si le produit est un Range Accrual
Case Is = 2
SumRange = Application.WorksheetFunction.Sum(Range("I2:I4"))
wsOutput.Cells(2, 8) = "Redeemed in shares"
wsOutput.Cells(3, 8) = "Coupon Paid"
wsOutput.Cells(4, 8) = "Called"
wsOutput.Cells(1, 9) = "Occurences"
wsOutput.Cells(1, 10) = "Probability"
wsOutput.Cells(2, 10) = wsOutput.Cells(2, 9) / SumRange
wsOutput.Cells(3, 10) = wsOutput.Cells(3, 9) / SumRange
wsOutput.Cells(4, 10) = wsOutput.Cells(4, 9) / SumRange
wsOutput.Cells(2, 10).NumberFormat = "#0.00%"
wsOutput.Cells(3, 10).NumberFormat = "#0.00%"
wsOutput.Cells(4, 10).NumberFormat = "#0.00%"
 
End Select
For k = 1 To Total_Fixings
    wsOutput.Cells(k + 1, 18) = k
    wsOutput.Cells(k, 19) = aFrequencies(1, k)
    wsOutput.Cells(k, 20) = aFrequencies(2, k)
Next
 
End Sub
 
Sub Cleanup()
Worksheets("Output").Activate
Cells.Select
Selection.Delete Shift:=xlUp
End Sub

the idea behind this is to get a frequency distribution of variables 1 and 2.
 
Last edited:

choub

New Member
Joined
Jul 4, 2008
Messages
6
ok I think I have figured it out:

I called my subs this way:

Proba_coupon aFrequencies

and I called the array in the sub this way: Sub Proba_Coupon(aFrequencies)

seesm to wor albeit the results are a bit surprising

thanks guys :)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,720
Members
414,401
Latest member
grenona2020

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