Public Variables

Hello there and thanks in advance for people that are gonna help!

At the beginning of my module, I define the following public variables
as I will need to use them in different calculations
______________________________________________________________
Public FirstFuture As Single
Public SecondFuture As Single
Public ThirdFuture As Single
Public FourthFuture As Single
______________________________________________________________

Then in the following Sub, I do define the value of this Public
Variables as follow
______________________________________________________________
Sub CalculateFuturesLevels()

Dim FirstFuture As Single
Dim SecondFuture As Single
Dim ThirdFuture As Single
Dim FourthFuture As Single

FirstFuture = (Sheets("FTSE").Range("C2").Value +
Sheets("FTSE").Range("D2").Value) / 2
SecondFuture = (Sheets("FTSE").Range("C3").Value +
Sheets("FTSE").Range("D3").Value) / 2
ThirdFuture = (Sheets("FTSE").Range("C5").Value +
Sheets("FTSE").Range("D5").Value) / 2
FourthFuture = (Sheets("FTSE").Range("C6").Value +
Sheets("FTSE").Range("D6").Value) / 2

Sheets("Implied Dividends").Range("R5") = FirstFuture
Sheets("Implied Dividends").Range("R6") = SecondFuture
Sheets("Implied Dividends").Range("R7") = ThirdFuture
Sheets("Implied Dividends").Range("R8") = FourthFuture
______________________________________________________________

Up to now everything is fine, and I now - maybe am I wrong - to be able
to retrieve these Public Variables as soon as I need them.

But unfortunatelly in the test sub
______________________________________________________________
Sub test()

Sheets("Implied Dividends").Range("Q15") = FirstFuture

End Sub
______________________________________________________________

I only retrieve "0" as if the variable "FirstFuture" is not stored at
all.

Am I expecting too much of Public Variable, am I on the wrong road or I
am missing something really obvious ? Why my variables are not stored ?

Many thanks for your help ...

best regards
Daniel

You're double-declaring your variables, so the variables actually used in CalculateFuturesLevels
aren't the global variables. Simply remove the declarations from here:

Sub CalculateFuturesLevels()

Dim FirstFuture As Single
Dim SecondFuture As Single
Dim ThirdFuture As Single
Dim FourthFuture As Single

FirstFuture = (Sheets("FTSE").Range("C2").Value + .....

So you should end up with

Sub CalculateFuturesLevels()
FirstFuture = (Sheets("FTSE").Range("C2").Value +.....


HTH,
Bernie
MS Excel MVP


<[email protected]> wrote in message news:[email protected]...
> Hello there and thanks in advance for people that are gonna help!
>
> At the beginning of my module, I define the following public variables
> as I will need to use them in different calculations
> ______________________________________________________________
> Public FirstFuture As Single
> Public SecondFuture As Single
> Public ThirdFuture As Single
> Public FourthFuture As Single
> ______________________________________________________________
>
> Then in the following Sub, I do define the value of this Public
> Variables as follow
> ______________________________________________________________
> Sub CalculateFuturesLevels()
>
> Dim FirstFuture As Single
> Dim SecondFuture As Single
> Dim ThirdFuture As Single
> Dim FourthFuture As Single
>
> FirstFuture = (Sheets("FTSE").Range("C2").Value +
> Sheets("FTSE").Range("D2").Value) / 2
> SecondFuture = (Sheets("FTSE").Range("C3").Value +
> Sheets("FTSE").Range("D3").Value) / 2
> ThirdFuture = (Sheets("FTSE").Range("C5").Value +
> Sheets("FTSE").Range("D5").Value) / 2
> FourthFuture = (Sheets("FTSE").Range("C6").Value +
> Sheets("FTSE").Range("D6").Value) / 2
>
> Sheets("Implied Dividends").Range("R5") = FirstFuture
> Sheets("Implied Dividends").Range("R6") = SecondFuture
> Sheets("Implied Dividends").Range("R7") = ThirdFuture
> Sheets("Implied Dividends").Range("R8") = FourthFuture
> ______________________________________________________________
>
> Up to now everything is fine, and I now - maybe am I wrong - to be able
> to retrieve these Public Variables as soon as I need them.
>
> But unfortunatelly in the test sub
> ______________________________________________________________
> Sub test()
>
> Sheets("Implied Dividends").Range("Q15") = FirstFuture
>
> End Sub
> ______________________________________________________________
>
> I only retrieve "0" as if the variable "FirstFuture" is not stored at
> all.
>
> Am I expecting too much of Public Variable, am I on the wrong road or I
> am missing something really obvious ? Why my variables are not stored ?
>
> Many thanks for your help ...
>
> best regards
> Daniel
>

Thanks a lot Bernie, it is indeed the case! I will perhaps come back
soon as another related question did arise very quickly!

regards
Daniel

Thank you again Bernie, but I am missing something - again - on my next
sub().

FirstFuture
SecondFuture
ThirdFuture
FourthFuture
has well been defined as Public Variables

In the Sub CalculateFuturesRolls() I am using these values to define
new variables

FirstFutureRoll = FirstFuture - FirstFuture
SecondFutureRoll = SecondFuture - FirstFuture
ThirdFutureRoll = ThirdFuture - FirstFuture
FourthFutureRoll = FourthFuture - FirstFuture

They are well defined as I can see their values in the local window. My
goal is to input the values calculated there "FirstFutureRoll" or
"SecondFutureRoll" into some specific places in one of my columns.

But when I want to run the macro, I get a run time error 1004 "you
cannot change part of an array".

I am surprise, I recon. It does not looks like linked to Variables
anymore but probably to a syntax error of my own. If anybody figure out
what, be sure I still appreciate!

best regards everybody
Daniel

_______________________________________________________________________________________________
Sub CalculateFuturesRolls()

FirstFutureRoll = FirstFuture - FirstFuture
SecondFutureRoll = SecondFuture - FirstFuture
ThirdFutureRoll = ThirdFuture - FirstFuture
FourthFutureRoll = FourthFuture - FirstFuture

Sheets("Implied Dividends").Range("N15").Value = SecondFutureRoll

For i = 4 To 13

If Sheets("Implied Dividends").Range("B" & i) = "Future Roll 1" Then
Sheets("Implied Dividends").Range("N" & i).Value = FirstFutureRoll
If Sheets("Implied Dividends").Range("B" & i) = "Future Roll 2" Then
Sheets("Implied Dividends").Range("N" & i).Value = SecondFutureRoll
If Sheets("Implied Dividends").Range("B" & i) = "Future Roll 3" Then
Sheets("Implied Dividends").Range("N" & i).Value = ThirdFutureRoll
If Sheets("Implied Dividends").Range("B" & i) = "Future Roll 4" Then
Sheets("Implied Dividends").Range("N" & i).Value = FourthFutureRoll

Next i

End Sub
_____________________________________________________________________________________________________

My dumb dumb dumb mistake, it is just fine!
sorry about it!
best regards and thanks again Bernie

Daniel

That sounds like you have something array entered on your sheet: a multi-cell array formula usually
throws that kind of error.

Try this macro, to see if that is the case:

Sub FindArray()
For i = 4 To 13
On Error GoTo NoArrayFound
If Sheets("Implied Dividends").Range("N" & i).CurrentArray.Cells.Count > 1 Then
MsgBox Range("N" & i).CurrentArray.Address & " are array entered."
End If
NoArrayFound:
Resume Next
Next i
End Sub

HTH,
Bernie
MS Excel MVP


<[email protected]> wrote in message news:[email protected]...
> Thank you again Bernie, but I am missing something - again - on my next
> sub().
>
> FirstFuture
> SecondFuture
> ThirdFuture
> FourthFuture
> has well been defined as Public Variables
>
> In the Sub CalculateFuturesRolls() I am using these values to define
> new variables
>
> FirstFutureRoll = FirstFuture - FirstFuture
> SecondFutureRoll = SecondFuture - FirstFuture
> ThirdFutureRoll = ThirdFuture - FirstFuture
> FourthFutureRoll = FourthFuture - FirstFuture
>
> They are well defined as I can see their values in the local window. My
> goal is to input the values calculated there "FirstFutureRoll" or
> "SecondFutureRoll" into some specific places in one of my columns.
>
> But when I want to run the macro, I get a run time error 1004 "you
> cannot change part of an array".
>
> I am surprise, I recon. It does not looks like linked to Variables
> anymore but probably to a syntax error of my own. If anybody figure out
> what, be sure I still appreciate!
>
> best regards everybody
> Daniel
>
> _______________________________________________________________________________________________
> Sub CalculateFuturesRolls()
>
> FirstFutureRoll = FirstFuture - FirstFuture
> SecondFutureRoll = SecondFuture - FirstFuture
> ThirdFutureRoll = ThirdFuture - FirstFuture
> FourthFutureRoll = FourthFuture - FirstFuture
>
> Sheets("Implied Dividends").Range("N15").Value = SecondFutureRoll
>
> For i = 4 To 13
>
> If Sheets("Implied Dividends").Range("B" & i) = "Future Roll 1" Then
> Sheets("Implied Dividends").Range("N" & i).Value = FirstFutureRoll
> If Sheets("Implied Dividends").Range("B" & i) = "Future Roll 2" Then
> Sheets("Implied Dividends").Range("N" & i).Value = SecondFutureRoll
> If Sheets("Implied Dividends").Range("B" & i) = "Future Roll 3" Then
> Sheets("Implied Dividends").Range("N" & i).Value = ThirdFutureRoll
> If Sheets("Implied Dividends").Range("B" & i) = "Future Roll 4" Then
> Sheets("Implied Dividends").Range("N" & i).Value = FourthFutureRoll
>
> Next i
>
> End Sub
> _____________________________________________________________________________________________________
>

thanks again Bernie, you are indeed right, I have been slightly
stupid...

br
DR