Cleaning all global variables 1

[ItIsHardToProgram]

Hello every one, I am currently doing a procedure that calculates a standard deviation by iteration.

The problem is, if I don't press "stop" on VBA, the variables don't clean up...



I call a sub at the end of my procedure:

Sub CleanVariables()

dSdev = 0
iCell = 0
iColumn = 0
iLine = 0
j = 0
i = 0
iOffset = 0
iMaxCell = 0
iSheet = 0
iWorkbooks = 0
iWorksheets = 0
sColumnName = ""
dMoyenne = 0
iTotalOffset = 0
bidon = 0
sColumn = ""

End Sub

dSdev and dMoyenne are used to calculate the standard deviation through this sub:

Sub DynamicSdev()
'Formule pour calculer l'écart type par itération. Julien Roy 2009-01-14

'S'assure de la visibilité de tous les items du tableau croisée dynamique. Elle n'est pas assez rapide pour l'utiliser (autant mieu le faire manuel)
'For Each pi In Worksheets("Total").PivotTables("TableauTotal").PivotFields("Surname             ").PivotItems
'    pi.Visible = True
'Next


Worksheets("Total").PivotTables("TableauTotal").PivotCache.Refresh
    

iTotalOffset = 1

For iColumn = 13 To 14
iOffset = 0
For iCell = 16 To 6000
    If Worksheets("Total").Cells(iCell, iColumn).Value <> 0 Then
           iOffset = iOffset + 1
           dSdev = dSdev + (Worksheets("Total").Cells(iCell, iColumn).Value - Worksheets("Total").Cells(5, iColumn + 6)) ^ 2
           dMoyenne = dMoyenne + Worksheets("Total").Cells(iCell, iColumn).Value
    End If
    

Next iCell
    'Écriture des données pour la contingenc
    

    Worksheets("Total").Cells(4, iColumn).Value = iOffset
    
    dMoyenne = dMoyenne / iOffset
    dSdev = Sqr(1 / iOffset * dSdev)
    
    Worksheets("Total").Cells(5, iColumn).Value = Worksheets("Total").Cells(5, iColumn + 6).Value
    Worksheets("Total").Cells(6, iColumn).Value = dSdev
    'écriture de la contingence.
    Worksheets("Total").Cells(8, iColumn).Value = dMoyenne + 2 * dSdev

    iTotalOffset = iTotalOffset + iOffset
Next iColumn



Exit Sub
ErrorHandler:

MsgBox (Error(Err) & Err)


End Sub

And this sub is run through a bigger sub, that calls this sub, the procedure "call CleanVariables" is called in another sub, that is fired in my global procedure.

All the variables are global.




"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

Hi,

Call CleanVariables whenever you begin a StdDev process.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

Ok, I guess I could clean all the variables that need to be cleaned before starting my stdev, Thanks.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.