Use of Public for data declaration in Excel 2000

[Gandalph]

I want to use the same data in sub routines in different sheets of a workbook.
I wrote the following test routines, the first pair "in Sheet 1" and the
last "in Sheet 2"
The variable InUseCol was accessible to both routines in Sheet 1, but the
routine in Sheet 2 could not access it - returns a null value

Sheet 1

Public InUseCol As Integer
Sub GenTest()

InUseCol = 5
MsgBox InUseCol, vbOKOnly, "Selected Column"
End Sub
Sub TestingSh1()
MsgBox InUseCol, vbOKOnly, "Box 2"
End Sub

Sheet 2

Sub TestingSh2()
MsgBox InUseCol, vbOKOnly, "Box 3"
End Sub

Assistance much appreciated
Gandalph

 

[Jim Rech]

Typically public variable should be put in standard modules (Insert, Module)
rather than sheet modules. They can be seen from anywhere in the project.

Alternatively you can pint to them by using the codename of the sheet
module:

Sub TestingSh1()
MsgBox Sheet1.InUseCol, vbOKOnly, "Box 2"
End Sub


--
Jim
|I want to use the same data in sub routines in different sheets of a
workbook.
| I wrote the following test routines, the first pair "in Sheet 1" and the
| last "in Sheet 2"
| The variable InUseCol was accessible to both routines in Sheet 1, but the
| routine in Sheet 2 could not access it - returns a null value
|
| Sheet 1
|
| Public InUseCol As Integer
| Sub GenTest()
|
| InUseCol = 5
| MsgBox InUseCol, vbOKOnly, "Selected Column"
| End Sub
| Sub TestingSh1()
| MsgBox InUseCol, vbOKOnly, "Box 2"
| End Sub
|
| Sheet 2
|
| Sub TestingSh2()
| MsgBox InUseCol, vbOKOnly, "Box 3"
| End Sub
|
| Assistance much appreciated
| Gandalph