How to store information (2002/SP3)

Topic

New Reply

I am looking a way to store information and then to call back this information later on in another part of the code using a VBA code. I’m just not sure how this data should be stored. Here is an example. I have two categories: Category 1 has 3 rows of data and category 2 has 5, as follows:

ABC Category
Row 1
Row 2
Row 3

XYZ Category
Row 1
Row 2
Row 3
Row 4
Row 5

I want to store this data such that for each category, I can call a procedure that will perform an action for each row in this category. For example, for category 1, I will perform 3 call actions, followed by category 2 where I will perform 5 call actions.

Any ideas?

Reply | Quote

Replies

Do you want to store the information in variables? If so, you could use arrays, e.g.

Dim arrABC(1 To 3) As String
arrABC(1) = “Row 1”
arrABC(2) = “Row 2”
arrABC(3) = “Row 3”

Later on you can loop through the items:

Dim i As Integer
For i = LBound(arrABC) To UBound(arrABC)
‘ Do something with array element
Debug.Print arrABC(i)
Next i

You can also store the data in cells on a (possibly hidden) worksheet. Or in a text file.

Reply | Quote

Thanks Hans.

The procedure that I wrote reads the categories and rows from a text file? How do I deal with unknown number of categories and unknown number of rows in an array?

Reply | Quote

If you don’t know how many categories there will be, arrays might not be the best solution.

Can you explain what you want to accomplish? That might focus our ideas.

Reply | Quote

The text file that I’m using to read from can have from 1 to 20 various categories and 1 to 100 rows in each category. I thought of using the array function and declare the array to be fixed using “Dim varArray (1 to 20, 1 to 100, 1 to 100)”, where the varArray is a 3-dimensional array containining (category name, test name, test code). One of the problems is that the data enteries in the array can change depending on the input text file. Is there a way to resize the array such that it would contain the actual number of enteries?

I’m planning to use this array to do this:
For each “category name” = 1 to UBound(“category name”)
For each “test name”” from N = 1 to 1 to UBound(“test name”)
For each “test name”” from R = 1 to 1 to UBound(“test name”)
Call procedure (M, R)

Reply | Quote

Sorry, in the last post it should read:

For each “test name”” from N = 1 to 1 to UBound(“category name”)
For each “test name”” from R = 1 to 1 to UBound(“test name”)
Call procedure (N, R)

Reply | Quote

It’s still not clear to me what you want to do with the data.

Reply | Quote

Sorry for not being clear. Let me see if the following explanation would be better.

I am a test file (see attached) that contains the temperature readings of a number of tests (specified by the name of the Experiment #) and listed for a number of thermocouple readings (specified by t #). What I want to do is extract specific information from each test. The information that I need is listed in another file called “RelevantTestData.txt” (see attached). Note that I might not be interested in all of the Experiments listed in the test file and not in all the thermocouple/temperature data listed under each Experiment.

For The example that I provided, the final data would listed in a spreadsheet (see attached).

Reply | Quote

I’d simply open both text files in Excel.
Loop sequentially through the cells in column A of relevanttestdata.xls and fill single variables with the names of the experiment, location and number.
When you have a complete set, create a new column in the outcome sheet, then loop through the cells of column A of testfile.xls and parse the text. Fill the outcome sheet as you go.

Reply | Quote

Thanks Hans, I’ll give it a try.

By the way, I have a question about using the command “ReDim Preserve array()”. What are the limitations of using the “ReDim Preserve” command for a 2 or a 3 dimensional array?

Reply | Quote

You can only change the last dimension when you use ReDim Preserve.

For example:

Dim arrData()
ReDim arrData(5, 8)
…
‘ The following instruction is valid:
ReDim Preserve arrData(5, 10)
‘ But this one causes an error:
ReDim Preserve arrData(7, 8)

Reply | Quote

Is there a way to redimension a multi-dimensional array while still preserving the contents of the array?

Reply | Quote

Only the last dimension, as I wrote in my previous reply. There is no way to resize the other dimensions while preserving the data.
You could create a new array with the desired dimensions, and copy the data from the old array to the new one.

Reply | Quote

Thanks Hans

Reply | Quote