Hi,

My public variables can't be referenced by sub routines in my modules.
The two msgboxes in the code below display them correctly.


Public newDataSheet As Worksheet
Public nikkeiTime As Date

Private Sub Workbook_Open()

Set newDataSheet = Workbooks("Historic Data").Worksheets("NewData")
nikkeiTime = "00:00:05"

MsgBox nikkeiTime
MsgBox newDataSheet.name

Application.OnTime Now + TimeValue("00:00:01"), "runSchedule"

End Sub


However the msgboxes in runSchdule are blank, code shown below

Sub runSchedule()

MsgBox nikkeiTime
MsgBox newDataSheet.name

End Sub

Can anybody help me with this one please?

Thanks

T:)


--
Tomski
------------------------------------------------------------------------
Tomski's Profile: http://www.excelforum.com/member.php...o&userid=26824
View this thread: http://www.excelforum.com/showthread...hreadid=570344

Put the public variables in a general module, not the thisworkbook module.
You can still initialize them in the Workbook_Open event. Your runSchedule
routine should also be in a general module.

--
Regards,
Tom Ogilvy


"Tomski" wrote:


Hi,

My public variables can't be referenced by sub routines in my modules.
The two msgboxes in the code below display them correctly.


Public newDataSheet As Worksheet
Public nikkeiTime As Date

Private Sub Workbook_Open()

Set newDataSheet = Workbooks("Historic Data").Worksheets("NewData")
nikkeiTime = "00:00:05"

MsgBox nikkeiTime
MsgBox newDataSheet.name

Application.OnTime Now + TimeValue("00:00:01"), "runSchedule"

End Sub


However the msgboxes in runSchdule are blank, code shown below

Sub runSchedule()

MsgBox nikkeiTime
MsgBox newDataSheet.name

End Sub

Can anybody help me with this one please?

Thanks

T:)


--
Tomski
------------------------------------------------------------------------
Tomski's Profile: http://www.excelforum.com/member.php...o&userid=26824
View this thread: http://www.excelforum.com/showthread...hreadid=570344

Set newDataSheet = Workbooks("Historic Data").Worksheets("NewData")


does it really work ??

dont think so

why dont you use "option explicit" directive ?

Thanks mate, just the job.

T


--
Tomski
------------------------------------------------------------------------
Tomski's Profile: http://www.excelforum.com/member.php...o&userid=26824
View this thread: http://www.excelforum.com/showthread...hreadid=570344