Public Variables

[daniroy]

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

 

[Bernie Deitrick]

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

 

[daniroy]

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

 

[daniroy]

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
_____________________________________________________________________________________________________

 

[daniroy]

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

Daniel

 

[Bernie Deitrick]

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

 

[daniroy]

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

br
DR