help with variable
 

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

help with variable
 

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

"aneurin" wrote:

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

help with variable
 

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)

"aneurin" wrote in message
om...
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

help with variable
 

"Bob Phillips" wrote ...

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.

Option Explicit

Private m_lngValue As Long

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

;-)

Jamie.

--

help with variable
 

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)

"Jamie Collins" wrote in message
om...
"Bob Phillips" wrote ...

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.

Option Explicit

Private m_lngValue As Long

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

;-)

Jamie.

--