Trouble with Global Variables

[User Name]

I run a sub during my opening sequence that populates a series of arrays. I've declared the arrays outside of the sub and made them public.

    Public Day_Series_28(1 To 28, 1 To 1000) As Double
    Public Day_Series_29(1 To 29, 1 To 1000) As Double
    Public Day_Series_30(1 To 30, 1 To 1000) As Double
    Public Day_Series_31(1 To 31, 1 To 1000) As Double
    
Sub Day_Sequence()

I've verified that the arrays are populating with data but when I try to access this data from other worksheets I get nothing but zeroes. What am I missing here?

 

[RoryA]

An unhandled code error, altering code, adding activex controls will for example all cause a project reset that loses your public variables.

 

[BrandonWLH]

Try throwing an Option Explicit at the beginning. Can't hurt, and helps catch some minor var bugs with naming and declaring.

Is there anywhere else or in any other modules that those globals are used or referenced?

 

[User Name]

The globals are only used by one sub. If I stop this sub with a break but then close out of the sub without altering anything will this erase the globals?

 

[shg]

If you drag the execution point indicator down to an Exit Sub line it will not reset.

There is no difference between Global and Public other than that Global does not work in object modules.

Public doesn't either, n'est ce pas?

 

[User Name]

"Public doesn't either, n'est ce pas?"

I'm saying the variable declaration isn't the problem. There's something happening downstream inside of my simulation sub that is erasing the array. I'm going through and declaring all the undeclared variables right now in the hope that this may help.

"If you drag the execution point indicator down to an Exit Sub line it will not reset."

I don't know what this means? What is an execution point indicator?

 

[User Name]

OK... I added Option Explicit and updated the variable declarations where needed.

Here's where my trouble shooting is at this point. Steps

Open up program - arrays populate
Run a simulation
Go into VBA and add a break
Run a simulation that hits the break
Here I can verify that the arrays are full
Close out of VBA
Run another simulation that hits the break
The arrays are empty

When I go into vba to add the break and then exit I still have the global array data available. There's something about going into VBA via the break and then closing out that is erasing the globals. This isn't necessarily a problem for me because you don't expect to be going into vba debug mode during normal use of the program and I can always add some code that repopulates the arrays if need be. My concern was that you'd have to repopulate the arrays for every simulation which is specifically what I wanted to avoid.

 

[shg]

What is an execution point indicator?

The yellow highlight that indicates the line of code about to execute.

You can drag the yellow arrow in the margin to the next line of code you want to execute. Or you can select another line (here, an Exit Sub or End Sub), right click, and select Set Next Statement, and then press F8 to execute the line.

I suggest you spend some time reading http://www.cpearson.com/excel/debug.htm

Close out of VBA

Close out of VBA how? Pressing End erases everything.

 

[BrandonWLH]

Ran your code more than once and it works for me on Excel 2007 and the globals would keep their values but, there is a problem I ran into with a division by zero.

        'Lag 1 Calculation Part 1, Duffie, Klein, Beckman
        Day = 1
        Sum_Part1 = 0
        Do While Day < (Days_in_Month - 1)
        Lag1_Part1 = (Random_KT_Array(Day) - KT_bar) * (Random_KT_Array(Day + 1) - KT_bar)
        Sum_Part1 = Lag1_Part1 + Sum_Part1
        Day = Day + 1
        Loop
        
        'Lag 1 Calculation Part 2,Duffie, Klein, Beckman
        Day = 1
        Sum_Part2 = 0
        Do While Day < Days_in_Month
        Lag1_Part2 = (Random_KT_Array(Day) - KT_bar) ^ 2
        Sum_Part2 = Lag1_Part2 + Sum_Part2
        Day = Day + 1
        Loop
        
        'Knight, Klein & Duffie, Equation 15
        Lag1 = Sum_Part1 / Sum_Part2  <-------- HERE

I would get a division by 0 as indicated here. It might be one of those random things where you get a random number so small and after you raise it to the 2, its gets so small it get truncated to 0. You might want to add a 0.000000001 or something to this value or check to make sure it doesn't ever hit zero or throw an On Error there to catch and handle it. That might be a cause. Unhandled error here and it wipes your globals out.