calling a macro in a macro: array problem

[choub]

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?

 

[Andrew Poulsom]

If you want your variables to be available to both procedures, declare them at the top of your module before any procedures, and remove the declarations inside the procedures.

For a detailed discussion see:

http://www.cpearson.com/Excel/Scope.aspx

 

[choub]

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.

 

[Richard Schollar]

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]

hi Richard,

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

'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.

 

[choub]

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