help with variable

A

aneurin

Hi i am having a problem with a variable
the variable is called data_to_find and is used several time in my excel workbook
i have declared it as public


Option Explicit
Public data_to_find As Variant
data_to_find = Date - 1

but when i run my code i get the error invalid outside proceedure

hope someone can help
 
B

Basil

You can either set the variable within your procedure (after the Sub xxxx() line), or create it as a public function - in that way it can also be used from within the worksheet.

This air code will allow you to set the days before today whenever the function is used:

Public Function Data_to_find(i as Integer)
Data_to_find = Date - i
End Function

This way you would call it by saying Data_to_find(3) for three days back etc.

If you always want it to be yesterday then use,

Public Function Data_to_find()
Data_to_find = Date - 1
End Function

simply call Data_to_find()

Baz
 
B

Bob Phillips

You can create a variable of module scope outside of a procedure, but you
can only access it from within a sub or a function.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
J

Jamie Collins

Bob Phillips said:
You can create a variable of module scope outside of a procedure, but you
can only access it from within a sub or a function.
Click to expand...

Option Explicit

Private m_lngValue As Long

Public Property Get Value() As Long
Value = m_lngValue
End Property

;-)

Jamie.

--
 
B

Bob Phillips

Lol. But in a standard module what use is it?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top