Is there a way to clear all variables after a loop?

**gshock** · 08-12-2008, 10:17 AM

I'm using a block of code that was written by someone else to perform some calculations.
This is a list of the variables created by the original authors of the program.

Dim RADIUS(9) As Double
Dim INTERFACE(7) As Double
Dim WDTH(8) As Double
Dim AREA(9) As Double
Dim Index As Integer
Dim T1(8) As Double, R1(8) As Double, U1(8) As Double, T2(8) As Double, R2(8) As Double, U2(8) As Double
Dim A1(8) As Double, A2(8) As Double, A3(8) As Double, B1(8) As Double, B2(8) As Double, B3(8) As Double
Dim R(7, 1) As Double, F(7, 1) As Double
Dim E1(7, 7) As Double
Dim E2(7, 7) As Double
Dim A4(8) As Double, T3(8) As Double
Dim E As Double 'Youngs Modulus (MPa)
Dim RHO As Double 'Density (Lbs/in^3)
Dim POISSONR As Double 'Poisson's Ratio
Dim RPM As Double 'Rotations Per Minute
Dim PERIPHP As Double 'Peripheral Pressure (KSI)
Dim BOREP As Double 'Bore Pressure (KSI)
Dim W As Double 'Rotational Speed (Radians)
Dim WOSPED As Double 'Over Speed (Radians)
Dim N As Double 'Centrifugal Stress Coefficient
Dim A5 As Double
Dim T4 As Double
Dim OS As Double

Because I ported this code into my spreadsheet, I suspect that there's some way that these get reset each time the code is run. Somehow, that 'reset' isn't working.
Is there a way to clear all variables after each iteration?

Thanks,
-gshock

**royUK** · 08-12-2008, 10:30 AM

You need to post the code, remember to use Code Tags

**royUK** · 08-12-2008, 10:36 AM

Those variables must have a value set within the code. This will overwrite any previous values. The variables will be cleared from memory when the workbook is closed.

**gshock** · 08-12-2008, 10:38 AM

Originally Posted by royUK

Those variables must have a value set within the code. This will overwrite any previous values. The variables will be cleared from memory when the workbook is closed.

Ah HA! That's exactly what the problem is! The workbook iterates through a number of stages. So the variables don't get cleared before the next stage is calculated.

**royUK** · 08-12-2008, 11:17 AM

To start tidying up. all constants & variables need to be at the top of the macro, before the actual code.

This line makes no sense

 For i = 2 To 2

It needn't be a loop because 2 to 2 would be 1.

**gshock** · 08-12-2008, 11:34 AM

Originally Posted by royUK

To start tidying up. all constants & variables need to be at the top of the macro, before the actual code.

This line makes no sense

 For i = 2 To 2

It needn't be a loop because 2 to 2 would be 1.

I'm just doing that to get it to run through once to see if I can find where it's causing the error. I have also tried running it through 4 loops using

 For i = 2 To 5  'Cells(9, Columns.Count).End(xlToLeft).Column

If you notice the comment on that line, the code should loop through all the columns that have data based on the values in row 9. rylo helped me with the loop. Thanks for taking the time to look at this.

Regards,

**gshock** · 08-12-2008, 10:37 AM

Originally Posted by royUK

You need to post the code, remember to use Code Tags

No worries, but don't say I didn't warn you. It's ugly. This was written by some engineers who don't necessarily understand the concept of writing efficient code.

Sub Rotor_Calcs()


Sheet94.Activate

  Dim CalcSH As Worksheet
  Set CalcSH = Sheet93
  For i = 2 To 2 'Cells(9, Columns.Count).End(xlToLeft).Column
    Sheet94.Activate
    CalcSH.Range("C14:C14").Value = Range(Cells(6, i), Cells(6, i)).Value
    CalcSH.Range("G8:G16").Value = Range(Cells(9, i), Cells(17, i)).Value
    CalcSH.Range("H9:H16").Value = Range(Cells(18, i), Cells(25, i)).Value
    

'This procedure calculates Stresses and Fits

    Sheet93.Activate

'CONSTANTS

Const Pi As Double = 3.1416
Const G As Double = 384 'acceleration due to gravity in/s^2

'Variable Declarations

Dim RADIUS(9) As Double
Dim INTERFACE(7) As Double
Dim WDTH(8) As Double
Dim AREA(9) As Double
Dim Index As Integer
Dim T1(8) As Double, R1(8) As Double, U1(8) As Double, T2(8) As Double, R2(8) As Double, U2(8) As Double
Dim A1(8) As Double, A2(8) As Double, A3(8) As Double, B1(8) As Double, B2(8) As Double, B3(8) As Double
Dim R(7, 1) As Double, F(7, 1) As Double
Dim E1(7, 7) As Double
Dim E2(7, 7) As Double
Dim A4(8) As Double, T3(8) As Double
Dim E As Double 'Youngs Modulus (MPa)
Dim RHO As Double 'Density (Lbs/in^3)
Dim POISSONR As Double 'Poisson's Ratio
Dim RPM As Double 'Rotations Per Minute
Dim PERIPHP As Double 'Peripheral Pressure (KSI)
Dim BOREP As Double 'Bore Pressure (KSI)
Dim W As Double 'Rotational Speed (Radians)
Dim WOSPED As Double 'Over Speed (Radians)
Dim N As Double 'Centrifugal Stress Coefficient
Dim A5 As Double
Dim T4 As Double
Dim OS As Double

'DATA INPUTS

E = 1000000 * Worksheets("Input&Output").Range("C8") 'Youngs Modulus
RHO = Worksheets("Input&Output").Range("C9") 'Density
POISSONR = Worksheets("Input&Output").Range("C10") 'Poisson's Ratio

RPM = Worksheets("Input&Output").Range("C13") 'speed
PERIPHP = Worksheets("Input&Output").Range("C14") * 1000 'peripheral pressure
BOREP = Worksheets("Input&Output").Range("C15") * 1000 'bore pressure

For counter = 8 To 14
        INTERFACE(counter - 7) = Worksheets("Input&Output").Cells(counter, 5).Value
Next counter

For counter = 8 To 16
        RADIUS(counter - 8) = Worksheets("Input&Output").Cells(counter, 7).Value
Next counter

For counter = 9 To 16
        WDTH(counter - 8) = Worksheets("Input&Output").Cells(counter, 8).Value
Next counter

OS = Worksheets("Input&Output").Cells(15, 5).Value / 100

'Calculate Constants

W = (2 * Pi * RPM) / 60
WOSPED = (1 + OS) * W

N = ((3 + POISSONR) / 4) * ((RHO * W ^ 2) / G)

'Setup Simulataneous Equations

For Index = 1 To 8
    A1(Index) = (-1) * ((RADIUS(Index) ^ 2 + RADIUS(Index - 1) ^ 2) / (RADIUS(Index) ^ 2 - RADIUS(Index - 1) ^ 2))
    A2(Index) = (-1) * ((-2 * RADIUS(Index) ^ 2) / (RADIUS(Index) ^ 2 - RADIUS(Index - 1) ^ 2))
    A3(Index) = N * (RADIUS(Index) ^ 2 + ((1 - POISSONR) / (3 + POISSONR)) * RADIUS(Index - 1) ^ 2)
    B1(Index) = (-1) * ((2 * RADIUS(Index - 1) ^ 2) / (RADIUS(Index) ^ 2 - RADIUS(Index - 1) ^ 2))
    B2(Index) = (-1) * (((-1) * RADIUS(Index) ^ 2 - RADIUS(Index - 1) ^ 2) / (RADIUS(Index) ^ 2 - RADIUS(Index - 1) ^ 2))
    B3(Index) = N * (((1 - POISSONR) / (3 + POISSONR)) * RADIUS(Index) ^ 2 + RADIUS(Index - 1) ^ 2)
Next Index

For counter = 0 To 7
     For Index = 0 To 7
        E1(counter, Index) = 0#
    Next Index
Next counter

For Index = 1 To 7
    E1(Index, Index) = (A1(Index + 1) - POISSONR) * (WDTH(Index) / WDTH(Index + 1)) - (B2(Index) - POISSONR)
    F(Index, 1) = INTERFACE(Index) * E - A3(Index + 1) + B3(Index)
Next Index

F(1, 1) = F(1, 1) - B1(1) * BOREP
F(7, 1) = F(7, 1) - A2(8) * PERIPHP

For Index = 2 To 7
    E1(Index, Index - 1) = (-1) * B1(Index) * (WDTH(Index - 1) / WDTH(Index))
Next Index

For Index = 1 To 6
    E1(Index, Index + 1) = A2(Index + 1)
Next Index

E1(0, 0) = 1

'Calculate Radial Pressures

'Find inverse of matrix
Dim Determ As Double
Dim adjE1(7, 7) As Double
Dim cofE1(7, 7) As Double
Dim minors(6, 6) As Double
Dim Detmin As Double
Dim minrow As Integer
Dim mincol As Integer
Dim pointrow As Integer
Dim pointcol As Integer

'Find Determininant of E1
Determ = Application.WorksheetFunction.MDeterm(E1)

'Find Adjoint of E1
For minrow = 0 To 7
    For mincol = 0 To 7
        pointrow = 0
        For Index = 0 To 6
            pointcol = 0
            For counter = 0 To 6
                If (pointrow = minrow) Then pointrow = pointrow + 1
                If (pointcol = mincol) Then pointcol = pointcol + 1
                minors(Index, counter) = E1(pointrow, pointcol)
                pointcol = pointcol + 1
            Next counter
            pointrow = pointrow + 1
        Next Index
        cofE1(minrow, mincol) = (-1) ^ (minrow + mincol) * Application.WorksheetFunction.MDeterm(minors)
    Next mincol
Next minrow

'Take the transpose of the cofactors matrix and divide by |E1|
For Index = 0 To 7
    For counter = 0 To 7
        E2(Index, counter) = cofE1(counter, Index) / Determ
    Next counter
Next Index

'For Index = 0 To 7
'    For counter = 0 To 7
'        E2(Index, counter) = Application.WorksheetFunction.MInverse(E1)
'    Next counter
'Next Index

'E2 = Application.WorksheetFunction.MInverse(E1)

For Index = 0 To 7
    For counter = 0 To 7
        R(Index, 1) = R(Index, 1) + E2(Index, counter) * F(counter, 1)
    Next counter
Next Index
'R = Application.WorksheetFunction.MMult(E2, F)

For Index = 1 To 7
    R2(Index) = R(Index, 1)
Next Index

R2(8) = PERIPHP
R1(1) = (-1) * BOREP

For Index = 2 To 8
    R1(Index) = R2(Index - 1) * (WDTH(Index - 1) / WDTH(Index))
Next Index

'Calculate Tangential Stresses

For Index = 1 To 8
    T1(Index) = A1(Index) * R1(Index) + A2(Index) * R2(Index) + A3(Index)
    T2(Index) = B1(Index) * R1(Index) + B2(Index) * R2(Index) + B3(Index)
Next Index

' Calculate Radial Displacements

For Index = 1 To 8
    U1(Index) = (RADIUS(Index - 1) / E) * (T1(Index) - POISSONR * R1(Index))
    U2(Index) = (RADIUS(Index) / E) * (T2(Index) - POISSONR * R2(Index))
Next Index

'Calculate Disc Area and Average Tangential Stress

A5 = 0#
T4 = 0#

For Index = 1 To 8
    A4(Index) = WDTH(Index) * (RADIUS(Index) - RADIUS(Index - 1))
    T3(Index) = R2(Index) * RADIUS(Index) - R1(Index) * RADIUS(Index - 1)
    T3(Index) = T3(Index) + ((RHO * W ^ 2) / (3 * G)) * (RADIUS(Index) ^ 3 - RADIUS(Index - 1) ^ 3)
    T3(Index) = T3(Index) / (RADIUS(Index) - RADIUS(Index - 1))
    A5 = A5 + A4(Index)
    T4 = T4 + A4(Index) * T3(Index)
Next Index

T4 = T4 / A5

'Output Results

counter = 28
For Index = 1 To 8
        Worksheets("Input&Output").Cells(counter, 2) = RADIUS(Index - 1)
        counter = counter + 1
        Worksheets("Input&Output").Cells(counter, 2) = RADIUS(Index)
        counter = counter + 1
Next Index

counter = 28
For Index = 1 To 8
        Worksheets("Input&Output").Cells(counter, 3) = WDTH(Index)
        counter = counter + 1
        Worksheets("Input&Output").Cells(counter, 3) = WDTH(Index)
        counter = counter + 1
Next Index

counter = 28
For Index = 1 To 8
        Worksheets("Input&Output").Cells(counter, 4) = R1(Index) / 1000
        counter = counter + 1
        Worksheets("Input&Output").Cells(counter, 4) = R2(Index) / 1000
        counter = counter + 1
Next Index

counter = 28
For Index = 1 To 8
        Worksheets("Input&Output").Cells(counter, 5) = T1(Index) / 1000
        counter = counter + 1
        Worksheets("Input&Output").Cells(counter, 5) = T2(Index) / 1000
        counter = counter + 1
Next Index

counter = 28
For Index = 1 To 8
        Worksheets("Input&Output").Cells(counter, 6) = U1(Index)
        counter = counter + 1
        Worksheets("Input&Output").Cells(counter, 6) = U2(Index)
        counter = counter + 1
Next Index

counter = 47
For Index = 1 To 8
    Worksheets("Input&Output").Cells(counter, 4) = A4(Index)
    Worksheets("Input&Output").Cells(counter, 5) = T3(Index) / 1000
    counter = counter + 1
Next Index

Worksheets("Input&Output").Cells(55, 5) = T4 / 1000

'FINISH


        
    Sheet94.Activate
    Cells(30, i).Resize(16, 1).Value = CalcSH.Range("D28:D43").Value
    Cells(49, i).Resize(16, 1).Value = CalcSH.Range("E28:E43").Value
    Cells(68, i).Resize(9, 1).Value = CalcSH.Range("E47:E55").Value
    

      
  Next i
  

End Sub

If you can lend a hand, I would greatly appreciate it.

Best,

-gshock

Is there a way to clear all variables after a loop?

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Is there a way to clear all variables after a loop?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions