How to use workbook_open()?

[Susan Hayes]

Hello

Im trying to read a range of entries, but Im not sure how this works.

Under the excel objects in "this workbook" I wrote:

Public Sub Workbook_Open()

Dim i As Single
Dim j As Single
Dim myarray(18, 10)

For i = 1 To 18
For j = 1 To 10
myarray(i, j) = Worksheets("schedule").Cells(i + 3, Chr(68 + j))
Next
Next
End Sub



After in sheet1 I try to display the myarray to see if it was read but doesnt work. Can anyone help me with this?
Im also not sure how to pass variables between sheets, I can only do it within the same sheet.
Any help is greatly appreciate. This newsgroup has helped me alot
Thank you

Mike

 

[Guest]

In your code you are declaring the variable, including your array with Dim
statements. This means that they are only "valid" for the duration of the
sub or procedure. With "End Sub" these variable cease to exist.

When you say that you then try to show the myarray, it fails because it no
longer exists. You must do something with your array while still inside the
macro.

Alternatively, you can declare variables with a Public statement at the very
top of a module, RATHER than within the macro. This will cause the variable
to retain values beyond a single macro.

As for using variable in more than one Sheet -- there is no connection
between variables and sheets. In a macro you can declare a variable, give
it a value, then use it with any sheet:
ActiveSheet.Range("C5")=MyVar
Sheets("Customers").Range("CurrCust")=MyVar

HTH

 

[Tom Ogilvy]

gocush gave you a good explanation, but seemed to fail to answer your
specific question. If you want workbook wide visibility of a variable,
which you seem to do, insert a general module (insert=>Module) and at the
very top of the module above any procedures, put in the declaration

Public Myarray(18, 10)

then remove this declaration from any other module, including the
Workbook_Open

This will make Myarray visible to all modules in the workbook (as long as
you don't make the module private - public is the default, so this should
not be a problem)

Public Sub Workbook_Open()

Dim i As Single
Dim j As Single


For i = 1 To 18
For j = 1 To 10
myarray(i, j) = Worksheets("schedule").Cells(i + 3, Chr(68 + j))
Next
Next
End Sub

Also, the cells object accepts integers or letters, so you don't need to
convert to a letter.

since it appears you want to start in column E
? chr(68+1)
E

then
myarray(i, j) = Worksheets("schedule").Cells(i + 3, j+4)

should work.

--
Regards,
Tom Ogilvy

Hello

Im trying to read a range of entries, but Im not sure how this works.

Under the excel objects in "this workbook" I wrote:

Public Sub Workbook_Open()

Dim i As Single
Dim j As Single
Dim myarray(18, 10)

For i = 1 To 18
For j = 1 To 10
myarray(i, j) = Worksheets("schedule").Cells(i + 3, Chr(68 + j))
Next
Next
End Sub



After in sheet1 I try to display the myarray to see if it was read but

doesnt work. Can anyone help me with this?