worksheet is not picked up in Vba

[choubix]

hello,

I have declared constants like this:

'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.xls")
Set wsRC_Simple = Worksheets("Input_RC_Simple")
Set wsAccu_Simple = Worksheets("Input_Accumulator")
Set wsOutput = Worksheets("Output")
End Sub

then in the sub RC_Simple() I call the Constants. the sub works well.
now need to recycle most of my RC_Simple() code. the new sub is called "Accu_Simple".

Instead of calling wsRC_Simple it calls wsAccu_simple which points to Input_Accumulator.

the thing is: it doesnt work in that sub when it works perfectly well in RC_Simple... after a while I get an "Overflow" error. I tried replacing all the wsAccu_Simple by wsRC_Simple and it works again... any idea what's goign wrong in my code please?

thx!

[Bob Phillips]

Got an example workbook?

[choubix]

it's using external references and data import modules so it's going to be a bit cumbersome.

I my VBE I have this:

'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.xls")
Set wsRC_Simple = Worksheets("Input_RC_Simple")
Set wsAccu_Simple = Worksheets("Input_Accumulator")
Set wsOutput = Worksheets("Output")
End Sub
 
Sub Accu()
Call Constants
Call Cleanup
Dim Start_Date As Variant, End_Date As Variant, Total_Days As Variant, Total_Fixings As Variant
Dim Strike As Variant, Barrier_DI As Variant, Barrier_UO As Variant, Leverage As Variant
Dim Vol As Variant, Exp_Return As Variant
Dim i As Integer, j As Variant, Fixings As Integer, Guarantee As Integer
Dim cCount As Variant
'qualibrage de la simulation (longueur)
Set Start_Date = wsAccu_Simple.Cells(19, 1)
Set End_Date = wsAccu_Simple.Cells(19, 2)
Set Total_Fixings = wsAccu_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 = wsAccu_Simple.Cells(16, 2) * 100 'idem
Barrier_DI = wsAccu_Simple.Cells(16, 3) * 100 'idem
Barrier_UO = wsAccu_Simple.Cells(16, 4) * 100 'idem
Leverage = wsAccu_Simple.Cells(16, 5) * 100
'parametres pour le tirage aleatoire
Set Vol = wsAccu_Simple.Cells(12, 2)
Set Exp_Return = wsAccu_Simple.Cells(12, 3)
Total_Days = networkdays(Start_Date, End_Date)
Fixings = Total_Days / Total_Fixings
Guarantee = wsAccu_Simple.Cells(19, 4)
'debut de la simulation avec spot = 100
wsOutput.Cells(1, 2) = 100 * wsAccu_Simple.Cells(16, 1)
'n'affiche pas les calculs a l'ecran
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
    For i = 1 To Total_Days
'iterations dans la colonne 1
    'tirage au sort au sein d'une loin lognormale
    'pointer pour les dates de fixing
wsOutput.Cells(i + 1, 1) = i
    wsOutput.Cells(i + 1, 2) = "=" & wsOutput.Cells(i, 2).Address & "*lognorm2sim(" & Exp_Return & "," & Vol & ",""0.004"")" 'rajouter tirage logreturns + parameters
For j = 1 To Fixings
            If i = (j + Guarantee) * Fixings Then 'takes into account if there is a guarantee
            Set Spot_Current = wsOutput.Cells(i + 1, 2) 'get the current spot level
            wsOutput.Cells(i + 1, 3) = "=Accu_Evaluation(" & Spot_Current.Address & "," & Strike & "," & Barrier_DI & "," & Barrier_UO & "," & Leverage & ")" 'on utilise Adress ici car a chaque iteration on souhaite recuperer le resultat de la fonction
            End If
        Next
Next
'on verifie que l'on ait bien tous les fixings
'le cas echeant on rajoute le dernier ici
rStart = wsOutput.Cells(Rows.Count, "C").End(xlUp).Address 'derniere evaluation Spot vs Strike de la colonne C
rEnd = wsOutput.Cells(Rows.Count, "C").End(xlUp).Offset(Fixings, 0).Address 'derniere cellule de B, offset de 1 (colonne C
cCount = Evaluate("SUMPRODUCT((Len(" & rStart & ":" & rEnd & ") = 1) * 1)")
If cCount = 1 Then
wsOutput.Cells(Rows.Count, "B").End(xlUp).Offset(0, 1) = "=RC_Evaluation(" & wsOutput.Cells(Rows.Count, "B").End(xlUp).Address & "," & Strike & "," & Barrier_DI & "," & Barrier_UO & ")"
End If
'copie le spot et Spot vs Strike en colonne E et F
Call Strategy_Recap
'spot < au strike a une date de fixing
wsOutput.Cells(1, 8) = "Output 1"
wsOutput.Cells(1, 9) = "=IF(COUNTIF(C:C,0),0,1)+ outputv()"
'defines the name of the output
wsOutput.Names.Add Name:="RC_No_Memory", RefersToR1C1:="=Output!R1C9"
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
'dont forget to empty the variables that were SET
End Sub

I am showing you the sub "Accu" the other one is "RC_Simple (their are twin sisters now appart from a variable "Leverage" in Accu that doesnt exist in RC_Simple and the formula that will change in Accu)

[choubix]

by the way, if I change:

Set wsAccu_Simple = Worksheets("Input_Accumulator")

for

Set wsAccu_Simple = Worksheets("Input_RC_Simple")

the worksheet is picked up in the sub. then if I change the name for anything else it doesnt work. (of course, when I try this I change the names on both the workbook AND the VBE)

[choubix]

i really don't get it at all...
why would it work with 1 worksheet (Input_RC_Simple) and not with the other one (Input_Accumulator)....