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?

 

[Sektor]

Full procedure, please.

 

[User Name]

    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()
    Dim KT_bar As Double
    Dim KT_Day As Double
    Dim KT_Max As Double
    Dim FX As Double
    Dim Day As Integer
    Dim Gamma_X As Double
    Dim KT_Array(1 To 31) As Variant
    Dim Random_KT_Array(1 To 31) As Variant
    Dim Temp_Array(1 To 31) As Double
    Dim Random_Numbers(1 To 31) As Integer
    Dim Row_Counter As Double
    Dim Day_Step As Integer
    Dim Lag1 As Double
    
    Const e As Double = 2.718281828                 '2.7182818284590452353602874713526624977572

Days_in_Month = 28

Do While Days_in_Month < 32

    KT_bar = 0.21
    Count = 1
    Row_Counter = 1
      
 Do While Row_Counter < 1001
    
    KT_Max = 0.6313 + 0.267 * KT_bar - 11.9 * (KT_bar - 0.75) ^ 8
    FX = (KT_Max - 0.05) / (KT_Max - KT_bar)
    Gamma_X = -1.498 + (1.184 * FX - 27.182 * e ^ (-1.5 * FX)) / (KT_Max - 0.05)
   
        Day_Step = 1
        Day = 1
        k = 0
        
        Do While Day_Step < (Days_in_Month * 2)
        
            'Townsend's solution for daily Kt values - S1272
            KT_Day = 1 / Gamma_X * Log((1 - Day_Step / (Days_in_Month * 2)) * _
            e ^ (Gamma_X * 0.05) + Day_Step / (Days_in_Month * 2) * e ^ (Gamma_X * KT_Max))
            
            KT_Array(Day) = KT_Day
            Day = Day + 1
            Day_Step = Day_Step + 2
               
        Loop
            
        Do
        X = Int(Rnd * Days_in_Month) + 1
        If Not Temp_Array(X) Then
        k = k + 1
        Random_Numbers(k) = X
        Temp_Array(X) = True
        End If
        Loop Until k = Days_in_Month
        
        Erase Temp_Array
               
        Day = 1
       
        Do While Day < Days_in_Month + 1
            Random_KT_Array(Day) = KT_Array(Random_Numbers(Day))
            Day = Day + 1
        Loop

        '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
        
        Day = 1
        
            If Lag1 > 0.15 Then
                If Lag1 < 0.35 Then
                Row_Counter = Row_Counter + 1
                
                If Row_Counter < 1001 Then
                    Do While Day < Days_in_Month + 1
                    
                        If Days_in_Month = 28 Then
                        Day_Series_28(Day, Row_Counter) = Random_Numbers(Day)
                        End If
                        
                        If Days_in_Month = 29 Then
                        Day_Series_29(Day, Row_Counter) = Random_Numbers(Day)
                        End If
                        
                        If Days_in_Month = 30 Then
                        Day_Series_30(Day, Row_Counter) = Random_Numbers(Day)
                        End If
                        
                        If Days_in_Month = 31 Then
                        Day_Series_31(Day, Row_Counter) = Random_Numbers(Day)
                        End If
                        
                        Day = Day + 1
                    Loop
                End If
                
                KT_bar = KT_bar + 0.01
                    If KT_bar > 0.85 Then
                        KT_bar = 0.2
                    End If
                End If
            End If
        Count = Count + 1
        
        If Count = 10000 Then
        xyd = 4758
        End If
        
    Loop
    
    Days_in_Month = Days_in_Month + 1
    
    Loop
        
End Sub

 

[User Name]

Ahhh...


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

Needs to be:

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

Problem solved

 

[RoryA]

There is no difference between Global and Public other than that Global does not work in object modules. It is kept purely for compatibility with earlier versions of VB(A).

 

[User Name]

I'm running Excel 2010... Using Public didn't work. I changed over to Global and I started getting values. There's clearly a difference of some sort.

 

[RoryA]

Nope. They both do the same thing. Tested your code with both Public and Global and they behave the same.

 

[User Name]

Try putting the code in a module and see if your worksheets can still see the data with public.

 

[RoryA]

Yes they can. (the code has to be in a module, so I'm not sure what you meant by that?)

 

[User Name]

Hmmm... Well, the problem isn't with the declaration of the variables at all. Public or Global work/don't work equally well.

The first time I call on the data in one of the Day_Series arrays I find it. When I run a second simulation that tries to call on the Day_Series array I find that that arrays are empty.

What, besides closing a workbook, could cause my Day_Series arrays to empty?