arrays and global variables

[hcvink]

Hi all,

I have a workbook with 5 sheets. All the sheets have different meaning but have one id in common. The order Id.

I am using a UserForm to represent the data in these sheets. Not only to create a more readable overview of what is on the sheets, but also to let people find, edit, add information.

On this userform I made the option to filter the information.

I want to create an array for each worksheet which contains all the information in the worksheet. These Arrays have to stay in memory and should be available at all times while the workbook is open.

I am not sure where to create these arrays and how I can keep them available.

Any pointers/help would be very much appreciated. I do not seek complete solutions but just a push in the right direction.

 

[osx99]

First question is do you really need to store the values in an array since the values are available already within each sheet?

If the answer is still yes (as often is) you could declare public variables in a MODULE which is then available to all workbooks/worksheets; eg...

Option Explicit
Public URLarray() As Variant
Public URLarray() As String
Public URLarray() As Long

then assign values/range/other to your public variables

 

[osx99]

Sorry should have said, example given hasn't dimensioned the array

either use example given and redim variable in seperate procedure...

ReDim URLarray(1 To 10, 3)

or define as fixed size outside procedure

Public URLarray(1 To 10, 3) As Variant

 

[SkipVought]

Unless you're doing a LOT of heavy looping (10's of thousands of rows MANY times), just reference the ranges in each sheet. I almost exclusively use this approch.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MajP]

{quote]
I want to create an array for each worksheet which contains all the information in the worksheet. These Arrays have to stay in memory and should be available at all times while the workbook is open.
[/quote]
I will echo. Why???
If this is an academic exercise have at it, but if not it sounds like a complete waste of time and effort.
You have available a robust set of excel objects especial the Range object, so why put into arrays? If you plan to manipulate, summarize, and perform functions on arrays what a tremendous amount of additional effort.

Do your users have Access? You could bring the worksheets in as linked tables.

I have a workbook with 5 sheets. All the sheets have different meaning but have one id in common. The order Id

Sounds like tables in a relational database

Not only to create a more readable overview of what is on the sheets, but also to let people find, edit, add information.

If you had Access as your front end, you have an army of capabilities to make GUIs that find, edit, add, summarize, report, and hide the complexity of the worksheets.

 

[hcvink]

Thanks for the replies all. I realize that I have to rethink the way I am doing what I want. I created something and moved on from what I made. A step back is needed to be able to move forward.

I want to stay with excel for now and I will just fill the listbox I have with selecting a range and putting it in.

 

[hcvink]

Ok a follow up question. I have now a working listbox which I populated with a range. I now want to be able to filter the data in the listbox based on user entry.

The examples I see after googling are using arrays. Since I just gave up arrays to fill the listbox I wondered if there is another way.

 

[SkipVought]

I would use MS Query to generate the next list based on user's selection(s). faq68-5829

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}