I need a global array to access from my VBA code.

I have following code on thisworkbook declaration areare
Dim GradeSheet(1 To 7) As String

To initilize it in Workbook_open

Private Sub Workbook_Open()
GradeSheet(1) = "GRADE"
GradeSheet(2) = "EXAM"
GradeSheet(3) = "HOME WORK"
GradeSheet(4) = "READING"
GradeSheet(5) = "SPEAKING"
GradeSheet(6) = "COMPOSITION"
GradeSheet(7) = "PROJECT"
End Sub

in my spreadsheet have following code


dim i, istudent as integer
For i = 2 To 7
Call COPY_STUDENT_INFO_DETAILS(GradeSheet(i), iStudent)
Next i


I got compiled error "sub or function not defined
It seems VBA considers GradeSheet as a function or sub not an array.

Where I did wrong here?
Any information is great appreciated,

Hi Souris,

I have following code on thisworkbook declaration areare
Dim GradeSheet(1 To 7) As String

Move this Declaration from the ThisWorkbook module to a standard code
module.

dim i, istudent as integer

This declaration does not do what you expect it to do. Your declaration is
equivalent to:

Dim i As variant, iStudent As Integer

You need explicitly to dim each variable:

Dim i As Integer, iStudent As Integer

Call COPY_STUDENT_INFO_DETAILS(GradeSheet(i), iStudent)

You have not initialised the iStudent variable.

To test the above, I successfully ran the simple:

Sub Tester01()

Dim i As Long, iStudent As Long

For i = LBound(GradeSheet) To UBound(GradeSheet)
Debug.Print GradeSheet(i)
Next i

End Sub


---
Regards,
Norman



"Souris" wrote in message
...
I need a global array to access from my VBA code.

I have following code on thisworkbook declaration areare
Dim GradeSheet(1 To 7) As String

To initilize it in Workbook_open

Private Sub Workbook_Open()
GradeSheet(1) = "GRADE"
GradeSheet(2) = "EXAM"
GradeSheet(3) = "HOME WORK"
GradeSheet(4) = "READING"
GradeSheet(5) = "SPEAKING"
GradeSheet(6) = "COMPOSITION"
GradeSheet(7) = "PROJECT"
End Sub

in my spreadsheet have following code


dim i, istudent as integer
For i = 2 To 7
Call COPY_STUDENT_INFO_DETAILS(GradeSheet(i), iStudent)
Next i


I got compiled error "sub or function not defined
It seems VBA considers GradeSheet as a function or sub not an array.

Where I did wrong here?
Any information is great appreciated,

Take Dim GradeSheet(1 To 7) As String out of the ThisWorkbook module

and put this:
Public GradeSheet(1 To 7) As String

At the top of a normal code module. It could be the module where you have:
COPY_STUDENT_INFO_DETAILS

GradeSheet has to be declared Public, but you can't do that in the
ThisWorkbook module as this is not a normal module, but really a class
module.

RBS


"Souris" wrote in message
...
I need a global array to access from my VBA code.

I have following code on thisworkbook declaration areare
Dim GradeSheet(1 To 7) As String

To initilize it in Workbook_open

Private Sub Workbook_Open()
GradeSheet(1) = "GRADE"
GradeSheet(2) = "EXAM"
GradeSheet(3) = "HOME WORK"
GradeSheet(4) = "READING"
GradeSheet(5) = "SPEAKING"
GradeSheet(6) = "COMPOSITION"
GradeSheet(7) = "PROJECT"
End Sub

in my spreadsheet have following code


dim i, istudent as integer
For i = 2 To 7
Call COPY_STUDENT_INFO_DETAILS(GradeSheet(i), iStudent)
Next i


I got compiled error "sub or function not defined
It seems VBA considers GradeSheet as a function or sub not an array.

Where I did wrong here?
Any information is great appreciated,

Hi Souris,

Move this Declaration from the ThisWorkbook module to a standard code
module.

Should read:

Move this Declaration from the ThisWorkbook module to a standard code
module and declare it with Public scope:

Option Explicit
Public GradeSheet(1 To 7) As String

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Souris,

I have following code on thisworkbook declaration areare
Dim GradeSheet(1 To 7) As String

Move this Declaration from the ThisWorkbook module to a standard code
module.

dim i, istudent as integer

This declaration does not do what you expect it to do. Your declaration is
equivalent to:

Dim i As variant, iStudent As Integer

You need explicitly to dim each variable:

Dim i As Integer, iStudent As Integer

Call COPY_STUDENT_INFO_DETAILS(GradeSheet(i), iStudent)

You have not initialised the iStudent variable.

To test the above, I successfully ran the simple:

Sub Tester01()

Dim i As Long, iStudent As Long

For i = LBound(GradeSheet) To UBound(GradeSheet)
Debug.Print GradeSheet(i)
Next i

End Sub


---
Regards,
Norman



"Souris" wrote in message
...
I need a global array to access from my VBA code.

I have following code on thisworkbook declaration areare
Dim GradeSheet(1 To 7) As String

To initilize it in Workbook_open

Private Sub Workbook_Open()
GradeSheet(1) = "GRADE"
GradeSheet(2) = "EXAM"
GradeSheet(3) = "HOME WORK"
GradeSheet(4) = "READING"
GradeSheet(5) = "SPEAKING"
GradeSheet(6) = "COMPOSITION"
GradeSheet(7) = "PROJECT"
End Sub

in my spreadsheet have following code


dim i, istudent as integer
For i = 2 To 7
Call COPY_STUDENT_INFO_DETAILS(GradeSheet(i), iStudent)
Next i


I got compiled error "sub or function not defined
It seems VBA considers GradeSheet as a function or sub not an array.

Where I did wrong here?
Any information is great appreciated,