Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use workbook_open()?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use workbook_open()?
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 "Susan Hayes" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use workbook_open()?
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 "Susan Hayes" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Workbook_Open () | Excel Discussion (Misc queries) | |||
Workbook_Open() will not run | Excel Programming | |||
Workbook_Open | Excel Programming | |||
workbook_open | Excel Programming | |||
Workbook_Open | Excel Programming |