Global array declare

[Souris]

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,

[Norman Jones]

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" <[email protected]> wrote in message
news:[email protected]...
>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,
>
>

[RB Smissaert]

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" <[email protected]> wrote in message
news:[email protected]...
>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,
>
>

[Norman Jones]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>>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,
>>
>>
>
>